大量查询环境下innodb的慢速日志

my997559

大家好,请教大家一个慢速日志的问题.
我们网站每天有500W的PV,我用 SELECT start_time,query_time,lock_time,rows_sent,rows_examined,sql_text
FROM `slow_log`
ORDER BY `slow_log`.`start_time` DESC
查询出来的结果截图如下(每天大约有7W条类似这样的慢速日志):

1:dealLeaveWord表,对wareId,userId建立的有复合索引.这张表一共有680W的记录.
2:userInfo表,id是自增的主键,这张表一共有90W数据.
3:userMoney表,有485W数据.

以上这三张表都是innodb类型,而且所有的慢速日志(我设置的是3秒以上就记日志)都是innodb的查询.这三张表是很重要的表,短期内没有改成myisam的打算.

我的服务器的操作系统是centos5,内存32G(全部被用完了),硬盘空间还有50%空闲,只跑这个mysql服务

mysql的版本是5.1.15-beta-log,目前暂时不能换高版本的mysql,我们曾经换过一次,但有些查询明明用到索引了(在当前版本的mysql),可是在高版本的mysql里就说没有用到索引,造成全站很慢,最后不得已换回去了.

my.cnf上关于innodb的设置如下:

innodb_buffer_pool_size = 2G
innodb_additional_mem_pool_size = 64M
innodb_log_file_size = 256M
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50

用iostat -x 3查看iowait的使用一般都是5%以内,偶尔会跳到15%.

请大家给我一些建议,先谢了.

枫影谁用了

32G的内存，innodb_buffer_pool_size 只设民2G?

并发很大吗？

my997559

上午100左右的连接数,下午在700左右,晚上1000左右.因为不是所有的表都是用innodb,所以只设置了2G,其实我有试着将这个值修改为4G,3G,但运行之后都没有效果.

cenalulu

内存32G(全部被用完了)？ 怎么用完的？
innodb_buffer_pool_size 只有2G阿。。。

至于你那3句语句，都是很简单的sql。
在没有压力的时候（或者去同等数据量的测试机），手动执行看看，如果还是大于4s，那么说明索引设置有问题。
如果很快，说明是大并发导致的，需要调优下参数。

my997559

我的数据体积总共有19G,mysql全新启动以后,一天后,内存就被用完了.
平时拿这样的东西去测试都很快的,并发的量是比较大的,上午一般有1500人同时在线,下午3000至4000左右,晚上一般都是5000人以上.
对于一张查询最多的表,用了一台从机去处理列表的显示.

cenalulu

ps aux|grep mysql 确定一下你的内存是不是mysql消耗完的，占多大。
并且你所谓的用完是怎么看出来的？

数据19G而
innodb_buffer_pool_size 只设置2G阿显然是不太合理的

my997559

呵,不好意思,从ps -aux | grep mysql的结果如下:
root     29296  0.0  0.0  63808  1204 ?        S    Sep28   0:00 /bin/sh ./bin/mysqld_safe --datadir=/sql/mysqlData --pid-file=/sql/mysqlData/localhost.localdomain.pid
mysql    29361 66.0 14.1 6241188 4675432 ?     Sl   Sep28 783:05 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/sql/mysqlData --user=mysql --pid-file=/sql/mysqlData/localhost.localdomain.pid --log-error=/sql/mysqlData/localhost.localdomain.err --socket=/tmp/mysql.sock --port=3306

我是用free -m看到内存被用光了的.
             total       used       free     shared    buffers     cached
Mem:         32189      31729        459          0        233      26119
-/+ buffers/cache:       5377      26812
Swap:        34175          0      34175

cenalulu

mysql用了内存4675432，也就是4g左右，虚拟内存6241188，也就是6g左右。

而free -m也可以看到内存有很多的空余。。。

而你所谓的用完一定是看到了 free只有459吧？
别被误导了，google以下free各列的解释吧。

一般判断内存是否用尽，是看swap的used是不是很多。


现在给你的建议就是增大innodb_buffer_pool_size 到25G左右，由于你的服务器是mysql独用的，可以考虑设的更大

枫影谁用了

Linux将你暂时不使用的内存作为文件和数据缓存，以提高系统性能，当你需要这些内存时，系统会自动释放,free命令将used的值减去 buffer和cache的值就是你当前真实内存使用。

当然问题也可能并不在这个地方，只是这个地方表面上看起来配置得不太合理。
我一开始问你是否并发很大，如果很大的话则有需要慎重高速这个参数。

my997559

啊 ...今天才知道free的具体含义,多谢两位啦..
我明天早上把innodb_buffer_pool_size调整成8G试一下效果.因为数据库里绝大部分都是用的myisam类型的表,所以我再做调整试试..