performance of the server There are number of configuration parameters that affect the performance of Pgpool-II. In this chapter we present how to tune them. Pgpool-II does not consume too much resource. However there are minimum requirements for resource. In this section we are going to explain one by one. There are two types of memory usage in Pgpool-II: shared memory and process private memory. The former is allocated at the startup of Pgpool-II main server process and will not be freed until whole Pgpool-II servers shut down. The latter is allocated within each Pgpool-II child process and will be freed at the end of the process. Here is a formula to calculate the shared memory requirement. Shared memory requirement (in mega bytes) = 10 + * * 0.02 For example if you have num_init_children = 32 (the default) and max_pool = 4 (the default), then you will need 10 + 32 * 4 * 0.02 = 12.6 MB. If you plan to use in memory query cache (see for more details) in the shared memory, you will need more RAM for it. See and for required RAM size. Note that, however, in Pgpool-II 4.1 or after, even if the in memory query cache is not enabled, it consumes additional 128MB of shared memory, if is enabled (it is enabled by default). Here is a formula to calculate the process memory requirement. Process memory requirement in total (in mega bytes) = * 0.16 For example if you have num_init_children = 32 (the default), you will need 5.2MB. Please note that this is minimum memory requirement upon Pgpool-II child process starting up. Once the process runs, it will consume more memory depending on the message packet sizes and other factors. It is recommended to measure the amount of memory actually used by the process before starting production use. Pgpool-II does not consume much disk space. Also it does not require high speed disk because disk I/O traffic caused by Pgpool-II is small. However, if you plan to emit much logs, of course you need disk space for them. As the number of client connections accepted is growing, the number of Pgpool-II child process which can accept new connections from client is decreasing and finally reaches to 0. In this situation new clients need to wait until a child process becomes free. Under heavy load, it could be possible that the queue length of waiting clients is getting longer and longer and finally hits the system's limit (you might see "535 times the listen queue of a socket overflowed" error"). In this case you need to increase the queue limit. There are several ways to deal with this problem. The obvious way to deal with the problem is increasing the number of child process. This can be done by tweaking . However increasing child process requires more CPU and memory resource. Also you have to be very careful about max_connections parameter of PostgreSQL because once the number of child process is greater than max_connections, PostgreSQL refuses to accept new connections, and failover will be triggered. Another drawback of increasing num_init_children is, so called "thundering herd problem". When new connection request comes in, the kernel wake up any sleeping child process to issue accept() system call. This triggers fight of process to get the socket and could give heavy load to the system. To mitigate the problem, you could set serialize_accept to on so that there's only one process to grab the accepting socket. However notice that the performance may be dropped when the number of concurrent clients is small. In Pgpool-II 4.4 or later, it is possible to use for more efficient management. By setting process-management-mode to dynamic, when the number of concurrent clients is small, the number of child process of Pgpool-II can be decreased thus we can save the resource consumption. On the other hand when the number of concurrent clients gets larger, the number of child process increases so that it can respond to the more demand of connections. However, notice that the time for connection establishment could be increasing because new process need to be started to have more child process. See also for understanding process-management-mode. Another solution would be increasing the connection request queue. This could be done by increasing . However, none of above solutions guarantees that the connection accepting the queue would not be filled up. If a client connection request arrives quicker than the rate of processing queries, the queue will be filled in someday. For example, if there are some heavy queries that take long time, it could easily trigger the problem. The solution is setting so that overflowed connection requests are rejected as PostgreSQL already does. This gives visible errors to applications ("Sorry max_connections already") and force them retrying. So the solution should only be used when you cannot foresee the upper limit of system load. If there are multiple PostgreSQL nodes and Pgpool-II operates in streaming replication mode, logical replication mode, slony mode or replication mode (for those running mode see for more details), it is possible to distribute read queries among those database nodes to get more throughput since each database nodes processes smaller number of queries. To enable the feature you need to turn on . At this point vast majority of systems use streaming replication mode, so from now on we focus on the mode. By default load balance mode is "session level" which means the node read queries are sent is determined when a client connects to Pgpool-II. For example, if we have node 0 and node 1, one of the node is selected randomly each time new session is created. In the long term, the possibility which node is chosen will be getting closer to the ratio specified by 0 and 1. If those two values are equal, the chance each node is chosen will be even. On the other hand, if is set to on, the load balance node is determined at the time each query starts. This is useful in case that application has its own connection pooling which keeps on connecting to Pgpool-II and the load balance node will not be changed once the application starts. Another use case is a batch application. It issues tremendous number of queries but there's only 1 session. With statement level load balancing it can utilize multiple servers. In OLAP environment sometimes it is desirable to have a large read-only database for specific purpose. By creating such a database is possible by creating a replica database using streaming replication. In this case it is possible to redirect read queries to the database in two ways: specifying database names(s) or specifying application name(s). For former, use . For latter use . Pgpool-II allows to cache read query results for later use. This will bring huge benefit for a type of applications which issue same read queries many times. If there are two queries and the query strings (parameter for prepared statements if any) are identical, two queries are regarded as "same". For the first time the query is sent, Pgpool-II saves the query result, and use it for the second query without asking anything to PostgreSQL. This technique is explained in . When a table is modified, query results against the table could be changed. To avoid inconsistency, Pgpool-II discards query cache data when corresponding table is modified. So frequently updated database will not be suitable to use in memory query caching. You can check if your database is suitable to use query caching or not, you could use . If query cache hit ration is lower than 70%, probably you want to avoid using the query cache. Except in raw mode (see ) or is set to off, sometimes Pgpool-II needs to ask PostgreSQL to get meta information, such as whether a table is a temporary one or not. To get those information, Pgpool-II sends queries primary PostgreSQL which could be up to as many as 10 queries (in 4.1 or after, the number of queries has been decreased, it is not zero, however). To reduce the overhead, Pgpool-II maintains "relation cache". Next time same table is included in a query, Pgpool-II extracts the information from the cache. There are some parameters to configure the relation cache. See , , , for more details. The relation cache basically lives in process private memory, which is bound to a process. So even if a relation cache is created to for a table, in different process the relation cache might not be created yet. After all, until a relation cache entry is created in all process, queries continue to sent to PostgreSQL. Pgpool-II 4.1 overcomes the issue by creating relation cache in shared memory. If a session creates a relation cache entry in the shared memory, other sessions will get the cache result by looking at the shared relation cache. See configuration parameter section for more details. This feature is pretty effective and we recommend this feature be enabled. This section introduces some other performance considerations. If is large, it is possible that many Pgpool-II process are woke up and heavy context switching happens. This leads to high system load and hurt the overall system performance. This problem is called "the thundering herd problem". Enabling could solve the problem. Please note that for smaller , might make the system performance worse. Please take a look at the guidance in section. To create a disaster recovery setting, it is possible to deploy a Pgpool-II plus PostgreSQL primary server, and another Pgpool-II plus standby PostgreSQL server in a geographically distant place. Clients close to the standby server send read only queries to the Pgpool-II, being close to the standby server. However, since standby Pgpool-II sends internal queries to system catalog of primary PostgreSQL server, query performance may be getting worse. To avoid the problem, it is possible to use so that such queries are sent to the standby. See for more details.