Re: Postgres vs. intel ccNUMA on Linux

James Robinson wrote:
> Any tips / conventional wisdom regarding running postgres on
> large-ish memory ccNUMA intel machines, such as a 32G dual-quad-core,
> showing two NUMA nodes of 16G each? I expect each postgres backend's
> non-shared memory usage to remain nice and reasonably sized, hopefully
> staying within the confines of its processor's local memory region,
> but how will accesses to shared memory and / or buffer cache play out?
> Do people tune their backends via 'numactl' ?

My gut feel here is that the odds this particular area will turn into
your bottleneck is so slim that worrying about it in advance is
premature optimization. If you somehow end up in the unexpected
situation where processor time that might be altered via such fine
control is your bottleneck, as opposed to disks, buffer cache
contention, the ProcArray contention Robert mentioned, WAL contention,
or something else like that--all things you can't segment usefully
here--well maybe at that point I'd start chasing after numactl. As for
how likely that is, all I can say is I've never gotten there before
finding a much more obvious bottleneck first.

However, I recently wrote a little utility to test memory speeds as
increasing numbers of clients do things on a system, and it may provide
you some insight into how your system responds as different numbers of
them do things: http://github.com/gregs1104/stream-scaling

I've gotten results submitted to me where you can see memory speeds
fluctuate on servers where threads bounce between processors and their
associated memory, stuff that goes away if you then lock the test
program to specific cores. If you want to discuss results from trying
that on your system and how that might impact real-world server
behavior, I'd recommend posting about that to the pgsql-performance list
rather than this one. pgsql-hackers is more focused on code-level
issues with PostgreSQL. There really aren't any of those in the area
you're asking about, as the database is blind to what the OS is doing
underneath of it here.

> Furthermore, if one had more than one database being served by the
> machine, would it be advisable to do this via multiple clusters
> instead of a single cluster, tweaking the processor affinity of each
> postmaster accordingly, trying to ensure each cluster's shared memory
> segments and buffer cache pools remain local for the resulting backends?

If you have a database that basically fits in memory, that might
actually work. Note however that the typical useful tuning for
PostgreSQL puts more cache into the operating system side of things than
what's dedicated to the database, and that may end up mixed across
"banks" as it were. I'd still place my money on running into another
limitation first, but the idea is much more sound . What I would try
doing here is running the SELECT-only version of pgbench against both
clusters at once, and see if you really can get more total oomph out of
the system than a single cluster of twice the size. The minute disks
start entering the picture though, you're likely to end up back to where
processor/memory affinity is the least of your concerns.

--
Greg Smith, 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
Author, "PostgreSQL 9.0 High Performance" Pre-ordering at:
https://www.packtpub.com/postgresql-9-0-high-performance/book