Thread: Pg 8.3 tuning recommendations for embedded low-memory device (for OLPC :-) )

Hi,

I am working on the School Server (aka XS: a Fedora 9 spin, tailored
to run on fairly limited hw), I'm preparing the configuration settings
for it. It's a somewhat new area for me -- I've done a lot of tuning
with Pg 7.4 to 8.2 on mid-range hardware (several GBs RAM, RAID
controllers, many spindles, etc) and written and maintained lots of sw
that uses Pg... but this is... different. So I'm interested in hearing
from other people that may be working in similar low-mem
configurations.

In brief:

 - The workload is relatively "light": mainly Moodle and Mediawiki.
Small datasets, but those webapps are somewhat "intensive" in that
they'll do >30 SELECTs  + 1 INSERT (to a log table) before they serve
a page. To make things more interesting, we sometimes get teachers
saying "and now we all click [OK]" -- on the apache/php side there are
a few tricks to tame the thundering herd, but Pg will still have to
handle some sudden traffic spikes. I'll patch that INSERT on the
webapp side to use the new async commits.

 - The XS will (in some locations) be hooked to *very* unreliable
power... uncontrolled shutdowns are the norm. I don't expect this to
be a problem with Pg :-)

 - After a bad shutdown, graceful recovery is the most important
aspect. If a few transactions were lost we can cope.

 - The XS hardware runs many services (mostly webbased), so Pg gets
only a limited slice of memory. To make matters worse, I *really*
don't want the webservices processes (Pg, Apache/PHP) to get paged
out. So I am interested in pegging the max (non-mmapped) memory Pg
will take.

 - The XS hw is varied. In small schools it may have 256MB RAM (likely
to be running on XO hardware + usb-connected ext hard-drive).
Medium-to-large schools will have the recommended 1GB RAM and a cheap
SATA disk. A few very large schools will be graced with more RAM (2 or
4GB).

... so RAM allocation for Pg will prob range between 32MB at the
lower-end and 128MB/196MB at the 1GB "recommended" RAM.

My main question is: for the moodle/mediawiki workload, how would you
tune Pg 8.3 so that

  - it does not allocate directly more than 32MB / 196MB? (Here, I
assume that the linux kernel with be smart about mmapped stuff. Is
that a reasonable assumption with current kernels and Pg?)

 - still gives us good thoughput? :-)

cheers,




m
--
 martin.langhoff@gmail.com
 martin@laptop.org -- School Server Architect
 - ask interesting questions
 - don't get distracted with shiny stuff - working code first
 - http://wiki.laptop.org/go/User:Martinlanghoff

On Mon, Sep 15, 2008 at 11:55 AM, Martin Langhoff
<martin.langhoff@gmail.com> wrote:
> ... so RAM allocation for Pg will prob range between 32MB at the
> lower-end and 128MB/196MB at the 1GB "recommended" RAM.

Assuming a 128MB target, I've been poking and probing with the
postgresql.conf and the new settings...

 - 1/4 of it to shared buffers
 - 45 max connections - matched to a max of 40 apache/php processes
 - 2MB work_mem (some complex queries in Moodle require sorts)

means 32MB shmem and an unlikely maximum of 90MB taken for work_mem.
There is 6MB left over in my "budget" which I expect will be consumed
by per-connection overheads. When I start up Pg on this machine,
ps_mem.py (a nice smap parse-and-summarise tool) tells me a completely
idle postmaster:

 Private  +   Shared  =  RAM used    Program
(...)
  2.9 MiB +   1.0 MiB =   3.9 MiB    postmaster (6)

These are the changes I'm making to the default postgresql.conf:

+max_connections = 45
+## work_mem can also cost up to max_connections * work_mem
+
+shared_buffers = 32MB
+temp_buffers = 8MB
+max_prepared_transactions = 5
+work_mem = 2MB
+maintenance_work_mem = 16MB
+max_stack_depth = 2MB
+wal_buffers = 1MB
+wal_writer_delay = 1000ms

cheers,



m
--
 martin.langhoff@gmail.com
 martin@laptop.org -- School Server Architect
 - ask interesting questions
 - don't get distracted with shiny stuff - working code first
 - http://wiki.laptop.org/go/User:Martinlanghoff

I was working in the past with postgres in some machines with only
192mb ram shared with Linux Os (redhat9 and conectiva10) with KDE,
Apache and Postfix, the exact version was 7.4 and pgsql work fine
withput problems and many coneccionts because this machine were a
public web server. The configutation was the default configuration in
Conectiva without changes and all work fine.

Now I am trying to compile Pgsql in some architectures like Via but
now my problem is the OS (Linux of course), I hope to install Pgsql
8.3 in a machine with very little resources like an OLPC (I am trying
to have an AMD Geod in a Mini ITX mainboard).

good luck


2008/9/14 Martin Langhoff <martin.langhoff@gmail.com>:
> On Mon, Sep 15, 2008 at 11:55 AM, Martin Langhoff
> <martin.langhoff@gmail.com> wrote:
>> ... so RAM allocation for Pg will prob range between 32MB at the
>> lower-end and 128MB/196MB at the 1GB "recommended" RAM.
>
> Assuming a 128MB target, I've been poking and probing with the
> postgresql.conf and the new settings...
>
>  - 1/4 of it to shared buffers
>  - 45 max connections - matched to a max of 40 apache/php processes
>  - 2MB work_mem (some complex queries in Moodle require sorts)
>
> means 32MB shmem and an unlikely maximum of 90MB taken for work_mem.
> There is 6MB left over in my "budget" which I expect will be consumed
> by per-connection overheads. When I start up Pg on this machine,
> ps_mem.py (a nice smap parse-and-summarise tool) tells me a completely
> idle postmaster:
>
>  Private  +   Shared  =  RAM used       Program
> (...)
>  2.9 MiB +   1.0 MiB =   3.9 MiB       postmaster (6)
>
> These are the changes I'm making to the default postgresql.conf:
>
> +max_connections = 45
> +## work_mem can also cost up to max_connections * work_mem
> +
> +shared_buffers = 32MB
> +temp_buffers = 8MB
> +max_prepared_transactions = 5
> +work_mem = 2MB
> +maintenance_work_mem = 16MB
> +max_stack_depth = 2MB
> +wal_buffers = 1MB
> +wal_writer_delay = 1000ms
>
> cheers,
>
>
>
> m
> --
>  martin.langhoff@gmail.com
>  martin@laptop.org -- School Server Architect
>  - ask interesting questions
>  - don't get distracted with shiny stuff - working code first
>  - http://wiki.laptop.org/go/User:Martinlanghoff
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



--
Inscribete en las listas de APESOL
http://www.apesol.org/listas.php

Visita
http://www.eqsoft.net
Manuales, noticias, foros, etc.

On Mon, Sep 15, 2008 at 12:56 PM, Martin Langhoff
<martin.langhoff@gmail.com> wrote:
> Assuming a 128MB target, I've been poking and probing with the
> postgresql.conf and the new settings...

Actually, I misreported the host memory - 128MB is not in the picture
anymore. The minium RAM we expect to operate with is 256MB (on the XO
hardware).

Do these settings make sense?

### 256MB RAM, 32MB for Pg
### - 16MB shmem
### - 15 max_connections (to match: 12 apache processes)
### - 15MB max for workmem: 1MB * max_connections

### 512MB RAM, 96MB for Pg
### - 24MB shmem
### - 30 max_connections (to match: 25 apache processes)
### - 60MB max for workmem: 2MB * max_connections

### 1GB RAM, 128MB for Pg
### - 32MB shmem
### - 45 max_connections (to match: 45 apache processes)
### - 90MB max for workmem: 2MB * max_connections

## 2GB RAM, 256MB for Pg
### - 64MB shmem
### - 60 max_connections (to match: 25 apache processes)
### - 180MB max for workmem: 3MB * max_connections

the config files, plus init script doing the selection can be seen at
http://dev.laptop.org/git?p=projects/xs-config;a=commitdiff;h=d2f3fe29295717d6727044e8b6152b5c3e204531

cheers,



martin
--
 martin.langhoff@gmail.com
 martin@laptop.org -- School Server Architect
 - ask interesting questions
 - don't get distracted with shiny stuff - working code first
 - http://wiki.laptop.org/go/User:Martinlanghoff

On 2008-09-15 01:55, Martin Langhoff wrote:

> I am working on the School Server (aka XS: a Fedora 9 spin, tailored
> to run on fairly limited hw)

I think Fedora or any spin of it is a bad choice for a server. It's
support lifecycle is so short you'll have to reinstall it every year. A
very similar to Fedora and also free CentOS is supported for 5 years.

Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
                                                      Winnie the Pooh

On Mon, 15 Sep 2008, Martin Langhoff wrote:

> +max_prepared_transactions = 5

That is the default on 8.3, am guessing you just uncommented it but didn't
change.  If you're not actually using prepared transactions anywhere, you
may very well be able to drive memory use down a touch more by lowering
this to zero.  If you're not sure, the easy but somewhat harsh way to find
out is to set it that low on a test system and see if everything still
works.  If you're using them, 5 is actually too low; you'd want one for
every connection to be safe.

> +wal_writer_delay = 1000ms

Presumably your goal is to lower how often transactions get written to
disk to lower overhead, right?  You mentioned in your first message you
could handle some of that even if it's at the expense of robustness on
crash.  In that case, what you also need to set here is:

synchronous_commit = off

When then lets wal_writer_delay do what I think you want.  See
http://www.postgresql.org/docs/8.3/interactive/wal-async-commit.html for
more info.

Other than that little bit of tweaking, it looks like you've got a good
handle on the memory allocation model.  The other parameter you should be
setting is effective_cache_size, to about how much total RAM is available
for PostgreSQL to use including the OS buffer cache.  That's probably at
least 1/2 of the RAM in each system, you can look at what's leftover after
the system is running to get a rough value there.  This is only used for
estimating what size of queries could be handled by the system, it's not a
memory allocation.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

On Monday 15 September 2008 02:42:32 Greg Smith wrote:
> On Mon, 15 Sep 2008, Martin Langhoff wrote:
> > +max_prepared_transactions = 5
>
> That is the default on 8.3, am guessing you just uncommented it but didn't
> change.  If you're not actually using prepared transactions anywhere, you
> may very well be able to drive memory use down a touch more by lowering
> this to zero.  If you're not sure, the easy but somewhat harsh way to find
> out is to set it that low on a test system and see if everything still
> works.  If you're using them, 5 is actually too low; you'd want one for
> every connection to be safe.
>

neither moodle or mediawiki should be using prepared transactions.

> > +wal_writer_delay = 1000ms
>
> Presumably your goal is to lower how often transactions get written to
> disk to lower overhead, right?  You mentioned in your first message you
> could handle some of that even if it's at the expense of robustness on
> crash.  In that case, what you also need to set here is:
>
> synchronous_commit = off
>
> When then lets wal_writer_delay do what I think you want.  See
> http://www.postgresql.org/docs/8.3/interactive/wal-async-commit.html for
> more info.
>
> Other than that little bit of tweaking, it looks like you've got a good
> handle on the memory allocation model.  The other parameter you should be
> setting is effective_cache_size, to about how much total RAM is available
> for PostgreSQL to use including the OS buffer cache.  That's probably at
> least 1/2 of the RAM in each system, you can look at what's leftover after
> the system is running to get a rough value there.  This is only used for
> estimating what size of queries could be handled by the system, it's not a
> memory allocation.
>

Call me crazy, but I think you need to drop postgres and maybe even template0
databases from the system, just to reduce overall footprint, plus gives you
less databases to have to keep track of wrt autovacuum and such.

Also, if your disk is limited, you might want to play with the
autovacuum_max_freeze_age and the corresponding vacuum settings to try and
reduce pg_clog size.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

On Mon, Sep 15, 2008 at 6:42 PM, Greg Smith <gsmith@gregsmith.com> wrote:
> On Mon, 15 Sep 2008, Martin Langhoff wrote:
>
>> +max_prepared_transactions = 5
>
> That is the default on 8.3, am guessing you just uncommented it but didn't
> change.  If you're not actually using prepared transactions anywhere, you
> may very well be able to drive memory use down a touch more by lowering this
> to zero.

Nothing uses prepared transactions, so I'll set it to 0.

>> +wal_writer_delay = 1000ms
>
> Presumably your goal is to lower how often transactions get written to disk
> to lower overhead, right?  You mentioned in your first message you could
> handle some of that even if it's at the expense of robustness on crash.  In
> that case, what you also need to set here is:
>
> synchronous_commit = off
>
> When then lets wal_writer_delay do what I think you want.  See
> http://www.postgresql.org/docs/8.3/interactive/wal-async-commit.html for
> more info.

I'm somewhat hesitant on completely avoiding sync. Moodle for example
rarely writes to the DB (as most web content mgmt) except for a log
table that gets an insert per pageview. For the time being, I plan to
get _that_ insert in a async commit --  I think this will give me 99%
of the advantage, and keep the transactional sanity for the data that
matters.

Is that a reasonable approach?

> Other than that little bit of tweaking, it looks like you've got a good
> handle on the memory allocation model.

Thanks for confirming that :-)

>  The other parameter you should be
> setting is effective_cache_size, to about how much total RAM is available
> for PostgreSQL to use including the OS buffer cache.  That's probably at
> least 1/2 of the RAM in each system, you can look at what's leftover after
> the system is running to get a rough value there.  This is only used for
> estimating what size of queries could be handled by the system, it's not a
> memory allocation.

Ok. Will set that one too.

And *thanks* -- these are great hints.





martin
--
 martin.langhoff@gmail.com
 martin@laptop.org -- School Server Architect
 - ask interesting questions
 - don't get distracted with shiny stuff - working code first
 - http://wiki.laptop.org/go/User:Martinlanghoff

On Tue, Sep 16, 2008 at 8:36 AM, Robert Treat
<xzilla@users.sourceforge.net> wrote:
> Call me crazy, but I think you need to drop postgres and maybe even template0
> databases from the system, just to reduce overall footprint, plus gives you
> less databases to have to keep track of wrt autovacuum and such.

What would be the impact of running w/o postgres and template0? They
seem to take 13MB on a freshly init'd Pg -- I can afford to have them
there if needed. In general, I am not expecting to be super-tight on
disk... (not yet at least)

> Also, if your disk is limited, you might want to play with the
> autovacuum_max_freeze_age and the corresponding vacuum settings to try and
> reduce pg_clog size.

Intriguing. I'll look into those.

thanks!


m
--
 martin.langhoff@gmail.com
 martin@laptop.org -- School Server Architect
 - ask interesting questions
 - don't get distracted with shiny stuff - working code first
 - http://wiki.laptop.org/go/User:Martinlanghoff

On Mon, Sep 15, 2008 at 3:46 PM, Martin Langhoff
<martin.langhoff@gmail.com> wrote:
> On Tue, Sep 16, 2008 at 8:36 AM, Robert Treat
> <xzilla@users.sourceforge.net> wrote:
>> Call me crazy, but I think you need to drop postgres and maybe even template0
>> databases from the system, just to reduce overall footprint, plus gives you
>> less databases to have to keep track of wrt autovacuum and such.
>
> What would be the impact of running w/o postgres and template0? They
> seem to take 13MB on a freshly init'd Pg -- I can afford to have them
> there if needed. In general, I am not expecting to be super-tight on
> disk... (not yet at least)

template0 is the database used in case of emergency to restore
template1 should you do something stupid.  Generally it's never used.

postgres is the database that all the command line utils (createdb,
vacuumdb, etc) all use to connect to.