Joseph S <jks@xxxxxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
