Ultra slow mySQL dump import [windows]

You are not logged in. [Log In] ubbDev » Forums » Community Development » HTML, CSS, Programming and MySQL
» MySQL and Server Configuration » Ultra slow mySQL dump import [windows]

Topic Options

#311498 - 09/13/06 09:42 AM

Ultra slow mySQL dump import [windows]

PaNTerSan Offline

Journeyman

Registered: 03/19/04
Posts: 122
Loc: 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

Top

#311504 - 09/13/06 01:28 PM Re: Ultra slow mySQL dump import [windows] [Re: PaNTerSan]
AllenAyres I type Like navaho Registered: 03/10/00 Posts: 25448 Loc: 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?
Top

#311513 - 09/14/06 03:34 AM Re: Ultra slow mySQL dump import [windows] [Re: AllenAyres]
PaNTerSan Journeyman Registered: 03/19/04 Posts: 122 Loc: 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
Top

#311519 - 09/14/06 05:41 PM Re: Ultra slow mySQL dump import [windows] [Re: PaNTerSan]
AllenAyres I type Like navaho Registered: 03/10/00 Posts: 25448 Loc: 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?
Top

#311520 - 09/15/06 03:26 AM Re: Ultra slow mySQL dump import [windows] [Re: AllenAyres]
PaNTerSan Journeyman Registered: 03/19/04 Posts: 122 Loc: EU, Slovenia	To make things worse - this is happening on two different machines. _________________________ Kind regards, PaNTerSan
Top

#311529 - 09/17/06 02:07 PM Re: Ultra slow mySQL dump import [windows] [Re: PaNTerSan]
PaNTerSan Journeyman Registered: 03/19/04 Posts: 122 Loc: 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
Top

#311541 - 09/19/06 05:27 PM Re: Ultra slow mySQL dump import [windows] [Re: PaNTerSan]
AllenAyres I type Like navaho Registered: 03/10/00 Posts: 25448 Loc: Texas	Cool, thanks for sharing the fix _________________________ - Allen - What Drives You?
Top

Previous Topic

Index

Next Topic

Hop to: