Recently we had a customer who was running PostgreSQL 8.2 on a 32 cores system with 64GB of memory. They were deploying this server in addition to the already running one with 24 total cores and 32GB of memory. PostgreSQL configuration has been adjusted for extra resources, the database has been partitioned roughly in half between the 2 servers and the queries running against both servers were similar.

Suprisingly, when compared to the old server, the extra resources didn't improve the performance. Quite the contrary, the load average and CPU utilization on a new system was much higher during load spikes, while the TPS number plummeted. After performing initial examination of their server (applying our Audit & Tune package) we've decided that 8.2 might itself become an issue. This version of PostgreSQL is outdated and no longer supported by the community. What was suggested is that 8.2 doesn't scale well for 32 cores. How can we verify that hypothesis? Since they were running a relatively modern Linux kernel (2.6.32, supplied with RHEL 6) we were able to take advantage of the interface provided by the taskset utility.

taskset is a small Linux tool that allows setting the CPU affinity of the process, i.e. which cores a given process is scheduled to run on. For instance, if we have total 4 cores available and willing to limit the process ID 12345 to the last two cores (the first two might be handling a lot of I/O) we can do it with the following command, assuming you are the owner of PID 12345 (CPU cores start from 0):

taskset -pc 2,3 12345

Alternatively, the set of CPU cores can be specified by a hexadecimal bitmask, which is convenient if the mask is calculated programatically. The last 2 cores out of 4 total are represented by the 1100 binary mask, hex 0xC. The command above is equivalent to:

taskset -p 0xC 12345

It's possible to start a new task already limited to a given subset of CPU cores, i.e. by applying taskset to a PostgreSQL startup script:

taskset 0xC /etc/init.d/postgresql start

In our case, however, we had to deal with a production server and run taskset against the already running PostgreSQL processes.

To restrict PostgreSQL to specific cores one has to set the affinity for each of the processes the server consists of. The order in which the processes are restricted is important: if we don't handle the postmaster first it may spawn new backend processes that won't get into the list of postgres PIDs and won't be affected by taskset. Normally, one can get the postmaster's PID by getting it from ps. On a system with no PID wraparound and a single PostgreSQL instance the postmaster process has the lowest PID among all of the PostgreSQL processes, so here's the one-liner to get it:

postmaster_pid=$(pidof postgres | xargs -n1 | sort | head -n1)

In our case we have limited PostgreSQL it to the total of 24 cores, from 8 to 31:

taskset -pc 8-31 $postmaster_pid

After the postmaster's affinity is set no new postgres backends can utilize cores outside of those the postmaster process was limited to. Next we can do the same for the existing backend and auxillary processes:

pidof postgres -o $postmaster_pid | xargs -n1 taskset -pc 8-31

Afterwards, one can verify (via top, vmstat or any other utility that shows utilization of individual CPU cores) that postgres processes only utilize those cores they were limited to. In our initial test (not to be performed on a production instance!) we launched a number of postgres backends running a simple infinite loop:

do $$ begin loop end loop;end;$$;

and checked via the top utility that the cores that postgres was not supposed to be using were idle.

Finally, what about the initial hypothesis? Turns out that binding 8.2 to 24 cores didn't produce the expected load relief. Nevertheless, given that data the customer was able to alleviate the load spikes by making changes to the application, and we learned another tool that might be useful when debugging PostgreSQL on high-performance multi-core systems.