pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to configure a read-only database server?
7+ messages / 6 participants
[nested] [flat]
* How to configure a read-only database server?
@ 2011-04-18 22:08 Stefan Keller <sfkeller@gmail.com>
2011-04-19 07:45 ` Re: How to configure a read-only database server? philippe <pg@rimbault.org>
2011-04-19 08:57 ` Re: How to configure a read-only database server? Robert Klemme <shortcutter@googlemail.com>
2011-04-19 14:30 ` Re: How to configure a read-only database server? Greg Smith <greg@2ndQuadrant.com>
2011-04-23 16:10 ` Re: How to configure a read-only database server? Robert Haas <robertmhaas@gmail.com>
0 siblings, 4 replies; 7+ messages in thread
From: Stefan Keller @ 2011-04-18 22:08 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to configure a read-only database server?
2011-04-18 22:08 How to configure a read-only database server? Stefan Keller <sfkeller@gmail.com>
@ 2011-04-19 07:45 ` philippe <pg@rimbault.org>
3 siblings, 0 replies; 7+ messages in thread
From: philippe @ 2011-04-19 07:45 UTC (permalink / raw)
To: pgsql-performance
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to configure a read-only database server?
2011-04-18 22:08 How to configure a read-only database server? Stefan Keller <sfkeller@gmail.com>
@ 2011-04-19 08:57 ` Robert Klemme <shortcutter@googlemail.com>
3 siblings, 0 replies; 7+ messages in thread
From: Robert Klemme @ 2011-04-19 08:57 UTC (permalink / raw)
To: Stefan Keller <sfkeller@gmail.com>; +Cc: pgsql-performance
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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to configure a read-only database server?
2011-04-18 22:08 How to configure a read-only database server? Stefan Keller <sfkeller@gmail.com>
@ 2011-04-19 14:30 ` Greg Smith <greg@2ndQuadrant.com>
3 siblings, 0 replies; 7+ messages in thread
From: Greg Smith @ 2011-04-19 14:30 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to configure a read-only database server?
2011-04-18 22:08 How to configure a read-only database server? Stefan Keller <sfkeller@gmail.com>
@ 2011-04-23 16:10 ` Robert Haas <robertmhaas@gmail.com>
2011-04-24 09:38 ` Re: How to configure a read-only database server? Stefan Keller <sfkeller@gmail.com>
3 siblings, 1 reply; 7+ messages in thread
From: Robert Haas @ 2011-04-23 16:10 UTC (permalink / raw)
To: Stefan Keller <sfkeller@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to configure a read-only database server?
2011-04-18 22:08 How to configure a read-only database server? Stefan Keller <sfkeller@gmail.com>
2011-04-23 16:10 ` Re: How to configure a read-only database server? Robert Haas <robertmhaas@gmail.com>
@ 2011-04-24 09:38 ` Stefan Keller <sfkeller@gmail.com>
2011-04-24 12:37 ` Re: How to configure a read-only database server? Tomas Vondra <tv@fuzzy.cz>
0 siblings, 1 reply; 7+ messages in thread
From: Stefan Keller @ 2011-04-24 09:38 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to configure a read-only database server?
2011-04-18 22:08 How to configure a read-only database server? Stefan Keller <sfkeller@gmail.com>
2011-04-23 16:10 ` Re: How to configure a read-only database server? Robert Haas <robertmhaas@gmail.com>
2011-04-24 09:38 ` Re: How to configure a read-only database server? Stefan Keller <sfkeller@gmail.com>
@ 2011-04-24 12:37 ` Tomas Vondra <tv@fuzzy.cz>
0 siblings, 0 replies; 7+ messages in thread
From: Tomas Vondra @ 2011-04-24 12:37 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2011-04-24 12:37 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-04-18 22:08 How to configure a read-only database server? Stefan Keller <sfkeller@gmail.com>
2011-04-19 07:45 ` philippe <pg@rimbault.org>
2011-04-19 08:57 ` Robert Klemme <shortcutter@googlemail.com>
2011-04-19 14:30 ` Greg Smith <greg@2ndQuadrant.com>
2011-04-23 16:10 ` Robert Haas <robertmhaas@gmail.com>
2011-04-24 09:38 ` Stefan Keller <sfkeller@gmail.com>
2011-04-24 12:37 ` Tomas Vondra <tv@fuzzy.cz>
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