Re: Sudden insert performance degradation

On Mon, Jul 13, 2020 at 12:28 PM Michael Lewis <mlewis(at)entrata(dot)com> wrote:

> Is this an insert only table and perhaps not being picked up by
> autovacuum? If so, try a manual "vacuum analyze" before/after each batch
> run perhaps. You don't mention updates, but also have been adjusting
> fillfactor so I am not not sure.
>

It is mostly an insert table. Only queries I need to run on it are to
aggegate the count of IDs inserted per hour.

I did the vacuuming of the table; Didn't help. I tried both vacuum(analyze)
and vacuum(full) ... took a looooong time and no improvements.

I adjusted the `fillfactor` because the documentation didn't make it too
clear if by `updates to the table` it meant updating the value of existing
rows, or updating the table itself (which in my understanding would mean
that adding new data into it would cause the table to be updated). I just
started messing with the `fillfactor` to see if that would give me any
improvements. It seems to me it did since the first time I created the
table, I didn't change the fillfactor and stumbled upon the performance
issue after 12 hours; I then recreated the table with a fillfactor of 30
and was good again for about 12 hours more. Could be a coincidence though.
I tried to recreate the table using fillfactor 10, but it was taking too
long to add the data to it (12+ hours running and it wasn't done yet and
the WRITE speed on iotop was around 20K/s .... I ended up just canceling
it).

As of now, the table has about 280 million records in it.

Henrique