Thread: Sizing and striping log area
Forgive my ignorance, but what should I consider when sizing the partition Also, on a related note, would putting the pg_xlog onto a RAID-0 array Now straying even further from the original subject... Would hardware vs Thanks for any help. |
Join Excite! - http://www.excite.com
The most personalized portal on the Web!
On 13 Oct 2002 at 16:26, Brett wrote: >Forgive my ignorance, but what should I consider when sizing the partition >which houses the pg_xlog area? Are logs cleared out every once in awhile >or do they continue to accumulate? Let's say your DB is around 4 GB, >what should the log area size be, or does the size of the DB not matter, >if not which factors should be considered? Well, you know the exact size of pg_xlogs in advance. It's 16MB*number of WAL files. So for 40 WAL files in postgresql.conf, you get 640MB of pg_xlog. Yes, they are recycled. So they don't grow beyond this size. But from performance point of view, what's important is to put them on separate drive, much better on separate IO channel. >Now straying even further from the original subject... Would hardware vs >software RAID 0 not make a difference in reality? Software striping You can try. People here have come up with figures that indicate that it does not make difference. But software RAID requires bit more BW as OS has to send data to different drives by itself. But software RAID is easier to upgrade and doesn't cost much in terms of CPU. Take your pick.. HTH Bye Shridhar -- 17th Rule of Friendship: A friend will refrain from telling you he picked up the same amount of life insurance coverage you did for half the price when yours is noncancellable. -- Esquire, May 1977
""Shridhar Daithankar"" <shridhar_daithankar@persistent.co.in> wrote in message news:3DAAACC3.24747.8A66792@localhost... > On 13 Oct 2002 at 16:26, Brett wrote: > >Forgive my ignorance, but what should I consider when sizing the partition > >which houses the pg_xlog area? Are logs cleared out every once in awhile > >or do they continue to accumulate? Let's say your DB is around 4 GB, > >what should the log area size be, or does the size of the DB not matter, > >if not which factors should be considered? > > Well, you know the exact size of pg_xlogs in advance. It's 16MB*number of WAL > files. So for 40 WAL files in postgresql.conf, you get 640MB of pg_xlog. > > Yes, they are recycled. So they don't grow beyond this size. But from > performance point of view, what's important is to put them on separate drive, > much better on separate IO channel. Are they archived like Oracle logs? Oracle writes to a log area and reads from the log area to generate archives. The archives are then used for recovery in case of corruption. This is important because reading from the drive throws off the head used for writing so seeking could be involved given this situation where reading and writing happen on the same drive concurrently. Oracle minimizes this by filling up, say, 500k of a certain disk before writing the logs to the next disk. Then it reads the 500k of logs and moves those to the archive. But if postgres doesn't use archives then this isn't an issue. Which leads me to another question... Does Postgres use archive logs and how does Postgres recover if it doesn't use archive logs? Or does it not recover as well as Oracle? I realize that you get what you pay for so I'm prepared for the truth. :)
news.postgresql.org wrote: > Are they archived like Oracle logs? Oracle writes to a log area and reads > from the log area to generate archives. The archives are then used for > recovery in case of corruption. This is important because reading from the > drive throws off the head used for writing so seeking could be involved > given this situation where reading and writing happen on the same drive > concurrently. Oracle minimizes this by filling up, say, 500k of a certain > disk before writing the logs to the next disk. Then it reads the 500k of > logs and moves those to the archive. But if postgres doesn't use archives > then this isn't an issue. > > Which leads me to another question... Does Postgres use archive logs and how > does Postgres recover if it doesn't use archive logs? Or does it not > recover as well as Oracle? We sync() the info to disk and then recycle the logs. 7.4 will have archive of logs, and hance point-in-time recovery. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On 14 Oct 2002 at 14:40, news.postgresql.org wrote: > Are they archived like Oracle logs? Oracle writes to a log area and reads > from the log area to generate archives. The archives are then used for > recovery in case of corruption. This is important because reading from the > drive throws off the head used for writing so seeking could be involved > given this situation where reading and writing happen on the same drive > concurrently. Oracle minimizes this by filling up, say, 500k of a certain > disk before writing the logs to the next disk. Then it reads the 500k of > logs and moves those to the archive. But if postgres doesn't use archives > then this isn't an issue. As Bruce has pointed out, WALs are recycled currently and archival is added in 7.4(IIRC). However I think postgresql recycles WAL files only after data is committed. So at any point of time, if you have your WAL files in physically consistent state, you can recover from them. > I realize that you get what you pay for so I'm prepared for the truth. :) You could have done without that comment. Bye Shridhar -- Van Roy's Law: Honesty is the best policy - there's less competition.Van Roy's Truism: Life is a whole series of circumstances beyond your control.