Re: WAL segments size

On Mon, 12 Nov 2007, Bruno Almeida do Lago wrote:

> One of our PostgreSQL databases is generating archive logs too frequently,
> since the number of transactions/s (inserts & updates) is very high.

If the problem is that the archive logs are too frequent, then suggestions
so far like increasing checkpoint_segments aren't going to help you;
you've asked the right question.

> I was looking for a parameter on postgresql.conf to increase the WAL
> segments size, but according to documentation this can only be changed while
> building PostgreSQL.

You have to edit src/include/pg_config_manual.h and adjust XLOG_BLCKSZ,
which defaults to 8192. Note the warning in there about direct I/O, which
you may be using depending on your wal_sync_method.

> 1. For databases with this kind of behavior (high number of inserts &
> updates), wouldn't be better (from the performance point of view) to have
> larger WAL segments?

Sure. There are commercial PostgreSQL distributions that increase some of
these block sizes for reasons like this.

> 2. Is there a special reason to not allow the WAL size to be changed on
> postgresql.conf (version 8.2 or 8.3)?

When you change this number, you actually have to re-create the database
using initdb in order to rebuild it that way. So it's impractical to
change it at run time, and playing with the value can be dangerous.
That's why it doesn't make sense for there to be an easy interface to make
this change. If you wanted to do this for your application, you'll have
to go through a dump, initdb with the change, then reload on your
database.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD