主页 / dba / 问题 / 144661
Accepted
Mugoma J. Okomba
Asked: 2016-07-23 06:37:47 +0800 CST

MySQL 的使用率突然飙升

  • 772

我们有一个 MySQL DB 正常运行超过 2 年的站点。

然而，2 周前我们注意到负载突然飙升。我们启动了 MySQL 服务并规范了负载。我们认为这个问题是一次性的并且忘记了它。

但是在过去的 1 周里，MySQL 负载几乎每天都很高。有时重启后几分钟。它使网站几乎无法使用。

对日志的检查没有显示任何会导致峰值的查询。大多数查询（约 90%）花费的时间少于 10 毫秒。少数（约 9%）需要 10 到 20 毫秒。还有一些需要 20 到 30 毫秒。但这都是微不足道的，约占1%。除了在应用程序启动时运行的 2 个长时间运行的查询外，我还没有看到任何超过 30 毫秒的常规查询。

$ uname -a
Linux 3.10.0-229.7.2.el7.x86_64 #1 SMP Tue Jun 23 22:06:11 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

$ mysql --version
mysql  Ver 14.14 Distrib 5.6.27, for Linux (x86_64) using  EditLine wrapper

MySQL 会突然变得不稳定的任何原因？

如果由于某种原因，我们刚刚遇到 MySQL 的限制（可能是 InnoDB 问题），如果我们转移到 MariaDB 或 Persona Server（使用 XtraDB）会有帮助吗？

编辑 1：附加信息

我注意到的是在性能下降时有几个SELECT 查询。它们似乎不是任何大的资源密集型查询（最长大约需要 4 毫秒），但它们会突发出现，这可能就是问题所在。

“突发查询”看起来是有效的，而不是 DOS 攻击或 XSS 的结果。

**编辑 2：MySQL 配置*

$ cat /etc/my.cnf
[mysqld]
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under different user or group, 
# customize your systemd unit file for mysqld
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
tmpdir=/var/mysqltmp

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

open_files_limit=50000
query_cache_size=2048M
max_connections=100
max_user_connections=25
wait_timeout=20
tmp_table_size=6144M
max_heap_table_size=6144M
thread_cache_size=64
key_buffer_size=8192M
max_allowed_packet=268435456
#table_cache=32768
table_open_cache=32768
table_definition_cache=32768

delayed_insert_timeout=20 # Turn on if max_connections being reached due to delayed inserts
delayed_queue_size=300 # Turn on if max_connections being reached due to delayed inserts

myisam_sort_buffer_size=512M # can be increased per sessions if needed for alter tables (indexes, repair)

query_cache_limit=32M # leave at default unless there is a good reason
query_cache_type=1
join_buffer=32M # leave at default unless there is a good reason
sort_buffer_size=32M # leave at default unless there is a good reason
read_rnd_buffer_size=16M # leave at default unless there is a good reason
read_buffer_size=32M # leave at default unless there is a good reason

collation_server=utf8_unicode_ci
character_set_server=utf8

general_log=0
log-output=TABLE # select * from mysql.general_log order by event_time desc limit 10;

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
innodb_file_per_table=1
innodb_buffer_pool_size=34406M
innodb_additional_mem_pool_size=7424M
innodb_log_buffer_size=7424M
innodb_thread_concurrency=8 # Number of physical + virtual CPU's, preset when server is provisioned to have correct # of cores
innodb_change_buffering=all
innodb_flush_log_at_trx_commit=0
innodb_support_xa=0
innodb_doublewrite = 0

default-storage-engine=MyISAM


[mysqld_safe]
#malloc-lib=/usr/lib64/libtcmalloc_minimal.so.4
malloc-lib=/usr/lib64/libtcmalloc_minimal.so.4
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

my.cnf显示 default-storage-engine=MyISAM 但我创建的所有表都是 InnoDB。

环境

多少内存？48GB

使用InnoDB？是的

几个核心？8个

mysql innodb

1 个回答

  1. Best Answer

    当您扩展到某个点时，这是一个常见问题。可能有很多原因；让我们先寻找原因；然后是解决方案。

    long_query_time=0
turn on the slowlog
run for a few hours
turn off the slowlog (or raise the cutoff to 1)
run pt-query-digest to find the 'worst' couple of queries.

    这些查询可能是无数个 10 毫秒的查询，也可能是几个 1 分钟的查询。无论哪种情况，请仔细查看是否

    • 你经常需要它们，
    • 他们有最佳指标，
    • 它们可以以更有效的方式组合
    • 他们可以被重写
    • 等等

    检查一些可调参数

    • 你有多少内存？
    • 你在使用 InnoDB 吗？
    • 的价值是innodb_buffer_pool_size多少？
    • 如果你给我提供SHOW GLOBAL STATUS;和SHOW VARIABLES;，我会搜索其他问题。

    其他可能的原因

    • UUID 作为键很糟糕；一旦数据超过缓存，你就陷入了困境。
    • EAV 模式有问题
    • 几个核心？什么是max_connections，Max_used_connections？什么版本？（你可能有“雷鸣般的人群”或“杂货店里太多人”综合症。）

    调整分析

    观察：

    Version: 5.6.27
48 GB of RAM
Uptime = 03:06:22; Please rerun SHOW GLOBAL STATUS after several hours.
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

    更重要的问题

    由于您没有（？）使用 MyISAM，因此减少key_buffer_size到仅 10M。

    innodb_log_buffer_size超过 7G，RAM 的 15%——太大了；减少到 100M。

    innodb_additional_mem_pool_size不再使用；从 my.cnf 中删除它

    将 tmp_table_size 和 max_heap_table_size 设置得太高是非常危险的。建议 RAM 大小的 1%，而不是 12%。

    query_cache_type = ON并且query_cache_size = 2G——这可能是最严重的性能损失。大小不超过100M。2G的时候，每次写都要花很多时间通过Query Cache来清除修改表的条目。此外，QC 上的指标表明它不是很有效。所以甚至考虑将其关闭。

    多个 STATUS 指标表明缺少索引和/或查询编写不当。让我们来看看其中的几个。

    innodb_doublewrite = OFF-- 这可能（很少）在崩溃后导致“页面撕裂”。

    似乎没有 MyISAM 活动；这是意外的，因为一些系统表是 MyISAM。是什么赋予了？同样令人费解的是： default-storage-engine=MyISAM.

    细节和其他观察

    内存分配

    ( (key_buffer_size - 1.2 * Key_blocks_used * 1024) / _ram ) = (8589934592 - 1.2 * 0 * 1024) / 49152M = 16.7% -- key_buffer 中浪费的 RAM 百分比。-- 减小 key_buffer_size。

    ( Key_blocks_used * 1024 / key_buffer_size ) = 0 * 1024 / 8589934592 = 0 -- 使用的 key_buffer 的百分比。高水位线。-- 降低 key_buffer_size 以避免不必要的内存使用。

    ( open_files_limit ) = 1,024 -- ulimit -n -- 要允许更多文件，请更改 ulimit 或 /etc/security/limits.conf 或在 sysctl.conf (kern.maxfiles & kern.maxfilesperproc) 或其他内容（取决于操作系统）

    ( innodb_log_buffer_size ) = 7G——太大了；减少到 100M

    ( join_buffer_size ) = 32M -- 每个线程 0-N。可能会加速 JOIN（更好地修复查询/索引）（所有引擎）用于索引扫描、范围索引扫描、全表扫描、每个完整 JOIN 等。 -- 使用默认值。

    ( min( tmp_table_size, max_heap_table_size ) / _ram ) = min( 6442450944, 6442450944 ) / 49152M = 12.5% -- 当需要 MEMORY 表（每个表）或 SELECT 中的临时表（每个临时表每个一些）时分配的 RAM 百分比选择）。太高可能导致交换。-- 将 tmp_table_size 和 max_heap_table_size 减少到 ram 的 1%。

    ( Created_tmp_disk_tables / (Created_tmp_disk_tables + Created_tmp_tables) ) = 5,245 / (5245 + 19126) = 21.5% -- 溢出到磁盘的临时表的百分比 -- 可能增加 tmp_table_size 和 max_heap_table_size；避免斑点等

    ( tmp_table_size ) = 6,442,450,944 = 6144MB -- 限制用于支持 SELECT 的MEMORY临时表的大小 -- 减少 tmp_table_size 以避免用完 RAM。也许不超过64M。

    ( Handler_read_rnd_next ) = 23,608,688,071 / 11182 = 2111311 /sec -- 如果大量表扫描则高 -- 可能是键不足

    ( Handler_read_rnd_next / Com_select ) = 23,608,688,071 / 116779 = 202,165 -- 每个 SELECT 扫描的平均行数。（大约）——考虑提高 read_buffer_size

    ( Select_scan ) = 33,170 / 11182 = 3 /sec -- 全表扫描 -- 添加索引/优化查询（除非它们是小表）

    ( Select_scan / Com_select ) = 33,170 / 116779 = 28.4% -- 执行全表扫描的选择百分比。（可能会被 Stored Routines 愚弄。） -- 添加索引/优化查询

    ( long_query_time ) = 10.000000 = 10 -- 定义“慢”查询的截止时间（秒）。-- 建议 2

    ( back_log / max_connections ) = 70 / 100 = 70.0%

    ( Connections ) = 28,080 / 11182 = 2.5 /sec -- Connections -- 增加wait_timeout；使用池？

    ( thread_cache_size ) = 64 -- 保留多少额外进程（使用线程池时不相关）（自 5.6.8 起自动调整；基于 max_connections） -- 0 对 Windows 有利 0 对非 Windows 效率低下；10可能没问题

    ( thread_cache_size / max_connections ) = 64 / 100 = 64.0% -- (Windows 为 0)

    ( Com_show_collat​​ions ) = 30/分钟。这是怎么回事？

    • 2

相关问题