I think I missed something; For clarification, you mention that you're running the tests and are monitoring memory use but (And here's what I'm missing) you don't see a memory load against the application? If you're using the :MEMORY: database, throwing anything at it should bump up the count and NOT release memory until you vacuum, and if its not, something isn't right. If the machine has a +4gb of available memory, I'd suggest maybe creating an additional table and throwing a gig worth of a record blob and see if memory does get bumped.
One of my apps toggles between the entire DB in either disk or memory, and the database itself is close to 300meg in size. No other schema changes happen at the database level. At app launch, if the user has opted to use memory, the backup API is invoked, and I note that task manager shows memory filling up accordingly for the application. During development, one of my tests was to throw 4gig of data at it, and memory bumped accordingly. When the app is configured to run off the disk, memory utilization hangs at around the 1meg mark and stays consistent, at least until I start actively using it. (Undos are stored in memory via SQLite). My daily/dev/game rig is a first gen Intel I7 @ 3.3ghz, 12gig of memory, with twin Sata3 256gig SDDs @ RAID-0. When I toggle between disk and memory use, I notice a change in performance. The program just feels more responsive with memory usage instead of 'platter' usage, considering EVERY time I release the mouse button, the change is immediately written to the database (Each change amounts to about 6k of a blob). I also wrote some of the code on a lower end I5 with a platter and I still notice a difference between the two modes as well, and the i5 runs off a 7200rpm platter SATA3 and only 4gig of memory. Both are Win7x64 machines. Mind you, I've not done any timing bench marks, and what I'm noticing is purely on just the feel of the app. You mention indexes. Are these just basic Unique constraints, or, standard indexes? How many records are in the database, say on medium to heavy load? On Wed, Feb 20, 2013 at 1:06 PM, Seebs <se...@seebs.net> wrote: > On 20 Feb 2013, at 11:47, Simon Slavin wrote: > > On 20 Feb 2013, at 5:32pm, Seebs <se...@seebs.net> wrote: >> >> First: The SQL is completely trivial. >>> Second: I am not having performance problems with sqlite, I am having >>> performance problems with :memory:. Performance on files is lovely. >>> >> >> This normally means that your computer has very little actual memory to >> devote to 'memory' uses. It is using a lot of swap space (or paging, or >> virtual memory, whatever you want to call it). So when you think you are >> doing calls which reference ':memory:' it actually has to fetch and write >> those parts of memory to disk. >> > > I am pretty sure that's not it. > > I know I'm a newbie on the list, but please imagine for the sake of > argument that I am a basically competent programmer with enough sysadmin > background to be aware of the obvious problems. > > In this case, I tested this pretty carefully over a period of about six > hours of testing across multiple machines. I was watching CPU load, memory > load, and all that stuff. And this is a machine with >4GB of *free* memory > -- that's over and above even the couple GB of disk cache being used. > > Process memory size is not appreciably different between sqlite 3.6 and > 3.7, or between page size of 1024 or 8192. Runtime is massively different. > I am pretty sure this is an actual computation-time issue, and my intuition > is that it's quite possible there's at least some other performance issues > lurking, because it appears that :memory: *used to be* dramatically faster > than disk, but something changed in the last couple of years. > > -s > > ______________________________**_________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<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
