Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL: Very slow update/insert/delete queries hanging on "query end" step
    text
    copied!<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>
 

Querying!

 
Guidance

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload