Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a RAID10-Server and have Postgres writing the log(pg_xlog a.k.a WAL) onto the same RAID10-Array.

I use the WAL in sync-mode with a big buffer and do alot of bulk inserts and updates, so that buffer is hopfully used.

Is it an good idea to split the log onto a extra disk, not in the raid10? I could stuff in a RAID with 2 disks, but would that really improve anything?

I looking forward if someone could name a few pros and cons.

Thanks alot

share|improve this question

migrated from stackoverflow.com Jan 3 '12 at 11:48

This question came from our site for professional and enthusiast programmers.

3 Answers 3

up vote 4 down vote accepted

You asked for pros and cons, the con that few people mention when giving the standard advice to separate out the logs is that it will slow your reads because you now have fewer spindles to dedicate to reads. Which way you go depends on the ratio of reads to writes in your database. If it is 99% read (say a web server scenario) then you may end up not gaining anything by separating out your logs.

The other possible gotcha that is rarely mentioned is that putting your logs on a separate spindle only gives you sequential writes if you only put a single databases logs on that spindle. If you have a server with 10 active databases then putting all of their logs on the same disk does not give you sequential writes, they will be spread about on the different log files. Then again putting them on 10 separate mirrored drives means you need to dedicate 20 drives to logs which also may not be possible. A good solution in this case is to use SSD storage for your log files which performs MUCH better on random writes when you find your environment just does not have sequential writes.

share|improve this answer

Yes, a separate RAID set will likely improve things performance-wise, because the disks will be dedicated to the xlog, which is where all your write contention will be. It's also more or less entirely sequential (the writes are sequential, but there is also filesystem metadata), which the raid controller than then optimize more.

The downside of it is obviously that you will likely waste a lot of space on this disk, because you can't buy disks small enough not to be a waste.

share|improve this answer
    
Okay I researched a little. Splitting of disks from the array to use as a separate log pulls down the general performance on the data (less disks). I thing a good approach is to have the log on a extra partition as long as the RAID-Controller can handle fsync for a partition separatly and doesn't flush all the dirty stuff out of the cache. –  Franz Kafka Mar 9 '11 at 14:36

From PostgreSQL documentation:

It is advantageous if the log is located on a different disk from the main database files. This can be achieved by moving the pg_xlog directory to another location (while the server is shut down, of course) and creating a symbolic link from the original location in the main data directory to the new location.

In other sources one can find that moving logs to separate disk may speed up your db performance 10-50%.

share|improve this answer
    
I had suggested this in an earlier post dba.stackexchange.com/a/45228/877 the person who used it got a 10% increase in performance. BTW Welcome to the DBA StackExchange. –  RolandoMySQLDBA Aug 10 '13 at 22:21
    
thx ;) I'm gonna test it by myself this week and will share the results. –  Borys Aug 11 '13 at 0:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.