From: Alan Stange <stange@rentec.com>
To: Luke Lonergan <llonergan@greenplum.com>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: Hardware/OS recommendations for large databases (
Date: Wed, 23 Nov 2005 17:00:37 -0500
Message-ID: <4384E685.4060108@rentec.com> (raw)
In-Reply-To: <BFA9EC80.14581%llonergan@greenplum.com>
References: <BFA9EC80.14581%llonergan@greenplum.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: stange@rentec.com, llonergan@greenplum.com
Subject: Re: Hardware/OS recommendations for large databases (
In-Reply-To: <4384E685.4060108@rentec.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox