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