pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedinserts take longer and longer
5+ messages / 3 participants
[nested] [flat]
* inserts take longer and longer
@ 2018-11-20 15:55 David Modica <davidmo@imaginesoftware.com>
0 siblings, 2 replies; 5+ messages in thread
From: David Modica @ 2018-11-20 15:55 UTC (permalink / raw)
To: pgsql-admin
hi all.
I hope this is the correct way to get help from the list.
we are postgres 10.4 :
# DB Version: 10
# OS Type: linux
# DB Type: mixed
# Total Memory (RAM): 60 GB
# CPUs num: 22
# Connections num: 100
# Data Storage: san
we have a developer who is doing
batch inserts 20 at a time and each subsequent batch is taking
longer to the point where it is a critical problem. I recently changed
the out of the box config to see if that would help but it hasn’t:
max_connections = 100
shared_buffers = 15GB
effective_cache_size = 45GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 300
work_mem = 7149kB
min_wal_size = 1GB
max_wal_size = 2GB
max_worker_processes = 22
max_parallel_workers_per_gather = 11
max_parallel_workers = 22
any direction as to what to look at would be appreciated.
david
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: inserts take longer and longer
@ 2018-11-20 16:41 Stéphane KANSCHINE <stephane@hexack.fr>
parent: David Modica <davidmo@imaginesoftware.com>
1 sibling, 1 reply; 5+ messages in thread
From: Stéphane KANSCHINE @ 2018-11-20 16:41 UTC (permalink / raw)
To: David Modica <davidmo@imaginesoftware.com>; +Cc: pgsql-admin
Le mar. 20 nov., vers 15:55, David Modica exprimait :
> hi all.
Hi,
> I hope this is the correct way to get help from the list.
[...]
> we have a developer who is doing batch inserts 20 at a time and each
> subsequent batch is taking longer to the point where it is a
> critical problem. I recently changed the out of the box config to
> see if that would help but it hasn’t:
[...]
> any direction as to what to look at would be appreciated.
When you're doing bulk insert, it's a good practice to drop index and
recreate it after.
Regards,
--
Stéphane KANSCHINE - https://www.hexack.fr./ - https://www.nuajik.io./
＠ stephane@hexack.fr
+33 6 64 31 72 52
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: inserts take longer and longer
@ 2018-11-20 16:52 Ravi Krishna <srkrishna@outlook.com>
parent: David Modica <davidmo@imaginesoftware.com>
1 sibling, 1 reply; 5+ messages in thread
From: Ravi Krishna @ 2018-11-20 16:52 UTC (permalink / raw)
To: David Modica <davidmo@imaginesoftware.com>; +Cc: pgsql-admin
>
> we have a developer who is doing
> batch inserts 20 at a time and each subsequent batch is taking
> longer to the point where it is a critical problem. I recently changed
> the out of the box config to see if that would help but it hasn’t:
>
I recollect reading something few years ago, but not able to place my finger on the issue. IIRC it was something to do with the inserting session creating a HOT bloat because vaccum could not do anything on that live session. I will try to search for that post. But the symptoms were similar to yours. Initially good performance and then getting slower and slower.
Q: Is it straight insert or you do select or updates also along with insert.
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: inserts take longer and longer
@ 2018-11-21 14:03 David Modica <davidmo@imaginesoftware.com>
parent: Stéphane KANSCHINE <stephane@hexack.fr>
0 siblings, 0 replies; 5+ messages in thread
From: David Modica @ 2018-11-21 14:03 UTC (permalink / raw)
To: Stéphane KANSCHINE <stephane@hexack.fr>; +Cc: pgsql-admin
we will try that and see if it helps.
-----Original Message-----
From: Stéphane KANSCHINE <stephane@hexack.fr>
Sent: Tuesday, November 20, 2018 11:41 AM
To: David Modica <davidmo@imaginesoftware.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: inserts take longer and longer
Le mar. 20 nov., vers 15:55, David Modica exprimait :
> hi all.
Hi,
> I hope this is the correct way to get help from the list.
[...]
> we have a developer who is doing batch inserts 20 at a time and each
> subsequent batch is taking longer to the point where it is a critical
> problem. I recently changed the out of the box config to see if that
> would help but it hasn’t:
[...]
> any direction as to what to look at would be appreciated.
When you're doing bulk insert, it's a good practice to drop index and recreate it after.
Regards,
--
Stéphane KANSCHINE - https://www.hexack.fr./ - https://www.nuajik.io./
＠ stephane@hexack.fr
+33 6 64 31 72 52
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: inserts take longer and longer
@ 2018-11-21 14:04 David Modica <davidmo@imaginesoftware.com>
parent: Ravi Krishna <srkrishna@outlook.com>
0 siblings, 0 replies; 5+ messages in thread
From: David Modica @ 2018-11-21 14:04 UTC (permalink / raw)
To: Ravi Krishna <srkrishna@outlook.com>; +Cc: pgsql-admin
straight insert doing batches of 100. dropping the index to see if that helps.
-----Original Message-----
From: Ravi Krishna <srkrishna@outlook.com>
Sent: Tuesday, November 20, 2018 11:52 AM
To: David Modica <davidmo@imaginesoftware.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: inserts take longer and longer
>
> we have a developer who is doing
> batch inserts 20 at a time and each subsequent batch is taking longer
> to the point where it is a critical problem. I recently changed the
> out of the box config to see if that would help but it hasn’t:
>
I recollect reading something few years ago, but not able to place my finger on the issue. IIRC it was something to do with the inserting session creating a HOT bloat because vaccum could not do anything on that live session. I will try to search for that post. But the symptoms were similar to yours. Initially good performance and then getting slower and slower.
Q: Is it straight insert or you do select or updates also along with insert.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2018-11-21 14:04 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-11-20 15:55 inserts take longer and longer David Modica <davidmo@imaginesoftware.com>
2018-11-20 16:41 ` Stéphane KANSCHINE <stephane@hexack.fr>
2018-11-21 14:03 ` David Modica <davidmo@imaginesoftware.com>
2018-11-20 16:52 ` Ravi Krishna <srkrishna@outlook.com>
2018-11-21 14:04 ` David Modica <davidmo@imaginesoftware.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox