3

I have a database that is used by a Production application. It's an db.m3.medium on Amazon RDS. We've noticed that a lot of queries are starting to get slow that typically shouldn't be slow (e.g. select row by primary key).

We started to investigate the issue and found that our select 1 health check against the database is also slow. For example, here's some graphs about running explain (analyze buffers) select 1 every second over a ~20 hour period:

Duration (round trip time) (ms)

select 1 duration

I've capped the y-axis at 500 ms.

Planning (ms)

select 1 planning

I've capped the y-axis at 100 ms.

Execution (ms)

select 1 execution

I've capped the y-axis at 100 ms.


It seems crazy that planning a query is taking such extreme times.

When we look at the database as a whole, there doesn't seem to be any signs that it's under heavy enough load. Here's a typical day:

CPU

cpu

Read (blue) / write (purple) throughput (MiB/s)

read/write throughput

Network transmit (purple) / receive (blue) throughput (KiB/s)

network transmit/receive throughput

Disk

The disk is 30 GiB and has 5.75 GiB used.

Memory

There is 3.75 GiB of RAM and 2.2 GiB is free.


The database instance is new as of December 17, 2017 (after a manual failover from another instance because of required AWS maintenance). Our hypothesis is that the single-core is causing the OS (and Postgres) to have issues context switching. However, none of the metrics of the database really point to the fact that it's running very hot. I can also confirm that we don't have any long-running queries/transactions.

What could be causing simple queries such as select 1 to have high planning and execution times?

  • Is your planning time you use the time reported by the output of EXPLAIN (ANALYZE) itself, or is it arrived at by measuring the round trip time of an EXPLAIN without ANALYZE? – jjanes May 18 '18 at 16:19
  • Install sysstat to monitor your system at the same time as monitoring your database. Aren't there any better tools out there for monitoring the database than SELECT 1?Try here and here. PgHero is another tool. The mainstream tools have plugins for PostgreSQL - Nagios for example. You can check your CPU - more /proc/cpuinfo - an use sar (sysstat activity report AFAIK) with sysstat. – Vérace May 18 '18 at 16:33
  • @jjanes the planning time is from the output of the EXPLAIN (ANALYZE). The "duration" is the full round trip time of EXPLAIN (ANALYZE). – TheCloudlessSky May 18 '18 at 16:40
  • @Vérace I'm using Datadog to monitor the RDS instance and am willing to provide other metrics. Otherwise, I can't install anything on the server because it's RDS... – TheCloudlessSky May 18 '18 at 16:41
  • You might have CPU or disk IO latency spikes that are too transient to show up in the graphs. Turning on log_planner_stats might help (if RDS will let you do so). Does the "select 1" open a new connection for each query, or keep a persistent connection? – jjanes May 18 '18 at 16:50

Your Answer

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

Browse other questions tagged or ask your own question.