ansaurus

Question

can sqlite load 10M data into the memory.

Answer 1

+1 A:

Yes.

SQLite loads data into memory in pages. The default page size is 1024 bytes. You can change the page size using this command.

PRAGMA page_size = bytes;

But you have to do this before you create the database (or in 3.5.8, you can change it by running a VACUUM after issuing the new page size pragma).

The cache is based on number of pages. The default cache size is 2000 pages. You can change it using this command.

PRAGMA cache_size = Number-of-pages;

So to store 10MB of data in memory, either increase the page size to 5120 or increase the cache size to 10000.

More info on pragmas is here.

http://sqlite.org/pragma.html

Sam 2010-02-02 16:29:20

You should *not* change the page size unless you know what you're doing; it's the wrong thing to play with if you're trying to influence caching, and setting it to 5120 will probably have dire performance consequences. Also, for many scenarios, you want to pre-cache everything, since lazily populating cache by reading the file out of order through normal access can be very slow.

Glenn Maynard 2010-02-03 06:58:28

@Glenn Maynard, typically the first thing that's recommended when discussing performance in SQLite is to adjust the page size. The default value of 1024 is used for backwards compatibility and is a compromise between the many platforms where SQLite is used. Certainly on an embedded OS with very limited memory you want a lower page size than on a server OS that is largely dedicated to a database driven application. Our own testing with our application on Windows showed that the best page size for us is 4096 bytes.

Sam 2010-02-03 14:39:04

That's for I/O performance, not "how to make the cache size bigger". Setting it to a non-power-of-two like 5120 is almost certainly a very bad idea.

Glenn Maynard 2010-02-03 22:12:22

ansaurus

tags:

views:

answers:

can sqlite load 10M data into the memory.

related questions