187
PostgreSQL 在鐵老大訂單係統中的schemaless設計和性能壓測
標簽
PostgreSQL , UDF , schemaless , 自動建表 , 自動分區 , 訂單查詢 , 用戶訂單查詢 , 席別訂單查詢
背景
數據的流動孕育數據生態，在很多大型係統中，業務係統之間的數據流動是很常見的事情。
例如鐵大哥的訂單數據的流動，可能帶動很多圍繞訂單的業務需求。比如說訂單查詢：
1、按用戶查詢，返回用戶30天內的所有訂單。
2、按坐席和乘車日期查詢，返回這個坐席的售出記錄，看看有沒有退票、有沒有中途票可以加塞等。
以預售30天為例，
假設有20000趟車，20節車廂，200個座位，平均每個座位賣10張票（算上中途票）。 每天的訂單約8000萬筆。
30天約24億訂單記錄數據量。
用PostgreSQL來滿足這類場景，如何設計呢？性能又如何呢？我們關注三個性能指標：
1、生成訂單速度
2、按用戶查詢，返回用戶的所有訂單，平均返回2.4條。(30天，24億記錄，假設有10億用戶，平均每個用戶2.4筆訂單)
3、按坐席和乘車日期查詢，平均返回10條記錄。（每個座位平均每天賣出10張票）
PS：春運數據，2016年春運20天運送旅客3.26億人次。實際上本例的PostgreSQL設計在這個業務中已經完全滿足全年的最高峰需求。
https://baike.baidu.com/item/%E6%98%A5%E8%BF%90
schemaless設計
這個場景是典型的讀多寫少的業務。寫體現在下單、修改訂單。讀體現在查詢。
幾個需要關注的點：
1、訂單數據保留30天。使用時間分區，清數據最方便。同時滿足時間維度的查詢性能要求。
2、高速寫入，高速讀取。
3、按用戶查詢，按坐席和乘車日期查詢。按坐席和乘車日期查詢，不需要特別設計，按時間分區即可。按用戶查詢要達到最好的性能，我們可以來一份冗餘數據，按用戶哈希。
采用schemaless的設計，解決自動建分區表，自動讀寫分區表的問題。
設計表結構
1、建表1，按get_on上車時間分區。
-- 序列，假設為訂單號
create sequence seq cache 1000;
create table train_order (
id int8, -- 訂單號
train_id int, -- 車次
box_id int2, -- 車廂
site_id int2, -- 座位號
station_1 int2, -- 上車站
station_2 int2, -- 下車站
get_on date, -- 乘車日期
uid int8, -- 用戶ID
pay_stat int2 default 1, -- 付款狀態
charge float8, -- 金額
weight int2 default 0, -- 排隊號
window_id int2 default 1, -- 購買渠道
crt_time timestamp default now(), -- 訂單創建時間
mod_time timestamp -- 訂單修改時間
);
-- 索引，提高按坐席查詢的效率
create index idx_train_order_1 on train_order (train_id, box_id, site_id);
冗餘一份數據，按用戶哈希分區，提高按用戶查詢的效率。
2、建表2，按uid用戶ID哈希分區。
create table train_order_u (
id int8, -- 訂單號
train_id int, -- 車次
box_id int2, -- 車廂
site_id int2, -- 座位號
station_1 int2, -- 上車站
station_2 int2, -- 下車站
get_on date, -- 乘車日期
uid int8, -- 用戶ID
pay_stat int2 , -- 付款狀態
charge float8, -- 金額
weight int2 , -- 排隊號
window_id int2 , -- 購買渠道
crt_time timestamp , -- 訂單創建時間
mod_time timestamp -- 訂單修改時間
) ;
-- 索引，提高按用戶查詢的效率
create index idx_train_order_u on train_order_u (uid);
3、預先建分區表，按用戶哈希。
do language plpgsql $$
declare
begin
for i in 0..31
loop
execute 'create table train_order_u'||i||' (like train_order_u including all , check (mod(uid,32)='||i||')) inherits (train_order_u)';
end loop;
end;
$$;
4、schemaless設計來了，創建訂單函數
create or replace function create_order (
v_id int8, -- 訂單號
v_train_id int, -- 車次
v_box_id int2, -- 車廂
v_site_id int2, -- 座位號
v_station_1 int2, -- 上車站
v_station_2 int2, -- 下車站
v_get_on date, -- 乘車日期
v_uid int8, -- 用戶ID
v_pay_stat int2, -- 付款狀態
v_charge float8, -- 金額
v_weight int2, -- 排隊號
v_window_id int2, -- 購買渠道
v_crt_time timestamp
) returns void as $$
declare
sql1 text := '';
sql2 text := '';
tbl1 name := 'train_order_'||to_char(v_get_on,'yyyymmdd'); -- 自動寫入分區表
tbl2 name := 'train_order_u'||mod(v_uid,32);
begin
-- 數據冗餘，一條訂單數據，同時寫入用戶分區表，以及日期分區表。
sql1 := format (
'insert into %I (id, train_id, box_id, site_id, station_1, station_2, get_on, uid, pay_stat, charge, weight, window_id, crt_time) values
(%L, %L, %L, %L, %L, %L, %L, %L, %L, %L, %L, %L, %L)' ,
tbl1, v_id, v_train_id, v_box_id, v_site_id, v_station_1, v_station_2, v_get_on, v_uid, v_pay_stat, v_charge, v_weight, v_window_id, v_crt_time
);
sql2 := format (
'insert into %I (id, train_id, box_id, site_id, station_1, station_2, get_on, uid, pay_stat, charge, weight, window_id, crt_time) values
(%L, %L, %L, %L, %L, %L, %L, %L, %L, %L, %L, %L, %L)' ,
tbl2, v_id, v_train_id, v_box_id, v_site_id, v_station_1, v_station_2, v_get_on, v_uid, v_pay_stat, v_charge, v_weight, v_window_id, v_crt_time
);
execute sql1;
execute sql2;
exception when SQLSTATE '42P01' then
execute format('create table %I (like train_order including all) inherits(train_order)', tbl1); -- 自動建表
execute sql1;
execute sql2;
end;
$$ language plpgsql strict ;
5、schemaless設計來了，修改訂單函數
略，同上寫法。
6、schemaless設計來了，按用戶查詢，返回用戶的所有訂單，平均返回2.4條。
create or replace function get_user_order(v_uid int8) returns setof train_order_u as $$
declare
tbl name := 'train_order_u'||mod(v_uid,32);
begin
return query execute format('select * from %I where uid=%L', tbl, v_uid);
end;
$$ language plpgsql strict;
postgres=# select (get_user_order(100000000)).*;
id | train_id | box_id | site_id | station_1 | station_2 | get_on | uid | pay_stat | charge | weight | window_id | crt_time | mod_time
------------+----------+--------+---------+-----------+-----------+------------+-----------+----------+------------------+--------+-----------+----------------------------+----------
1557799015 | 125 | 4 | 189 | 79 | 16 | 2017-10-21 | 100000000 | 1 | 1197.33886221517 | 1 | 1 | 2017-09-27 18:14:19.076026 |
811630684 | 997 | 18 | 179 | 41 | 12 | 2017-10-14 | 100000000 | 1 | 453.880406067241 | 1 | 1 | 2017-09-27 15:48:09.125487 |
597179567 | 536 | 3 | 177 | 7 | 7 | 2017-10-07 | 100000000 | 1 | 7182.26095053926 | 1 | 1 | 2017-09-27 15:15:02.786637 |
(3 rows)
Time: 0.367 ms
7、schemaless設計來了，按坐席和乘車日期查詢，平均返回10條記錄。
create or replace function get_site_order(v_get_on date, v_train_id int, v_box_id int2, v_site_id int2) returns setof train_order as $$
declare
tbl name := 'train_order_'||to_char(v_get_on,'yyyymmdd');
begin
return query execute format('select * from %I where train_id=%L and box_id=%L and site_id=%L', tbl, v_train_id, v_box_id, v_site_id);
end;
$$ language plpgsql strict;
postgres=# select (get_site_order('2017-10-09', 1999, 3::int2, 1::int2)).* ;
id | train_id | box_id | site_id | station_1 | station_2 | get_on | uid | pay_stat | charge | weight | window_id | crt_time | mod_time
------------+----------+--------+---------+-----------+-----------+------------+-----------+----------+------------------+--------+-----------+----------------------------+----------
2300300781 | 1999 | 3 | 1 | 68 | 14 | 2017-10-09 | 419271707 | 1 | 7061.5369010889 | 1 | 1 | 2017-09-27 20:54:56.710393 |
2234878940 | 1999 | 3 | 1 | 85 | 45 | 2017-10-09 | 885259649 | 1 | 5214.0475622518 | 1 | 1 | 2017-09-27 20:41:22.906565 |
2107427991 | 1999 | 3 | 1 | 35 | 98 | 2017-10-09 | 985856266 | 1 | 8642.02400384098 | 1 | 1 | 2017-09-27 20:12:33.85781 |
1794488978 | 1999 | 3 | 1 | 94 | 20 | 2017-10-09 | 351055777 | 1 | 1718.21027540183 | 1 | 1 | 2017-09-27 19:06:43.643281 |
1374974043 | 1999 | 3 | 1 | 16 | 26 | 2017-10-09 | 923638283 | 1 | 4711.63916029176 | 1 | 1 | 2017-09-27 17:33:47.147368 |
1351805341 | 1999 | 3 | 1 | 85 | 74 | 2017-10-09 | 212355965 | 1 | 4541.93220470985 | 1 | 1 | 2017-09-27 17:28:20.974681 |
1182159676 | 1999 | 3 | 1 | 15 | 31 | 2017-10-09 | 567906740 | 1 | 8566.17715372145 | 1 | 1 | 2017-09-27 16:54:26.478221 |
35506838 | 1999 | 3 | 1 | 1 | 47 | 2017-10-09 | 884099213 | 1 | 983.503066563047 | 1 | 1 | 2017-09-27 13:55:21.280078 |
(8 rows)
Time: 0.391 ms
8、調度清理數據，由於數據隻需要保留30天，所以自動清理。
8.1 用戶哈希表數據清理（清理7天以前的數據）
delete from train_order_u where get_on < current_date - 7;
vacuum train_order_u;
8.2 時間分區訂單表，清理7天前的分區。
drop table train_order_20170920;
.....
9、生成訂單數據壓測。
測試最極端的情況，未來30天的票全部售完。
20000趟車，20節車廂，200個座位，平均每個座位賣10張票。 8000萬訂單/天。
24億數據量。
vi test.sql
select create_order(nextval('seq'),(random()*19999)::int,(random()*19)::int2,(random()*199)::int2,(random()*99)::int2,(random()*99)::int2,current_date+(random()*30)::int,(random()*1000000000)::int8,1::int2,(random()*9999)::float8,1::int2,1::int2,now()::timestamp);
nohup pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -t 37500000 >./log.csv 2>&1 &
性能非常平穩。
......
progress: 5778.0 s, 127956.8 tps, lat 0.500 ms stddev 0.198
progress: 5779.0 s, 128701.9 tps, lat 0.497 ms stddev 0.208
progress: 5780.0 s, 127889.8 tps, lat 0.500 ms stddev 0.195
progress: 5781.0 s, 128157.5 tps, lat 0.499 ms stddev 0.204
progress: 5782.0 s, 128529.6 tps, lat 0.498 ms stddev 0.207
progress: 5783.0 s, 128740.1 tps, lat 0.497 ms stddev 0.190
progress: 5784.0 s, 128211.0 tps, lat 0.499 ms stddev 0.191
progress: 5785.0 s, 128414.9 tps, lat 0.498 ms stddev 0.197
progress: 5786.0 s, 127032.1 tps, lat 0.504 ms stddev 0.201
progress: 5787.0 s, 128371.0 tps, lat 0.499 ms stddev 0.186
progress: 5788.0 s, 128116.8 tps, lat 0.500 ms stddev 0.202
progress: 5789.0 s, 127408.5 tps, lat 0.502 ms stddev 0.207
progress: 5790.0 s, 127691.7 tps, lat 0.501 ms stddev 0.204
progress: 5791.0 s, 128833.5 tps, lat 0.497 ms stddev 0.195
progress: 5792.0 s, 128363.8 tps, lat 0.499 ms stddev 0.204
progress: 5793.0 s, 128307.7 tps, lat 0.499 ms stddev 0.203
progress: 5794.0 s, 128599.4 tps, lat 0.498 ms stddev 0.186
......
寫入總數據量48億，占用空間 549 GB。
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+-----------+---------+-------+-----------------------+--------+------------+--------------------------------------------
postgres | postgres | SQL_ASCII | C | C | | 549 GB | pg_default | default administrative connection database
10、查詢壓測
10.1、按用戶查詢，返回用戶的所有訂單，平均返回2.4條。
vi test1.sql
\set uid random(1,1000000000)
select * from get_user_order((:uid)::int8);
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: 25440118
latency average = 0.302 ms
latency stddev = 4.141 ms
tps = 211975.134822 (including connections establishing)
tps = 212024.097764 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.002 \set uid random(1,1000000000)
0.301 select * from get_user_order((:uid)::int8);
10.2、按坐席和乘車日期查詢，平均返回10條記錄。
vi test2.sql
\set train_id random(0,19999)
\set box_id random(0,19)
\set site_id random(0,199)
select * from get_site_order(current_date+(random()*30)::int, (:train_id)::int, (:box_id)::int2, (:site_id)::int2);
pgbench -M prepared -n -r -P 1 -f ./test2.sql -c 64 -j 64 -T 120
transaction type: ./test2.sql
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 64
duration: 120 s
number of transactions actually processed: 25650025
latency average = 0.299 ms
latency stddev = 0.047 ms
tps = 213746.298310 (including connections establishing)
tps = 213763.615290 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.002 \set train_id random(0,19999)
0.000 \set box_id random(0,19)
0.000 \set site_id random(0,199)
0.302 select * from get_site_order(current_date+(random()*30)::int, (:train_id)::int, (:box_id)::int2, (:site_id)::int2);
小結
1、生成訂單速度，約 12.8萬行/s。
2、按用戶查詢，返回用戶的所有訂單，平均返回2.4條。約 21.2萬 tps。
3、按坐席和乘車日期查詢，平均返回10條記錄。約 tps。
4、表格：
| 訂單記錄數 | 空間占用 | 表占用 | 索引占用 |
|---|---|---|---|
| 48 億 | 549 GB | 449 GB | 100 GB |
| CASE | 返回記錄數 | TPS | 響應速度 |
|---|---|---|---|
| 生成訂單 | - | 12.8 萬 | 0.5 毫秒 |
| 按用戶查詢訂單 | 3 條 | 21.2 萬 | 0.3 毫秒 |
| 按坐席和日期查詢訂單 | 10 條 | 21.3 萬 | 0.3 毫秒 |
5、目前分區性能最好的還是pg_pathman, 10內置的分區表寫入、查詢方麵的性能不佳。如果要快速寫入24億，建議直接寫分區子表，單步寫入可以達到 35萬條/s 左右。
為了達到均衡的目的，本方案使用了schemaless方案，無需建表，無需維護分區。犧牲一些些性能。
6、其他schemaless設計的案例：
《PostgreSQL schemaless 的實現(類mongodb collection)》
《PostgreSQL 時序最佳實踐 - 證券交易係統數據庫設計 - 阿裏雲RDS PostgreSQL最佳實踐》
《行為、審計日誌 (實時索引/實時搜索)建模 - 最佳實踐 2》
7、采用schemaless的方案，表分區可以更加自由，可以更細，因為直擊目標表。按時間分區的訂單表，可以再按train_id, site_id進行二級分區（例如32*8個分區，當未來30天的票全部售完的情況下，每個分區31萬數據，cluster很快很快。）。將單表記錄數降到更低，采用train_id+site_id索引進行cluster，使得數據密集存儲，查詢時可以掃描更少的數據塊。
8、采用schemaless的方案，表分區可以更加自由，可以更細，因為直擊目標表。按用戶ID哈希分區的訂單表，模數可以設更大，例如設置為2048，當未來30天的票全部售完的情況下，單表則降到120萬記錄。cluster很快很快。將單表記錄數降到更低，采用uid索引進行cluster，使得數據密集存儲，查詢時可以掃描更少的數據塊。
9、監測pg_stats.correlation，當相關性絕對值低於0.8時，觸發cluster。可以使得數據自動維持在一個較高密集度。
最後更新：2017-10-28 23:04:00