最近使用了一個非常簡單易用的方法解決了業務上的一個insert吞吐量的問題,在此總結一下。
首先我們明確一下,insert吞吐量其實並不是指的IPS(insert per second),而是指的RPS(effect rows per second)。
其次我們再說一下batch insert,其實顧名思義,就是批量插入。這種優化思想是很基本的,MySQL中最出名的應用就是group commit。
簡單的來說,就是將SQL A 變成 SQL B
SQL A : insert into table values ($values); SQL B : insert into table values ($values),($values)...($values);
下面,我們來看看這種異常簡單的改動會帶來什麼樣子的變化。
測試環境交代:單id的表結構,10w個int values,本地使用socket連接MySQL server,使用shell單進程測試。
首先,我們看下使用SQL A將10w個int values插入到test表中所需的耗時,耗時1777秒。
real 29m37.090s user 9m11.705s sys 5m0.762s
然後,我們看下使用SQL B(每次insert,插入10 values)將10w個int values插入到test表中所需的耗時,耗時53秒
real 0m53.871s user 0m19.455s sys 0m6.285s
這是整整近33倍的時間提升。這部分性能提升的原因在於以下幾點:
1、每次和MySQL server建立連接都需要經過各種初始化、權限認證,語法解析等等多個步驟,需要消耗一定的資源。
2、更新一個values和更新n個values耗時基本一致。(下面對比一下insert 單values核insert 10 values的profile耗時)
單values: +------------------------------+----------+ | Status | Duration | +------------------------------+----------+ | starting | 0.000056 | | checking permissions | 0.000010 | | Opening tables | 0.000034 | | System lock | 0.000010 | | init | 0.000011 | | update | 0.000061 | | Waiting for query cache lock | 0.000003 | | update | 0.000015 | | end | 0.000003 | | query end | 0.000053 | | closing tables | 0.000009 | | freeing items | 0.000021 | | logging slow query | 0.000002 | | cleaning up | 0.000003 | +------------------------------+----------+ 10 values: +------------------------------+----------+ | Status | Duration | +------------------------------+----------+ | starting | 0.000061 | | checking permissions | 0.000008 | | Opening tables | 0.000027 | | System lock | 0.000008 | | init | 0.000012 | | update | 0.000073 | | Waiting for query cache lock | 0.000003 | | update | 0.000010 | | end | 0.000008 | | query end | 0.000053 | | closing tables | 0.000010 | | freeing items | 0.000021 | | logging slow query | 0.000002 | | cleaning up | 0.000003 | +------------------------------+----------+
但是,是否values積攢的越多,效率越高嗎? 答案自然是否定的,任何優化方案都不會是純線性的,肯定會在某個條件下出現拐點。
我們按照不同的values number進行測試,分別為1、10、50、100、200、500、1000、5000、10000.
從下圖我們可以看出,隨著values number的增加,耗時先是急劇下降,從1777s變成53s,然後在增加values number就不會有太大的變化,直到values number超過200,最後的10000個values number耗時達到了2分鐘。
從下圖我們可以看到隨著values numbers的增加,QPS(藍線)先是猛增,然後下降,最終小於1/s。而RPS(綠線)隨著增加猛增到一個高level,然後隨著增加逐步下降,超過5000個values number之後開始急劇下降。
另,最關鍵的是, QPS最高峰和RPS的最高峰並不在同一個values number下,也就是說QPS最高的時候並不代表著insert的吞吐量就最高 。
在我這個簡單測試場景中,values number最合適的值是50,和單values對比,耗時減少 97% ,insert吞吐量提升 36倍 。
而這個值和表結構和字段類型及大小都有關系。需要根據不同的場景進行測試之後才可以得出,但是普遍來說,50-100是比較推薦的考慮值。
至於這個如何實現,只要前端寫入的時候加入隊列即可,可以按照2個條件進行合並
總結:
1、使用batch insert可以提高insert的吞吐量。
2、疊加的values number需要根據實際情況測試得出。
3、同時使用個數和時間控制閥值。
附簡單測試的記錄值:
ValuesNum
Time
QPS
Rows
1
1777
56
56
10
53
188
1886
50
49
40
2040
100
50
19
2000
200
51
10
1960
500
57
3
1754
1000
60
2
1666
5000
69
0.3
1449
10000
133
0.07
751