设置
autocommit = off
innodb_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 merges
merged operations:
insert 2481177, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash 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操作数：248117 对比测试：
设置
autocommit = off
innodb_flush_log_at_trx_commit = 0
innodb_buffer_pool_size = 134217728
innodb_change_buffer_max_size = 25
innodb_change_buffering = none
call 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 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
--转自
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2015-8-14 10:03:49 |
赞(
操作
