使用温InnoDB缓冲池启动MySQL测试

​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
​