Re: big select is resulting in a large amount of disk writing by kjournald - Mailing list pgsql-performance

From Kevin Grittner
Subject Re: big select is resulting in a large amount of disk writing by kjournald
Date
Msg-id 4B20D071020000250002D2E5@gw.wicourts.gov
Whole thread Raw
In response to big select is resulting in a large amount of disk writing by kjournald  (Joseph S <jks@selectacast.net>)
Responses Re: big select is resulting in a large amount of disk writing by kjournald  (Scott Carey <scott@richrelevance.com>)
List pgsql-performance
Joseph S <jks@selectacast.net> wrote:
> I just installed a shiny new database server with pg 8.4.1 running
> on CentOS 5.4. After using slony to replicate over my database I
> decided to do some basic performance tests to see how spiffy my
> shiny new server is.  This machine has 32G ram, over 31 of which
> is used for the system file cache.
>
> 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

As others have mentioned, this is due to hint bit updates, and doing
an explicit VACUUM after the load and before you start using the
database will avoid run-time issues.  You also need statistics, so
be sure to do VACUUM ANALYZE.

There is one other sneaky surprise awaiting you, however.  Since
this stuff was all loaded with a narrow range of transaction IDs,
they will all need to be frozen at about the same time; so somewhere
down the road, either during a routine database vacuum or possibly
in the middle of normal operations, all of these rows will need to
be rewritten *again* to change the transaction IDs used for managing
MVCC to the special "frozen" value.  We routinely follow a load with
VACUUM FREEZE ANALYZE of the database to combine the update to
freeze the tuples with the update to set the hint bits and avoid
this problem.

There has been some talk about possibly writing tuples in a frozen
state with the hint bits already set if they are loaded in the same
database transaction which creates the table, but I'm not aware of
anyone currently working on this.

-Kevin

pgsql-performance by date:

Previous
From: Matthew Wakeling
Date:
Subject: Re: Fw: Help me put 2 Gigs of RAM to use
Next
From: Mark Stosberg
Date:
Subject: Re: Help me put 2 Gigs of RAM to use