pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSeems like a large amount of xlog files
3+ messages / 2 participants
[nested] [flat]
* Seems like a large amount of xlog files
@ 2011-05-24 23:19 Michael Holt <michael@aers.ca>
0 siblings, 1 reply; 3+ messages in thread
From: Michael Holt @ 2011-05-24 23:19 UTC (permalink / raw)
To: pgsql-admin
We're trying to do some performance tuning on a couple of postgres boxes and
I've noticed there seems to be a lot of activity in the pg_xlog folder.
There seems to consitently be 132 files with many being written almost every
minute. Looking just now I see 40 written in the last minute and 48 in the
minute before that. I'm having some difficulty determining why there are so
many WAL files being written so frequently as this is normally a
low-write/high read server. There are some things causing implicit
transactions in some old code, but they do not actually write to any tables.
So i have two questions: 1) Does simply running a transaction (implicit or
explicit) cause writing to the WAL? 2) Is there any simple way to track what
may be causing WAL writes?
Alternatively, is there a simple way to see what is being recorded in these
files?
--
*Michael Holt*, *Database Administrator* | A.E.R.S. & TERAPEAK
2307-4464 Markham Street, Victoria, BC, CANADA V8Z 7X8
+1 250 483 3271 (FAX)
www.terapeak.com | www.aers.ca | [image:
facebook]<http://www.facebook.com/pages/Terapeak/21544372056;| [image:
twitter] <http://twitter.com/terapeak;
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Seems like a large amount of xlog files
@ 2011-05-25 01:59 Fujii Masao <masao.fujii@gmail.com>
parent: Michael Holt <michael@aers.ca>
0 siblings, 1 reply; 3+ messages in thread
From: Fujii Masao @ 2011-05-25 01:59 UTC (permalink / raw)
To: Michael Holt <michael@aers.ca>; +Cc: pgsql-admin
On Wed, May 25, 2011 at 8:19 AM, Michael Holt <michael@aers.ca> wrote:
>
> We're trying to do some performance tuning on a couple of postgres boxes and I've noticed there seems to be a lot of activity in the pg_xlog folder. There seems to consitently be 132 files with many being written almost every minute. Looking just now I see 40 written in the last minute and 48 in the minute before that. I'm having some difficulty determining why there are so many WAL files being written so frequently as this is normally a low-write/high read server. There are some things causing implicit transactions in some old code, but they do not actually write to any tables. So i have two questions: 1) Does simply running a transaction (implicit or explicit) cause writing to the WAL? 2) Is there any simple way to track what may be causing WAL writes?
Is archive_timeout more than zero in your setting?
If yes, that setting might have caused such a large number of
WAL files.
Regards,
--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Seems like a large amount of xlog files
@ 2011-05-26 01:42 Fujii Masao <masao.fujii@gmail.com>
parent: Fujii Masao <masao.fujii@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Fujii Masao @ 2011-05-26 01:42 UTC (permalink / raw)
To: Michael Holt <michael@aers.ca>; +Cc: pgsql-admin
CC to pgsql-admin@postgresql.org
On Wed, May 25, 2011 at 5:10 PM, Michael Holt <michael@aers.ca> wrote:
> archive_timeout is not set. The default is to have it off right?
Yes.
My another guess is that those many files were generated by
initial data load and have remained up to now. No?
Anyway, you can check whether your system is still generating
so many WAL files now, by periodically calling
pg_current_xlog_location(). This function returns the current
WAL write location, so if the rate of increase of that location
is small, you don't need to worry about the number of files.
Regards,
--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-05-26 01:42 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-05-24 23:19 Seems like a large amount of xlog files Michael Holt <michael@aers.ca>
2011-05-25 01:59 ` Fujii Masao <masao.fujii@gmail.com>
2011-05-26 01:42 ` Fujii Masao <masao.fujii@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox