PostgreSQL at low level: Stay curious

https://learning.oreilly.com/videos/oreilly-velocity-conference/9781492050742/9781492050742-video328387

  • Very dense (and quick) talk with a lot of useful information.
  • More about general techniques to profile postgres-like systems, and not too much postgres-specific stuff.
  • BPF in particular is very interesting; you can effectively attach custom programs to any function of any running program (including the kernel itself), and receive data at runtime.
    • With this in the Linux observability toolkit, is FreeBSD really better at this?
  • It’s relatively easy to perform debugging at the database level, but the abstractions really layer up and make this more difficult:
  • In general, you can use a combination of {reading the source,perf,strace,BPF} to figure out any performance issue in production, but you have to want to do this and not give up because the idea of observability at this level seems too difficult.
  • Postgres uses Linux buffered IO, which can cause thrash when memory contention is high (not familiar enough with the details to have a strong intuition about why this is).

Look Up

  • Spinlocks
  • Lock holder preemption / PLE
  • Linux buffered IO + postgres’ usage of it
  • HugePages + its impact on the TLB cache
  • patroni / postgres-operator
  • /dev/shm
  • dtrace / strace
  • VDSO
  • Intel MDS (iirc this is mitigation against spectre/meltdown)
  • TLB statistics via perf
  • BPF / bcc / trace (python)
Edit