Horton

Re: Optimize commit performance with a large number of 'on commit delete rows' temp tables

wenhui qiu <qiuwenhuifx@gmail.com>

View thread
From: wenhui qiu <qiuwenhuifx@gmail.com>
To: feichanghong <feichanghong@qq.com>, Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers <pgsql-hackers@lists.postgresql.org>
Date: 2024-07-08T04:18:17Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Lift limitation that PGPROC->links must be the first field

Hi feichanghong
    I don't think it's acceptable to introduce a patch to fix a problem
that leads to performance degradation, or can we take tom's suggestion to
optimise PreCommit_on_commit_actions?  I think it to miss the forest for
the trees



Best Regards,

feichanghong <feichanghong@qq.com> 于2024年7月8日周一 10:35写道：

> Hi wenhui,
>
> Thank you for your suggestions. I have supplemented some performance tests.
>
> Here is the TPS performance data for different numbers of temporary tables
> under different thresholds, as compared with the head (98347b5a). The
> testing
> tool used is pgbench, with the workload being to insert into one temporary
> table (when the number of temporary tables is 0, the workload is SELECT 1):
>
> | table num     | 0            | 1            | 5           | 10
> | 100         | 1000        |
>
> |---------------|--------------|--------------|-------------|-------------|-------------|-------------|
> | head 98347b5a | 39912.722209 | 10064.306268 | 7452.071689 | 5641.487369
> | 1073.203851 | 114.530958  |
> | threshold 1   | 40332.367414 | 7078.117192  | 7044.951156 | 7020.249434
> | 6893.652062 | 5826.597260 |
> | threshold 5   | 40173.562744 | 10017.532933 | 7023.770203 | 7024.283577
> | 6919.769315 | 5806.314494 |
>
> Here is the TPS performance data for different numbers of temporary tables
> at a threshold of 5, compared with the head (commit 98347b5a). The testing
> tool
> is pgbench, with the workload being to insert into all temporary tables:
>
> | table num     | 1           | 5           | 10          | 100        |
> 1000      |
>
> |---------------|-------------|-------------|-------------|------------|-----------|
> | head 98347b5a | 7243.945042 | 3627.290594 | 2262.594766 | 297.856756 |
> 27.745808 |
> | threshold 5   | 7287.764656 | 3130.814888 | 2038.308763 | 288.226032 |
> 27.705149 |
>
> According to test results, the patch does cause some performance loss with
> fewer temporary tables, but benefits are substantial when many temporary
> tables
> are used. The specific threshold could be set to 10 (HDDs may require a
> smaller
> one).
>
> I've provided two patches in the attachments, both with a default
> threshold of 10.
> One has the threshold configured as a GUC parameter, while the other is
> hardcoded
> to 10.
> ------------------------------
> Best Regards,
> Fei Changhong
>