title: MySQL: Very slow update/insert/delete queries hanging on "query end" step
body: <p>I have a large and heavy loaded mysql database which performs quite fast at times, but some times get terribly slow. All tables are <strong>InnoDB</strong>, server has <strong>32GB of RAM</strong> and <strong>database size is about 40GB</strong>.</p>
  
  <p>Top 20 queries in my <code>slow_query_log</code> are <code>update</code>, <code>insert</code> and <code>delete</code> queries and I cannot understand why they are so slow (up to 120 seconds sometimes!)</p>
  
  <p>Here is the most frequent query: </p>
  
  <pre><code>UPDATE comment_fallows set comment_cnt_new = 0 WHERE user_id = 1;
  </code></pre>
  
  <p>Profiling results:</p>
  
  <pre><code>mysql&gt; set profiling = 1;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql&gt; update comment_fallows set comment_cnt_new = 0 where user_id = 1;
  Query OK, 0 rows affected (2.77 sec)
  Rows matched: 18  Changed: 0  Warnings: 0
  
  mysql&gt; show profile for query 1;
  +---------------------------+----------+
  | Status                    | Duration |
  +---------------------------+----------+
  | starting                  | 0.000021 |
  | checking permissions      | 0.000004 |
  | Opening tables            | 0.000010 |
  | System lock               | 0.000004 |
  | init                      | 0.000041 |
  | Searching rows for update | 0.000084 |
  | Updating                  | 0.000055 |
  | end                       | 0.000010 |
  | query end                 | 2.766245 |
  | closing tables            | 0.000007 |
  | freeing items             | 0.000013 |
  | logging slow query        | 0.000003 |
  | cleaning up               | 0.000002 |
  +---------------------------+----------+
  13 rows in set (0.00 sec)
  </code></pre>
  
  <p>I am using master/server replication, so the binary log is enabled. I've fallowed one advice I've found on the internet and set <code>flush_log_at_trx_commit</code> to <code>0</code> but it did not make any difference:</p>
  
  <pre><code>mysql&gt; show variables like '%trx%';
  +-------------------------------------------+-------+
  | Variable_name                             | Value |
  +-------------------------------------------+-------+
  | innodb_flush_log_at_trx_commit            | 0     |
  | innodb_use_global_flush_log_at_trx_commit | ON    |
  +-------------------------------------------+-------+
  </code></pre>
  
  <p>The table structure:</p>
  
  <pre><code>CREATE TABLE `comment_fallows` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `user_id` int(11) NOT NULL,
    `part_id` int(11) DEFAULT NULL,
    `article_id` int(11) DEFAULT NULL,
    `request_id` int(11) DEFAULT NULL,
    `comment_cnt` int(10) unsigned NOT NULL,
    `comment_cnt_new` int(10) unsigned NOT NULL DEFAULT '0',
    `last_comment_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `user_id` (`user_id`,`last_comment_date`),
    KEY `part_id` (`part_id`),
    KEY `last_comment_date` (`last_comment_date`),
    KEY `request_id` (`request_id`),
    CONSTRAINT `comment_fallows_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `comment_fallows_ibfk_2` FOREIGN KEY (`part_id`) REFERENCES `fanfic_parts` (`id`) ON DELETE CASCADE,
    CONSTRAINT `comment_fallows_ibfk_3` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE
  ) ENGINE=InnoDB AUTO_INCREMENT=2239419 DEFAULT CHARSET=utf8
  </code></pre>
  
  <p>And all the innodb settings (server has 32 GB of RAM):</p>
  
  <pre><code>mysql&gt; show variables like '%innodb%';
  +-------------------------------------------+------------------------+
  | Variable_name                             | Value                  |
  +-------------------------------------------+------------------------+
  | have_innodb                               | YES                    |
  | ignore_builtin_innodb                     | OFF                    |
  | innodb_adaptive_flushing                  | ON                     |
  | innodb_adaptive_flushing_method           | estimate               |
  | innodb_adaptive_hash_index                | ON                     |
  | innodb_adaptive_hash_index_partitions     | 1                      |
  | innodb_additional_mem_pool_size           | 16777216               |
  | innodb_autoextend_increment               | 8                      |
  | innodb_autoinc_lock_mode                  | 1                      |
  | innodb_blocking_buffer_pool_restore       | OFF                    |
  | innodb_buffer_pool_instances              | 1                      |
  | innodb_buffer_pool_restore_at_startup     | 0                      |
  | innodb_buffer_pool_shm_checksum           | ON                     |
  | innodb_buffer_pool_shm_key                | 0                      |
  | innodb_buffer_pool_size                   | 21474836480            |
  | innodb_change_buffering                   | all                    |
  | innodb_checkpoint_age_target              | 0                      |
  | innodb_checksums                          | ON                     |
  | innodb_commit_concurrency                 | 0                      |
  | innodb_concurrency_tickets                | 500                    |
  | innodb_corrupt_table_action               | assert                 |
  | innodb_data_file_path                     | ibdata1:10M:autoextend |
  | innodb_data_home_dir                      |                        |
  | innodb_dict_size_limit                    | 0                      |
  | innodb_doublewrite                        | ON                     |
  | innodb_doublewrite_file                   |                        |
  | innodb_fake_changes                       | OFF                    |
  | innodb_fast_checksum                      | OFF                    |
  | innodb_fast_shutdown                      | 1                      |
  | innodb_file_format                        | Antelope               |
  | innodb_file_format_check                  | ON                     |
  | innodb_file_format_max                    | Antelope               |
  | innodb_file_per_table                     | ON                     |
  | innodb_flush_log_at_trx_commit            | 0                      |
  | innodb_flush_method                       |                        |
  | innodb_flush_neighbor_pages               | area                   |
  | innodb_force_load_corrupted               | OFF                    |
  | innodb_force_recovery                     | 0                      |
  | innodb_ibuf_accel_rate                    | 100                    |
  | innodb_ibuf_active_contract               | 1                      |
  | innodb_ibuf_max_size                      | 10737401856            |
  | innodb_import_table_from_xtrabackup       | 0                      |
  | innodb_io_capacity                        | 10000                  |
  | innodb_kill_idle_transaction              | 0                      |
  | innodb_large_prefix                       | OFF                    |
  | innodb_lazy_drop_table                    | 0                      |
  | innodb_lock_wait_timeout                  | 120                    |
  | innodb_locks_unsafe_for_binlog            | OFF                    |
  | innodb_log_block_size                     | 512                    |
  | innodb_log_buffer_size                    | 8388608                |
  | innodb_log_file_size                      | 268435456              |
  | innodb_log_files_in_group                 | 3                      |
  | innodb_log_group_home_dir                 | ./                     |
  | innodb_max_dirty_pages_pct                | 90                     |
  | innodb_max_purge_lag                      | 0                      |
  | innodb_mirrored_log_groups                | 1                      |
  | innodb_old_blocks_pct                     | 37                     |
  | innodb_old_blocks_time                    | 0                      |
  | innodb_open_files                         | 300                    |
  | innodb_page_size                          | 16384                  |
  | innodb_purge_batch_size                   | 20                     |
  | innodb_purge_threads                      | 1                      |
  | innodb_random_read_ahead                  | OFF                    |
  | innodb_read_ahead                         | linear                 |
  | innodb_read_ahead_threshold               | 56                     |
  | innodb_read_io_threads                    | 8                      |
  | innodb_recovery_stats                     | OFF                    |
  | innodb_recovery_update_relay_log          | OFF                    |
  | innodb_replication_delay                  | 0                      |
  | innodb_rollback_on_timeout                | OFF                    |
  | innodb_rollback_segments                  | 128                    |
  | innodb_show_locks_held                    | 10                     |
  | innodb_show_verbose_locks                 | 0                      |
  | innodb_spin_wait_delay                    | 6                      |
  | innodb_stats_auto_update                  | 1                      |
  | innodb_stats_method                       | nulls_equal            |
  | innodb_stats_on_metadata                  | ON                     |
  | innodb_stats_sample_pages                 | 8                      |
  | innodb_stats_update_need_lock             | 1                      |
  | innodb_strict_mode                        | OFF                    |
  | innodb_support_xa                         | ON                     |
  | innodb_sync_spin_loops                    | 30                     |
  | innodb_table_locks                        | ON                     |
  | innodb_thread_concurrency                 | 16                     |
  | innodb_thread_concurrency_timer_based     | OFF                    |
  | innodb_thread_sleep_delay                 | 10000                  |
  | innodb_use_global_flush_log_at_trx_commit | ON                     |
  | innodb_use_native_aio                     | ON                     |
  | innodb_use_sys_malloc                     | ON                     |
  | innodb_use_sys_stats_table                | OFF                    |
  | innodb_version                            | 1.1.8-rel25.1          |
  | innodb_write_io_threads                   | 8                      |
  +-------------------------------------------+------------------------+
  92 rows in set (0.00 sec)
  </code></pre>
  
  <p>I've been struggling with this problem for weeks and would be very greatfull for any advice on how to solve this problem.</p>
  
  <p>Why could my <code>update</code>, <code>insert</code> and <code>delete</code> queries be so slow on <code>query end</code> step?</p>
  
  <p><strong>update</strong></p>
  
  <p>I have disabled query cache, but <code>update</code>, <code>insert</code> and <code>delete</code> queries are still very very slow (nothing changed)</p>
  
  <pre><code>show variables like '%cache%';
  +------------------------------+----------------------+
  | Variable_name                | Value                |
  +------------------------------+----------------------+
  | binlog_cache_size            | 4194304              |
  | binlog_stmt_cache_size       | 32768                |
  | have_query_cache             | YES                  |
  | key_cache_age_threshold      | 300                  |
  | key_cache_block_size         | 1024                 |
  | key_cache_division_limit     | 100                  |
  | max_binlog_cache_size        | 18446744073709547520 |
  | max_binlog_stmt_cache_size   | 18446744073709547520 |
  | metadata_locks_cache_size    | 1024                 |
  | query_cache_limit            | 16777216             |
  | query_cache_min_res_unit     | 4096                 |
  | query_cache_size             | 0                    |
  | query_cache_strip_comments   | OFF                  |
  | query_cache_type             | ON                   |
  | query_cache_wlock_invalidate | OFF                  |
  | stored_program_cache         | 256                  |
  | table_definition_cache       | 400                  |
  | table_open_cache             | 2048                 |
  | thread_cache_size            | 8                    |
  +------------------------------+----------------------+
  </code></pre>