Thread
-
What exactly is postgres doing during INSERT/UPDATE ?
Joseph Shraibman <jks@selectacast.net> — 2009-08-28T06:56:23Z
If I run " dd if=/dev/zero bs=1024k of=file count=1000 " iostat shows me: Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 671.50 88.00 113496.00 176 226992 However postgres 8.3.7 doing a bulk data write (a slony slave, doing inserts and updates) doesn't go nearly as fast: Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 418.41 648.76 7052.74 1304 14176 Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 237.50 44.00 3668.00 88 7336 Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 221.50 444.00 3832.00 888 7664 Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 166.00 248.00 3360.00 496 6720 Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 163.00 480.00 3184.00 960 6368 Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 102.50 724.00 1736.00 1448 3472 Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 295.50 712.00 6004.00 1424 12008 Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 109.45 433.83 2260.70 872 4544 Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 180.00 640.00 3512.00 1280 7024 top shows the cpu usage of the pg process ranges from zero to never more than ten percent of a cpu, and that one cpu is always ninety some odd percent in iowait. So what is postgres doing (with fsync off) that causes the cpu to spend so much time in iowait? This is a 64 bit amd linux system with ext3 filesystem. free shows: total used free shared buffers cached Mem: 8116992 8085848 31144 0 103016 3098568 -/+ buffers/cache: 4884264 3232728 Swap: 6697296 2035508 4661788 -
Re: What exactly is postgres doing during INSERT/UPDATE ?
Pierre C <lists@peufeu.com> — 2009-08-28T07:52:33Z
> top shows the cpu usage of the pg process ranges from zero to never more > than ten percent of a cpu, and that one cpu is always ninety some odd > percent in iowait. So what is postgres doing (with fsync off) that > causes the cpu to spend so much time in iowait? Updating indexes ?
-
Re: What exactly is postgres doing during INSERT/UPDATE ?
Greg Smith <gsmith@gregsmith.com> — 2009-08-28T08:08:15Z
On Fri, 28 Aug 2009, Joseph S wrote: > If I run " dd if=/dev/zero bs=1024k of=file count=1000 " iostat shows me: > > Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn > sda 671.50 88.00 113496.00 176 226992 That's the sequential transfer rate of your drive. It's easier to present these numbers if you use "vmstat 1" instead; that shows the I/O in more useful units, and with the CPU stats on the same line. > However postgres 8.3.7 doing a bulk data write (a slony slave, doing inserts > and updates) doesn't go nearly as fast: In PostgreSQL, an update is: 1) A read of the old data 2) Writing out the updated data 3) Marking the original data as dead 4) Updating any indexes involved 5) Later cleaning up after the now dead row On top of that Slony may need to do its own metadata updates. This sort of workload involves random I/O rather than sequential. On regular hard drives this normally happens at a tiny fraction of the speed because of how the disk has to seek around. Typically a single drive capable of 50-100MB/s on sequential I/O will only do 1-2MB/s on a completely random workload. You look like you're getting somewhere in the middle there, on the low side which doesn't surprise me. The main two things you can do to improve this on the database side: -Increase checkpoint_segments, which reduces how often updated data has to be flushed to disk -Increase shared_buffers in order to hold more of the working set of data in RAM, so that more reads are satisfied by the database cache and less data gets evicted to disk. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
-
Re: What exactly is postgres doing during INSERT/UPDATE ?
Scott Marlowe <scott.marlowe@gmail.com> — 2009-08-28T08:29:17Z
On Fri, Aug 28, 2009 at 2:08 AM, Greg Smith<gsmith@gregsmith.com> wrote: > > This sort of workload involves random I/O rather than sequential. On > regular hard drives this normally happens at a tiny fraction of the speed > because of how the disk has to seek around. Typically a single drive > capable of 50-100MB/s on sequential I/O will only do 1-2MB/s on a completely > random workload. You look like you're getting somewhere in the middle > there, on the low side which doesn't surprise me. > > The main two things you can do to improve this on the database side: > > -Increase checkpoint_segments, which reduces how often updated data has to > be flushed to disk > > -Increase shared_buffers in order to hold more of the working set of data in > RAM, so that more reads are satisfied by the database cache and less data > gets evicted to disk. After that you have to start looking at hardware. Soimething as simple as a different drive for indexes and another for WAL, and another for the base tables can make a big difference.
-
Re: What exactly is postgres doing during INSERT/UPDATE ?
Joseph Shraibman <jks@selectacast.net> — 2009-08-28T14:25:10Z
Greg Smith wrote: > The main two things you can do to improve this on the database side: > > -Increase checkpoint_segments, which reduces how often updated data has > to be flushed to disk It fsync is turned off, does this matter so much? >
-
Re: What exactly is postgres doing during INSERT/UPDATE ?
Joseph Shraibman <jks@selectacast.net> — 2009-08-28T14:28:51Z
Scott Marlowe wrote: > After that you have to start looking at hardware. Soimething as > simple as a different drive for indexes and another for WAL, and > another for the base tables can make a big difference. > If I have 14 drives in a RAID 10 to split between data tables and indexes what would be the best way to allocate the drives for performance?
-
Re: What exactly is postgres doing during INSERT/UPDATE ?
Luke Koops <luke.koops@entrust.com> — 2009-08-29T04:20:30Z
Joseph S Wrote > If I have 14 drives in a RAID 10 to split between data tables > and indexes what would be the best way to allocate the drives > for performance? RAID-5 can be much faster than RAID-10 for random reads and writes. It is much slower than RAID-10 for sequential writes, but about the same for sequential reads. For typical access patterns, I would put the data and indexes on RAID-5 unless you expect there to be lots of sequential scans. If you do this, you can drop the random_page_cost from the default 4.0 to 1.0. That should also encourage postgres to use the index more often. I think the default costs for postgres assume that the data is on a RAID-1 array. Either that, or they are a compromise that isn't quite right for any system. On a plain old disk the random_page_cost should be 8.0 or 10.0. The division of the drives into two arrays would depend on how much space will be occupied by the tables vs the indexes. This is very specific to your database. For example, if indexes take half as much space as tables, then you want 2/3rds for tables and 1/3rd for indexes. 8 drives for tables, 5 drives for indexes, and 1 for a hot standby. The smaller array may be a bit slower for some operations due to reduced parallelism. This also depends on the intelligence of your RAID controller. Always put the transaction logs (WAL Files) on RAID-10 (or RAID-1 if you don't want to dedicate so many drives to the logs). The only significant performance difference between RAID-10 and RAID-1 is that RAID-1 is much slower (factor of 4 or 5) for random reads. I think the ratio of random reads from the transaction logs would typically be quite low. They are written sequentially and during checkpoint they are read sequentially. In the interim, the data is probably still in shared memory if it needs to be read. You don't want your transaction logs or any swapfiles on RAID-5. The slow sequential write performance can be a killer. -Luke
-
Re: What exactly is postgres doing during INSERT/UPDATE ?
Greg Stark <gsstark@mit.edu> — 2009-08-29T08:46:15Z
On Sat, Aug 29, 2009 at 5:20 AM, Luke Koops<luke.koops@entrust.com> wrote: > Joseph S Wrote >> If I have 14 drives in a RAID 10 to split between data tables >> and indexes what would be the best way to allocate the drives >> for performance? > > RAID-5 can be much faster than RAID-10 for random reads and writes. It is much slower than RAID-10 for sequential writes, but about the same for sequential reads. For typical access patterns, I would put the data and indexes on RAID-5 unless you expect there to be lots of sequential scans. That's pretty much exactly backwards. RAID-5 will at best slightly slower than RAID-0 or RAID-10 for sequential reads or random reads. For sequential writes it performs *terribly*, especially for random writes. The only write pattern where it performs ok sometimes is sequential writes of large chunks. > Always put the transaction logs (WAL Files) on RAID-10 (or RAID-1 if you don't want to dedicate so many drives to the logs). The only significant performance difference between RAID-10 and RAID-1 is that RAID-1 is much slower (factor of 4 or 5) for random reads. no, RAID-10 and RAID-1 should perform the same for reads. RAID-10 will be slower at writes by about a factor equal to the number of mirror sides. > I think the ratio of random reads from the transaction logs would typically be quite low. During normal operation the logs are *never* read, neither randomly nor sequentially. > You don't want your transaction logs or any swapfiles on RAID-5. The slow sequential write performance can be a killer. As i mentioned sequential writes are the only write case when RAID-5 sometimes ok. However the picture is complicated by transaction syncing which would make RAID-5 see it more as random i/o. In any case wal normally doesn't take much disk space so there's not much reason to use anything but RAID-1. -- greg http://mit.edu/~gsstark/resume.pdf
-
Re: What exactly is postgres doing during INSERT/UPDATE ?
Scott Marlowe <scott.marlowe@gmail.com> — 2009-08-29T13:59:33Z
On Sat, Aug 29, 2009 at 2:46 AM, Greg Stark<gsstark@mit.edu> wrote: > On Sat, Aug 29, 2009 at 5:20 AM, Luke Koops<luke.koops@entrust.com> wrote: >> Joseph S Wrote >>> If I have 14 drives in a RAID 10 to split between data tables >>> and indexes what would be the best way to allocate the drives >>> for performance? >> >> RAID-5 can be much faster than RAID-10 for random reads and writes. It is much slower than RAID-10 for sequential writes, but about the same for sequential reads. For typical access patterns, I would put the data and indexes on RAID-5 unless you expect there to be lots of sequential scans. > > That's pretty much exactly backwards. RAID-5 will at best slightly > slower than RAID-0 or RAID-10 for sequential reads or random reads. > For sequential writes it performs *terribly*, especially for random > writes. The only write pattern where it performs ok sometimes is > sequential writes of large chunks. Note that while RAID-10 is theoretically always better than RAID-5, I've run into quite a few cheapie controllers that were heavily optimised for RAID-5 and de-optimised for RAID-10. However, if it's got battery backed cache and can run in JBOD mode, linux software RAID-10 or hybrid RAID-1 in hardware RAID-0 in software will almost always beat hardware RAID-5 on the same controller.
-
Re: What exactly is postgres doing during INSERT/UPDATE ?
David Rees <drees76@gmail.com> — 2009-08-29T19:52:07Z
On Sat, Aug 29, 2009 at 1:46 AM, Greg Stark<gsstark@mit.edu> wrote: > On Sat, Aug 29, 2009 at 5:20 AM, Luke Koops<luke.koops@entrust.com> wrote: >> RAID-5 can be much faster than RAID-10 for random reads and writes. It is much slower than >> RAID-10 for sequential writes, but about the same for sequential reads. For typical access >> patterns, I would put the data and indexes on RAID-5 unless you expect there to be lots of >> sequential scans. > > That's pretty much exactly backwards. RAID-5 will at best slightly > slower than RAID-0 or RAID-10 for sequential reads or random reads. > For sequential writes it performs *terribly*, especially for random > writes. The only write pattern where it performs ok sometimes is > sequential writes of large chunks. Also note that how terribly RAID5 performs on those small random writes depends on a LOT on the implementation. A good controller with a large BBU cache will be able to mitigate the performance penalty of having to read stripes before small writes to calculate parity (of course, if the writes are really random enough, it's still not going to help much). >> Always put the transaction logs (WAL Files) on RAID-10 (or RAID-1 if you don't want to dedicate >> so many drives to the logs). The only significant performance difference between RAID-10 and >> RAID-1 is that RAID-1 is much slower (factor of 4 or 5) for random reads. > > no, RAID-10 and RAID-1 should perform the same for reads. RAID-10 will > be slower at writes by about a factor equal to the number of mirror > sides. Let's keep in mind that a 2-disk RAID-10 is really the same as a 2-disk RAID-1, it just doesn't have any mirrors to stripe over. So since you really need 4-disks for a "true" RAID-10, the performance of a RAID-10 array compared to a RAID1 array is pretty much proportional to the number of disks in the array (more disks = more performance). The "far" RAID-10 layout that is available when using Linux software raid is interesting. It will lay the data out on the disks so that you can get the streaming read performance of a RAID-0 array, but streaming write performance will suffer a bit since now the disk will have to seek to perform those writes. You can also use this layout with just 2 disks instead of RAID1. Some claim that the performance hit isn't noticeable due to write caching/IO ordering, but I have not tested it's performance using PostgreSQL. Might be a nice thing for someone to try. http://en.wikipedia.org/wiki/Non-standard_RAID_levels#Linux_MD_RAID_10 -Dave
-
Re: What exactly is postgres doing during INSERT/UPDATE ?
Merlin Moncure <mmoncure@gmail.com> — 2009-08-30T15:40:01Z
On Sat, Aug 29, 2009 at 9:59 AM, Scott Marlowe<scott.marlowe@gmail.com> wrote: > On Sat, Aug 29, 2009 at 2:46 AM, Greg Stark<gsstark@mit.edu> wrote: >> On Sat, Aug 29, 2009 at 5:20 AM, Luke Koops<luke.koops@entrust.com> wrote: >>> Joseph S Wrote >>>> If I have 14 drives in a RAID 10 to split between data tables >>>> and indexes what would be the best way to allocate the drives >>>> for performance? >>> >>> RAID-5 can be much faster than RAID-10 for random reads and writes. It is much slower than RAID-10 for sequential writes, but about the same for sequential reads. For typical access patterns, I would put the data and indexes on RAID-5 unless you expect there to be lots of sequential scans. >> >> That's pretty much exactly backwards. RAID-5 will at best slightly >> slower than RAID-0 or RAID-10 for sequential reads or random reads. >> For sequential writes it performs *terribly*, especially for random >> writes. The only write pattern where it performs ok sometimes is >> sequential writes of large chunks. > > Note that while RAID-10 is theoretically always better than RAID-5, > I've run into quite a few cheapie controllers that were heavily > optimised for RAID-5 and de-optimised for RAID-10. However, if it's > got battery backed cache and can run in JBOD mode, linux software > RAID-10 or hybrid RAID-1 in hardware RAID-0 in software will almost > always beat hardware RAID-5 on the same controller. raid 5 can outperform raid 10 on sequential writes in theory. if you are writing 100mb of actual data on, say, a 8 drive array, the raid 10 system has to write 200mb data and the raid 5 system has to write 100 * (8/7) or about 114mb. Of course, the raid 5 system has to do parity, etc. For random writes, raid 5 has to write a minimum of two drives, the data being written and parity. Raid 10 also has to write two drives minimum. A lot of people think parity is a big deal in terms of raid 5 performance penalty, but I don't -- relative to the what's going on in the drive, xor calculation costs (one of the fastest operations in computing) are basically zero, and off-lined if you have a hardware raid controller. I bet part of the problem with raid 5 is actually contention. since your write to a stripe can conflict with other writes to a different stripe. The other problem with raid 5 that I see is that you don't get very much extra protection -- it's pretty scary doing a rebuild even with a hot spare (and then you should probably be doing raid 6). On read performance RAID 10 wins all day long because more drives can be involved. merlin
-
Re: What exactly is postgres doing during INSERT/UPDATE ?
Greg Stark <gsstark@mit.edu> — 2009-08-30T15:52:07Z
On Sun, Aug 30, 2009 at 4:40 PM, Merlin Moncure<mmoncure@gmail.com> wrote: > For random writes, raid 5 has to write a minimum of two drives, the > data being written and parity. Raid 10 also has to write two drives > minimum. A lot of people think parity is a big deal in terms of raid > 5 performance penalty, but I don't -- relative to the what's going on > in the drive, xor calculation costs (one of the fastest operations in > computing) are basically zero, and off-lined if you have a hardware > raid controller. The cost is that in order to calculate the parity block the RAID controller has to *read* in either the old data block being overwritten and the old parity block or all the other data blocks which participate in that parity block. So every random write becomes not just two writes but two reads + two writes. If you're always writing large sequential hunks at a time then this is minimized because the RAID controller can just calculate the new parity block for the whole new hunk. But if you often just seek to random places in the file and overwrite 8k at a time then things go very very poorly. -- greg http://mit.edu/~gsstark/resume.pdf
-
Re: What exactly is postgres doing during INSERT/UPDATE ?
Mark Mielke <mark@mark.mielke.cc> — 2009-08-30T17:36:19Z
On 08/30/2009 11:40 AM, Merlin Moncure wrote: > For random writes, raid 5 has to write a minimum of two drives, the > data being written and parity. Raid 10 also has to write two drives > minimum. A lot of people think parity is a big deal in terms of raid > 5 performance penalty, but I don't -- relative to the what's going on > in the drive, xor calculation costs (one of the fastest operations in > computing) are basically zero, and off-lined if you have a hardware > raid controller. > > I bet part of the problem with raid 5 is actually contention. since > your write to a stripe can conflict with other writes to a different > stripe. The other problem with raid 5 that I see is that you don't > get very much extra protection -- it's pretty scary doing a rebuild > even with a hot spare (and then you should probably be doing raid 6). > On read performance RAID 10 wins all day long because more drives can > be involved. > In real life, with real life writes (i.e. not sequential from the start of the disk to the end of the disk), where the stripes on the disk being written are not already in RAM (to allow for XOR to be cheap), RAID 5 is horrible. I still recall naively playing with software RAID 5 on a three disk system and finding write performance to be 20% - 50% less than a single drive on its own. People need to realize that the cost of maintaining parity is not the XOR itself - XOR is cheap - the cost is having knowledge of all drives in the stripe in order to write the parity. This implies it is already in cache (requires a very large cache, or a very localized load such that the load all fits in cache), or it requires 1 or more reads before 2 or more writes. Latency is a killer here - latency is already the slowest part of the disk, so to effectively multiply latency x 2 has a huge impact. I will never use RAID 5 again unless I have a huge memory backed cache for it to cache writes against. By huge, I mean something approximately the size of the data normally read and written. Having 1 Gbytes of RAM dedicated to RAID 5 for a 1 Tbyte drive may not be enough. RAID 1+0 on the other hand, has never disappointed me yet. Disks are cheap, and paying x2 for single disk redundancy is an acceptable price. Cheers, mark -- Mark Mielke<mark@mielke.cc>
-
Re: What exactly is postgres doing during INSERT/UPDATE ?
Joseph Shraibman <jks@selectacast.net> — 2009-08-30T20:01:49Z
I've already learned my lesson and will never use raid 5 again. The question is what I do with my 14 drives. Should I use only 1 pair for indexes or should I use 4 drives? The wal logs are already slated for an SSD. Scott Marlowe wrote: > On Sat, Aug 29, 2009 at 2:46 AM, Greg Stark<gsstark@mit.edu> wrote: >> On Sat, Aug 29, 2009 at 5:20 AM, Luke Koops<luke.koops@entrust.com> wrote: >>> Joseph S Wrote >>>> If I have 14 drives in a RAID 10 to split between data tables >>>> and indexes what would be the best way to allocate the drives >>>> for performance? >>> RAID-5 can be much faster than RAID-10 for random reads and writes. It is much slower than RAID-10 for sequential writes, but about the same for sequential reads. For typical access patterns, I would put the data and indexes on RAID-5 unless you expect there to be lots of sequential scans. >> That's pretty much exactly backwards. RAID-5 will at best slightly >> slower than RAID-0 or RAID-10 for sequential reads or random reads. >> For sequential writes it performs *terribly*, especially for random >> writes. The only write pattern where it performs ok sometimes is >> sequential writes of large chunks. > > Note that while RAID-10 is theoretically always better than RAID-5, > I've run into quite a few cheapie controllers that were heavily > optimised for RAID-5 and de-optimised for RAID-10. However, if it's > got battery backed cache and can run in JBOD mode, linux software > RAID-10 or hybrid RAID-1 in hardware RAID-0 in software will almost > always beat hardware RAID-5 on the same controller. >
-
Re: What exactly is postgres doing during INSERT/UPDATE ?
Merlin Moncure <mmoncure@gmail.com> — 2009-08-30T22:56:08Z
On Sun, Aug 30, 2009 at 1:36 PM, Mark Mielke<mark@mark.mielke.cc> wrote: > On 08/30/2009 11:40 AM, Merlin Moncure wrote: >> >> For random writes, raid 5 has to write a minimum of two drives, the >> data being written and parity. Raid 10 also has to write two drives >> minimum. A lot of people think parity is a big deal in terms of raid >> 5 performance penalty, but I don't -- relative to the what's going on >> in the drive, xor calculation costs (one of the fastest operations in >> computing) are basically zero, and off-lined if you have a hardware >> raid controller. >> >> I bet part of the problem with raid 5 is actually contention. since >> your write to a stripe can conflict with other writes to a different >> stripe. The other problem with raid 5 that I see is that you don't >> get very much extra protection -- it's pretty scary doing a rebuild >> even with a hot spare (and then you should probably be doing raid 6). >> On read performance RAID 10 wins all day long because more drives can >> be involved. >> > > In real life, with real life writes (i.e. not sequential from the start of > the disk to the end of the disk), where the stripes on the disk being > written are not already in RAM (to allow for XOR to be cheap), RAID 5 is > horrible. I still recall naively playing with software RAID 5 on a three > disk system and finding write performance to be 20% - 50% less than a single > drive on its own. > > People need to realize that the cost of maintaining parity is not the XOR > itself - XOR is cheap - the cost is having knowledge of all drives in the > stripe in order to write the parity. This implies it is already in cache > (requires a very large cache, or a very localized load such that the load > all fits in cache), or it requires 1 or more reads before 2 or more writes. > Latency is a killer here - latency is already the slowest part of the disk, > so to effectively multiply latency x 2 has a huge impact. This is not necessarily correct. As long as the data you are writing is less than the raid stripe size (say 64kb), then you only need the old data for that stripe (which is stored on one disk only), the parity (also stored on one disk only), and the data being written to recalculate the parity. A raid stripe is usually on one disk. So a raid 5 random write will only involve two drives if it's less than stripe size (and three drives if it's up to 2x stripe size, etc). IOW, if your stripe size is 64k: 64k written: raid 10: two writes raid 5: two writes, one read (but the read and one of the writes is same physical location) 128k written raid 10: four writes raid 5: three writes, one read (but the read and one of the writes is same physical location) 192k written raid 10: six writes raid 5: four writes, one read (but the read and one of the writes is same physical location) now, by 'same physical' location, that may mean that the drive head has to move if the data is not in cache. I realize that many raid 5 implementations tend to suck. That said, raid 5 should offer higher theoretical performance for writing than raid 10, both for sequential and random. (many, many online descriptions of raid get this wrong and stupidly blame the overhead of parity calculation). raid 10 wins on read all day long. Of course, on a typical system with lots of things going on, it gets a lot more complicated... (just for the record, I use raid 10 on my databases always) :-) merlin
-
Re: What exactly is postgres doing during INSERT/UPDATE ?
Greg Stark <gsstark@mit.edu> — 2009-08-30T23:38:33Z
On Sun, Aug 30, 2009 at 11:56 PM, Merlin Moncure<mmoncure@gmail.com> wrote: > 192k written > raid 10: six writes > raid 5: four writes, one read (but the read and one of the writes is > same physical location) > > now, by 'same physical' location, that may mean that the drive head > has to move if the data is not in cache. > > I realize that many raid 5 implementations tend to suck. That said, > raid 5 should offer higher theoretical performance for writing than > raid 10, both for sequential and random. In the above there are two problems. 1) 192kB is not a random access pattern. Any time you're writing a whole raid stripe or more then RAID5 can start performing reasonably but that's not random, that's sequential i/o. The relevant random i/o pattern is writing 8kB chunks at random offsets into a multi-terabyte storage which doesn't fit in cache. 2) It's not clear but I think you're saying "but the read and one of the writes is same physical location" on the basis that this mitigates the costs. In fact it's the worst case. It means after doing the read and calculating the parity block the drive must then spin a full rotation before being able to write it back out. So instead of an average latency of 1/2 of a rotation you have that plus a full rotation, or 3x as much latency before the write can be performed as without raid5. It's not a fault of the implementations, it's a fundamental problem with RAId5. Even a spectacular implementation of RAID5 will be awful for random access writes. The only saving grace some hardware implementations have is having huge amounts of battery backed cache which mean that they can usually buffer all the writes for long enough that the access patterns no longer look random. If you buffer enough then you can hope you'll eventually overwrite the whole stripe and can write out the new parity without reading the old data. Or failing that you can perform the reads of the old data when it's convenient because you're reading nearby data effectively turning it into sequential i/o. -- greg http://mit.edu/~gsstark/resume.pdf
-
Re: What exactly is postgres doing during INSERT/UPDATE ?
Merlin Moncure <mmoncure@gmail.com> — 2009-08-31T14:38:25Z
On Sun, Aug 30, 2009 at 7:38 PM, Greg Stark<gsstark@mit.edu> wrote: > On Sun, Aug 30, 2009 at 11:56 PM, Merlin Moncure<mmoncure@gmail.com> wrote: >> 192k written >> raid 10: six writes >> raid 5: four writes, one read (but the read and one of the writes is >> same physical location) >> >> now, by 'same physical' location, that may mean that the drive head >> has to move if the data is not in cache. >> >> I realize that many raid 5 implementations tend to suck. That said, >> raid 5 should offer higher theoretical performance for writing than >> raid 10, both for sequential and random. > > In the above there are two problems. > > 1) 192kB is not a random access pattern. Any time you're writing a > whole raid stripe or more then RAID5 can start performing reasonably > but that's not random, that's sequential i/o. The relevant random i/o > pattern is writing 8kB chunks at random offsets into a multi-terabyte > storage which doesn't fit in cache. > > 2) It's not clear but I think you're saying "but the read and one of > the writes is same physical location" on the basis that this mitigates > the costs. In fact it's the worst case. It means after doing the read > and calculating the parity block the drive must then spin a full > rotation before being able to write it back out. So instead of an > average latency of 1/2 of a rotation you have that plus a full > rotation, or 3x as much latency before the write can be performed as > without raid5. > > It's not a fault of the implementations, it's a fundamental problem > with RAId5. Even a spectacular implementation of RAID5 will be awful > for random access writes. The only saving grace some hardware > implementations have is having huge amounts of battery backed cache > which mean that they can usually buffer all the writes for long enough > that the access patterns no longer look random. If you buffer enough > then you can hope you'll eventually overwrite the whole stripe and can > write out the new parity without reading the old data. Or failing that > you can perform the reads of the old data when it's convenient because > you're reading nearby data effectively turning it into sequential i/o. I agree, that's good analysis. The main point I was making was that if you have say a 10 disk raid 5, you don't involve 10 disks, only two...a very common misconception. I made another mistake that you didn't catch: you need to read *both* the data drive and the parity drive before writing, not just the parity drive. I wonder if flash SSD are a better fit for raid 5 since the reads are much cheaper than writes and there is no rotational latency. (also, $/gb is different, and so are the failure cases). merlin
-
Re: What exactly is postgres doing during INSERT/UPDATE ?
Aidan Van Dyk <aidan@highrise.ca> — 2009-08-31T14:48:09Z
* Merlin Moncure <mmoncure@gmail.com> [090831 10:38]: > I agree, that's good analysis. The main point I was making was that > if you have say a 10 disk raid 5, you don't involve 10 disks, only > two...a very common misconception. I made another mistake that you > didn't catch: you need to read *both* the data drive and the parity > drive before writing, not just the parity drive. > > I wonder if flash SSD are a better fit for raid 5 since the reads are > much cheaper than writes and there is no rotational latency. (also, > $/gb is different, and so are the failure cases). The other thing that scares me about raid-5 is the write-hole, and the possible delayed inconsistency that brings... Again, hopefully mitigated by a dependable controller w/ BBU... -- Aidan Van Dyk Create like a god, aidan@highrise.ca command like a king, http://www.highrise.ca/ work like a slave.
-
Re: What exactly is postgres doing during INSERT/UPDATE ?
Jeff Janes <jeff.janes@gmail.com> — 2009-08-31T15:24:01Z
On Sun, Aug 30, 2009 at 1:01 PM, Joseph S <jks@selectacast.net> wrote: > I've already learned my lesson and will never use raid 5 again. The > question is what I do with my 14 drives. Should I use only 1 pair for > indexes or should I use 4 drives? The wal logs are already slated for an > SSD. > Why not just spread all your index data over 14 spindles, and do the same with your table data? I haven't encountered this debate in in the pgsql world, but from the Oracle world it seems to me the "Stripe And Mirror Everything" people had the better argument than the "separate tables and indexes" people. Jeff
-
Re: What exactly is postgres doing during INSERT/UPDATE ?
Kevin Grittner <kevin.grittner@wicourts.gov> — 2009-08-31T16:31:13Z
Jeff Janes <jeff.janes@gmail.com> wrote: > Joseph S <jks@selectacast.net> wrote: >> The question is what I do with my 14 drives. Should I use only 1 >> pair for indexes or should I use 4 drives? The wal logs are >> already slated for an SSD. > Why not just spread all your index data over 14 spindles, and do the > same with your table data? If you have the luxury of being able to test more than one configuration with something resembling your actual workload, I would strongly recommend including this as one of your configurations. Spreading everything over the larger number of spindles might well out-perform your most carefully hand-crafted tuning of object placement on smaller spindle sets. -Kevin
-
Re: What exactly is postgres doing during INSERT/UPDATE ?
Scott Marlowe <scott.marlowe@gmail.com> — 2009-08-31T19:15:56Z
On Mon, Aug 31, 2009 at 10:31 AM, Kevin Grittner<Kevin.Grittner@wicourts.gov> wrote: > Jeff Janes <jeff.janes@gmail.com> wrote: >> Joseph S <jks@selectacast.net> wrote: > >>> The question is what I do with my 14 drives. Should I use only 1 >>> pair for indexes or should I use 4 drives? The wal logs are >>> already slated for an SSD. > >> Why not just spread all your index data over 14 spindles, and do the >> same with your table data? > > If you have the luxury of being able to test more than one > configuration with something resembling your actual workload, I would > strongly recommend including this as one of your configurations. > Spreading everything over the larger number of spindles might well > out-perform your most carefully hand-crafted tuning of object > placement on smaller spindle sets. The first thing I'd test would be if having a separate mirror set for pg_xlog helps. If you have a high write environment moving pg_xlog off of the main data set can help a lot.