Hi, i've got a problem with my server because some times per day, something occurs and server load average grows until 20 due to mysql. When it occurs, with "show processlist", I can see a lot of queued queries in "locked" state ( more than 100 queued). You can see the load average at http://cluster2.genteya.com/load.png ( notice these peaks, 4 o 5 peaks per day).
I know a query get into locked state when the table that it's trying to access is locked, but the queries that are in locked state are related to different tables, so it means that all tables are locked when it occurs. I know too that a table is auto-locked when an update or insert is being done, but when all those queries are queued in locked state i can't see any insert/update that affect to a bunch of rows, my updates/inserts always affects to a single row, then i've no idea of what could cause this situation. System is Dual XEON 2.7Ghz ( 4processors), 2Gb RAM and SCSI HDD. Tables are myisam and above you have the info from mysql config and status. I'm waiting hopeful for some help. Thanks ! Ivan L. my.cnf: # The MySQL server [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking skip-innodb datadir=/disco3/mysql/data #datadir=/usr/local/mysql/data set-variable=long_query_time=1 log-slow-queries = /var/log/mysqld_low.log set-variable=max_connections=2000 key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 10M #2M read_buffer_size = 8M #2M read_rnd_buffer_size = 15M #8M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 50M query_cache_type = 1 # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 mysql> show status; +----------------------------+------------+ | Variable_name | Value | +----------------------------+------------+ | Aborted_clients | 44 | | Aborted_connects | 6 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Bytes_received | 512828130 | | Bytes_sent | 1759373754 | | 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 | 228518 | | 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 | 1 | | Com_dealloc_sql | 0 | | Com_delete | 64244 | | 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 | 638593 | | Com_insert_select | 3854 | | Com_kill | 0 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table | 0 | | Com_lock_tables | 0 | | Com_optimize | 46 | | Com_preload_keys | 0 | | Com_prepare_sql | 0 | | Com_purge | 0 | | Com_purge_before_date | 0 | | Com_rename_table | 0 | | Com_repair | 46 | | Com_replace | 41040 | | 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 | 702124 | | Com_set_option | 140 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 1 | | Com_show_charsets | 35 | | Com_show_collations | 35 | | Com_show_column_types | 0 | | Com_show_create_db | 2 | | Com_show_create_table | 4 | | Com_show_databases | 14 | | Com_show_errors | 0 | | Com_show_fields | 24 | | Com_show_grants | 1 | | Com_show_innodb_status | 0 | | Com_show_keys | 18 | | 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 | 20 | | Com_show_slave_hosts | 0 | | Com_show_slave_status | 0 | | Com_show_status | 4 | | Com_show_storage_engines | 0 | | Com_show_tables | 135 | | Com_show_variables | 141 | | Com_show_warnings | 0 | | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_stmt_close | 0 | | Com_stmt_execute | 0 | | Com_stmt_prepare | 0 | | Com_stmt_reset | 0 | | Com_stmt_send_long_data | 0 | | Com_truncate | 0 | | Com_unlock_tables | 0 | | Com_update | 1212661 | | Com_update_multi | 0 | | Connections | 228566 | | Created_tmp_disk_tables | 9372 | | Created_tmp_files | 3 | | Created_tmp_tables | 43499 | | Delayed_errors | 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Flush_commands | 1 | | Handler_commit | 0 | | Handler_delete | 98550 | | Handler_discover | 0 | | Handler_read_first | 248056 | | Handler_read_key | 81039775 | | Handler_read_next | 60400833 | | Handler_read_prev | 1263111078 | | Handler_read_rnd | 3814667 | | Handler_read_rnd_next | 1929229841 | | Handler_rollback | 0 | | Handler_update | 2039252 | | Handler_write | 52757488 | | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 313413 | | Key_blocks_used | 147888 | | Key_read_requests | 367172022 | | Key_reads | 762652 | | Key_write_requests | 1186788 | | Key_writes | 943102 | | Max_used_connections | 329 | | Not_flushed_delayed_rows | 0 | | Open_files | 263 | | Open_streams | 0 | | Open_tables | 195 | | Opened_tables | 3198 | | Qcache_free_blocks | 874 | | Qcache_free_memory | 50596440 | | Qcache_hits | 605875 | | Qcache_inserts | 694412 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 7725 | | Qcache_queries_in_cache | 1642 | | Qcache_total_blocks | 4187 | | Questions | 3726220 | | Rpl_status | NULL | | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 17405 | | Select_range_check | 0 | | Select_scan | 47833 | | Slave_open_temp_tables | 0 | | Slave_retried_transactions | 0 | | Slave_running | OFF | | Slow_launch_threads | 0 | | Slow_queries | 4203 | | Sort_merge_passes | 0 | | Sort_range | 55649 | | Sort_rows | 1244821211 | | Sort_scan | 51616 | | Table_locks_immediate | 2918486 | | Table_locks_waited | 89114 | | Threads_cached | 4 | | Threads_connected | 6 | | Threads_created | 16985 | | Threads_running | 1 | | Uptime | 30042 | +----------------------------+------------+ 163 rows in set (0.00 sec)
