WAL Archiving frequency

Hi,

I've setup WAL file archiving to my SAN storage over the network
(using an NFS mount to the file server).

I've noticed that it's archiving the 16 MB wal files about once every
minute. Is this normal? There's a huge number of files in my
wal_archives directory now.

Is there a parameter to tell it not to archive so frequently? Or is
this nothing to be too concerned about?

Thanks,

____________________________________________________________________
Brendan Duddridge | CTO | 403-277-5591 x24 | brendan(at)clickspace(dot)com

ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB T2G 0V9

http://www.clickspace.com

Brendan Duddridge <brendan(at)clickspace(dot)com> writes:
> I've noticed that it's archiving the 16 MB wal files about once every
> minute. Is this normal? There's a huge number of files in my
> wal_archives directory now.
> Is there a parameter to tell it not to archive so frequently? Or is
> this nothing to be too concerned about?

If you've got a reasonably active database, 16MB/minute is not an
unlikely rate of WAL generation. You can probably slow it down a bit by
increasing the checkpoint-spacing parameters, but you can't just say
"don't generate so much WAL please". Either you want a log or you don't.

Basically, you have to take base backups often enough that the total
number of WAL archive files needed stays under your threshold of pain...

regards, tom lane

Thanks Tom,

I just didn't know if that was normal or not. I actually saw 4 WAL
files / minute today. I set checkpoint_segments = 128, so maybe that
will help.

Thanks,

____________________________________________________________________
Brendan Duddridge | CTO | 403-277-5591 x24 | brendan(at)clickspace(dot)com

ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB T2G 0V9

http://www.clickspace.com

On Mar 31, 2006, at 8:47 PM, Tom Lane wrote:

> Brendan Duddridge <brendan(at)clickspace(dot)com> writes:
>> I've noticed that it's archiving the 16 MB wal files about once every
>> minute. Is this normal? There's a huge number of files in my
>> wal_archives directory now.
>> Is there a parameter to tell it not to archive so frequently? Or is
>> this nothing to be too concerned about?
>
> If you've got a reasonably active database, 16MB/minute is not an
> unlikely rate of WAL generation. You can probably slow it down a
> bit by
> increasing the checkpoint-spacing parameters, but you can't just say
> "don't generate so much WAL please". Either you want a log or you
> don't.
>
> Basically, you have to take base backups often enough that the total
> number of WAL archive files needed stays under your threshold of
> pain...
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

On March 31, 2006 10:39 pm, Brendan Duddridge <brendan(at)clickspace(dot)com>
wrote:
> Thanks Tom,
>
> I just didn't know if that was normal or not. I actually saw 4 WAL
> files / minute today. I set checkpoint_segments = 128, so maybe that
> will help.

That's normal, if your database is busy. I get up to 6 a minute during busy
times. They compress quite well, fortunately.

--
"None are more hopelessly enslaved than those who falsely believe they are
free." -- Johann W. Von Goethe