Re: DML sql execution time slow down PGv14 compared with PGv13

Hello James,

Could you please add configurations of your PostgreSQL installations too?
I also wonder why you skip vacuuming (-n parameter) before starting of
tests.

Best regards.
Samed YILDIRIM

On Thu, 15 Dec 2022 at 10:22, James Pang (chaolpan) <chaolpan(at)cisco(dot)com>
wrote:

> Hi,
>
> We had some load test ( DML inserts/deletes/updates) and found that
> PGV14 slow down 10-15% compared with PGV13. Same test server, same schema
> tables and data. From pg_stat_statements, sql exec_time, we did found
> similar mean_exec_time increased from 20%-30% with same SQL statements.
> Both v14 and v13 give very fast sql response time, just compare the %diff
> from sql statements mean_exec_time.
>
> Now, I get a pgbench test in same server, the steps as below, small sql
> statement running very fast, not like our application load test that show
> INSERTS slow down 20-30%, but did see v14 slow down 5-10% for DML,compared
> with v13.
>
> 1.date;pgbench -i -s 6000 -F 85 -U pgbench --partition-method=hash
> --partitions=32
>
> 2.reboot OS to refresh buffer
>
> 3.run four rounds of test: date;pgbench -c 10 -j 10 -n -T 180 -U
> pgbench -M prepared
>
>
>
> Compare 14.6 and 13.9 on RHEL8.4, the “add primary key” step 14.6 much
> fast than 13.9, but most of insert/updates slow down 5-10%. The table is
> very simple and sql should be same, no idea what contribute to the sql
> exec_time difference? Attached please find sql exec_time.
>
>
>
> I copy the sql here too,
>
>
>
> version
>
> min_exec_time
>
> max_exec_time
>
> mean_exec_time
>
> calls
>
> SQL
>
> 13.9
>
> 0.002814
>
> 1.088559
>
> 0.004214798
>
> 3467468
>
> INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2,
> $3, $4, CURRENT_TIMESTAMP)
>
> 14.6
>
> 0.003169
>
> 0.955241
>
> 0.004482497
>
> 3466665
>
> INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2,
> $3, $4, CURRENT_TIMESTAMP)
>
> %diff
>
> 12.61549396
>
>
>
> 6.351410351
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> 13.9
>
> 0.013449
>
> 15.638027
>
> 1.18372356
>
> 3467468
>
> UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2
>
> 14.6
>
> 0.016109
>
> 133.106913
>
> 1.228978518
>
> 3466665
>
> UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2
>
> %diff
>
> 19.77842219
>
>
>
> 3.823101875
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> 13.9
>
> 0.005433
>
> 2.051736
>
> 0.008532748
>
> 3467468
>
> UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2
>
> 14.6
>
> 0.00625
>
> 1.847688
>
> 0.009062454
>
> 3466665
>
> UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2
>
> %diff
>
> 15.03773238
>
>
>
> 6.207914363
>
>
>
>
>
>
>
> Thanks,
>
>
>
> James
>