Autovacuum behavior with rapid insert/delete 9.6

From: Ted Filmore <pilepiper(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Autovacuum behavior with rapid insert/delete 9.6
Date: 2018-03-29 23:01:13
Message-ID: CABeaYEBsQgZNJPdArNkLA6LOW93_wPusS6XHE3VXHhV1p_6sqA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Using mostly default parameters with 9.3 we are seeing reasonable
performance with our heavy transaction-based application.

However, when we are upgraded to 9.6 we are seeing horrible performance
with the same loads using the same hardware.

Gathering the configuration details, but I think I've spotted something
that might point to the problem. Our application has a repeated pattern of
inserting a customer invoice and details sending a message to another
application which reads the transaction, inserts it on another database
server, and the deletes the original.

In looking at pg_stat_activity I notice under the same load, autovacuum
seems to be running way more often under 9.6 and hanging around longer in
the activity queue. Also, I notice that IO run queue is like 10x deeper
and write latency balloons from < 10ms to around 300/400msec. Clearly
something is slamming the IO channel.

Granted I see there are many new autovacuum variables that likely have to
be tuned, Also, we've really got to redesign the application logic, it
seems moronic to have rapid insert/deletes in a handful of tables that is
likely to overwhelm autovacuum for no good reason.

What I am really asking to confirm is after describing the situation is it
reasonable to focus on (in the short term) tuning autovacuum to increase
performance or does this not make sense given the workload and I should
look elsewhere?

Thanks.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Peter Geoghegan 2018-03-29 23:07:43 Re: Autovacuum behavior with rapid insert/delete 9.6
Previous Message Cory Tucker 2018-03-29 22:29:16 Asynchronous Trigger?