Linux and PostgreSQL in the Multiverse of Connections
summary
Postgres’ process-per-connection model creates concrete Linux and DB costs: shared_buffers mapped with 4KB pages can blow PTE tables into tens of gigabytes (Percona saw ~25GB), so map shared_buffers to huge pages and disable transparent huge pages; kernel 6.6’s EEVDF scheduler reduces latency for bursty short transactions; aim for about 4 active connections per CPU core for OLTP (1:1 for OLAP); work_mem can be consumed ~2× per query with ~1.5× heap left behind; use interleaved NUMA for isolation; Postgres 14/18 lift key MVCC and locking limits.
AI-written summary · about
chapters (14)
- 0:00Music & Introduction
- 0:39Speaker introduction: credativ and Postgres workloads
- 1:31Postgres multi-process architecture overview
- 3:15Inter-process communication and tmpfs
- 4:3164-Bit address space and page-table hierarchy
- 5:48Postmaster forks, shared buffers, and the TLB
- 7:04Why huge pages matter: the PTE blowup
- 8:55NUMA architecture on a Phytium test server
- 11:50NUMA binding policies and interleaved mode
- 14:03Context switching and the EEVDF scheduler change
- 18:49How many connections per CPU core?
- 20:01Per-connection memory and the smaps interface
- 21:42The work_mem mystery and heap allocation
- 24:46MVCC snapshots and fast-path locking limits
related talks
26mRethinking PostgreSQL Performance in the Age of Monster Hardware
52mPostgreSQL Configuration for Humans
45mProper PostgreSQL Parameters to Prevent Poor Performance
57mPostgreSQL When It's Not Your Job
34mTuning PostgreSQL to work even better
48mPractical Memory Tuning for PostgreSQL
41mLinux IO internals for PostgreSQL administrators in 2023
30mPostgreSQL and Linux Kernel interactions