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

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(at)qq(dot)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
>