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
hardto 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
throughempty leaf pages is way faster than resolving pages full of pointers to dead-to-all tuple, so the kill code
stillgives 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
indexpages (+ whatever heap) to get to the first live tuple. (This was run close enough to the vacuum that I don't
thinkvisibility of these tuples would have changed mid-stream).
--
Jim Nasby, Lead Data Architect (512) 569-9461