目前我正在测试TokuDB，印象非常深刻。此时，在两个作业同时运行的情况下，每秒的插入次数达到了峰值，刚刚超过每秒50,000次。平均插入速率在每秒38000到42000个插入之间。
我想要更高，每秒100,000次插入，因为我现在需要插入12亿行计算行，在不久的将来需要插入大约60亿行。我想要一些关于如何实现这一目标的建议:-)
我的当前设置:
- 硬件:VPS 4GB RAM, 150GB SSD, 2核:Intel Westmere E56xx/L56xx/X56xx (Nehalem-C) 2.59GHz CPU
- 磁盘挂载选项:default,noatime
- 操作系统:CentOS 6.8 64bit 数据库:Percona Server 5.7.14-8
my . cnf中所做
# TokuDB #
tokudb_cache_size = 2G
tokudb_commit_sync = 0
tokudb_fsync_log_period = 1000
TokuDB表布局:
CREATE TABLE `t1` (
`id` int(15) NOT NULL AUTO_INCREMENT,
`m_id` int(11) NOT NULL,
`c1` decimal(6,2) DEFAULT NULL,
`c2` decimal(6,2) DEFAULT NULL,
`c3` decimal(6,2) DEFAULT NULL,
`c4` decimal(6,2) DEFAULT NULL,
`c5` decimal(6,2) DEFAULT NULL,
`c6` decimal(6,2) DEFAULT NULL,
`c7` decimal(6,2) DEFAULT NULL,
`factor` decimal(4,2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=TokuDB DEFAULT CHARSET=latin1
CREATE TABLE `t2` (
`id` int(15) NOT NULL AUTO_INCREMENT,
`v_id` int(15) NOT NULL,
`pid` int(11) DEFAULT NULL,
`amount` decimal(6,2) DEFAULT NULL,
`unit` int(1) DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=TokuDB DEFAULT CHARSET=latin1
我知道我没有使用任何索引，除了主键索引。这是由于键会产生负面的时间影响有插入。为每个表创建一个集群键插入作业结束。
附加的MySQL命令行选项:
SET unique_checks=OFF;
不知怎么的，我无法在my.cnf中得到这个。如果有人知道怎么做，那么这将是非常感激的(目前unique_checks = off将阻止MySQL从启动由于一个未知变量在my.cnf)。
SQL语句以15000条为一组进行分组。PHP脚本生成SQL语句，并通过mysqli_multiquery向MySQL服务器发送查询:
<?PHP
foreach (generateCombinations($Arr) as $c) {
$QueryBatch[] = "insert into t1 values (NULL"
. ", " . $record->id
. ", " . rand(1, 35)
. ", " . rand(1, 140)
. ", " . rand(1, 20)
. ", NULL"
. ", " . rand(1, 14)
. ", " . rand(1, 300)
. ", " . rand(1, 4)
. ", NULL );";
$QueryBatch[] = "SET @t1id = LAST_INSERT_ID();";
$cntBatch++;
$pquery = array();
foreach ( $c as $key => $pid){
if ( is_null($pid) )
continue;
$pquery[] = "(NULL, @t1id, " . $pid . ", " . rand(1, 800) . ", 0)";
$cntBatch++;
}
$QueryBatch[] = "insert into t2 values " . implode(',', $pquery) . ";";
if ($cntBatch > 15000) {
$query = implode($QueryBatch);
if ( $mysqli->multi_query($query) ){
while ($mysqli->next_result()) {;}
} else {
printf("Errormessage: %sn", $mysqli->error);
echo $query . "n";
}
$cntBatch = 0;
unset($QueryBatch);
}
}
?>
insert into t1 values (NULL, 1 , 30, 100, 15, NULL, 10, 250, 2, NULL );
SET @t1id = LAST_INSERT_ID();
insert into t2 values (NULL, @t1id, 1, 750, 0),(NULL, @t1id, 1, 600, 0),(NULL, @t1id, 1, 500, 0),(NULL, @t1id, 1, 400, 0),(NULL, @t1id, 1, 300, 0),(NULL, @t1id, 1, 200, 0),(NULL, @t1id, 1, 100, 0);
insert into t1 values (NULL, 2 , 25, 95, 14, NULL, 11, 200, 3, NULL );
SET @t1id = LAST_INSERT_ID();
insert into t2 values (NULL, @t1id, 1, 600, 0),(NULL, @t1id, 1, 100, 0),(NULL, @t1id, 1, 300, 0),(NULL, @t1id, 1, 443, 0),(NULL, @t1id, 1, 521, 0),(NULL, @t1id, 1, 213, 0),(NULL, @t1id, 1, 433, 0);
[.. At least 14982 more..]
如果是我，我会减少正在执行的语句的数量，减少提交的数量。我假设AUTO_COMMIT是启用的，因为我们没有看到任何BEGIN TRANSACTION或COMMIT语句。
这是单个INSERT和SET语句的整个膨胀负载。至少对子表的插入是使用多行插入，而不是为每一行单独的插入语句。
如果我需要这个更快，我会
- 为
t1表生成id值，并将这些值包含在INSERT语句 中 - 取消调用
LAST_INSERT_ID() - 为
t1使用多行插入(而不是为每一行单独的insert语句) - 使用
BEGIN TRANSACTION和COMMIT - 运行单个进程执行插入到
t1(序列化)，以避免潜在的锁争用
如果是InnoDB，我也会做SET FOREIGN_KEY_CHECKS=0。
在代码中已经有很多对rand函数的调用;所以对t1增加一个整数id不会移动指针。当我们开始时，我们需要一个查询来获取当前的AUTO_INCREMENT值，或者获取MAX(id)，无论哪个…
基本上，我会减少要执行的语句的数量，并在每个语句中完成更多的工作，并且在每个COMMIT之前完成更多的工作。
在每条语句中插入十(10)行t1将显著地减少需要执行的语句的数量。
BEGIN TRANSACTION;
-- insert ten rows into t1
INSERT INTO t1 (id,m_id,c1,c2,c3,c4,c5,c6,c7,factor) VALUES
(444055501, 1 , 30, 100, 15, NULL, 10, 250, 2, NULL )
,(444055502, 2 , 25, 95, 14, NULL, 11, 200, 3, NULL )
, ...
,(444055510, 10 , 7, 45, 12, NULL, 10, 300, 4, NULL )
;
-- batch together the t2 rows associated with the ten t1 rows we just inserted
INSERT INTO t2 VALUES
-- 444055501
(NULL, 444055501, 1, 750, 0)
,(NULL, 444055501, 1, 600, 0)
,(NULL, 444055501, 1, 500, 0)
,(NULL, 444055501, 1, 400, 0)
,(NULL, 444055501, 1, 300, 0)
,(NULL, 444055501, 1, 200, 0)
,(NULL, 444055501, 1, 100, 0)
-- 444055502
,(NULL, 444055502, 1, 600, 0)
,(NULL, 444055502, 1, 100, 0)
,(NULL, 444055502, 1, 300, 0)
,(NULL, 444055502, 1, 443, 0)
,(NULL, 444055502, 1, 521, 0)
,(NULL, 444055502, 1, 213, 0)
,(NULL, 444055502, 1, 433, 0)
-- 444055503
, ...
;
-- another ten rows into t1
INSERT INTO t1 (id,m_id,c1,c2,c3,c4,c5,c6,c7,factor) VALUES
(444055511, 11 , 27, 94, 15, NULL, 10, 250, 11, NULL )
,(444055512, 12 , 24, 93, 14, NULL, 11, 200, 12, NULL )
, ...
,(444055520, 10 , 7, 45, 12, NULL, 10, 300, 4, NULL )
;
INSERT INTO t2 VALUES
(NULL, 444055511, 1, 820, 0)
,(NULL, 444055511, 1, 480, 0)
, ...
;
-- repeat INSERTs into t1 and t2, and after 1000 loops
-- i.e. 10,000 t1 rows, do a commit
COMMIT;
BEGIN TRANSACTION;
INSERT INTO t1 ...
加载数据文件
如果不提到LOAD DATA INFILE，任何关于insert性能的讨论都是不完整的。
就最佳性能而言，这是无与伦比的。但是由于我们在文件中没有数据，并且我们没有键值(t2中外键所需的键值)，并且我们已经调用了rand来生成数据，因此LOAD DATA INFILE似乎不太适合。