I hadn't found the malloc page - thanks for the link, it helps a lot. I'll look into moving to memsys5 when I upgrade to SQLite 3.6.1. I assumed I needed SQLITE_32BIT_ROWID as I have no native 64-bit integer support available - maybe it's not necessary after all? I also defined SQLITE_INT64_TYPE as long.
Assuming a disk-based DB is fully-cached (I specified 8000 pages), is it normal for an index to (temporarily) use many more cache pages than those taken up by the index data itself (as determined by the DB file size increase)? Cheers, Dave. -----Original Message----- From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: 27 August 2008 06:55 To: General Discussion of SQLite Database Subject: Re: [sqlite] Index memory usage in SQLite On Aug 26, 2008, at 1:13 PM, Dave Toll wrote: > Hello all > > > > I'm running some general performance tests on SQLite 3.5.9 (embedded C > platform), and I noticed that creating an index seems to use a lot > more > memory than I expected. > > > > An index on an integer column (30495 rows) uses 1,011,560 bytes. > > DB file size increases by 311,296 bytes. Perhaps the extra memory is used by additional cache space. What is your cache size set to? (The default is 2000 pages.) Have you read http://www.sqlite.org/malloc.html yet? > > > > > An index on a varchar column (average null-terminated text length 18 > bytes, 30495 rows) uses 2,180,040 bytes. > > DB file size increases by 856,064 bytes. > > > > I'm using the static memory allocator (mem3.c), page size 4096, and I > compiled with SQLITE_32BIT_ROWID. I measured the difference in memory > reported by sqlite3_memory_used(). Dropping the index does not return > any of the memory used, and does not reduce the DB file size. Are > these > results normal, or is some optimisation possible? > The mem3.c allocator is deprecated and will likely go away in a future release. mem5.c is preferred. The SQLITE_32BIT_ROWID option has not been tested by me since I can remember. I have no idea if it really works in all cases or not. For all I know it causes a memory leak. Dropping a table or index from a database causes the freed disk space to go onto a freelist to be used on the next INSERT. The space is not returned to the OS and the file size is not reduced. To reduce the database file size run VACUUM or enable auto_vacuum. D. Richard Hipp [EMAIL PROTECTED] _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
