How to Achieve 570,000 Writes/sec in MySQL with TokuDB

1. Requirement

A friend needed to load more than 2 billion rows from a big‑data platform into MySQL for next‑day reporting.

2. Implementation Analysis

InnoDB can sustain 100‑150 k inserts per second when the data fits in memory, but for data larger than memory we tested XeLabs TokuDB.

3. XeLabs TokuDB Overview

Project URL: https://github.com/XeLabs/tokudb

Built‑in jemalloc memory allocator

Additional TokuDB performance metrics

Supports Xtrabackup backup

Integrates ZSTD compression

Supports TokuDB binlog_group_commit feature

4. Test Table

Core TokuDB configuration:

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

Table definition:

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 command:

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

The load of 200 million rows finished in 5 min 48 sec, yielding ~574 k rows per second.

File size was 8.5 GB; the TokuDB files occupied 3.5 GB, about a 2‑fold compression.

5. Test Conclusions

On an 8‑core, 8 GB RAM, 500 GB SSD cloud instance, TokuDB consistently achieved ~570 k writes per second. InnoDB required roughly 3‑4 × more time for the same workload.

6. Additional Scenarios

When using an auto‑increment primary key, bulk loading loses its advantage and performance drops dramatically (22 min 44 sec for 200 M rows). Removing the auto‑increment or using a unique index improves speed.

7. Test Environment

CentOS 7 with a custom‑compiled XeLabs TokuDB binary (download link omitted).