On Tue, 19 Feb 2013 16:05:14 -0600 Seebs <se...@seebs.net> wrote: > I'm afraid I haven't got a nicely isolated reproducer for this. > > I maintain a package, called pseudo, which includes a server built > around an sqlite database. In the process of debugging some performance > issues, I did some casual benchmarking. One of the first things I tried > was an in-memory database. Now, as has been observed, this is not > necessarily expected to be dramatically faster than an on-disk database. > > What I observed was a very, very, large slowdown. Time for an overall > task relying on the database was increased by a factor of 2-3 -- and the > database code is not that significant a part of the runtime, usually. I > used the sqlite3_profile() and observed that the sum of reported > processing time from that was within a few percent of the total increase > in execution time, which is at least suspicious.
Execution time doing what?, Waiting for I/O? How do you get execution time? What sql are you doing? > I did a bunch of testing trying to figure out more about this (and many > thanks to the friendly folks in #sqlite IRC who helped suggest some). > > First: No, not swapping or paging. We're talking 10MB of database > against 12GB of RAM with several GB free. The database on disk was > running synchronous = OFF, so I wasn't necessarily expecting huge > improvements. Don't run with synchronous off, it's only calms the symptom, don't cure/repair the problem and can mask the real problem. > In all cases, I was running against brand-new freshly created databases, > whether in memory or on disk. > > What I found: > 4. It scales roughly with database size; at 28,000 rows, it's quite > noticeable, and at 84,000 it's painful. Are you using a join? > I did find one thing that made me at least a little suspicious even in > 3.7 (specifically 3.7.15.2). In sqlite3PagerMovepage, there's a comment > right up at the top about journaling the page we're moving from, so > there's a call to sqlite3PagerWrite() if MEMDB. There's no check for > journaling mode, and it seems to me that if journaling is off, this > shouldn't be needed. I remember a previous discussion about this topic. > But that's not nearly enough to explain this. > > Admittedly, a performance issue which seems mostly fixed in 3.7 is > probably a lowish priority. What concerns me is that it seems to me that > the performance of :memory: may have taken a severe hit at some point, > leading to a flood of internet forum posts, stackoverflow questions, and > the like about poor performance of :memory:. Since sqlite is so fast to > begin with, this may not have gotten noticed. I don't use :memory: db, when need to do so, i use a normal db with 10-20% more page cache than file size. In rare use cases, i use a ram memory disk and backup(). > The test case I was using was pseudo version 1.4.5, on Linux hosts, > using the pseudo wrapper to untar a 28,000 file tarball. Surely I'm misinterpreted it but, Is the sqlite db in a directory with 28000 files? Each time a journal or temporal file is created, modified and deleted, and the main db file is modified, the directory entry must be updated and with 28000 files it's a very slow process. Please, post an example of your sql, perhaps it can be tuned for sqlite. > I am not sure > how easy or hard it would be to duplicate this with a simpler test case, > and won't have time to look more closely for a while, if ever. I'm > passing this on in case this rings a bell for someone, and to have it in > the archives if someone else comes looking. > > -s --- --- Eduardo Morras <emorr...@yahoo.es> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
