程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 使用batch insert解決MySQL的insert吞吐量問題,batchinsert

使用batch insert解決MySQL的insert吞吐量問題,batchinsert

編輯:MySQL綜合教程

使用batch insert解決MySQL的insert吞吐量問題,batchinsert


最近使用了一個非常簡單易用的方法解決了業務上的一個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個條件進行合並

  • 隊列中積攢到n個values number後在寫入數據庫,優點是性能最高,缺點是時間不可控,有可能等到第n個需要n秒,這時候業務已經不可接收了。
  • 隊列中積攢1s之後,有多少個就寫入多少個,優點是時間可控,缺點就是values number數目不可能,高並發的情況,可能1s已經積攢上千個values了。
  • 最優的方案其實是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

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved