Thread: Write performance on a large database

Write performance on a large database

From
Håvard Wahl Kongsgård
Date:
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?


--
Håvard Wahl Kongsgård

http://havard.security-review.net/

Re: Write performance on a large database

From
Vick Khera
Date:
2011/6/9 Håvard Wahl Kongsgård <haavard.kongsgaard@gmail.com>:
> To improve the write performance, what are my options?

add more ram. add more checkpoint segments. get faster disks.  reduce
the number of indexes you have. split your big tables into smaller
partitions.

which of these may work depends on your exact problem.  we don't know
what that is.

Re: Write performance on a large database

From
tv@fuzzy.cz
Date:
> 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?

Hard to tell with this little info. We need to know more about your
hardware (CPU, RAM, disk setup, ...), PostgreSQL configuration (number of
checkpoint segments, ...) and output from tools like vmstat and iostat.

regards
Tomas


Re: Write performance on a large database

From
Alan Hodgson
Date:
On June 9, 2011 05:15:26 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?

Buy fast disks (many), attach them to a good raid controller with a battery-
backed write cache, setup in RAID-10. Or move to SSD.

--
Obama has now fired more cruise missiles than all other Nobel Peace prize
winners combined.

Re: Write performance on a large database

From
Greg Smith
Date:
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@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Write performance on a large database

From
Greg Smith
Date:
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@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general