MySQL数据库负载很高的解决办法
来源:互联网 发布:新疆大学教务网络 编辑:程序博客网 时间:2024/04/30 22:11
一、应急解决办法
在MySQL数据库连接数很多,而且大多属于活跃的状态时MySQL机器基本上负载很高,属于基本上快要死去的状态了. 这时怎么办呢?
有可能两个办法.
第一: 先限制Innodb的并发处理.如果innodb_thread_concurrency = 0 可以先改成 16或是64 看机器压力,如果 非常大,先改成16让机器的压力下来,然后慢慢增达,适应自已的业务. 处理方法:
set global innodb_thread_concurrency=16;
第二: 对于连接数已经超过600或是更多的情况,可以考虑适当的限制一下连接数,让前端报一下错,也别让DB挂了. DB在了,总是可以用来加载一下数据,当数据加载到了nosql里了,慢慢的DB压力也会降下来的. 限制单用户连接数在500以下. 如:
set global max_user_connections=500;
(MySQL随着连接数的增加性能会是下降的,这也是thread_pool出现的原因) 另外对于有的监控程序会读取information_schema下面的表的程序可以考虑关闭下面的参数 innodb_stats_on_metadata=0
set global innodb_stats_on_metadata=0;
这个参数主要防止对读取information_schema时造成大量读取磁盘进行信息统计(如果慢查询中出现关于information_schema中表时,也可以考虑禁用该参数)
二、问题分析
思路:
1、确定高负载的类型 htop,dstat命令看负载高是CPU还是IO
2、监控具体的sql语句,是insert update 还是 delete导致高负载
3、检查mysql日志
4、检查硬件问题
dstat
可以看到具体是哪个用户哪个进程占用了相关系统资源,当前CPU、内存谁在使用
[root@cc ~]# dstat -l -m -r -c --top-io --top-mem --top-cpu--io/total- ------memory-usage----- --most-expensive- ----most-expensive---- -most-expensive- read writ| used buff cach free| memory process | i/o process | cpu process 1.90 267 |3399M 178M 3892M 400M|php-fpm: poo 372M|init 1682k 647k|flush-202:0 0.1 0 72.0 |3399M 178M 3892M 400M|php-fpm: poo 372M|php-fpm: po 10k 143k|php-fpm: pool2.0 0 8.00 |3399M 178M 3892M 399M|php-fpm: poo 372M|nginx: work 228k 229k|php-fpm: pool0.5 0 88.0 |3399M 178M 3892M 399M|php-fpm: poo 372M|nginx: work 102k 166k|php-fpm: pool 11 0 38.0 |3399M 178M 3892M 399M|php-fpm: poo 372M|php-fpm: po 787k 650B|php-fpm: pool4.8 0 0 |3399M 178M 3892M 399M|php-fpm: poo 372M|php-fpm: po 788k 723B|php-fpm: pool1.8 0 140 |3400M 178M 3892M 399M|php-fpm: poo 372M|nginx: work 38k 154k|php-fpm: pool1.2 0 12.0 |3400M 178M 3892M 399M|php-fpm: poo 372M|nginx: work 178k 364k|php-fpm: pool1.5 0 0 |3400M 178M 3892M 399M|php-fpm: poo 372M|nginx: work 758k 639k|php-fpm: pool1.5 0 12.0 |3400M 178M 3892M 399M|php-fpm: poo 372M|nginx: work 773k 616k|php-fpm: pool2.06.00 0 |3401M 178M 3892M 398M|php-fpm: poo 372M|nginx: work 994k 688k|nginx: worker1.5 0 272 |3401M 178M 3892M 398M|php-fpm: poo 372M|nginx: work 388k 422k|php-fpm: pool1.5 0 0 |3400M 178M 3893M 398M|php-fpm: poo 372M|nginx: work 483k 548k|php-fpm: pool1.8 0 4.00 |3400M 178M 3893M 398M|php-fpm: poo 372M|php-fpm: po 787k 650B|php-fpm: pool1.5 0 12.0 |3400M 178M 3893M 398M|php-fpm: poo 372M|nginx: work 223k 323k|php-fpm: pool1.5 0 0 |3400M 178M 3893M 398M|php-fpm: poo 372M|nginx: work 371k 474k|php-fpm: pool7.8
htop
htop是top的增强版,更直观
[root@cc ~]# htop 1 [||||||||||| 12.4%] 2 [||||||||| 9.5%] 3 [| 1.0%] 4 [|| 1.9%] Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||3394/7869MB] Swp[|||||||||||||| 75/478MB] Tasks: 71, 12 thr; 2 runningLoad average: 0.39 0.39 0.31 Uptime: 526 days(!), 17:36:38 PID USER PRI NI VIRT RES SHR S CPU% MEM% TIME+ Command 1 root 20 0 19232 396 248 S 0.0 0.0 0:01.86 /sbin/init30752 root 20 0 52532 72 56 S 0.0 0.0 0:00.16 ├─ /usr/sbin/vsftpd /etc/vsftpd/vsftpd.conf24301 root 20 0 193M 3268 1600 S 0.0 0.0 1:41.43 ├─ /usr/sbin/snmpd -LS0-6d -Lf /dev/null -p /var/run/snmpd.pid21361 root 20 0 902M 6500 1308 S 0.0 0.1 0:07.16 ├─ php-fpm: master process (/etc/php-fpm.conf)28627 www 20 0 962M 202M 138M S 0.0 2.6 0:34.46 │ ├─ php-fpm: pool www-c27537 www 20 0 965M 236M 171M R 1.4 3.0 1:19.64 │ ├─ php-fpm: pool www-c27449 www 20 0 961M 251M 189M S 0.0 3.2 1:35.54 │ ├─ php-fpm: pool www-a26442 www 20 0 962M 280M 217M S 0.0 3.6 2:29.71 │ ├─ php-fpm: pool www-a26310 www 20 0 917M 251M 234M S 1.9 3.2 2:46.45 │ ├─ php-fpm: pool www-a26162 www 20 0 962M 297M 233M S 0.0 3.8 2:37.50 │ ├─ php-fpm: pool www-b26147 www 20 0 924M 258M 233M S 0.0 3.3 2:38.37 │ ├─ php-fpm: pool www-c25717 www 20 0 965M 302M 238M S 0.0 3.8 2:54.50 │ ├─ php-fpm: pool www-c24585 www 20 0 964M 324M 260M S 0.0 4.1 4:15.20 │ ├─ php-fpm: pool www-b
tcpdump
抓取mysql包分析,一般抓3306端口的数据
[root@cc ~]# tcpdump -i eth0 -A -s 3000 port 3306 > ~/sql.log
然后使用awk,sort,wc 等命令进行分析
或者
[root@cc ~]# tcpdump -i eth0 -s 0 -l -w - dst port 3306 | strings | perl -e 'while(<>) { chomp; next if /^[^ ]+[ ]*$/;if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER)/i) { if (defined $q) { print "$qn"; } $q=$_; } else { $_ =~ s/^[ t]+//; $q.=" $_"; }}'
就可以看出最繁忙的sql语句了
strace 或 pstack
查看系统调用是否有问题,进程是否堵塞,是否有Broken pipe
[root@cc ~]# strace -p 26578
[root@cc ~]#
pstack 26356
pt-query-digest
分析mysql慢日志,查看哪些sql语句最耗时
[root@cc ~]# pt-query-digest slow.logs# 390ms USER TIME, 10ms system TIME, 15.67M rss, 105.84M vsz # CURRENT DATE: Thu DEC 29 13:22:42 2014 # Hostname: test # Files: slow.log # Overall: 776 total, 11 UNIQUE, 0.00 QPS, 0.00x concurrency _____________ # TIME range: 2011-09-10 04:03:19 TO 2011-12-29 05:02:51 # Attribute total MIN MAX avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # EXEC TIME 5657s 2s 33s 7s 23s 6s 5s # LOCK TIME 33s 0 19s 43ms 98us 715ms 38us # ROWS sent 323.38k 0 107.36k 426.73 0.99 6.35k 0 # ROWS examine 323.39k 0 107.36k 426.74 0 6.35k 0 # Query SIZE 217.95k 38 562 287.61 420.77 81.78 284.79
show processlist
查看系统到底在干什么
mysql> show full processlist;+-----------+---------------+---------------------+---------------------+---------+------+---------------+---------------------------+| Id | User | Host | db | Command | Time | State | Info |+-----------+---------------+---------------------+---------------------+---------+------+---------------+---------------------------+| 184498848 | testdb_rr1356 | 10.11.211.120:61343 | testdb_rr1356_db121 | Sleep | 1384 | | NULL || 184508740 | testdb_rr1356 | 10.11.211.120:11809 | testdb_rr1356_db121 | Sleep | 87 | | NULL || 184509415 | testdb_rr1356 | 10.11.211.120:12760 | testdb_rr1356_db121 | Query | 0 | NULL | show full processlist || 184509451 | testdb_rr1356 | 10.11.211.120:12804 | testdb_rr1356_db121 | Sleep | 10 | | NULL || 184509528 | testdb_rr1356 | 10.11.211.120:12919 | testdb_rr1356_db121 | Query | 0 | freeing items | DESCRIBE test_channel |
检查mysql配置参数是否有问题,引起大量的IO或者高CPU操作
innodb_flush_log_at_trx_commit 、innodb_buffer_pool_size 、key_buffer_size 等重要参数
mysql> show variables like '%innodb%';+---------------------------------+----------------------------+| Variable_name | Value |+---------------------------------+----------------------------+| have_innodb | YES || ignore_builtin_innodb | ON || innodb_adaptive_flushing | ON || innodb_adaptive_hash_index | ON || innodb_additional_mem_pool_size | 2097152 || innodb_autoextend_increment | 8 || innodb_autoinc_lock_mode | 1 || innodb_buffer_pool_size | 2013265920 || innodb_change_buffering | inserts || innodb_checksums | ON |
通过show engine innodb status查看当前事务,内存使用
mysql> show engine innodb status \GLATEST DETECTED DEADLOCK------------------------150731 10:36:50*** (1) TRANSACTION:TRANSACTION EBFBBEC, ACTIVE 0 sec, process no 20691, OS thread id 47345217033984 insertingmysql tables in use 1, locked 1LOCK WAIT 5 lock struct(s), heap size 1248, 4 row lock(s), undo log entries 2MySQL thread id 143249904, query id 1286731854 10.135.21.120 tybuser2014 update#此处具体sql省略----------------------BUFFER POOL AND MEMORY----------------------Total memory allocated 2058485760; in additional pool allocated 0Dictionary memory allocated 819282Buffer pool size 122879Free buffers 97599Database pages 24313Old database pages 8954Modified db pages 7Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 6, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 1049, created 41853, written 304016040.00 reads/s, 0.00 creates/s, 1.75 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 24313, unzip_LRU len: 0I/O sum[45]:cur[0], unzip sum[0]:cur[0]
最后通过zabbix或者cacti等监控来查看IO、CPU、MEMORY、磁盘等是否有异常
这样基本上就可以把问题找出来了
- MySQL数据库负载很高的解决办法
- 高负载应用下的mysql数据库彻底优化
- MySQL数据库负载很高连接数处理方法
- MySQL 数据库高负载故障分析
- MySQL 数据库高负载故障分析
- PHP-FPM高负载的解决办法
- PHP-FPM高负载的解决办法
- PHP-FPM高负载的解决办法
- PHP-FPM高负载的解决办法
- PHP-FPM高负载的解决办法
- PHP-FPM高负载的解决办法
- PHP-FPM高负载的解决办法
- MySql读写分离,及数据库高可用负载均衡的实现
- MySql读写分离,及数据库高可用负载均衡的实现
- 数据库cpu负载高
- linux高负载下mysql数据库彻底优化zz
- 在Linux高负载下mysql数据库彻底优化
- linux高负载下彻底优化mysql数据库
- Unity3D -- 5.X 新版AssetBundle使用方案及策略
- 运动目标检测(混合高斯)——opencv3.0.0
- 1998: Fsk物品调度 并查集
- Recent Evolution of Zero Data Loss Guarantee in Spark Streaming With Kafka
- 读取xml文件
- MySQL数据库负载很高的解决办法
- 2016 PayPal商家账户界面 如何获取API凭证
- Kotlin运行提示Error running app: Instant Run requires 'Tools | Android | Enable ADB integration' t解决办法
- 关于EL表达式
- 代码笔记 | 定位导航效果
- 浅谈Oracle 中 varchar2(N) 与 varchar2(N char) 的区别
- ubuntu中python版本切换
- WinForm弹出字体样式
- Linux软连接和硬链接