Thread: Possible causes for database corruption and solutions
Hello all,
Over the past 6 months or so I have posted to the list a couple times looking for information regarding recovering databases from corruption. At the time the incidents of corruption among our users was very low, but the frequency is starting to increase, most likely due to the increased user base that has upgraded to the version of our software that uses Postgres.
I have a couple questions I am hoping to get some feedback on.
The first, likely causes of the corruption. In response to my emails I mentioned above, I was told that generally these sort of corruptions are caused by faulty hardware/hard drives of some sort. Which seems reasonable. When we started to see the number of corruptions per week on the rise we did some more digging and came across some information that points directly to hard drives, specifically their write buffers.
(Specifically this page: http://www.mail-archive.com/sqlite-users@sqlite.org/msg06502.html)
The solution to the problem seemed to be to change the value for the wal_sync_method setting to fsync_writethrough from the default of fsync.
I was curious if there were perhaps any other reasons that we should look at? Or if there may be other alternatives to changing the wal_sync_method setting.
I should note, our product runs on OS X, and I would say about 95% of the corruptions happen in a bytea column in a given table which tends to hold largish data (like email bodies which may or may not have embedded attachments).
Secondly, I ask about an alternative solution to the corruption problem because with preliminary testing we have seen a significant degradation in performance. So far the two operations we have noted are database creation and database restores. We haven't tried some other things that will likely suffer, such as bulk imports into the database. But to give an idea, I am using a 590K sized dump created with the command:
pg_dump -F c DB > ./backup
I then restore the dump into a newly created DB (createdb -E UTF8 Test1) with the command:
pg_restore -F c -d Test1 pgbackup
With wal_sync_method set to fsync it takes 2 seconds.
With wal_sync_method set to fsync_writethrough it takes 3 minutes and 51 seconds.
Similarly, the createdb command:
With fsync it takes .3 seconds.
With fsync_writethrough it takes 13 seconds.
This is quite worrying from a performance point of view, especially given that many databases out there are in the gigabytes. (Our own database is 5.6GB)
I should note here that we have not tuned PG at all.
The only things we have changed are the max connections (set to 200), shared_buffers was adjusted to 4800kB and max_fsm_pages is set to 20000.
I guess that is pretty laughable, but even with these settings and the rest at default Postgres was performing better than our previous database engine, and so tuning dropped down the priority list quite a bit.
I know this is a bit long winded, but I hope that someone can shed some light for us.
I am hoping there may be an alternative to fsync_writethrough and/or we are barking up the wrong tree w.r.t. the cause of the corruptions.
Thanks very much in advance,
Michael.
On Tue, Dec 15, 2009 at 3:39 PM, Michael Clark <codingninja@gmail.com> wrote: > Hello all, > Over the past 6 months or so I have posted to the list a couple times > looking for information regarding recovering databases from corruption. At > the time the incidents of corruption among our users was very low, but the > frequency is starting to increase, most likely due to the increased user > base that has upgraded to the version of our software that uses Postgres. > I have a couple questions I am hoping to get some feedback on. > The first, likely causes of the corruption. In response to my emails I > mentioned above, I was told that generally these sort of corruptions are > caused by faulty hardware/hard drives of some sort. Which seems reasonable. > When we started to see the number of corruptions per week on the rise we > did some more digging and came across some information that points directly > to hard drives, specifically their write buffers. > (Specifically this page: > http://www.mail-archive.com/sqlite-users@sqlite.org/msg06502.html) > The solution to the problem seemed to be to change the value for the > wal_sync_method setting to fsync_writethrough from the default of fsync. > I was curious if there were perhaps any other reasons that we should look > at? Or if there may be other alternatives to changing the wal_sync_method > setting. > I should note, our product runs on OS X, and I would say about 95% of the > corruptions happen in a bytea column in a given table which tends to hold > largish data (like email bodies which may or may not have embedded > attachments). OK, remember that a lot of us on this list run medium to large databases with no problems with corruption. My recipe for corruption free pgsql is: HARDWARE: Server grade SAS drives (15K Seagate, Hitachi, Samsung, WD etc) on a quality HW RAID controller (3Ware, Areca) tested for at least two weeks of memtest86 and two weeks of pgbench running full throttle. OS: Centos / RHEL 5.2 or FreeBSD 6 (haven't useed 7 but heard good things) PG version 8.3.latest I've had a few problems that I can't yet track down in pg 8.4 so we're not migrating to it until we find those problems and fix them. They're backend crashes, sig 11 btw. The reason 95% of your corruption like occurs in bytea is that they are likely the biggest columns (95% of the storage size) so it's likely that other columns could induce corruption as well. While hard drives are often the cause of corruption, bad memory / cpu / mobo / RAID controller etc can all cause these problems as well. I'd recommend very thorough and intense acceptance testing where you are trying to break the computer so to speak. Pulling power plugs while pgbench is running, things like that. Running on two of three redundant power supplies. I'd also recommend moving off of OSX as you're using a minority OS as far as databases are concerned, and you won't have a very large community to help out when things do go wrong. Apple's focus is and has been on user oriented OS experiences, not databases. While the linux kernel is maintained by someone who has made it clear what he's interested in is the single user experience, it is built into an OS by several companies who take running servers very seriously (RH, Debian, Ubuntu to a lesser extent).
On 16/12/2009 6:39 AM, Michael Clark wrote: > Hello all, > > Over the past 6 months or so I have posted to the list a couple times > looking for information regarding recovering databases from corruption. > At the time the incidents of corruption among our users was very low, > but the frequency is starting to increase, most likely due to the > increased user base that has upgraded to the version of our software > that uses Postgres. > > I have a couple questions I am hoping to get some feedback on. > Secondly, I ask about an alternative solution to the corruption problem > because with preliminary testing we have seen a significant degradation > in performance. From changing to fsync_writethrough ? That's a good thing - it suggests that maybe now the data is actually hitting disk when Pg asks it to. You can have fast(er), or safe, but not both. Now that your database is actually doing what it should be and truthfully promising that data has hit disk when you commit, you may have to adopt some strategies to reduce the number of very short repetitive transactions you perform. ( Should Pg perhaps detect OS X and switch the default to fsync_writethrough ? Or are the "test to see if fsync() works on startup" plans going anywhere? ) > I then restore the dump into a newly created DB (createdb -E UTF8 Test1) > with the command: > pg_restore -F c -d Test1 pgbackup Make sure to do the restore in a single transaction. It will be *enormously*, *massively* faster. See the "--single-transaction" option to pg_restore. If you can't do that, then set a commit_delay so that PostgreSQL can batch the fsync()s for commmits together. See postgresql.conf and the documentation for commit_delay and commit_siblings. > I am hoping there may be an alternative to fsync_writethrough and/or we > are barking up the wrong tree w.r.t. the cause of the corruptions. If fsync_writethrough slowed things down that much then you've almost certainly nailed the cause of the corruptions. Now you just need to tune your DB, and adapt how you use the DB, so that you're less affected by the necessary performance hit imposed by safe and reliable use of disk storage. -- Craig Ringer
On 16/12/2009 9:07 AM, Scott Marlowe wrote: > I'd also recommend moving off of OSX as you're using a minority OS as > far as databases are concerned, and you won't have a very large > community to help out when things do go wrong. It sounds like PostgreSQL is being used as a DB bundled with an app - not quite embedded, but as close as Pg gets. Right, OP? If so, they wouldn't be moving off Mac OS X, they'd be moving off Pg. While Pg doesn't seem to be hugely used on Mac OS X as a production environment for running dedicated database servers, it should still work safely and with acceptable performance. If it doesn't then good problem reports will help improve that. So I for one encourage them to stick with Pg and stay in touch on the list. They shouldn't have issues now that they've got OS X honouring fsync, and if they do then it'd be good to hear about it. I'll be happy to help out if I can - I don't use Pg on OS X, but I do have access to OS X machines and have to administrate them on the network at work, so I can do testing if I need to. Just because Pg isn't targeted at app embedding doesn't mean it shouldn't work well on a mostly end-user platform when shipped with an application. If the app installer is prepared to put up with the fuss of setting up Pg on the machine, it should be able to (and can) reasonably expect it to work. For what its worth, there are clearly a fair few Mac OS X users of Pg out there - especially dev setups on Mac laptops. They turn up on the lists sometimes, and the things they ask about don't suggest to me that Mac OS X is a particularly untrustworthy platform for running Pg on. -- Craig Ringer
Michael Clark wrote: > The solution to the problem seemed to be to change the value for the > wal_sync_method setting to fsync_writethrough from the default of fsync. I was surprised recently to discover the default wasn't fsync_writethrough on that platform, because it probably should be. There is no other safe mode to run PostgreSQL in OS X with. If you don't invoke the write-through cache flushing code, you can expect databases to get regularly corrupted if people do things like lose power in the middle of writing something, exactly as you're seeing. > Secondly, I ask about an alternative solution to the corruption > problem because with preliminary testing we have seen a significant > degradation in performance. So far the two operations we have noted > are database creation and database restores. For the restore case, you might get a good sized boost in performance without introducing a risk of corruption by turning off the synchronous_commit parameter. That will put you in a position where you can have a committed transaction not actually be on disk if there's a crash or sudden power outage, but you won't get an actual corruption in that case. So fsync_writethough plus synchronous_commit=off should be no less safe than what you've got now, but probably not as fast as what you're used to. As already pointed out, there is a trade-off here you can't bargain with: you can either have your data completely safe, or you can execute quickly, but you can't do both. Robust data integrity slows things down and there's little you can do about it without buying hardware targeted to improve on that. The database creation issue just came up on one of the lists here the other day as being particularly slow in the situation you're in, and that parameter change doesn't help there. There's been some design change suggestions around that to improve the situation, but you're not likely to see those in the server code for a year or more. > I should note here that we have not tuned PG at all. You could probably see a good sized performance increase just from increasing checkpoint_segments a bit from its default (3). Since it sounds like you're trying to keep your product's disk space footprint under control, increasing that to around 10 would probably as high as you want to go. You can't really increase shared_buffers a lot on your platform lest your users get stuck with weird problems where the server won't start, from what I hear OS X is fairly hostile to the kernel adjustments you need to do in order to support that. There's a general intro to things you might tune in the postgresql.conf at http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server None of those are going to help you out with slow database creation, you might be able to pull down the restore times by tweaking some of the parameters there upwards. A large number of the tunables recommend to tweak there mainly impact query execution time. -- Greg Smith 2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.com
Craig Ringer wrote: > On 16/12/2009 9:07 AM, Scott Marlowe wrote: >> I'd also recommend moving off of OSX as you're using a minority OS as >> far as databases are concerned, and you won't have a very large >> community to help out when things do go wrong > > It sounds like PostgreSQL is being used as a DB bundled with an app - > not quite embedded, but as close as Pg gets. ... > For what its worth, there are clearly a fair few Mac OS X users of Pg > out there...the things they ask about don't suggest to me that > Mac OS X is a particularly untrustworthy platform for running Pg on. Not to mention that Apple seems to use postgres bundled in some of their high-end software: http://support.apple.com/kb/TS1648 http://www.apple.com/finalcutserver/ http://www.devworld.apple.com/appleapplications/ardsql.html If postgres had any significant trouble running on OSX, I'm confidant Apple would love to hear about it and do something about it very quickly.
On Tue, Dec 15, 2009 at 7:45 PM, Craig Ringer <craig@postnewspapers.com.au> wrote: > On 16/12/2009 9:07 AM, Scott Marlowe wrote: > >> I'd also recommend moving off of OSX as you're using a minority OS as >> far as databases are concerned, and you won't have a very large >> community to help out when things do go wrong. > > It sounds like PostgreSQL is being used as a DB bundled with an app - not > quite embedded, but as close as Pg gets. Right, OP? > > If so, they wouldn't be moving off Mac OS X, they'd be moving off Pg. > > While Pg doesn't seem to be hugely used on Mac OS X as a production > environment for running dedicated database servers, it should still work > safely and with acceptable performance. If it doesn't then good problem > reports will help improve that. So I for one encourage them to stick with Pg > and stay in touch on the list. They shouldn't have issues now that they've > got OS X honouring fsync, and if they do then it'd be good to hear about it. Oh, I completely missed that they didn't have fsync working right. Yeah, I agree then, embedded on OSX, fsync working, that's reasonable.
* Michael Clark: > The solution to the problem seemed to be to change the value for the > wal_sync_method setting to fsync_writethrough from the default of fsync. > I was curious if there were perhaps any other reasons that we should look > at? Or if there may be other alternatives to changing the wal_sync_method > setting. Fsync and related settings only matter if the operating system (not just the database) crashes. Does this happen frequently for you? > I should note, our product runs on OS X, and I would say about 95% of the > corruptions happen in a bytea column in a given table which tends to hold > largish data (like email bodies which may or may not have embedded > attachments). That's not surprising if 95% of the data are stored that way. > With wal_sync_method set to fsync it takes 2 seconds. > With wal_sync_method set to fsync_writethrough it takes 3 minutes and 51 > seconds. fsync_writethrough seems to be global in effect (not file specific), so it's going to hurt if there is other I/O activity on the box. -- Florian Weimer <fweimer@bfk.de> BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99
On 16/12/2009 3:54 PM, Florian Weimer wrote: > * Michael Clark: > >> The solution to the problem seemed to be to change the value for the >> wal_sync_method setting to fsync_writethrough from the default of fsync. >> I was curious if there were perhaps any other reasons that we should look >> at? Or if there may be other alternatives to changing the wal_sync_method >> setting. > > Fsync and related settings only matter if the operating system (not > just the database) crashes. Does this happen frequently for you? When you're dealing with end users who have machines running god-knows-what kinds of awful hardware drivers and with no power protection, then I expect it does. Add laptop users with ageing/flakey batteries, laptops let go flat after they go into powersave suspend, etc, and you're sure to see plenty of cases of apparent crashes. -- Craig Ringer
* Craig Ringer: > On 16/12/2009 3:54 PM, Florian Weimer wrote: >> * Michael Clark: >> >>> The solution to the problem seemed to be to change the value for the >>> wal_sync_method setting to fsync_writethrough from the default of fsync. >>> I was curious if there were perhaps any other reasons that we should look >>> at? Or if there may be other alternatives to changing the wal_sync_method >>> setting. >> >> Fsync and related settings only matter if the operating system (not >> just the database) crashes. Does this happen frequently for you? > > When you're dealing with end users who have machines running > god-knows-what kinds of awful hardware drivers Even Mac OS X? There should be less variety. > and with no power protection, then I expect it does. Add laptop > users with ageing/flakey batteries, laptops let go flat after they > go into powersave suspend, etc, and you're sure to see plenty of > cases of apparent crashes. I hope that Mac OS X turns off write caches on low battery. Improperly disconnected external drives are quite common and the effect mimics operating system crashes, but is it common to store PostgreSQL databases there? I don't think so. -- Florian Weimer <fweimer@bfk.de> BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99
On 16/12/2009 6:41 PM, Florian Weimer wrote: > * Craig Ringer: >> When you're dealing with end users who have machines running >> god-knows-what kinds of awful hardware drivers > > Even Mac OS X? There should be less variety. Of disk and other critical drivers, sure. There is, however, a huge variety of horrible drivers for add-on devices from TV-in cards to NERF missile launchers. The one thing common among these drivers is: bugs. Driver bugs can bring down an OS X machine with a pretty kernel panic, just like most other systems, and to Pg there's no difference between a kernel panic caused by the TV-in card and a power failure or crash in core OS components. -- Craig Ringer
Hello Craig - thanks for the reply. I will reply below.
On Tue, Dec 15, 2009 at 9:34 PM, Craig Ringer <craig@postnewspapers.com.au> wrote:
On 16/12/2009 6:39 AM, Michael Clark wrote:Hello all,
Over the past 6 months or so I have posted to the list a couple times
looking for information regarding recovering databases from corruption.
At the time the incidents of corruption among our users was very low,
but the frequency is starting to increase, most likely due to the
increased user base that has upgraded to the version of our software
that uses Postgres.
I have a couple questions I am hoping to get some feedback on.From changing to fsync_writethrough ? That's a good thing - it suggests that maybe now the data is actually hitting disk when Pg asks it to.Secondly, I ask about an alternative solution to the corruption problem
because with preliminary testing we have seen a significant degradation
in performance.
You can have fast(er), or safe, but not both. Now that your database is actually doing what it should be and truthfully promising that data has hit disk when you commit, you may have to adopt some strategies to reduce the number of very short repetitive transactions you perform.
That is true, this does bode well for us finding our cause. And that old axiom does often ring true.
( Should Pg perhaps detect OS X and switch the default to fsync_writethrough ? Or are the "test to see if fsync() works on startup" plans going anywhere? )
Not sure if you were directing that to me specifically, I feel probably to the general PG public.
But, this is probably a lot less of a problem in a proper server environment, which most of our users are not in. (I touch more on that to in my reply to you and Scott).
For example, we run our software on a Mac Pro, with raid 1 and the machine is protected by UPS. We have never had an issue ourselves.
I guess it depends on the majority use case for PG on OS X whether this should be defaulted to on.
(Although siding with caution usually never hurts when data is concerned.)
Make sure to do the restore in a single transaction. It will be *enormously*, *massively* faster. See the "--single-transaction" option to pg_restore.I then restore the dump into a newly created DB (createdb -E UTF8 Test1)
with the command:
pg_restore -F c -d Test1 pgbackup
Thanks for that tip, cut the restore in half in this particular test.
If you can't do that, then set a commit_delay so that PostgreSQL can batch the fsync()s for commmits together. See postgresql.conf and the documentation for commit_delay and commit_siblings.
I will look those up as well, thanks.
If fsync_writethrough slowed things down that much then you've almost certainly nailed the cause of the corruptions. Now you just need to tune your DB, and adapt how you use the DB, so that you're less affected by the necessary performance hit imposed by safe and reliable use of disk storage.I am hoping there may be an alternative to fsync_writethrough and/or we
are barking up the wrong tree w.r.t. the cause of the corruptions.
It definitely feels better knowing that we likely found our issue, and you are right, time to tune as best we can.
Luckily these sort of large operations are generally the smaller percentage of use cases.
Thanks again for the reply,
Michael
--
Craig Ringer
Hi Scott and Craig,
On Tue, Dec 15, 2009 at 9:45 PM, Craig Ringer <craig@postnewspapers.com.au> wrote:
On 16/12/2009 9:07 AM, Scott Marlowe wrote:It sounds like PostgreSQL is being used as a DB bundled with an app - not quite embedded, but as close as Pg gets. Right, OP?I'd also recommend moving off of OSX as you're using a minority OS as
far as databases are concerned, and you won't have a very large
community to help out when things do go wrong.
If so, they wouldn't be moving off Mac OS X, they'd be moving off Pg.
Correct. We are using PG for the backing store of our OS X desktop application.
It has been working really well for us (minus these issues), and it is pretty much embedded. We have built an OS X framework around PG, specialized the building process to generate a proper universal binary of the PG executables/libraries and set it up to run in place from within our application files. We do not install PG to the users machine separately.
And we run it in two modes, multi user mode where we allow ip based connections, and single user mode where we restrict connections to unix file sockets (we had to patch pg_ctl to handle this special case in fact).
Loving Postgres!
While Pg doesn't seem to be hugely used on Mac OS X as a production environment for running dedicated database servers, it should still work safely and with acceptable performance. If it doesn't then good problem reports will help improve that. So I for one encourage them to stick with Pg and stay in touch on the list. They shouldn't have issues now that they've got OS X honouring fsync, and if they do then it'd be good to hear about it. I'll be happy to help out if I can - I don't use Pg on OS X, but I do have access to OS X machines and have to administrate them on the network at work, so I can do testing if I need to.
Just because Pg isn't targeted at app embedding doesn't mean it shouldn't work well on a mostly end-user platform when shipped with an application. If the app installer is prepared to put up with the fuss of setting up Pg on the machine, it should be able to (and can) reasonably expect it to work.
For what its worth, there are clearly a fair few Mac OS X users of Pg out there - especially dev setups on Mac laptops. They turn up on the lists sometimes, and the things they ask about don't suggest to me that Mac OS X is a particularly untrustworthy platform for running Pg on.
I was quite shocked to hear the concern over OS X to be honest. It is a very stable environment with a strong pedigree.
But having said that, it does provide for a hostile* environment for PG in our use case. Now with fsync set properly I think it will deal fine with the hostilities, the much larger percentage of our user base with no problems already speaks volumes.
Thanks again for the reply,
Michael.
*Hostile in that generally OS X based machines are not treated like a glass houses, with hd redundancy, UPS protection, massive up times, etc.
--
Craig Ringer
Hi Greg, thanks for the reply!
On Tue, Dec 15, 2009 at 10:52 PM, Greg Smith <greg@2ndquadrant.com> wrote:
Michael Clark wrote:For the restore case, you might get a good sized boost in performance without introducing a risk of corruption by turning off the synchronous_commit parameter. That will put you in a position where you can have a committed transaction not actually be on disk if there's a crash or sudden power outage, but you won't get an actual corruption in that case. So fsync_writethough plus synchronous_commit=off should be no less safe than what you've got now, but probably not as fast as what you're used to. As already pointed out, there is a trade-off here you can't bargain with: you can either have your data completely safe, or you can execute quickly, but you can't do both. Robust data integrity slows things down and there's little you can do about it without buying hardware targeted to improve on that.Secondly, I ask about an alternative solution to the corruption problem because with preliminary testing we have seen a significant degradation in performance. So far the two operations we have noted are database creation and database restores.
That sounds like an interesting setting, I will look into that further, thanks!
The database creation issue just came up on one of the lists here the other day as being particularly slow in the situation you're in, and that parameter change doesn't help there. There's been some design change suggestions around that to improve the situation, but you're not likely to see those in the server code for a year or more.
That is a lot less of a problem, for us anyways. Faster (and safe) is always better though. The real concern is explaining why a 2 second restore now takes almost 4 minutes!
Not that there is anything that can be done (except for some tuning), but that is not really an issue for here.
You could probably see a good sized performance increase just from increasing checkpoint_segments a bit from its default (3). Since it sounds like you're trying to keep your product's disk space footprint under control, increasing that to around 10 would probably as high as you want to go. You can't really increase shared_buffers a lot on your platform lest your users get stuck with weird problems where the server won't start, from what I hear OS X is fairly hostile to the kernel adjustments you need to do in order to support that.I should note here that we have not tuned PG at all.
There's a general intro to things you might tune in the postgresql.conf at http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
None of those are going to help you out with slow database creation, you might be able to pull down the restore times by tweaking some of the parameters there upwards. A large number of the tunables recommend to tweak there mainly impact query execution time.
Thanks for those tips as well. Much appreciated,
Michael.
On Wed, Dec 16, 2009 at 5:41 AM, Florian Weimer <fweimer@bfk.de> wrote:
* Craig Ringer:
> On 16/12/2009 3:54 PM, Florian Weimer wrote:
>> * Michael Clark:
>>> and with no power protection, then I expect it does. Add laptop> users with ageing/flakey batteries, laptops let go flat after theyI hope that Mac OS X turns off write caches on low battery.
> go into powersave suspend, etc, and you're sure to see plenty of
> cases of apparent crashes.
OS X is relatively smart at dealing with batteries. For example, on newer (last 2-3 years maybe) laptops when the battery hits a critical low level it automatically hibernates the machine (different from sleep), where the machine state is saved to disk and then shut off.
Improperly disconnected external drives are quite common and the
effect mimics operating system crashes, but is it common to store
PostgreSQL databases there? I don't think so.
For us, no, we do not allow the PG data store to be moved off the root volume (which I guess could be an external drive if booted from one).
But this article talks a bit about external drives, to paraphrase, a lot of external firewire drives disable the command that allows the fsync_writethrough from working (yikes)
Michael.
Florian Weimer wrote: > I hope that Mac OS X turns off write caches on low battery. > I've never heard of such a thing. The best you can do is try to push the system into hibernation instead of going down hard. That *should* clear any disk caches as part of the graceful shutdown. But you're relying on a relatively fragile system now, once the battery is quite low who knows if that will even execute in the window of time you have left. > Improperly disconnected external drives are quite common and the > effect mimics operating system crashes, but is it common to store > PostgreSQL databases there? I don't think so. > I hope people don't do this. External Firewire and USB drives are the worst possible place to store one's data at from a reliability point of view. They usually don't pass through SMART errors that would let you know when the drive is dying. They might not correctly honor write cache calls, because a lot of bridge chipsets are cheap garbage that support only the bare minimum of operations (see "don't pass through SMART"). And if you're using a regular desktop drive in an external enclosure, the expected lifetime before it dies is a fraction of a drive that doesn't move around all day--note how small the warranties of such items are compared to the same drive for internal use. Recently I've started using 2.5" drives aimed at laptops, now that I can get 500GB that way, with an E-SATA connector on them. That's the only even remotely reliable external drive solution nowadays, because at least you're guaranteed to get SMART data, cache flushes, and a drive technology that's always been optimized for ruggedness. -- Greg Smith 2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.com
On Wed, Dec 16, 2009 at 11:25 AM, Greg Smith <greg@2ndquadrant.com> wrote:
Florian Weimer wrote:I've never heard of such a thing. The best you can do is try to push the system into hibernation instead of going down hard. That *should* clear any disk caches as part of the graceful shutdown. But you're relying on a relatively fragile system now, once the battery is quite low who knows if that will even execute in the window of time you have left.I hope that Mac OS X turns off write caches on low battery.
And at this point it is not unreasonable to expect the user to perform some sort of action. Shutdown before the power completely dies, or plug in the power.
Michael Clark wrote:
Sure, but the point is that if your database will get corrupted if that doesn't happen, that's a mistake because sooner or later every such portable system is going to end up there and not get plugged in on time.
On Wed, Dec 16, 2009 at 11:25 AM, Greg Smith <greg@2ndquadrant.com> wrote:Florian Weimer wrote:I've never heard of such a thing. The best you can do is try to push the system into hibernation instead of going down hard. That *should* clear any disk caches as part of the graceful shutdown. But you're relying on a relatively fragile system now, once the battery is quite low who knows if that will even execute in the window of time you have left.I hope that Mac OS X turns off write caches on low battery.
And at this point it is not unreasonable to expect the user to perform some sort of action. Shutdown before the power completely dies, or plug in the power.
Sure, but the point is that if your database will get corrupted if that doesn't happen, that's a mistake because sooner or later every such portable system is going to end up there and not get plugged in on time.
-- Greg Smith 2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.com
On Wed, Dec 16, 2009 at 2:05 PM, Greg Smith <greg@2ndquadrant.com> wrote:
Sure, but the point is that if your database will get corrupted if that doesn't happen, that's a mistake because sooner or later every such portable system is going to end up there and not get plugged in on time.Michael Clark wrote:On Wed, Dec 16, 2009 at 11:25 AM, Greg Smith <greg@2ndquadrant.com> wrote:Florian Weimer wrote:I've never heard of such a thing. The best you can do is try to push the system into hibernation instead of going down hard. That *should* clear any disk caches as part of the graceful shutdown. But you're relying on a relatively fragile system now, once the battery is quite low who knows if that will even execute in the window of time you have left.I hope that Mac OS X turns off write caches on low battery.
And at this point it is not unreasonable to expect the user to perform some sort of action. Shutdown before the power completely dies, or plug in the power.
Totally agree, not having PG setup properly and expecting the user to not fall into this problem is irresponsible.
I was speaking more generally on the OS level. How aggressive should we expect an OS to be in a case like this where some responsibility should lie with the user.
Is changing the OS/X wal_sync_method default something we should consider? --------------------------------------------------------------------------- Greg Smith wrote: > Michael Clark wrote: > > The solution to the problem seemed to be to change the value for the > > wal_sync_method setting to fsync_writethrough from the default of fsync. > > I was surprised recently to discover the default wasn't > fsync_writethrough on that platform, because it probably should be. > There is no other safe mode to run PostgreSQL in OS X with. If you > don't invoke the write-through cache flushing code, you can expect > databases to get regularly corrupted if people do things like lose power > in the middle of writing something, exactly as you're seeing. > > > Secondly, I ask about an alternative solution to the corruption > > problem because with preliminary testing we have seen a significant > > degradation in performance. So far the two operations we have noted > > are database creation and database restores. > > For the restore case, you might get a good sized boost in performance > without introducing a risk of corruption by turning off the > synchronous_commit parameter. That will put you in a position where you > can have a committed transaction not actually be on disk if there's a > crash or sudden power outage, but you won't get an actual corruption in > that case. So fsync_writethough plus synchronous_commit=off should be > no less safe than what you've got now, but probably not as fast as what > you're used to. As already pointed out, there is a trade-off here you > can't bargain with: you can either have your data completely safe, or > you can execute quickly, but you can't do both. Robust data integrity > slows things down and there's little you can do about it without buying > hardware targeted to improve on that. > > The database creation issue just came up on one of the lists here the > other day as being particularly slow in the situation you're in, and > that parameter change doesn't help there. There's been some design > change suggestions around that to improve the situation, but you're not > likely to see those in the server code for a year or more. > > > I should note here that we have not tuned PG at all. > You could probably see a good sized performance increase just from > increasing checkpoint_segments a bit from its default (3). Since it > sounds like you're trying to keep your product's disk space footprint > under control, increasing that to around 10 would probably as high as > you want to go. You can't really increase shared_buffers a lot on your > platform lest your users get stuck with weird problems where the server > won't start, from what I hear OS X is fairly hostile to the kernel > adjustments you need to do in order to support that. > > There's a general intro to things you might tune in the postgresql.conf > at http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server > > None of those are going to help you out with slow database creation, you > might be able to pull down the restore times by tweaking some of the > parameters there upwards. A large number of the tunables recommend to > tweak there mainly impact query execution time. > > -- > Greg Smith 2ndQuadrant Baltimore, MD > PostgreSQL Training, Services and Support > greg@2ndQuadrant.com www.2ndQuadrant.com > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote: > Is changing the OS/X wal_sync_method default something we should > consider? > It's certainly reasonable to consider changing both OS X and Windows so wal_sync_method defaulted to fsync_writethrough, and provide safer operation by default on both those platforms. It would also result in a mass of complaints that PG 9.0 was much slower than 8.4 from people who were running it in an unsafe way before. Given that we're already starting to see that bad PR on Linux+ext4: http://www.phoronix.com/scan.php?page=article&item=ext4_then_now&num=3 I wonder whether it's the right time for the "reliable is the default on every platform" to just suck up and adopt everywhere, if we're already going to be fighting this "why is PG so slow on recent Linux versions?" PR campaign anyway. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.us