MySQL 每秒 570000 的寫入，如何實現(xiàn)？
點擊關注上方“SQL數(shù)據(jù)庫開發(fā)”，
設為“置頂或星標”，第一時間送達干貨
一、需求
一個朋友接到一個需求，從大數(shù)據(jù)平臺收到一個數(shù)據(jù)寫入在20億+，需要快速地加載到MySQL中，供第二天業(yè)務展示使用。
二、實現(xiàn)再分析
對于單表20億， 在MySQL運維，說真的這塊目前涉及得比較少，也基本沒什么經(jīng)驗，但對于InnoDB單表Insert 如果內(nèi)存大于數(shù)據(jù)情況下，可以維持在10萬-15萬行寫入。但很多時間我們接受的項目還是數(shù)據(jù)超過內(nèi)存的。這里使用XeLabs TokuDB做一個測試。
三、XeLabs TokuDB 介紹
項目地址：https://github.com/XeLabs/tokudb
相對官方TokuDB的優(yōu)化：
內(nèi)置了jemalloc 內(nèi)存分配
引入更多的內(nèi)置的TokuDB性能指標
支持Xtrabackup備份
引入ZSTD壓縮算法
支持TokuDB的binlog_group_commit特性
四、測試表
TokuDB核心配置：
loose_tokudb_cache_size=4G
loose_tokudb_directio=ON
loose_tokudb_fsync_log_period=1000
tokudb_commit_sync=0
表結(jié)構(gòu)
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?'同噸位人數(shù)',
?UNIQUE?KEY?`u_user_id`?(`user_id`)
)?ENGINE=TokuDB?DEFAULT?CHARSET=utf8
利用load data寫入數(shù)據(jù)
root@localhost?[zst]>LOAD?DATA?INFILE?'/u01/work/134-136.txt'?
INTO?TABLE?user_summary(user_id,?weight,?level,?beat_rate,level_num);
Query?OK,?200000000?rows?affected?(5?min?48.30?sec)
Records:?200000000?Deleted:?0?Skipped:?0?Warnings:?0
計算一下每秒寫入速度：
root@localhost?[zst]>select?200000000/(5*60+48.30);
+------------------------+
|?200000000/(5*60+48.30)?|
+------------------------+
|?574217.6285?|
+------------------------+
1?row?in?set?(0.00?sec)
文件大小：
-rw-r--r--?1?root?root?8.5G?11月?25?20:05?134-136.txt
-rw-r-----?1?mysql?mysql?8.6K?11月?25?20:44?user_summary.frm
-rw-r-----?1?mysql?mysql?3.5G?11月?25?20:51?user_summary_main_229_1_1d_B_0.tokudb
實際文件8.5G，寫入TokuDB大小3.5G，只是接近于一半多點的壓縮量。對于20億數(shù)據(jù)寫入，實際測試在58分鐘多點就可以完成。可以滿足實際需求，另外對于磁盤IO比較好的機器（SSD類盤，云上的云盤），如果內(nèi)存和數(shù)據(jù)差不多情況，這量級數(shù)據(jù)量測試在Innodb里需要添加自增列，可以在3個小多一點完成。從最佳實戰(zhàn)上來看，Innodb和TokuDB都寫入同樣的數(shù)據(jù)，InnoDB需要花大概是TokuDB3-4倍時間。文件大小區(qū)別，同樣20億數(shù)據(jù)：
-rw-r-----?1?mysql?mysql?35G?11月?25?23:29?user2_main_26a_1_1d_B_0.tokudb
-rw-r-----?1?mysql?mysql?176G?11月?26?03:32?user5.ibd
文件大小在5倍大小的區(qū)別。
測試結(jié)論：
利用TokuDB在某云環(huán)境中8核8G內(nèi)存，500G高速云盤環(huán)境，多次測試可以輕松實現(xiàn)57萬每秒的寫入量。
另外測試幾種場景也供大家參考：如果在TokuDB中使用帶自增的主鍵，主鍵無值讓MySQL內(nèi)部產(chǎn)生寫入速度，下降比較明顯，同樣寫入2億數(shù)據(jù)，帶有自建主鍵：
root@localhost?[zst]>CREATE?TABLE?`user3`?(
?->?`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?'同噸位人數(shù)',
?->?`id`?bigint(20)?NOT?NULL?AUTO_INCREMENT,
?->?PRIMARY?KEY?(`id`),
?->?UNIQUE?KEY?`u_user_id`?(`user_id`)
?->?)?ENGINE=TokuDB;
Query?OK,?0?rows?affected?(0.03?sec)
root@localhost?[zst]>LOAD?DATA?INFILE?'/u01/work/134-136.txt'?INTO?TABLE?user3(user_id,?weight,?level,?beat_rate,level_num);
Query?OK,?200000000?rows?affected?(22?min?43.62?sec)
Records:?200000000?Deleted:?0?Skipped:?0?Warnings:?0
同樣的數(shù)據(jù)寫入在主鍵自增無值產(chǎn)生時，不能使用TokuDB的 Bulk loader data特性，相當于轉(zhuǎn)換為了單條的Insert實現(xiàn)，所以效果上慢太多。
關于TokuDB Bulk Loader前提要求，這個表是空表，對于自增列，如自增列有值的情況下，也可以使用。建議實際使用中，如果自增列有值的情況下，可以考慮去除自增屬性，改成唯一索引，這樣減少自增的一些處理邏輯，讓TokuDB能跑地更快一點。另外在Bulk Loader處理中為了追求更快速的寫入，壓縮方面并不是很好。
關于TokuDB Bulk Loader :https://github.com/percona/PerconaFT/wiki/TokuFT-Bulk-Loader
五、測試環(huán)境說明
測試使用CentOS7環(huán)境，編譯的XeLabs TokuDB版本百度云地址：https://pan.baidu.com/s/1qYRyH3I
來自：老葉茶館，作者：吳炳錫
鏈接：https://yq.aliyun.com/articles/278034
——End——
后臺回復關鍵字：1024，獲取一份精心整理的技術干貨 后臺回復關鍵字：進群，帶你進入高手如云的交流群。 推薦閱讀
這是一個能學到技術的公眾號，歡迎關注