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: 118
EU, Slovenia
P
PaNTerSan Offline OP
Journeyman
PaNTerSan  Offline OP
Journeyman
P

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 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: 21,084
Texas
AllenAyres Offline
I type Like navaho
AllenAyres  Offline
I type Like navaho

Joined: Mar 2000
Posts: 21,084
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: 118
EU, Slovenia
P
PaNTerSan Offline OP
Journeyman
PaNTerSan  Offline OP
Journeyman
P

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 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: 21,084
Texas
AllenAyres Offline
I type Like navaho
AllenAyres  Offline
I type Like navaho

Joined: Mar 2000
Posts: 21,084
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: 118
EU, Slovenia
P
PaNTerSan Offline OP
Journeyman
PaNTerSan  Offline OP
Journeyman
P

Joined: Mar 2004
Posts: 118
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: 118
EU, Slovenia
P
PaNTerSan Offline OP
Journeyman
PaNTerSan  Offline OP
Journeyman
P

Joined: Mar 2004
Posts: 118
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: 21,084
Texas
AllenAyres Offline
I type Like navaho
AllenAyres  Offline
I type Like navaho

Joined: Mar 2000
Posts: 21,084
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
Gizmo
Gizmo
Portland, OR, USA
Posts: 5,779
Joined: January 2000
Show All Member Profiles 
Forum Statistics
Forums64
Topics37,482
Posts293,586
Members13,811
Most Online1,498
Mar 17th, 2017
Top Posters(All Time)
AllenAyres 21,084
JoshPet 10,370
LK 7,395
Lord Dexter 6,709
Gizmo 5,779
Greg Hard 4,625
Top Posters(30 Days)
driv 19
isaac 17
Gizmo 6
Today's Statistics
Currently Online 908
Topics Created 0
Posts Made 0
Users Online 2
Birthdays 19
The UBB.Developers Network (UBB.Dev/Threads.Dev) is ©2000-2019 VNC Web Services

 
Powered by UBB.threads™ PHP Forum Software 7.7.2
(Snapshot build 20190304.dev)
PHP: 5.4.45 Page Time: 0.038s Queries: 15 (0.009s) Memory: 3.2306 MB (Peak: 3.4190 MB) Data Comp: Zlib Server Time: 2019-03-20 09:51:09 UTC