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.

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.

Even more fine load balancing control with Pgpool-II 4.0

While back go, I posted an article titled "More load balancing fine control" to explain a new feature of upcoming Pgpool-II 4.0. Today I would like talk about yet another new feature of load balancing in Pgpool-II.

Pgpool-II is already able to control read query load balancing in several granularity:
(See the document for more details).

•  Whether enable load balancing or not (load_balance_mode)
• By database (database_redirect_preference_list)
• By application name (app_name_redirect_preference_list)
• By functions used in the query (white_function_list, black_function_list)
• Statement level (/*NO LOAD BALANCE*/ comment)

 The last method allows the statement level control on load balancing but it needs to rewrite a query which is often impossible if you are using commercial software.

A new configuration parameter called "black_query_pattern_list" allows you to disable load balancing for queries specified by the parameter. The parameter takes a regular expression string. If a query matched with the expression, load balancing is disabled for the query: the query is sent to the primary (master) PostgreSQL server.

Here are some examples.

We have:
black_query_pattern_list = 'SELECT \* FROM t1\;;'
in pgpool.conf. Note that some special characters must be qualified by using '\' (back slash) characters.

We have no SELECTs issued to both primary and standby.

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          | false             | 0                 | 2018-06-15 11:57:25
 1       | /tmp     | 11003 | up     | 0.500000  | standby | 0          | true              | 0                 | 2018-06-15 11:57:25
(2 rows)

If following query is issued, then the "select_cnt" column of standby should be incremented since standby is the load balance node (notice the "role" column").

test=# SELECT * FROM t1;

But because of the effect of  black_query_pattern_list, the SELECT is redirected to primary.

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                 | 2018-06-15 11:57:25
 1       | /tmp     | 11003 | up     | 0.500000  | standby | 0          | true              | 0                 | 2018-06-15 11:57:25
(2 rows)


However, "SELECT * FROM t1 WHERE i = 0" will be sent to standby since the expression specied in black_query_pattern_list does not match with the query.

test=# select * from t1 where i = 1;
 i
---
(0 rows)

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                 | 2018-06-15 11:57:25
 1       | /tmp     | 11003 | up     | 0.500000  | standby | 1          | true              | 0                 | 2018-06-15 11:57:25
(2 rows)

If you want to match with any query using table t2, you want to specify something like:

  .* t2;;.* t2 .*;

The first part ".* t2;" matches any query without any qualification (example: "SELECT * FROM t2"), while latter matches any query having space after t2 (example: "SELECT * FROM t2 WHERE i = 1).

Please note that Pgpool-II does not do any syntax analysis on the query here. Please be careful when specifying a regular expression. For instance, "SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2)" will not match with any of the regular expression pattern above. So the query will be sent to standby. This may or may not follow your expectations.

By the way, you may already noticed the extra column "last_status_change" of "show pool_nodes" command. This indicates the times stamp when any of "status" or "role" has been changed. This is useful when you want to see log lines when failover happend last time.

More load balancing fine control

Pgpool-II is known as it provides load balancing across multiple PostgreSQL servers for read queries.

Consider a cluster consisting of Pgpool-II and two PostgreSQL servers configured with streaming replication. One is a primary server, the other is a standby server. Also the load balancing weight is 0:1, which means read queries will be basically sent to the standby server.

In an explicit transaction if a read query is sent from a client, Pgpool-II sends it to the standby server (as long as replication delay is not too much). Next a write query is coming. Of course it is sent to the primary server. Interesting thing happens if a read query is sent. Unlike previous read query, it is sent to the primary to avoid the replication delay.

Pgpool-II 4.0 will have a new configuration parameter called "disable_load_balance_on_write" to give more fine control of the behavior above.

If disable_load_balance_on_write = 'transaction', the behavior is exactly same as above. This is the default.

If disable_load_balance_on_write = 'off', Pgpool-II no longer takes account of the write query and feels free to load balance read queries as much as possible.

 This choice is good if you want to take maximum benefit of load balancing.

The variant of 'tranacton' is, 'trans_transaction'. Unlike 'transaction', the effect of disable load balancing after a write query continues even if the transaction closes and a next transaction begins.

So 'trans_transaction' is good for those who want data consistency than load balancing. Please note that a ready query issued between explicit transactions are not affected by the parameter.

Finally there's 'always'. If the mode is specified and once write query is sent, any read query regardless it's in an explicit transaction or not, will not be load balanced and sent to the primary server until the session ends.

In summary, the degree of protecting data consistency is in the order of always, trans_transaction, transaction and off. In contrast, the degree of performance is in the order of off, transaction, trans_transaction and always. If you are not sure which one is the best choice for you, I recommend you to start with 'transaction' because this is on a good balance between data consistency and performance.

Detecting "false" primary server of PostgreSQL

One of upcoming Pgpool-II 4.0's interesting features will be: "false" primary server detection in streaming replication environment.

Suppose we have 1 primary server and two standby servers connection the primary server.

test=# show pool_nodes;
 node_id | hostname | port  | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay
---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------
 0       | /tmp     | 11002 | up     | 0.000000  | primary | 1          | false             | 0
 1       | /tmp     | 11003 | up     | 0.000000  | standby | 0          | false             | 0
 2       | /tmp     | 11004 | up     | 1.000000  | standby | 0          | true              | 0
(3 rows)

What will happen if the node 2 standby server is promoted to primary?

test=# show pool_nodes;
 node_id | hostname | port  | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay
---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------
 0       | /tmp     | 11002 | up     | 0.000000  | primary | 1          | false             | 0
 1       | /tmp     | 11003 | up     | 0.000000  | standby | 0          | false             | 0
 2       | /tmp     | 11004 | up     | 1.000000  | standby | 0          | true              | 0
(3 rows)

As you can see nothing has been changed as far as show pool_nodes command goes.
But: actually node 2 is not the standby connected to the primary anymore. So if large updates are sent to the primary, the node 2 is far behind the primary server since data is not replicated to the node any more.

t-ishii@localhost: pgbench -i -p 11000 test
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
creating tables...
100000 of 100000 tuples (100%) done (elapsed 0.19 s, remaining 0.00 s)
vacuum...
set primary keys...
done.

 test=# show pool_nodes;
 node_id | hostname | port  | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay
---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------
 0       | /tmp     | 11002 | up     | 0.000000  | primary | 1          | false             | 0
 1       | /tmp     | 11003 | up     | 0.000000  | standby | 0          | false             | 0
 2       | /tmp     | 11004 | up     | 1.000000  | standby | 0          | true              | 13100296
(3 rows)

How can we detect the situation and fix it?

Pgpool-II 4.0 will help you. It will have a new parameter called "detach_false_primary". If the parameter is enabled, Pgpool-II will automatically detect the situation and detach the node 2 because it's a"false" primary.

 test=# show pool_nodes;
 node_id | hostname | port  | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay
---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------
 0       | /tmp     | 11002 | up     | 0.000000  | primary | 1          | false             | 0
 1       | /tmp     | 11003 | up     | 0.000000  | standby | 0          | false             | 0
 2       | /tmp     | 11004 | down   | 1.000000  | standby | 0          | true              | 13100296
(3 rows)

Note that there's a limitation in the feature. For example, if  there are 4 nodes: primary 1 connects to standby 1, while primary 2 connect to standby 2. There's no reliable way to decide which node is the "true" primary. In this case the false primary detection does not work.

In the figure bellow explains what Pgpool-II 4.0 can do and cannot.
You can read more details of the feature from the manual.

Celebrating 15th anniversary of Pgpool and next version number

It will be 15 years in coming July since Pgpool was born. At that time Pgpool was just a simple connection pooler program, no replication, no load balancing. Many contributors have been working hard on enhancing Pgpool since then.

Pgpool-II developers have decided last week that upcoming Pgpool-II's  major version will be 4.0, rather than 3.8 to celebrate the anniversary. Of course that's not the only reason for the version number. As usual Pgpool-II 4.0 will have exciting new features including enhanced authentication mechanism and more. Stay tuned.

Importing PostgreSQL 9.6's SQL parser

In almost every releases of Pgpool-II, we import the latest version of PostgreSQL's SQL parser (more precisely, the raw parser). This time, our new Pgpool-II developer faced with an interesting problem while importing PostgreSQL 9.6's parser.

In PostgreSQL, the SQL parser is written in bion, a general-purpose parser generator. The particular file including the SQL grammar rules is named "gram.y". gram.y used to include "scan.l", which is a lexical scanner written in flex.

 In reality, gram.y is translated into a C source file by bison, then compiled. Same thing can be said to scan.l, which is translated by flex though.

So the main part of SQL parser  source file was single big file consisted of gram.y and scan.l.

From PostgreSQL 9.6, however, PostgreSQL developers decided to keep gram.y and flex.l separated.

Build backend/parser/scan.l and interfaces/ecpg/preproc/pgc.l standalone.

This gives enough confusion to the developer in charge of the work and took some time before realize the change. I would say it's a fun part of the task when we work on an OSS project:-)  However I cannot stop saying that it would be nice if the SQL parser is exported as a separate library so that we do not need this kind of work in every releases of Pgpool-II.

Pgpool-II new minor versions are out

pgpool-II new minor versions are out. Pgpool-II is a connection pooling/clustering tool for PostgreSQL ONLY. This time we released:

• 3.3.1 (the latest stable version)
• 3.2.6
• 3.1.9
• 3.0.13

These versions fix manu bugs of previous releases of pgpool-II. Please visit  http://www.pgpool.net/mediawiki/index.php/Main_Page#pgpool-II_3.3.1.2C_3.2.6.2C_3.1.9.2C_3.0.13_officially_released_.282013.2F09.2F06.29 to find more info.

Pgpool-II 3.3 released

Finaly we released new major release: pgpool-II 3.3.

This version focuses on enhancing "watchdog" module of pgpool-II.  It is in chage of avoiding the single point of failure caused by pgpool-II itself. Because pgpool-II is a proxy program for PostgreSQL, dying of pgpool-II immediately causes a service down of entire database system. Traditionally we deal with the problem by using two pgpool-II instances and "pgpool-HA", a heart beat script. Watchdog is a replacement for pgpool-HA. Users do not need to install pgpool-HA anymore. Just install two (or more) pgpool-II instances and turn on watchdog. Watchdog appeared in pgpool-II 3.2 and now it is far enhanced in 3.3.

Enhancements for watchdog includes:

• New monitoring method of watchdog lifecheck using heartbeat signal
• Interlocking of failover/failback script
• Secure watchdog communication
• PCP command for retrieving the watchdog status

  Other enhancements in 3.3 include:

• import PostgreSQL 9.2 raw parser
• New pgpool_setup tool
• Support for using CREATE EXTENSION to install pgpool specific extensions
• regression test suit 
• new simple installer

Please visit  http://www.pgpool.net/mediawiki/index.php/Main_Page#pgpool-II_3.3_and_pgpoolAdmin_3.3_officially_released_.282013.2F07 to find more info.

pgpool-II new minor versions are out!

pgpool-II new minor versions are out. pgpool-II is a connection pooling/clustering tool for PostgreSQL ONLY. This time we released:

• 3.2.5 (the latest stable version)
• 3.1.8
• 3.0.12

These versions are compatible with each previous versions. As usual, many bug fixes are applied. Here are some important fixes from my point of view:

•  Fix error when pgpool_regclass is not installed. The query used in pool_has_pgpool_regclass() fails if pgpool_regclass does not exist. The bug was introduced in 3.2.4.
•  Fix do_query() to not hang when PostgreSQL returns an error. The typical symptom is "I see SELECT is keep on running according to pg_stat_activity".
• Fix possible deadlock during failover with watchdog enabled.
• Fix bug with do_query which causes hung in extended protocol. 
• Fix possible failure of query cache invalidation for DML in transaction.
• Fix to register pgpool_regclass in pg_catalog schema. This is necessary to deal with clients which restricts schema search path to pg_catalog only. Postgres_fdw is such a client.
• Fix a segmentation fault that occurs when on memory query cache enabled and the query is issued in extended query mode and the result is too large.
• Fix a segmentation fault of a child process that occurs when a startup packet has no PostgreSQL user information.
• Fix to verify the backend node number in pcp_recovery_node. When an invalid number is used, null value is passed as an arguments of recovery script, and this causes a malfunction. In especially, rsync may delete unrelated files in basebackup scripts.

See release notes  for more details. Go to here to download source codes.

pgpool-II and postgres_fdw

PostgreSQL 9.3 beta1 is out. One of pgpool users tried pgpool with postgres_fdw and found interesting thing. So I looked into this to know what actually happened here.

To test postgres_fdw, I created one pgpool installation with replication mode and two PostgreSQL clusters using pgpool_setup. One is running on port 11001. I executed following commands on 11001 PostgreSQL to create a foreign table called "pgpool".

CREATE EXTENSION postgres_fdw;
CREATE SERVER pgpool FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'test', port '11002');
CREATE USER MAPPING FOR PUBLIC SERVER pgpool;
CREATE FOREIGN TABLE fr1(i INTEGER) SERVER pgpool;

On port 11002 pgpool is running. On port 11000 PostgreSQL is running. To create a real table corresponding to the foreign table "fr1", I executed "CREATE fr1 TABLE(i INTEGER);". Below is the setup I explained above.

The point here is, the foreign table "fr1" accesses the real table "fr1" via pgpool-II. So from the pgpool's point of view, the foreign table is looks like an ordinary client. If I set "client_idle_limit" to, say 5, I saw an interesting thing. The client_idle_limit is a feature to kick out idle clients. If client sends no query within client_idle_limit seconds, the connection from the client to pgpool is forced to disconnected. Here is the session log when client_idle_limit is set to 5.

$ psql -p 11001 test
-- access foreign table fr1
test=# select * from fr1;
 i
---
 1
(1 row)
-- after 6 seconds passed
test=# select * from fr1;
ERROR:  unknown error
CONTEXT:  Remote SQL command: START TRANSACTION ISOLATION LEVEL REPEATABLE READ
test=# select * from fr1;
ERROR:  unknown error
CONTEXT:  Remote SQL command: START TRANSACTION ISOLATION LEVEL REPEATABLE READ
test=# select * from fr1;
ERROR:  unknown error
CONTEXT:  Remote SQL command: START TRANSACTION ISOLATION LEVEL REPEATABLE READ
test=# \q

 This continues until I quit the session and restart a session. What happens here? you can know it from the pgpool log(my comments are written in red color):
Several commands are issued from postgres_fdw:
2013-06-22 21:01:55 LOG:   pid 11209: DB node id: 0 backend pid: 13109 statement: SET search_path = pg_catalog
2013-06-22 21:01:55 LOG:   pid 11209: DB node id: 0 backend pid: 13109 statement: SET timezone = 'UTC'
2013-06-22 21:01:55 LOG:   pid 11209: DB node id: 0 backend pid: 13109 statement: SET datestyle = ISO
2013-06-22 21:01:55 LOG:   pid 11209: DB node id: 0 backend pid: 13109 statement: SET intervalstyle = postgres
2013-06-22 21:01:55 LOG:   pid 11209: DB node id: 0 backend pid: 13109 statement: SET extra_float_digits = 3
2013-06-22 21:01:55 LOG:   pid 11209: DB node id: 0 backend pid: 13109 statement: START TRANSACTION ISOLATION LEVEL REPEATABLE READ
2013-06-22 21:01:55 LOG:   pid 11209: DB node id: 0 backend pid: 13109 statement: Parse: DECLARE c1 CURSOR FOR
SELECT i FROM public.fr1
Looks like postgres_fdw uses extended protocol
2013-06-22 21:01:55 LOG:   pid 11209: DB node id: 0 backend pid: 13109 statement: B message
2013-06-22 21:01:55 LOG:   pid 11209: DB node id: 0 backend pid: 13109 statement: D message
2013-06-22 21:01:55 LOG:   pid 11209: DB node id: 0 backend pid: 13109 statement: Execute: DECLARE c1 CURSOR FOR
SELECT i FROM public.fr1
2013-06-22 21:01:55 LOG:   pid 11209: DB node id: 0 backend pid: 13109 statement: FETCH 100 FROM c1
2013-06-22 21:01:55 LOG:   pid 11209: DB node id: 0 backend pid: 13109 statement: CLOSE c1
2013-06-22 21:01:55 LOG:   pid 11209: DB node id: 0 backend pid: 13109 statement: COMMIT TRANSACTION
 After 6 seconds passed, I executed "SELECT * from fr1" again
2013-06-22 21:02:01 LOG:   pid 11209: pool_process_query: child connection forced to terminate due to client_idle_limit(5) reached
Pgpool told that client_idle_limit expires
2013-06-22 21:02:01 LOG:   pid 11209: DB node id: 0 backend pid: 13109 statement:  DISCARD ALL

How to avoid the error?

Because there's no way for pgpool-II to  know the connection from client is from postgres_fdw, the only workaround is disabling client_idle_limit.

Creating regression test suite for pgpool-II

Having a regression test suite has been a long standing TODO item. Now that we have pgpool_setup, I started to think about making it real. Today I think I finally made it.

The idea behind it is

• create required pgpool cluster (pgpool + PostgreSQL instances) by using pgpool_setup
• each test case is a simple shell script
• each script returns 0 or 1 according to the test result

Sounds too simple? Yes, I admit. However it works as expected.

$ sh regress.sh
testing 001.load_balance...ok.
testing 002.native_replication...ok.
testing 003.failover...ok.
testing 050.bug58...ok.
testing 051.bug60...ok.
testing 052.do_query...ok.
testing 053.insert_lock_hangs...ok.
out of 7 ok:7 failed:0

For example, "001.load_balance" checks whether load balancing is working against 2 node native replication or streaming replication cluster by using this simple script:

#! /bin/sh
#-------------------------------------------------------------------
# test script for load balancing.
#
source $TESTLIBS
TESTDIR=testdir

for mode in s r
do
    rm -fr $TESTDIR
    mkdir $TESTDIR
    cd $TESTDIR

# create test environment
    echo -n "creating test environment..."
    sh $PGPOOL_SETUP -m $mode -n 2 || exit 1
    echo "done."

    source ./bashrc.ports

    echo "backend_weight0 = 0" >> etc/pgpool.conf
    echo "backend_weight1 = 1" >> etc/pgpool.conf
    echo "black_function_list = 'f1'" >> etc/pgpool.conf

    ./startall

    export PGPORT=$PGPOOL_PORT

    wait_for_pgpool_startup

    psql test <<EOF
CREATE TABLE t1(i INTEGER);
CREATE FUNCTION f1(INTEGER) returns INTEGER AS 'SELECT \$1' LANGUAGE SQL;
SELECT * FROM t1;        -- this load balances
SELECT f1(1);        -- this does not load balance
EOF

# check if simle load balance worked
    fgrep "SELECT * FROM t1;" log/pgpool.log |grep "DB node id: 1">/dev/null 2>&1
    if [ $? != 0 ];then
    # expected result not found
        ./shutdownall
        exit 1
    fi

# check if black function list worked
    fgrep "SELECT f1(1);" log/pgpool.log |grep "DB node id: 0">/dev/null 2>&1
    if [ $? != 0 ];then
    # expected result not found
        ./shutdownall
        exit 1
    fi

    echo "white_function_list = 'f1'" >> etc/pgpool.conf
    echo "black_function_list = ''" >> etc/pgpool.conf

    ./pgpool_reload

    psql test <<EOF
SELECT f1(1);        -- this does load balance
EOF

# check if white function list worked
    fgrep "SELECT f1(1);" log/pgpool.log |grep "DB node id: 1">/dev/null 2>&1
    if [ $? != 0 ];then
    # expected result not found
        ./shutdownall
        exit 1
    fi

    ./shutdownall

    cd ..

done

exit 0

I hope pgpool developers would add more test cases.

Setting up everything at one time

While developping pgpool-II 3.3, I created a small script to make developer's life a little bit easier. However, the tool might be useful for PostgreSQL/pgpool-II beginners. So I decided to introduce it in my blog.

Disclaimer: the environment created by the tool below should never be used for production.

The script is called "pgpool_setup".  It creates followings by just one command under current directory. The directory must be empty. PostgreSQL and pgpool binaries must be installed and in your command search path beforehand.

• PostgreSQL clusters. You can choose "streaming replication mode" or "native replication mode". The former you already know, I assume. The latter is just a set of identical PostgreSQL clusters except the port numbers are different.
• If you choose streaming replication mode, one primary server and standby servers are ready after the command runs (even with WAL archival is enabled).
• By default those PostgreSQL cluster port number start from 11000 and continues 11001, 11002 and so on.
• The port numbers of pgpool and pcp follow.
• One pgpool server, with failover/online recovery/followmaster command are all set.
• A test database "test" is created.
• pcp commands(which are for pgpool administrator) account and password are ready for you.

By running pgpool_setup, you are ready to start pgpool and PostgreSQL with complete settings (at least for testing pupose) on your computer. I use this tool to create multiple groups  of pgpool+PostgreSQL set on my laptop.

So here's a sample session.

$ pgpool_setup
Satrting set up in streaming replication mode
creating startall and shutdownall
creating failover script
creating database cluster /home/t-ishii/work/git.postgresql.org/tmp2/data0...done.
update postgreql.conf
creating pgpool_remote_start
creating basebackup.sh
creating recovery.conf
creating database cluster /home/t-ishii/work/git.postgresql.org/tmp2/data1...done.
update postgreql.conf
creating pgpool_remote_start
creating basebackup.sh
creating recovery.conf
temporarily start data0 cluster to create extensions
temporarily start pgpool-II to create standby nodes
waiting for failover happens...done.
 node_id | hostname | port  | status | lb_weight |  role   
---------+----------+-------+--------+-----------+---------
 0       | /tmp     | 11000 | 2      | 0.500000  | primary
 1       | /tmp     | 11001 | 3      | 0.500000  | standby
(2 rows)

recovery node 1...done.
creating follow master script
Pager usage is off.
 node_id | hostname | port  | status | lb_weight |  role   
---------+----------+-------+--------+-----------+---------
 0       | /tmp     | 11000 | 2      | 0.500000  | primary
 1       | /tmp     | 11001 | 2      | 0.500000  | standby
(2 rows)

shutdown all

pgpool-II setting for streaming replication mode is done.
To start the whole system, use /home/t-ishii/work/git.postgresql.org/tmp2/startall.
To shutdown the whole system, use /home/t-ishii/work/git.postgresql.org/tmp2/shutdownall.
pcp command user name is "t-ishii", password is "t-ishii".
Each PostgreSQL, pgpool-II and pcp port is as follows:
#1 port is 11000
#2 port is 11001
pgpool port is 11002
pcp port is 11003
The info above is in README.port.
$ ls
README.port  bashrc.ports*  data1/  log/              run/          startall*
archivedir/  data0/         etc/    pgpool_setup.log  shutdownall

# So here you can see PostgreSQL cluster 0 as "data0" and cluster 1 as
# "data1".  You can start everything (pgpool and PostgreSQL) by using
# "startall", which is creaeted by the comand.

$ sh startall
waiting for server to start.... done
server started
waiting for server to start.... done
server started

# Make sure primary and standby are running.  "show pool_nodes" shows
# the info. Port 11002 is the port for pgpool.  As you can see
# primary's port is 11000 and standby's port is 11001.
# Status "2" means up and running.

$ psql -p 11002 -c 'show pool_nodes' test
 node_id | hostname | port  | status | lb_weight |  role   
---------+----------+-------+--------+-----------+---------
 0       | /tmp     | 11000 | 2      | 0.500000  | primary
 1       | /tmp     | 11001 | 2      | 0.500000  | standby
(2 rows)

# Now let's make sure that streaming replication is activated.

$ psql -p 11002 test
Pager usage is off.
psql (9.3beta1)
Type "help" for help.

test=# create table t1(i int);
CREATE TABLE
test=# insert into t1 values(1);
INSERT 0 1
test=# select * from t1;
 i 
---
 1
(1 row)

test=# \q

# Connect to standby.
$ psql -p 11001 test
Pager usage is off.
psql (9.3beta1)
Type "help" for help.

test=# select * from t1;
 i 
---
 1
(1 row)

test=# \q

# Ok, "t1" is propagated.

# Next test failover. So shutdown standby.

$ pg_ctl -D data1 -m f stop
waiting for server to shut down.... done
server stopped
$ psql -p 11002 -c 'show pool_nodes' test
 node_id | hostname | port  | status | lb_weight |  role   
---------+----------+-------+--------+-----------+---------
 0       | /tmp     | 11000 | 2      | 0.500000  | primary
 1       | /tmp     | 11001 | 3      | 0.500000  | standby
(2 rows)

# Note that status "3" means down.

# Let's stary standby and make it online.
$ pg_ctl -D data1 start
server starting
$ pcp_attach_node 1 localhost 11003 t-ishii t-ishii 1
$ psql -p 11002 -c 'show pool_nodes' test
 node_id | hostname | port  | status | lb_weight |  role   
---------+----------+-------+--------+-----------+---------
 0       | /tmp     | 11000 | 2      | 0.500000  | primary
 1       | /tmp     | 11001 | 2      | 0.500000  | standby
(2 rows)

# Next test is promotion. Let's stop primary and see if standby promotes.

$ pg_ctl -D data0 -m f stop
waiting for server to shut down.... done
server stopped

# Wait for while to let our failover script and follow master script do the job.

$ psql -p 11002 -c 'show pool_nodes' test
 node_id | hostname | port  | status | lb_weight |  role   
---------+----------+-------+--------+-----------+---------
 0       | /tmp     | 11000 | 2      | 0.500000  | standby
 1       | /tmp     | 11001 | 2      | 0.500000  | primary
(2 rows)

# Ok, now node 0 is standby, and 1 is primary. 

pgpool_setup is available in Pgpool-II 3.4 or later.

Pgpool-II talk at PostgreSQL Conference Europe 2012

I'm going to give a pgpool-II talk at upcoming PostgreSQL Conference Europe 2012. The talk is titled "Boosting performance and reliability by using pgpool-II" and I will explain how to enhance DB performance by using pgpool-II 3.2's "on memory query cache". Also I will explain how to set up "watchdog", which is also a new feature of pgpool-II 3.2. By using this, you can avoid SPOF(single point of failure) problem of pgpool-II itself without using extra HA software.

The conference will be held in Prague, the Czech Republic, October 23-26. I've never been to Prague, and am excited at this opportunity to visit the old beautiful city!

Visiting Brussels

After visiting Paris, I moved to Brussels to join FOSDEM, the largest open source conference in Europe.

I gave a 45-minute talk abut PostgreSQL and pgpool-II.

After finishing the talk we enjoyed a small tour of the beautifull city.


This is the famous "Grand Place".


There are nice Art Nouveau caffes in the city.

I bought some laces in a old lace shop. They are hand-made and very nice.

There is a great chuch which has a big pipe organ. I was so impressed and bought a CD, recorded the acuall play of the organ!

Playing with PHP PDO

PHP PDO(PHP Data Object) allows you to access database including PostgreSQL within PHP scripts. Good thing with PDO is, you do not need to heavily modify your PHP script even if you change the database product you want to use. This resembles to JDBC driver or Perl DBI. Moreover you could use database product dependent interface. Which means you can enjoy the full power of PostgreSQL with sacrificing portability of course. There's no free lunch. In summary, PHP PDO is great and I would like to recommend to anyone who is thinking about serious database programming in PHP.

One day I came across a complaint from pgpool-II user. According to him, his PHP client was blocked by pgpool-II while trying to connect to it. Ok, this is a typical situation when user misconfigured num_init_children and I thought he tried to connect pgpool-II concurrently with more than num_initi_children sessions. But his answer is, no. I suspected the PDO internal but I was too lazy to look into PDO source code. Instead I use strace to see what PDO is doing. To make the long story short, PDO releases the connection to pgpool-II long after "$dbh = null;"($dbh is the "handle" of the connection. This should release the connection). So if his script continues and connects to pgoool-II again after he thinks that he releases the connection, the script actually has two concurrent connections to pgpool-II. Of course this will create more connections than he thought.

I think the reason why many people do not come up with similar complaint is, their script usually create just one connection and release it at the end of the script.

Tutorial to pgpool-II plus Streaming replication

I have written a tutorial to pgpool-II using PostgreSQL 9.0's streaming replication. The tutorial is designed to use minimum resource: a Linux box. On the box, pgpool-II, pgpoolAdmin(a PHP based pgpool-II GUI admin tool) and two PostgreSQL 9.0 instances.

Though the tutorial system is surprisingly simple, you could learn:

- How pgpool-II detects PostgreSQL failure and automatically promote a standby server to primary server

- How pgpool-II re-sync the failed node by using "online recovery" capability

Please enjoy!

PostgreSQL 9.0 and pgpool-II endurance test

I have started to run an endurance test of PostgreSQL 9.0beta4 and pgpool-II 3.0-dev on Aug 12 17:47.
The hardware: two dual-core opteron 2.2GHz, 2GB mem.
One is used for the streaming replication primary server and the other is used for the standby server.
The load is provided by pgbench -N -c 10 (10 concurent users) -C (each time the transaction starts, pgbench connects to pgpool), which runs on the primary server.

Up to now the test has lasted for about 88 hours. No errors, no OOM killer run because of memory leak. Totally 291 million transactions have been processed.

Here is some TPS data. "excluding" means excluding connect/disconnect time. "including" means including connect/disconnect time. So it's not supprising that "excluding" is far better than "including". This is the reson why you want connection pooling.

select avg(excluding) as excluding_avg,
stddev(excluding) as excluding_stddev,
avg(including) as including_avg,
stddev(including) as including_stddev
from endurance_test;

-[ RECORD 1 ]----+-----------------
excluding_avg    | 1290.55333121045
excluding_stddev | 18.6107550266789
including_avg    | 916.442996303828
including_stddev | 10.3926488799762

Talk with the author of Streaming Replication

Yesterday I talked with Masao Fujii, the author of Streaming Replication. Streaming Replication(SR) is a long awaited feature, "built in replication" of PostgreSQL. The great feature will come with PostgreSQL 9.0, which is supposed to be released this Summer. With SR, pgpool-II will provide two important features: load balancer and failover control. Since SR is a master/slave type replication system, the standby server (slave) do not accept write queries such as INSERT, DELETE, UPDATE, CREATE TABLE and so on. Any write query sent to pgpool will be redirected to the primary server (master) and users do not need to worry about it. On the other hand, as SELECT can be executed both on the primary and the standby, pgpool will choose one of them and send the SELECT to it. This technique is known as "load balancing" and this will significantly improve the over all performance.

One thing we were worried about was, what if the connection between the primary server and the standby server goes down? There's no easy way for pgpool to know it. If pgpool does not know that, SELECT can be sent to the standby whose data is far behind the primary. Apparently this is what users want to avoid. To overcome the problem, pgpool checks the transaction log number (LSN) on both the primary and the standby. If standby's LSN is too small comparing with the one on the primary, we might have a problem. So pgpool stops the load balancing and send SELECT only to the primary. How behind is "behind" should be defined by new directive in pgpool.conf. Good thing with this is, the technique will also detects the delay because of the heavy load of the standby.

We will continue to discuss how to improve the usability of SR and pgpool-II.