This is a loose translation of a post by one of the strongest Postgres developers, Andres Freund. In addition to the fact that the developer is strong, the article is also quite interesting and reveals the details of how the Linux OS works.
It is quite common to hear claims that post-Grid connections use too much memory. This is often mentioned when comparing the process model of handling client connections with another model where each connection is served in a separate thread.
As for me, there is a lot to discuss here. Additionally, you can make several improvements and reduce memory usage.
I think this concern about memory overhead is due to one common reason, which is that the simplest way to measure memory consumption through utilities like top and ps is pretty deceiving.
Indeed, it is especially difficult to measure how the memory used increases with each new connection.
In this post, I will talk about Postgres running on Linux, because it is in this direction that I have the most experience.
And before continuing, I want to emphasize that with an accurate and accurate measurement, one connection has an overhead of less than 2MiB (see the conclusions at the end of the post).
First glance
, , ( ). (huge pages), . . , Postgres:
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
16MiB.
!?! ,
. , pgprewarm, (shared buffers):
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
3GB. , , . , :
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)
, , :
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
, Postgres 3GB 2.7GB . , huge_pages=off, ps (shred - ) , . .
- 4KiB, , 2MiB.
huge_pages=on, . , " ":
andres@awork3:~$ ps -q 3245907 -eo pid,rss PID RSS 3245907 7612
, 7MiB. (page table) , - , 512 (4KiB * 512 = 2MiB).
:
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
, 12MiB 9MiB , 3GiB 2.7GiB.
.
, Linux , , : RSS ps top.
4.5, /proc/$pid/status :
VmRSS . (VmRSS = RssAnon + RssFile + RssShmem)
RssAnon .
RssFile .
RssShmem ( SysV shm, tmpfs )
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 "" , .. . RssFile , postgres. RssShmem .
ps - - .
, 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
, . :
, RssFile - (Postgres mmap() - ). .
, RssAnon . ps , postgres ( postmaster). Linux ( fork()), Copy-on-Write , .
, . , 4.14 ( ) /proc/$pid/smaps_rollup . Pss " " ( smaps_rollups Pss ). , .
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 , Pss_File , Pss_Shmem ( ) .
, . pgbench (scale 1000, -S -M prepared -c 1024) :
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
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
Pss , . , . `/proc/$pid/status`.
, , VmPTE ( ) , Vm* , VmStk copy-on-write.
, huge_pages=off:
andres@awork3:~$ grep ^VmPTE /proc/2004042/status VmPTE: 6480 kB
huge_pages=on:
VmPTE: 132 kB
- , , .
Based on these measurements, we can imagine that a process performing a fairly simple read-only OLTP load has an overhead of about 7.6MiB with huge_pages = off and about 1.3MiB with huge_pages = on including Pss_Anon in VmPTE.
Even if we imagine that there is some kind of "invisible" overhead, and a large amount of data in the buffer, etc., I think we will return to my earlier statement that the connection overhead is less than 2MiB.
Supplement from the translator. Postgres 14 introduces a new pg_backend_memory_contexts view that shows detailed memory utilization by the current process from the perspective of Postgres itself.