Just to be sure of the real issue:
- your application runs just fine with a small amount of memory (2M) for most
- a small subset of queries requires "the whole db" to be kept in memory for
- the application should revert to "small memory" when the "whole db" query is
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.
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Nick
Gesendet: Freitag, 13. April 2018 10:07
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
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
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