MySQL:非常缓慢的更新/插入/删除查询卡在 "query end"步骤

Question

我有一个大而重负载的 mysql 数据库，它有时执行得很快，但有时会变得非常慢。所有表都是 InnoDB，服务器有 32GB RAM 和数据库大小约为 40GB。

我的 slow_query_log 中排名前 20 的查询是 update、insert 和 delete 查询，我无法理解它们的原因太慢了(有时长达 120 秒!)

这是最常见的查询:

UPDATE comment_fallows set comment_cnt_new = 0 WHERE user_id = 1;

分析结果:

mysql> set profiling = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> 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> 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)

我正在使用主/服务器复制，因此启用了二进制日志。我采纳了在互联网上找到的一个建议，将 flush_log_at_trx_commit 设置为 0 但没有任何区别:

mysql> show variables like '%trx%';
+-------------------------------------------+-------+
| Variable_name                             | Value |
+-------------------------------------------+-------+
| innodb_flush_log_at_trx_commit            | 0     |
| innodb_use_global_flush_log_at_trx_commit | ON    |
+-------------------------------------------+-------+

表结构:

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

以及所有 innodb 设置(服务器有 32 GB RAM):

mysql> 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)

数周以来我一直在为这个问题而苦苦挣扎，如果有任何关于如何解决这个问题的建议，我将非常感激。

为什么我的update、insert 和delete 查询在query end 步骤上这么慢？

更新

我已禁用查询缓存，但update、insert 和delete 查询仍然非常非常慢(没有改变)

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                    |
+------------------------------+----------------------+

Answer 1

尝试设置值:

innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT (for non-windows machine)
innodb_buffer_pool_size=25GB (currently it is close to 21GB)
innodb_doublewrite=0
innodb_support_xa=0
innodb_thread_concurrency=0...1000 (try different values, beginning with 200)

引用资料:

MySQL docs用于描述不同的变量。

MySQL Server Setting Tuning

MySQL Performance Optimization basics

希望对你有帮助...