Re: [sqlite] Performance Difference on Linux Kernel 2.4 and Kernel 2.6
[EMAIL PROTECTED] wrote: Your script requires 1002 separate ACID transactions. Each such transaction should require at least 2 complete revolutions of your disk drive platter - meaning that the minimum time to complete your script should be 16 seconds. The 2.4 kernel finished faster than this, which tells me that the 2.4 kernel probably does not implement the fsync() system call correctly. I have heard reports of this but could not verify it. What this means is that if you loose power unexpectedly, the 2.4 kernel might corrupt your database. Probably since the ReiserFS does a good job of journalling itself, you won't corrupt the database, but your transactions will certainly not be Durable. The 2.6 kernel is much slower because reiserFS is inefficient in its implementation of fsync(). Thanks for the thorough explanation. I understand it better now. You script does not measure how fast SQLite processes inserts. It measure how fast it does transacxtions. If you put a BEGIN and a COMMIT at the beginning and end of your script, I think you will find that the whole thing will run in a fraction of a second under either kernel. Yeah, I have tried that, but my benchmarking is on individual transactions instead of everything in one transaction. Thanks for the great help! -- D. Richard Hipp <[EMAIL PROTECTED]> Cheers, Phuah Yee Keat
Re: [sqlite] Performance Difference on Linux Kernel 2.4 and Kernel 2.6
Phuah Yee Keat <[EMAIL PROTECTED]> wrote: > Hi, > > I would like to take a shot at asking this question here instead of > Linux kernel mailing list just in case anyone here have encountered this > issue before. > > I am running sqlite 3.3.4. > > On a desktop slackware 10.2 machine, I have installed two stock kernels > 2.4.31 and 2.6.13. The box is using reiserfs. > > I have attached a script that I use to generate a sql script to test. > > The 2.4 kernel took 5 seconds to finish the sql script but the 2.6 > kernel took 50 seconds. Here are the GNU time output: > > Kernel 2.4===> > 0.31user 1.54system 0:06.27elapsed 29%CPU (0avgtext+0avgdata 0maxresident)k > 0inputs+0outputs (686major+184minor)pagefaults 0swaps > > Kernel 2.6===> > 0.48user 1.40system 0:52.24elapsed 3%CPU (0avgtext+0avgdata 0maxresident)k > 0inputs+0outputs (0major+1040minor)pagefaults 0swaps > > Any help will be greatly appreciated. I could provide more information > on the system as required. > Your script requires 1002 separate ACID transactions. Each such transaction should require at least 2 complete revolutions of your disk drive platter - meaning that the minimum time to complete your script should be 16 seconds. The 2.4 kernel finished faster than this, which tells me that the 2.4 kernel probably does not implement the fsync() system call correctly. I have heard reports of this but could not verify it. What this means is that if you loose power unexpectedly, the 2.4 kernel might corrupt your database. Probably since the ReiserFS does a good job of journalling itself, you won't corrupt the database, but your transactions will certainly not be Durable. The 2.6 kernel is much slower because reiserFS is inefficient in its implementation of fsync(). You script does not measure how fast SQLite processes inserts. It measure how fast it does transacxtions. If you put a BEGIN and a COMMIT at the beginning and end of your script, I think you will find that the whole thing will run in a fraction of a second under either kernel. -- D. Richard Hipp <[EMAIL PROTECTED]>