From: Tomas Vondra <tv@fuzzy.cz>
To: pgsql-performance@postgresql.org
Subject: Re: Sanity checking big select performance
Date: Tue, 28 Oct 2014 22:42:47 +0100
Message-ID: <54500DD7.9040900@fuzzy.cz> (raw)
In-Reply-To: <CAByqJFtMg4b-QPa0rxOMQp8gBEgupMLz-adkpuzkcitz+THoiw@mail.gmail.com>
References: <CAByqJFtMg4b-QPa0rxOMQp8gBEgupMLz-adkpuzkcitz+THoiw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 28.10.2014 22:15, Jeff Chen wrote:
> Hi friends!
>
> I'd love to get a sanity check on whether a fat select query I'm doing
> makes sense given the infrastructure that we have.
>
> We have 3 big tables that we typically join together for certain
> queries: a ~40 million row photos table, a ~20 million row users table,
> and a ~50 million row photo_to_album table that maps photos to albums.
So how much data is it? Does it fit within RAM (after loading into DB,
with all the indexes)?
> We like to display real time analytics, which often results in a query like:
>
> select (random aggregations )
> from
> photo_to_album join photos on photos.id <http://photos.id; =
> photo_to_album.photo_id
> join users on users.id <http://users.id; = photos.user_id
> where
> photo_to_album.album_id = <something>
> and
> photos.created_at between <some dates>
> and <other junk>
>
> We have indexes on all of the joins, and the where clauses.
Can we get EXPLAIN (and ideally EXPLAIN ANALYZE) for such queries?
> One of these queries that should be targeting something like 300K
> photos takes 38 seconds to run (with an aggregate/nested loop taking
> effectively all of that time), and then upon second execution with a
> warm cache, 4 seconds.
Well, if you're hitting disk, it's going to be slow. As you observed,
after loading it into page cache, it's much faster.
> Also worryingly, it spikes read IOPS to almost 1500/sec during the time
> and write IOPS 200/sec. When not running the query, steady level read
> iops basically nil, write hovers around 50-100.
>
> This also increases the queue depth from basically 0 up to 6. Keeping
> the queue depth high seems to cause timeouts in other queries. The CPU
> is barely if at all affected, hovering around 20%. Memory also barely
> affected.
20% is ~2 CPU cores (as you have 8 of them).
> We have a RDS Postgres database, m3.2xlarge with 2000 Provisioned IOPS
> and 400GB storage. This translates to 8 virtual CPUs, 30GiB memory, and
> all SSD drives.
AFAIK there are two PostgreSQL major versions supported on RDS - 9.1 and
9.3. Which one are you using?
Also, can you list values for some basic parameters (shared_buffers,
work_mem)? We don't know what are the default values on RDS, neither if
you somehow modified them.
> Several questions here:
>
> 1) Is that level of IOPS normal?
Ummmmm, why wouldn't it be? Each IO request works with 16 KB (on EBS),
and you're reading/writing a certain amount of data.
> 2) Is it bad that the level of iops can queue requests that screw up the
> whole database even if it's just select queries? Especially when the
> CPU and Memory are still plentiful?
You're saturating a particular resource. If you hit I/O wall, you can't
use the CPU / memory. The fact that it slows down your queries is
somehow expected.
Is it bad? Well, if you need to minimize impact on other queries, then
probably yes.
> 3) What is up with the huge difference between cold and warm cache?
I don't understand why you're surprised by this? The EBS performance on
m3.2xlarge (with EBS-Optimized networking, i.e. 1 Gbit dedicated to EBS)
you get up to ~120 MB/s, except that you set 2000 IOPS, which is ~32
MB/s. Memory is orders of magnitude faster, hence the difference.
regards
Tomas
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tv@fuzzy.cz
Subject: Re: Sanity checking big select performance
In-Reply-To: <54500DD7.9040900@fuzzy.cz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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