Previous Thread
Next Thread
Print Thread
Rate Thread
Ultra slow mySQL dump import [windows] #311498
09/13/2006 9:42 AM
09/13/2006 9:42 AM
Joined: Mar 2004
Posts: 122
EU, Slovenia
P
PaNTerSan Offline OP
Journeyman
PaNTerSan  Offline OP
Journeyman
P
Joined: Mar 2004
Posts: 122
EU, Slovenia
environment: WinXP, apache, mySQL 4.0.26, threads

Before, (after mySQL installation) when I was importing threads database from actual server to a local environment, it took about 5 minutes (maybe less) to import 80Mb dump.

I don't know what happened but NOW, it takes more than 40 minutes to import 82Mb dump shocked

my.ini
Code
 
key_buffer = 256M 
table_cache = 256
thread_cache = 8
thread_concurrency = 4

max_allowed_packet = 4M
sort_buffer_size = 1M
read_buffer_size = 1M
net_buffer_length = 8K
myisam_sort_buffer_size = 16M 

query_cache_limit = 1M
query_cache_size = 8M
query_cache_type = 1 


# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
innodb_buffer_pool_size = 128M
innodb_additional_mem_pool_size = 32M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 128M
innodb_log_buffer_size = 8M


[mysql]
no-auto-rehash


[mysqldump]
quick
max_allowed_packet = 16M



extended-status after 6 minutes of importing database
Code
 
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| Aborted_clients          | 0       |
| Aborted_connects         | 3       |
| Bytes_received           | 574370  |
| Bytes_sent               | 217140  |
| Com_admin_commands       | 0       |
| Com_alter_table          | 0       |
| Com_analyze              | 0       |
| Com_backup_table         | 0       |
| Com_begin                | 0       |
| Com_change_db            | 0       |
| Com_change_master        | 0       |
| Com_check                | 0       |
| Com_commit               | 0       |
| Com_create_db            | 1       |
| Com_create_function      | 0       |
| Com_create_index         | 0       |
| Com_create_table         | 17      |
| Com_delete               | 0       |
| Com_delete_multi         | 0       |
| Com_drop_db              | 1       |
| Com_drop_function        | 0       |
| Com_drop_index           | 0       |
| Com_drop_table           | 0       |
| Com_flush                | 0       |
| Com_grant                | 1       |
| Com_ha_close             | 0       |
| Com_ha_open              | 0       |
| Com_ha_read              | 0       |
| Com_insert               | 8443    |
| Com_insert_select        | 0       |
| Com_kill                 | 0       |
| Com_load                 | 0       |
| Com_load_master_data     | 0       |
| Com_load_master_table    | 0       |
| Com_lock_tables          | 0       |
| Com_optimize             | 0       |
| Com_purge                | 0       |
| Com_rename_table         | 0       |
| Com_repair               | 0       |
| Com_replace              | 0       |
| Com_replace_select       | 0       |
| Com_reset                | 0       |
| Com_restore_table        | 0       |
| Com_revoke               | 0       |
| Com_rollback             | 0       |
| Com_savepoint            | 0       |
| Com_select               | 0       |
| Com_set_option           | 0       |
| Com_show_binlog_events   | 0       |
| Com_show_binlogs         | 0       |
| Com_show_create          | 0       |
| Com_show_databases       | 0       |
| Com_show_fields          | 0       |
| Com_show_grants          | 0       |
| Com_show_innodb_status   | 0       |
| Com_show_keys            | 0       |
| Com_show_logs            | 0       |
| Com_show_master_status   | 0       |
| Com_show_new_master      | 0       |
| Com_show_open_tables     | 0       |
| Com_show_processlist     | 77      |
| Com_show_slave_hosts     | 0       |
| Com_show_slave_status    | 0       |
| Com_show_status          | 44      |
| Com_show_tables          | 0       |
| Com_show_variables       | 1       |
| Com_slave_start          | 0       |
| Com_slave_stop           | 0       |
| Com_truncate             | 0       |
| Com_unlock_tables        | 0       |
| Com_update               | 0       |
| Com_update_multi         | 0       |
| Connections              | 14      |
| Created_tmp_disk_tables  | 0       |
| Created_tmp_files        | 3       |
| Created_tmp_tables       | 0       |
| Delayed_errors           | 0       |
| Delayed_insert_threads   | 0       |
| Delayed_writes           | 0       |
| Flush_commands           | 1       |
| Handler_commit           | 0       |
| Handler_delete           | 0       |
| Handler_read_first       | 1       |
| Handler_read_key         | 10      |
| Handler_read_next        | 0       |
| Handler_read_prev        | 0       |
| Handler_read_rnd         | 0       |
| Handler_read_rnd_next    | 17      |
| Handler_rollback         | 1       |
| Handler_update           | 0       |
| Handler_write            | 8443    |
| Key_blocks_used          | 2       |
| Key_read_requests        | 32      |
| Key_reads                | 0       |
| Key_write_requests       | 34      |
| Key_writes               | 34      |
| Max_used_connections     | 3       |
| Not_flushed_delayed_rows | 0       |
| Not_flushed_key_blocks   | 0       |
| Open_files               | 12      |
| Open_streams             | 0       |
| Open_tables              | 14      |
| Opened_tables            | 37      |
| Qcache_free_blocks       | 1       |
| Qcache_free_memory       | 8379904 |
| Qcache_hits              | 0       |
| Qcache_inserts           | 0       |
| Qcache_lowmem_prunes     | 0       |
| Qcache_not_cached        | 0       |
| Qcache_queries_in_cache  | 0       |
| Qcache_total_blocks      | 1       |
| Questions                | 8591    |
| Rpl_status               | NULL    |
| Select_full_join         | 0       |
| Select_full_range_join   | 0       |
| Select_range             | 0       |
| Select_range_check       | 0       |
| Select_scan              | 0       |
| Slave_open_temp_tables   | 0       |
| Slave_running            | OFF     |
| Slow_launch_threads      | 0       |
| Slow_queries             | 0       |
| Sort_merge_passes        | 0       |
| Sort_range               | 0       |
| Sort_rows                | 0       |
| Sort_scan                | 0       |
| Table_locks_immediate    | 8449    |
| Table_locks_waited       | 0       |
| Threads_cached           | 0       |
| Threads_connected        | 4       |
| Threads_created          | 4       |
| Threads_running          | 2       |
| Uptime                   | 387     |
+--------------------------+---------+


Kind regards,
PaNTerSan
Sponsored Links
Re: Ultra slow mySQL dump import [windows] [Re: PaNTerSan] #311504
09/13/2006 1:28 PM
09/13/2006 1:28 PM
Joined: Mar 2000
Posts: 25,587
Texas
AllenAyres Offline
I type Like navaho
AllenAyres  Offline
I type Like navaho
Joined: Mar 2000
Posts: 25,587
Texas
How much ram do you have and is the server dedicated or you use it for something else? (winxp).

Also, do you have other scripts installed keeping you from upgrading mysql and php? threads runs fine on the latest of each I believe.


- Allen wavey
- What Drives You?
Re: Ultra slow mySQL dump import [windows] [Re: AllenAyres] #311513
09/14/2006 3:34 AM
09/14/2006 3:34 AM
Joined: Mar 2004
Posts: 122
EU, Slovenia
P
PaNTerSan Offline OP
Journeyman
PaNTerSan  Offline OP
Journeyman
P
Joined: Mar 2004
Posts: 122
EU, Slovenia
Originally Posted by AllenAyres
How much ram do you have and is the server dedicated or you use it for something else? (winxp).

WinXP, desktop, 1Gb ram, 2x SATA disk, 2x IDE disk, mySQL database on SATA disk, import files on another SATA disk.

Originally Posted by AllenAyres

Also, do you have other scripts installed keeping you from upgrading mysql and php? threads runs fine on the latest of each I believe.

On the localhost I could do anything smile

php is 4.4, and this I wouldn't dare to upgrade to 5.0 but mysql could be ugpraded. We have InoDB databases and one database is MyISAM type.


But on server there are running 5 dynamic sites (mySQL databases) and 5+ static sites.

And import on server works fast. And that's the issue: i don't understand why on my local machine, import is so slow now. It used to be fast.


Could it be that some microsoft update is messing up mysql?


Kind regards,
PaNTerSan
Re: Ultra slow mySQL dump import [windows] [Re: PaNTerSan] #311519
09/14/2006 5:41 PM
09/14/2006 5:41 PM
Joined: Mar 2000
Posts: 25,587
Texas
AllenAyres Offline
I type Like navaho
AllenAyres  Offline
I type Like navaho
Joined: Mar 2000
Posts: 25,587
Texas
hmm.. maybe your local machine has background processes taking up your processor and memory. It runs fine on my winxp notebook with apache installed. I also use php5 something and mysql 5 something as well smile


- Allen wavey
- What Drives You?
Re: Ultra slow mySQL dump import [windows] [Re: AllenAyres] #311520
09/15/2006 3:26 AM
09/15/2006 3:26 AM
Joined: Mar 2004
Posts: 122
EU, Slovenia
P
PaNTerSan Offline OP
Journeyman
PaNTerSan  Offline OP
Journeyman
P
Joined: Mar 2004
Posts: 122
EU, Slovenia
To make things worse - this is happening on two different machines.


Kind regards,
PaNTerSan
Sponsored Links
Re: Ultra slow mySQL dump import [windows] [Re: PaNTerSan] #311529
09/17/2006 2:07 PM
09/17/2006 2:07 PM
Joined: Mar 2004
Posts: 122
EU, Slovenia
P
PaNTerSan Offline OP
Journeyman
PaNTerSan  Offline OP
Journeyman
P
Joined: Mar 2004
Posts: 122
EU, Slovenia
Solution: skip-innodb

Performance: from 40minutes to 30 seconds.

Code
set-variable = lower_case_table_names=2

set-variable = key_buffer=32M
set-variable = max_allowed_packet=4M
set-variable = table_cache=128
set-variable = sort_buffer=8M
set-variable = net_buffer_length=8K
set-variable = myisam_sort_buffer_size=4M
set-variable = read_buffer_size=2M

skip-innodb


Kind regards,
PaNTerSan
Re: Ultra slow mySQL dump import [windows] [Re: PaNTerSan] #311541
09/19/2006 5:27 PM
09/19/2006 5:27 PM
Joined: Mar 2000
Posts: 25,587
Texas
AllenAyres Offline
I type Like navaho
AllenAyres  Offline
I type Like navaho
Joined: Mar 2000
Posts: 25,587
Texas
Cool, thanks for sharing the fix thumbsup


- Allen wavey
- What Drives You?

Donate Today!
Donate via PayPal

Donate to UBBDev today to help aid in Operational, Server and Script Maintenance, and Development costs.

Please also see our parent organization VNC Web Services if you're in the need of a new UBB.threads Install or Upgrade, Site/Server Migrations, or Security and Coding Services.
Recommended Hosts
We have personally worked with, and recommend, the following Web Hosts:
· Stable Host
· Blue Host
· Interserver.net
Visit Us on Facebook
Member Spotlight
isaac
isaac
California
Posts: 1,170
Joined: July 2001
Show All Member Profiles 
Forum Statistics
Forums64
Topics37,448
Posts293,484
Members13,793
Most Online1,498
Mar 17th, 2017
Top Posters(All Time)
AllenAyres 25,587
JoshPet 11,330
Rick 8,373
LK 7,396
Lord Dexter 6,503
Gizmo 5,938
Greg Hard 5,533
Top Posters(30 Days)
isaac 4
Today's Statistics
Currently Online 724
Topics Created 0
Posts Made 0
Users Online 0
Birthdays 19
The UBB.Developers Network (UBB.Dev/Threads.Dev) is ©2000-2018 VNC Web Services

 
Powered by UBB.threads™ PHP Forum Software 7.6.2
(Preview build 20180611.dev)
Page Time: 0.139s Queries: 15 (0.111s) Memory: 3.2759 MB (Peak: 3.4376 MB) Zlib enabled. Server Time: 2018-06-19 20:28:00 UTC