Thread: How to configure a read-only database server?
I browsed the faq and looked at PostgreSQL performance books but I could not find the obvious: How to configure a read-only database server? I have a single-disk virtual Linux system and a read-only dataset which is exposed to internet and completely replaced from time to time. This is what I found so far: * Disabling autovacuum daemon. * Setting postgresql.conf parameters: fsync=off synchronous_commit=off full_page_writes=off * For the session: SET transaction_read_only TO FALSE; SET TRANSACTION READ ONLY; * What about wal_level and archive_mode? => Any comments on speeding up/optimizing such database server? Yours, Stefan
On Tue, Apr 19, 2011 at 12:08 AM, Stefan Keller <sfkeller@gmail.com> wrote: > I browsed the faq and looked at PostgreSQL performance books but I > could not find the obvious: > How to configure a read-only database server? > > I have a single-disk virtual Linux system and a read-only dataset > which is exposed to internet and completely replaced from time to > time. > > This is what I found so far: > > * Disabling autovacuum daemon. I guess this will give you only small benefits as the daemon won't find any tables with modifications. > * Setting postgresql.conf parameters: > fsync=off > synchronous_commit=off Since you don't commit changes the effect of this might be small as well. > full_page_writes=off > > * For the session: > SET transaction_read_only TO FALSE; Did you mean "TRUE"? > SET TRANSACTION READ ONLY; What about ALTER DATABASE x SET default_transaction_read_only = on; ? > * What about wal_level and archive_mode? > > => Any comments on speeding up/optimizing such database server? Kind regards robert -- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/
On 04/18/2011 06:08 PM, Stefan Keller wrote: > * What about wal_level and archive_mode? > Presumably you don't care about either of these. wal_level=minimal, archive_mode=off. The other non-obvious thing you should do in this situation is do all the database maintenance in one big run after the data is loaded, something like: VACUUM FREEZE ANALYZE; Otherwise you will still have some trickle of write-activity going on, not always efficiently, despite being in read-only mode. It's because of what's referred to as Hint Bits: http://wiki.postgresql.org/wiki/Hint_Bits VACUUMing everything will clean those us, and freezing everything makes sure there's no old transactions to concerned about that might kick off anti-wraparound autovacuum. The only other thing you probably want to do is set checkpoint_segments to a big number. Shouldn't matter normally, but when doing this freeze operation it will help that execute quickly. You want a lower maintenance_work_mem on a read-only system than the master too, possibly a higher shared_buffers as well. It's all pretty subtle beyond the big parameters you already identified. -- 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
hi, Perhaps in postgresql.conf : default_transaction_read_only regards philippe Le 19/04/2011 00:08, Stefan Keller a écrit : > I browsed the faq and looked at PostgreSQL performance books but I > could not find the obvious: > How to configure a read-only database server? > > I have a single-disk virtual Linux system and a read-only dataset > which is exposed to internet and completely replaced from time to > time. > > This is what I found so far: > > * Disabling autovacuum daemon. > * Setting postgresql.conf parameters: > fsync=off > synchronous_commit=off > full_page_writes=off > > * For the session: > SET transaction_read_only TO FALSE; > SET TRANSACTION READ ONLY; > > * What about wal_level and archive_mode? > > => Any comments on speeding up/optimizing such database server? > > Yours, Stefan >
On Apr 18, 2011, at 6:08 PM, Stefan Keller <sfkeller@gmail.com> wrote: > I browsed the faq and looked at PostgreSQL performance books but I > could not find the obvious: > How to configure a read-only database server? > > I have a single-disk virtual Linux system and a read-only dataset > which is exposed to internet and completely replaced from time to > time. > > This is what I found so far: > > * Disabling autovacuum daemon. > * Setting postgresql.conf parameters: > fsync=off > synchronous_commit=off > full_page_writes=off All of those speed up writes. I don't know that they will make any difference at all on a read-only workload. > * What about wal_level and archive_mode? Same with these. > ...Robert
AFAIK it helps at least bulk loading my data every other time. So I'm confused and backup again: Given a single-disk virtual Linux system and a 'read-only' dataset, which is exposed to the internet and completely replaced from time to time, and expecting SELECT queries including joins, sorts, equality and range (sub-)queries... => What are the suggested postgresql.conf and session parameters for such a "read-only database" to "Whac-A-Mole" (i.e. to consider :->)? Stefan 2011/4/23 Robert Haas <robertmhaas@gmail.com>: > On Apr 18, 2011, at 6:08 PM, Stefan Keller <sfkeller@gmail.com> wrote: >> I browsed the faq and looked at PostgreSQL performance books but I >> could not find the obvious: >> How to configure a read-only database server? >> >> I have a single-disk virtual Linux system and a read-only dataset >> which is exposed to internet and completely replaced from time to >> time. >> >> This is what I found so far: >> >> * Disabling autovacuum daemon. >> * Setting postgresql.conf parameters: >> fsync=off >> synchronous_commit=off >> full_page_writes=off > > All of those speed up writes. I don't know that they will make any difference at all on a read-only workload. > >> * What about wal_level and archive_mode? > > Same with these. > >> > > ...Robert
Dne 24.4.2011 11:38, Stefan Keller napsal(a): > AFAIK it helps at least bulk loading my data every other time. Yes, but this thread was about setting the DB for read-only workload, so those settings were a bit strange. > So I'm confused and backup again: Given a single-disk virtual Linux > system and a 'read-only' dataset, which is exposed to the internet and > completely replaced from time to time, and expecting SELECT queries > including joins, sorts, equality and range (sub-)queries... > > => What are the suggested postgresql.conf and session parameters for > such a "read-only database" to "Whac-A-Mole" (i.e. to consider :->)? What database size are we talking about? Does that fit into RAM or not? If not, set large shared buffers and effective cache size appropriately. If it fits into memory, you could lower the random_page_cost (but this should be handled by the DB). Or you could create a ramdisk and use it to store the data (in this case lowering random_page_cost makes much more sense). regards Tomas