[Mysql] 使用mysqladmin查看innodb_buffer_pool缓存命中率及缓存使用情况


本文总阅读量次

1、查看缓存命中率

计算缓存命中率相关参数：

1
2
Innodb_buffer_pool_reads：innodb缓存无法满足逻辑读，不得不从磁盘获取数据的逻辑读数量
Innodb_buffer_pool_read_requests：逻辑读的请求数量

如果查看当前某一时刻的值：

1
2
3
 mysqladmin -uroot -p12345678  ext | grep Innodb_buffer_pool_reads
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
| Innodb_buffer_pool_reads                      | 31763701                                         |
1
2
3
 mysqladmin -uroot -p12345678 ext | grep Innodb_buffer_pool_read_requests 
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
| Innodb_buffer_pool_read_requests              | 866018530                                        |

如果想要查看多次的取值，并计算间隔的差值(-i 1间隔为1s)(-r 获取前后两个值之间的差值)

1
2
3
4
5
6
7
 mysqladmin -uroot -p12345678 -ri1 ext | grep Innodb_buffer_pool_reads
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
| Innodb_buffer_pool_reads                      | 31763701                                         |
| Innodb_buffer_pool_reads                      | 0                                                |
| Innodb_buffer_pool_reads                      | 0                                                |
| Innodb_buffer_pool_reads                      | 0                                                |
| Innodb_buffer_pool_reads                      | 0                                                |
1
2
3
4
5
6
7
8
9
mysqladmin -uroot -p12345678 -ri1 ext | grep Innodb_buffer_pool_read_requests 
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
| Innodb_buffer_pool_read_requests              | 866018530                                        |
| Innodb_buffer_pool_read_requests              | 0                                                |
| Innodb_buffer_pool_read_requests              | 0                                                |
| Innodb_buffer_pool_read_requests              | 0                                                |
| Innodb_buffer_pool_read_requests              | 0                                                |
| Innodb_buffer_pool_read_requests              | 0                                                |
| Innodb_buffer_pool_read_requests              | 0                                                |

也可指定-c(value)参数，指定获取几次的值：

1
mysqladmin -uroot -p12345678 -ri1 -c5 ext | grep Innodb_buffer_pool_read_requests

根据获取到的值计算缓存命中率：

1
(Innodb_buffer_pool_read_requests-Innodb_buffer_pool_reads)/Innodb_buffer_pool_read_requests

如果太低，建议增大innodb_buffer_pool

2、查看缓存池使用情况

缓存池使用情况参数：

1
2
3
Innodb_buffer_pool_pages_free:innodb buffer pool空闲页；
Innodb_buffer_pool_pages_data：innodb buffer pool包含数据的页数量：
Innodb_buffer_pool_pages_total：innodb buffer pool内存页总数量(每页16k)；
1
2
3
4
5
6
7
mysqladmin -uroot -p12345678 -ri1 ext | grep Innodb_buffer_pool_pages_free
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
| Innodb_buffer_pool_pages_free                 | 31974                                            |
| Innodb_buffer_pool_pages_free                 | 0                                                |
| Innodb_buffer_pool_pages_free                 | 0                                                |
| Innodb_buffer_pool_pages_free                 | 0                                                |
| Innodb_buffer_pool_pages_free                 | 0                                                |
1
2
3
4
5
6
7
mysqladmin -uroot -p12345678 -ri1 ext | grep Innodb_buffer_pool_pages_data
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
| Innodb_buffer_pool_pages_data                 | 96923                                            |
| Innodb_buffer_pool_pages_data                 | 0                                                |
| Innodb_buffer_pool_pages_data                 | 0                                                |
| Innodb_buffer_pool_pages_data                 | 0                                                |
| Innodb_buffer_pool_pages_data                 | 0                                                |
1
2
3
4
5
6
7
mysqladmin -uroot -p12345678 -ri1 ext | grep Innodb_buffer_pool_pages_total
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
| Innodb_buffer_pool_pages_total                | 131056                                           |
| Innodb_buffer_pool_pages_total                | 0                                                |
| Innodb_buffer_pool_pages_total                | 0                                                |
| Innodb_buffer_pool_pages_total                | 0                                                |
| Innodb_buffer_pool_pages_total                | 0                                                |

如果Innodb_buffer_pool_pages_free偏大的话，证明有很多缓存没有被利用到，这时可以考虑减小缓存，相反Innodb_buffer_pool_pages_data过大就考虑增大缓存。

3、另附使用mysqladmin查询qps的方法，参考链接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysqladmin -P3306 -uroot -p12345678  -ri1 ext |\
awk -F"|" \
"BEGIN{ count=0; }"\
'{ if($2 ~ /Variable_name/ && ((++count)%20 == 1)){\
  print "----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --";\
  print "---Time---|---QPS---|select insert update delete| read inserted updated deleted|  logical  physical";\
}\
else if ($2 ~ /Queries/){queries=$3;}\
else if ($2 ~ /Com_select /){com_select=$3;}\
else if ($2 ~ /Com_insert /){com_insert=$3;}\
else if ($2 ~ /Com_update /){com_update=$3;}\
else if ($2 ~ /Com_delete /){com_delete=$3;}\
else if ($2 ~ /Innodb_rows_read/){innodb_rows_read=$3;}\
else if ($2 ~ /Innodb_rows_deleted/){innodb_rows_deleted=$3;}\
else if ($2 ~ /Innodb_rows_inserted/){innodb_rows_inserted=$3;}\
else if ($2 ~ /Innodb_rows_updated/){innodb_rows_updated=$3;}\
else if ($2 ~ /Innodb_buffer_pool_read_requests/){innodb_lor=$3;}\
else if ($2 ~ /Innodb_buffer_pool_reads/){innodb_phr=$3;}\
else if ($2 ~ /Uptime / && count >= 2){\
 printf(" %s |%9d",strftime("%H:%M:%S"),queries);\
 printf("|%6d %6d %6d %6d",com_select,com_insert,com_update,com_delete);\
 printf("|%6d %8d %7d %7d",innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted);\
 printf("|%10d %11d\n",innodb_lor,innodb_phr);\
}}'
mysql
目录
  1. 1. 1、查看缓存命中率
  2. 2. 2、查看缓存池使用情况
  3. 3. 3、另附使用mysqladmin查询qps的方法，参考链接

Proudly powered by Hexo and Theme by Lap
本站访客数人次
© 2020 zeven0707's blog