573 阿裏雲技術社區[雲棲]

機票業務(單實例 2700萬行/s return)數據庫架構設計 - 阿裏雲RDS PostgreSQL最佳實踐

背景

機票業務的某個模塊，數據量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)

原始SQL性能評估、瓶頸分析

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.002  \set typeid random(0,99999)    
        38.697  select * from test where typeid=:typeid;

2、perf 觀察瓶頸

perf top -ag    
    
  Children      Self  Shared Object              Symbol                             
+   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，cluster化

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.002  \set typeid random(0,99999)    
        23.543  select * 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，聚集化

優化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.002  \set typeid random(0,99999)    
        50.801  select * 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},    
......

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