DROP/TRUNCATE分区引发的一系列问题
问题背景:
- 生产的分片数据库innodb_buffer_pool_reads偏高
- 某个开发DBA指出buffer_pool内存里的数据和实际占用磁盘空间应是一致的,也就是varchar(N)字段在buffer_pool中也仅占用N个字节
- DROP和TRUNCATE分区期间,查询挂起在opening table 状态
确认问题的研究方向:
- buffer_pool内存占用空间什么情况和磁盘一致,若不一致的话在什么地方
- drop和truncate分区区别在哪
- 是什么导致DROP或TRUNCATE分区的时候让数据库挂起
验证问题1:
- 建测试表test:
CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `text` varchar(5) DEFAULT NULL,<span style="white-space:pre"> </span>##varchar(2000) PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- 建测试存储过程:
CREATE PROCEDURE `insert_test`(counts INT) begin declare num int; set num=0; while num<counts do insert into test(text) values ('aaaaa'); set num=num+1; end while; end
-
进行测试call insert_test(N)
-
测试结果:
-
text varchar(5)
-
记录数N database_pages data_length data_free buffer_pages size(PAGE_TYPE=INDEX and TABLE_NAME like '%tb%') phsical size 0 402 16384 0 16384 98304 10000 433 344064 0 344064 409600 20000 453 1589248 4194304 671744 9437184 30000 472 1589248 4194304 983040 9437184 40000 492 1589248 4194304 1310720 9437184 50000 514 2637824 4194304 1622016 10485760 100000 611 3686400 4194304 3211264 11534336 200000 806 6832128 4194304 6389760 14680064 500000 1387 15220736 4194304 15908864 24117248 1000000 2359 32047104 6291456 31834112 41943040
-
text varchar(2000)
-
记录数N database_pages data_length data_free buffer_pages size(PAGE_