Memory Tuning for workloads in PostgreSQL Flexible Server on Azure

[Gennadyk]

Queries against large sets of table rows may need a lot of server memory to sort, join, aggregate, or sub select data. Azure Database for PostgreSQL – Flexible Server exposes number of important parameters on portal parameters page that mirror important global settings exposed by PostgreSQL via postgres.conf configuration file.  

You can navigate to Flexible Server global parameters page via Azure Portal in Azure Portal -> Azure Database for PosgreSQL Servers -> Your Flexible Server -> Server Parameters.  But before we discuss some of the memory related parameters, lets first take a look at general Postgres memory architecture. 

Figure 1. PostgreSQL Flexible Server parameters page in Azure Portal. 

PostgreSQL Memory Architecture

Memory usage in Postgres can be classified in two main categories.

First is known as Local Memory. This is memory allocated by each Postgres backend process for itself.  In PostgreSQL, each backend process allocates local memory for query processing; each area is divided into sub-areas whose sizes are either fixed or variable. These sub areas are listed below:

• Work_mem. 

  PostgreSQL execution engine, aka Executor,  uses this area for sorting tuples by ORDER BY and DISTINCT operations, and for joining tables by merge-join and hash-join operations.

• Maintenance_work_mem. 

  This memory are is used  by maintenance operations , such as VACUUM, REINDEX, etc.

• Temp_buffers.  

  Executor uses this area for temporary table storage

Second is Shared Memory , which is memory used by all Postgres backend processes. This area is allocated by a PostgreSQL server when it starts up. This area is also divided into several sub-areas, such as:

• Shared Buffer Pool. PostgreSQL loads pages within tables and indexes from persistent storage to a shared buffer pool, and then operates on them directly.
• WAL Buffer.  

  PostgreSQL supports the WAL (Write ahead log) mechanism to ensure that no data is lost after a server failure. WAL data is really a transaction log in PostgreSQL and WAL buffer is a buffering area of the WAL data before writing it to a persistent storage.

• Commit Log.  

  Commit Log(CLOG) keeps the states of all transactions (e.g., in_progress, committed, aborted) for Concurrency Control (CC) mechanism.

Figure 2. PostgreSQL memory architecture

Tuning Azure PostgreSQL Flexible Server memory server parameters

Based on above here are some important server parameters that impact performance you may wish to know about for memory management in Azure Database for PostgreSQL – Flexible Server:

• Shared Buffers. The PostgreSQL shared_buffers is the major component of the shared memory used by the server. It is a large block allocated for caching blocks when reading from and writing to the database. This can be set via parameter that is called same – shared buffers. The default value for this parameter is 128 MB, which is fairly low.
  
  On Azure Database for PostgreSQL -Flexible Server the shared_buffers parameter setting changes depending on the selected SKU (SKU determines the memory available). General Purpose servers have 2GB shared_buffers for 2 vCores; Memory Optimized servers have 4GB shared_buffers for 2 vCores. The shared_buffers setting scales linearly (approximately) as vCores increase in a tier. Therefore, you will like not have to tune this value manually in most of the cases. Since this parameter is static you may wish to restart the service for the value to take effect if you do decide to manually change it.
  
  

        Figure 3. Shared Buffers parameter on Flexible Server parameters page in Azure Portal

•  Work_mem. 

  This parameter specifies the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files. If your workload has few queries with a lot of complex sorting and you have a lot of available memory, increasing this parameter may allow Postgres to do larger scans in-memory, which will be faster than spilling to disk. However, if there is not enough work_mem allocated, Postgres will create temporary disk files to handle the operations, slowing down performance.

  
  

  If you have a number of short-running queries that run very frequently and perform simple lookups and joins, then maintaining a lower work_mem is beneficial. In this case, increasing work_mem may not provide significant benefits. Be cautious, however, as a single complex query may involve multiple sort and hash operations running concurrently. Each of these operations will use as much memory as the work_mem value allows before it starts writing to disk-based temporary files. Therefore, on a relatively busy system, total memory usage can be many times the individual work_mem parameter. When you consume more memory than is available on your machine, you can start to see out-of-memory errors within your Postgres logs, or in worse cases, the OOM killer may start randomly killing running processes to free up memory.

  
  Also, work_mem is allocated by powers of two, so round to the nearest binary step.  The default value of work_mem = 4MB. You can set work_mem value on multiple levels including at the server level via parameters page in Azure Portal, at the current session level and even on individual transaction level (to fine tune memory usage for specific queries without affecting the users and connections that perform other queries).  

  
  The example below shows how to allow specific query use up to 128 MB physical memory to perform sorting and then resets work_mem value for the current session to the current default value.

  SET work_mem = ‘128MB’;
  SELECT * FROM products ORDER BY LOWER(product_name);
  RESET work_mem;

  
  You can use following workflow to set work_mem to value other than default:
  
  
  1.  Check your max_connections parameter on parameters page for Azure Database for PostgreSQL – Flexible Server.  You can tune max_connections on Postgres Flexible Server, where it can be set to 5,000 maximum connections. 
     
     
  2.  You can use the formula below to calculate an initial work_mem value for the database server - Total RAM * 0.25 / max_connections. 
     
     
  3. Due to large global memory impact we would recommend changing work_mem on transaction or session level for queries that can benefit from higher value before contemplating server level change.  
     
     To understand whether you should tune  work_mem for a particular query you can execute this query with EXPLAIN PLAN.  If the parameter Sort Method: external merge  Disk: xxxxkB is in the output, it means you should consider enlarging  the work_mem setting to increase performance as you may be seeing disk spillover.  Next, test with setting up work_mem value for transaction higher than those <XXXX>kB that you see in EXPLAIN ANALYZE output. As a rule of thumb, you can round up that value to the nearest megabyte. Finally, rerun EXPLAIN ANALYZE to check if ...external merge Disk... message has disappeared. 
     
     
• Effective_cache_size. 

  This parameter estimates the amount of memory available for disk caching and serves as a hint for the PostgreSQL query planner. In Azure Flexible Server, the effective_cache_size value is aligned with the SKU and typically requires minimal tuning. It seldom significantly affects performance unless set to very low values.

We are hoping that you find this blog article helpful and are always interested how you plan to use Flexible Server offering to drive performance to your critical applications.  Additional information on topics discussed above can be found in following documents:

• PostgreSQL docs – Resource Consumption
• Azure Database for PostgreSQL – Flexible Server Parameters

We’re always eager to get your feedback, so please reach out via email to Ask Azure DB for PostgreSQL.