Hello Jakub, Windows user here. I use Sqlite to contain some fairly bulky data but the tables that store this data are simple and the queries and indexes are also simple. Using a 300 meg test file, I fed data into fresh DB's using different versions of Sqlite including some of the ones you listed in your testing. In all cases the latest version was marginally faster. Say 3 seconds in a 75 second run.
Since we don't use the same DB's, I'm not surprised I couldn't reproduce your results but, it does suggest that raw speed to disk is close between the versions. I tried without journal, with journal and WAL mode. WAL mode was slightly faster than journal mode. Not using a journal file at all knocked 18 seconds off a run. I was able to knock 10 seconds off the feed in all modes by increasing my Sqlite cache size to 10,000 pages. My users had reported that older versions were faster than new too but my testing suggests, even if it's true, the problem isn't Sqlite in my case. I see your new post about multiple threads. Typically my access pattern is one thread at a time so, again my testing probably doesn't apply to your particular case. C Wednesday, August 26, 2015, 7:51:08 AM, you wrote: JZ> Hi, JZ> also nope. I start with an empty (completely new) database. Then JZ> inserted 100K what we call "objects" and the same amount of JZ> "events". Inserting "events" takes place in one big commit. As JZ> soon as events are detected, the processing starts. I measure time JZ> since beginning of the first process till the end of the last. It JZ> is quite a lot of transactions, selects, and updates. Slightly less inserts and deletions. JZ> As a rule we do a VACUUM at each startup but clients expect to JZ> run this thing for months (if not years) without restart. JZ> -- JZ> Gruesse, JZ> Jakub JZ> -----Original Message----- JZ> From: sqlite-users-bounces at mailinglists.sqlite.org JZ> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Rowan Worth JZ> Sent: Mittwoch, 26. August 2015 13:33 JZ> To: General Discussion of SQLite Database JZ> Subject: Re: [sqlite] Performance problems on windows JZ> What about fragmentation of the database itself? Does running JZ> VACUUM on the database affect performance? JZ> -Rowan JZ> 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 >> > > >> >
