How to Achieve 570,000 MySQL Writes per Second with TokuDB

Background

A requirement existed to load more than 2 billion rows from a big‑data platform into MySQL for next‑day reporting. InnoDB can sustain 100‑150 k inserts / s when the dataset fits in memory, but performance degrades sharply when the data exceeds memory.

XeLabs TokuDB

The test used the XeLabs fork of TokuDB (GitHub: https://github.com/XeLabs/tokudb). This fork adds jemalloc, extra performance metrics, Xtrabackup support, ZSTD compression, and binlog_group_commit.

Server Configuration

loose_tokudb_cache_size=4G
loose_tokudb_directio=ON
loose_tokudb_fsync_log_period=1000
tokudb_commit_sync=0

Test Table

CREATE TABLE `user_summary` (
  `user_id` bigint(20) unsigned NOT NULL COMMENT '用户id/手机号',
  `weight` varchar(5) DEFAULT NULL COMMENT '和码体重(KG)',
  `level` varchar(20) DEFAULT NULL COMMENT '重量级',
  `beat_rate` varchar(12) DEFAULT NULL COMMENT '击败率',
  `level_num` int(10) DEFAULT NULL COMMENT '同吨位人数',
  UNIQUE KEY `u_user_id` (`user_id`)
) ENGINE=TokuDB DEFAULT CHARSET=utf8;

Data Load

Data were loaded with MySQL's LOAD DATA INFILE command:

LOAD DATA INFILE '/u01/work/134-136.txt' INTO TABLE user_summary
(user_id, weight, level, beat_rate, level_num);

200 million rows were inserted in 5 min 48.30 s, achieving a write rate of 574 217 rows / s.

Compression

The source file was 8.5 GB; the resulting TokuDB table occupied 3.5 GB, roughly a 2× compression ratio. An equivalent InnoDB load would take about 3‑4 times longer.

Effect of Auto‑Increment Primary Keys

If the table contains an auto‑increment column, TokuDB’s bulk loader cannot be used, forcing row‑by‑row inserts. Loading the same 200 M rows then took 22 min 43 s, a dramatic slowdown.

Recommendations

Avoid auto‑increment columns for bulk loads; use a unique index instead.

Leverage TokuDB’s built‑in jemalloc and ZSTD compression for better memory and I/O efficiency.

Consult the Percona TokuFT Bulk Loader documentation (https://github.com/percona/PerconaFT/wiki/TokuFT-Bulk-Loader) for additional tuning options.

Test Environment

CentOS 7, 8‑core CPU, 8 GB RAM, 500 GB high‑performance cloud disk. The XeLabs TokuDB binary was compiled from source.

Conclusion: In a modest cloud instance, TokuDB can sustain approximately 570 k writes / s for massive bulk loads, outperforming InnoDB by a factor of three to four.