Thanks Jay I'm currently using an 8000 page cache, and as far as I know my DB should have been fully-cached before I created the indices (it was built from scratch and queried several times). Disk usage is pretty much as I expected. Does creating an index use extra pages on top of the fully-cached DB and the index representation itself? I'll have to read up on VACUUM...
Cheers, Dave. -----Original Message----- From: Jay A. Kreibich [mailto:[EMAIL PROTECTED] Sent: 27 August 2008 06:52 To: General Discussion of SQLite Database Subject: Re: [sqlite] Index memory usage in SQLite On Tue, Aug 26, 2008 at 06:13:29PM +0100, Dave Toll scratched on the wall: > 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. Creating an index requires reading and sorting the original table. It tends to beat the page cache fairly hard. This is a known condition. In fact, the first bit of advice to increase the speed of index creation is to make the page cache larger. > An index on an integer column (30495 rows) uses 1,011,560 bytes. > > DB file size increases by 311,296 bytes. Or about 10.2 bytes per item, which sounds pretty reasonable. An index in SQLite contains a full copy of the indexed data, so that plus a rowid reference back to the original table and other metadata makes 10 bytes sound about right, especially if most of the integers are smallish (SQLite uses var-length integers). > 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. Same thing. 28 bytes per item sounds reasonable, especially if the average data item is 18 bytes. Strings won't pack into pages quite as efficiently, so I'd expect a higher overhead. > 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, If you're measuring the process memory usage, that's normal. Free pages aren't returned to the OS. If you're measuring the allocator, chances are most of that memory is in the page cache, and will not be returned. The default page size is 1K and the default cache size is 2K with about 0.5K of overhead per page, meaning the default system expects about 3MB worth of cache. Neither of the examples you've given go over that, so unless you can figure out otherwise, I'd assume most of that is cache allocation. The cache will grow until it hits its max size but won't be returned. > and does not reduce the DB file size. That's normal. As with memory systems, free pages are not released unless you vacuum the database file. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
