機票商務(單一實例 2700萬行/s return)資料庫架構設計 - 阿裡雲RDS PostgreSQL最佳實踐

摘要： 幕後機票商務的某個模組，資料量10億+，寫、更新、移除量較低。根據KEY查詢一些資料，每次查詢返回1萬條左右的記錄。就是這樣簡單的需求，商務方發現讀成為了巨大的瓶頸，每次返回1萬條，100個並行要求，每秒就是100萬條（500MB左右），主要的瓶頸：1、網路是個較大的開銷。

機票商務的某個模組，資料量10億+，寫、更新、移除量較低。根據KEY查詢一些資料，每次查詢返回1萬條左右的記錄。

就是這樣簡單的需求，商務方發現讀成為了巨大的瓶頸，每次返回1萬條，100個並行要求，每秒就是100萬條（500MB左右），主要的瓶頸：

1、網路是個較大的開銷。

2、不同KEY的資料可能是分散存放的，存在查詢時的IO放大，可能有一定的效能影響。

3、每次要求的返回記錄數較多，資料庫search buffer叫用可能開銷會上升。

就這幾個問題，我們來看看如何優化或解決商務方的問題。

1、建表

create table test(

id int,

info text,--一些屬性，我這裡用一個欄位代表它

typeid int,--類別，也是用戶的查詢遮罩條件，約10萬個類別，每個類別1萬條記錄，總共10億記錄。

crt_time timestamp,--建立時間

mod_time timestamp--修改時間

);

2、灌入測試資料

insert into test select generate_series(1,1000000000), 'test', random()*99999, now();

3、建立索引

create index idx_test_typeid on test (typeid);

4、原始SQL要求

select * from test where typeid=?;

約返回1萬記錄。

postgres=# select schemaname, tablename, attname, correlation from pg_stats where tablename='test';

schemaname | tablename | attname| correlation

------------+-----------+----------+-------------

postgres| test| id|1

postgres| test| info|1

postgres| test| typeid|0.0122783

postgres| test| crt_time |1

postgres| test| mod_time |

(5 rows)

通過pg_stats可以看到typeid和實體儲存體的線性關聯性才0.012，非常分散。

按TYPEID存取時，IO放大很嚴重，也就是說1萬條記錄可能分散在1萬個資料區塊中。

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from test where typeid =1;

QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------

Index Scan using idx_test_typeid on postgres.test(cost=0.57..13343.21 rows=10109 width=29) (actual time=0.029..14.283 rows=9935 loops=1)

Output: id, info, typeid, crt_time, mod_time

Index Cond: (test.typeid = 1)

Buffers: shared hit=9959-- typeid=1的記錄分散在9959個資料區塊中

Planning time: 0.085 ms

Execution time: 14.798 ms

(6 rows)

1、壓測

vi test.sql

set typeid random(0,99999)

select * from test where typeid=:typeid;

壓測結果，TPS 1653。

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 120

transaction type: ./test.sql

scaling factor: 1

query mode: prepared

number of clients: 64

number of threads: 64

duration: 120 s

number of transactions actually processed: 198445

latency average = 38.699 ms

latency stddev = 7.898 ms

tps = 1653.239177 (including connections establishing)

tps = 1653.525600 (excluding connections establishing)

script statistics:

- statement latencies in milliseconds:

0.002set typeid random(0,99999)

38.697select * from test where typeid=:typeid;

2、perf觀察瓶頸

perf top -ag

ChildrenSelfShared ObjectSymbol

+15.31%15.19%postgres[.] hash_search_with_hash_value

+14.48%8.78%postgres[.] heap_hot_search_buffer

+9.95%2.26%[kernel][k] page_fault

+9.44%8.24%postgres[.] heap_page_prune_opt

+7.67%0.02%[kernel][k] do_page_fault

+7.62%0.21%[kernel][k] __do_page_fault

+6.89%0.41%[kernel][k] handle_mm_fault

+6.87%6.80%postgres[.] PinBuffer

+4.32%0.18%[kernel][k] __do_fault

+4.03%4.00%postgres[.] LWLockAcquire

+3.83%0.00%[kernel][k] system_call_fastpath

+3.17%3.15%libc-2.17.so[.] __memcpy_ssse3_back

+3.01%0.16%[kernel][k] shmem_fault

+2.85%0.13%[kernel][k] shmem_getpage_gfp

1、PostgreSQL提供了一個cluster的功能，可以將表按索引進行CLUSTER，即拌和。

效果是這個索引對應列（或多列）與物理順序的線性關聯性變成1或-1，也就是線性完全符合，那麼在按這個欄位或這些欄位進行條件遮罩時，掃描的堆表資料區塊大幅度降低。

postgres=# cluster test using idx_test_typeid;

postgres=# d test

Table "postgres.test"

Column|Type| Collation | Nullable | Default

----------+-----------------------------+-----------+----------+---------

id| integer|||

info| text|||

typeid| integer|||

crt_time | timestamp without time zone |||

mod_time | timestamp without time zone |||

Indexes:

"idx_test_typeid" btree (typeid) CLUSTER

2、測試cluster後，按typeid遮罩資料，只需要掃描96個資料區塊了。SQL的回應時間也從14.8毫秒降到了1.9毫秒。

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from test where typeid =1;

QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------

Index Scan using idx_test_typeid on postgres.test(cost=0.57..13343.21 rows=10109 width=29) (actual time=0.011..1.413 rows=9935 loops=1)

Output: id, info, typeid, crt_time, mod_time

Index Cond: (test.typeid = 1)

Buffers: shared hit=96

Planning time: 0.039 ms

Execution time: 1.887 ms

(6 rows)

3、壓測，TPS 2715。相比原始效能升階了 64%。

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 120

transaction type: ./test.sql

scaling factor: 1

query mode: prepared

number of clients: 64

number of threads: 64

duration: 120 s

number of transactions actually processed: 326188

latency average = 23.546 ms

latency stddev = 7.793 ms

tps = 2715.409760 (including connections establishing)

tps = 2715.677062 (excluding connections establishing)

script statistics:

- statement latencies in milliseconds:

0.002set typeid random(0,99999)

23.543select * from test where typeid=:typeid;

4、perf觀察瓶頸

用戶態的叫用不在TOP裡面。

+14.30%0.00%[kernel][k] system_call_fastpath

+9.62%1.26%[kernel][k] page_fault

+8.35%0.01%[kernel][k] do_page_fault

+8.27%0.14%[kernel][k] __do_page_fault

+6.81%0.37%libc-2.17.so[.] sysmalloc

+6.48%0.10%[kernel][k] __alloc_pages_nodemask

+5.84%0.40%[kernel][k] handle_mm_fault

+5.84%0.05%libpthread-2.17.so[.] __libc_send

+5.83%5.79%libc-2.17.so[.] __memcpy_ssse3_back

+5.74%0.03%libpthread-2.17.so[.] __libc_recv

優化1小結

1、優化手段1沒有涉及到降低網路開銷的優化。

2、使用cluster後，完全規避了IO放大的問題。

3、但是每次要求返回的記錄數與原來一樣，對資料庫search buffer沒有起到效果。

4、聚集動作是靜態動作，資料庫並不會一直維持這個狀態。

不過PG可以設定fillfactor，使得更新後的組建儘量在本期資料區塊。這種方法對於更新很有效，只要對應的搜尋KEY不變更，那麼線性關聯性可以一直被維持。對於新增資料無效。所以cluster特別適合相對靜態資料，或者時間維度上，舊版資料基本不變更的場景，可以使用時間磁碟分割表格，對舊資料實施CLUSTER，許諾就資料的線性關聯性。

alter table test set (fillfactor=80);

優化2的目標和1類似，但是將資料聚集為單條，同時升階資料的壓縮比，不過是資料庫端壓縮，所以對網路需求的降低並沒有效果。

1、聚集，因為更新少，所以我們可以將多條記錄聚集為一條記錄。

create table test_agg (typeid int, content jsonb);

insert into test_agg select typeid, jsonb_agg(jsonb_build_object('id',id,'info',info,'crt_time',crt_time,'mod_time',mod_time)) from test group by typeid;

create index idx_test_agg_1 on test_agg(typeid);

2、查詢要求

select * from test_agg where typeid=?

3、增、刪、改

JSON類型的動作函數如下：

https://www.postgresql.org/docs/10/static/functions-json.html

4、優化後的效能指標

壓測，效能並沒有升階

vi test1.sql

set typeid random(0,99999)

select * from test_agg where typeid=:typeid;

pgbench -M prepared -n -r -P 1 -f ./test1.sql -c 64 -j 64 -T 120

transaction type: ./test1.sql

scaling factor: 1

query mode: prepared

number of clients: 64

number of threads: 64

duration: 120 s

number of transactions actually processed: 151156

latency average = 50.803 ms

latency stddev = 2.913 ms

tps = 1258.934362 (including connections establishing)

tps = 1259.301582 (excluding connections establishing)

script statistics:

- statement latencies in milliseconds:

0.002set typeid random(0,99999)

50.801select * from test_agg where typeid=:typeid;

優化2小結

效能並沒有升階，轉換為JSONB類型後，每個ELEMETE都增加了頭部資訊，所以網路傳輸的空間實際變大了。

......

{"id": 172264479, "info": "test", "crt_time": "2017-07-27T20:41:32.365209", "mod_time": null},

{"id": 172304687, "info": "test", "crt_time": "2017-07-27T20:41:32.365209", "mod_time": null},

......

這個優化方法並沒有賺到。

PostgreSQL支援SSL連結，通過SSL支援壓縮和加密傳輸。

如果傳輸頻寬有限，使用這種鏈路是非常不錯的選擇，但是會消耗一部分用戶端和資料庫端的CPU資源。

有一些例子：

《PostgreSQLSSL鏈路壓縮例子》

《PostgreSQLssl ciphers performance 比較》

這個優化方法簡單有效，但是需要投入一些資源，PostgreSQL支援兩種備庫，物理、邏輯備庫。

物理備庫唯讀，延遲低，不管交易多大，延遲都在毫秒級。但是物理備庫只能全庫複製。

邏輯備庫可寫，同時可以訂閱部份資料，但是延遲較高（通常一個訂閱通道的速率在3萬行/s，一個實例可以支援多個訂閱通道，比如每個表一個訂閱通道）。

同時建議資料庫節點與APPLICATION節點的網路儘量靠近，甚至將備庫部署在商務服務器都是贊許的。

參考文件：

《PostgreSQL10 流式物理、邏輯主從 最佳實踐》

按使用者切分，將資料切分到多個資料庫實例。

按照優化手段1的指標，每個節點可以提供1.3GB/s的輸出資料流量，如果切分到16個節點，可以支援21GB/s的輸出資料流量。完全不用考慮備庫。

中介層可以考慮使用plproxy，中介軟體等方法。

《PostgreSQL最佳實踐 - 水準分庫(基於plproxy)》

https://github.com/go-pg/sharding

參考文件

《PostgreSQL9.6 sharding based on FDW & pg_pathman》

1、原來單條的隱藏，用戶每次要求，返回1萬條記錄，所以主機的網路頻寬，資料庫的資料存取離散IO的放大都是較大的效能阻礙因素。

使用cluster的方法，將資料按KEY存放，完全關閉IO放大的問題，效能升階非常明顯。

使用FILLFACTOR，可以讓資料的更新儘量在本期資料區塊完成，從而不破壞cluster的順序。解決UPDATE引入的破壞線性關聯性問題。

2、通過聚集(cluster)的方法，將使用者需要存取的資料合併成單行(或者按順序存放)，減少掃描的資料區塊。查詢效率有大幅升階。

通過擴充頻寬或者上線少量的備庫就可以滿足商務方的需求。

3、PostgreSQL支援多種彙總方法，陣列、KV、JSON。

但是彙總的方法帶來另一個問題，資料的DML變得很麻煩。

4、通過聚集，被查詢的資料靠在一起了，使得資料壓縮比更高，同時關閉了原來的IO放大的問題，還可以減少多條記錄引入的代碼跳躍額外開銷。

5、聚集後，資料的增、刪、改可以通過UDF來實現。PostgreSQL的plpgsql功能很強大，類似Oracle的PL/SQL。同時PostgreSQL還支援pljava,plpython等UDF語言，方便其他的開發人員使用。

最後，推薦的優化方法：

1、cluster

2、網路壓縮

3、讀寫分離

4、sharding

建議的優化群組1+4，或者1+3。

一些可供選擇的架構：

1、一個資料庫隱藏全量資料，提供讀寫。使用邏輯訂閱，將資料分身，拆成多份，提供讀寫。

2、一個資料庫隱藏全量資料，提供讀寫。使用邏輯訂閱，將資料分身，拆成多份，提供讀寫。採用級聯邏輯訂閱者式，建立其他讀寫邏輯訂閱庫。

3、一個資料庫隱藏全量資料，提供讀寫。使用邏輯訂閱，將資料分身，拆成多份，提供讀寫。採用級聯物理流複製方式，建立其他鏡像唯讀備庫。

4、一個資料庫隱藏全量資料，提供讀寫。採用物理流複製方式，建立一堆鏡像唯讀備庫。

5、一個資料庫隱藏全量資料，提供讀寫。採用物理流複製方式，建立一堆鏡像唯讀備庫。採用級聯物理流複製方式，建立其他鏡像唯讀備庫。

6、前端shard，多個資料庫隱藏全量資料，提供讀寫。使用邏輯訂閱，完全鏡像，提供讀寫。

7、前端shard，多個資料庫隱藏全量資料，提供讀寫。使用邏輯訂閱，完全鏡像，提供讀寫。採用級聯邏輯訂閱者式，建立其他讀寫邏輯訂閱庫。

8、前端shard，多個資料庫隱藏全量資料，提供讀寫。採用物理流複製方式，建立唯讀備庫。採用級聯物理流複製方式，建立其他鏡像唯讀備庫。

9、前端shard，多個資料庫隱藏全量資料，提供讀寫。採用物理流複製方式，建立一堆唯讀備庫。

《PostgreSQL聚集隱藏 與BRIN索引 - 高並行行為、曲目類大吞吐資料查詢場景解說》

《PostgreSQL10 流式物理、邏輯主從 最佳實踐》

sharding中介軟體

https://github.com/dangdangdotcom/sharding-jdbc

https://github.com/go-pg/sharding/

《PostgreSQL最佳實踐 - 水準分庫(基於plproxy)》

相關產品：

1. 雲資料庫RDS

2. 安全管家

3. 資料管理

4. 雲端服務器ECS