When pgbench -i , it did already done vacuuming just before pgbench tpc-b test, below is the output of init loading. Same postgresql.conf for both v14 and v13, please check attached.
date;pgbench -i -s 6000 -F 85 -U pgbench --partitions 6 Fri Dec 9 05:54:17 GMT 2022 dropping old tables... creating tables... creating 6 partitions... generating data (client-side)... 600000000 of 600000000 tuples (100%) done (elapsed 577.18 s, remaining 0.00 s)) vacuuming... creating primary keys... done in 1568.52 s (drop tables 8.40 s, create tables 0.02 s, client-side generate 579.66 s, vacuum 339.54 s, primary keys 640.91 s). Thanks, James From: Samed YILDIRIM <sa...@reddoc.net> Sent: Thursday, December 15, 2022 4:38 PM To: James Pang (chaolpan) <chaol...@cisco.com> Cc: pgsql-performance@lists.postgresql.org Subject: 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) <chaol...@cisco.com<mailto:chaol...@cisco.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
postgresql.conf
Description: postgresql.conf
