Re: [sqlite] Unhappy with performance
Hi, I have now sanitized the logic in the rest of the code to not require these flags anymore. Thus I got rid of the frequent updates to each record, that were an abomination in the first place. So I am left with decent DB-Operations which SQLite can manage perfectly well. Happy now. Thanks for all the help and for fantastic software. That said I can elaborate a bit: > That is a tad over 36,000 b-tree nodes per second. What are your actual > performance requirements? There are no hard requirements. But a 10-minute query every now and then really is outside of scope :-). I have inherited this piece of software. It used a fixed-size array in memory, blasted 1:1 in binary format out to disc periodically, as a database. That was fine except for two problems that needed adressing: 1) The device is limited to 128MB of RAM. That is for kernel, application and data. The current DB was 40MB. The number of records is quickly growing with business and is projected to become a problem soon. Also because of the fixed size it would have to be decided on a new maximum size and about 500 systems in the field upgraded. Only to repeat when that new maximum size is too small again. 2) Updating changed records is slow even in RAM because there is no indexing whatsoever. A larger number of overall records means more updates per time frame, the device is unresponsive while updating, and this is starting to become a problem. 2) I could have fixed by adding an indexing scheme but 1) is inherently unfixable. Any solution requires a switch to a disk-based system and any such system is going to be slower than stuff-it-ALL-into-a-contiguous-block-of-RAM . That is perfectly acceptable as long as the slowdown is well constrained. And it is now. So it was a shootout between some system between a homebrew on-disc-structure plus indexing, something like Berkeley-DB or a SQL-Engine. I preferred the latter because: - It would radically simplify the application code. And it did: The new version is less than 10% the application-LoC as the old one and much much cleaner. It almost looks a designed piece of software now as opposed to a smoldering heap of, of, something. - Also it would give me easy access to the database for debugging. Having a commandline tool to browse, query and update the data (and not having to write it myself) is a real plus. - After initially having rolled out an update to change to the DB-based code changes to the format of the data become much easier to handle (There have been several cases in the past where string fields needed resizing and so on, don't ask, it's all very sad). I was planning to clean up the client code to that DB backend anyway. That is the part doing all the ridiculous updates. But I had planned to do so in a seperate cycle. It IS an extremely ugly codebase and still breaks whenever I look at it hard enough. But now that I upgraded DB-backend to SQLite and fixed the basic algorithms in the client-code together it is beginning to resemble a real database-application and already works much better. Ciao, MM -- Marian Aldenhövel, Rosenhain 23, 53123 Bonn www.marian-aldenhoevel.de "Du chillst nicht, Du hängst doch nur faul rum!" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unhappy with performance
Hi, I have now sanitized the logic in the rest of the code to not require these flags anymore. Thus I got rid of the frequent updates to each record, that were an abomination in the first place. So I am left with decent DB-Operations which SQLite can manage perfectly well. Happy now. Thanks for all the help and for fantastic software. That said I can elaborate a bit: > That is a tad over 36,000 b-tree nodes per second. What are your actual > performance requirements? There are no hard requirements. But a 10-minute query every now and then really is outside of scope :-). I have inherited this piece of software. It used a fixed-size array in memory, blasted 1:1 in binary format out to disc periodically, as a database. That was fine except for two problems that needed adressing: 1) The device is limited to 128MB of RAM. That is for kernel, application and data. The current DB was 40MB. The number of records is quickly growing with business and is projected to become a problem soon. Also because of the fixed size it would have to be decided on a new maximum size and about 500 systems in the field upgraded. Only to repeat when that new maximum size is too small again. 2) Updating changed records is slow even in RAM because there is no indexing whatsoever. A larger number of overall records means more updates per time frame, the device is unresponsive while updating, and this is starting to become a problem. 2) I could have fixed by adding an indexing scheme but 1) is inherently unfixable. Any solution requires a switch to a disk-based system and any such system is going to be slower than stuff-it-ALL-into-a-contiguous-block-of-RAM . That is perfectly acceptable as long as the slowdown is well constrained. And it is now. So it was a shootout between some system between a homebrew on-disc-structure plus indexing, something like Berkeley-DB or a SQL-Engine. I preferred the latter because: - It would radically simplify the application code. And it did: The new version is less than 10% the application-LoC as the old one and much much cleaner. It almost looks a designed piece of software now as opposed to a smoldering heap of, of, something. - Also it would give me easy access to the database for debugging. Having a commandline tool to browse, query and update the data (and not having to write it myself) is a real plus. - After initially having rolled out an update to change to the DB-based code changes to the format of the data become much easier to handle (There have been several cases in the past where string fields needed resizing and so on, don't ask, it's all very sad). I was planning to clean up the client code to that DB backend anyway. That is the part doing all the ridiculous updates. But I had planned to do so in a seperate cycle. It IS an extremely ugly codebase and still breaks whenever I look at it hard enough. But now that I upgraded DB-backend to SQLite and fixed the basic algorithms in the client-code together it is beginning to resemble a real database-application and already works much better. Ciao, MM ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unhappy with performance
Since you just use one table you have no compelling reason to use a DB and could use a simple index file. I would expect your update of 300,000 records in that case to only take a few seconds. The footprint would also be far less. Something like D-ISAM would do the job. Note that you would forsake the transactional integrity and ACID features of Sqlite for speed and simplicity. JS Marian Aldenhövel wrote: > Hi, > > >>FWIW, I ran your simple example on a Windows XP machine through the Ruby >>driver and got 8 seconds for the update. > > > Scaling that down to the hardware being used, which is a 486-clone with > a 16bit bus showing as running at 31 BogoMIPS in linux (don't know the > clockspeed), propably kills the idea of using SQLite. > > Given SQLites performance data as published there propably also is no > suitable replacement that would allow me to use nice SQL. > > > > Ciao, MM ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unhappy with performance
Marian Aldenhövel wrote: > Hi, > > >> FWIW, I ran your simple example on a Windows XP machine through the Ruby >> driver and got 8 seconds for the update. >> > > Scaling that down to the hardware being used, which is a 486-clone with > a 16bit bus showing as running at 31 BogoMIPS in linux (don't know the > clockspeed), propably kills the idea of using SQLite. > > Given SQLites performance data as published there propably also is no > suitable replacement that would allow me to use nice SQL. > > > That is a tad over 36,000 b-tree nodes per second. What are your actual performance requirements? John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unhappy with performance
It does not look like you are using transactions. Marian Aldenhoevel wrote: > Hi, > > I have tried converting a program from a homebrew "database" to sqlite3 > for easier maintenance and hopefully better performance. While the > former is easily achieved, the performance is not making me happy. The > system is a "semi-embedded" small form-factor x86 machine with 128MB of > RAM booting and running off CF. OS is a 2.4.18-based linux built from > scratch. > > I have run several tests outlined below and I can't get decent > UPDATE-Performance out of my database. Apparently I am doing something > horribly wrong. Can someone enlighten me? > > The DB consists of a single table I am creating like this: > > CREATE TABLE IF NOT EXISTS KFZ ( > kfznr TEXT PRIMARY KEY, > saeule TEXT, > berechtigung2 TEXT, > berechtigung TEXT, > a_km TEXT, > max_km TEXT, > kont TEXT, > pincode TEXT, > CRC32 INTEGER, > verweis BLOB, > sperrung TEXT, > isNew INTEGER, > mustTrans INTEGER, > kennzeichen TEXT, > kontingentierung INTEGER); > > CREATE INDEX IF NOT EXISTS IDX_KFZ_MUSTRANS ON KFZ (mustTrans); > > CREATE INDEX IF NOT EXISTS IDX_KFZ_CRC32 ON KFZ (CRC32); > > Then I insert about 30 records in the context of a transaction. That > takes a while, but works reasonably well. The result is a DB file of > about 30MB. > > The problem is with bulk-updating: > > > # time sqlite3 kfzdb 'update kfz set musttrans=3' > > real10m 7.75s > > user8m 49.73s > > sys 0m 24.29s > > 10 minutes is too long. > > I must be doing something wrong. My database is on CF memory, and I > suspected that to be the problem. To verify that I mounted a tmpfs, > copied the DB there (taking 5.7s), and reran the test. Using memory > instead of disk brings the total down to just under 9 minutes. > > So disk-I/O is propably not the cause. It's dominated by user-space time > and while the command is running the CPU is used to 99% by sqlite3. > > Next I tried several of the suggestions from the SQLite Optimization > FAQ[1]. I timed the final combination of most of them: > > ># time sqlite3 kfzdb 'pragma synchronous=OFF ; pragma > count_changes=OFF ; pragma > journal_mode=OFF ; pragma temp_store=MEMORY ; update kfz set musttrans=3' > >off > >real8m 29.87s > >user8m 17.64s > >sys 0m 8.10s > > So no substantial improvement. > > Finally I repeated the test using a simpler table consisting only of the > column musttrans and 30 records. Updating that took abount the same > amount of time. > > Ciao, MM > > [1] http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unhappy with performance
>> Are you able to benchmark it using an actual PC's local hard drive? >> Just for comparison. To be fair, you'd have to use the same build of >> sqlite, or at one that was built the same way. > > That would be quite an effort. Just a thought. Since the build for your device is likely to be different than the one for the desktop, it's not really a meaningful comparison. You could post the commands that you're using to run your build and see if anyone can point out any problems or improvements there. Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unhappy with performance
Hi, > FWIW, I ran your simple example on a Windows XP machine through the Ruby > driver and got 8 seconds for the update. Scaling that down to the hardware being used, which is a 486-clone with a 16bit bus showing as running at 31 BogoMIPS in linux (don't know the clockspeed), propably kills the idea of using SQLite. Given SQLites performance data as published there propably also is no suitable replacement that would allow me to use nice SQL. Ciao, MM -- Marian Aldenhövel, Rosenhain 23, 53123 Bonn www.marian-aldenhoevel.de "Du chillst nicht, Du hängst doch nur faul rum!" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unhappy with performance
Marian Aldenhoevel wrote: > Hi, > > >> Are you able to benchmark it using an actual PC's local hard drive? >> Just for comparison. To be fair, you'd have to use the same build of >> sqlite, or at one that was built the same way. >> > > That would be quite an effort. > > For a quick data-point I copied the database file to my > development-machine running Ubuntu Server 7.x. > > The statement runs 11s on that system. That still feels somewhat > excessive for a simple "update 'em all", but I have no data to fairly > compare it with. > FWIW, I ran your simple example on a Windows XP machine through the Ruby driver and got 8 seconds for the update. John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unhappy with performance
Hi, > Are you able to benchmark it using an actual PC's local hard drive? > Just for comparison. To be fair, you'd have to use the same build of > sqlite, or at one that was built the same way. That would be quite an effort. For a quick data-point I copied the database file to my development-machine running Ubuntu Server 7.x. The statement runs 11s on that system. That still feels somewhat excessive for a simple "update 'em all", but I have no data to fairly compare it with. Ciao, MM ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unhappy with performance
> > What happens when you run the update inside a transaction? > I tried it like this: > > time sqlite3 kfzdb 'begin ; update kfz set musttrans=5 ; end' > No significant change in runtime either. Are you able to benchmark it using an actual PC's local hard drive? Just for comparison. To be fair, you'd have to use the same build of sqlite, or at one that was built the same way. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unhappy with performance
Hi, > Considering that all or most of the records have the same value in > musttrans column, do you really need an index on it? Try dropping the > index, see if it helps. They have the same value in my test. In the real application that field is used as a status field and most of the records will have 0, and a few dozen something else. I have repeated the test without the index. The difference is negligible and probaply in the normal range of measurements using my dummy benchmarking-technique. > What happens when you run the update inside a transaction? I tried it like this: > time sqlite3 kfzdb 'begin ; update kfz set musttrans=5 ; end' No significant change in runtime either. Thank you for your suggestions so far. Anything else? The alternative is cleaning up the homebrew version used so far and adding some sort of indexing scheme to it. And I am definitely NOT looking forward to having to do that. It is extremely yucky code! Ciao, MM ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unhappy with performance
> The problem is with bulk-updating: > > > # time sqlite3 kfzdb 'update kfz set musttrans=3' What happens when you run the update inside a transaction? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unhappy with performance
Marian Aldenhoevel <[EMAIL PROTECTED]> writes: > > Hi, > > I have tried converting a program from a homebrew "database" to sqlite3 > for easier maintenance and hopefully better performance. While the > former is easily achieved, the performance is not making me happy. The > system is a "semi-embedded" small form-factor x86 machine with 128MB of > RAM booting and running off CF. OS is a 2.4.18-based linux built from > scratch. > > I have run several tests outlined below and I can't get decent > UPDATE-Performance out of my database. Apparently I am doing something > horribly wrong. Can someone enlighten me? > > Finally I repeated the test using a simpler table consisting only of the > column musttrans and 30 records. Updating that took abount the same > amount of time. > > Ciao, MM > Speaking as a non-indexed person ! from other posts here I would suggest it's the fact that you have an index on the field you are updating that takes the time. Remove the index on that field and see how long it takes. Some people have even suggested (in a non-flash-based system presumably) DROPping the index, doing the UPDATEs, then reindexing. Regards, MikeW ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unhappy with performance
"Marian Aldenhoevel" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > CREATE INDEX IF NOT EXISTS IDX_KFZ_MUSTRANS ON KFZ (mustTrans); > > The problem is with bulk-updating: > >> # time sqlite3 kfzdb 'update kfz set musttrans=3' >> real10m 7.75s >> user8m 49.73s >> sys 0m 24.29s > > 10 minutes is too long. Considering that all or most of the records have the same value in musttrans column, do you really need an index on it? Try dropping the index, see if it helps. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
