主页 / dba / 问题 / 267901
Accepted
j4nd3r53n
Asked: 2020-05-27 01:49:27 +0800 CST

为什么这个mysql查询需要这么长时间？

  • 772

我有一个配备硬件的 MySQL 服务器（5.6 版）（Linux，在 AWS 中，32GB RAM，56 个 CPU），根据 top 和 iotop，它甚至感觉不到温暖；然而这个查询需要大约 2 小时（实际查询，而不是解释）：

mysql> explain
    -> SELECT
    -> DATE_FORMAT(DATE('2020-04-14'), '%m/%d/%Y') AS "Gaming Day",
    -> g.name AS "Game name",
    -> u.username AS "User Id",
    -> ga.game_instance_id AS "Game Round Id",
    -> gt.user_transaction_id AS "Transaction Id",
    -> ga.type AS "Transaction Type",
    -> ga.amount AS "Transaction Amount",
    -> CONVERT_TZ(ga.created_timestamp, 'UTC', 'SYSTEM') AS "Transaction Date Time (EST)"
    -> FROM spin.game_action ga
    -> INNER JOIN spin.game_instance gi
    -> ON gi.game_instance_id = ga.game_instance_id
    -> INNER JOIN spin.game_transaction gt
    -> ON gt.game_action_id = ga.game_action_id
    -> INNER JOIN spin.user u
    -> ON ga.user_id = u.user_id
    -> INNER JOIN spin.organisation_site os
    -> ON u.organisation_site_id = os.organisation_site_id
    -> INNER JOIN spin.game g
    -> ON g.game_id = ga.game_id
    -> WHERE os.hostname = 'nyx'
    -> AND gi.end_datetime BETWEEN CONVERT_TZ('2020-04-14 00:00:00', 'SYSTEM', 'UTC') AND CONVERT_TZ('2020-04-21 23:59:59', 'SYSTEM', 'UTC')
    -> AND gi.status IN ('RESOLVED', 'AUTO_COMPLETED');
+----+-------------+-------+--------+---------------------------------+---------+---------+-----------------------------+------+-------------+
| id | select_type | table | type   | possible_keys                   | key     | key_len | ref                         | rows | Extra       |
+----+-------------+-------+--------+---------------------------------+---------+---------+-----------------------------+------+-------------+
|  1 | SIMPLE      | g     | index  | PRIMARY                         | BG_UK1  | 202     | NULL                        |   60 | Using index |
|  1 | SIMPLE      | ga    | ref    | PRIMARY,GA_IX01,GA_IX02,GA_IX03 | GA_IX01 | 4       | spin.g.game_id              |  674 | Using where |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY,U_UK01,U_IX_04          | PRIMARY | 4       | spin.ga.user_id             |    1 | NULL        |
|  1 | SIMPLE      | os    | eq_ref | PRIMARY                         | PRIMARY | 4       | spin.u.organisation_site_id |    1 | Using where |
|  1 | SIMPLE      | gi    | ref    | PRIMARY                         | PRIMARY | 8       | spin.ga.game_instance_id    |    1 | Using where |
|  1 | SIMPLE      | gt    | ref    | GT_IX03                         | GT_IX03 | 9       | spin.ga.game_action_id      |    1 | Using index |
+----+-------------+-------+--------+---------------------------------+---------+---------+-----------------------------+------+-------------+
6 rows in set (0.01 sec)

我尝试在分析下运行它，但这基本上没用：

mysql> show profile for query 2;
+----------------------+------------+
| Status               | Duration   |
+----------------------+------------+
| starting             |   0.000160 |
| checking permissions |   0.000005 |
| checking permissions |   0.000002 |
| checking permissions |   0.000003 |
| checking permissions |   0.000003 |
| checking permissions |   0.000003 |
| checking permissions |   0.000005 |
| Opening tables       |   0.000067 |
| init                 |   0.000133 |
| System lock          |   0.000201 |
| optimizing           |   0.000049 |
| statistics           |   0.000416 |
| preparing            |   0.000050 |
| executing            |   0.000005 |
| Sending data         | 999.999999 |
| end                  |   0.000010 |
| query end            |   0.000008 |
| closing tables       |   0.010543 |
| freeing items        |   0.000062 |
| logging slow query   |   0.000002 |
| logging slow query   |   0.110968 |
| cleaning up          |   0.003693 |
+----------------------+------------+
22 rows in set, 1 warning (0.01 sec)

据我了解，Sending data涵盖了将结果实际传输给客户端和处理的主要部分。我现在正在考虑如何使用 performance_schema 进一步分析它；它可能会透露更多有用的细节吗？

编辑

输出SHOW GLOBAL STATUS\G：https ://pastebin.com/QejRk9RA

输出SHOW GLOBAL VARIABLES\G：https ://pastebin.com/b3B76v21

输出SHOW FULL PROCESSLIST\G：https ://pastebin.com/N85YUwFj

MySQLtuner 报告：https ://pastebin.com/HJJsCCzL

编辑 2

$ ulimit -a
time(seconds)        unlimited
file(blocks)         unlimited
data(kbytes)         unlimited
stack(kbytes)        8192
coredump(blocks)     0
memory(kbytes)       unlimited
locked memory(kbytes) 64
process              128223
nofiles              1024
vmemory(kbytes)      unlimited
locks                unlimited
rtprio               0

db3 root = iostat -xm 5 3
Linux 4.9.0-11-amd64 (db3.spin-production.gamingrealms.org)     05/27/2020      _x86_64_        (56 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.72    0.00    0.07    0.04    0.00   99.17

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.11    11.30   20.83   17.03     3.66     0.66   233.37     0.05    1.24    1.41    1.04   0.51   1.94
dm-0              0.00     0.00    0.04    0.04     0.00     0.00     8.13     0.01   99.38    3.62  184.79   1.05   0.01
dm-1              0.00     0.00   20.88   27.29     3.66     0.66   183.42     0.05    1.05    1.42    0.76   0.40   1.94

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           3.60    0.00    0.16    0.08    0.00   96.16

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00    18.60  121.40   13.80    30.00     0.27   458.59     0.13    0.93    0.92    1.10   0.49   6.64
dm-0              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
dm-1              0.00     0.00  121.40   32.40    30.00     0.27   403.13     0.13    0.82    0.91    0.47   0.43   6.64

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           1.59    0.00    0.16    0.03    0.00   98.23

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00    15.00   49.00   17.60    12.02     0.21   375.95     0.04    0.64    0.87    0.00   0.37   2.48
dm-0              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
dm-1              0.00     0.00   49.00   32.60    12.02     0.21   306.84     0.04    0.52    0.87    0.00   0.30   2.48

db3 root = top -c
top - 04:36:25 up 167 days, 21:28,  1 user,  load average: 1.97, 2.28, 2.18
Tasks: 584 total,   2 running, 582 sleeping,   0 stopped,   0 zombie
%Cpu(s):  3.4 us,  0.2 sy,  0.0 ni, 96.4 id,  0.1 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem : 32848684 total,  5421432 free, 19970372 used,  7456880 buff/cache
KiB Swap: 66916348 total, 66844172 free,    72176 used. 12286984 avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
43108 root      20   0    4440   1312   1044 R  89.2  0.0 243:45.28 /bin/gzip -
18933 root      20   0    4440   1332   1048 S  61.0  0.0 107:19.61 /bin/gzip -
43107 root      20   0  249232  40072  11788 S   9.8  0.1  24:21.13 /usr/bin/innobackupex --stream=tar --user=backupuser --password=x xxxxxx --safe-slave-backup --slave-info --databa+
18932 root      20   0  249232  40024  11740 S   7.2  0.1  10:17.08 /usr/bin/innobackupex --stream=tar --user=backupuser --password=x xxxxxx --safe-slave-backup --slave-info --databa+
18934 root      20   0 1058508 147260   9648 S   4.3  0.4   6:46.18 /usr/bin/python3 /usr/bin/aws s3 cp - s3://spin.db.backup/2020-05-27-spin.tar.gz --region us-east-1
43109 root      20   0 1069768 174488   9404 S   3.6  0.5  14:16.26 /usr/bin/python3 /usr/bin/aws s3 cp - s3://spin.db.backup/2020-05-26-spin.tar.gz --region us-east-1
54915 root      20   0   45472   4396   3240 R   1.6  0.0   0:00.29 top -c
39686 mysql     20   0 21.178g 0.018t   8980 S   1.3 58.2   2979:59 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-err+
41601 root      20   0   33196  11696   5100 S   0.7  0.0  70:42.86 /usr/share/filebeat/bin/filebeat -c /etc/filebeat/filebeat.yml -path.home /usr/share/filebeat -path.config /etc/fi+
  312 root      20   0       0      0      0 S   0.3  0.0   0:36.25 [ksoftirqd/50]
 1661 sensu     20   0 1594004  28004   5676 S   0.3  0.1 127:00.25 /opt/sensu/embedded/bin/ruby /opt/sensu/bin/sensu-client -c /etc/sensu/config.json -d /etc/sensu/conf.d -e /etc/se+
11120 root      20   0  109512    684    660 S   0.3  0.0 132:39.67 /var/ossec/bin/wazuh-modulesd
24552 sensu     20   0  135144  24696      0 S   0.3  0.1 217:07.55 /usr/sbin/sensu-agent start
    1 root      20   0   57396   5144   3668 S   0.0  0.0  11:17.41 /sbin/init
...

当服务器似乎没有负载时（根据 top 和 show processlist）以及一些繁重的 SQL 作业将其推高到大约 1000% CPU 时，我都运行了这个查询，并且它似乎没有太大的区别，相信它或不是。〜2小时是什么都没有发生的事情。

编辑3

显示全球状态：https ://pastebin.com/5PeBEkz7

显示全局变量：https ://pastebin.com/SnGS28rD

显示完整的进程列表：https ://pastebin.com/AR2WZbnM

编辑4

db3 root = hdparm -I /dev/sda

/dev/sda:
SG_IO: bad/missing sense data, sb[]:  70 00 05 00 00 00 00 0d 00 00 00 00 20 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00

ATA device, with non-removable media
Standards:
        Likely used: 1
Configuration:
        Logical         max     current
        cylinders       0       0
        heads           0       0
        sectors/track   0       0
        --
        Logical/Physical Sector size:           512 bytes
        device size with M = 1024*1024:           0 MBytes
        device size with M = 1000*1000:           0 MBytes
        cache/buffer size  = unknown
Capabilities:
        IORDY not likely
        Cannot perform double-word IO
        R/W multiple sector transfer: not supported
        DMA: not supported
        PIO: pio0

编辑

显示创建表并显示表状态：https ://pastebin.com/s9Y61GTb

mysql query-performance

5 个回答

  1. 变量和全局状态分析：

    观察：

    • 版本：5.5.62-38.14-log
    • 32 GB 内存
    • 正常运行时间 = 7 天 21:49:20
    • 您没有在 Windows 上运行。
    • 运行 64 位版本
    • 您似乎完全（或大部分）运行 InnoDB。

    更重要的问题：

    除非您在同一台服务器上有一个小数据集或许多其他应用程序，innodb_buffer_pool_size（现在是 5G）应该更大——比如 22G

    innodb_log_file_size(256M) 对于您正在经历的活动来说相当低。1G会更好。

    假设您有 SSD 驱动器，innodb_io_capacity应该大于 200。比如说，1000。

    “查询缓存”似乎已打开，但未使用。推荐

    query_cache_type = OFF
query_cache_size = 0

    似乎有很多慢查询。让我们调查它们（在另一个问题中）。 http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

    每秒有很多写入。让我们讨论一下。可能有一些技巧可以加快插入/删除/更新的速度——在某些情况下可以提高 10 倍。

    您最繁忙的查询类型是UPDATE. 让我们讨论一下它在做什么。

    由于您不使用很多连接（cf Max_used_connections= 6），建议max_connections从 1000 降低到例如 30。

    CREATE INDEX每 15 分钟发生一次。这很常见；为什么？

    我不是autocommit=0. 如果你愿意，我们可以讨论。同时，请注意，init_connect“root”和其他“SUPER”用户会忽略它。

    细节和其他观察：

    ( innodb_buffer_pool_size / innodb_buffer_pool_instances ) = 5120M / 1 = 5120MB-- 每个 buffer_pool 实例的大小。-- 一个实例至少应为 1GB。在非常大的 RAM 中，有 16 个实例。

    ( Innodb_os_log_written ) = 257,579,082,240 / 683360 = 376930 /sec-- 这是 InnoDB 繁忙程度的指标。-- 非常空闲或非常繁忙的 InnoDB。

    ( Innodb_log_writes ) = 179,367,526 / 683360 = 262 /sec

    ( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 257,579,082,240 / (683360 / 3600) / 2 / 256M = 2.53-- 比率 -- (见分钟)

    ( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 683,360 / 60 * 256M / 257579082240 = 11.9-- InnoDB 日志轮换之间的分钟数从 5.6.8 开始，可以动态更改；请务必同时更改 my.cnf。-- （轮换间隔 60 分钟的建议有些随意。）调整 innodb_log_file_size（现在为 268435456）。（不能在 AWS 中更改。）

    ( innodb_flush_method ) = innodb_flush_method =-- InnoDB 应该如何要求操作系统写入块。建议使用 O_DIRECT 或 O_ALL_DIRECT (Percona) 以避免双重缓冲。（至少对于 Unix。）有关 O_ALL_DIRECT 的警告，请参阅 chrischandler

    ( innodb_io_capacity ) = 200- 磁盘上每秒的 I/O 操作数。100 用于慢速驱动器；200 用于旋转驱动器；SSD 1000-2000；乘以 RAID 系数。

    ( innodb_stats_on_metadata ) = innodb_stats_on_metadata = ON- 触摸统计数据时重新分析表格。-- ON 可能会减慢某些 SHOW 和 information_schema 访问。

    ( expand_fast_index_creation ) = expand_fast_index_creation = OFF-- 使用 ON 可以大大加快 ALTER 和 OPTIMIZE。- 可能更好的是。

    ( innodb_recovery_update_relay_log ) = innodb_recovery_update_relay_log = OFF-- 有助于避免崩溃后的复制错误。

    ( innodb_import_table_from_xtrabackup ) = 0-- 对可传输表空间有用

    ( sync_binlog ) = 0-- 使用 1 来增加安全性，以 I/O 为代价 =1 可能会导致大量“查询结束”；=0 可能会导致“binlog 在不可能的位置”并在崩溃中丢失事务，但速度更快。

    ( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF-- 是否记录所有死锁。-- 如果你被死锁困扰，打开它。注意：如果你有很多死锁，这可能会写入很多磁盘。

    ( character_set_server ) = character_set_server = latin1 -- 将 character_set_server（现在是 latin1）设置为 utf8mb4 可以帮助解决字符集问题。那是未来的默认值。

    ( local_infile ) = local_infile = ON -- local_infile (now ON) = ON 是一个潜在的安全问题

    ( Qcache_free_memory / query_cache_size ) = 54,508,208 / 52M = 100.0%-- Pct Query Cache free -- 降低 query_cache_size（现在为 54525952） 降低它会释放 RAM 用于其他用途，但由于可用空间会随时间而变化，因此这一读数可能会欺骗您。

    ( Qcache_not_cached / (Qcache_hits + Com_select + Qcache_not_cached) ) = 252,613 / (0 + 253801 + 252613) = 49.9%-- 未缓存在 QC 中的 SELECT 百分比。-- QC 不是很有用。

    ( Qcache_hits / Qcache_inserts ) = 0 / 3 = 0-- 命中插入率 -- 高是好的 -- 考虑关闭查询缓存。

    ( Qcache_hits / (Qcache_hits + Com_select) ) = 0 / (0 + 253801) = 0-- 命中率 -- 使用 QC 的 SELECT -- 考虑关闭查询缓存。

    ( Qcache_hits / (Qcache_hits + Qcache_inserts + Qcache_not_cached) ) = 0 / (0 + 3 + 252613) = 0-- 查询缓存命中率 -- 可能最好关闭 QC。

    ( Qcache_free_blocks / Qcache_total_blocks ) = 1 / 1 = 100.0%-- 查询缓存中的碎片。——各种各样的东西。

    ( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (52M - 54508208) / 0 / 8192 = INF-- query_alloc_block_size vs formula -- 调整 query_alloc_block_size (现在 8192)

    ( (Queries-Questions)/Queries ) = (568240759-827878)/568240759 = 99.9%-- 存储例程内的查询部分。--（如果高也不错；但它会影响其他一些结论的有效性。）

    ( Created_tmp_disk_tables / Questions ) = 50,164 / 827878 = 6.1%-- 需要磁盘 tmp 表的查询的百分比。-- 更好的索引/没有斑点/等等。

    ( Handler_read_rnd_next / Com_select ) = 10,856,649,505 / 253801 = 42,776-- 每个 SELECT 扫描的平均行数。（大约）——考虑提高 read_buffer_size（现在为 131072）

    ( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (170092830 + 199370523 + 19280137 + 0) / 178617506 = 2.18-- 每个提交的语句（假设所有 InnoDB） -- 低：可能有助于在事务中将查询分组；高：长期交易使各种事情紧张。

    ( Select_scan / Com_select ) = 280,730 / 253801 = 110.6%-- % 的选择进行全表扫描。（可能被存储例程愚弄。）——添加索引/优化查询

    ( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (170092830 + 19280137 + 0 + 0 + 199370523 + 0) / 683360 = 568 /sec-- writes/sec -- 50 writes/sec + 日志刷新可能会最大化普通驱动器的 I/O 写入容量

    ( Com__biggest ) = Com__biggest = Com_update-- 哪个“Com_”指标最大。-- 通常是 Com_select（现在是 253801）。如果是别的东西，那么它可能是一个草率的平台，或者可能是别的东西。

    ( back_log ) = 50-- （自 5.6.6 起自动调整大小；基于 max_connections） -- 提高到 min(150, max_connections (现在为 1000)) 可能会在进行大量连接时有所帮助。

    ( thread_cache_size / Max_used_connections ) = 8 / 6 = 133.3% - 让线程缓存大于您可能的连接数没有任何优势。浪费空间是缺点。

    异常小：

    (Com_select + Qcache_hits) / (Com_insert + Com_update + Com_delete + Com_replace) = 0.00065
Open_tables = 102
Sort_scan = 0.42 /HR

    异常大：

    Com_begin = 261 /sec
Com_commit = 261 /sec
Com_commit + Com_rollback = 261 /sec
Com_create_index = 0.27 /HR
Com_delete = 28 /sec
Com_insert = 248 /sec
Com_show_slave_status = 0.1 /sec
Com_slave_start = 0.047 /HR
Com_slave_stop = 0.084 /HR
Com_update = 291 /sec
Innodb_buffer_pool_pages_LRU_flushed = 0.49 /sec
Innodb_buffer_pool_read_ahead = 41 /sec
Innodb_buffer_pool_write_requests / Innodb_buffer_pool_pages_flushed = 182
Innodb_checkpoint_age / innodb_log_file_size = 1.15
Innodb_ibuf_merged_inserts = 6.7 /sec
Innodb_ibuf_size = 73
Innodb_rows_updated = 291 /sec
Ssl_accepts = 93
Ssl_default_timeout = 7,200
Ssl_finished_accepts = 88
Ssl_session_cache_misses = 2
Ssl_used_session_cache_entries = 8
Ssl_verify_mode = 5
Table_locks_immediate = 51245 /sec
max_prepared_stmt_count = 100,000

    异常字符串：

    Slave_heartbeat_period = 1800
Slave_running = ON
init_connect = SET autocommit=0
innodb_fast_shutdown = 1
time_zone = +00:00
    • 1
  2. Best Answer

    Your ulimit Open Files limit of 1024 is starving MySQL for handles. From OS command prompt, ulimit -n 36000 and press Enter will dynamically make 36000 handles available - on the next stop/start of MySQL.

    To make 36000 handles available over OS restart, follow this URL 

     https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/

    不要在他们的示例中使用 500,000，使用 36000 使 MySQL 最多可以使用 20000 - 您的 open_files_limit 请求。

    每秒速率 = RPS

    为您的 AWS 参数组考虑的建议

    innodb_io_capacity=1900  # from 200 - you will likely need to work with AWS to get here
innodb_buffer_pool_size=16G  # from ~5 G to reduce innodb_buffer_pool_reads RPS of 36
innodb_buffer_pool_instances=8  # from 1 to reduce mutex contention
read_rnd_buffer_size=128K  # from 256K to reduce handler_read_rnd_next RPS of 15,887
thread_cache_size=16  # from 8 to reduce threads created
innodb_open_files=512  # from 300 to match table_open_cache

    应用这些更改将减少 CPU 繁忙。您的版本 5.5.62-38.14 已超过生命周期 18 个月。新版本具有更好的性能和额外的全局变量，以提高和更好地管理性能。查看我的个人资料、网络个人资料以获取联系信息和免费下载的实用程序脚本以帮助进行性能调整。

    看起来您的慢查询缺少 end_datetime 和状态的 gi 组合索引。如果您可以将两个 AND 向上移动大约 8 行以使 JOIN 具有选择性，它可能会有所帮助。

    • 1

  3. 尝试优化查询表以获得更好的性能：

    optimize table  spin.game_action ;
optimize table  spin.game_instance; 
optimize table  spin.game_transaction; 
optimize table spin.user ;
optimize table  spin.organisation_site; 
optimize table  spin.game ;
    • 0
    • 如果您正在从 转换DATETIME，则每年两次都无法获得正确的时间 - 在夏令时切换期间。使用TIMESTAMP.
    • 如果其中一些表是“多对多”映射表，请遵循此处的提示：http: //mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table
    • 如果您需要更多有关架构的帮助，请提供SHOW CREATE TABLE，以便我们判断索引。
    • MySQL 5.5 真的很老了——那是 AWS 所能提供的最好的吗？
    • 0

  5. It looks like there is a huge misestimation. You wrote this query should return about 500K of rows. MySQL Server estimates it will need to check ~40K of rows (and filter them) and chooses inefficient execution plan. Your tables are large and partitioned. As you may know partitioning is not a performance feature and it complicates things. There may be different ways to improve this query performance but I suppose the first step should be aligning it with the partitions (adding corresponding conditions to reduce the number of used partitions).

    • 0

相关问题