☰

随机缓慢的 MySQL 查询

tag-icon • tag-icon mysql performance
随机缓慢的 MySQL 查询

我知道这种问题经常出现。但我做了很多研究，尝试了很多不同的设置，但仍然有同样的问题：通常非常快的查询可能会随机花费 3 到 5 秒。

服务器是 i7-3770（8 核），32GB RAM。CPU 使用率约为 50% 空闲，而不是 CPU 峰值。未使用交换，可用内存平均约为 10GB。我在 CentOS 6 上运行 mysql 5.5.32。

已为 MySQL 分配了 9GB 的 RAM，它使用了大约 2GB。所有数据都应适合内存（600MB 数据，700MB 索引）。

平均每秒的查询数（无实际峰值）：

  • 1.5 选择
  • 0.2 更新
  • 0.05 插入

下面是一个查询示例，它仅需要几毫秒，但有时需要超过 3 秒：

# Query_time: 4.337884  Lock_time: 0.050146 Rows_sent: 1  Rows_examined: 1
SELECT me.id, me.url, me.filename, me.instance_id, me.virtual_id, me.status, me.user_id, me.time_added, me.time_finished, me.priority, me.size, me.delay, me.flash_delay, me.tries, me.details, me.json_file, me.html, me.shots, me.shot_interval, me.screen_width, me.screen_height FROM Screenshots me WHERE ( me.id = '5992705' );

id 是主键。

尽管我的 SELECT 查询比 INSERT 查询多，但速度较慢的 INSERT 查询比 SELECT 查询多

我已经尝试和测试过的内容：

  • 确保所有必需的索引都存在，但没有多余的索引，也没有未使用的索引
  • 当时没有 CPU 峰值，没有 IO 峰值，没有交换
  • MySQL 的第二个实例作为从属，大多数 SELECT 查询都在从属上完成
  • 删除并 TEXT 和等效数据类型
  • 调整我的.cnf

调整 my.cnf 有很大帮助。我尝试启用和禁用查询缓存，但没有什么区别。

使用从属服务器进行 SELECT 实际上使事情变得更糟：我在主服务器上的慢查询较少，但它们最多可能需要 12 秒！

这是我当前的 my.cf (在这种情况下带有查询缓存)：

tmp_table_size                 = 32M
max_heap_table_size            = 32M
query_cache_type               = 1
query_cache_size               = 1M
thread_cache_size              = 50
open_files_limit               = 65535
table_definition_cache         = 1024
table_open_cache               = 4096

innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 256M
innodb_log_buffer_size         = 8M
innodb_thread_concurrency      = 8
innodb_flush_log_at_trx_commit = 0
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 9G

max_connections=1000
transaction-isolation          = READ-UNCOMMITTED 
innodb_locks_unsafe_for_binlog = 1
innodb_io_capacity             = 1000
innodb_change_buffering        = inserts
innodb_fast_shutdown           = 0

key_buffer_size                = 2G

我没什么主意了。我找不到任何模式（频率、间隔等）来解释这些缓慢的查询。

答案1

您的磁盘设置是什么？您没有提到这一点。听起来它受 IO 限制。

innodb_io_capacity - 保留默认值，除非您有充分的理由或基准来证明您的设置是正确的，否则没有理由更改它。

我怀疑你把这个值设置得太高了。InnoDB 写入线程每秒向磁盘抛出的数据量超出了它们能够处理的范围，从而导致 I/O 排队。

相关内容