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-users > > > > > > > > > -- > > 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 >
