Scaling beyond 16 concurrent connetions

Question

I've just adopted a Postgres 9.0 database running on RHEL 6.1 on a rather beefy 256 core, 2TB system with ok drive (Hardware RAID 50).

I've started benchmarking the system using pgbench so I can get an idea of what this thing can do. The application spends most of its time reading data, so I used the simple 'select' bgbench tests with the following parameters against a data set initialized with a scaling factor of 300:

pgbench -S -j 8 -T 60

I recorded the TPS reported from pgbench using n+8 (n = 0 to 16) clients for 16 samples. I produced the following chart from the results:

My question is, why the peak at 16 concurrent connections? I ran pgbench from the same host and looked at IO, memory, CPU utilization, all the usual suspects and found nothing that stood out as being a cause. For postgresql.conf I have the following settings:

max_connections = 1024
shared_buffers = 16G
temp_buffers = 2G
work_mem =128M
maint_work_mem = 1G

%iowait is ~3.8% w/ 16 connections, 8.2% w/ 32, and 8.9% w/ 48.

I recently saw that PG 9.2 has a new 'feature' (http://www.postgresql.org/about/featurematrix/detail/218/) labeled as 'Multi-core scalability for read-only workloads' but this seems like a red herring. I can't help but think it a configuration issue.

TIA.

Answer 1

Before spending much more time on it, upgrade to 9.2. New versions aren't just released for kicks, they tend to contain significant performance, tuning, and scalability improvements. Quite a lot of work has been done on read- and write-scaling recently.

Lower max_connections. Even unused entries in max_connections have a cost. On your machine you'll probably want to max out somewhere around 300. If you need more concurrent connections, put a pgbouncer in front of PostgreSQL.

Watch out for the known issues with NUMA and transparent huge pages in the Linux kernel; there are some pretty pathalogical interactions with PostgreSQL. I don't have details immediately to hand, but it's been discussed several times on the mailing lists. Some of the people on the team I work with do a lot of work with big NUMA systems and find Linux's handling of huge pages and page migration to be areas that require tuning for acceptable Pg performance.

On 9.0 your shared_buffers might be a bit big; you may actually see better results by lowering it. You'll need to play around with this as it varies a bit based on kernel and hardware.

Finally, make sure you run pgbench from a separate host, or it'll create contention for the same resources that it's trying to benchmark.

Set effective_cache_size to around 1TB. It should be higher, but IIRC there are some limitations in the code for handling multi-terabyte quantities of RAM, at least in 9.

If that RAID 50 is comprised of spinning rust it's going to limit performance unless your DB fits in RAM - which it probably does at scale factor of 300, but might not on your real DB.

Answer 2

I've seen scaling charts out beyond 16 cores, e.g. this from Robert Hass or this on serverfault.

Your machine is well beyond anything I've had the luck to work with, but I do know pgbench can be fiddly to produce decent testing without introducing its own bottlenecks.

I'd recommend checking the mailing list archives on postgresql.org and subscribing to the performance mailing list. There are people there who can give you detailed advice on scaling for large platforms.