標簽

PostgreSQL , 門禁廣告 , 數組 , 范圍類型 , 搶購 , 排他約束 , 大盤分析 , 廣告查詢 , 火車票

背景

搶火車票是很有意思的一個課題,對IT人的智商以及IT系統的健壯性,尤其是數據庫的功能和性能都是一種挑戰.

我記得很多年前寫過一篇PostgreSQL varbit和火車票相關的文章,翻出來看看,發現又和BIT有關.

回顧一下我之前寫的

《基于 阿里云 RDS PostgreSQL 打造實時用戶畫像推薦系統》

《門禁廣告銷售系統需求剖析 與 PostgreSQL數據庫實現》

PostgreSQL的bit功能還是很強大的,阿里云RDS PostgreSQL的bitpack也是用戶實際應用中的需求提煉的新功能,大伙一起來給阿里云提需求.

打造屬于國人的PostgreSQL.

正文

在PostgreSQL 中可以使用varbit存儲比特位, 下面模擬一個簡單的應用場景.

馬上春節了, 火車票又到了銷售旺季, 一票難求依舊.

下面就以火車票銷售為例來介紹一下PostgreSQL varbit類型的用法.

測試環境 :

PostgreSQL 9.2.1

測試表 :

列車信息表 :

create table train

(id int primary key, --主鍵

go_date date, -- 發車日期

train_num name, -- 車次

station text[] -- 途徑站點

);

車廂或bucket信息表 :

create table train_bucket

(id int primary key, --主鍵

tid int references train (id), -- 關聯列車ID

bno int, -- 車廂或bucket號

sit_level text, -- 席別

sit_cnt int, -- 該車廂或bucket的座位總數

sit_remain int, -- 剩余座位

sit_bit varbit -- 座位BIT位, 已售座位用1表示, 未售座位用0表示

);

位置信息表 :

create table train_sit

(id serial8 primary key, -- 主鍵

tid int references train (id), --關聯列車ID

tbid int references train_bucket(id), --關聯bucket表ID

sit_no int, -- 座位號, 來自train_bucket.sit_bit的位置信息.

station_bit varbit -- 途徑站點組成的BIT位信息, 已售站點用1表示, 未售站點用0表示.

);

創建索引 :

create index idx_train_bucket_sit_remain on train_bucket(sit_remain) where sit_remain>0;

create index idx_train_sit_station_bit on train_sit (station_bit) where station_bit<>repeat('1', 13)::varbit;

插入測試數據, 1趟火車, 途徑14個站點.

insert into train values (1, '2013-01-20', 'D645', array['上海南','嘉興','杭州南','諸暨','義烏','金華','衢州','上饒','鷹潭','新余','宜春','萍鄉','株洲','長沙']);

插入測試數據, 共計200W個車廂或bucket, 每個車廂98個位置.

insert into train_bucket values (generate_series(1,1000000), 1, generate_series(1,1000000), '一等座', 98, 98, repeat('0',98)::varbit);

insert into train_bucket values (generate_series(1000001,2000000), 1, generate_series(1000001,2000000), '二等座', 98, 98, repeat('0',98)::varbit);

創建取數組中元素位置的函數 :

create or replace function array_pos (a anyarray, b anyelement) returns int as $$

declare

i int;

begin

for i in 1..array_length(a,1) loop

if b=a[i] then

return i;

end if;

i := i+1;

end loop;

return null;

end;

$$ language plpgsql;

創建購票函數 :

create or replace function buy

(

inout i_train_num name,

inout i_fstation text,

inout i_tstation text,

inout i_go_date date,

out o_slevel text,

out o_bucket_no int,

out o_sit_no int,

out o_order_status boolean

)

returns record as $$

declare

curs1 refcursor;

curs2 refcursor;

v_row int;

v_station text[];

v_train_id int;

v_train_bucket_id int;

v_train_sit_id int;

v_from_station_idx int;

v_to_station_idx int;

v_station_len int;

begin

set enable_seqscan=off;

v_row := 0;

o_order_status := false;

select array_length(station,1), station, id, array_pos(station, i_fstation), array_pos(station, i_tstation)

into v_station_len, v_station, v_train_id, v_from_station_idx, v_to_station_idx

from train where train_num=i_train_num and go_date = i_go_date;

if ( found and array_pos(v_station, i_fstation) is not null

and array_pos(v_station, i_tstation) is not null

and array_pos(v_station, i_fstation) < array_pos(v_station, i_tstation)

) then

else

o_order_status := false;

return;

end if;

open curs2 for select tid,tbid,sit_no from train_sit

where (station_bit & bitsetvarbit(repeat('0', v_station_len-1)::varbit, v_from_station_idx-1, v_to_station_idx-v_from_station_idx, 1)) = repeat('0', v_station_len-1)::varbit

and station_bit <> repeat('1', v_station_len-1)::varbit

-- and ctid not in (select locked_row from pgrowlocks('train_sit')) -- 耗時約300毫秒, 用它來解決熱點鎖等待不劃算.

limit 1

for update nowait; -- 也可不加nowait, 加了的話如果獲取鎖失敗將返回55P03異常, 需要程序重新提交

loop

fetch curs2 into v_train_id,v_train_bucket_id,o_sit_no;

if found then

update train_sit set station_bit=bitsetvarbit(station_bit, v_from_station_idx-1, v_to_station_idx-v_from_station_idx, 1)

where current of curs2;

GET DIAGNOSTICS v_row = ROW_COUNT;

if (v_row = 1) then

select sit_level, bno into o_slevel, o_bucket_no from train_bucket where id=v_train_bucket_id;

close curs2;

o_order_status := true;

return;

end if;

else

close curs2;

exit;

end if;

end loop;

v_row := 0;

open curs1 for select id, tid, strpos(sit_bit::text,'0'), sit_level, bno from train_bucket

where sit_remain>0

-- and ctid not in (select locked_row from pgrowlocks('train_bucket')) -- 耗時約300毫秒, 用它來解決熱點鎖等待不劃算.

limit 1

for update nowait; -- 也可不加nowait, 加了的話如果獲取鎖失敗將返回55P03異常, 需要程序重新提交.

loop

fetch curs1 into v_train_bucket_id, v_train_id, o_sit_no, o_slevel, o_bucket_no;

if found then

update train_bucket set sit_bit = set_bit(sit_bit, strpos(sit_bit::text,'0')-1, 1), sit_remain = sit_remain-1

where current of curs1;

GET DIAGNOSTICS v_row = ROW_COUNT;

if (v_row = 1) then

close curs1;

exit;

end if;

else

close curs1;

exit;

end if;

end loop;

if v_row = 1 then

insert into train_sit(tid,tbid,sit_no,station_bit)

values (

v_train_id,

v_train_bucket_id,

o_sit_no,

bitsetvarbit(repeat('0', v_station_len-1)::varbit, v_from_station_idx-1, v_to_station_idx-v_from_station_idx, 1)

);

o_order_status := true;

return;

else

o_order_status := false;

return;

end if;

exception

when others then

o_order_status := false;

return;

end;

$$ language plpgsql;

測試 :

digoal=# select * from buy('D645','杭州南','宜春','2013-01-20');

i_train_num | i_fstation | i_tstation | i_go_date | o_slevel | o_bucket_no | o_sit_no | o_order_status

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

D645 | 杭州南 | 宜春 | 2013-01-20 | 一等座 | 35356 | 9 | t

(1 row)

壓力測試

vi test.sql

select * from buy('D645','上海南','長沙','2013-01-20');

不加nowait測試結果 :

ocz@db-172-16-3-150-> pgbench -M prepared -f ./test.sql -n -r -c 16 -j 8 -T 1200 -U postgres digoal

transaction type: Custom query

scaling factor: 1

query mode: prepared

number of clients: 16

number of threads: 8

duration: 1200 s

number of transactions actually processed: 2197407

tps = 1831.143708 (including connections establishing)

tps = 1831.169308 (excluding connections establishing)

statement latencies in milliseconds:

8.734424 select * from buy('D645','上海南','長沙','2013-01-20');

加nowait測試結果 :

ocz@db-172-16-3-150-> pgbench -M prepared -f ./test.sql -n -r -c 16 -j 16 -T 12 -U postgres digoal

transaction type: Custom query

scaling factor: 1

query mode: prepared

number of clients: 16

number of threads: 16

duration: 12 s

number of transactions actually processed: 93632

tps = 7800.056248 (including connections establishing)

tps = 7818.803904 (excluding connections establishing)

statement latencies in milliseconds:

2.042862 select * from buy('D645','上海南','長沙','2013-01-20');

小結

1. 需要解決更新熱點, 降低等待, 提高并行處理幾率.

本例的熱點在 : update train_bucket set sit_bit = set_bit(sit_bit, strpos(sit_bit::text,'0')-1, 1), sit_remain = sit_remain-1

where current of curs1;

以及

update train_sit set station_bit=bitsetvarbit(station_bit, v_from_station_idx-1, v_to_station_idx-v_from_station_idx, 1)

where current of curs2;

對應的游標 :

open curs2 for select tid,tbid,sit_no from train_sit

where (station_bit & bitsetvarbit(repeat('0', v_station_len-1)::varbit, v_from_station_idx-1, v_to_station_idx-v_from_station_idx, 1)) = repeat('0', v_station_len-1)::varbit

and station_bit <> repeat('1', v_station_len-1)::varbit

-- and ctid not in (select locked_row from pgrowlocks('train_sit')) -- 耗時約300毫秒, 用它來解決熱點鎖等待不劃算.

limit 1

for update;

以及

open curs1 for select id, tid, strpos(sit_bit::text,'0'), sit_level, bno from train_bucket

where sit_remain>0

-- and ctid not in (select locked_row from pgrowlocks('train_bucket')) -- 耗時約300毫秒, 用它來解決熱點鎖等待不劃算.

limit 1

for update;

解決的關鍵在這里.

如果不能解決熱點的問題, 那就提高處理速度, 精簡字段數量和長度, 精簡索引. 提高更新速度.

2. 減少數據掃描的量.

partial index, 避免滿座車廂的掃描, 以及全程占位位子的掃描.

3. 先查bucket 是否空閑, 再查sit是否空閑.

4. 還需要考慮優先級的問題 :

例如有111000和111100兩個位子, 如果請求要最后兩個站的票, 應該優先匹配111100, 這樣更不容易浪費.如下 :

111000 | 000011 = 111011

111100 | 000011 = 111111

參考

1. setbitvarbit

http://blog.163.com/digoal@126/blog/static/163877040201302192427651/