MySQL 5.6 插入缓冲测试

设置

autocommit = offinnodb_flush_log_at_trx_commit = 0
innodb_buffer_pool_size = 134217728
innodb_change_buffer_max_size = 25
innodb_change_buffering = all

建表

create table t1 (id int auto_increment not null primary key, name varchar(30), key(name)) engine=innodb;

插入数据

delimiter //CREATE PROCEDURE p1 (n int)BEGIN  SET @x = 0;  WHILE @x < n DO    INSERT INTO t1 (NAME)      VALUES (FLOOR(1 + RAND() * (n - 1)));    SET @x = @x + 1;  END WHILE;  COMMIT;END;//delimiter ;call p1 (10000000);

Query OK, 0 rows affected (8 min 23.79 sec)

查看插入缓冲状态

show engine innodb status\G......-------------------------------------INSERT BUFFER AND ADAPTIVE HASH INDEX-------------------------------------Ibuf: size 1, free list len 3081, seg size 3083, 50425 mergesmerged operations: insert 2481177, delete mark 0, delete 0discarded operations: insert 0, delete mark 0, delete 0Hash table size 276671, node heap has 1 buffer(s)0.00 hash searches/s, 2902.60 non-hash searches/s......

说明：

插入1000万条记录
执行时间：8 min 23.79 sec
当前插入缓冲的大小为：3083 * 16K
合并次数：50425
执行insert操作数：2481177

对比测试：

设置

autocommit = offinnodb_flush_log_at_trx_commit = 0innodb_buffer_pool_size = 134217728innodb_change_buffer_max_size = 25innodb_change_buffering = nonecall p1 (10000000);

Query OK, 0 rows affected (22 min 18.85 sec)

查看插入缓冲状态

-------------------------------------INSERT BUFFER AND ADAPTIVE HASH INDEX-------------------------------------Ibuf: size 1, free list len 3081, seg size 3083, 0 mergesmerged operations: insert 0, delete mark 0, delete 0discarded operations: insert 0, delete mark 0, delete 0Hash table size 276707, node heap has 1 buffer(s)0.00 hash searches/s, 0.00 non-hash searches/s

更多相关文章

随机推荐