pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Autovacuum potential bug
2+ messages / 2 participants
[nested] [flat]

* Autovacuum potential bug
@ 2020-01-10 21:13  maschka, sam <sam.maschka@nextworld.net>
  0 siblings, 1 reply; 2+ messages in thread

From: maschka, sam @ 2020-01-10 21:13 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org

We have been seeing an issue where reads on a table seem to slowly degrade
over time. When we run VACUUM ANALYZE on the table the operations perform
much better. It usually takes about a week or two for the reads to degrade
to a point where we have to manually run VACUUM ANALYZE. This has been
happening to a handful of our tables. The mutation operations ran against
these tables are mainly update operations. Very few insert and delete
operations are ran against these tables. Also a jsonb object stores most of
the data within each record. The size of the jsonb object can vary widely
between records. We thought the autovacuum facility would have prevented us
from having to periodically run VACUUM ANALYZE. Is this a bug or is it a
configuration issue on our tables?

*Sam Maschka*

Software Engineer
651-245-4054

*ne**x**tw**o**rld* <http://www.nextworld.net/;

7979 E. Tufts Ave. Suite 900
<https://maps.google.com/?q=7979+E.+Tufts+Ave.+Suite+900+%0D+Denver,+CO+80237&entry=gmail&sou...;
Denver, CO 80237

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Autovacuum potential bug
@ 2020-01-10 22:12  Tomas Vondra <tomas.vondra@2ndquadrant.com>
  parent: maschka, sam <sam.maschka@nextworld.net>
  0 siblings, 0 replies; 2+ messages in thread

From: Tomas Vondra @ 2020-01-10 22:12 UTC (permalink / raw)
  To: maschka, sam <sam.maschka@nextworld.net>; +Cc: pgsql-bugs@lists.postgresql.org

Hi,

On Fri, Jan 10, 2020 at 02:13:47PM -0700, maschka, sam wrote:
>We have been seeing an issue where reads on a table seem to slowly degrade
>over time. When we run VACUUM ANALYZE on the table the operations perform
>much better. It usually takes about a week or two for the reads to degrade
>to a point where we have to manually run VACUUM ANALYZE. This has been
>happening to a handful of our tables. The mutation operations ran against
>these tables are mainly update operations. Very few insert and delete
>operations are ran against these tables. Also a jsonb object stores most of
>the data within each record. The size of the jsonb object can vary widely
>between records. We thought the autovacuum facility would have prevented us
>from having to periodically run VACUUM ANALYZE. Is this a bug or is it a
>configuration issue on our tables?
>

Unfortunately that's impossible to answer without providing much more
detailed information about the issue. This could easily be just a matter
of autovacuum not being aggressive enough on those tables, for example.

I don't think this qualifies as a bug, certainly not in the current
shape. Perhaps we'll discover something, but if I had to guess at this
point, I'd say it's likely a configuration issue.

I suggest you post a more detailed report to pgsql-performance [1] with
much more details. Right of the bat we need to know

- PostgreSQL version
- size of the database
- non-default configuration
- what type of queries you're running
- how much slower it gets (2x, 10x, 100x?)
- does autovacuum run at all on the tables?
- does the execution plan change?
- if the execution plan does not change, maybe try some CPU profiling

There's also [2] which is primarily focused on slow query analysis, but
maybe it'll give you ideas what other information to provide.


regards


[1] https://www.postgresql.org/list/pgsql-performance/
[2] https://wiki.postgresql.org/wiki/Slow_Query_Questions

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services 





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2020-01-10 22:12 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-01-10 21:13 Autovacuum potential bug maschka, sam <sam.maschka@nextworld.net>
2020-01-10 22:12 ` Tomas Vondra <tomas.vondra@2ndquadrant.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