RE: DML sql execution time slow down PGv14 compared with PGv13

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 <samed@xxxxxxxxxx>
Sent: Thursday, December 15, 2022 4:38 PM
To: James Pang (chaolpan) <chaolpan@xxxxxxxxx>
Cc: pgsql-performance@xxxxxxxxxxxxxxxxxxxx
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) <chaolpan@xxxxxxxxx> 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

Attachment: postgresql.conf
Description: postgresql.conf


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux