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@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
