Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a very simple query that runs in 216ms on my development machine but was taking more than 45 seconds on my production server, that is, until I restarted MySQL there:

UPDATE `forum_categories` SET `post_count` = COALESCE(`post_count`, 0) + 1 WHERE (`id` = 20)

Here is the SHOW STATUS value just before the restart:

+-----------------------------------+--------------+
| Variable_name                     | Value        |
+-----------------------------------+--------------+
| Aborted_clients                   | 356          | 
| Aborted_connects                  | 1            | 
| Binlog_cache_disk_use             | 0            | 
| Binlog_cache_use                  | 0            | 
| Bytes_received                    | 288          | 
| Bytes_sent                        | 17160        | 
| Com_admin_commands                | 0            | 
| Com_assign_to_keycache            | 0            | 
| Com_alter_db                      | 0            | 
| Com_alter_db_upgrade              | 0            | 
| Com_alter_event                   | 0            | 
| Com_alter_function                | 0            | 
| Com_alter_procedure               | 0            | 
| Com_alter_server                  | 0            | 
| Com_alter_table                   | 0            | 
| Com_alter_tablespace              | 0            | 
| Com_analyze                       | 0            | 
| Com_backup_table                  | 0            | 
| Com_begin                         | 0            | 
| Com_binlog                        | 0            | 
| Com_call_procedure                | 0            | 
| Com_change_db                     | 0            | 
| Com_change_master                 | 0            | 
| Com_check                         | 0            | 
| Com_checksum                      | 0            | 
| Com_commit                        | 0            | 
| Com_create_db                     | 0            | 
| Com_create_event                  | 0            | 
| Com_create_function               | 0            | 
| Com_create_index                  | 0            | 
| Com_create_procedure              | 0            | 
| Com_create_server                 | 0            | 
| Com_create_table                  | 0            | 
| Com_create_trigger                | 0            | 
| Com_create_udf                    | 0            | 
| Com_create_user                   | 0            | 
| Com_create_view                   | 0            | 
| Com_dealloc_sql                   | 0            | 
| Com_delete                        | 0            | 
| Com_delete_multi                  | 0            | 
| Com_do                            | 0            | 
| Com_drop_db                       | 0            | 
| Com_drop_event                    | 0            | 
| Com_drop_function                 | 0            | 
| Com_drop_index                    | 0            | 
| Com_drop_procedure                | 0            | 
| Com_drop_server                   | 0            | 
| Com_drop_table                    | 0            | 
| Com_drop_trigger                  | 0            | 
| Com_drop_user                     | 0            | 
| Com_drop_view                     | 0            | 
| Com_empty_query                   | 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_install_plugin                | 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_release_savepoint             | 0            | 
| Com_rename_table                  | 0            | 
| Com_rename_user                   | 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_rollback_to_savepoint         | 0            | 
| Com_savepoint                     | 0            | 
| Com_select                        | 1            | 
| Com_set_option                    | 0            | 
| Com_show_authors                  | 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_contributors             | 0            | 
| Com_show_create_db                | 0            | 
| Com_show_create_event             | 0            | 
| Com_show_create_func              | 0            | 
| Com_show_create_proc              | 0            | 
| Com_show_create_table             | 0            | 
| Com_show_create_trigger           | 0            | 
| Com_show_databases                | 0            | 
| Com_show_engine_logs              | 0            | 
| Com_show_engine_mutex             | 0            | 
| Com_show_engine_status            | 0            | 
| Com_show_events                   | 0            | 
| Com_show_errors                   | 0            | 
| Com_show_fields                   | 0            | 
| Com_show_function_status          | 0            | 
| Com_show_grants                   | 0            | 
| Com_show_keys                     | 0            | 
| Com_show_master_status            | 0            | 
| Com_show_new_master               | 0            | 
| Com_show_open_tables              | 0            | 
| Com_show_plugins                  | 0            | 
| Com_show_privileges               | 0            | 
| Com_show_procedure_status         | 0            | 
| Com_show_processlist              | 8            | 
| Com_show_profile                  | 0            | 
| Com_show_profiles                 | 0            | 
| Com_show_slave_hosts              | 0            | 
| Com_show_slave_status             | 0            | 
| Com_show_status                   | 1            | 
| Com_show_storage_engines          | 0            | 
| Com_show_table_status             | 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_reprepare                | 0            | 
| Com_stmt_reset                    | 0            | 
| Com_stmt_send_long_data           | 0            | 
| Com_truncate                      | 0            | 
| Com_uninstall_plugin              | 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                       | 486          | 
| Created_tmp_disk_tables           | 0            | 
| Created_tmp_files                 | 13           | 
| Created_tmp_tables                | 0            | 
| 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                     | 0            | 
| Innodb_buffer_pool_pages_data     | 439          | 
| Innodb_buffer_pool_pages_dirty    | 16           | 
| Innodb_buffer_pool_pages_flushed  | 342213       | 
| Innodb_buffer_pool_pages_free     | 0            | 
| Innodb_buffer_pool_pages_misc     | 73           | 
| Innodb_buffer_pool_pages_total    | 512          | 
| Innodb_buffer_pool_read_ahead_rnd | 766743       | 
| Innodb_buffer_pool_read_ahead_seq | 697962       | 
| Innodb_buffer_pool_read_requests  | 6614591621   | 
| Innodb_buffer_pool_reads          | 31600209     | 
| Innodb_buffer_pool_wait_free      | 0            | 
| Innodb_buffer_pool_write_requests | 2122755      | 
| Innodb_data_fsyncs                | 266343       | 
| Innodb_data_pending_fsyncs        | 0            | 
| Innodb_data_pending_reads         | 0            | 
| Innodb_data_pending_writes        | 0            | 
| Innodb_data_read                  | 796822818816 | 
| Innodb_data_reads                 | 34112076     | 
| Innodb_data_writes                | 556215       | 
| Innodb_data_written               | 11420234240  | 
| Innodb_dblwr_pages_written        | 342213       | 
| Innodb_dblwr_writes               | 18181        | 
| Innodb_log_waits                  | 0            | 
| Innodb_log_write_requests         | 221569       | 
| Innodb_log_writes                 | 223375       | 
| Innodb_os_log_fsyncs              | 230097       | 
| Innodb_os_log_pending_fsyncs      | 0            | 
| Innodb_os_log_pending_writes      | 0            | 
| Innodb_os_log_written             | 203197440    | 
| Innodb_page_size                  | 16384        | 
| Innodb_pages_created              | 3208         | 
| Innodb_pages_read                 | 48634093     | 
| Innodb_pages_written              | 342213       | 
| Innodb_row_lock_current_waits     | 11           | 
| Innodb_row_lock_time              | 6248224      | 
| Innodb_row_lock_time_avg          | 27769        | 
| Innodb_row_lock_time_max          | 51857        | 
| Innodb_row_lock_waits             | 225          | 
| Innodb_rows_deleted               | 106967       | 
| Innodb_rows_inserted              | 109771       | 
| Innodb_rows_read                  | 7740417900   | 
| Innodb_rows_updated               | 98427        | 
| Key_blocks_not_flushed            | 0            | 
| Key_blocks_unused                 | 13396        | 
| Key_blocks_used                   | 3            | 
| Key_read_requests                 | 78911        | 
| Key_reads                         | 0            | 
| Key_write_requests                | 2447         | 
| Key_writes                        | 0            | 
| Last_query_cost                   | 0.000000     | 
| Max_used_connections              | 31           | 
| Not_flushed_delayed_rows          | 0            | 
| Open_files                        | 46           | 
| Open_streams                      | 0            | 
| Open_table_definitions            | 101          | 
| Open_tables                       | 156          | 
| Opened_files                      | 108434       | 
| Opened_table_definitions          | 0            | 
| Opened_tables                     | 0            | 
| Prepared_stmt_count               | 0            | 
| Qcache_free_blocks                | 58150        | 
| Qcache_free_memory                | 309389896    | 
| Qcache_hits                       | 6750944      | 
| Qcache_inserts                    | 7589394      | 
| Qcache_lowmem_prunes              | 0            | 
| Qcache_not_cached                 | 487929       | 
| Qcache_queries_in_cache           | 105847       | 
| Qcache_total_blocks               | 270852       | 
| Queries                           | 15733827     | 
| Questions                         | 10           | 
| Rpl_status                        | NULL         | 
| Select_full_join                  | 0            | 
| Select_full_range_join            | 0            | 
| Select_range                      | 0            | 
| Select_range_check                | 0            | 
| Select_scan                       | 0            | 
| 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             | 8315915      | 
| Table_locks_waited                | 0            | 
| Tc_log_max_pages_used             | 0            | 
| Tc_log_page_size                  | 0            | 
| Tc_log_page_waits                 | 0            | 
| Threads_cached                    | 3            | 
| Threads_connected                 | 28           | 
| Threads_created                   | 31           | 
| Threads_running                   | 13           | 
| Uptime                            | 66323        | 
| Uptime_since_flush_status         | 66323        | 
+-----------------------------------+--------------+

Table schema is as follows:

mysql> SHOW CREATE TABLE forum_categories\G
*************************** 1. row ***************************
       Table: forum_categories
Create Table: CREATE TABLE `forum_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ancestry` varchar(255) DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `description` text,
  `position` int(11) DEFAULT '0',
  `published` tinyint(1) DEFAULT '1',
  `exclusive` tinyint(1) DEFAULT '0',
  `show_homepage` tinyint(1) DEFAULT '1',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `post_count` int(11) DEFAULT '0',
  `slug` varchar(255) DEFAULT NULL,
  `last_post_id` int(11) DEFAULT NULL,
  `exclusive_level` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_forum_categories_on_ancestry` (`ancestry`),
  KEY `index_forum_categories_on_exclusive` (`exclusive`),
  KEY `index_forum_categories_on_last_post_id` (`last_post_id`),
  KEY `index_forum_categories_on_position` (`position`),
  KEY `index_forum_categories_on_published` (`published`),
  KEY `index_forum_categories_on_show_homepage` (`show_homepage`),
  KEY `index_forum_categories_on_post_count` (`post_count`)
) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8

FYI - the machine has an abundance of RAM and the load average is regularly near 1. MySQL version is 5.1.41.

Any ideas? Thanks.

share|improve this question
    
Compare the storage engine settings. For MyISAM, are the key_buffer_size options the same on both machines ??? For InnoDB, are the innodb_buffer_pool_size options the same ??? Please SHOW CREATE TABLE forum_categories\G and post it in your question. –  RolandoMySQLDBA Feb 15 '11 at 20:36
    
Table info added to original post. Both servers are using default innodb_buffer_pool_size (8MB). Thanks. –  modulaaron Feb 15 '11 at 20:55
    
What did 'show processlist' look like while this was running? The query you posted really shouldn't be taking any significant amount of time to run given you only have 45 rows in the forum_categories table. –  devicenull Feb 16 '11 at 2:40
    
SHOW PROCESSLIST showed the UPDATE query listed in my question above - a couple of them to be exact since they were bottlenecked. –  modulaaron Feb 16 '11 at 21:02
    
Updated my.cnf based on mysqltuner.pl recommendations and I haven't seen issue since. Fingers crossed... –  modulaaron Feb 17 '11 at 23:40
show 1 more comment

1 Answer

Use mysqltuner.pl and see what it says.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.