Hi, just and update here. It seems that the newer libraries perform worse when we use multiple worker threads. I don't know why and I don't have time to investigate it any further. We'll stay with the old 3.7.16.2 for now.
Thank you for your assistance. -- Gruesse, Jakub -----Original Message----- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Jakub Zakrzewski Sent: Mittwoch, 26. August 2015 13:51 To: General Discussion of SQLite Database Subject: Re: [sqlite] Performance problems on windows Hi, also nope. I start with an empty (completely new) database. Then inserted 100K what we call "objects" and the same amount of "events". Inserting "events" takes place in one big commit. As soon as events are detected, the processing starts. I measure time since beginning of the first process till the end of the last. It is quite a lot of transactions, selects, and updates. Slightly less inserts and deletions. As a rule we do a VACUUM at each startup but clients expect to run this thing for months (if not years) without restart. -- Gruesse, Jakub -----Original Message----- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Rowan Worth Sent: Mittwoch, 26. August 2015 13:33 To: General Discussion of SQLite Database Subject: Re: [sqlite] Performance problems on windows What about fragmentation of the database itself? Does running VACUUM on the database affect performance? -Rowan On 26 August 2015 at 16:16, Jakub Zakrzewski <jzakrzewski at e2e.ch> wrote: > Hi, > > nope. The defragmentation job runs every Wednsday night and the > fragmentation is very low. > > PS: I'm putting together a small program to replay the queries I got > from sqlite trace. If that will behave the same way as the original, > I'll post it here. > > -- > Gruesse, > Jakub > > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto: > sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Scott > Robison > Sent: Dienstag, 25. August 2015 18:55 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Performance problems on windows > > On Aug 25, 2015 5:21 AM, "Jakub Zakrzewski" <jzakrzewski at e2e.ch> wrote: > > > > Hi, > > > > The slowdown is confirmed by one of our customers. He uses Win 2008 > Server x64. I'm testing on Win7 x64. > > This might be obvious in which case my apologies for bringing it up but: > are these systems demonstrating slowness perhaps heavily fragmented? > Either the database is fragmented, other non-SQLite files required by > the system, or especially the master file table? I've seen truly > abysmal performance on such systems myself, depending on IO patterns. > > > > > I'm sure that you're testing each release carefully. We do too. And > > we > still have weird bugs ;) > > > > Thanks for interest. If I can provide any useful information, just > > tell > me, what you need. I cannot reveal the source code but some profiling > results or SQL statements are not a secret. > > > > -- > > Gruesse, > > Jakub > > > > -----Original Message----- > > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto: > sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Richard > Hipp > > Sent: Dienstag, 25. August 2015 03:19 > > To: General Discussion of SQLite Database > > Subject: Re: [sqlite] Performance problems on windows > > > > I don't have any clues. > > > > While most of our work happens on Linux, we do measure performance > > on > Windows from one release to the next (see for example item 23-b on the > most recent release check-list > > https://www.sqlite.org/checklists/private/3081100/index#c23) and it > > gets > better from one release to the next. So I don't know why you are > seeing a slowdown. > > > > How do you measure? > > > > On 8/24/15, Jakub Zakrzewski <jzakrzewski at e2e.ch> wrote: > > > Hi All, > > > > > > I finally got a chance to upgrade SQLite for our product from > > > ancient 3.7.16.2. Initial tests on Linux were very promising - > > > ranging from 33% to even 300% (for one degenerated case) speed > > > improvement. So far > so good. > > > Problems begun when I have tested it on Windows. Depending on test > > > case the new version is up to 0.28x slower! In the course of > > > investigation I have managed to improve the performance by adding > > > few missing indexes but this has influenced both old and new > > > versions so the relative performance with > > > 3.8.11.1 is still like one third worse. > > > > > > I have tried doing it step-by-step: 3.7.16.2 -> 3.7.17.0 -> > > > 3.8.0.0 and the results are quite surprising: > > > 3.7.16.2 -> 3.7.17.0 : ~16% faster > > > 3.7.17.0 -> 3.8.0.0 : ~26% slower > > > 3.7.16.2 -> 3.8.0.0 : ~15% slower > > > 3.7.16.2 -> 3.8.11.1 : ~28% slower > > > > > > We use SQLite as backend ("persistent storage") to an > > > implementation of a state machine. The queries are rather simple > > > and each of them is too fast to measure (SQLite performance timer > > > only has resolution of > > > milliseconds) in the profiler it looks like the actual filesystem > > > functions were taking more time in new versions but that tells me > nothing really. > > > > > > Is there something I can tweak? > > > The page size is set to 4K (NTFS file system), synchonous is OFF, > > > journal_mode=truncated; > > > > > > With 10K objects all versions seem to perform equally: test takes > > > ~45s. For 100K objects it's already [m]:[s] > > > 3.7.16.2 : ~10:55 > > > 3.7.17.0 : ~09:30 > > > 3.8.0.0 : ~12:46 > > > 3.8.11.1 : ~15:08 > > > > > > I'm out of ideas here. Can someone help me with further investigation? > > > > > > -- > > > Gruesse, > > > Jakub > > > > > > _______________________________________________ > > > sqlite-users mailing list > > > sqlite-users at mailinglists.sqlite.org > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-use > > > rs > > > > > > > > > -- > > D. Richard Hipp > > drh at sqlite.org > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
