From: | "James Pang (chaolpan)" <chaolpan(at)cisco(dot)com> |
---|---|
To: | "pgsql-performance(at)lists(dot)postgresql(dot)org" <pgsql-performance(at)lists(dot)postgresql(dot)org> |
Subject: | DML sql execution time slow down PGv14 compared with PGv13 |
Date: | 2022-12-15 08:22:29 |
Message-ID: | PH0PR11MB51911232537B586332D3051ED6E19@PH0PR11MB5191.namprd11.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
Attachment | Content-Type | Size |
---|---|---|
sqlstats_v13_v14_pgbench.xlsx | application/vnd.openxmlformats-officedocument.spreadsheetml.sheet | 12.0 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Samed YILDIRIM | 2022-12-15 08:38:24 | Re: DML sql execution time slow down PGv14 compared with PGv13 |
Previous Message | James Pang (chaolpan) | 2022-12-15 08:12:31 | DML sql execution time slow down PGv14 compared with PGv13 |