Read Query Load Balancing
| pgpool-II 4.1.3 Documentation | |||
|---|---|---|---|
| Prev | Up | Chapter 7. Performance Considerations | Next |
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 Section 3.3.2 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 load_balance_mode .
At this point vast majority of systems use streaming replication mode, so from now on we focus on the mode.
7.3.1. Session Level Load Balancing vs. Statement Level Load Balancing
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 backend_weight 0 and backend_weight 1. If those two values are equal, the chance each node is chosen will be even.
On the other hand, if statement_level_load_balance 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.
7.3.2. Creating Specific Purpose Database Node
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 database_redirect_preference_list . For latter use app_name_redirect_preference_list .