[pgpool-general: 848] pgpool-II 3.2.0 and pgpoolAdmin 3.2.0 officially released
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:
Attached are release notes.
(Memory cache) http://www.pgpool.net/pgpool-web/contrib_docs/memqcache/en.html
- The new query cache "On memory query cache" took the place of the old
- Now the parameter "enable_query_cache" is deleted.
| Memory based query cache |
Original author is Masanori Yamazaki, improved by Development Group.
(Tatsuo Ishii, Nozomi Anzai, Yugo Nagata)
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
* 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.
- 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
- 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_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.
"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
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.
- 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
- 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.
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.
- 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.
- Commentout params about system db from pgpool.conf.sample-*.
- Add new parameter to failover/failback/followmaster command.
%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().
- 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,
- Fix pool_ssl_write. It seems someone forgot to do retrying when SSL
returns SSL_ERROR_WANT_READ or SSL_ERROR_WANT_WRITE.
Per [pgpool-general: 797]
Re: Problem with pgpool when using SSL, for client/pgpool communication
- Improve the design of manuals. (Nozomi Anzai)
- 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.
Query example to reproduce:
(S1) SELECT * FROM t;
(S2) DELETE FROM t;
(S2) VACUUM t;
- Fix pool_process_query() bug reported in [pgpool-general: 672].
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.
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.
So added check for BEGIN WORK ISOLATION LEVEL SERIALIZABLE case.
Load Balancing / Streaming Replication / Isolation Level serializable
- Fix send_to_where() to send the query to only primary if the it is like
SET TRANSACTION ISOLATION LEVELSERIALIZABLE etc. (Tatsuo Ishii)
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:
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL
SERIALIZABLE or SET transaction_isolation TO 'serializable'
SET default_transaction_isolation TO 'serializable'
Original complain is [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
- 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 : [257-1] ERROR: canceling statement
due to conflict with recovery
2012-07-17 00:11:03 NZST : [258-1] DETAIL: User query might
have needed to see row versions that must be removed.
2012-07-17 00:11:03 NZST : [259-1] STATEMENT: <SNIP>
2012-07-17 00:11:03 NZST : [366-1] FATAL: terminating
connection due to conflict with recovery
2012-07-17 00:11:03 NZST : [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
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.
* 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.
[ Health Check ]
[ On Memory Query Cache ]
memory_cache_enabled, memqcache_method, memqcache_memcached_host,
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 ]
- 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.
SRA OSS, Inc. Japan
More information about the pgpool-general