PostgreSQL 秒杀场景优化

本文涉及的产品
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
云原生数据库 PolarDB 分布式版,标准版 2核8GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
简介:
先说结果:
TPS 从 2855 优化到 231376。

秒杀场景的典型瓶颈在于对同一条记录的多次更新请求,然后只有一个或者少量请求是成功的,其他请求是以失败或更新不到告终。
例如,Iphone的1元秒杀,如果我只放出1台Iphone,我们把它看成一条记录,秒杀开始后,谁先抢到(更新这条记录的锁),谁就算秒杀成功。
例如:
使用一个标记位来表示这条记录是否已经被更新,或者记录更新的次数(几台Iphone)。
update tbl set xxx=xxx,upd_cnt=upd_cnt+1 where id=pk and upd_cnt+1<=5;   -- 假设可以秒杀5台
这种方法的弊端:
获得锁的用户在处理这条记录时,可能成功,也可能失败,或者可能需要很长时间,(例如数据库响应慢)在它结束事务前,其他会话只能等着。
等待是非常不科学的,因为对于没有获得锁的用户,等待是在浪费时间。
所以一般的优化处理方法是先使用for update nowait的方式来避免等待,即如果无法即可获得锁,那么就不等待。
例如:
begin;
select 1 from tbl where id=pk for update nowait;  --  如果用户无法即刻获得锁,则返回错误。从而这个事务回滚。
update tbl set xxx=xxx,upd_cnt=upd_cnt+1 where id=pk and upd_cnt+1<=5;
end;
这种方法可以减少用户的等待时间,因为无法即刻获得锁后就直接返回了。
但是这种方法也存在一定的弊端,对于一个商品,如果可以秒杀多台的话,我们用1条记录来存储多台,降低了秒杀的并发性。
因为我们用的是行锁。
解决这个问题办法很多,最终就是要提高并发性,例如:
1. 分段秒杀,把商品数量打散,拆成多个段,从而提高并发处理能力。
总体来说,优化的思路是减少锁等待时间,避免串行,尽量并行。

优化到这里就结束了吗?显然没有,以上方法任意数据库都可以做到,如果就这样结束怎么体现PostgreSQL的特性呢?
PostgreSQL还提供了一个锁类型,advisory锁,这种锁比行锁更加轻量,支持会话级别和事务级别。(但是需要注意ID是全局的,否则会相互干扰,也就是说,所有参与秒杀或者需要用到advisory lock的ID需要在单个库内保持全局唯一)

例子:
update tbl set xxx=xxx,upd_cnt=upd_cnt+1 where id=pk and upd_cnt+1<=5 and pg_try_advisory_xact_lock(:id);

最后必须要对比一下for update nowait和advisory lock的性能。
下面是在一台本地虚拟机上的测试。

新建一张秒杀表
postgres=# \d t1
      Table "public.t1"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | not null
 info   | text    | 
Indexes:
    "t1_pkey" PRIMARY KEY, btree (id)
只有一条记录,不断的被更新
postgres=# select * from t1;
 id |             info              
----+-------------------------------
  1 | 2015-09-14 09:47:04.703904+08
(1 row)

压测for update nowait的方式:
CREATE OR REPLACE FUNCTION public.f1(i_id integer)  
 RETURNS void  
 LANGUAGE plpgsql  
AS $function$ 
declare 
begin 
  perform 1 from t1 where id=i_id for update nowait; 
  update t1 set info=now()::text where id=i_id; 
  exception when others then  
  return; 
end; 
$function$;  

postgres@digoal-> cat test1.sql
\setrandom id 1 1
select f1(:id);

压测advisory lock的方式:
postgres@digoal-> cat test.sql
\setrandom id 1 1
update t1 set info=now()::text where id=:id and pg_try_advisory_xact_lock(:id);

清除压测统计数据:
postgres=# select pg_stat_reset();
 pg_stat_reset 
---------------
 
(1 row)
postgres=# select * from pg_stat_all_tables where relname='t1';
-[ RECORD 1 ]-------+-------
relid               | 184731
schemaname          | public
relname             | t1
seq_scan            | 0
seq_tup_read        | 0
idx_scan            | 0
idx_tup_fetch       | 0
n_tup_ins           | 0
n_tup_upd           | 0
n_tup_del           | 0
n_tup_hot_upd       | 0
n_live_tup          | 0
n_dead_tup          | 0
n_mod_since_analyze | 0
last_vacuum         | 
last_autovacuum     | 
last_analyze        | 
last_autoanalyze    | 
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0

压测结果:
postgres@digoal-> pgbench -M prepared -n -r -P 1 -f ./test1.sql -c 20 -j 20 -T 60
......
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 20
number of threads: 20
duration: 60 s
number of transactions actually processed: 792029
latency average: 1.505 ms
latency stddev: 4.275 ms
tps = 13196.542846 (including connections establishing)
tps = 13257.270709 (excluding connections establishing)
statement latencies in milliseconds:
        0.002625        \setrandom id 1 1
        1.502420        select f1(:id);

postgres=# select * from pg_stat_all_tables where relname='t1';
-[ RECORD 1 ]-------+-------
relid               | 184731
schemaname          | public
relname             | t1
seq_scan            | 0
seq_tup_read        | 0
idx_scan            | 896963   //  大多数是无用功
idx_tup_fetch       | 896963   //  大多数是无用功
n_tup_ins           | 0
n_tup_upd           | 41775
n_tup_del           | 0
n_tup_hot_upd       | 41400
n_live_tup          | 0
n_dead_tup          | 928
n_mod_since_analyze | 41774
last_vacuum         | 
last_autovacuum     | 
last_analyze        | 
last_autoanalyze    | 
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0


postgres@digoal-> pgbench -M prepared -n -r -P 1 -f ./test.sql -c 20 -j 20 -T 60
......
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 20
number of threads: 20
duration: 60 s
number of transactions actually processed: 1392372
latency average: 0.851 ms
latency stddev: 2.475 ms
tps = 23194.831054 (including connections establishing)
tps = 23400.411501 (excluding connections establishing)
statement latencies in milliseconds:
        0.002594        \setrandom id 1 1
        0.848536        update t1 set info=now()::text where id=:id and pg_try_advisory_xact_lock(:id);

postgres=# select * from pg_stat_all_tables where relname='t1';
-[ RECORD 1 ]-------+--------
relid               | 184731
schemaname          | public
relname             | t1
seq_scan            | 0
seq_tup_read        | 0
idx_scan            | 1368933  //  大多数是无用功
idx_tup_fetch       | 1368933   //  大多数是无用功
n_tup_ins           | 0
n_tup_upd           | 54957
n_tup_del           | 0
n_tup_hot_upd       | 54489
n_live_tup          | 0
n_dead_tup          | 1048
n_mod_since_analyze | 54957
last_vacuum         | 
last_autovacuum     | 
last_analyze        | 
last_autoanalyze    | 
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0
我们注意到,不管用哪种方法,都会浪费掉很多次的无用功扫描。

为了解决无用扫描的问题,可以使用以下函数。(当然,还有更好的方法是对用户透明。)
CREATE OR REPLACE FUNCTION public.f(i_id integer)  
 RETURNS void  
 LANGUAGE plpgsql  
AS $function$ 
declare 
  a_lock boolean := false;
begin 
  select pg_try_advisory_xact_lock(i_id) into a_lock;
  if a_lock then
    update t1 set info=now()::text where id=i_id; 
  end if;
  exception when others then  
  return; 
end; 
$function$;  

transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 20
number of threads: 20
duration: 60 s
number of transactions actually processed: 1217195
latency average: 0.973 ms
latency stddev: 3.563 ms
tps = 20283.314001 (including connections establishing)
tps = 20490.143363 (excluding connections establishing)
statement latencies in milliseconds:
        0.002703        \setrandom id 1 1
        0.970209        select f(:id);

postgres=# select * from pg_stat_all_tables where relname='t1';
-[ RECORD 1 ]-------+-------
relid               | 184731
schemaname          | public
relname             | t1
seq_scan            | 0
seq_tup_read        | 0
idx_scan            | 75927
idx_tup_fetch       | 75927
n_tup_ins           | 0
n_tup_upd           | 75927
n_tup_del           | 0
n_tup_hot_upd       | 75902
n_live_tup          | 0
n_dead_tup          | 962
n_mod_since_analyze | 75927
last_vacuum         | 
last_autovacuum     | 
last_analyze        | 
last_autoanalyze    | 
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0
除了吞吐率的提升,我们其实还看到真实的处理数(更新次数)也有提升,所以不仅仅是降低了等待延迟,实际上也提升了处理能力。

最后提供一个物理机上的数据参考,使用128个并发连接,同时对一条记录进行更新:
不做任何优化的并发处理能力:
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 128
number of threads: 128
duration: 100 s
number of transactions actually processed: 285673
latency average: 44.806 ms
latency stddev: 45.751 ms
tps = 2855.547375 (including connections establishing)
tps = 2855.856976 (excluding connections establishing)
statement latencies in milliseconds:
        0.002509        \setrandom id 1 1
        44.803299       update t1 set info=now()::text where id=:id;

使用for update nowait的并发处理能力:
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 128
number of threads: 128
duration: 100 s
number of transactions actually processed: 6663253
latency average: 1.919 ms
latency stddev: 2.804 ms
tps = 66623.169445 (including connections establishing)
tps = 66630.307999 (excluding connections establishing)
statement latencies in milliseconds:
        0.001934        \setrandom id 1 1
        1.917297        select f1(:id);

使用advisory lock后的并发处理能力:
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 80
number of threads: 80
duration: 60 s
number of transactions actually processed: 13883387
latency average: 0.344 ms
latency stddev: 0.535 ms
tps = 231197.323122 (including connections establishing)
tps = 231376.427515 (excluding connections establishing)
statement latencies in milliseconds:
        0.344042        select f(1);
此时的perf top
   PerfTop:   23883 irqs/sec  kernel:32.2%  exact:  0.0% [1000Hz cycles],  (all, 32 CPUs)
--------------------------------------------------------------------------------     

 samples  pcnt function         DSO
 _______ _____ ____________________________ ______________________________________________

10645.00  3.5% GetSnapshotData  /u02/digoal/soft_bak/pgsql9.5/bin/postgres    
 7963.00  2.6% AllocSetAlloc    /u02/digoal/soft_bak/pgsql9.5/bin/postgres    
 4720.00  1.6% _int_malloc      /lib64/libc-2.12.so   
 4270.00  1.4% __schedule       [kernel.kallsyms]     
 4234.00  1.4% fmgr_info_cxt_security       /u02/digoal/soft_bak/pgsql9.5/bin/postgres    
 4217.00  1.4% LWLockAcquire    /u02/digoal/soft_bak/pgsql9.5/bin/postgres    
 3958.00  1.3% hash_search_with_hash_value  /u02/digoal/soft_bak/pgsql9.5/bin/postgres    
 3656.00  1.2% __GI_vfprintf    /lib64/libc-2.12.so   
 3572.00  1.2% update_blocked_averages      [kernel.kallsyms]     
 3338.00  1.1% PostgresMain     /u02/digoal/soft_bak/pgsql9.5/bin/postgres    
 3267.00  1.1% __switch_to      [kernel.kallsyms]     
 3095.00  1.0% __strlen_sse42   /lib64/libc-2.12.so   
 2996.00  1.0% memcpy           /lib64/libc-2.12.so   
 2930.00  1.0% _int_free        /lib64/libc-2.12.so   
 2568.00  0.8% LWLockRelease    /u02/digoal/soft_bak/pgsql9.5/bin/postgres    
 2446.00  0.8% SearchCatCache   /u02/digoal/soft_bak/pgsql9.5/bin/postgres    
 2178.00  0.7% ExecInitExpr     /u02/digoal/soft_bak/pgsql9.5/bin/postgres    
 2053.00  0.7% hash_any         /u02/digoal/soft_bak/pgsql9.5/bin/postgres    
 2035.00  0.7% __GI___libc_malloc           /lib64/libc-2.12.so   
 2009.00  0.7% _raw_spin_lock_irqsave       [kernel.kallsyms]     
 1804.00  0.6% exec_stmt        /u02/digoal/soft_bak/pgsql9.5/lib/plpgsql.so  
 1764.00  0.6% __memset_sse2    /lib64/libc-2.12.so   
 1717.00  0.6% pqParseInput3    /u02/digoal/soft_bak/pgsql9.5/lib/libpq.so.5.8
 1696.00  0.6% do_select        [kernel.kallsyms]     
 1686.00  0.6% __strcpy_ssse3   /lib64/libc-2.12.so   
 1685.00  0.6% update_curr      [kernel.kallsyms]     
 1619.00  0.5% enqueue_entity   [kernel.kallsyms]     
 1607.00  0.5% pfree/u02/digoal/soft_bak/pgsql9.5/bin/postgres    
 1598.00  0.5% doCustom         /u02/digoal/soft_bak/pgsql9.5/bin/pgbench     
 1594.00  0.5% idle_cpu         [kernel.kallsyms]     
 1589.00  0.5% update_cfs_rq_blocked_load   [kernel.kallsyms]     
 1554.00  0.5% lapic_next_deadline          [kernel.kallsyms]     
 1512.00  0.5% update_cfs_shares[kernel.kallsyms]     
 1491.00  0.5% MemoryContextCreate          /u02/digoal/soft_bak/pgsql9.5/bin/postgres    
 1482.00  0.5% _raw_spin_lock   [kernel.kallsyms]     
 1423.00  0.5% palloc           /u02/digoal/soft_bak/pgsql9.5/bin/postgres    
 1419.00  0.5% __GI___sigsetjmp /lib64/libc-2.12.so   
 1412.00  0.5% __cfree          /lib64/libc-2.12.so   
 1399.00  0.5% unix_stream_recvmsg          [kernel.kallsyms]     
 1393.00  0.5% __fget_light     [kernel.kallsyms]     
 1359.00  0.4% ResourceOwnerReleaseInternal /u02/digoal/soft_bak/pgsql9.5/bin/postgres    
 1351.00  0.4% AllocSetFree     /u02/digoal/soft_bak/pgsql9.5/bin/postgres    
 1277.00  0.4% unix_stream_sendmsg          [kernel.kallsyms]     
 1246.00  0.4% __memcmp_sse4_1  /lib64/libc-2.12.so   
 1240.00  0.4% plpgsql_exec_function        /u02/digoal/soft_bak/pgsql9.5/lib/plpgsql.so  
 1225.00  0.4% expression_tree_walker       /u02/digoal/soft_bak/pgsql9.5/bin/postgres    
 1160.00  0.4% exec_stmt_block  /u02/digoal/soft_bak/pgsql9.5/lib/plpgsql.so
使用advisory lock,性能相比不做任何优化性能提升了约66倍,相比for update nowait性能提升了约1.8倍。
这种优化可以快速告诉用户是否能秒杀到此类商品,而不需要等待其他用户更新结束后才知道。所以大大降低了RT,提高了吞吐率。

最后提一下9.5的新特性, select ,,, for update ,,, skip locked.
http://blog.163.com/digoal@126/blog/static/163877040201551552017215/
如果能做到UPDATE语法里面,就完美了,直接跳过无法获得锁的行。并发能力瞬间提升,也不用advisory了。

[参考]
1. http://www.postgresql.org/docs/9.5/static/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
2月前
|
安全 关系型数据库 MySQL
MySQL数据库高效秘籍:10个小技巧,让你轻松应对各种场景!
【8月更文挑战第25天】本文介绍了十个提升MySQL数据库效率与安全性的实用技巧。涵盖查询性能分析、索引优化、慢查询日志利用、图形化工具如MySQL Workbench的应用、性能分析工具、主从复制实现、备份与恢复策略、数据库迁移方法及安全性保障等多个方面。通过具体的示例代码展示每个技巧的实际操作方式,帮助读者深入理解并有效运用MySQL数据库。
73 0
|
2月前
|
监控 关系型数据库 数据库
PostgreSQL的索引优化策略?
【8月更文挑战第26天】PostgreSQL的索引优化策略?
57 1
|
21天前
|
存储 SQL 关系型数据库
一篇文章搞懂MySQL的分库分表,从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
MySQL如何进行分库分表、数据迁移?从相关概念、使用场景、拆分方式、分表字段选择、数据一致性校验等角度阐述MySQL数据库的分库分表方案。
一篇文章搞懂MySQL的分库分表,从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
|
16天前
|
缓存 关系型数据库 数据库
如何优化 PostgreSQL 数据库性能?
如何优化 PostgreSQL 数据库性能?
19 2
|
2月前
|
监控 关系型数据库 数据库
如何优化PostgreSQL的性能?
【8月更文挑战第4天】如何优化PostgreSQL的性能?
110 7
|
3月前
|
SQL 关系型数据库 MySQL
(八)MySQL锁机制:高并发场景下该如何保证数据读写的安全性?
锁!这个词汇在编程中出现的次数尤为频繁,几乎主流的编程语言都会具备完善的锁机制,在数据库中也并不例外,为什么呢?这里牵扯到一个关键词:高并发,由于现在的计算机领域几乎都是多核机器,因此再编写单线程的应用自然无法将机器性能发挥到最大,想要让程序的并发性越高,多线程技术自然就呼之欲出,多线程技术一方面能充分压榨CPU资源,另一方面也能提升程序的并发支持性。
219 3
|
2月前
|
SQL 关系型数据库 MySQL
(十六)MySQL调优篇:单机数据库如何在高并发场景下健步如飞?
在当前的IT开发行业中,系统访问量日涨、并发暴增、线上瓶颈等各种性能问题纷涌而至,性能优化成为了现时代中一个炙手可热的名词,无论是在开发、面试过程中,性能优化都是一个常谈常新的话题。而MySQL作为整个系统的后方大本营,由于是基于磁盘的原因,性能瓶颈往往也会随着流量增大而凸显出来。
156 0
|
3月前
|
人工智能 关系型数据库 MySQL
探索和体验云原生数据库PolarDB MySQL版在AI场景中的应用
探索和体验云原生数据库PolarDB MySQL版在AI场景中的应用
161 0
|
3月前
|
关系型数据库 MySQL 分布式数据库
PolarDB MySQL场景评测:阿里云数据库服务的新高度
随着企业数字化转型的加速,对数据库的稳定性和性能提出了更高要求。阿里云的PolarDB MySQL应运而生,作为一款高度兼容MySQL协议的云原生数据库,它在性能、扩展性和安全性方面展现出了卓越的能力。本文将基于阿里云PolarDB MySQL的官方评测,深入探讨其在实际应用场景中的表现,以及为用户带来的价值。
97 0
|
4月前
|
SQL NoSQL 关系型数据库
Mysql锁及适用场景
Mysql锁及适用场景
41 0

相关产品

  • 云原生数据库 PolarDB
  • 云数据库 RDS PostgreSQL 版
  • 下一篇
    无影云桌面