Visibility with query cache

 

 Photo by Free-Photos

Pgpool-II provides "query cache" feature which speeds up SELECT to database. It stores query results to specified storage (either shared memory or memcached). If same query (more precisely, SELECT query which has identical text) arrives, Pgpool-II returns the cached result without accessing the database. This is very fast because:

• Query results are fetched from memory, rather than database's (typically on desk) storage
• Does not use CPU or memory on database server

Is there any pitfall?

Whether a SELECT result is fetched from the database or cache is transparent to client. That is, you don't realize the distinction except the response speed. Ok, sounds great. But is there any pitfall to use the query cache? One is, cache hit rates. Since any modifications to underlying table clears the cache, on a system which involves frequent updates are not suitable for the feature. According to the manual, system with lower than 70% cache hit rates is recommended to use the query cache.

Row visibility rules

Another pitfall you may need to care about is row visibility rules. The rule defines how the table rows are visible to other sessions (so you don't need to worry about this if you are the only user in the database). PostgreSQL provides several transaction isolation levels. The row visibility rule will vary depending on the transaction isolation level used in the transaction. For example, with the read committed isolation level (which is the default), other users will not see the new data of rows updated by your transaction until it gets committed.

 

 However with repeatable read isolation level, other users will not see the new data of rows updated by your transaction even after the transaction gets committed. They are visible only after the user starts a new transaction.

Query cache could break the visibility rule

Pgpool-II's query cache does not follow the visibility rule. The cache is created at a transaction gets committed. The difference is, once cache is created, the cache entry can be seen by any transaction. This is fine with read committed isolation level. But with repeatable read isolation level, it breaks the visibility rule.

Suppose query cache feature is used in repeatable read transaction isolation level. This time, after UPDATE, SELECT is issued to create a cache entry.

 

Unlike previous figure, user's transaction will see the new data once your transaction gets committed because the new data will be registered when the transaction gets committed and the data can been seen by anyone. This could happen in not only repeatable read transaction isolation level, but in serializable transaction isolation level.

This is somewhat similar to the phenomenon you may see while using COPY FREEZE. Suppose the transaction isolation level is repeatable read and you are copying data to table using COPY FREEZE in a transaction. Other transaction which has started before your transaction started will see the copied data once your transaction gets committed. This is because "frozen" rows can be seen by anyone.

Conclusion

Pgpool-II's query cache is a useful feature for read intensive busy system. However this may cause certain read anomaly in repeatable read and serializable transaction isolation level. So please use the feature carefully with those transaction isolation level.

Speeding up pgbench using COPY FREEZE

 

Photo by Florian Kurz

 What is pgbench

Pgbench is a simple tool to perform performance tests on PostgreSQL. Since pgbench comes with PostgreSQL distribution and it's easy to use, many users and developers regularly use pgbench.  The original version of pgbench was written by me in 1999 and then it was contributed to PostgreSQL. Since then many people have enhanced the tool.

Loading data takes long time

Pgbench has a feature to load initial data into the benchmark tables (-i or --initialize option). By specifying scaling factor (-s option) users can create data as large as he/she wants. For example scaling factor 100 will create 10 millions rows. When I execute pgbench command with the scaling factor 100, it took 70 seconds on my laptop. You can see how long  each part of the process took:

 done in 70.78 s (drop tables 0.21 s, create tables 0.02 s, client-side generate 12.42 s, vacuum 51.11 s, primary keys 7.02 s).

  As you can see, most of the time was spent in vacuum: 51.11 seconds.

What "pool" means in Pgpool-II?

 

Photo by Prachya Singhto

 Pgpool-II started its history as a connection pooler

The word "pool" in Pgpool-II had traditionally meant "connection pooling".  Actually the software was born as a connection pooling software in 2003. However after 18 years passed Pgpool-II is not only a connection pooling software anymore. Rather, the connection pooling feature is just one of rich features of Pgpool-II.

 More feature has been added to Pgpool-II

 In 2004 native replication feature (statement based replication) was added. In 2010 PostgreSQL's streaming replication can be managed. In 2012 Watchdog, a high availability feature for Pgpool-II itself, has been added.  Moreover automatic failover and load balancing are possible.

Now "pool" means "pool of PostgreSQL"

With all these feature now Pgpool-II is effectively a PostgreSQL cluster management software. So I decided to redefine the meaning of "pool" to be "pool of PostgreSQL servers". In this direction I have updated the intro chapter of PostgreSQL manual for the next release (Pgpool-II 4.3).

Pgpool-II manages a pool of PostgreSQL servers to achieve some features that are not available with single PostgreSQL installation.

An HTML compiled version of the chapter can be viewed here.

I hope this change gives people more appropriate view of this software.

Timeouts in Pgpool-II connections

 

There are several timeout parameters in Pgpool-II. In this blog I will explain those parameters used for connection management.  Below is a figure describing such a few parameters. Pgpool-II spawns child process (the number of process is defined by num_init_children parameter) at the startup time. Each child process takes care of client connections and backend connections to the PostgreSQL database.

First of all there are two connections. One is between clients and Pgpool-II. The other is between Pgpool-II and PostgreSQL. So you should recognize which parameter affects which connection.

Child_lifetime

This parameter controls the lifetime of Pgpool-II process. Since connections to/from clients and PostgreSQL are in the process, all connections will disappear when the process exits. A benefit to enable the parameter is to free out memories used by the Pgpool-II process. For example, Pgpool-II needs to allocate memory to hold message data sent from client. That could be large if table row is wide. Of course the memory used by the message is freed after the communication is done. But it does not actually free out memory from process space. So occasional process exit and restart may be good for applications which could handle large messages. Note that when Pgpool-II process exits, corresponding PostgreSQL also exits.

The timer for the parameter starts when no connections from clients exist. For a busy system the timer may not be triggered because next client connection arrives as soon as former client exits. In this case you may want to use child_max_connections parameter.

If you want to know how old the process is, you can use pcp_proc_info command or show pool_processes SQL command to examine the "start time" field.

Child_max_connections

If the number of connection count from clients exceeds this parameter, then the Pgpool-II process exits and restarts. Even if your system is too busy to trigger the child_lifetime, this parameter should work because it only counts the number of connections from clients.

Unfortunately currently there's no metrics to know how many connections from client have been made to a Pgpool-II process.

Client_idle_limit

If a client does not send next query for a period of this parameter, the connection to the client is disconnected. Note that the timer starts after previous query receives the result from PostgreSQL. Therefore bellow query does not trigger the disconnection even if client_idle_limit = 10 (seconds) until the client receives the result.

SELECT pg_sleep(60);

Connection_life_time

Unlike the parameters above this controls the lifetime of cached connections to PostgreSQL backend. Note that Pgpool-II keeps up to max_pool connections to PostgreSQL backend. The timer is checked against those connections to PostgreSQL backend when the Pgpool-II process returns to the idle state (waiting for connections from clients). So if a client occupies the connection for long time, the cached connections remain longer than the period specified by the parameter.

If you want to know how old the connection caches are, you can use pcp_proc_info command or show pool_processes SQL command to examine the "Creation time" field.

Conclusion

You can control the connection lifetime parameters to save resource of the system. For a systems which are not so busy, you can leave those parameters as default. However if your system getting busy you might want to tweak them. Hope this blog entry is useful for busy systems.

Using IVM in a docker container

Real time, fast update of materialized views

 In  this blog entry I have introduced the ongoing project: Incremental View Maintenance (IVM), which allows to real time, fast update of materialized views. Since the bolg, IVM now can handle outer joins and self joins. The out come of the project is in public in a form of set of patches against git master branch of PostgreSQL. Also a GitHub repository for this project is in public as well.

Docker image for IVM

So skilled PostgreSQL developers can test IVM today. However it's not that easy for people who do not regularly compile and build PostgreSQL and IVM from source code. Fortunately one of IVM developers "Yugo Nagata" comes up and starts to provide complete docker image of IVM. I am going to demonstrate how to use it step by step.

# Pull docker image  

docker pull yugonagata/postgresql-ivm 

 

 # Run docker container using port mapping 15432:5432 

docker run -d --name pgsql-ivm -e POSTGRES_PASSWORD=postgres -p 15432:5432 yugonagata/postgresql-ivm

 

 # Run psql to connect to PostgreSQL to create database named "test"

docker run -it --rm --link pgsql-ivm yugonagata/postgresql-ivm psql -h pgsql-ivm -U postgres 

# create database test; 

\q

# Run psql to run pgbench
docker run -it --rm --link pgsql-ivm yugonagata/postgresql-ivm pgbench -i -h pgsql-ivm -U postgres test

# Run psql to try IVM
docker run -it --rm --link pgsql-ivm yugonagata/postgresql-ivm psql -h pgsql-ivm -U postgres test

# Create a materialized view "mv1" for query "select count(*) from pgbench_accounts.

# Note that "pgbench_accounts" was created by pgbench
test=# create incremental materialized view mv1 as select count(*) from pgbench_accounts;
SELECT 1

# select the result from the view
test=# select * from mv1;
 count  
--------
 100000
(1 row)

 # Start a transaction

 test=# begin;
BEGIN

# delete 1 row from pgbench_accounts table
test=*# delete from pgbench_accounts where aid = 1;
DELETE 1

# Check to see if mv1 reflects the deletion
test=*# select * from mv1;
 count
-------
 99999
(1 row)

# Abort the transaction
test=*# abort;
ROLLBACK

# The deletion was cancelled in mv1 as well
test=# select * from mv1;
 count  
--------
 100000
(1 row)

Summary

IVM has been continuously developed. Now it can be easily tested in a docker container. Please try it out!

 

Fixing language in Pgpool-II 4.2

 The Challenge

 Upcoming Pgpool-II uses more appropriate language for some technical terms. This has been already done in PostgreSQL. Pgpool-II developers think it's a good thing and decided to follow the way. Actual work has been done by Umar Hayat. Challenge is we have to fix not only documentations but configuration parameters and programs. As a result, his patch was quite large: 159 files have been changed, over 2,000 lines were modified.

 Avoiding Some Words

We avoid some words including "master", "slave", "black", "white". We use instead "main", "leader", "primary", "replica" "read", "write" and so on.

Configuration Parameters are Changed

Here is the list of changed configuration parameters.

black_function_list → write_function_list

white_function_list → read_function_list

black_query_pattern → primary_routing_query_pattern

black_memcache_table_list → cache_unsafe_table_list

white_memcache_table_list → cache_safe_table_list

ALWAYS_MASTER flag → ALWAYS_PRIMARY flag

follow_master_command → follow_primary_command

Native replication mode

Pgpool-II has a mode which allows replication by sending identical SQL statement to PostgreSQL servers (thus no streaming replication configuration is neccessary). This mode is called "native replication mode". In this mode "master" (which is the first PostgreSQL node) and "slave" are used to represent the roll of each PostgreSQL server. Pgpool-II 4.2 will use "main" and "replica" instead.

Watchdog

Watchdog is the feature to avoid Pgpool-II's single point of failure. When deploying multiple Pgpool-II servers, they talk each other so that they can elect new leader server when former leader server goes down. We historically called the leader server as "master" and rest of the server as "standby". Now Pgpool-II uses "leader" instead of "master".

Conclusion

The language cleanup may make users migration process from older version of Pgpool-II a little bit harder. But this should have been done someday, and Pgpool-II developers are proud of doing that in this release.

See preview of 4.2 release note for more details (we expect to have Pgpool-II 4.2 release in October 2020).

New statistics data in Pgpool-II

 

Upcoming Pgpool-II 4.2 will provide new statistics data such as number of INSERT/DELETE/UPDATE etc.  to you. In this blog I will show you how it will look like.

Existing statistics

Before jumping into the new feature, let's take a look at existing statistics data. PostgreSQL already gives you tuple access data like number of tuples inserted in a database (see pg_stat_database manual). Before Pgpoool-II 4.1 or before gives number of SELECT sent to PostgreSQL in "show pool_nodes" command.

How is like the new statistics?

A new command called "show backend_stats" provides the statistics data like number of NSERT, UPDATE and DELETE issued since Pgpool-II started up. So it's not like PostgreSQL's pg_stat_database in that PostgreSQL provides number of tuples accessed, while Pgpool-II provides number SQL commands issued.  If you want number of tuples based statistics you can always access PostgresQL's statistics data.

 Here is an example of the new statistics data:

test=# show pool_backend_stats;
 node_id | hostname | port  | status |  role   | select_cnt | insert_cnt | update_cnt | delete_cnt | ddl_cnt | other_cnt | panic_cnt | fatal_cnt | error_cnt 
---------+----------+-------+--------+---------+------------+------------+------------+------------+---------+-----------+-----------+-----------+-----------
 0       | /tmp     | 11002 | up     | primary | 12         | 10         | 30         | 0          | 2       | 30        | 0         | 0         | 1
 1       | /tmp     | 11003 | up     | standby | 12         | 0          | 0          | 0          | 0       | 23        | 0         | 0         | 1
(2 rows) 

select_cnt, insert_cnt, update_cnt, delete_cnt, ddl_cnt, other_cnt are the numbers of SQL command issued since Pgpool-II started up. What is "other_cnt"? Well, it is a count of: CHECKPOINT/DEALLOCATE/DISCARD/EXECUTE/EXPLAIN/LISTEN/LOAD/LOCK/NOTIFY/PREPARE/SET/SHOW/Transaction commands/UNLISTEN. If an SQL does not belong to SELECT/INSERT/UPDATE/DELETE/other, then it is counted as DDL.

Please note that these counts are counted up even if an SQL command fails or roll backed.

panic_cnt, fatal_cnt and error_cnt are number of errors returned from PostgreSQL.  They are counts of PANIC, FATAL or ERROR. These error statistics is handy because PostgreSQL does not provide error statistics.

Summary

The "show pool_backend_stats" gives you at a glance view of whole cluster statistics on number SQL commands issued. Moreover, error counts, which does not exists in PostgreSQL could be useful for those who are interested in database or application failures.

Snapshot Isolation Mode

Pgpool-II 4.2 is under development

Pgpool-II developers have been working hard for upcoming Pgpool-II 4.2 release, which is supposed to be released around this September or October.

Snapshot isolation mode is coming

For the new release I have just added new clustering mode called "Snapshot isolation mode", which would be not only interesting for Pgpool-II users but PostgreSQL users and I think I decided to write about it in this blog.

Existing clustering mode

Until Pgpool-II 4.1, we have following clustering mode:

• Streaming replication mode. This is most widely used at this point. PostgreSQL's streaming replication is used for the replication task in this mode.
• Native replication mode. Until streaming replication is available, this was the most common setting. In this mode Pgpool-II issues same data modifying statements (DML/DDL) to all the PostgreSQL servers. As a result same data is replicated in all PostgreSQL servers.
• Slony mode. In this mode replication is done by Slony-I instead of streaming replication. This had been popular until streaming replication came up.
• Raw mode. No replication is done in this mode. Rarely used.

The new "Snapshot isolation mode" is pretty close to the existing native replication mode. It does not use the streaming replication at all. Replication is done by sending DML/DDL statements to all the PostgreSQL servers.  If so, what's the difference?

Atomic visibility

Before explaining that I would like to talk about the background a little bit.

PostgreSQL developers has been discussing about technology which can scale to handle large data. One of such a technical directions is extending FDW (Foreign Data Wrapper). It sends query to another PostgreSQL server to replicate or shard data. The idea is similar to the native replication mode in Pgpool-II because it also sends DML/DDL to servers to replicate data.

As for replicating data, they look good so far. However, there is a problem when multiple sessions try to access data at the same time.

Suppose we have 2 sessions: session 1 continuously executes UPDATE t1 SET i = i +1; session 2 continuously executes INSERT INTO log SELECT * FROM t1. Table t1 has only 1 row and it's initial value is 0 (actually each UPDATE and INSERT are executed in an explicit transaction).

So after session 1 and session 2 end, table "log" should have exactly same sequential numbers like "0, 1, 2, 3...." on both PostgreSQL servers. But see the example session below:

Session 1 executed UPDATE at t1 on servers1, while on server 2 UPDATE was executed on t2. Session 2 executed INSERT at t3 with value 1. Unfortunately on server2 INSERT was executed at t3, and t1 was not updated yet thus the value used was 0.

So after the sessions end, we will see different sequences of numbers in log table on server1 and server2. If we do not have atomic visibility this kind of data inconsistency could happen. Atomic visibility guarantees that value can be seen on server1 and server2 are same as long as they are executed in a same session. In the example above, data logged into log table would be either 0 (t1 has not been updated/committed yet) or 1 (t1 has been already updated/committed).

Snapshot isolation mode guarantees atomic visibility

The upcoming snapshot isolation mode is different from existing native replication mode in that it guarantees atomic visibility. So we don't need to worry about such that data inconsistency in the example above. In fact we have new regression to test the new snapshot isolation mode by using essentially same SQL used in the example above. Please note that in order to use snapshot isolation mode, the transaction isolation mode must be "REPEATABLE READ". (Postgres FDW also runs foreign transactions in this mode).

How to achieve atomic visibility?

PostgreSQL developers already recognize the necessity of atomic visibility. Postgres-XC, a fork of PostgreSQL, invented Global Transaction Manager to achieve atomic visibility. Some PostgreSQL developers are proposing CSN (Commit Sequence Number) approach. However these approch needs major surgery in PostgreSQL code.

Pgpool-II uses completely different approach, which is called "Pangea" proposed in an academic paper. With Pangea, no modification is necessary with PostgreSQL. Even you can use older versions of PostgreSQL like 8.0. So you can enjoy snapshot isolation mode of Pgpool-II today with your favorite version of PostgreSQL.

In Pagea the idea is called Global Snapshot Isolation, which extends snapshot isolation of local server to across servers. That's the reason why we call the new clustering mode as "Snapshot Isolation mode".

How Pangea works?

 With Pangea each first command in a transaction needs to wait if other transactions are trying to commit. If there are no commit is ongoing, the command will get snapshot which defines the visibility of the transaction. Since local transactions on each server acquire snapshots while there are no committing transactions, they can be regarded as consistent. In the example above, the INSERT command in session 2 will be forced to wait until t4, and at that time session 1 commits. Thus each INSERT command in session 2 will see "1" in table t1. Same thing can be said to the commit. Commit must wait if there's ongoing snapshot acquisition.

Summary

Pgpool-II 4.1 will come with new clustering mode called "Snapshot Isolation Mode" which guarantees the atomic visibility and is not provided in current standard PostgreSQL. Unlike other techniques to implement the atomic visibility, Pgpool-II does not require any modification to PostgreSQL. Also it can be used even with older version of PostgreSQL. Stay tuned.

Automatically updating materialized views

Materialized views are convenient and efficient way to retrieve information from database. Unlike ordinary views, materialized views save the query result and provide faster access to the data. For example, user can create a simple materialized view containing the number of rows in a table:

 CREATE MATERIALIZED VIEW mv1 AS SELECT count(*) FROM pgbench_accounts;

Obtaining the number of rows using the materialized view mv1 is much faster than  directly accessing number of rows in pgbench_accounts.

test=# SELECT * from v1;
 count 
--------
 100000
(1 row)

Time: 0.810 ms
test=# SELECT count(*) FROM pgbench_accounts;
 count 
--------
 100000
(1 row)

Time: 13.945 ms

However if you delete a row from pgbench_accounts, the number of rows in mv1 is still unchanged. To reflect the change of the base table (in this case pgbench_accounts) , you need to recreate or refresh (this actually recreate the contents of materialize views from scratch), which may take long time.

To overcome the problem, SRA OSS is proposing to add a new feature to existing materialized view  "incremental materialized view maintenance". This works like this.

• add new syntax to allow incrementally update the materialized view when it is created.

• install triggers automatically to be attached to the base table(s).

• when UPDATE/DELETE/INSERT applies on the base tables, the trigger calculate the difference to the rows in the materialized view and update the rows.

These should update the materialized view in a real time manner. The price is the over head of trigger invocation. So frequently updating tables are not best suite for the incremental materialized view maintenance. Here are demonstrations how to use the incrementally updating materialized view.

First create incrementally updating materialized view.

CREATE INCREMENTAL MATERIALIZED view mv2 AS SELECT count(*) FROM pgbench_accounts;

The view initially says there are 100,000 rows.

SELECT * FROM mv2;
 count 
--------
 100000
(1 row)

Delete a row from pgbench_accounts. Now the number of rows is 99999.

DELETE FROM pgbench_accounts WHERE aid = 10;
DELETE 1
SELECT count(*) FROM pgbench_accounts;
 count
-------
 99999
(1 row)

Make sure that mv2 reports the same number of rows.

SELECT * FROM mv2;
 count
-------
 99999
(1 row)

It works!

This is a very simple materialized view. More complex queries, for example inner joins also work.

CREATE INCREMENTAL MATERIALIZED VIEW mv3 AS SELECT a.aid, b.bid, t.tid FROM pgbench_accounts a INNER JOIN pgbench_branches b ON (a.bid = b.bid ) INNER JOIN pgbench_tellers t ON (b.bid = t.bid) WHERE a.aid BETWEEN 1 AND 5;
SELECT 50

SELECT * FROM mv3 LIMIT 5;
 aid | bid | tid
-----+-----+-----
   3 |   1 |  10
   2 |   1 |   1
   2 |   1 |   5
   2 |   1 |   4
   5 |   1 |   3
 (5 rows)

DELETE FROM pgbench_accounts WHERE aid = 2;
DELETE 1

SELECT * FROM mv3 LIMIT 5;
 aid | bid | tid
-----+-----+-----
   3 |   1 |  10
   5 |   1 |   3
   1 |   1 |   1
   5 |   1 |   8
   4 |   1 |   3
(5 rows)

Implementing other types of queries such as outer joins, self joins are in our plan, possibly toward PostgreSQL 13.

Here are some pointers to information regarding our incremental materialized view maintenance project:

• Project wiki page

• Discussion in pgsql-hacers

• Github

When primary server is far away from standby server

Sometimes we want to create a cluster configuration in which the primary server is physically located far away from standby servers. For example, the primary server is located in an AWS region A, while the standby server is located in an AWS region B. This type of configuration is suited for a DR (Disaster Recovery) or a company which has a branch in different countries. In the figure below, a company's head quarter is located in region A,  and people in the HQ office access the primary server database through Pgpool-II.

People in a branch office located in region B access standby database in mostly read only manner. In a few occasion they need to write the database but they understand that it needs to access HQ database which would be slow. Problem for them is, even if they just do a read access to the database, Pgpool-II needs to access system catalog in the primary server to obtain meta info of the tables involved in their query.

From Pgpool-II 4.1,  which is under development, user could eliminate the slow system catalog access in from region B to region A by setting relcache_query_target = load_balance_node and backend weight of primary server to 0 in Pgpool-II running in region B.

Now the system catalog access is redirected to the standby server, rather than to the primary server and the access speed should faster.

Please note that , however, there may be replication delay and that could cause a trouble if the target table is pretty new because the system catalog in the standby server may not the have the new table info. So this configuration is best suitable for a system the table creation is rare, or they are created while users do not access the system.

By the way, Pgpool-II developers define the target date for release 4.1 of Pgpool-II in this October. Please stay tuned.

Statement level load balancing

In the previous article I wrote about one of the new features of upcoming Pgpool-II 4.1.
This time I would like to introduce "statement level load balancing" feature of 4.1.

Pgpool-II can distribute read queries among PostgreSQL backend nodes. This allows to design a scale out cluster using PostgreSQL. The particular database node used for distributing read query is determined at the session level: when a client connects to Pgpool-II. This is so called "session level load balancing".

 For example, if a client connects to Pgpool-II and the load balance node is node 1 (we assume that this is a streaming replication standby node), then any read query will be sent to the load balance node (in this case node1, the standby node). Different session may choose master  (primary) as the load balance node. The distribution ratio of the load balancing node selection in different sessions is determined by "backend weight" parameter in the Pgpool-II configuration file (usually named "pgpool.conf"), typically "backend_weight0" or "backend_weight1", corresponding to node 0 and node 1 respectively.

This is good as long as clients connects to Pgpool-II, issue some queries, and disconnect, since next time a client connects to Pgpool-II, different load balance node may be chosen according to the backend weight parameters.

However, if your client already has a connection pooling feature, this way (session level load balancing) might be a problem, since the selection of load balance node is performed only once when the connection pooling from client to Pgpool-II is created.

The statement level load balancing feature is created to solve the problem. Unlike the session level load balancing, the load balancing node is determined when a new query is issued. The new parameter for this is "statement_level_load_balance". If this is set to on, the feature is enabled (the parameter can be changed by reloading the pgpool.conf).

At first "select_cnt" is 0, which means no SELECTs were issued.

test=# show pool_nodes;

 node_id | hostname | port  | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | last_status_change 
---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------+---------------------
 0       | /tmp     | 11002 | up     | 0.500000  | primary | 0          | true              | 0                 | 2019-04-02 15:36:58
 1       | /tmp     | 11003 | up     | 0.500000  | standby | 0          | false             | 0                 | 2019-04-02 15:36:58
(2 rows)

Let's issue a SELECT.

test=# select 1;
?column? 
----------
        1
(1 row)
test=# show pool_nodes;
 node_id | hostname | port  | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | last_status_change  
---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------+---------------------
 0       | /tmp     | 11002 | up     | 0.500000  | primary | 1          | true              | 0                 | 2019-04-02 15:36:58
 1       | /tmp     | 11003 | up     | 0.500000  | standby | 0          | false             | 0                 | 2019-04-02 15:36:58
(2 rows)

Now the select_cnt of node 0 is 1, which means the SELECT was sent to node 0. Also please note that "load_balance_node" colum of node 0 is "true", which means node 0 is chosen as the load balance node in the last query.

Ok, let's issue another SELECT:

test=# select 2;
 ?column? 
----------
        2
(1 row)

test=# show pool_nodes;
 node_id | hostname | port  | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | last_status_change  
---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------+---------------------
 0       | /tmp     | 11002 | up     | 0.500000  | primary | 1          | false             | 0                 | 2019-04-02 15:36:58
 1       | /tmp     | 11003 | up     | 0.500000  | standby | 1          | true              | 0                 | 2019-04-02 15:36:58
(2 rows)

Now the load_balance_node is changed to node 1, and the select_cnt of node 1 becomes 1. This is how the statement level load balancing works.