Actually, with our application that’s JDBC clients instead of pgbench , we saw similar DML exec_time increase too.
From: James Pang (chaolpan) <chaolpan@xxxxxxxxx>
Sent: Thursday, December 15, 2022 4:45 PM
To: Samed YILDIRIM <samed@xxxxxxxxxx>
Cc: pgsql-performance@xxxxxxxxxxxxxxxxxxxx
Subject: RE: DML sql execution time slow down PGv14 compared with PGv13
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
Could you please add configurations of your PostgreSQL installations too?
I also wonder why you skip vacuuming (-n parameter) before starting of tests.
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
|
