InnoDB insert性能拐点测试

本篇blog对insert的性能拐点做了一些对比研究。大家有兴趣就关注一下吧！

1、调整my.cnf的参数如下：
innodb_file_per_table = 0
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 8G
innodb_file_io_threads = 4
重启服务器，启动mysqld

2、在test库上建表：
CREATE TABLE `test` (
`ID` bigint(20) NOT NULL auto_increment,
`INT_A` int(11) default NULL,
`INT_B` int(11) default NULL,
`INT_C` int(11) default NULL,
`STRING_A` varchar(50) default NULL,
`STRING_B` varchar(250) default NULL,
`STRING_C` varchar(700) default NULL,
PRIMARY KEY (`ID`),
KEY `IDX_TEST_IA` (`INT_A`),
KEY `IDX_TEST_IB` (`INT_B`),
KEY `IDX_TEST_SA` (`STRING_A`,`INT_C`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk

3、50个线程并发，各执行以下SQL 80万次：
insert into test(INT_A, INT_B, INT_C, STRING_A, STRING_B, STRING_C) values(CEIL(RAND()*100000), CEIL(RAND()*100000), CEIL(RAND()*100000), random_string(CEIL(50*RAND())), random_string(CEIL(250*RAND())), random_string(CEIL(700*RAND())))

从以上图可以看出，在4000万的数据量内，InnoDB的insert效率是缓慢下降的，并没有出现突降的现象。在上一篇blog中，我曾经提出一个猜想，“前人的实验结果出现性能拐点，是因为内存耗尽，MySQL需要从磁盘上读取数据引起的”。为了证实这个想法，需要限制InnoDB使用buffer，因为8G的buffer再加上8G的操作系统cache，需要的数据量太大了！于是进行了以下第二个对比试验。
1、truncate table test

2、调整my.cnf的参数如下：
innodb_file_per_table = 0
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_buffer_pool_size = 500M
innodb_file_io_threads = 4
重启服务器，启动mysqld

3、50个线程并发，各执行以下SQL 80万次：
insert into test(INT_A, INT_B, INT_C, STRING_A, STRING_B, STRING_C) values(CEIL(RAND()*100000), CEIL(RAND()*100000), CEIL(RAND()*100000), random_string(CEIL(50*RAND())), random_string(CEIL(250*RAND())), random_string(CEIL(700*RAND())))

在以上图中，可以很清楚得看到了性能拐点！性能在数据量达到750万的时候出现了一个大大的降幅！那么，这个性能拐点是不是由buffer的设置引起的呢？再来做个实验验证一下！

1、truncate table test

2、调整my.cnf的参数如下：
innodb_file_per_table = 0
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_buffer_pool_size = 2G
innodb_file_io_threads = 4
重启服务器，启动mysqld

3、50个线程并发，各执行以下SQL 80万次：
insert into test(INT_A, INT_B, INT_C, STRING_A, STRING_B, STRING_C) values(CEIL(RAND()*100000), CEIL(RAND()*100000), CEIL(RAND()*100000), random_string(CEIL(50*RAND())), random_string(CEIL(250*RAND())), random_string(CEIL(700*RAND())))

性能突降的现象消失了！我们可以由此得出一个结论：以测试的表结构而言，4000万的数据量以内，insert的性能是缓步下降的，并未出现性能拐点。然而过小的buffer设置会引起频繁的交换，出现类似性能拐点的现象。结合之前的select性能测试，可以认为Innodb基本上不存在所谓的性能拐点。只要正确估计数据量，合理设置内存，就可以避免出现性能瓶颈。对于分布式MySQL系统来说，单表的最大数据量取决于整个数据库的总数据量、相应的表结构以及服务器的硬件设置。