最近有個新專案，它的特性跟以往我接觸的都不大一樣，就是它有很大量的 http POST 的需求。POST 當然是比 GET 難 scale 許多，尤其這次的 POST 會有許多新增資料。在處理 POST 之前，我得先研究新增資料撐不撐的住。資料庫如果吃不動，http 調再好也沒用。依據我之前的經驗，這個需求選擇 Cassandra 是比較合適的，NoSQL 強就是強在可以處理大量寫入。不過我很好奇 Postgresql 資料庫能不能吃下大量的 insert 呢？如果可以的話，那就不用處理多種資料庫混合的環境，架構上會輕很多。
所以我就進行了所謂的 micro benchmark，微性能測試的結果通常沒有絕對性，不過有個參考的基準點比什麼都不知道還好。我測試的資料結構是類似這樣：
CREATE TABLE Log (
key1 VARCHAR NOT NULL,
key2 VARCHAR NOT NULL,
data1 VARCHAR,
data2 VARCHAR NOT NULL,
data3 VARCHAR,
...
data21 REAL,
PRIMARY KEY (key1, key2)
)
這個 Log 表使用兩個複合鍵，共有 23 個欄位，大部份是 varchar，少部份是 integer/real，欄位都沒有外鍵，也沒有 constraint。23 個欄位是我們的需求，它的 insert 效能如何呢？
測試環境
- 筆電 Vaio, Intel 4 core 8 hyperthreading
- Intel 250G SSD
- Postgresql 9.3
- Ubuntu 14.04 LTS
- Java 8
- Spring JdbcTemplate + BoneCP 0.8.0
- 一共新增 10 萬筆 Log
OK，這是在筆電 Linux 上，用 jdbc 新增 10 萬筆到 Postgresql 的測試，初步結果是：
Round 1: Table with 23 columns
| No. | Thread | Sync Setting | Result (TPS) |
|---|---|---|---|
| 1 | 8 | sync on | 2346 |
| 2 | 128 | sync on | 4601 |
| 3 | 8 | sync off, delay commit 1ms | 4999 |
| 4 | 128 | sync off, delay commit 1ms | 4837 |
在幾個參數的排列組合下，得到大小 2~4千多的 TPS (每秒交易次數)。Thread 這參數代表同時間有多少個 thread 同時下 insert，Sync Settings 則是 postgresql.conf 的參數調整：
# in /etc/postgresql/9.3/main/postgresql.conf
synchronous_commit = off
commit_delay = 1000 # in microseconds
postgresql 預設每一次交易 commit 時，資料就一定寫到 disk 上。如果將 synchronous_commit 設為 off，以及 commit_delay = 1000，這表示每隔 1 milli-seconds 才會真的寫入 disk。延後寫入資料庫通常可以得到比較好的寫入效能，這是 Postgresql 在調校時常用的參數。但是延後寫入會有資料丟失的風險，例如主機如果突然斷電的話，那有可能上個 milli-second 的資料因為沒寫入 disk 就都消失了。所以這是犧牲資料完整性換取效能的作法。
好，回過頭來看上表的結果，我調了 sync on/off，也調了同時寫入的 thread 數，一開始只有到 2000 TPS，將 thread 調高後，效能加大了一倍。如果改調 sync off，同樣是 8 threads 下，也是有一倍的效能。而兩者的調校都打開，效能反而掉下來。
這個結果我這樣解讀：將 sync 關掉後，每個 insert 的速度加快了，所以很少的 thread 也可以有大的吞吐量，如果 sync 打開，則 insert 速變慢，會卡住 thread，所以加大 thread 數可有效提升效能。當然加大 thread 會有效果，它的前提是資料庫還能夠吃的下，以這個測試例子就是我的硬碟最多能撐到近 5000 的 TPS。
這個結果我不是很滿意，在 SSD 上這樣的效能我覺得有點遜，正統的方式調不動，我就開始亂改東西了，首先是將 23 個欄位大部份都 insert null，這樣應該會變快吧！實際測一下嚇到我了，null 值越多反而效能更差！掉了 30% 的速度。這實在是違反常理，我覺得這調整方向錯了，所以這問題我先放一邊，改成減少欄位數。
合併欄位為單一 JSON
減少欄位數結果有效！效能提升很多，但資料又不能不記，所以我改採將一些不重要的欄位合併到一個 JSON 欄位 (Postgresql 9.3 開始支援儲存與查詢 JSON type)，最後將原本 23 個欄位減少到 11 個。這一次結果如何呢？
Round 2: Table with 10 columns + 1 merged Json column
| No. | Thread | Sync Setting | Result (TPS) |
|---|---|---|---|
| 5 | 8 | sync on | 2207 |
| 6 | 128 | sync on | 11800 |
| 7 | 128 | sync off, delay commit 100ms | 12374 |
| 8 | 128 | sync off, delay commit 1ms | 12789 |
| 9 | 8 | sync off, delay commit 1ms | 14295 |
| 10 | 8 | sync off, delay commit 0.1ms | 14423 |
除了第五組的結果外，其他的測試數據都破萬了。第五組的結果與第一組的結果類似，所以它的問題是出在程式端寫入不夠快，而非達到資料庫的瓶頸。而加大 thread 數或是 sync 關閉都得到很好的結果。而其中第 9、10 組的結果很好，不需要用大量的 thread 就能達到 14000 TPS。這個測試也順便測了幾種 delay_commit 的組合，結果是它的大小在這個測試條件下沒差別。
第二輪的測試與第一輪的差別在於欄位數，一個是 11、另一個是 23，不過總資料量沒少，我只是將 13 個欄位匯整進一個很大的 JSON 欄位而已。但成果是效能增進了近三倍，我不是很了解 Postgresql 內部的運作原理，大概只能猜它每個欄位的驗證都很繁重，所以欄位數量的改變速度可以差很多。如果有人知道原因的話請麻煩指教。
基於第二輪的結果，我們新專案暫時會用一個大 JSON 的欄位取代零星散佈的多個欄位，當然這是我們的需求允許這樣存才可以這樣調校的，一般應該不會用到這招。
小結
微測試通常是不準的，像是上面的表格內有快幾倍的數據，但可能換個條件再測結果就會差很多。這一輪的測試只能大概給自己一個基準點。不過，我最少知道了調整欄位數量與synchronous_commit 會真的影響 insert 的速度。也發現了一些違反常識的結果：1. 使用一個很大的 JSON 欄位不見得會下降效能；2. 很多欄位在 insert 時設為 null 則有可能降速。它們的真正原因還有待未來研究。