1.首先我们看看InnoDB的架构图，应用访问数据库的时候，数据库从硬盘读取表空间的数据块写入内存的Buffer Pool.
[点击并拖拽以移动]
2.这是一个没有应用访问的MySQL实例的缓存池数据
MySQL localhost SQL > select @@GLOBAL.innodb_page_size;
+---------------------------+
| @@GLOBAL.innodb_page_size |
+---------------------------+
|16384 |
+---------------------------+
1 row in set (0.0006 sec)
MySQL localhost SQL > SELECT table_name,COUNT(*) AS pages,round( sum( IF ( compressed_size = 0, 16384, compressed_size ))/ 1024 / 1024 ) AS 'Total Data(MB)' FROM information_schema.INNODB_BUFFER_PAGE JOIN information_schema.innodbtablespaces USING ( space ) WHERE NAME NOT LIKE 'innodb%' GROUP BY table_name,NAME ORDER BY 3 DESC LIMIT 10;
+----------------------------------+-------+----------------+
| table_name| pages | Total Data(MB) |
+----------------------------------+-------+----------------+
| NULL|36 |1 |
| mysql.columns|82 |1 |
| mysql.tables|35 |1 |
| mysql.schemata|3 |0 |
| mysql.tablespace_files|2 |0 |
| mysql.tablespaces|2 |0 |
| mysql.index_column_usage|13 |0 |
| mysql.table_partition_values |1 |0 |
| mysql.table_partitions|7 |0 |
| mysql.triggers|7 |0 |
+----------------------------------+-------+----------------+
10 rows in set (0.4951 sec)
3.我们用sysbench给数据库注入数据，并做数据库常规预热
[root@mysql8_3 ~]# sysbench --db-driver=mysql --mysql-user=root --mysql-password=123456 --mysql-socket=/tmp/mysql.sock --mysql-db=test --range_size=100 --table_size=500000 --tables=6 --threads=2 --events=0 --time=60 --rand-type=uniform /usr/local/share/sysbench/oltp_read_only.lua prepare
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
Initializing worker threads...
Creating table 'sbtest1'...
Creating table 'sbtest2'...
Inserting 500000 records into 'sbtest1'
Inserting 500000 records into 'sbtest2'
Creating a secondary index on 'sbtest1'...
Creating a secondary index on 'sbtest2'...
Creating table 'sbtest3'...
Creating table 'sbtest4'...
Inserting 500000 records into 'sbtest3'
Inserting 500000 records into 'sbtest4'
Creating a secondary index on 'sbtest4'...
Creating a secondary index on 'sbtest3'...
Creating table 'sbtest6'...
Inserting 500000 records into 'sbtest6'
Creating table 'sbtest5'...
Inserting 500000 records into 'sbtest5'
Creating a secondary index on 'sbtest6'...
Creating a secondary index on 'sbtest5'...
[root@mysql8_3 ~]# sysbench --db-driver=mysql --mysql-user=root --mysql-password=123456 --mysql-socket=/tmp/mysql.sock --mysql-db=test --range_size=100 --table_size=500000 --tables=6 --threads=2 --events=0 --time=20 --rand-type=uniform /usr/local/share/sysbench/oltp_read_only.lua run
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 2
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 373660
write: 0
other: 53380
total: 427040
transactions: 26690 (1334.37 per sec.)
queries: 427040 (21350.00 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
Throughput:
events/s (eps): 1334.3749
time elapsed: 20.0019s
total number of events: 26690
Latency (ms):
min: 1.23
avg: 1.50
max: 12.61
95th percentile: 1.82
sum: 39966.12
Threads fairness:
events (avg/stddev): 13345.0000/14.00
execution time (avg/stddev): 19.9831/0.00
4.我们看一下MySQL实例的缓存池数据
MySQL localhost SQL > SELECT table_name, COUNT(*) AS pages, round( sum( IF ( compressed_size = 0, 16384, compressed_size ))/ 1024 / 1024 ) AS 'Total Data(MB)' FROM information_schema.INNODB_BUFFER_PAGE JOIN information_schema.innodbtablespaces USING ( space ) WHERE NAME NOT LIKE 'innodb%' GROUP BY table_name, NAME ORDER BY 3 DESC LIMIT 10;
+-------------------+-------+----------------+
| table_name| pages | Total Data(MB) |
+-------------------+-------+----------------+
| test.sbtest5|7231 |113 |
| test.sbtest4|7236 |113 |
| test.sbtest2|7225 |113 |
| test.sbtest1|7222 |113 |
| test.sbtest3|7180 |112 |
| test.sbtest6|7195 |112 |
| NULL|36 |1 |
| mysql.columns |83 |1 |
| mysql.tables|35 |1 |
| mysql.servers |1 |0 |
+-------------------+-------+----------------+
10 rows in set (0.6053 sec)
5.然后确认这几个参数目前的值，当前为默认值
MySQL localhost SQL > show global variables like "%buffer_pool_dump%";
+-------------------------------------+-------+
| Variable_name| Value |
+-------------------------------------+-------+
| innodb_buffer_pool_dump_at_shutdown | OFF|
| innodb_buffer_pool_dump_now| OFF|
| innodb_buffer_pool_dump_pct| 25|
+-------------------------------------+-------+
3 rows in set (0.2649 sec)
6.现在我们重启数据库，查看缓冲池里数据，里面没有前面内存InnoDB缓冲池里的数据
MySQL localhost SQL > restart;
Query OK, 0 rows affected (0.0077 sec)
MySQL localhost SQL > \reconnect
Attempting to reconnect to 'mysql://root@/tmp%2Fmysql.sock'..
The global session was successfully reconnected.
MySQL localhost SQL > SELECT table_name, COUNT(*) AS pages, round( sum( IF ( compressed_size = 0, 16384, compressed_size ))/ 1024 / 1024 ) AS 'Total Data(MB)' FROM information_schema.INNODB_BUFFER_PAGE JOIN information_schema.innodbtablespaces USING ( space ) WHERE NAME NOT LIKE 'innodb%' GROUP BY table_name, NAME ORDER BY 3 DESC LIMIT 10;
+----------------------------------+-------+----------------+
| table_name| pages | Total Data(MB) |
+----------------------------------+-------+----------------+
| NULL|36 |1 |
| mysql.columns|80 |1 |
| mysql.tables|35 |1 |
| mysql.schemata|3 |0 |
| mysql.tablespace_files|2 |0 |
| mysql.tablespaces|2 |0 |
| mysql.index_column_usage|12 |0 |
| mysql.table_partition_values |1 |0 |
| mysql.table_partitions|7 |0 |
| mysql.triggers|7 |0 |
+----------------------------------+-------+----------------+
10 rows in set (0.5388 sec)
7.我们跑一下sysbench
[root@mysql8_3 ~]# sysbench --db-driver=mysql --mysql-user=root --mysql-password=123456 --mysql-socket=/tmp/mysql.sock --mysql-db=test --range_size=100 --table_size=500000 --tables=6 --threads=2 --events=0 --time=20 --rand-type=uniform /usr/local/share/sysbench/oltp_read_only.lua run
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 2
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 283822
write: 0
other: 40546
total: 324368
transactions: 20273 (1013.55 per sec.)
queries: 324368 (16216.82 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
Throughput:
events/s (eps): 1013.5511
time elapsed: 20.0020s
total number of events: 20273
Latency (ms):
min: 1.22
avg: 1.97
max: 118.45
95th percentile: 4.03
sum: 39965.35
Threads fairness:
events (avg/stddev): 10136.5000/58.50
execution time (avg/stddev): 19.9827/0.00
8.然后我们启用InnoDB温启动参数
MySQL localhost SQL > set persist Innodb_buffer_pool_dump_at_shutdown=on;
Query OK, 0 rows affected (0.3886 sec)
MySQL localhost SQL > set persist Innodb_buffer_pool_dump_pct=100;
Query OK, 0 rows affected (0.0599 sec)
MySQL localhost SQL > show global variables like "%buffer_pool_dump%";
+-------------------------------------+-------+
| Variable_name| Value |
+-------------------------------------+-------+
| innodb_buffer_pool_dump_at_shutdown | ON|
| innodb_buffer_pool_dump_now| OFF|
| innodb_buffer_pool_dump_pct| 100|
+-------------------------------------+-------+
9.重启数据库查看缓冲池里数据，有数据没有表名，需要对表做一次访问，便会获取表名信息
MySQL localhost SQL > restart;
Query OK, 0 rows affected (0.0012 sec)
MySQL localhost SQL > \reconnect
Attempting to reconnect to 'mysql://root@/tmp%2Fmysql.sock'....
The global session was successfully reconnected.
MySQL localhost SQL > set global innodb_buffer_pool_load_now=on;
Query OK, 0 rows affected (0.2078 sec)
MySQL localhost SQL > SELECT table_name, COUNT(*) AS pages, round( sum( IF ( compressed_size = 0, 16384, compressed_size ))/ 1024 / 1024 ) AS 'Total Data(MB)' FROM information_schema.INNODB_BUFFER_PAGE JOIN information_schema.innodbtablespaces USING ( space ) WHERE NAME NOT LIKE 'innodb%' GROUP BY table_name, NAME ORDER BY 3 DESC LIMIT 10;
+-------------------+-------+----------------+
| table_name| pages | Total Data(MB) |
+-------------------+-------+----------------+
| NULL|6858 |107 |
| NULL|6859 |107 |
| NULL|6857 |107 |
| NULL|6859 |107 |
| NULL|6859 |107 |
| NULL|6858 |107 |
| NULL|39 |1 |
| mysql.columns |91 |1 |
| mysql.tables|41 |1 |
| mysql.servers |1 |0 |
+-------------------+-------+----------------+
10 rows in set (0.6161 sec)
10.然后再用sysbench跑一遍
[root@mysql8_3 ~]# sysbench --db-driver=mysql --mysql-user=root --mysql-password=123456 --mysql-socket=/tmp/mysql.sock --mysql-db=test --range_size=100 --table_size=500000 --tables=6 --threads=2 --events=0 --time=20 --rand-type=uniform /usr/local/share/sysbench/oltp_read_only.lua run
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 2
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 378518
write: 0
other: 54074
total: 432592
transactions: 27037 (1351.73 per sec.)
queries: 432592 (21627.61 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
Throughput:
events/s (eps): 1351.7255
time elapsed: 20.0018s
total number of events: 27037
Latency (ms):
min: 1.25
avg: 1.48
max: 9.38
95th percentile: 1.70
sum: 39964.79
Threads fairness:
events (avg/stddev): 13518.5000/70.50
execution time (avg/stddev): 19.9824/0.00
MySQL localhost test SQL > SELECT table_name, COUNT(*) AS pages, round( sum( IF ( compressed_size = 0, 16384, compressed_size ))/ 1024 / 1024 ) AS 'Total Data(MB)' FROM information_schema.INNODB_BUFFER_PAGE JOIN information_schema.innodbtablespaces USING ( space ) WHERE NAME NOT LIKE 'innodb%' GROUP BY table_name, NAME ORDER BY 3 DESC LIMIT 10;
+-------------------+-------+----------------+
| table_name| pages | Total Data(MB) |
+-------------------+-------+----------------+
| test.sbtest3|6858 |107 |
| test.sbtest5|6858 |107 |
| test.sbtest6|6858 |107 |
| test.sbtest4|6858 |107 |
| test.sbtest2|6858 |107 |
| test.sbtest1|6858 |107 |
| mysql.columns |99 |2 |
| NULL|36 |1 |
| mysql.tables|45 |1 |
| mysql.servers |1 |0 |
+-------------------+-------+----------------+
10 rows in set (0.5217 sec)
参考:
https://dev.mysql.com/doc/refman/8.4/en/innodb-architecture.html
https://dev.mysql.com/doc/refman/8.4/en/innodb-information-schema-buffer-pool-tables.html
https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.html
