We are using MySql as the backend to our Web (Apache) based Pool website. Over the last 6-9 months the MySql process has become busier and busier, to the point now where we see definative slowdowns to the Application, as experienced by users - in terms of latency.
Using TOP, I can see that mysql process uses up to 100% for extended periods of time, sometimes as long as 20 mins. This only appears to occur at busy times of the day, when we have over 200 concurrent logins to the site, however the network stats dont show any greater usage of the network writes/reads. Of all the process's, mysql is always the busiest. The server never uses all the Memory, at least 10% always free, and there's loads of free disk space. Just the CPU max's out, causing problems. I've posted below the output of STATUS and SHOW GLOBAL STATUS. Any and all comments would be much appreciated as to how we can get performance back on track. Thanks. Here's the output; mysql> mysql> STATUS -------------- mysql Ver 14.12 Distrib 5.0.27, for pc-linux-gnu (i686) using readline 5.0 Connection id: 420806 Current database: Current user: [EMAIL PROTECTED] SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.0.27-standard-log Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 3 days 13 hours 10 min 8 sec Threads: 9 Questions: 5026658 Slow queries: 0 Opens: 7045 Flush tables: 1 Open tables: 511 Queries per second avg: 16.394 -------------- mysql> SHOW GLOBAL STATUS; +-----------------------------------+------------+ | Variable_name | Value | +-----------------------------------+------------+ | Aborted_clients | 2 | | Aborted_connects | 348 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Bytes_received | 579199175 | | Bytes_sent | 1753363624 | | Com_admin_commands | 52 | | Com_alter_db | 0 | | Com_alter_table | 0 | | Com_analyze | 4 | | Com_backup_table | 0 | | Com_begin | 53 | | Com_change_db | 525106 | | Com_change_master | 0 | | Com_check | 4 | | Com_checksum | 0 | | Com_commit | 53 | | Com_create_db | 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_table | 50 | | Com_dealloc_sql | 0 | | Com_delete | 67759 | | Com_delete_multi | 0 | | Com_do | 0 | | Com_drop_db | 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_table | 50 | | 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 | 315231 | | Com_insert_select | 50 | | Com_kill | 0 | | Com_load | 8 | | Com_load_master_data | 0 | | Com_load_master_table | 0 | | Com_lock_tables | 865 | | Com_optimize | 52 | | Com_preload_keys | 0 | | Com_prepare_sql | 0 | | Com_purge | 0 | | Com_purge_before_date | 0 | | Com_rename_table | 0 | | Com_repair | 4 | | Com_replace | 1553 | | 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 | 1607907 | | Com_set_option | 5241 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 5 | | Com_show_charsets | 44 | | Com_show_collations | 44 | | Com_show_column_types | 0 | | Com_show_create_db | 0 | | Com_show_create_table | 148 | | Com_show_databases | 44 | | Com_show_errors | 0 | | Com_show_fields | 99 | | Com_show_grants | 16 | | Com_show_innodb_status | 1 | | Com_show_keys | 9 | | 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 | 1048 | | Com_show_slave_hosts | 0 | | Com_show_slave_status | 0 | | Com_show_status | 35 | | Com_show_storage_engines | 0 | | Com_show_tables | 101330 | | Com_show_triggers | 0 | | Com_show_variables | 132 | | 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 | 50 | | Com_unlock_tables | 865 | | Com_update | 773385 | | Com_update_multi | 616 | | 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 | 427633 | | Created_tmp_disk_tables | 159267 | | Created_tmp_files | 4085 | | Created_tmp_tables | 262915 | | Delayed_errors | 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Flush_commands | 1 | | Handler_commit | 106 | | Handler_delete | 307752 | | Handler_discover | 0 | | Handler_prepare | 0 | | Handler_read_first | 39490 | | Handler_read_key | 4265131 | | Handler_read_next | 5662509 | | Handler_read_prev | 116763 | | Handler_read_rnd | 270199887 | | Handler_read_rnd_next | 3334259467 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 568374 | | Handler_write | 448210176 | | Innodb_buffer_pool_pages_data | 47 | | Innodb_buffer_pool_pages_dirty | 0 | | Innodb_buffer_pool_pages_flushed | 161 | | Innodb_buffer_pool_pages_free | 464 | | Innodb_buffer_pool_pages_latched | 0 | | Innodb_buffer_pool_pages_misc | 1 | | Innodb_buffer_pool_pages_total | 512 | | Innodb_buffer_pool_read_ahead_rnd | 1 | | Innodb_buffer_pool_read_ahead_seq | 0 | | Innodb_buffer_pool_read_requests | 4061 | | Innodb_buffer_pool_reads | 22 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 816 | | Innodb_data_fsyncs | 119 | | Innodb_data_pending_fsyncs | 0 | | Innodb_data_pending_reads | 0 | | Innodb_data_pending_writes | 0 | | Innodb_data_read | 2658304 | | Innodb_data_reads | 35 | | Innodb_data_writes | 188 | | Innodb_data_written | 5897728 | | Innodb_dblwr_pages_written | 161 | | Innodb_dblwr_writes | 21 | | Innodb_log_waits | 0 | | Innodb_log_write_requests | 1193 | | Innodb_log_writes | 58 | | Innodb_os_log_fsyncs | 77 | | Innodb_os_log_pending_fsyncs | 0 | | Innodb_os_log_pending_writes | 0 | | Innodb_os_log_written | 612352 | | Innodb_page_size | 16384 | | Innodb_pages_created | 18 | | Innodb_pages_read | 29 | | Innodb_pages_written | 161 | | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 111 | | Innodb_row_lock_time_avg | 22 | | Innodb_row_lock_time_max | 34 | | Innodb_row_lock_waits | 5 | | Innodb_rows_deleted | 0 | | Innodb_rows_inserted | 2 | | Innodb_rows_read | 104 | | Innodb_rows_updated | 51 | | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 102916 | | Key_blocks_used | 25592 | | Key_read_requests | 2583581110 | | Key_reads | 5046625 | | Key_write_requests | 486341748 | | Key_writes | 433283 | | Last_query_cost | 0.000000 | | Max_used_connections | 78 | | Not_flushed_delayed_rows | 0 | | Open_files | 892 | | Open_streams | 0 | | Open_tables | 511 | | Opened_tables | 7045 | | Qcache_free_blocks | 1533 | | Qcache_free_memory | 3624048 | | Qcache_hits | 1198322 | | Qcache_inserts | 1446965 | | Qcache_lowmem_prunes | 22914 | | Qcache_not_cached | 262103 | | Qcache_queries_in_cache | 2117 | | Qcache_total_blocks | 9264 | | Questions | 5027678 | | Rpl_status | NULL | | Select_full_join | 700 | | Select_full_range_join | 0 | | Select_range | 32618 | | Select_range_check | 0 | | Select_scan | 1044606 | | Slave_open_temp_tables | 0 | | Slave_retried_transactions | 0 | | Slave_running | OFF | | Slow_launch_threads | 0 | | Slow_queries | 0 | | Sort_merge_passes | 2040 | | Sort_range | 14306 | | Sort_rows | 277838805 | | Sort_scan | 210645 | | Table_locks_immediate | 2850044 | | Table_locks_waited | 16597 | | Tc_log_max_pages_used | 0 | | Tc_log_page_size | 0 | | Tc_log_page_waits | 0 | | Threads_cached | 44 | | Threads_connected | 12 | | Threads_created | 78 | | Threads_running | 3 | | Uptime | 306640 | +-----------------------------------+------------+ 222 rows in set (0.00 sec) mysql> -- Nik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
