Previous Thread
Next Thread
Print Thread
Rate Thread
Joined: Mar 2004
Posts: 118
Journeyman
Journeyman
Offline
Joined: Mar 2004
Posts: 118
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
Joined: Mar 2000
Posts: 21,079
Likes: 3
I type Like navaho
I type Like navaho
Joined: Mar 2000
Posts: 21,079
Likes: 3
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?
Joined: Mar 2004
Posts: 118
Journeyman
Journeyman
Offline
Joined: Mar 2004
Posts: 118
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
Joined: Mar 2000
Posts: 21,079
Likes: 3
I type Like navaho
I type Like navaho
Joined: Mar 2000
Posts: 21,079
Likes: 3
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?
Joined: Mar 2004
Posts: 118
Journeyman
Journeyman
Offline
Joined: Mar 2004
Posts: 118
To make things worse - this is happening on two different machines.


Kind regards,
PaNTerSan
Sponsored Links
Joined: Mar 2004
Posts: 118
Journeyman
Journeyman
Offline
Joined: Mar 2004
Posts: 118
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
Joined: Mar 2000
Posts: 21,079
Likes: 3
I type Like navaho
I type Like navaho
Joined: Mar 2000
Posts: 21,079
Likes: 3
Cool, thanks for sharing the fix thumbsup


- Allen wavey
- What Drives You?

Link Copied to Clipboard
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
bluehost
InterServer
Visit us on Facebook
Member Spotlight
Nettomo
Nettomo
Germany, Bremen
Posts: 417
Joined: November 2001
Forum Statistics
Forums63
Topics37,573
Posts293,925
Members13,849
Most Online5,166
Sep 15th, 2019
Today's Statistics
Currently Online
Topics Created
Posts Made
Users Online
Birthdays
Top Posters
AllenAyres 21,079
JoshPet 10,369
LK 7,394
Lord Dexter 6,708
Gizmo 5,833
Greg Hard 4,625
Top Posters(30 Days)
Top Likes Received
isaac 82
Gizmo 20
Brett 7
WebGuy 2
Morgan 2
Top Likes Received (30 Days)
None yet
The UBB.Developers Network (UBB.Dev/Threads.Dev) is ©2000-2024 VNC Web Services

 
Powered by UBB.threads™ PHP Forum Software 8.0.0
(Preview build 20240506)