Thread: WAL segments size
Hi, One of our PostgreSQL databases is generating archive logs too frequently, since the number of transactions/s (inserts & updates) is very high. 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. Some questions came up to my mind when I read that: 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? 2. Is there a special reason to not allow the WAL size to be changed on postgresql.conf (version 8.2 or 8.3)? Best regards, Bruno Lago
On Mon, Nov 12, 2007 at 05:14:24PM -0300, Bruno Almeida do Lago wrote: > 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 mean this: http://www.postgresql.org/docs/8.2/static/runtime-config-wal.html#GUC-CHECKPOINT-SEGMENTS ? That doesn't say you have to set it at compile time. You can't change it without _restarting_ Postgres. That's because it's something that has to be initialized at start up. A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke
On Nov 12, 2007 2:14 PM, Bruno Almeida do Lago <teolupus@gmail.com> wrote: > Hi, > > One of our PostgreSQL databases is generating archive logs too frequently, > since the number of transactions/s (inserts & updates) is very high. > > 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. There's no great need to change the SIZE, just increase the number of WAL segments. More importantly, look into tuning your bgwriter so it keeps up with the write load and you don't have a lot of checkpoints. There was a post here last week: http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm that had a very good tutorial on bgwriting and how to configure it.
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@gregsmith.com http://www.gregsmith.com Baltimore, MD