Обсуждение: inserts take longer and longer
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
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
> > 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 withthe inserting session creating a HOT bloat because vaccum could not do anything on that live session. I will try tosearch for that post. But the symptoms were similar to yours. Initially good performance and then getting slower andslower. Q: Is it straight insert or you do select or updates also along with insert.
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
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 withthe inserting session creating a HOT bloat because vaccum could not do anything on that live session. I will try tosearch for that post. But the symptoms were similar to yours. Initially good performance and then getting slower andslower. Q: Is it straight insert or you do select or updates also along with insert.