一次MySQL性能优化实战
作者:ren-xi-jun 来源:IT168 发布时间:2009-03-09 15:01:00
过年这段时间由于线上数据库经常压力过大导致响应非常缓慢甚至死机,咬咬牙下大决心来解决效率不高的问题!
首先是由于公司秉承快速开发原则,频繁上线,导致每次忽视了性能问题!日积月累,所以导致系统越来越慢,所以如果你的系统查询语句本来就优化的很好了可能参考意义不大!
提取慢查询日志文件,应该在你的DataDir目录下面
通过程序处理慢查询文件,将文件格式的慢查询导入到数据库中:
1 mysql> desc slow_query;
2 +---------------+-------------+------+-----+---------+-------+
3 | Field | Type | Null | Key | Default | Extra |
4 +---------------+-------------+------+-----+---------+-------+
5 | Date | varchar(32) | NO | | | | 查询发生的时间
6 | user | varchar(64) | NO | | | |
7 | host | varchar(64) | NO | | | |
8 | content | text | NO | | | | 将Statement进行Mask后的语句,便于Group By
9 | query_time | int(11) | NO | | | | 查询所用时间,直接性能指标
10 | lock_time | int(11) | YES | | 0 | | 等待锁定的时间
11 | rows_sent | int(11) | YES | | 0 | | 返回的结果行数
12 | rows_examined | int(11) | YES | | 0 | | 扫描行数(很重要,上万以后就要重点注意了
13 | statement | text | YES | | NULL | | 实际查询语句
14 +---------------+-------------+------+-----+---------+-------+
然后发挥您的想象力在这个表中尽力捕捉你想捕捉的,那类型语句压力最大、扫描行数最多、等锁最久……
比如:
优化后:
1 mysql> select sum(query_time)/count(*),count
2 (*),sum(query_time),min(Date),Max(Date) from slow where Date>'2008-02-20 22:50:52' and Date<'2008-02-21 17:34:35';
3 +--------------------------+----------+-----------------+---------------------+---------------------+
4 | sum(query_time)/count(*) | count(*) | sum(query_time) | min(Date) | Max(Date) |
5 +--------------------------+----------+-----------------+---------------------+---------------------+
6 | 5.7233 | 2197 | 12574 | 2008-02-20 22:51:16 | 2008-02-21 17:34:10 |
7 +--------------------------+----------+-----------------+---------------------+---------------------+
8 1 row in set (0.09 sec)
优化前:
1 mysql> select sum(query_time)/count(*),count(*),sum(query_time),min(Date),Max(Date) from slow where Date>'2008-02-17 22:50:52' and Date<'2008-02-18 17:34:35';
2 +--------------------------+----------+-----------------+---------------------+---------------------+
3 | sum(query_time)/count(*) | count(*) | sum(query_time) | min(Date) | Max(Date) |
4 +--------------------------+----------+-----------------+---------------------+---------------------+
5 | 2.5983 | 16091 | 41810 | 2008-02-17 22:50:58 | 2008-02-18 17:34:34 |
6 +--------------------------+----------+-----------------+---------------------+---------------------+
7 1 row in set (0.15 sec)
再比如,优化前:
基本信息:
慢查询统计从 2008-02-17 17:59:34 到2008-02-18 22:45:22时间段,接近29个小时的数据;
总共有慢查询28914个,平均一小时有1000个慢查询;(花了一天优化降到每小时100个的样子了,成就感啊)
所有慢查询耗费总时间75690秒;
慢查询时间设置是大于2秒
参数说明:
sum--总执行时间(秒);
count--执行次数;
avg--平均执行时间(秒);
content--类似SQL语句的表达通式,其中'DD'代表数字;
statement--某一条具体执行的SQL语句
由于访问时的锁,导致update非常慢:
1 mysql> select count(*) as n,sum(query_time) as s, sum(query_time)/count(*) as avg,substring_index(statement,' ',2) as u from slow where statement like 'update%' and query_time>14 group by u;
2 +-----+------+---------+--------------------------+
3 | n | s | avg | u |
4 +-----+------+---------+--------------------------+
5 | 7 | 112 | 16.0000 | update conversation |
6 | 151 | 2413 | 15.9801 | update user |
7 | 4 | 65 | 16.2500 | update user_modification |
8 +-----+------+---------+--------------------------+
说明程序中还是存在一些忘记释放事务锁的情况
最耗费资源的10个查询:
其中第1,2,5应该是同一类查询,这样的话这一类查询占总查询的一半以上,每分钟出现10个以上这样的慢查询,需要重点解决!
以下为引用的内容:
1 mysql> select sum(query_time) as sum, count(*) as count, sum(query_time)/count(*) as avg,statement from slow wher
2 e host like '%69.12.23.%' group by content order by sum desc limit 0,10\G
3 *************************** 1. row ***************************
4 sum: 27326
5 count: 11681
6 avg: 2.3394
7 …………
猜你喜欢
- 有很多种方法来实现图片的预加载,通常大部分使用Javascript让事情滚动。不要再受Javascript预载的束缚了吧,用CSS你就可以毫
- SQL Server四类数据仓库建模的方法主要分为以下四类。第一类是关系数据库的三范式建模,通常我们将三范式建模方法用于建立各种操作型数据库
- 是的,我在这里要说:注册表单将死(好拗口啊,但是不知道怎么翻译更恰当)。回想一下,当你想享受一个网站服务的时候,碰到的第一件事是什么?一个表
- 如何让页面背景五彩缤纷? <%@ Language=VBScript %><html>
- 假设要生成一千万个随机数,常规的做法如下:var numbers = [];for (var&nbs
- 对于SQL的新手,NULL值的概念常常会造成混淆,他们常认为NULL是与空字符串''相同的事。情况并非如此。例如,下述语句是
- 2天内的现实new文字 <%if DateDiff("d",rs("date"),date()
- 网站 首页页面宽度 px Yahoo! 950 淘宝 950 MySpace 960 新浪 950 网易 960 Live Search 9
- asp之家补充两点,以让大家看的更明白:一.什么是GUID?由于水平有限在看到这篇文章时,我并不了解什么是GUID,为了看懂文章当然先请教一
- COM接口VC实现,接口: [id(1), helpstring("method Test"
- var a = 0, b = 0;[0, 0].sort(function() {a = 1;return 0;});[0, 1].sort
- 这是早上找了点时间写了一个利用404错误达到静态态效果的类,准备在HTTP://PJSKIN.MYSUC.COM中使用的。不过现在没时间去弄
- global 属性返回 Boolean 值,指出正则表达式使用的global 标志 (g) 的状态。默认值为 false。只读。rgExp.
- 不知道做网络程序的朋友是否重视COOKIES作用域对于多域名或 主域与WWW二级域名同时共用一站点,设置Cookies的作用域,让整个网站用
- IE历来被web标准的拥护者所诟病,而当FireFox横空出世以后,更多的网页制作者开始关注web标准设计。看着FireFox的市场占有率不
- 原文地址:30 Days of Mootools 1.2 Tutorials - Day 14 - Periodical and Intro
- 别误会,IE是不支持CSS3高级选择器,包括最新的IE8(详见《CSS选择器的浏览器支持》),但是CSS选择器的确是很有用的,它可以大大的简
- 1. 确认已经安装了NT/2000和SQL Server的最新补丁程序,不用说大家应该已经安装好了,但是我觉得最好还是在这里提醒一
- vbscript中,错误处理使用on error resume next来完成,如果在你的代码里加入这一句,在这句之后的其他代码如果出现错误
- 介绍:细处着手,巧处用功。高手和菜鸟之间的差别就是:高手什么都知道,菜鸟知道一些。电脑小技巧收集最新奇招高招,让你轻松踏上高手之路。 摘要