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

Re: Hardware/OS recommendations for large databases (

From: Alan Stange <stange(at)rentec(dot)com>
To: Luke Lonergan <llonergan(at)greenplum(dot)com>
Cc: Postgresql Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Hardware/OS recommendations for large databases (
Date: 2005-11-23 22:00:37
Message-ID: 4384E685.4060108@rentec.com (view raw or flat)
Thread:
Lists: pgsql-performance
Luke Lonergan wrote:
> Why not contribute something - put up proof of your stated 8KB versus 
> 32KB page size improvement.

I did observe that 32KB block sizes were a significant win "for our 
usage patterns".   It might be a win for any of the following reasons:

0) The preliminaries:   ~300GB database with about ~50GB daily 
turnover.   Our data is fairly reasonably grouped.  If we're getting one 
item on a page we're usually looking at the other items as well.

1) we can live with a smaller FSM size.  We were often leaking pages 
with a 10M page FSM setting.  With 32K pages, a 10M FSM size is 
sufficient.   Yes, the solution to this is "run vacuum more often", but 
when the vacuum was taking 10 hours at a time, that was hard to do.

2) The typical datum size in our largest table is about 2.8KB, which is 
more than 1/4 page size thus resulting in the use of a toast table.   
Switching to 32KB pages allows us to get a decent storage of this data 
into the main tables, thus avoiding another table and associated large 
index.   Not having the extra index in memory for a table with 90M rows 
is probably beneficial.

3) vacuum time has been substantially reduced.  Vacuum analyze now run 
in the 2 to 3 hour range depending on load.

4) less cpu time spent in the kernel.  We're basically doing 1/4 as many 
system calls.  

Overall the system has now been working well.  We used to see the 
database being a bottleneck at times, but now it's keeping up nicely.

Hope this helps.

Happy Thanksgiving!

-- Alan

In response to

Responses

pgsql-performance by date

Next:From: Pailloncy Jean-GerardDate: 2005-11-23 22:14:47
Subject: 8.1 count(*) distinct: IndexScan/SeqScan
Previous:From: Anjan DaveDate: 2005-11-23 21:33:24
Subject: Re: High context switches occurring

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