From: Jim Nasby <jnasby@enova.com>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Daniel Farina <daniel@heroku.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Horrific time for getting 1 record from an index?
Date: Tue, 12 Nov 2013 18:22:36 -0600
Message-ID: <5282C64C.5070304@enova.com> (raw)
In-Reply-To: <CAMkU=1yMrRQRCXVVxLk2U2DMgOkoaR-AXzHLUnqAGpmxkHz9mQ@mail.gmail.com>
References: <52815099.4080409@enova.com>
<CAAZKuFaroDOtTj1MiZYmv9ZuHck_LQ-B2LjP_gDotqRPXtofMg@mail.gmail.com>
<528152DB.60907@enova.com>
<CAMkU=1xB9YBFrx2Pr7CyrRC2_PpnC_9jdHRgKRo9upeQo2zfww@mail.gmail.com>
<52816825.3020605@enova.com>
<CAMkU=1zQg2jxbeOomydgFwRGcwkKTf7Xj+dg7Cwxz9NiUSAzjA@mail.gmail.com>
<CAMkU=1yMrRQRCXVVxLk2U2DMgOkoaR-AXzHLUnqAGpmxkHz9mQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 11/12/13 6:17 PM, Jeff Janes wrote:
> BTW, I originally had this, even after multiple queries:
>
> Buffers: shared hit=1 read=9476
>
>
> What were the timings like? Upon repeated execution it seems like all the buffers should be loaded and so be "hit", not "read".
Well, the problem here is that this is a heavily hit 1.5TB database with 8GB of shared buffers... so stuff has to work hard to stay in buffer (and I didn't run all this immediately one after the other).
> Then vacuum:
> INFO: index "page_hits_raw_pkey" now contains 50343572 row versions in 182800 pages
> DETAIL: 3466871 index row versions were removed.
> 44728 index pages have been deleted, 35256 are currently reusable.
>
> Then...
>
> Buffers: shared hit=1 read=4
>
> So I suspect a vacuum is actually needed...
>
>
> Hmm. Maybe the kill method doesn't unlink the empty pages from the tree?
>
>
> I verified that this is the case--the empty pages remain linked in the tree until a vacuum removes them. But walking through empty leaf pages is way faster than resolving pages full of pointers to dead-to-all tuple, so the kill code still gives a huge benefit. But of course nothing will do much good until the transaction horizon advances.
Aaaand... that gets to the other problem... our SAN performance is pretty abysmal. It took ~270 seconds to read 80MB of index pages (+ whatever heap) to get to the first live tuple. (This was run close enough to the vacuum that I don't think visibility of these tuples would have changed mid-stream).
--
Jim Nasby, Lead Data Architect (512) 569-9461
--
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: jnasby@enova.com, jeff.janes@gmail.com, daniel@heroku.com
Subject: Re: Horrific time for getting 1 record from an index?
In-Reply-To: <5282C64C.5070304@enova.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