Ultra slow mySQL dump import [windows]

Print Thread

Rate Thread

#311498 09/13/2006 9:42 AM

Joined: Mar 2004

Posts: 118

EU, Slovenia

PaNTerSan

Journeyman

PaNTerSan

Journeyman

Joined: Mar 2004

Posts: 118

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

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] PaNTerSan #311504 09/13/2006 1:28 PM
Joined: Mar 2000 Posts: 21,079 Likes: 3 Texas AllenAyres I type Like navaho
AllenAyres I type Like navaho Joined: Mar 2000 Posts: 21,079 Likes: 3 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 - What Drives You?

Re: Ultra slow mySQL dump import [windows] AllenAyres #311513 09/14/2006 3:34 AM
Joined: Mar 2004 Posts: 118 EU, Slovenia PaNTerSan Journeyman
PaNTerSan Journeyman Joined: Mar 2004 Posts: 118 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 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] PaNTerSan #311519 09/14/2006 5:41 PM
Joined: Mar 2000 Posts: 21,079 Likes: 3 Texas AllenAyres I type Like navaho
AllenAyres I type Like navaho Joined: Mar 2000 Posts: 21,079 Likes: 3 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 - Allen - What Drives You?

Re: Ultra slow mySQL dump import [windows] AllenAyres #311520 09/15/2006 3:26 AM
Joined: Mar 2004 Posts: 118 EU, Slovenia PaNTerSan Journeyman
PaNTerSan Journeyman Joined: Mar 2004 Posts: 118 EU, Slovenia	To make things worse - this is happening on two different machines. Kind regards, PaNTerSan

UBB.Developers Forums Community Development Programming MySQL and Server Configuration Ultra slow mySQL dump import [windows]