Does mmap increase PSS?

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Does mmap increase PSS?

Nick
Hi,
I guess that "cache_size=2000" means PSS of my process will always less than
2M.
But, when I use PRAMGA mmap_size to enable mmap, I found the PSS of my
process will almost the same as my db.
Is that correct?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Does mmap increase PSS?

Hick Gunter
Your guess is wrong.

"pragma cache_size=2000" will limit the internal page cache to 2000 PAGES (of whatever page size the db file has) PER OPEN FILE. To set a limit of 2000kBytes PER OPEN FILE, use "pragma cache_size=-2000". The page cache is process private memory. Whatever portion is actually loaded into main memory (the RSS) counts towards PSS.

"pragma mmap_size=N" will limit the number of bytes that will be mapped into memory PER OPEN FILE. Memory mapped files may be shared between processes. Note that each process gets to determine which part(s) of a file it wants to map. Whatever portion of the file is actually loaded into main memory (the RSS) counts towards the PSS of all connected processes, divided by the number of processes sharing that specific page (on a page per page basis).

If you are running only 1 process AND it has page faulted the whole mmap segment into main memory, all of that counts towards PSS.
If you are running 2 processes AND they have mmaped the same region of the same file AND they have page faulted the whole segment into main memory, each process is held accountable for HALF of that size.

Reducing PSS via mmap is only possible if you are runnning multiple copies of the same application against the same db file.

There is no way to directly influence the PSS of a process, since that partly depends on ALL of the other processes.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Nick
Gesendet: Mittwoch, 11. April 2018 08:52
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] Does mmap increase PSS?

Hi,
I guess that "cache_size=2000" means PSS of my process will always less than 2M.
But, when I use PRAMGA mmap_size to enable mmap, I found the PSS of my process will almost the same as my db.
Is that correct?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Does mmap increase PSS?

Nick
Thanks for your explanation.
I want to get a confirmation that my understanding is correct and that if I
use mmap_size=256M and I have only 1 process, then the PSS of the process
will always the same as the size of my db file, as unixMapfile(-1) means map
the whole file. (A big db file means 256M PSS) Is that correct?

In fact I had expected mmap only took up virtual memory instead of PSS.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Does mmap increase PSS?

Hick Gunter
No.

You are confusing several issues here.

There is "process size", which is the number of byte addresses that are theoretically valid within a process, as registered in the "process page table". Attempting to reference an address that has no corresponding page table entry causes a SEGV. Because accidentally dereferencing a NULL pointer is a common mistake and NULL is usually represented by binary all zeroes, the first page of a process never has a page table entry.

The cache_size and the mmap_size both influence how much of the "process size" is used to hold pages from your database file, by keeping a copy in the cache or mapping memory to the db disk file respectively.

Then there is "resident set size" (RSS), which is the portion of "process size" that, at a specific instant, actually refers to an address in main memory. Attempting to reference an address whose page table entry indicates that it is not currently in main memory causes a "page fault". The OS will interrupt program execution, allocate a page in main memory and provide the currently valid contents before restarting program execution at the instruction that caused the page fault.

Because main memory is usually much smaller than the combined process size of all currently running processes, the latter compete for main memory. The OS attempts to keep frequently accessed pages in main memory. It also attempts to re-use immutable pages by making them "shared"; it keeps just one copy in main memory and lets multiple processes map that page into their address space.

This is where "process set size" PSS comes into play. PSS is a measure of how much memory load is a process' fault. Each resident page that is exclusively used by a single process is that process' fault alone. The portion of cache_size that is used and currently resident falls within that category., as do stack, static non const and heap memory. Each resident page that is used by more than one process is the collective fault of all the processes. The portion of mmap_size that is used and currently resident falls within this category, as do code (most notably libraray code) and static const memory. Since blame is apportioned by current use, the PSS of a given process can and often does change without any action of its own. Creating or terminating a process that runs the same program, calls the same library or uses the same shared memory segment will change the PSS of any given process.

IF
- you have a system with a large enough amount of main memory
- and with a large enough amount of free main memory
- and run only one copy of your program
- and cache_size or mmap_size respectively are set larger than your db file
- and you are running a single copy of your application

THEN

- running a query than visits every row in your database (i.e. causes a page fault for each page) is likely to cause the PSS of the process to increase by the size of your db file.
- running the same query in a second process will cause the RSS of the original process to decrease by about half of the application code size and half the mmap_size

SO

- both cache_size and mmap_size TEND TO increase PSS, but mmap_size has the potential to split this among several instances of your application runnung with the same db file.

If you are running on a system that has severe main memory constraints, both settings will probably just shift the load between file IO and swap/page IO. Note thsat the sum of all RSS of all running processes can never exceed main memory.


-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Nick
Gesendet: Donnerstag, 12. April 2018 09:05
An: [hidden email]
Betreff: Re: [sqlite] [EXTERNAL] Does mmap increase PSS?

Thanks for your explanation.
I want to get a confirmation that my understanding is correct and that if I use mmap_size=256M and I have only 1 process, then the PSS of the process will always the same as the size of my db file, as unixMapfile(-1) means map the whole file. (A big db file means 256M PSS) Is that correct?

In fact I had expected mmap only took up virtual memory instead of PSS.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Does mmap increase PSS?

Nick
This post was updated on .
Thanks a lot, Hick.
So, if
- mmap_size=256M
- run only one copy of my program (has no other process to split PSS)
- have a large enough amount of main memory (bigger than 256M)
- a big db file (bigger than 256M)
- a SELECT *
Then the PSS of my program will be about 256M.

Is that correct?



--
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
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Does mmap increase PSS?

Hick Gunter
Not necessarily. Only if you run queries that access more than 256M of the db file AND there is no contention for memory resources.

Why is the PSS of your process such a concern?

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Nick
Gesendet: Donnerstag, 12. April 2018 11:24
An: [hidden email]
Betreff: Re: [sqlite] [EXTERNAL] Does mmap increase PSS?

Thanks a lot, Hick.
So, if
- mmap_size=256M
- run only one copy of my program (has no other process to split PSS)
- have a large enough amount of main memory (bigger than 256M)
- a big db file (bigger than 256M)
Then the PSS of my program will be about 256M.

Is that correct?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Does mmap increase PSS?

Nick
OK, I understand.

I ran a simple program to test if mmap will cause the increasing of PSS. But
I did not find the PSS increase according to showmap:
    addr = (char *)mmap(NULL, length, PROT_READ, MAP_SHARED, fd, 0);
    for(i=0; i<length; i++){
        c = addr[i];
    }
Which make me have a misunderstand that mmap will always not use physical
memory.

Thus, I enable mmap in my another sqlite program and find the PSS increases
along with the growing of my db file which makes me confused as 256M PSS is
too big for my system.

(Still do not understand why my simple test program does not work.)

And I guess there is no way to reduce the PSS of my sqlite program unless
pragma mmap_size=10M.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Does mmap increase PSS?

Hick Gunter
AFAICT the showmap program is just a pretty-printer for the /proc/<pid>/smaps file on android.

You need to compare three states:

1) before mmap() is called (there should be no section referring to your file)
2) after mmap() is called (the section should be there, but RSS=PSS=0)
3) after the loop (the section should be there and RSS=PSS > 0)

Which length(s) did you try? Perhaps android mmap is trying to be clever and is preloading parts of the file (you would see this in state 2).

With respect to PSS, there is no difference between setting the page cache size and setting the mmap size to the amount of PSS you are willing to allow for db access (instead of the database size). What makes you think that using mmap will be superior to using the page cache?

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Nick
Gesendet: Freitag, 13. April 2018 05:37
An: [hidden email]
Betreff: Re: [sqlite] [EXTERNAL] Does mmap increase PSS?

OK, I understand.

I ran a simple program to test if mmap will cause the increasing of PSS. But I did not find the PSS increase according to showmap:
    addr = (char *)mmap(NULL, length, PROT_READ, MAP_SHARED, fd, 0);
    for(i=0; i<length; i++){
        c = addr[i];
    }
Which make me have a misunderstand that mmap will always not use physical memory.

Thus, I enable mmap in my another sqlite program and find the PSS increases along with the growing of my db file which makes me confused as 256M PSS is too big for my system.

(Still do not understand why my simple test program does not work.)

And I guess there is no way to reduce the PSS of my sqlite program unless pragma mmap_size=10M.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Does mmap increase PSS?

Nick
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Does mmap increase PSS?

Hick Gunter
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:[hidden email]] Im Auftrag von Nick
Gesendet: Freitag, 13. April 2018 10:07
An: [hidden email]
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
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users