Thread: How to size a log file partition

How to size a log file partition

From
"Thorne, Francis"
Date:

Just after a little bit of advice, I will be setting up a new database server that will be running a database around the 200GB size.  To make sure that I get the best performance I plan to place the database files on one RAID 10 partition and the logs on another partition either RAID 10 or RAID 1.  At the moment though I'm struggling to size the partition of the log file.  Is there a recommendation to how I could go about sizing this partition ?

Any suggestions would be much appreciated

Thanks
Frant101
___________________________________________________

This email is intended for the named recipient. The information contained
in it is confidential. You should not copy it for any purposes, nor
disclose its contents to any other party. If you received this email
in error, please notify the sender immediately via email, and delete
it from your computer.

Any views or opinions presented are solely those of the author and do not
necessarily represent those of the company.

Cromwell Tools Limited, PO Box 14, 65 Chartwell Drive
Wigston, Leicester LE18 1AT. Tel 0116 2888000
Registered in England and Wales, Reg No 00986161
VAT GB 115 5713 87 900
__________________________________________________

Re: How to size a log file partition

From
Shane Ambler
Date:
Thorne, Francis wrote:
> Just after a little bit of advice, I will be setting up a new database
> server that will be running a database around the 200GB size.  To make
> sure that I get the best performance I plan to place the database files
> on one RAID 10 partition and the logs on another partition either RAID
> 10 or RAID 1.  At the moment though I'm struggling to size the partition
> of the log file.  Is there a recommendation to how I could go about
> sizing this partition ?
>
> Any suggestions would be much appreciated
>

Formatting and partitioning disks rather depends on what OS you have,
and is better suited to support channels for your OS.


If however you are talking about having postgresql rotate it's log files
when it reaches a certain size then you should look in your
postgresql.conf which you can find in your data dir.

If you are using stderr as log output then log_rotation_size = 10MB  is
the default setting. There are other logging option as well that you may
want to tweek.

If you are using pgadmin then you can adjust this through the server
configuration settings.


If on the other hand you are using another option like syslog for log
output then you need to look into other options for log file rotation.



--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz


Re: How to size a log file partition

From
"Kevin Grittner"
Date:
"Thorne, Francis" <thornef@cromwell.co.uk> wrote:

> I plan to place the database files on one RAID 10 partition and the
> logs on another partition either RAID 10 or RAID 1.  At the moment
> though I'm struggling to size the partition of the log file.  Is
> there a recommendation to how I could go about sizing this
> partition?

It sounds to me like you're talking about the Write Ahead Log (WAL)
files, right?  If so, on this page:

http://www.postgresql.org/docs/8.4/interactive/wal-configuration.html

The docs say:

"There will always be at least one WAL segment file, and will normally
not be more than (2 + checkpoint_completion_target) *
checkpoint_segments + 1 files. Each segment file is normally 16 MB
(though this size can be altered when building the server). You can
use this to estimate space requirements for WAL."

-Kevin