InnoDB insert性能拐点测试

发表于:2012-12-14来源:IT博客大学习作者:陶方点击数: 标签:性能拐点测试
上篇blog《InnoDB select性能拐点测试》测试了InnoDB select的性能拐点,本篇blog对insert的性能拐点做了一些对比研究。大家有兴趣就关注一下吧!

  上篇blog《InnoDB select性能拐点测试测试了InnoDB select的性能拐点,本篇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系统来说,单表的最大数据量取决于整个数据库的总数据量、相应的表结构以及服务器的硬件设置。

原文转自:http://www.ltesting.net