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> 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 >
