[pgpool-general: 742] Re: pgpool-II 3.2 Beta1 released

Tatsuo Ishii ishii at postgresql.org
Tue Jul 17 09:50:27 JST 2012


> This is quite the impressive list of new features.  I have two questions:
> 0) What is "strage"?  I see the word used in a few different places,
> but I don't think this is an English word, so perhaps its a
> translation mistake?

Yep. "storage" is correct.

> 1) How would I determine what size of cache I'd need to allocate?  Is
> it based on the total number of tables in the cluster, multiplied by
> some factor?

There are number of factors which affect required cache size:

1) How many different kind of queries are there? Because cache is
   based on query literal string, the more there are different
   queries, the bigger required cache size is.

2) Query result size. If query results are big, you need more cache
   storage. BTW, if query result size exceeds memq_maxcache, you will
   see something like these in your pgpool log:

2012-07-17 09:45:19 LOG:   pid 8154: DB node id: 0 backend pid: 9428 statement: select * from pgbench_accounts limit 100;
2012-07-17 09:45:19 LOG:   pid 8154: pool_add_temp_query_cache: data size exceeds memqcache_maxcache. current:983 requested:110 memq_maxcache:1024

3) memqcache_expire. This defines each cache entry's life time. If you
   set it somewhat short, and you do not send query so often, you need
   less cache storage.

4) How often you update the table. Since pgpool removes all the cache
   related to a particular table everytime the table gets updated, the
   more frequent you update, the less required cache size is. However
   you will not want to cache frequently updated table anyway, and you
   might want to consider to use black_memqcache_table_list in this
   case.

So I think a practical way to determine the cache size is, run read
queries for sometime and check the result of "show pool_cache". One of
output is cache_hit_ratio. If this is lower than you expect, you need
more cache storage.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> thanks!
> 
> On Thu, Jul 5, 2012 at 11:12 PM, Nozomi Anzai <anzai at sraoss.co.jp> wrote:
>> Pgpool Global Development Group is pleased to announce the
>> availability of pgpool-II 3.2 beta1.
>>
>> Users are encouraged to take part in our beta test program.
>> Please help us in testing and making pgpool-II 3.2 better!
>>
>> You can download it from here:
>> http://www.pgpool.net/download.php?f=pgpool-II-3.2beta1.tar.gz
>>
>>
>> We set milestones toward release pgpool-II 3.2:
>>
>> 2012/07/06(Fri): Beta 1 release
>> 2012/07/13(Fri): Beta 2 release
>> 2012/07/20(Fri): RC1 release
>> [pgpool-hackers: 80]
>>
>>
>> Major changes from 3.1.3 to beta1 are:
>>
>> ============================================================================
>> 3.2.0 (namameboshi) 2012/07/xx
>>
>> This is the first version of pgpool-II 3.2 series. That is, a "major
>> version up" from 3.1 series.
>>
>> ----------------------------------------------------------------------------
>> Incompatible changes
>> ----------------------------------------------------------------------------
>>
>> - The new query cache "On memory query cache" took the place of the old
>>   one.
>>   - Now the parameter "enable_query_cache" is deleted.
>>
>> ----------------------------------------------------------------------------
>> New features
>> ----------------------------------------------------------------------------
>>
>> - Memory based query cache. (Tatsuo Ishii, Nozomi Anzai, Yugo Nagata)
>>   Original author is Masanori Yamazaki, improved by Development Group.
>>
>>   Overview:
>>
>>     [ New fast query cache ! ]
>>
>>     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.
>>
>>    [ In some cases, it works slowly ]
>>
>>     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 strage:
>>     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:
>>
>>     [ Auto cache invalidation and Cache expire ]
>>
>>     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.
>>
>>     [ The case of mutual pgpool-II processes work ]
>>
>>     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
>>     case.
>>
>>   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
>>       follows:
>>       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
>>       follows:
>>       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, etc.:
>>
>>     - 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. (Atsushi Mitani, Yugo Nagata)
>>   Developped by Atsushi Mitani and 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.
>>
>>     [ Mutually 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
>>     state.
>>
>>     [ 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_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)
>>
>>
>> ----------------------------------------------------------------------------
>> Enhancements
>> ----------------------------------------------------------------------------
>>
>> - Try to support poor platforms those do not have vsyslog such as HP-UX
>>   and AIX.(Tatsuo Ishii)
>>
>> - 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.
>>   (Tatsuo Ishii)
>>
>>   This is usefull to identify which client gives wrong query without
>>   enabling log_connections, which produces too many log entries on busy web
>>   systems.
>>
>> - Add "role" which represents "primary" or "standby" iin streaming
>>   replication mode for example to "SHOW pool_nodes" command.(Tatsuo Ishii)
>>
>> - Adopt PostgreSQL 9.2. (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
>>
>> ----------------------------------------------------------------------------
>> Bug fixes
>> ----------------------------------------------------------------------------
>>
>> - 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.
>>
>> - 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.
>>
>> - 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 numbers.
>>   Problem reported at [pgpool-general: 396].
>>
>>   [pgpool-general: 396] strange load balancing issue in Solaris
>>   http://www.sraoss.jp/pipermail/pgpool-general/2012-April/000397.html
>>
>> - 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;
>>
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general


More information about the pgpool-general mailing list