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=0
datadir=/var/lib/mysql
socket=/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=1
skip-locking
query_cache_type=1
query_cache_limit=1M
query_cache_size=32M
max_connections=200
interactive_timeout=100
wait_timeout=15
connect_timeout=10
set-variable = key_buffer=128M
set-variable = max_allowed_packet=1M
set-variable = table_cache=512
set-variable = sort_buffer=1M
set-variable = record_buffer=1M
set-variable = myisam_sort_buffer_size=64M
set-variable = thread_cache=8
# Try number of CPU's*2 for thread_concurrency
set-variable = thread_concurrency=2
log-bin
server-id = 1
sort_buffer_size = 16M
read_buffer_size = 16M
read_rnd_buffer_size = 16M
log_slow_queries=/var/log/mysql.slow.log
long_query_time=10
default-character-set=latin5
default-collation=latin5_turkish_ci
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit=8192
[mysqldump]
quick
set-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=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=16M
set-variable = write_buffer=16M
[myisamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=16M
set-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.31
Write 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.00
Slow 0 0/s 0.00 %DMS: 0.00
DMS 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.00
Com_ 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.50
Range 265.70k 19.9/s 2.85
Full join 0 0/s 0.00
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 21.66k 1.6/s
Sort range 18.81k 1.4/s
Sort mrg pass 0 0/s
__ Query Cache _________________________________________________________
Memory usage 13.58M of 32.00M %Used: 42.44
Block Fragmnt 19.46%
Hits 16.06M 1.2k/s
Inserts 4.56M 341.9/s
Insrt:Prune 23.49:1 327.3/s
Hit:Insert 3.52:1
__ Table Locks _________________________________________________________
Waited 2.76M 207.3/s %Total: 27.72
Immediate 7.21M 540.5/s
__ Tables ______________________________________________________________
Open 348 of 512 %Cache: 67.97
Opened 781 0.1/s
__ Connections _________________________________________________________
Max used 72 of 200 %Max: 36.00
Total 166.10k 12.5/s
__ Created Temp ________________________________________________________
Disk table 2 0.0/s
Table 11.38k 0.9/s
File 5 0.0/s
__ Threads _____________________________________________________________
Running 38 of 47
Cached 1 of 8 %Hit: 92.24
Created 12.88k 1.0/s
Slow 0 0/s
__ Aborted _____________________________________________________________
Clients 120 0.0/s
Connects 7 0.0/s
__ Bytes _______________________________________________________________
Sent 1.68G 125.7k/s
Received 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
Members
Search
Help
Register
Login
Home
