PostgreSQL 10.0 preview 性能增强 - pg_xact align(cacheline对齐)

最新推荐文章于 2025-05-17 17:06:42 发布

转载最新推荐文章于 2025-05-17 17:06:42 发布 · 282 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://yq.aliyun.com/articles/72293

文章标签：

#数据库 #开发工具

通过cacheline对齐技术优化PostgreSQL在高并发场景下的性能表现，实验结果显示在特定环境下性能显著提升。

背景

cacheline对齐，可以大幅提升高并发下的性能。

Hackers,  

originally this idea was proposed by Andres Freund while experimenting with  
lockfree Pin/UnpinBuffer [1].  
The patch is attached as well as results of pgbench -S on 72-cores  
machine.  As before it shows huge benefit in this case.  
For sure, we should validate that it doesn't cause performance regression  
in other cases.  At least we should test read-write and smaller machines.  
Any other ideas?  

1.  
https://www.postgresql.org/message-id/20160411214029.ce3fw6zxim5k6a2r@alap3.anarazel.de  

------  
Alexander Korotkov  
Postgres Professional: http://www.postgrespro.com  
The Russian Postgres Company

测试

I already had the results with both the patches applied. But, as I was not
quite
able to understand on how Simon's patch for reducing pgxact access could
negate the regression on read-write workload that we saw with pgxact-align-3
patch earlier, I was slightly hesitant to share the results. Anyways, here
are
the results with combined patches on readonly and readwrite workload:

1) Results for read-only workload:
========================
pgbench -i -s 300 postgres
pgbench -M prepared -c $thread -j $thread -T $time_for_reading -S postgres

where, time_for_reading = 10mins
*non default param:*
shared_buffers=8GB
max_connections=300

CLIENT COUNT TPS (HEAD) TPS (PATCH) % IMPROVEMENT
4 36333 36835 1.381664052
8 70179 72496 3.301557446
16 169303 175743 3.803831001
32 328837 341093 3.727074508
64 363352 399847 10.04397939
72 372011 413437 11.13569222
128 443979 578355 30.26629638
180 321420 552373 71.85396055
196 276780 558896 101.927885
256 234563 568951 142.5578629

2) Results for read-write workload:
=========================
pgbench -i -s 300 postgres
pgbench -M prepared -c $thread -j $thread -T $time_for_reading  postgres

where, time_for_reading = 30mins

non default param:
shared_buffers=8GB
max_connections=300


CLIENT COUNT TPS (HEAD) TPS (PATCH) % IMPROVEMENT
4 2683 2690 0.2609019754
8 5321 5332 0.2067280586
16 10348 10387 0.3768844221
32 19446 19754 1.58387329
64 28178 28198 0.0709773582
72 28296 28639 1.212185468
128 28577 28600 0.0804843056
180 26665 27525 3.225201575
196 27628 28511 3.19603301
256 28467 28529 0.2177960445

HEAD is basically referring to the following git commit in master branch,

commit 5dbdb2f799232cb1b6df7d7a85d59ade3234d30c
Author: Robert Haas <rhaas(at)postgresql(dot)org>
Date:   Fri Feb 24 12:21:46 2017 +0530

    Make tablesample work with partitioned tables.

    This was an oversight in the original partitioning commit.

    Amit Langote, reviewed by David Fetter

--
With Regards,
Ashutosh Sharma
EnterpriseDB:http://www.enterprisedb.com

这个patch的讨论，详见邮件组，本文末尾URL。

PostgreSQL社区的作风非常严谨，一个patch可能在邮件组中讨论几个月甚至几年，根据大家的意见反复的修正，patch合并到master已经非常成熟，所以PostgreSQL的稳定性也是远近闻名的。