Just to be sure of the real issue: - your application runs just fine with a small amount of memory (2M) for most queries - a small subset of queries requires "the whole db" to be kept in memory for performance reasons - the application should revert to "small memory" when the "whole db" query is finished
The most common case where throwing memory at a query helps is when the query in question is doing a lot of re-reading of rows. This aagain is commonly caused by choosing an inferior data model (that makes the required information "hard to get") and/or an inferior execution plan (that does a lot of avoidable re-reading of rows). - Revisit your data model to make the desired information less "hard to get", e.g. reduce the number of joins required. - Run ANALYZE on a production-sized, typical data set; this allows the QP to devise a better plan - Add more indexes, rerun ANALYZE, see if the QP has changed plans, drop unused indexes, check performance. Rinse and repeat. - Manually adjust the order of joins (use CROSS JOIN syntax to prevent reordering of tables by the QP) I have found that most queries that join a multitude of tables can be split into "determine result set" and "add info for display purposes". Put the "determine result set" tables first, and the "add info for display" tables last. These measures should shave several orders of magnitude from execution times. To answer the orginal question: Turn up the mmap_size before running the "whole db" query and turn it back down afterwards. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Nick Gesendet: Freitag, 13. April 2018 10:07 An: sqlite-users@mailinglists.sqlite.org Betreff: Re: [sqlite] [EXTERNAL] Does mmap increase PSS? I find I confused several concepts of memory. Sorry for that. And I guess I finally understand what my question really is: Still there is only one process doing a SELECT * in a 256M db file. Then 256M physical memory should be used when doing the query. (Ignore the cache_size.) So the PSS of my program should be 256M at that time. That is OK. But from now on, the PSS will be 256M for a long time as my process will be active for hours doing insert-select-insert-select without closing. My system can not afford a 256M-PSS program. In another word, the most important thing is there is no opportunity to call unmmap() in my program. Can I find a reasonable time to call unmmap or sqlite3OsUnfetch? Or is there any way to solve the problem other than pragma mmap_size=2M? Really thanks for your help. -- Sent from: http://sqlite.1065341.n5.nabble.com/ _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
