Written by Andres Freund
on
Measuring the Memory Overhead of a Postgres Connection
One fairly common complaint about postgres is that is that each connection uses too much memory. Often made when comparing postgres' connection model to one where each connection is assigned a dedicated thread, instead of the current model where each connection has a dedicated process.
To be clear: This is a worthwhile discussion to have. And there are several important improvements we could make to reduce memory usage.
That said, I think one common cause of these concerns is that the easy ways to measure the memory usage of a postgres backend, like top and ps, are quite misleading.
It is surprisingly hard to accurately measure the increase in memory usage by an additional connection.
In this post I’m mostly going to talk about running Postgres on Linux, since that is what I have the most experience with.
My bold claim is that, when measuring accurately, a connection only has an overhead of less than 2MiB (see conclusion).
A first look
Just using the common operating system tools make the overhead look much bigger than it actually is. Especially when not utilizing huge_pages (not recommended), the memory usage for each process will look high.
Let’s first look at a freshly established connection, in a freshly started postgres cluster:
andres@awork3:~$ psql
postgres[2003213][1]=# SELECT pg_backend_pid();
┌────────────────┐
│ pg_backend_pid │
├────────────────┤
│ 2003213 │
└────────────────┘
(1 row)
andres@awork3:~/src/postgresql$ ps -q 2003213 -eo pid,rss
PID RSS
2003213 16944
About 16MiB.
Massive leaks!?! Luckily not.
What’s worse, the memory usage will appear to grow over time. To show this problem, I’ll use the pgprewarm extension to load all pages in a table into postgres' buffer pool:
postgres[2003213][1]=# SHOW shared_buffers ;
┌────────────────┐
│ shared_buffers │
├────────────────┤
│ 16GB │
└────────────────┘
(1 row)
postgres[2003213][1]=# SELECT SUM(pg_prewarm(oid, 'buffer')) FROM pg_class WHERE relfilenode <> 0;
┌────────┐
│ sum │
├────────┤
│ 383341 │
└────────┘
andres@awork3:~$ ps -q 2003213 -eo pid,rss
PID RSS
2003213 3169144
Now postgres memory usage appears to be around 3GB. Even though the individual connection did not actually allocate much additional memory. The added memory usage is is proportional to the amount of shared buffers touched:
postgres[2003213][1]=# SELECT pg_size_pretty(SUM(pg_relation_size(oid))) FROM pg_class WHERE relfilenode <> 0;
┌────────────────┐
│ pg_size_pretty │
├────────────────┤
│ 2995 MB │
└────────────────┘
(1 row)
And even worse than that, if yet another connection also uses those pages, it will also show as having a huge memory usage:
postgres[3244960][1]=# SELECT sum(abalance) FROM pgbench_accounts ;
┌─────┐
│ sum │
├─────┤
│ 0 │
└─────┘
(1 row)
andres@awork3:~/src/postgresql$ ps -q 3244960 -eo pid,rss
PID RSS
3244960 2700372
Of course postgres does not actually use 3+2.7 GiB of memory in this case. Instead, what is happening is that, with huge_pages=off off, ps will attribute the amount of shared memory, including the buffer pool, that a connection has utilized for each connection. Obviously leading to vastly over-estimating memory usage.
Huge pages accidentally kind of save the day
Many CPU micro-architectures normally use a page size of 4KiB, but also optionally can use larger page sizes, most commonly 2MiB.
Depending on operating system, configuration, and the type of applications used such larger pages can be utilized transparently by the operating system, or explicitly by applications. See e.g. Debian wiki page about huge pages for some details.
Repeating the previous experiments with huge_pages=on makes them look a lot better. First, looking at a “new connection”:
andres@awork3:~$ ps -q 3245907 -eo pid,rss
PID RSS
3245907 7612
So, a new connection now appears to use only about ~7MiB. This reduction in memory usage is caused by the page table needing less memory, as it now only needs to contain 1/512th of the previous entries, due to the larger page size.
And more importantly, the test where a lot of the memory is accessed:
postgres[3245843][1]=# ;SELECT SUM(pg_prewarm(oid, 'buffer')) FROM pg_class WHERE relfilenode <> 0;
…
postgres[3245851][1]=# SELECT sum(abalance) FROM pgbench_accounts ;
…
andres@awork3:~$ ps -q 3245907,3245974 -eo pid,rss
PID RSS
3245907 12260
3245974 8936
In contrast to above, these connections now appear to only use 12MiB and 9MiB respectively, where previously they used 3GiB and 2.7GiB. Quite the apparent change ;)
This is due to the way the use of larger pages is implemented in Linux, not because we used orders of magnitude less memory: huge pages used just aren’t shown as part of the RSS column of ps/top.
Getting less unreal
Since Linux 4.5 the /proc/$pid/status file shows the memory usage split into finer sub-categories:
VmRSS size of memory portions. It contains the three following parts (VmRSS = RssAnon + RssFile + RssShmem)
RssAnon size of resident anonymous memory
RssFile size of resident file mappings
RssShmem size of resident shmem memory (includes SysV shm, mapping of tmpfs and shared anonymous mappings)
Looking at these stats with huge_pages=off:
andres@awork3:~$ grep -E '^(Rss|HugetlbPages)' /proc/3247901/status
RssAnon: 2476 kB
RssFile: 5072 kB
RssShmem: 8520 kB
HugetlbPages: 0 kB
postgres[3247901][1]=# SELECT SUM(pg_prewarm(oid, 'buffer')) FROM pg_class WHERE relfilenode <> 0;
andres@awork3:~$ ps -q 3247901 -eo pid,rss
PID RSS
3247901 3167164
andres@awork3:~$ grep -E '^(Rss|HugetlbPages)' /proc/3247901/status
RssAnon: 3148 kB
RssFile: 9212 kB
RssShmem: 3154804 kB
HugetlbPages: 0 kB
RssAnon is the amount of “anonymous” memory, i.e. memory allocations. RssFile are memory mapped files, including the postgres binary itself. And lastly, RssShmem shows the accessed non-huge_pages shared memory.
This nicely shows that most of the high memory usage ps etc show is is due to the shared memory accesses.
And huge_pages=on:
andres@awork3:~$ grep -E '^(Rss|HugetlbPages)' /proc/3248101/status
RssAnon: 2476 kB
RssFile: 4664 kB
RssShmem: 0 kB
HugetlbPages: 778240 kB
postgres[3248101][1]=# SELECT SUM(pg_prewarm(oid, 'buffer')) FROM pg_class WHERE relfilenode <> 0;
andres@awork3:~$ grep -E '^(Rss|HugetlbPages)' /proc/3248101/status
RssAnon: 3136 kB
RssFile: 8756 kB
RssShmem: 0 kB
HugetlbPages: 3846144 kB
Approximating Accuracy
Just adding up the memory usage of the non-shmem values still over-estimates memory usage. There’s two main reasons:
First, it doesn’t actually make sense to include RssFile when measuring a postgres backend’s memory usage - for postgres that overwhelmingly just are the postgres binary and the shared libraries it uses (postgres does not mmap() files). As nearly all of that is shared between all processes in the system, it’s not a per-connection overhead.
Secondly, even just looking at RssAnon over-estimates memory usage. The reason for that is that ps measures the entire memory of the process, even though the majority of a new connection’s overhead is shared between the user connection and the supervisor process. This is because Linux does not copy all memory when fork()ing a new process, instead it uses Copy-on-write to only copy pages when modified.
There is no good way to accurately measure the memory usage of an individual forked process, but since version 4.14 the Linux kernel at least provides an approximation (commit with description) in a process' /proc/[pid]/smaps_rollup file. Pss shows the “the process’ proportional share of this mapping” across all of a process' mappings (Search linux’s documentation page for smaps_rollup and Pss, which unfortunately does not have direct links). For memory shared between processes it will divide the memory usage by the number of processes using a mapping.
postgres[2004042][1]=# SELECT SUM(pg_prewarm(oid, 'buffer')) FROM pg_class WHERE relfilenode <> 0;
┌────────┐
│ sum │
├────────┤
│ 383341 │
└────────┘
(1 row)
postgres[2004042][1]=# SHOW huge_pages ;
┌────────────┐
│ huge_pages │
├────────────┤
│ off │
└────────────┘
(1 row)
andres@awork3:~$ grep ^Pss /proc/2004042/smaps_rollup
Pss: 3113967 kB
Pss_Anon: 2153 kB
Pss_File: 3128 kB
Pss_Shmem: 3108684 kB
Pss_Anon contains memory allocated by the process, Pss_File includes shared libraries etc linked into the process, and Pss_Shmem (if not using huge_pages) the shared memory usage divided across all the processes having touched the corresponding pages.
What makes the proportional values not perfect is that the divisor depends on the number of connections to the server. Here I use pgbench (scale 1000, -S, -M prepared -c 1024) to start a large number of connections:
postgres[2004042][1]=# SELECT count(*) FROM pg_stat_activity ;
┌───────┐
│ count │
├───────┤
│ 1030 │
└───────┘
(1 row)
postgres[2004042][1]=# SELECT pid FROM pg_stat_activity WHERE application_name = 'pgbench' ORDER BY random() LIMIT 1;
┌─────────┐
│ pid │
├─────────┤
│ 3249913 │
└─────────┘
(1 row)
andres@awork3:~$ grep ^Pss /proc/3249913/smaps_rollup
Pss: 4055 kB
Pss_Anon: 1185 kB
Pss_File: 6 kB
Pss_Shmem: 2863 kB
And with huge_pages=on:
andres@awork3:~$ grep ^Pss /proc/2007379/smaps_rollup
Pss: 1179 kB
Pss_Anon: 1173 kB
Pss_File: 6 kB
Pss_Shmem: 0 kB
The Pss values unfortunately do not account for resources only not visible to the application. E.g. the size of the page table is not included. The page table size is also visible in the aforementioned /proc/$pid/status file.
To my knowledge - but I am not certain - VmPTE (the page table size) is completely private for each process, but most other Vm* values, including the stack VmStk are shared in a copy-on-write manner.
Using the above, the overhead due to the page table is, with huge_pages=off:
andres@awork3:~$ grep ^VmPTE /proc/2004042/status
VmPTE: 6480 kB
and with huge_pages=on
Of course there are some additional small overheads in the kernel for each process, but they are small enough not be worth being represented in the status file.
Conclusion
Based on the measurements above we can approximate that a connection running a very simplistic read-only OLTP workload has an overhead of about 7.6MiB with huge_pages=off and of 1.3MiB huge_pages=on by adding Pss_Anon to VmPTE.
Even when leaving room for some “invisible” overhead, a larger amount of data in Postgres' buffer pool, etc, I think this backs my earlier claim that the overhead of a connection is less than 2MiB.
from Hacker News https://ift.tt/34CzmVm
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.