GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

MySQL Performance Help (Really stuck here..)

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL Performance Help (Really stuck here..)

    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_time out=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

  • #2
    Very good informative post!
    Yes it was long but you got all the important information.

    The problem you are having is that you have a _lot_ of SELECT's and a few UPDATEs and that MyISAM is using _table_ level locking.
    When it comes to locking the rules are:
    1. You can have a lot of read locks (SELECT) at the same time.
    2. But you can _ONLY_ have _ONE_ write lock at a time.

    So if whenever you issue an UPDATE/DELETE or INSERT it means that there must exist a single write lock on the table.
    While a lot of SELECT's can be performed in parallell.

    My guess is that one of the UPDATEs/DELETEs could take time to execute.
    Do all the updates have proper indexes?

    The only other solution for you is to convert your tables to InnoDB (since it is using row level locking instead) but I think that you will need to tweak it a bit to get the speed you are after.

    Comment

    Working...
    X