On 09.10.2012 11:00, Marcus Grimm wrote:
On 09.10.2012 10:44, Dan Kennedy wrote:
On 10/09/2012 03:30 PM, Marcus Grimm wrote:
Hello,
I'm currently investigating how far I can go with my
favorite DB engine. For that purpose I'm testing
my application with an artificial database that is
appx. 50 times bigger that the maximum I have
seen in the field so far.
The test creates a database from the scratch and just fills
the tables with random data. To speed up this operation
(which takes 3 hours) I drop all irrelevant indices
prior running the inserting.
Afterwards I need to create these indices because they are
necessary for the regular database operations.
Now, this (CREATE INDEX) fails after a few minutes with an error code
of 7 (malloc failed). I'm using the native C-Api...
I also specify: "PRAGMA cache_size=500000;" if that matters.
Try changing that to 2000 or something before executing the
CREATE INDEX statement.
Assuming you don't have SQLite configured to stored temp files
in memory, a CREATE INDEX statement starts by sorting the data
using a merge-sort before building the b-tree structure to
represent the index. This is much faster than just inserting keys
into the new b-tree in arbitrary order. But, the merge-sort code
assumes that it can plausibly allocate up to (cache-size * page-size)
bytes of memory using malloc() (not as a single chunk - in total).
And if a malloc() fails, you get this SQLITE_NOMEM error. In this
case, with "PRAGMA cache_size=500000", that might end up being too
much.
Hi Dan,
thanks for the quick reply. Of course I'm not using temp-memory files... :-)
I'll give it a try - However, I can see that the application increases
memory up to appx. 600 MB while the database is populated - That
is the cache_size=500000, I assume. So sqlite was able to allocate
that memory - Does the create index allocate that amount in
addition ?
Anyway, I now re-run the test as suggested and will report how it goes...
Bravo... It finished! :-)
Cool...
I don't quite understand why sqlite didn't run out of memory
during the heavy insert operations but later on when
creating the indices.
Also it is interesting that the application reaches about
650 MB memory usage during the insertion, after that loop I reduce
the page cache to "PRAGMA cache_size=50000;" and the memory usage
increases slightly to 750 MB and remains there while the various create index
command take place. I would expect that the memory usage drops down
after issuing the reduced cache size pragma.
Anyway, thanks again Dan! - now I can proceed with by tests..
Marcus
Marcus
Dan.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
