背景
MySQL版本：8.0.24
cpu 32核
内存 32GB
CPU 和 内存 实际利用率都非常小
该数据库用于记录 上层系统 每日数据处理的结果，所以会有频繁的 truncate清空 与 insert插入 操作。同时，该数据库 还用于承载多套 下游系统。随着上层系统任务的增加，该数据库读取时偶尔会出现卡顿，影响下游系统的用户体验。故考虑优化数据库参数。
truncate操作
truncate操作会清除实际存储文件，delete不会
# delete table
$ ls -lh test_a.ibd
-rw-r----- 1 mysql mysql 64M Feb 2 06:06 test_a.ibd
# truncate table
$ ls -lh test_a.ibd
-rw-r----- 1 mysql mysql 112K Feb 2 06:06 test_a.ibd
也就是说 数据库表 每天都会清空，然后重新分配空间。由于实际数据存储文件达到了GB，考虑数据库扩容对性能的影响。
配置AUTOEXTEND_SIZE的方式
1. 使用innodb_page_size
官方文档显示AUTOEXTEND_SIZE与innodb_page_size有关
查看innodb_page_size的大小，该值默认16k，且只能在初始化时指定。也就是说要修改这个参数，得重新建数据库，显然对于生产环境不现实。
SELECT @@GLOBAL.innodb_page_size;
16384
根据官方文档，默认情况下，大致是，按照1M递增，增长到32M，按照4M递增
Pages, Extents, Segments, and Tablespaces
Each tablespace consists of database pages. Every tablespace in a MySQL instance has the same page size. By default, all tablespaces have a page size of 16KB; you can reduce the page size to 8KB or 4KB by specifying the innodb_page_size option when you create the MySQL instance. You can also increase the page size to 32KB or 64KB. For more information, refer to the innodb_page_size documentation.
The pages are grouped into extents of size 1MB for pages up to 16KB in size (64 consecutive 16KB pages, or 128 8KB pages, or 256 4KB pages). For a page size of 32KB, extent size is 2MB. For page size of 64KB, extent size is 4MB. The “files” inside a tablespace are called segments in InnoDB. (These segments are different from the rollback segment, which actually contains many tablespace segments.)
When a segment grows inside the tablespace, InnoDB allocates the first 32 pages to it one at a time. After that, InnoDB starts to allocate whole extents to the segment. InnoDB can add up to 4 extents at a time to a large segment to ensure good sequentiality of data.
By default, when a file-per-table or general tablespace requires additional space, the tablespace is extended incrementally according to the following rules:
If the tablespace is less than an extent in size, it is extended one page at a time.
If the tablespace is greater than 1 extent but smaller than 32 extents in size, it is extended one extent at a time.
If the tablespace is more than 32 extents in size, it is extended four extents at a time.
测试了一下，不停插入5000条数据，一开始按照KB增幅比较小，后续按照4MB增长
$ ls -lh test_a.ibd
-rw-r----- 1 mysql mysql 68M Feb 2 07:37 test_a.ibd
$ ls -lh test_a.ibd
-rw-r----- 1 mysql mysql 72M Feb 2 07:38 test_a.ibd
2. 建表时指定AUTOEXTEND_SIZE
还有另外一种方式，在建表语句里指定AUTOEXTEND_SIZE
ALTER TABLE test_a AUTOEXTEND_SIZE = 32M
show create table test_a
模拟数据插入，观察ibd文件，以32M为单位增长，说明修改AUTOEXTEND_SIZE生效
insert into test_a select * from xxx
且truncate后的大小受到AUTOEXTEND_SIZE的影响
#AUTOEXTEND_SIZE 256M
$ ls -lh test_a.ibd
-rw-r----- 1 mysql mysql 256M Feb 2 06:55 test_a.ibd
#AUTOEXTEND_SIZE 64M
$ ls -lh test_a.ibd
-rw-r----- 1 mysql mysql 64M Feb 2 06:56 test_a.ibd
对比测试性能
对比默认，和设置AUTOEXTEND_SIZE时的插入时间
用一个插入事务测试
START TRANSACTION;
insert into test_a select * from xxx
insert into test_a select * from xxx
insert into test_a select * from xxx
-- 重复多次
commit;
分别设置512M,设置256M,设置64M,不设置
-rw-r----- 1 mysql mysql 512M Feb 2 08:07 test_b.ibd
-rw-r----- 1 mysql mysql 256M Feb 2 08:07 test_c.ibd
-rw-r----- 1 mysql mysql 64M Feb 2 08:07 test_d.ibd
-rw-r----- 1 mysql mysql 112K Feb 2 08:07 test_e.ibd
Updated Rows 1792848
Execute time (ms) 102395
Execute time (ms) 96630
Execute time (ms) 100252
Execute time (ms) 101761
差距并不明显，且多次执行后结果并不相同
结论
那么设置AUTOEXTEND_SIZE以后有没有意义：
- 从理论上来看，数据量大的情况下，肯定是能够大幅减少扩容操作的。
- 但从实验结果上来看，优化有限。
- 并且该值过大，会有空间浪费。
放弃修改扩容参数，继续研究卡顿原因。
