3

I'm running a database with a few tables with large row counts (millions) but which are fairly small in data size. All the tables are append-only, so no deletes or updates ever happening, around 250 rows are added per minute. 99% of the time queries take less than 5ms. However, occasionally queries get terminated by Postgres for taking more than 200ms (my configured statement timeout).

I'm using default settings apart from autovacuum_analyze_scale_factor=0.05, autovacuum_vacuum_scale_factor=0.1 and autovacuum_naptime=30.

I'm running PostgreSQL 9.6.3 on AWS RDS on a t2.medium machine, with multi-az and connecting to it from AWS Lambda (Node.js 6.10) from within the same private VPC.

CPU stays under 7% at all times, disk read latency is less than 2 ms, number of concurrent connections rarely goes above 1. Average queries per second is quite consistent at around 20.

We've also tried turning synchronous_commit to off, but the same timeouts continued to happen.

Could this be an issue with the auto-vacuum process slowing queries even though I've got no updates or deletes?

Update: Having removed all writes to the database, we still see the same issue. So, no inserts, no updates, no deletes, just ~30 selects per second causes timeouts.

  • 1
    vacuum shouldn't block reads. check pg_locks and pg_stat_activity next time before you kill the processes. – Evan Carroll Jan 9 '18 at 22:50
  • To clarify, I've never actually had to kill any process - it's only ever a single query that fails, then it returns to normal within 1 second, so no time to look into other stats while it's failing. – Daniel Bradley Jan 17 '18 at 10:30
  • @danielrbradley, does your table happen to have a GIN index? I'm struggling with an intermittent-slow-queries issue, and I think it's related to Postgres's FASTUPDATE mechanism on a GIN index. – Chris W. Mar 28 '18 at 21:19
  • @ChrisW. nope fraid not - only simple indexes on the bigserial type – Daniel Bradley Apr 5 '18 at 7:48
  • did you find the reason of problem? we have same issue – Maryna Krasnova Aug 20 '18 at 10:42
1

I unfortunately don't know much about postgres on AWS, my company is just starting to move that way, but when a vacuum runs here it does create disk io that could potentially slow down a query. It could be that the locks are on resource contention more as opposed to Postgres Locks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.