深网著名网站,个人作品展示网站,网站制作公司都还赚钱吗,网站建设文章缩略图mysql 下常见遇到的问题有#xff0c;mysql连接池耗尽#xff0c;死锁、慢查、未提交的事务。等等我们可能需要看#xff1b;我们想要查看的可能有 1.当前连接池连接了哪些客户端#xff0c;进行了哪些操作 2.当前造成死锁的语句有哪些#xff0c;是哪个客户端上的#x…mysql 下常见遇到的问题有mysql连接池耗尽死锁、慢查、未提交的事务。等等我们可能需要看我们想要查看的可能有 1.当前连接池连接了哪些客户端进行了哪些操作 2.当前造成死锁的语句有哪些是哪个客户端上的我们如何杀掉结束掉这些连接 3.我们当前的慢查询有哪些执行了多少次这些语句有没有记录下来 4.如何查看是不是因为屋里内存、磁盘等原因导致mysql性能下降等
一、查看mysql的物理性能
使用以下命令查看mysql进程 性能瓶颈排查 top/free/vmstat/sar/mpstat
查看mysqld进程的cpu消耗占比 确认mysql进程的cpu消耗是%user, 还是sys%高 确认是否是物理内存不够用了 确认是否有swap产生
使用下面工具查看
top (%cpu load %MMEM)free -gtvmstat -S m 1 (procs io cpu)sar -u 1 (%user)sar -d 1查看mysql的物理连接池状态
show processlist; # 查询数据库的瞬时操作查看当前连接运行的情况 show processlist程序端一般采用线程池的hikriCP 、duriu等数据库线程池框架在项目启动的时候就会连接上数据库从这里我们也能看到数据库连接池线程连接行数据库的线程和每个线程的客户端的占用操作、是否有死锁等
各字段描述
id一个标识kill一个语句的时候很有用 (如 kill 11)user显示当前用户如果不是root这个命令就只显示你权限范围内的sql语句host显示这个语句是从哪个端口上发出的可用来追踪出问题语句的用户db显示这个进程目前连接的是哪个数据库command显示当前连接的执行命令一般就是休眠(sleep),查询(query),连接(connect)time这个状态持续的时间单位是秒state(重要)显示使用当前连接的sql语句的状态很重要的列后续会有所有的状态的描述state只是语句执行中的某一个状态一个SQL语句,已查询为例可能需要经过copying to tmptable,sorting result, sending data等状态才可以完成 状态为sleeping代表资源未释放 状态为copying to tmptable on disk 临时表从内存存储转为磁盘存储会导致大量的IO压力频繁出现需要优化 状态为locked 代表查询锁住了或有更新操作锁定(更新操作需要使用正确的索引) 状态为sorting for group 正在处理select查询的记录如果sending data连接过多通常是某查询的影响结果集过大需要优化 状态为reading from net 如大量出现迅速检查数据库到前端的网络状态和流量 info显示这个SQL语句因为长度有限所以长的SQL语句就显示不全但是一个判断问题语句的重要依据
status状态详解
status为 sorting for group 的状态分析
长时间的Sending data从引擎层读取数据返回给server端 1.长时间存在的原因:
没有合适的索引 查询效率低下读取大量数据 读取缓慢系统负载高 读取缓慢可能有长时间的没有提交的事务导致锁表等待
如何做
1 加上合适的索引2 改写sql3 增加LIMIT限制每次读取量4 检查升级IO设备性能
status 长时间等待MDL锁 (waiting for table metadata lock) 分析
原因:
DDL被阻塞 进而阻塞其他后续sqlDDL之前的sql长时间未结束这个表未释放锁
举例 a.开启一个事务。未提交这个时候fege表的id1这行有一个排它锁。 START TRANSACTION; update t_goods set name 李四 where id 1b.开启另外一个DDL事务重新打开另外一个session连接 c.查看是什么事务进程id未释放以及sql信息打开另外一个mysql的session 在事务没有完成之前fege表上的锁不会释放alter table同样获取不到metadata的独占锁。 d。找到未提交事物的sid通过show processlist看不到Table上有任何操作但实际上存在有未提交的事务可以在 information_schema.innodb_trx或者performance_schema.events_statements_current中查看到。 select * from information_schema.innodb_trxe. 执行 select * from performance_schema.events_statements_current\G; 可以看到当前未提交的sql信息 select * from performance_schema.events_statements_currentf 通过上面查看到未提交的THREAD_ID查看对应的进程id g 通过查看
select object_type,object_schema,object_name,lock_type,lock_duration,lock_status,owner_thread_id from performance_schema.metadata_locks;#再次查看
如何做:
提高每条sql的效率kill掉长时间运行的sql把DDL放在夜间低谷时段采用pt-osc执行DDL长时间的sleep占用连接数消耗内存未释放 可能有行锁(甚至是表锁未释放) 如何做:适当调低timeout主动kill超时不活跃连接定期检查锁、锁等待可以利用pt-kill工具
其他状态 Copy to tmp table [on disk]
执行alter table修改表结构需要生成临时表 建议放在夜间低谷进行 或者用pt-osc
状态 Creating tmp table
常见于group by没有索引的情况 需要拷贝数据到临时表[内存/磁盘上] 执行计划中会出现Using temporary关键字 建议创建合适的索引消除临时表
状态 Creating sort index
常见于order by没有索引的情况 需要进行filesort排序 执行计划中会出现Using filesort关键字 建议创建排序索引 其他排除方法
use information_schema; SELECT * from innodb_lock_waits; show engine innodb status;
