Pgpool-II Configuration Parameters - enable_shared_relcache

-

person holding black and white electronic device

In the previous post, I explained how to control the client connections by using reserved_connections parameter. In this post I would like to introduce how to use shared relation cache to improve performance.

Shared relation cache is added in Pgpool-II 4.1. Before explaining this feature, let's have a look at what relation cache of Pgpool-II is.

What is relation cache?

If Pgpool-II receives a client query, Pgpool-II will parse the query and extract the table name, then access PostgreSQL system catalogs to get the relation information. Pgpool-II obtains the following information from PostgreSQL system catalogs:

  • whether a table included in the query is a temporary table or not
  • whether a table included in the query is an unlogged table or not
  • whether a function included in the query is "immutable" or not (if a function is used in the query and query cache mode is enabled)

If the table included in the query is a temporary table or an unlogged table, then Pgpool-II must send the query to the primary PostgreSQL.

To reuse the information obtained from system catalogs, Pgpool-II stores the information in local cache. If the same object (e.g. table, function) appears in the subsequent queries, Pgpool-II fetches information from the local cache instead of accessing system catalogs.

Each child process stores the local relation cache in process private memory and other child processes can not access the process local relation cache. If a different process is assigned to a client request, the process has to access PostgreSQL system catalogs, even if same table information is stored in other child process's local relation cache. If a large value is set to num_init_children, Pgpool-II accesses PostgreSQL system catalog as many times as the value of num_init_children, and it may increase the overhead.

Shared relation cache feature introduced in Pgpool-II 4.1 can resolve this problem.

Shared relation cache

Pgpool-II can create relation cache in shared memory by enabling enable_shared_relcache parameter. If a child process has created a relation cache entry in the shared memory, other child processes can retrieve the result by accessing the shared memory. This feature is pretty effective and it is recommended to enable this feature. 

Configure the following parameter to enable shared relation cache. Default is on.

enable_shared_relcache = on

Let me explain how shared relation cache works in Pgpool-II.

  1. If the table/function information is not found in the local relation cache, then Pgpool-II checks the shared relation cache.
  2. If the table/function information is found in the shared relation cache, Pgpool-II copies the information to local cache.
  3. If it is not found in the shared relation cache, Pgpool-II accesses PostgreSQL system catalogs and stores the result in both local relation cache and shared relation cache.

Comments

Post a Comment

Popular posts from this blog

Installing Pgpool-II on Debian/Ubuntu

-
First of all, many thanks to the PostgreSQL Global Development Group (PGDG) for creating Pgpool-II packages for Debian and Ubuntu. This post shows you step by step how to install Pgpool-II using official APT repository provided by PGDG. Prerequisites This blog assumes you have already installed two PostgreSQL 14 servers and setup streaming replication between the PostgreSQL servers.   For testing purpose,we set trust authentication in pg_hba.conf . In a production environment, Please follow the PostgreSQL and Pgpool-II documentation to configure proper authentication settings.  Install Pgpool-II To use the PostgreSQL apt repository, follow the steps below. Create the repository configuration file: # echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list Import the repository signing key: # wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - Update the package lists: # ...
Read more

Authentication in Pgpool-II

-
Image
    Pgpool-II is a proxy software for PostgreSQL cluster and it supports several authentication methods. How does Pgpool-II perform user authentication? In this post, I will introduce the authentication methods which Pgpool-II supports and how Pgpool-II authentication mechanism works. Authentication methods in Pgpool-II Pgpool-II supports several authentication methods: Trust Authentication MD5 Password Authentication   scram-sha-256 Authentication (Pgpool-II 4.0 or later) Certificate Authentication   PAM Authentication LDAP Authentication (from the next major release 4.2)  Starting with Pgpool-II 4.0, Pgpool-II supports scram-sha-256 authentication. scram-sha-256 authentication method is strongly recommended because it is the most secure password-based authentication method. How does Pgpool-II authentication mechanism work? Since Pgpool-II is a PostgreSQL proxy that works between clients and PostgreSQL servers, the authentication comprises two steps: Authen...
Read more

Query Load Balancing in Pgpool-II

-
Image
Pgpool-II is a PostgreSQL cluster management tool. The major features of Pgpool-II are: Connection pooling Query load balancing Automated failover Watchdog (High availability of Pgpool-II) Replication In memory query cache In this post, I will describe the query load balancing mechanism in Pgpool-II and the relevant configuration parameters. Why use query load balancing? Query load balancing can distribute database server workloads across multiple PostgreSQL servers. Nowadays, most database systems use multiple replicated database servers to achieve high availability for PostgreSQL. Pgpool-II takes the advantage of the replication feature in order to distribute the workloads across multiple PostgreSQL servers. If there is a PostgreSQL cluster with multiple PostgreSQL servers, Pgpool-II is able to distribute READ queries across those PostgreSQL servers. The benefit of READ queries load balancing is, improve the system's throughput reduce the load on each PostgreSQL server optimize...
Read more