From: Kevin Grittner <kgrittn@ymail.com>
To: Jeff Chen <jeff@pixleeteam.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Sanity checking big select performance
Date: Wed, 29 Oct 2014 06:47:49 -0700
Message-ID: <1414590469.72962.YahooMailNeo@web122304.mail.ne1.yahoo.com> (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>
Jeff Chen <jeff@pixleeteam.com> wrote:
> 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),
With the seek time of commodity disk drives typically being 9ms, a
naive approach using random access to join to 300k rows on a single
thread with no caching would take 45 minutes; so the fact that you
are seeing much better than that implies some benefit from cache,
some sequential scanning, faster drives, or concurrent access to
multiple spindles.
> and then upon second execution with a warm cache, 4 seconds.
This shows that it is faster to access data in RAM than on disk,
and that your data wasn't already all in cache (most likely because
it doesn't all fit in RAM).
> 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.
The reads are just another symptom of not having the data fully
cached. The writes are more interesting. The two obvious
possibilities are that the query needed to use work files (for
sorts or hash tables) or that you were accessing a fair amount of
data which had not been vacuumed since it was last modified. To
help improve performance for the first, you might want to consider
increasing work_mem (although this will reduce RAM available for
caching). To improve performance for the second you might want to
make autovacuum more aggressive.
To get more specific advice, you may want to read this page and
follow the advice there:
https://wiki.postgresql.org/wiki/SlowQueryQuestions
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
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: kgrittn@ymail.com, jeff@pixleeteam.com
Subject: Re: Sanity checking big select performance
In-Reply-To: <1414590469.72962.YahooMailNeo@web122304.mail.ne1.yahoo.com>
* 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