Re: [sqlite] CREATE INDEX on huge DB fails with SQLITE_NOMEM
On 09.10.2012 12:46, Clemens Ladisch wrote: Marcus Grimm wrote: I can see that the application increases memory up to appx. 600 MB while the database is populated - That is the cache_size=50, I assume. cache_size=50 corresponds to: 255 MB (page size 512 bytes) 488 MB (page size 1 KB) 977 MB (page size 2 KB) 1.9 GB (page size 4 KB) What does "PRAGMA page_size;" say in your database? I think it the default value (1 KB). And are you running in a 32-bit or 64-bit process? It is a 32 bit process on a 64 bit Win7 machine. During the process of inserting the test data the memory usage increases constantly to around 650 MB and remains there - no problem with that. Sqlite just fills the cache that I specified. It is interesting though, that create index appears to require memory above the specified page cache. No problem actually but one need to be aware about it. Anyway, everything works now and I can proceed. Marcus Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org 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
Re: [sqlite] CREATE INDEX on huge DB fails with SQLITE_NOMEM
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=50;" 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=50", 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=50, 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=5;" 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 sqlite-users@sqlite.org 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CREATE INDEX on huge DB fails with SQLITE_NOMEM
Marcus Grimm wrote: > I can see that the application increases memory up to appx. 600 MB > while the database is populated - That is the cache_size=50, > I assume. cache_size=50 corresponds to: 255 MB (page size 512 bytes) 488 MB (page size 1 KB) 977 MB (page size 2 KB) 1.9 GB (page size 4 KB) What does "PRAGMA page_size;" say in your database? And are you running in a 32-bit or 64-bit process? Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CREATE INDEX on huge DB fails with SQLITE_NOMEM
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=50;" 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=50", 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=50, 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... Marcus Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org 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
Re: [sqlite] CREATE INDEX on huge DB fails with SQLITE_NOMEM
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=50;" 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=50", that might end up being too much. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] CREATE INDEX on huge DB fails with SQLITE_NOMEM
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=50;" if that matters. The table/index in question has appx. 300 million rows... Is there a workaround, other than having the indices defined from the beginning ? Haven't tried yet... though. It could be that I'll need to add indices in future versions of the application and I'm concerned that sqlite will not be able to do so if the database exceeds a certain size. Please note that sqlite can (I think) very well handle that DB size, it's just the CREATE INDEX that is, so far, a bit disappointing. Any comment on this ? I tried with sqlite 3.7.14.1 and 3.7.8 - no difference. Kind regards Marcus ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
