Re: Write performance on a large database

On 06/09/2011 08:15 AM, Håvard Wahl Kongsgård wrote:
> Hi, I have performance issues on very large database(100GB). Reading
> from the database is no problem, but writing(or heavy writing) is a
> nightmare.
> I have tried tuning postgresql, but that does not seem to improving
> the writing performance.
> To improve the write performance, what are my options?

Well, technically you have performances issues on a medium sized
database. The simplest answer to your question is "buy a server with
96GB of RAM". If it's still possible to get a server that holds your
entire database in memory for a moderate investment, it's really not
large yet.

There are many free guides that discuss various aspects of write
performance and tuning around them, some of which are excerpts from my
book which goes over all of this territory:

http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server : Covers
general server tuning
http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm :
Discusses the exact way checkpoints work and how people commonly tune them
http://wiki.postgresql.org/wiki/Reliable_Writes : all four of the
references there cover this area.
http://projects.2ndquadrant.com/talks : "The Write Stuff" presentation
goes over some of the limitations people run into with high write volume
applications.

I'd suggest taking a look at those. If you want to talk more about this
afterwards, start a new discussion on the pgsql-performance list with
some of the information recommended at
http://wiki.postgresql.org/wiki/SlowQueryQuestions : disk controller
and disk info, PostgreSQL version, and database server configuration all
have a lot of impact here. The contents of pg_stat_bgwriter would be
interesting too.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books