MySQL查询慢?可能是innodb_buffer_pool_size的问题

MySQL查询时快时慢, 可能是innodb_buffer_pool_size配置的问题.
我们查询下配置:

$ SELECT @@innodb_buffer_pool_size;
134217728

134217728 / (1024 * 1024) = 128 是默认的128M, 可能太小了, 设多大合适呢? 用这个命令查询:

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

例如:

mysql>     SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
    ->     (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
    ->     FROM information_schema.tables WHERE engine='InnoDB') A;
+-------+
| RIBPS |
+-------+
|     8 |
+-------+
1 row in set (4.31 sec)

我们修改数据库配置, 配置文件一般在/etc/my.cnf里:

[mysqld]
innodb_buffer_pool_size=8G

然后重启.
或者先配置临时生效:

mysql> SET GLOBAL innodb_buffer_pool_size=8589934592;

Query OK, 0 rows affected (0.01 sec)

https://dba.stackexchange.com/questions/27328/how-large-should-be-mysql-innodb-buffer-pool-size