Postgresql 11 row insert low performance using jdbc

Question

I am trying to improve the performance of row insertion in a postgresql 11 db using standard java prepared statements. It is standing on top of debian 11 in a VM environnement. I am using pentaho data integration and a simple table output step. I want to make my transformation database transactional so using batch update is not an option. I have created tables without index, nor constraints and made then unlogged. They have a few columns. I am currently getting row insert top speed of 40 r/s per connection. I can increase this speed by using parallelism and each connection tends to reach this speed.

The server is running smoothly using 3% cpu and about 10% RAM.

How can I increase the "per connection" row insert performance ?

Thanks for you help

Do you run the inserts in a single transaction? What kind of storage do you have, and how is it attached? How many indexes and constraints are on the table? Triggers? Can we see EXPLAIN (ANALYZE, BUFFERS) outputfor such an insert? — Laurenz Albe
– Laurenz Albe, Commented May 28, 2021 at 10:11
"I want to make my transformation database transactional so using batch update is not an option." Why not? Batch updates also execute inside transactions. — jjanes
– jjanes, Commented May 28, 2021 at 12:09
@LaurenzAlbe I do not know how to get the explain from pdi. I am filling a table with no constraints (no not null, no index, no primary key, no external key), so I was thinking the plan was not the issue. — gildas
– gildas, Commented May 28, 2021 at 12:44
@jjanes, pdi is not allowing it. When you set the transactional option, each connection spec becomes unique and batch updates are disabled. — gildas
– gildas, Commented May 28, 2021 at 12:46

jjanes · Accepted Answer · 2021-05-28 12:12:49Z

0

It sounds like you are bottlenecked by synchronous write speed. That makes the solution easy. Buy storage hardware that supports faster fsync speed and put your pg_wal directory on that storage.

answered May 28, 2021 at 12:12

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

gildas Over a year ago

I have launched ioping commande, but I am not able to read the results:

--- . (ext4 /dev/sda1) ioping statistics --- 9 requests completed in 3.92 ms, 72 KiB written, 2.29 k iops, 17.9 MiB/s generated 10 requests in 9.00 s, 80 KiB, 1 iops, 8.89 KiB/s min/avg/max/mdev = 379.0 us / 435.6 us / 551.3 us / 48.6 us

Laurenz Albe Over a year ago

Try running pg_test_fsync on the disk and see what it says.

gildas · Accepted Answer · 2021-05-28 13:05:03Z

0

I found out that it came from my connection to the server. I was using a ssh tunnel (I should have stated it, sorry). Using a direct connection, I get decent insert times. I did not suspect it as I was having good performance using batches.

Thanks for your answers

answered May 28, 2021 at 13:05

gildas

1

Collectives™ on Stack Overflow

Postgresql 11 row insert low performance using jdbc

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related