Summary of PostgreSQL tuning effort

PostgreSQL: Tuning for Linux on Z

PostgreSQL is one of the popular databases on Linux on Z. It’s available in all major distributions. As with every database tuning is important. Marc Beyerle, a colleague of mine, wrote an in depth article about his tuning experiences: “PostgreSQL: Experiences and tuning recommendations on Linux on IBM Z“.

For the standard workload used, the overall improvement is a 45% increase in throughput. This does not include the Simultaneous Multi Threading (SMT) benefit as the base system was already exploiting this. Here are the most effective measures for this specific workload:

  1. Adapt shared buffers and cache usage. This is accomplished by setting the shared_buffers parameter to ~1/4 of the available memory. And setting further the effective_cache_size parameter to ~3/4 of the available memory. Be aware that the second one is pretty aggressive. For other workloads leaving some more room for unexpected memory needs might be preferable. Nevertheless the main message is that PostgreSQL needs the Linux cache for good performance.
  2. Tune read-ahead. For other databases like Oracle DB the recommendation is to disable read-ahead. But as PostgreSQL really utilizes the Linux cache this is beneficial here.
  3. Enable and use huge pages. This is a standard recommendation for all type of database workloads. And it also helps for this workload and PostgreSQL. As with Oracle DB this has two effects: First there are less address translations and second as PostgreSQL is also process based there are quite some memory savings when many users are using the database
  4. Tune Linux scheduler. This one is really special and mainly worked because of the shared L3 cache in the z14 used. If you are on a system without a large shared L3 cache, this one might backfire.

Marc agreed to be contacted – marc.beyerle  a_t de.ibm.com . There is also a recording of a Live Virtual Class available.

The official PostgreSQL wiki also has in depth explanations and recommendations for performance tuning.

Leave a Comment

Your email address will not be published. Required fields are marked *