Обсуждение: DML sql execution time slow down PGv14 compared with PGv13
Hi,
We had some load test ( DML inserts/deletes/updates/ on tens of hash partition tables) 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 5%-25% 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, it’s similar as our application workload test, small sql statement running very fast 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
Вложения
On Thu, 15 Dec 2022 at 21:12, James Pang (chaolpan) <chaolpan@cisco.com> wrote: > We had some load test ( DML inserts/deletes/updates/ on tens of hash partition tables) and found that PGV14 slow down10-15% compared with PGV13. Same test server, same schema tables and data. From pg_stat_statements, sql exec_time, wedid found similar mean_exec_time increased from 5%-25% with same SQL statements. Both v14 and v13 give very fast sql responsetime, just compare the %diff from sql statements mean_exec_time. I tried this out on the tip of the PG13 and PG14 branch with the same scale of pgbench as you mentioned and I don't see the same slowdown as you do. PG13: tps = 1711.980109 (excluding connections establishing) PG14: tps = 1736.466835 (without initial connection time) As for why yours might be slower. You might want to have a look at the EXPLAIN ANALYZE output for the UPDATE statements. You can recreate the -M prepared by using PREPARE and EXECUTE. You might want to execute the statements 6 times and see if the plan changes on the 6th execution. It's likely not impossible that PG14 is using custom plans, whereas PG13 might be using generic plans for these updates. There were some quite significant changes made to the query planner in PG14 that changed how planning works for UPDATEs and DELETEs from partitioned tables. Perhaps there's some reason there that the custom/generic plan choice might differ. I see no reason why INSERT would have become slower. Both the query planning and execution is very different for INSERT. You might also want to have a look at what perf says. If you have the debug symbols installed, then you could just watch "perf top --pid=<pg backend running the pgbench workload>". Maybe that will show you something interesting.
Did you check pg_stat_statements ? looks like select some better , but DML decreased. -----Original Message----- From: David Rowley <dgrowleyml@gmail.com> Sent: Thursday, December 15, 2022 6:42 PM To: James Pang (chaolpan) <chaolpan@cisco.com> Cc: pgsql-performance@lists.postgresql.org Subject: Re: DML sql execution time slow down PGv14 compared with PGv13 On Thu, 15 Dec 2022 at 21:12, James Pang (chaolpan) <chaolpan@cisco.com> wrote: > We had some load test ( DML inserts/deletes/updates/ on tens of hash partition tables) and found that PGV14 slow down10-15% compared with PGV13. Same test server, same schema tables and data. From pg_stat_statements, sql exec_time, wedid found similar mean_exec_time increased from 5%-25% with same SQL statements. Both v14 and v13 give very fast sql responsetime, just compare the %diff from sql statements mean_exec_time. I tried this out on the tip of the PG13 and PG14 branch with the same scale of pgbench as you mentioned and I don't see thesame slowdown as you do. PG13: tps = 1711.980109 (excluding connections establishing) PG14: tps = 1736.466835 (without initial connection time) As for why yours might be slower. You might want to have a look at the EXPLAIN ANALYZE output for the UPDATE statements.You can recreate the -M prepared by using PREPARE and EXECUTE. You might want to execute the statements 6 timesand see if the plan changes on the 6th execution. It's likely not impossible that PG14 is using custom plans, whereasPG13 might be using generic plans for these updates. There were some quite significant changes made to the query planner in PG14 that changed how planning works for UPDATEs and DELETEs from partitioned tables. Perhaps there's some reason therethat the custom/generic plan choice might differ. I see no reason why INSERT would have become slower. Both the queryplanning and execution is very different for INSERT. You might also want to have a look at what perf says. If you have the debug symbols installed, then you could just watch"perf top --pid=<pg backend running the pgbench workload>". Maybe that will show you something interesting.
Did you check pg_stat_statements ? looks like select better, but DML decreased, so average tps looks similar . -----Original Message----- From: David Rowley <dgrowleyml@gmail.com> Sent: Thursday, December 15, 2022 6:42 PM To: James Pang (chaolpan) <chaolpan@cisco.com> Cc: pgsql-performance@lists.postgresql.org Subject: Re: DML sql execution time slow down PGv14 compared with PGv13 On Thu, 15 Dec 2022 at 21:12, James Pang (chaolpan) <chaolpan@cisco.com> wrote: > We had some load test ( DML inserts/deletes/updates/ on tens of hash partition tables) and found that PGV14 slow down10-15% compared with PGV13. Same test server, same schema tables and data. From pg_stat_statements, sql exec_time, wedid found similar mean_exec_time increased from 5%-25% with same SQL statements. Both v14 and v13 give very fast sql responsetime, just compare the %diff from sql statements mean_exec_time. I tried this out on the tip of the PG13 and PG14 branch with the same scale of pgbench as you mentioned and I don't see thesame slowdown as you do. PG13: tps = 1711.980109 (excluding connections establishing) PG14: tps = 1736.466835 (without initial connection time) As for why yours might be slower. You might want to have a look at the EXPLAIN ANALYZE output for the UPDATE statements.You can recreate the -M prepared by using PREPARE and EXECUTE. You might want to execute the statements 6 timesand see if the plan changes on the 6th execution. It's likely not impossible that PG14 is using custom plans, whereasPG13 might be using generic plans for these updates. There were some quite significant changes made to the query planner in PG14 that changed how planning works for UPDATEs and DELETEs from partitioned tables. Perhaps there's some reason therethat the custom/generic plan choice might differ. I see no reason why INSERT would have become slower. Both the queryplanning and execution is very different for INSERT. You might also want to have a look at what perf says. If you have the debug symbols installed, then you could just watch"perf top --pid=<pg backend running the pgbench workload>". Maybe that will show you something interesting.