Skip site navigation (1) Skip section navigation (2)

Re: big select is resulting in a large amount of disk writing by kjournald

From: Greg Smith <greg(at)2ndquadrant(dot)com>
To: Joseph S <jks(at)selectacast(dot)net>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: big select is resulting in a large amount of disk writing by kjournald
Date: 2009-12-09 22:04:47
Message-ID: 4B201EFF.2060703@2ndquadrant.com (view raw or flat)
Thread:
Lists: pgsql-performance
Joseph S wrote:
> Greg Smith wrote:
>> Joseph S wrote:
>>> So I run "select count(*) from large_table" and I see in xosview a 
>>> solid block of write activity. Runtime is 28125.644 ms for the first 
>>> run.  The second run does not show a block of write activity and 
>>> takes 3327.441 ms
>> http://wiki.postgresql.org/wiki/Hint_Bits
>>
>
> Hmm. A large select results in a lot of writes? This seems broken.  
> And if we are setting these hint bits then what do we need VACUUM 
> for?  Is there any way to tune this behavior? Is there any way to get 
> stats on how many rows/pages would need hint bits set?
Basically, the idea is that if you're pulling a page in for something 
else that requires you to compute the hint bits, just do it now so 
VACUUM doesn't have to later, while you're in there anyway.  Why make 
VACUUM do the work later if you're doing part of it now anyway?  If you 
reorganize your test to VACUUM first *before* running the "select (*) 
from...", you'll discover the writes during SELECT go away.  You're 
running into the worst-case behavior.  For example, if you inserted a 
bunch of things more slowly, you might discover that autovacuum would do 
this cleanup before you even got to looking at the data.

There's no tuning for the behavior beyond making autovacuum more 
aggressive (to improve odds it will get there first), and no visibility 
into what's happening either.  And cranking up autovacuum has its own 
downsides.  This situation shows up a lot when you're benchmarking 
things, but not as much in the real world, so it's hard to justify 
improving.

-- 
Greg Smith    2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com  www.2ndQuadrant.com


In response to

Responses

pgsql-performance by date

Next:From: Joseph SDate: 2009-12-09 22:24:50
Subject: Re: big select is resulting in a large amount of disk writing by kjournald
Previous:From: Joseph SDate: 2009-12-09 20:50:57
Subject: Re: big select is resulting in a large amount of disk writing by kjournald

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group