Powered by Typecho)))
Optimized by EAimTY
本次文章完全使用 mysql 自带的 show 命令实现获取,从 connects、buffercache、lock、SQL、statement、Database throughputs、serverconfig7 大方面全面获取监控数据。
show status like 'Max_used_connections'
show status like 'Threads_connected'
show status like 'Innodb_buffer_pool_reads'
show status like 'Innodb_buffer_pool_read_requests'
show status like 'Innodb_buffer_pool_pages_total'
show status like 'Innodb_buffer_pool_pages_free'
(1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)\*100%
((Innodb_buffer_pool_pages_total-Innodb_buffer_pool_pages_free)/Innodb_buffer_pool_pages_total)\*100%
show status like 'Innodb_row_lock_waits'
show status like 'Innodb_row_lock_time_avg'
show open TABLES where in_use>0;
有数据代表存在锁表,空为无表锁备注:锁等待统计得数量为累加数据,每次获取得时候可以跟之前得数据进行相减,得到当前统计得数据
在MySQL5.7中,performance schema有很大改进,包括引入大量新加入的监控项、降低占用空间和负载,以及通过新的sys schema机制显著提升易用性。下面通过本文给大家介绍 MySQL5.7中 performance和sys schema中的监控参数解释,需要的朋友可以参考下
大部分时候,两个大表做join的时候,where条件前置不但可以节省扫描行数,还能避免创建磁盘临时文件:
优化前:
SELECT g.goods_id, g.add_time, g.goods_name, g.market_price, g.is_new
, g.is_best, g.is_hot, g.shop_price AS org_price
, IFNULL(mp.user_price, g.shop_price * '1.00') AS shop_price
, g.promote_price, g.promote_start_date, g.promote_end_date, g.goods_thumb, g.goods_img
, g.goods_brief, g.goods_type
FROM `ecshop`.`fp_goods` g
LEFT JOIN `ecshop`.`fp_member_price` mp
ON mp.goods_id = g.goods_id
AND mp.user_rank = '1'
WHERE (g.is_delete = 0
AND g.is_on_sale = 1
AND g.is_alone_sale = 1
AND 1)
ORDER BY goods_id DESC
LIMIT 200, 40
背景:在平时和开发的交流 以及 在论坛回答问题的或称中会发现这个问题被问及的频率非常高。 程序中报错: MySQL server has gone away 是什么意思? 如何避免? 因此,感觉有必要总结一下发生这个问题的原因。今天正好看到一篇外文blog总结的比较好,就翻译过来了
判断是否属于这个原因的方法很简单,执行以下命令,查看mysql的运行时长
$ mysql -uroot -p -e "show global status like 'uptime';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime | 68928 |
+---------------+-------+
1 row in set (0.04 sec)