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 
performance reasons
- 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.

-----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
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

Reply via email to