2

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:

pgbench 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.

migrated from stackoverflow.com Jul 27 '13 at 13:30

This question came from our site for professional and enthusiast programmers.

  • Did you figure out why this was happening? I'm on a 6c/12t machine and even I'm having a hard time understanding the behaviour of pgbench as I vary various configuration params. – Saurabh Nanda Jan 22 at 18:30
3

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.

  • Thanks for your suggestions Craig. I'll read about the NUMA stuff and make the other changes to see what the deltas are. – user1351763 Jul 24 '13 at 12:30
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.

  • Yeah, I started on Robert Hass' pages. I was hoping to do something similar, although he compares 9.1 to 9.2 on a 64 core machine. I've GOT to believe 9.0 can't be that far behind on a bigger box. Thanks for the info. – user1351763 Jul 23 '13 at 19:55
  • Oh, I hadn't spotted you were on 9.0. That's not going to scale so far. The kind of hardware you're using just wasn't available when it was being built and tested. Why spend all that money on an expensive server and stick out of date software on it? – Richard Huxton Jul 23 '13 at 23:31
  • This hardware and software predate me on this project. I'm trying to establish benchmarks before upgrading. Based on what I've read though, 9.2 is well beyond 9.0 wrt performance. We do a lot of nearest neighbor GIS searches and the addition on KNN indexes alone are enough for us to upgrade. I just want to make sure that what I'm experiencing is or is not expected with 9.0. I find that performance curve kind of odd. – user1351763 Jul 24 '13 at 12:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy