MySQL：挂在“查询结束”步骤上的更新/插入/删除查询非常慢
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12316122/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: Very slow update/insert/delete queries hanging on "query end" step
提问by Silver Light
I have a large and heavy loaded mysql database which performs quite fast at times, but some times get terribly slow. All tables are InnoDB, server has 32GB of RAMand database size is about 40GB.
我有一个大而重的 mysql 数据库，它有时执行得非常快，但有时会变得非常慢。所有表都是InnoDB，服务器有32GB 的 RAM，数据库大小约为 40GB。
Top 20 queries in my slow_query_logare update, insertand deletequeries and I cannot understand why they are so slow (up to 120 seconds sometimes!)
顶部我的20个查询slow_query_log的update，insert以及delete查询，我不明白他们为什么这么慢（长达120秒，有时！）
Here is the most frequent query:
这是最常见的查询：
UPDATE comment_fallows set comment_cnt_new = 0 WHERE user_id = 1;
Profiling results:
分析结果：
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)
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 flush_log_at_trx_committo 0but it did not make any difference:
我正在使用主/服务器复制，因此启用了二进制日志。我已经放弃了我在互联网上找到的一个建议并设置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 |
+-------------------------------------------+-------+
The table structure:
表结构：
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
And all the innodb settings (server has 32 GB of RAM):
以及所有 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)
I've been struggling with this problem for weeks and would be very greatfull for any advice on how to solve this problem.
数周以来，我一直在为这个问题苦苦挣扎，对于如何解决这个问题的任何建议，我都会非常满意。
Why could my update, insertand deletequeries be so slow on query endstep?
为什么我的update,insert和delete查询query end步骤如此缓慢？
update
更新
I have disabled query cache, but update, insertand deletequeries are still very very slow (nothing changed)
我已禁用查询缓存，但是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 |
+------------------------------+----------------------+
回答by jsist
Try setting values:
尝试设置值：
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)
References:
参考：
MySQL docsfor description of different variables.
用于描述不同变量的MySQL 文档。
MySQL Performance Optimization basics
Hope it helps...
希望能帮助到你...
回答by joocer
There appears to be a bug with how MySQL handles the query cache which causes similar behaviour (see http://bugs.mysql.com/bug.php?id=28382).
MySQL 处理查询缓存的方式似乎存在错误，导致类似行为（请参阅http://bugs.mysql.com/bug.php?id=28382）。
What is basically happening is that the cache needs to be updated following any query that modifies data (INSERT, UPDATE, DELETE). With a large cache it is taking a long time to do this, if the cache is smaller then its faster.
基本上发生的事情是在任何修改数据的查询（插入、更新、删除）之后都需要更新缓存。对于大型缓存，这样做需要很长时间，如果缓存较小，则速度更快。
So the work-around until the engine is fixed is to decrease the cache size.
因此，在修复引擎之前的解决方法是减少缓存大小。
回答by kzmr
It may be a problem of hardware if you are using DELL server. I resolved this command.
如果您使用的是 DELL 服务器，则可能是硬件问题。我解决了这个命令。
/opt/dell/srvadmin/bin/omconfig storage vdisk action=changepolicy controller=0 vdisk=0 writepolicy=fwb
/opt/dell/srvadmin/bin/omconfig storage vdisk action=changepolicy controller=0 vdisk=0 writepolicy=fwb
回答by user188826
I have the problem in our test environment(Not set up by DBA).Finally I find there is one conf in my.cnf : sync_binlog=1. I change this conf to 0,and it works. You can have a try.
我在我们的测试环境中遇到了问题（不是由 DBA 设置的）。最后我发现 my.cnf 中有一个 conf：sync_binlog=1。我将此配置更改为 0，它可以工作。你可以试试。
回答by Matija Nalis
It is probably due to slow disk writes.
这可能是由于磁盘写入速度慢。
In our case it was because Debian GNU/Linux running mysqld was virtualised in Hyper-V, and even if it was given SSD storage hdparm -twas giving terrible results (10-20MB/s instead of 600MB/s that it gets on raw hardware)
在我们的例子中，这是因为运行 mysqld 的 Debian GNU/Linux 在 Hyper-V 中被虚拟化了，即使给它 SSD 存储hdparm -t也给出了糟糕的结果（10-20MB/s 而不是 600MB/s，它在原始硬件上获得）
回答by Cristian Porta
Try starting tuning your innodb_buffer_pool_instancesaccording the innodb_buffer_pool_size
尝试开始调整你的innodb_buffer_pool_instances根据innodb_buffer_pool_size
Firs of all I think you can increase significantly your innodb_buffer_pool_size...
首先，我认为您可以显着增加您的innodb_buffer_pool_size...
innodb_buffer_pool_instances sysvar
innodb_buffer_pool_instances 系统变量
The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. The default value is 128MB, increased from a historical default of 8MB. The maximum value depends on the CPU architecture, 32-bit or 64-bit. For 32-bit systems, the CPU architecture and operating system sometimes impose a lower practical maximum size.
The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated database server, you may set this to up to 80% of the machine physical memory size. Be prepared to scale back this value if these other issues occur...
InnoDB 用于缓存其表的数据和索引的内存缓冲区的大小（以字节为单位）。默认值为 128MB，从历史默认值 8MB 增加。最大值取决于 CPU 架构，32 位或 64 位。对于 32 位系统，CPU 体系结构和操作系统有时会施加较低的实际最大大小。
您将此值设置得越大，访问表中的数据所需的磁盘 I/O 就越少。在专用数据库服务器上，您最多可以将其设置为机器物理内存大小的 80%。如果发生这些其他问题，请准备好缩减此值...
Then you can tune the innodb_buffer_pool_instances, using multiple buffer pools was very important, yuo can read a good test case here:
然后你可以调整innodb_buffer_pool_instances，使用多个缓冲池非常重要，你可以在这里阅读一个很好的测试用例：
