UBB.Dev
Posted By: PaNTerSan Ultra slow mySQL dump import [windows] - 09/13/2006 4:42 PM
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 |
+--------------------------+---------+
Posted By: AllenAyres Re: Ultra slow mySQL dump import [windows] - 09/13/2006 8:28 PM
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.
Posted By: PaNTerSan Re: Ultra slow mySQL dump import [windows] - 09/14/2006 10:34 AM
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?
Posted By: AllenAyres Re: Ultra slow mySQL dump import [windows] - 09/15/2006 12:41 AM
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
Posted By: PaNTerSan Re: Ultra slow mySQL dump import [windows] - 09/15/2006 10:26 AM
To make things worse - this is happening on two different machines.
Posted By: PaNTerSan Re: Ultra slow mySQL dump import [windows] - 09/17/2006 9:07 PM
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
Posted By: AllenAyres Re: Ultra slow mySQL dump import [windows] - 09/20/2006 12:27 AM
Cool, thanks for sharing the fix thumbsup
© UBB.Developers