Обсуждение: Write performance on a large database
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?
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.
> 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
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.
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
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