判读mysql的InnoDB缓冲池大小是否设置足够

1.首先让我们来统计一下InnoDB表的实际占用大小。执行如下查询：这会给出一个参考，让你知道如果你想缓存整个数据集应该为InnoDB缓冲池设置多少内存合适。
不过大多数情况你不需要那样做，你只需要缓存你经常使用的数据集。
设置好之后，我们来看看如何检查InnoDB缓冲池大小是否设置足够

mysql> SELECT engine,
-> count(*) as TABLES,
-> concat(round(sum(table_rows)/1000000,2),'M') rows,
-> concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA,
-> concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
-> concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
-> round(sum(index_length)/sum(data_length),2) idxfrac
-> FROM information_schema.TABLES
-> WHERE table_schema not in ('mysql', 'performance_schema', 'information_schema')
-> GROUP BY engine
-> ORDER BY sum(data_length+index_length) DESC LIMIT 10;
+--------+--------+---------+--------+--------+------------+---------+
| engine | TABLES | rows | DATA | idx | total_size | idxfrac |
+--------+--------+---------+--------+--------+------------+---------+
| InnoDB | 203 | 984.03M | 45.30G | 33.10G | 78.40G | 0.73 |
| NULL | 112 | NULL | NULL | NULL | NULL | NULL |
+--------+--------+---------+--------+--------+------------+---------+

 

 

 

2.检查InnoDB缓冲池大小是否设置足够

[root@scada_master ~]# mysqladmin ext -ri1 | grep Innodb_buffer_pool_reads
| Innodb_buffer_pool_reads | 2384992 |
| Innodb_buffer_pool_reads | 0 |
| Innodb_buffer_pool_reads | 0 |
| Innodb_buffer_pool_reads | 0 |
| Innodb_buffer_pool_reads | 0 |
| Innodb_buffer_pool_reads | 0 |
| Innodb_buffer_pool_reads | 0 |

 

你所看到的是从硬盘读取数据到缓冲池的次数(每秒)。

参考连接：

https://blog.csdn.net/cookily_liangzai/article/details/117249494