Re: [PERFORM] Two hard drives --- what to do with them?
On Tue, Feb 27, 2007 at 15:35:13 +1030, Shane Ambler [EMAIL PROTECTED] wrote: From all that I have heard this is another advantage of SCSI disks - they honor these settings as you would expect - many IDE/SATA disks often say sure I'll disable the cache but continue to use it or don't retain the setting after restart. It is easy enough to tests if your disk lie about disabling the cache. I doubt that it is all that common for modern disks to do that. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Two hard drives --- what to do with them?
On Wed, Feb 28, 2007 at 05:21:41 +1030, Shane Ambler [EMAIL PROTECTED] wrote: The difference between SCSI and IDE/SATA in this case is a lot if not all IDE/SATA drives tell you that the cache is disabled when you ask it to but they either don't actually disable it or they don't retain the setting so you get caught later. SCSI disks can be trusted when you set this option. I have some Western Digital Caviars and they don't lie about disabling write caching. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Two hard drives --- what to do with them?
On 2/27/07, Shane Ambler [EMAIL PROTECTED] wrote: Jeff Davis wrote: Sorry for for not being familar with storage techonologies... Does battery here mean battery in the common sense of the word - some kind of independent power supply? Shouldn't the disk itself be backed by a battery? As should the entire storage subsystem? Yes, a battery that can hold power to keep data alive in the write cache in case of power failure, etc., for a long enough time to recover and commit the data to disk. Just to expand a bit - the battery backup options are available on some raid cards - that is where you would be looking for it. I don't know of any hard drives that have it built in. Of cause another reason to have a UPS for the server - keep it running long enough after the clients have gone down so that it can ensure everything is on disk and shuts down properly. So, a write cache is OK (even for pg_xlog) if it is durable (i.e. on permanent storage or backed by enough power to make sure it gets there). However, if PostgreSQL has no way to know whether a write is durable or not, it can't guarantee the data is safe. The reason this becomes an issue is that many consumer-grade disks have write cache enabled by default and no way to make sure the cached data actually gets written. So, essentially, these disks lie and say they wrote the data, when in reality, it's in volatile memory. It's recommended that you disable write cache on such a device. From all that I have heard this is another advantage of SCSI disks - they honor these settings as you would expect - many IDE/SATA disks often say sure I'll disable the cache but continue to use it or don't retain the setting after restart. As far as I know, SCSI drives also have write cache which is turned off by default, but can be turned on (e.g. with the sdparm utility on Linux). The reason I am so much interested in how write cache is typically used (on or off) is that I recently ran our benchmarks on a machine with SCSI disks and those benchmarks with high commit ratio suffered significantly compared to our previous results traditionally obtained on machines with IDE drives. I wonder if running a machine on a UPS + 1 hot standby internal PS is equivalent, in terms of data integrity, to using battery backed write cache. Instinctively, I'd think that UPS + 1 hot standby internal PS is better, since this setup also provides for the disk to actually write out the content of the cache -- as you pointed out. Thanks Peter -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Two hard drives --- what to do with them?
Just remember that batteries (in both RAID cards and UPSes) wear out and will eventually have to be replaced. It depends how critical your data is, but if you only have a UPS, you risk badness in the off chance that your power fails and you haven't replaced your UPS battery. On Feb 27, 2007, at 12:27 AM, Peter Kovacs wrote: I wonder if running a machine on a UPS + 1 hot standby internal PS is equivalent, in terms of data integrity, to using battery backed write cache. Instinctively, I'd think that UPS + 1 hot standby internal PS is better, since this setup also provides for the disk to actually write out the content of the cache -- as you pointed out.
Re: [PERFORM] Two hard drives --- what to do with them?
Peter Kovacs wrote: The reason this becomes an issue is that many consumer-grade disks have write cache enabled by default and no way to make sure the cached data actually gets written. So, essentially, these disks lie and say they wrote the data, when in reality, it's in volatile memory. It's recommended that you disable write cache on such a device. From all that I have heard this is another advantage of SCSI disks - they honor these settings as you would expect - many IDE/SATA disks often say sure I'll disable the cache but continue to use it or don't retain the setting after restart. As far as I know, SCSI drives also have write cache which is turned off by default, but can be turned on (e.g. with the sdparm utility on Linux). The reason I am so much interested in how write cache is typically used (on or off) is that I recently ran our benchmarks on a machine with SCSI disks and those benchmarks with high commit ratio suffered significantly compared to our previous results traditionally obtained on machines with IDE drives. Most likely - with write cache, when the drive gets the data it puts it into cache and then says yep all done and you continue on as it puts it on the disk. But if the power goes out as it's doing that you got trouble. The difference between SCSI and IDE/SATA in this case is a lot if not all IDE/SATA drives tell you that the cache is disabled when you ask it to but they either don't actually disable it or they don't retain the setting so you get caught later. SCSI disks can be trusted when you set this option. I wonder if running a machine on a UPS + 1 hot standby internal PS is equivalent, in terms of data integrity, to using battery backed write cache. Instinctively, I'd think that UPS + 1 hot standby internal PS is better, since this setup also provides for the disk to actually write out the content of the cache -- as you pointed out. This is covering two different scenarios. The UPS maintains power in the event of a black out. The hot standby internal PS maintains power when the first PS dies. It is a good choice to have both as a PS dying will be just as bad as losing power without a UPS and the UPS won't save you if the PS goes. A battery backed raid card sits in between these - as long as the drive's write cache is off - the raid card will hold data that was sent to disk until it confirms it is written to disk. The battery backup will even hold that data until the machine is switched back on when it completes the writing to disk. That would cover you even if the PS goes. -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Two hard drives --- what to do with them?
On Tue, 2007-02-27 at 13:23, Jeff Davis wrote: Also, put things in context. The chances of failure due to these kinds of things are fairly low. If it's more likely that someone spills coffee on your server than the UPS fails, it doesn't make sense to spend huge amounts of money on NVRAM (or something) to store your data. So identify the highest-risk scenarios and prevent those first. Also keep in mind what the cost of failure is: a few hundred bucks more on a better RAID controller is probably a good value if it prevents a day of chaos and unhappy customers. Just FYI, I can testify to the happiness a good battery backed caching RAID controller can bring. I had the only server that survived a complete power grid failure in the data center where I used to work. A piece of wire blew out a power conditioner, which killed the other power conditioner, all three UPSes and the switch to bring the diesel generator online. the only problem the pgsql server had coming back up was that it had remote nfs mounts it used for file storage that weren't able to boot up fast enough so we just waited a few minutes and rebooted it. All of our other database servers had to be restored from backup due to massive data corruption because someone had decided that NFS mounts were a good idea under databases. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Two hard drives --- what to do with them?
On Sun, 2007-02-25 at 23:11 +0100, Peter Kovacs wrote: A related question: Is it sufficient to disable write cache only on the disk where pg_xlog is located? Or should write cache be disabled on both disks? When PostgreSQL does a checkpoint, it thinks the data pages before the checkpoint have successfully made it to disk. If the write cache holds those data pages, and then loses them, there's no way for PostgreSQL to recover. So use a battery backed cache or turn off the write cache. Regards, Jeff Davis ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Two hard drives --- what to do with them?
On 2/26/07, Jeff Davis [EMAIL PROTECTED] wrote: On Sun, 2007-02-25 at 23:11 +0100, Peter Kovacs wrote: A related question: Is it sufficient to disable write cache only on the disk where pg_xlog is located? Or should write cache be disabled on both disks? When PostgreSQL does a checkpoint, it thinks the data pages before the checkpoint have successfully made it to disk. If the write cache holds those data pages, and then loses them, there's no way for PostgreSQL to recover. So use a battery backed cache or turn off the write cache. Sorry for for not being familar with storage techonologies... Does battery here mean battery in the common sense of the word - some kind of independent power supply? Shouldn't the disk itself be backed by a battery? As should the entire storage subsystem? Thanks Peter Regards, Jeff Davis ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Two hard drives --- what to do with them?
On Tue, 2007-02-27 at 01:11 +0100, Peter Kovacs wrote: On 2/26/07, Jeff Davis [EMAIL PROTECTED] wrote: On Sun, 2007-02-25 at 23:11 +0100, Peter Kovacs wrote: A related question: Is it sufficient to disable write cache only on the disk where pg_xlog is located? Or should write cache be disabled on both disks? When PostgreSQL does a checkpoint, it thinks the data pages before the checkpoint have successfully made it to disk. If the write cache holds those data pages, and then loses them, there's no way for PostgreSQL to recover. So use a battery backed cache or turn off the write cache. Sorry for for not being familar with storage techonologies... Does battery here mean battery in the common sense of the word - some kind of independent power supply? Shouldn't the disk itself be backed by a battery? As should the entire storage subsystem? Yes, a battery that can hold power to keep data alive in the write cache in case of power failure, etc., for a long enough time to recover and commit the data to disk. So, a write cache is OK (even for pg_xlog) if it is durable (i.e. on permanent storage or backed by enough power to make sure it gets there). However, if PostgreSQL has no way to know whether a write is durable or not, it can't guarantee the data is safe. The reason this becomes an issue is that many consumer-grade disks have write cache enabled by default and no way to make sure the cached data actually gets written. So, essentially, these disks lie and say they wrote the data, when in reality, it's in volatile memory. It's recommended that you disable write cache on such a device. Regards, Jeff Davis ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Two hard drives --- what to do with them?
A related question: Is it sufficient to disable write cache only on the disk where pg_xlog is located? Or should write cache be disabled on both disks? Thanks Peter On 2/25/07, Tom Lane [EMAIL PROTECTED] wrote: Carlos Moreno [EMAIL PROTECTED] writes: The question is: does PostgreSQL have separate, independent areas that require storage such that performance would be noticeably boosted if the multiple storage operations could be done simultaneously? The standard advice in this area is to put pg_xlog on a separate spindle; although that probably is only important for update-intensive applications. You did not tell us anything about your application... regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] Two hard drives --- what to do with them?
Say that I have a dual-core processor (AMD64), with, say, 2GB of memory to run PostgreSQL 8.2.3 on Fedora Core X. I have the option to put two hard disks (SATA2, most likely); I'm wondering what would be the optimal configuration from the point of view of performance. I do have the option to configure it in RAID-0, but I'm sort of reluctant; I think there's the possibility that having two filesystems that can be accessed truly simultaneously can be more beneficial. The question is: does PostgreSQL have separate, independent areas that require storage such that performance would be noticeably boosted if the multiple storage operations could be done simultaneously? Notice that even with RAID-0, the twice the performance may turn into an illusion --- if the system requires access from distant areas of the disk (distant as in many tracks apart), then the back-and-forth travelling of the heads would take precedence over the doubled access speed ... Though maybe it depends on whether accesses are in small chunks (in which case the cache of the hard disks could take precedence). Coming back to the option of two independent disks --- the thing is: if it turns out that two independent disks are a better option, how should I configure the system and the mount points? And how would I configure PostgreSQL to take advantage of that? Advice, anyone? Thanks, Carlos -- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Two hard drives --- what to do with them?
Carlos Moreno [EMAIL PROTECTED] writes: The question is: does PostgreSQL have separate, independent areas that require storage such that performance would be noticeably boosted if the multiple storage operations could be done simultaneously? The standard advice in this area is to put pg_xlog on a separate spindle; although that probably is only important for update-intensive applications. You did not tell us anything about your application... regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Two hard drives --- what to do with them?
On Feb 25, 2007, at 04:39 , Carlos Moreno wrote: I do have the option to configure it in RAID-0, but I'm sort of reluctant; I think there's the possibility that having two filesystems that can be accessed truly simultaneously can be more beneficial. The question is: does PostgreSQL have separate, independent areas that require storage such that performance would be noticeably boosted if the multiple storage operations could be done simultaneously? Putting the WAL (aka pg_xlog) on a separate disk will take some load off your main database disk. See http://www.varlena.com/GeneralBits/ Tidbits/perf.html for this. It is also possible to put individual tables and/or indexes on separate disks by using tablespaces: For example, an index which is very heavily used can be placed on a very fast, highly available disk, such as an expensive solid state device. At the same time a table storing archived data which is rarely used or not performance critical could be stored on a less expensive, slower disk system. (http://www.postgresql.org/docs/8.2/interactive/manage-ag- tablespaces.html) In both cases, the performance benefits tend to be relative to the amount of write activity you experience, and the latter solution assumes you know where the hotspots are. If you have two tables that see continuous, intense write activity, for example, putting each on a separate disk Alexander. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
