mysql - mysqld 消耗 232% CPU
问题描述
我的 mysqld 进程消耗 232% CPU 并且有 14000+ 个连接
(我对这件事有点陌生,但在 Stack Overflow 寻求帮助)。
最佳:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
3112 mysql 20 0 7061444 1.397g 15848 S 232.6 8.9 1138:06 mysqld
系统:Ubuntu 18.04、16GB RAM、8 核 CPU、120GB 磁盘和 MySQL 版本 5.7.25
mysql> show status like 'Conn%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Connection_errors_accept | 0 |
| Connection_errors_internal | 0 |
| Connection_errors_max_connections | 0 |
| Connection_errors_peer_address | 0 |
| Connection_errors_select | 0 |
| Connection_errors_tcpwrap | 0 |
| Connections | 14007 |
+-----------------------------------+-------+
7 rows in set (0.01 sec)
并显示像“%timeout%”这样的变量
mysql> show variables like "%timeout%";
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 28800 |
+-----------------------------+----------+
13 rows in set (0.01 sec)
和mysqld.cnf设置
[mysqld]
# Skip reverse DNS lookup of clients
skip-name-resolve
default-storage-engine=InnoDB
max_allowed_packet=500M
max_connections = 256
interactive_timeout=7200
wait_timeout=7200
innodb_file_per_table=1
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 4
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_open_files=5000
innodb_io_capacity=2000
innodb_io_capacity_max=4000
innodb_old_blocks_time=2000
open_files_limit=50000
query_cache_type = 1
query_cache_min_res_unit = 1M
query_cache_limit = 1M
query_cache_size = 50M
tmp_table_size= 256M
max_heap_table_size= 256M
#key_buffer_size = 128M
thread_stack = 128K
thread_cache_size = 32
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
long_query_time = 1
注意:更正上面的 mysqld.cnf 值以匹配以下附加报告
附加信息:
- htop:- https://pastebin.com/43f4b3fK
- 顶部:- https://pastebin.com/rTh1XvUt
- 全局变量:https ://pastebin.com/K2fgKwEv (完成)
- INNODB 状态:- https://pastebin.com/nGrZjHAg
- Mysqltuner:- https://pastebin.com/ZNYieJj8
- [显示完整进程列表]、[ulimit -a]、[iostat -xm]、[lscpu]:- https://pastebin.com/mrnyQrXf
执行多个数据库事务时服务器冻结。是否有类似锁的东西或任何配置缺陷?
(背景:这是一个 WordPress 博客,现在没有其他人访问它。我不知何故从一个旧博客导入了 115K 帖子,但在这里被这个 CPU 幽灵击中)
解决方案
每秒速率 = RPS - 建议为您的 my.cnf [mysqld] 部分考虑,
innodb_lru_scan_depth=100 # from 1024 to reduce 90% of cpu cycles used for function every SECOND
innodb_io_capacity=3500 # from 2000 to enable higher IOPS on your SSD devices
innodb_flushing_avg_loops=5 # from 30 to reduce innodb_buffer_pool_pages_dirty overhead - count was 3183 in SGStatus
read_buffer_size=256K # from 128K to reduce handler_read_next RPS of 277,134
read_rnd_buffer_size=192K # from 256K to reduce handler_read_rnd_next RPS of 778
通过全局变量有更多的机会来提高性能。免责声明:我是我的个人资料中提到的网站的作者,网络个人资料包括联系信息。
推荐阅读
- elasticsearch - 在多边形中查找坐标
- python - 使用python打印10个100个随机数范围(1,100)的列表,并且每个数字在同一个列表中重复但不在另一个列表中重复?
- javascript - 如何修改嵌套对象的属性?
- r - R:特征向量都是空的
- python - 在循环中拟合 Ensemble Regressor 会生成重复值
- python - 无法使用 OpenGL 找到渲染
- python - python代码对象和抽象语法树有什么关系?
- python - 如何将年月时间字符串转换为时间?
- python - 使用 Euler 方法对物体下落进行建模的图与预期不符
- python - 根据数据框 B 中的列过滤数据框 A