[pgpool-general: 848] pgpool-II 3.2.0 and pgpoolAdmin 3.2.0 officially released

Nozomi Anzai anzai at sraoss.co.jp
Fri Aug 3 11:49:13 JST 2012

Pgpool Global Development Group is pleased to announce the availability of
pgpool 3.2.0 and pgpoolAdmin 3.2.0.

You can download them from here:
(pgpool-II)    http://www.pgpool.net/download.php?f=pgpool-II-3.2.0.tar.gz
(pgpoolAdmin)  http://www.pgpool.net/download.php?f=pgpoolAdmin-3.2.0.tar.gz  

Attached are release notes.

pgpool-II 3.2.0

Release note:


(Memory cache) http://www.pgpool.net/pgpool-web/contrib_docs/memqcache/en.html
(Watchdog)     http://www.pgpool.net/pgpool-web/contrib_docs/watchdog/en.html

Incompatible changes

  - The new query cache "On memory query cache" took the place of the old
    - Now the parameter "enable_query_cache" is deleted.

New features

| Memory based query cache                                                     |

Original author is Masanori Yamazaki, improved by Development Group.
(Tatsuo Ishii, Nozomi Anzai, Yugo Nagata)

* Overview:

    On memory query cache is faster because cache storage is on memory.
    Moreover you don't need to restart pgpool-II when the cache is outdated
    because the underlying table gets updated.

    On memory cache saves pair of SELECT statements (with its Bind
    parameters if the SELECT is an extended query). If the same SELECTs
    comes in, it returns the value from cache. Since no SQL parsing nor
    access to PostgreSQL are involed, it's extremely fast.

    On the other hand, it might be slower than the normal path because it
    adds some overhead to store cache. Moreover when a table is updated,
    pgpool automatically deletes all the caches related to the table. So
    the prformace will be degraded by a system with a lot of updates. If
    the cache_hit_ratio is lower than 70%, you might want to disable onl
    memory cache.

* Choosing cache storage:

    You can choose a cache strage: shared memory or memcached (you can't
    use the both).
    Query cache with shared memory is fast and easy because you don't have
    to install and config memcached, but restricted the max size of cache
    by the one of shared memory. Query cache with memcached needs a
    overhead to access network, but you can set the size as you like.

* Restrictions:

  - On memory query cache deletes the all cache of an updated table
    automatically with monitoring if the executed query is UPDATE, INSERT,
    ALTER TABLE and so on.
    But pgpool-II isn't able to recognize implicit updates due to trigers,
    foreign keys and DROP TABLE CASCADE.
    You can avoid this problem with memqcache_expire by which pgpool
    deletes old cache in a fixed time automatically, or with
    black_memqcache_table_list by which pgpool's memory cache flow ignores
    the tables.

  - If you want to use multiple instances of pgpool-II with online memory
    cache which uses shared memory, it could happen that one pgpool deletes
    cache, and the other one doesn't do it thus finds old cached result
    when a table gets updated. Memcached is the better cache strage in this

* New parameters:

  - Add parameters for on memoey query cache as follows:
    memory_cache_enabled, memqcache_method, memqcache_expire,
    memqcache_maxcache, memqcache_oiddir. (Tatsuo Ishii)

  - Add parameters about shared memory for on memory query cache as
    memqcache_total_size, memqcache_max_num_cache,
    memqcache_cache_block_size. (Tatsuo Ishii)

  - Add parameters about memcached for on memory query cache as follows:
    memqcache_memcached_host, memqcache_memcached_port. (Tatsuo Ishii)

  - Add parameters about relation cache for on memory query cache as
    relcache_expire, relcache_size. (Tatsuo Ishii)

  - Add a parameter "check_temp_table" to check if the SELECTed table is
    temp table. (Tatsuo Ishii)

  - Add the parameters of white_memqcache_table_list,
    black_memqcache_table_list that check if the SELECTed tables, temp
    tables and views are to be cached or not. (Nozomi Anzai)

  - Add a parameter "memqcache_auto_cache_invalidation" of the flag if
    query cache is triggered by corresponding DDL/DML/DCL (and
    memqcache_expire). (Yugo Nagata)

* New commands:

  - Add "SHOW pool cache" which shows hit ratio of query cache and the
    status of cache strage.

  - Add "--with-memcached" option to configure. (Nozomi Anzai)

  - Add "-C, --clear-oidmaps" option to "pgpool" command. (Nozomi Anzai)
    If pgpool with memcached starts / restarts with -C option, discard oid
    maps, if not, it can reuse old oid maps and query caches.

| Watchdog                                                                     |

The author is Atsushi Mitani, tested by Yugo Nagata.

* Overview:

  "Watchdog" is a sub process of pgpool-II aiming for adding high
  availability feature to it. Features added by watchdog include:

  - Life checking of pgpool service

    Watchdog monitors responses of pgpool service rather than process. It
    sends queries to PostgreSQL via pgpool which is being monitored by
    watchdog and watchdog checks the response. Also watchdog monitors
    connections to up stream servers (application servers etc.) from the
    pgpool. The connection between the up stream servers and the pgpool is
    monitored as service of pgpool.

  - Mutual monitoring of watchdog processes

    Watchdog processes exchange information on the monitored servers to
    keep the information up to date, and to allow watchdog processes to
    mutually monitor each other.

  - Changing active/standby state in case of certain faults detected

    When a fault is detected in the pgpool service, watchdog notifies the
    other watchdogs of it. Watchdogs decide the new active pgpool if
    previous active pgpool is broken by voting and change active/standby

  - Automatic virtual IP address assigning synchronous to server switching

    When a standby pgpool server promotes to active, the new active server
    brings up virtual IP interface. Meanwhile, the previous active server
    brings down the virtual IP interface. This enables the active pgpool to
    work using the same IP address even when servers is switched over.

  - Automatic registration of a server as standby in recovery

    When broken server recovers or new server is attached, the watchdog
    process notifies the other watchdog process along with information of
    the new server, and the watchdog process receives information on the
    active server and other servers. Then, the attached server is
    registered as standby.

* New parameters:

  - Add a parameter to enable watchdog: use_watchdog. (Atsushi Mitani)

  - Add the parameters about life checking of pgpool service as follows:
    wd_interval,other_pgpool_port wd_life_point, wd_lifecheck_query,
    other_pgpool_hostname, other_pgpool_port. (Atsushi Mitani)

  - Add the parameters about up to stream connection (e.g. to application
    servers) as follows:
    trusted_servers, ping_path. (Atsushi Mitani)

  - Add the parameters about mutual monitoring of watchdog processes
    as follows:
    wd_hostname, wd_port, other_wd_port. (Atsushi Mitani)

  - Add the parameters about virtual IP as follows:
    ifconfig_path, if_up_cmd, if_down_cmd, arping_path, arping_cmd.
    (Atsushi Mitani)


  - Retry if health check faied rather than immediately do failover. For
    this purpose new directives "health_check_max_retries" and
    "health_check_retry_delay" were added. (Tatsuo Ishii)
    Patch contributed by Matt Solnit.

      Subject: [Pgpool-hackers] Health check retries (patch)
      From: Matt Solnit
      Date: Fri, 18 Nov 2011 16:28:44 -0500

  - Log client IP and port number when pgpool failed to parse given query.

    This is usefull to identify which client gives wrong query without
    enabling log_connections, which produces too many log entries on busy
    web systems.

  - Refactor pool_process_query(). (Tatsuo Ishii)

    Deal with the case when:
      1) query not in progress
      2) other than master node has pending data.
    It is reported that pgpool goes into infinite loop in this case.

      [pgpool-general: 43]
      Re: [Pgpool-general] seemingly hung pgpool process consuming 100% CPU

  - Add "role" which represents "primary" or "standby" iin streaming
    replication mode for example to "SHOW pool_nodes" command.
    (Tatsuo Ishii)

  - Add params to the result of "SHOW pool_status": backend_data_directory,
    ssl_ca_cert, ssl_ca_cert_dir, and sort by orders in pgpool.conf.
    (NoZomi Anzai)

  - Commentout params about system db from pgpool.conf.sample-*.
    (Nozomi Anzai)

  - Add new parameter to failover/failback/followmaster command.
    (Tatsuo Ishii)

      %r: new master port number
      %R: new master database cluster path

  - Allow to reload to recognize md5 password change. (Tatsuo Ishii)
    Before, the only way to recognize md5 password change was restarting
    pgpool-II. Patch contributed by Gurjeet Singh.

  - Remove unused parameter "query" from is_set_transaction_serializable().
    (Tastuo Ishii)

  - Fix on memory query cache section's comments in pgpool.com in the way
    which other sections employ. (Tastuo Ishii)

  - Add missing health_check_max_retries and health_check_retry_delay
    directives to pgpool.conf.sample-master-slave,
    pgpool.conf.sample-replication pgpool.conf.sample-stream.
    (Tastuo Ishii)

  - Fix pool_ssl_write. It seems someone forgot to do retrying when SSL
    Per [pgpool-general: 797]

    [pgpool-general: 797]
    Re: Problem with pgpool when using SSL, for client/pgpool communication

  - Improve the design of manuals. (Nozomi Anzai)

Bug Fix

  - Fix memory leak in Raw mode.(Tatsuo Ishii)

  - Fix failover/failback in Raw mode. (Tatsuo Ishii)
    - Simply does not failover if target node is not master.
    - Fail to select master node if the node is in CON_UP.

  - Allow health check retry while connect(). (Tatsuo Ishii)

    It is reported that connect() blocks sigalarm under some conditions,
    for example: When system is configured for security reasons not to
    return destination host unreachable messages ([pgpool-general: 131])
    Part of changes are contributed by Stevo Slavic.

    [pgpool-general: 131] Healthcheck timeout not always respected

  - Fix pool_send_and_wait() to send or not to send COMMIT / ABORT
    depending on the transaction state on each node. (Tatsuo Ishii)

    It is possible that only primary is in an explicit transaction but
    standby is not in it if multi statement query has been sent.
    Per bug report [pgpool-general-jp: 1049].

  - Fix load balance in Solaris. (Tatsuo Ishii)

    Problem is, random() in using random() in Solaris results in strange
    load balancing calculation.
    Use srand()/rand() instead although they produce lesser quality random
    Problem reported at [pgpool-general: 396].

    [pgpool-general: 396] strange load balancing issue in Solaris

  - Fix segfault of pcp_systemdb_info not in parallel mode. (Nozomi Anzai)

  - Fix "unnamed prepared statment does not exist" error. (Tatsuo Ishii)

    This is caused by pgpool's internal query, which breaks client's
    unnamed statements. To fix this, if extended query is used, named
    statement/portal for internal are used for internal query.

  - Fix hangup when query conflict occurs in Hot-Standby mode.
    (Yugo Nagata)

    Query example to reproduce:

        (S1) BEGIN;
        (S1) SELECT * FROM t;
        (S2) DELETE FROM t;
        (S2) VACUUM t;

  - Fix pool_process_query() bug reported in [pgpool-general: 672].
    (Tatsuo Ishii)

    This is caused by the function waits for primary node which does not
    have pending data, while standbys have pending data.

    [pgpool-general: 672] Transaction never finishes

  - Fix wait_for_query_response() not to send param status to frontend if
    frontend is NULL. (Tatsuo Ishii)

    This could happen while processing reset_query_list and occur crash.

  - Fix bug with treatment of BEGIN TRANSACTION in master/slave mode.
    (Tatsuo Ishii)

    Original complain is [pgpool-general: 714].
    From 3.1, pgpool-II sends BEGIN.. to all DB nodes. Of course we cannot
    send BEGIN TRANSACTION READ WRITE to standby nodes.
    Problem is, we did not check BEGIN WORK ISOLATION LEVEL SERIALIZABLE;
    and sent to standby nodes. Of course this is wrong, since it's not
    allowed to run transactions in serializable mode on standby nodes.

    [pgpool-general: 714]
    Load Balancing / Streaming Replication / Isolation Level serializable

  - Fix send_to_where() to send the query to only primary if the it is like

    Case in streaming replication mode. Previously, it was sent to not only
    primary but also to standby and of course this causes an error.
    Similar SQLs are:
     SERIALIZABLE or SET transaction_isolation TO 'serializable'
     SET default_transaction_isolation TO 'serializable'

    Original complain is [pgpool-general: 715].

    [pgpool-general: 715]
    Re: Load Balancing / Streaming Replication / Isolation Level

  - Prevent errors even if memcached is not available with on memory query
    cache enabled and cache storage is memcached. (Tatsuo Ishii)

    This is mainly achieved by modifying pool_fetch_cache to pretend
    "cache not found" if memcached_get returns error other than
    MEMCACHED_NOTFOUND. Also we set pool_config->memory_cache_enabled to 0
    to prevent future errors by accessing memcached.

  - Run rerun libtoolize with --copy and --force option. (Tatsuo Ishii)

    This should prevent build problems in different environment.
    Following commands were executed:
    libtoolize --copy --force
    automake -a

  - Fix pool_ssl_read. (Tatsuo Ishii)

    When SSL_read returns unknown error, treat as if EOF detected and
    returns 0 to caller (pool_read). This is same as libpq's behavior. Also
    this will avoid unwanted failover in pool_read. pool_read triggers
    failover if underlying I/O functions, read(2) or pool_ssl_read returns

  - Fix pool_process_query. (Tatsuo Ishii)

    When other than primary sends packet, pgpool tries to terminate session
    gracefully and hung because now ssl_read returns EOF, rather than error
    and triggers failover. For example [pgpool-general: 766] reported this:

    2012-07-17 00:11:03 NZST [15692]: [257-1] ERROR: canceling statement
    due to conflict with recovery
    2012-07-17 00:11:03 NZST [15692]: [258-1] DETAIL: User query might
    have needed to see row versions that must be removed.
    2012-07-17 00:11:03 NZST [15692]: [259-1] STATEMENT: <SNIP>
    2012-07-17 00:11:03 NZST [15696]: [366-1] FATAL: terminating
    connection due to conflict with recovery
    2012-07-17 00:11:03 NZST [15696]: [367-1] DETAIL: User query might
    have needed to see row versions that must be removed.

    In this case pool_process_query should return POOL_ERROR, rather than

    [pgpool-general: 766]
    Re: pgpool dropping backends too much

  - If frontend terminates while reading large number of query results
    from backend, pgpool continues to read from backend and write to
    frontend until all query results consumued. (Tatsuo Ishii)

    This will take very long time if query results is huge. To finish
    pgpool session as soon as possible, modify pool_flush_it to returns
    error if fail to write to frontend when operated in other than
    replication mode. In replication mode, we need to keep the previous
    behavior to save consistency among backends).

    Also fix SimpleForwardToFrontend to return pool_write_and_flush error
    status. Before it was ignored.

pgpoolAdmin 3.2 

* Version 3.2 2012/08/03

  pgpoolAdmin 3.2 adopts pgpool-II 3.2.

    - Add new parameters and remove deleted parameters in pgpool-II 3.2.
      (Anzai, Nagata)
      - new:
        [ Health Check ]
        health_check_password, health_check_max_retries,

        [ On Memory Query Cache ]
        memory_cache_enabled, memqcache_method, memqcache_memcached_host,
        memqcache_memcached_port, memqcache_total_size,
        memqcache_max_num_cache, memqcache_cache_block_size,
        memqcache_expire, memqcache_auto_cache_invalidation,
        memqcache_maxcache, memqcache_oiddir, white_memqcache_table_list,

        [ Watchdog ]
        use_watchdog, trusted_servers, ping_path, wd_interval,
        wd_life_point, wd_lifecheck_query, delegate_IP, ifconfig_path,
        if_up_cmd, if_down_cmd, arping_path, arping_cmd, wd_hostname,
        wd_port, other_pgpool_hostname, other_pgpool_port, other_wd_port

        [ Others ]
        relcache_size, check_temp_table

      - deleted:

    -  Support older versions of pgpool-II. (Anzai)
      - pgpoolAdmin 3.1 was only for pgpool-II 3.1, however the latest
        pgpoolAdmin is fixed bugs and sometimes enhanced.
        So we decided adopting these 3 pgpool-II versions: 3.0, 3.1, 3.2.
      - Now pgmgt.conf has new defined variable as the version of

    - Add "-C" option to the command "pgpool". (Anzai)

    - Use health_check_password for health check. (Anzai)

    - Update helps about pgpool.conf. Sync ones in the manual. (Anzai)

    - Some visual enhances. (Anzai)

    - Replace Smarty libralies to 3.1.8. (Anzai)
      - And Fix a lot of Notice Errors invisible in Smarty 2.x.


Nozomi Anzai
SRA OSS, Inc. Japan

More information about the pgpool-general mailing list