Re: [sqlite] sluggish operation on os x?
Very much so. And even to make it a runtime-changeable pragma. (Because, otherwise things like fink packages would have a difficult decision to make. only the application really knows, whether syncing is absolutely required, and to which degree. If the fink author needs to make the choice, I'd opt for regular 'fsync()', too (who plugs out their OS X anyway.. ;) 21.2.2005 kello 21:00, Curtis King kirjoitti: My application does this and the performance was still very poor on OS X with F_FULLSYNC on. Since OS X is the only OS which has F_FULLFSYNC it would be nice to make the use of it a configure option. ck
Re: [sqlite] sluggish operation on os x?
On Feb 21, 2005, at 11:44 AM, Curtis King wrote: On 21-Feb-05, at 11:11 AM, [EMAIL PROTECTED] wrote: OK -- so, you are willing to accept the risk of non-recoverable database corruption in the event of power outage or other kinds of catastrophic system failure (including the plug being pulled on a FireWire drive without it being properly unmounted)? I.e. that risk is perceived to be acceptably small that the performance hit is not justifiable? The performance hit is much larger than the risk, so in some cases, no FireWire drives and there is an UPS, the risk is acceptable for the performance gain. To ask the question an other way since FreeBSD, Linux, Solaris, etc do not support F_FULLFSYNC is it safe to run any kind of database on them ;) FreeBSD/Linux/Solaris are most often run in less hostile environments -- cages, racks, UPS'd, etc... Mac OS X has to deal with a very hostile computing environment -- lots of sleep/wake, power loss, drives being hot plugged (often without proper unmounting), etc... But, agreed, it should be an option. b.bum
Re: [sqlite] sluggish operation on os x?
On 21-Feb-05, at 11:11 AM, [EMAIL PROTECTED] wrote: OK -- so, you are willing to accept the risk of non-recoverable database corruption in the event of power outage or other kinds of catastrophic system failure (including the plug being pulled on a FireWire drive without it being properly unmounted)? I.e. that risk is perceived to be acceptably small that the performance hit is not justifiable? The performance hit is much larger than the risk, so in some cases, no FireWire drives and there is an UPS, the risk is acceptable for the performance gain. To ask the question an other way since FreeBSD, Linux, Solaris, etc do not support F_FULLFSYNC is it safe to run any kind of database on them ;) ck
Re: [sqlite] sluggish operation on os x?
On Feb 21, 2005, at 11:00 AM, Curtis King wrote: On 21-Feb-05, at 10:39 AM, [EMAIL PROTECTED] wrote: It is a trade off between guaranteed data integrity and performance. If there happen to be a bunch of other apps writing to the disk when you do a SQLite transaction, then all of that data has to be flushed to the disk. As Domnic said, fsync() does not guarantee that the bytes hit the platter on any system. Pull the plug after a COMMIT and you are very likely going to see only part of the pages written. You can also use the 'synchronous' pragma to control the number of F_FULLSYNCs executed during any single transaction. By default, it will be three-- probably too excessive. I sill want the "normal" fsync() called and using this pragma means fsync() is not called. OK -- so, you are willing to accept the risk of non-recoverable database corruption in the event of power outage or other kinds of catastrophic system failure (including the plug being pulled on a FireWire drive without it being properly unmounted)? I.e. that risk is perceived to be acceptably small that the performance hit is not justifiable? The best way to guarantee maximal performance is to bunch up your INSERT and UPDATE statements into transactions as much as possible. This is often true regardless of the presence of F_FULLSYNC. My application does this and the performance was still very poor on OS X with F_FULLSYNC on. Since OS X is the only OS which has F_FULLFSYNC it would be nice to make the use of it a configure option. Noted. Thanks. b.bum
Re: [sqlite] sluggish operation on os x?
On 21-Feb-05, at 10:39 AM, [EMAIL PROTECTED] wrote: It is a trade off between guaranteed data integrity and performance. If there happen to be a bunch of other apps writing to the disk when you do a SQLite transaction, then all of that data has to be flushed to the disk. As Domnic said, fsync() does not guarantee that the bytes hit the platter on any system. Pull the plug after a COMMIT and you are very likely going to see only part of the pages written. You can also use the 'synchronous' pragma to control the number of F_FULLSYNCs executed during any single transaction. By default, it will be three-- probably too excessive. I sill want the "normal" fsync() called and using this pragma means fsync() is not called. The best way to guarantee maximal performance is to bunch up your INSERT and UPDATE statements into transactions as much as possible. This is often true regardless of the presence of F_FULLSYNC. My application does this and the performance was still very poor on OS X with F_FULLSYNC on. Since OS X is the only OS which has F_FULLFSYNC it would be nice to make the use of it a configure option. ck
Re: [sqlite] sluggish operation on os x?
On Feb 21, 2005, at 9:54 AM, James Berry wrote: On Feb 21, 2005, at 9:40 AM, Curtis King wrote: I noticed this as well, so I profiled my call and found sync was taking forever. I removed the following fcntl call, rc = fcntl(fd, F_FULLFSYNC, 0);. Performance was back to normal. Here are some comments about F_FULLFSYNC, off the darwin list just two days ago. They mention why it's there, but don't mention how slow the performance might be... It is a trade off between guaranteed data integrity and performance. If there happen to be a bunch of other apps writing to the disk when you do a SQLite transaction, then all of that data has to be flushed to the disk. As Domnic said, fsync() does not guarantee that the bytes hit the platter on any system. Pull the plug after a COMMIT and you are very likely going to see only part of the pages written. You can also use the 'synchronous' pragma to control the number of F_FULLSYNCs executed during any single transaction. By default, it will be three-- probably too excessive. The best way to guarantee maximal performance is to bunch up your INSERT and UPDATE statements into transactions as much as possible. This is often true regardless of the presence of F_FULLSYNC. Note that this situation only arises in the case of catastrophic system failure such as a power failure or kernel panic. b.bum
Re: [sqlite] sluggish operation on os x?
On Feb 21, 2005, at 9:40 AM, Curtis King wrote: I noticed this as well, so I profiled my call and found sync was taking forever. I removed the following fcntl call, rc = fcntl(fd, F_FULLFSYNC, 0);. Performance was back to normal. Here are some comments about F_FULLFSYNC, off the darwin list just two days ago. They mention why it's there, but don't mention how slow the performance might be... -jdb From: [EMAIL PROTECTED] Subject: Re: bad fsync? (A.M.) Date: February 19, 2005 5:59:21 PM PST To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] MySQL makes the following claim at: http://dev.mysql.com/doc/mysql/en/news-4-1-9.html "InnoDB: Use the fcntl() file flush method on Mac OS X versions 10.3 and up. Apple had disabled fsync() in Mac OS X for internal disk drives, which caused corruption at power outages." First of all, is this accurate? A pointer to some docs or a tech note on this would be helpful. The comments about fsync() are wrong... On MacOS X, fsync() always has and always will flush all file data from host memory to the drive on which the file resides. The behavior of fsync() on MacOS X is the same as it is on every other version of Unix since the dawn of time (well, since the introduction of fsync anyway :-). I believe that what the above comment refers to is the fact that fsync() is not sufficient to guarantee that your data is on stable storage and on MacOS X we provide a fcntl(), called F_FULLFSYNC, to ask the drive to flush all buffered data to stable storage. Let me explain in more detail. With fsync() even though the OS writes the data through to the disk and the disk says "yes I wrote the data", the data is not actually on permanent storage. Unless you explicitly disable it, all disks have a write buffer which holds data you've written. The disk buffers the data you wrote until it decides to flush it to the platters (and the writes may not be in the order you wrote them). If you lose power or the system crashes before the data is written, you can wind up in a situation where only some of your data is actually on disk. What is worse is that even if you write blocks A, B and C, call fsync() and then write block D you may find after rebooting that blocks A and D are on disk but B and C are not (in fact any ordering of A, B, C, and D is possible). While this may seem like a rare case it is not. In fact if you sit down and pull the plug on a system you can make it happen in one or two plug pulls. I have even gone so far as to watch this behavior with a logic analyzer on the ATA bus: I saw the data for two writes come across the ATA cable, the drive replied and said the writes were successful and then when we rebooted the data from the second write was correct on disk but the data from the first write was not. To deal with this we introduced the F_FULLFSYNC fcntl which will ask the drive to flush all of its buffered data to disk. When an app needs to guarantee that data is on disk it should use F_FULLFSYNC. In most cases you do not need such a heavy handed operation and fsync() is good enough. But in an app like a database, it is essential if you want transactional integrity. Now, a little bit more detail: on ATA drives we implement F_FULLFSYNC with the FLUSH_TRACK_CACHE command. All drives sold by Apple will honor this command. Unfortunately quite a few firewire drive vendors disable this command and do not pass it to the drive. This means that most external firewire drives are not reliable if you lose power or the system crashes. We can't work-around that unless we ask the drive to disable the write cache completely (which hurts performance quite badly -- and even that may not be enough as some drives will ignore that request too). So in summary, I believe that the comments in the MySQL news posting are slightly confused. On MacOS X fsync() behaves the same as it does on all Unices. That's not good enough if you really care about data integrity and so we also provide the F_FULLFSYNC fcntl. As far as I know, MacOS X is the only OS to provide this feature for apps that need to truly guarantee their data is on disk. Hope this clears things up. --dominic ___ Do not post admin requests to the list. They will be ignored. Darwin-dev mailing list ([EMAIL PROTECTED]) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/darwin-dev/james%40jberry.us This email sent to [EMAIL PROTECTED]
Re: [sqlite] sluggish operation on os x?
I noticed this as well, so I profiled my call and found sync was taking forever. I removed the following fcntl call, rc = fcntl(fd, F_FULLFSYNC, 0);. Performance was back to normal. ck
