Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M52382=pgsql-performance-ar@postgresql.org>)
 id 1XjEXa-0004IJ-Fy
 for pgsql-performance@arkaria.postgresql.org; Tue, 28 Oct 2014 21:42:58 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M52382=pgsql-performance-ar@postgresql.org>)
 id 1XjEXZ-0001QM-FN
 for pgsql-performance@arkaria.postgresql.org; Tue, 28 Oct 2014 21:42:57 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256)
 (Exim 4.80) (envelope-from <tv@fuzzy.cz>) id 1XjEXY-0001QG-MW
 for pgsql-performance@postgresql.org; Tue, 28 Oct 2014 21:42:56 +0000
Received: from smtp-out2.gransy.com ([77.78.126.49])
 by magus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <tv@fuzzy.cz>) id 1XjEXS-00058I-Rt
 for pgsql-performance@postgresql.org; Tue, 28 Oct 2014 21:42:55 +0000
Received: from smtp-out.gransy.com (localhost [127.0.0.1])
 by smtp-out2.gransy.com (Postfix) with ESMTP id 66E253F01C5
 for <pgsql-performance@postgresql.org>; Tue, 28 Oct 2014 22:42:53 +0100 (CET)
Received: from smtp.gransy.com (smtp.simplia.cz [77.78.126.20])
 by smtp-out2.gransy.com (Postfix) with ESMTP id EA1A63F01BD
 for <pgsql-performance@postgresql.org>; Tue, 28 Oct 2014 22:42:52 +0100 (CET)
Received: from [192.168.1.163] (ip-78-45-139-138.net.upcbroadband.cz
 [78.45.139.138]) (Authenticated sender: tv@fuzzy.cz)
 by smtp.gransy.com (Postfix) with ESMTPSA id 02BBD19A0410
 for <pgsql-performance@postgresql.org>; Tue, 28 Oct 2014 22:42:43 +0100 (CET)
Message-ID: <54500DD7.9040900@fuzzy.cz>
Date: Tue, 28 Oct 2014 22:42:47 +0100
From: Tomas Vondra <tv@fuzzy.cz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Thunderbird/31.2.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Sanity checking big select performance
References: 
 <CAByqJFtMg4b-QPa0rxOMQp8gBEgupMLz-adkpuzkcitz+THoiw@mail.gmail.com>
In-Reply-To: 
 <CAByqJFtMg4b-QPa0rxOMQp8gBEgupMLz-adkpuzkcitz+THoiw@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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