DML sql execution time slow down PGv14 compared with PGv13

Поиск
Список
Период
Сортировка
От James Pang (chaolpan)
Тема DML sql execution time slow down PGv14 compared with PGv13
Дата
Msg-id PH0PR11MB51911232537B586332D3051ED6E19@PH0PR11MB5191.namprd11.prod.outlook.com
обсуждение исходный текст
Ответы Re: DML sql execution time slow down PGv14 compared with PGv13  (Samed YILDIRIM <samed@reddoc.net>)
Список 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

Вложения

В списке pgsql-performance по дате отправления:

Предыдущее
От: "James Pang (chaolpan)"
Дата:
Сообщение: DML sql execution time slow down PGv14 compared with PGv13
Следующее
От: Samed YILDIRIM
Дата:
Сообщение: Re: DML sql execution time slow down PGv14 compared with PGv13