Hello people;
I have a virtual dedicated server from godaddy with 512mb guaranteed ram. I am running a web based online strategy game which have generally 250-300 online users. My problem is, suddenly (when everything was going perfect) server started being real slow. No config and code changed but suddenly it happened and now my server is really slow. Code is really good optimized and was working perfect till that day. I have researched and worked with a lot of my.cnf configs but nothing changed really.
I am using mysql5.1 (deault install with plesk 8.1) and apache. I am on fedora core 6.
SHOW STATUS;
±----------------------------------±---------+| Variable_name | Value |±----------------------------------±---------+| Aborted_clients | 118 || Aborted_connects | 7 || Binlog_cache_disk_use | 0 || Binlog_cache_use | 15 || Bytes_received | 101 || Bytes_sent | 76 || Com_admin_commands | 0 || Com_alter_db | 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_checksum | 0 || Com_commit | 0 || Com_create_db | 0 || Com_create_function | 0 || Com_create_index | 0 || Com_create_table | 0 || Com_dealloc_sql | 0 || Com_delete | 0 || Com_delete_multi | 0 || Com_do | 0 || Com_drop_db | 0 || Com_drop_function | 0 || Com_drop_index | 0 || Com_drop_table | 0 || Com_drop_user | 0 || Com_execute_sql | 0 || Com_flush | 0 || Com_grant | 0 || Com_ha_close | 0 || Com_ha_open | 0 || Com_ha_read | 0 || Com_help | 0 || Com_insert | 0 || 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_preload_keys | 0 || Com_prepare_sql | 0 || Com_purge | 0 || Com_purge_before_date | 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_revoke_all | 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_charsets | 0 || Com_show_collations | 0 || Com_show_column_types | 0 || Com_show_create_db | 0 || Com_show_create_table | 0 || Com_show_databases | 0 || Com_show_errors | 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_ndb_status | 0 || Com_show_new_master | 0 || Com_show_open_tables | 0 || Com_show_privileges | 0 || Com_show_processlist | 0 || Com_show_slave_hosts | 0 || Com_show_slave_status | 0 || Com_show_status | 1 || Com_show_storage_engines | 0 || Com_show_tables | 0 || Com_show_triggers | 0 || Com_show_variables | 0 || Com_show_warnings | 0 || Com_slave_start | 0 || Com_slave_stop | 0 || Com_stmt_close | 0 || Com_stmt_execute | 0 || Com_stmt_fetch | 0 || Com_stmt_prepare | 0 || Com_stmt_reset | 0 || Com_stmt_send_long_data | 0 || Com_truncate | 0 || Com_unlock_tables | 0 || Com_update | 0 || Com_update_multi | 0 || Com_xa_commit | 0 || Com_xa_end | 0 || Com_xa_prepare | 0 || Com_xa_recover | 0 || Com_xa_rollback | 0 || Com_xa_start | 0 || Compression | OFF || Connections | 163600 || Created_tmp_disk_tables | 0 || Created_tmp_files | 5 || Created_tmp_tables | 1 || Delayed_errors | 0 || Delayed_insert_threads | 0 || Delayed_writes | 0 || Flush_commands | 1 || Handler_commit | 0 || Handler_delete | 0 || Handler_discover | 0 || Handler_prepare | 0 || Handler_read_first | 0 || Handler_read_key | 0 || Handler_read_next | 0 || Handler_read_prev | 0 || Handler_read_rnd | 0 || Handler_read_rnd_next | 0 || Handler_rollback | 0 || Handler_savepoint | 0 || Handler_savepoint_rollback | 0 || Handler_update | 0 || Handler_write | 130 || Innodb_buffer_pool_pages_data | 81 || Innodb_buffer_pool_pages_dirty | 0 || Innodb_buffer_pool_pages_flushed | 1 || Innodb_buffer_pool_pages_free | 431 || Innodb_buffer_pool_pages_latched | 0 || Innodb_buffer_pool_pages_misc | 0 || Innodb_buffer_pool_pages_total | 512 || Innodb_buffer_pool_read_ahead_rnd | 3 || Innodb_buffer_pool_read_ahead_seq | 0 || Innodb_buffer_pool_read_requests | 3253 || Innodb_buffer_pool_reads | 56 || Innodb_buffer_pool_wait_free | 0 || Innodb_buffer_pool_write_requests | 1 || Innodb_data_fsyncs | 7 || Innodb_data_pending_fsyncs | 0 || Innodb_data_pending_reads | 0 || Innodb_data_pending_writes | 0 || Innodb_data_read | 3510272 || Innodb_data_reads | 73 || Innodb_data_writes | 7 || Innodb_data_written | 35328 || Innodb_dblwr_pages_written | 1 || Innodb_dblwr_writes | 1 || Innodb_log_waits | 0 || Innodb_log_write_requests | 0 || Innodb_log_writes | 2 || Innodb_os_log_fsyncs | 5 || Innodb_os_log_pending_fsyncs | 0 || Innodb_os_log_pending_writes | 0 || Innodb_os_log_written | 1024 || Innodb_page_size | 16384 || Innodb_pages_created | 0 || Innodb_pages_read | 81 || Innodb_pages_written | 1 || Innodb_row_lock_current_waits | 0 || Innodb_row_lock_time | 0 || Innodb_row_lock_time_avg | 0 || Innodb_row_lock_time_max | 0 || Innodb_row_lock_waits | 0 || Innodb_rows_deleted | 0 || Innodb_rows_inserted | 0 || Innodb_rows_read | 118 || Innodb_rows_updated | 0 || Key_blocks_not_flushed | 0 || Key_blocks_unused | 112945 || Key_blocks_used | 5535 || Key_read_requests | 42987471 || Key_reads | 12991 || Key_write_requests | 217478 || Key_writes | 181150 || Last_query_cost | 0.000000 || Max_used_connections | 72 || Not_flushed_delayed_rows | 0 || Open_files | 409 || Open_streams | 0 || Open_tables | 334 || Opened_tables | 0 || Qcache_free_blocks | 1980 || Qcache_free_memory | 22482080 || Qcache_hits | 15817170 || Qcache_inserts | 4483010 || Qcache_lowmem_prunes | 193670 || Qcache_not_cached | 4696046 || Qcache_queries_in_cache | 8816 || Qcache_total_blocks | 19725 || Questions | 26113763 || Rpl_status | NULL || Select_full_join | 0 || Select_full_range_join | 0 || Select_range | 0 || Select_range_check | 0 || Select_scan | 1 || Slave_open_temp_tables | 0 || Slave_retried_transactions | 0 || Slave_running | OFF || Slow_launch_threads | 0 || Slow_queries | 0 || Sort_merge_passes | 0 || Sort_range | 0 || Sort_rows | 0 || Sort_scan | 0 || Ssl_accept_renegotiates | 0 || Ssl_accepts | 0 || Ssl_callback_cache_hits | 0 || Ssl_cipher | || Ssl_cipher_list | || Ssl_client_connects | 0 || Ssl_connect_renegotiates | 0 || Ssl_ctx_verify_depth | 0 || Ssl_ctx_verify_mode | 0 || Ssl_default_timeout | 0 || Ssl_finished_accepts | 0 || Ssl_finished_connects | 0 || Ssl_session_cache_hits | 0 || Ssl_session_cache_misses | 0 || Ssl_session_cache_mode | NONE || Ssl_session_cache_overflows | 0 || Ssl_session_cache_size | 0 || Ssl_session_cache_timeouts | 0 || Ssl_sessions_reused | 0 || Ssl_used_session_cache_entries | 0 || Ssl_verify_depth | 0 || Ssl_verify_mode | 0 || Ssl_version | || Table_locks_immediate | 7088431 || Table_locks_waited | 2714074 || Tc_log_max_pages_used | 0 || Tc_log_page_size | 0 || Tc_log_page_waits | 0 || Threads_cached | 3 || Threads_connected | 55 || Threads_created | 12662 || Threads_running | 43 || Uptime | 13143 |±----------------------------------±---------+
As you can see already, it seems the problem lies within the table_locks_waited. But I worked a lot of config changes and nothing really changed. By the way my tables are all MyIsam.
Here is my.cnf
[mysqld]set-variable=local-infile=0datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock# Default to using old password format for compatibility with mysql 3.x# clients (those using the mysqlclient10 compatibility package).old_passwords=1skip-lockingquery_cache_type=1query_cache_limit=1Mquery_cache_size=32Mmax_connections=200interactive_timeout=100wait_timeout=15connect_timeout=10set-variable = key_buffer=128Mset-variable = max_allowed_packet=1Mset-variable = table_cache=512set-variable = sort_buffer=1Mset-variable = record_buffer=1Mset-variable = myisam_sort_buffer_size=64Mset-variable = thread_cache=8# Try number of CPU’s*2 for thread_concurrencyset-variable = thread_concurrency=2log-binserver-id = 1sort_buffer_size = 16Mread_buffer_size = 16Mread_rnd_buffer_size = 16Mlog_slow_queries=/var/log/mysql.slow.loglong_query_time=10default-character-set=latin5default-collation=latin5_turkish_ci[mysql.server]user=mysqlbasedir=/var/lib[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidopen_files_limit=8192[mysqldump]quickset-variable = max_allowed_packet=16M[mysql]no-auto-rehash# Remove the next comment character if you are not familiar with SQL#safe-updates[isamchk]set-variable = key_buffer=64Mset-variable = sort_buffer=64Mset-variable = read_buffer=16Mset-variable = write_buffer=16M[myisamchk]set-variable = key_buffer=64Mset-variable = sort_buffer=64Mset-variable = read_buffer=16Mset-variable = write_buffer=16M[mysqlhotcopy]interactive-timeout
And the result from mysqlreport (3rd party addon that inspects show status)
MySQL 5.0.27-log uptime 0 3:42:11 Tue Jun 12 11:08:18 2007__ Key _______________________________________________________________Buffer used 5.41M of 128.00M %Used: 4.22 Current 18.31M %Usage: 14.31Write hit 18.76%Read hit 99.97% Questions _______________________________________________________Total 26.53M 2.0k/s QC Hits 16.06M 1.2k/s %Total: 60.54 DMS 9.97M 747.8/s 37.57 Com 335.06k 25.1/s 1.26 COM_QUIT 166.04k 12.5/s 0.63 -Unknown 25 0.0/s 0.00Slow 0 0/s 0.00 %DMS: 0.00DMS 9.97M 747.8/s 37.57 SELECT 9.33M 700.1/s 35.18 93.62 UPDATE 523.97k 39.3/s 1.97 5.26 DELETE 69.04k 5.2/s 0.26 0.69 INSERT 42.78k 3.2/s 0.16 0.43 REPLACE 0 0/s 0.00 0.00Com 335.06k 25.1/s 1.26 set_option 167.55k 12.6/s 0.63 change_db 167.34k 12.6/s 0.63 show_variab 116 0.0/s 0.00 SELECT and Sort ___________________________________________________Scan 46.69k 3.5/s %SELECT: 0.50Range 265.70k 19.9/s 2.85Full join 0 0/s 0.00Range check 0 0/s 0.00Full rng join 0 0/s 0.00Sort scan 21.66k 1.6/sSort range 18.81k 1.4/sSort mrg pass 0 0/s Query Cache _______________________________________________________Memory usage 13.58M of 32.00M %Used: 42.44Block Fragmnt 19.46%Hits 16.06M 1.2k/sInserts 4.56M 341.9/sInsrt:Prune 23.49:1 327.3/sHit:Insert 3.52:1 Table Locks _______________________________________________________Waited 2.76M 207.3/s %Total: 27.72Immediate 7.21M 540.5/s Tables ____________________________________________________________Open 348 of 512 %Cache: 67.97Opened 781 0.1/s Connections _______________________________________________________Max used 72 of 200 %Max: 36.00Total 166.10k 12.5/s Created Temp ______________________________________________________Disk table 2 0.0/sTable 11.38k 0.9/sFile 5 0.0/s Threads ___________________________________________________________Running 38 of 47Cached 1 of 8 %Hit: 92.24Created 12.88k 1.0/sSlow 0 0/s Aborted ___________________________________________________________Clients 120 0.0/sConnects 7 0.0/s Bytes _______________________________________________________________Sent 1.68G 125.7k/sReceived 1.72G 129.2k/s
I am pretty sure the hardware upgrade won’t change anything because the same setup, same code and the same amount of onlien users was running very smooth for 6 months.
Here is the cat /proc/user_beancounters in case you need
Version: 2.5 uid resource held maxheld barrier limit failcnt 4030: kmemsize 24833252 24841444 33925283 37317811 0 lockedpages 0 0 1400 1400 0 privvmpages 196620 196878 524288 524288 0 shmpages 5944 5944 131072 131072 0 dummy 0 0 0 0 0 numproc 190 190 1024 1024 0 physpages 76451 76461 0 2147483647 0 vmguarpages 0 0 128000 2147483647 0 oomguarpages 76451 76461 128000 2147483647 0 numtcpsock 260 264 820 820 0 numflock 88 95 1024 1024 0 numpty 1 1 64 64 0 numsiginfo 0 1 1024 1024 0 tcpsndbuf 987712 1173616 7916940 11308428 0 tcprcvbuf 1042144 1078600 7916940 11308428 0 othersockbuf 91912 111200 3958470 7349958 0 dgramrcvbuf 0 0 3958470 3958470 0 numothersock 112 120 820 820 0 dcachesize 0 0 7408590 7630848 0 numfile 4155 4172 10240 10240 0 dummy 0 0 0 0 0 dummy 0 0 0 0 0 dummy 0 0 0 0 0 numiptent 500 500 500 500 1674
My table indexes are set up good and tables are optimized good. Some tables like user table (holding user info like gold, food, population, land etc) is very busy with reads and updates. Nearly all tables are connected to themselves with user field and in all tables user fields are set as indexes.
Sorry for the long and detailed post but I am really stuck here. I will be very appreciated if anyone have any suggestions and solutions for my problem.
Thanks
Ilkan