[pgpool-general: 1787] Re: Master-Slave, load bancing and memory cache questions

[Tatsuo Ishii]

> Hi Tatsuo,
> 
> Our configuration are:
> master_slave_mode = on
> master_slave_sub_mode = 'stream'
> load_balance_mode = off
> 
> If memory_cache_enabled = on, in PostgreSQL slave backend log display
> internal PgPool queries, for example:
> 
> 
> 2013-05-28 09:56:05 CEST [3498]:[422] LOG: statement: BEGIN
> 2013-05-28 09:56:05 CEST [3371]:[659] LOG: execute
> pgpool3347/pgpool3347: SELECT count(*) FROM pg_class AS c,
> pg_namespace AS n WHERE c.relname = 'est_config' AND c.relnamespace =
> n.oid AND n.nspname ~ '^pg_temp_'
> 2013-05-28 09:56:05 CEST [3484]:[385] LOG: statement: BEGIN
> 2013-05-28 09:56:05 CEST [3371]:[660] LOG: execute
> pgpool3347/pgpool3347: SELECT count(*) FROM pg_catalog.pg_class AS c
> WHERE c.oid = pgpool_regclass('est_config') AND c.relpersistence = 'u'
> 2013-05-28 09:56:05 CEST [3473]:[541] LOG: execute
> pgpool3329/pgpool3329: SELECT count(*) FROM pg_class AS c,
> pg_namespace AS n WHERE c.relname = 'est_context' AND c.relnamespace =
> n.oid AND n.nspname ~ '^pg_temp_'
> 2013-05-28 09:56:05 CEST [3374]:[465] LOG: execute
> pgpool3240/pgpool3240: SELECT count(*) FROM pg_class AS c,
> pg_namespace AS n WHERE c.relname = 'est_capabilities' AND
> c.relnamespace = n.oid AND n.nspname ~ '^pg_temp_'
> 2013-05-28 09:56:05 CEST [3407]:[430] LOG: execute
> pgpool3337/pgpool3337: SELECT count(*) FROM pg_catalog.pg_class AS c
> WHERE c.oid = pgpool_regclass('est_context') AND c.relpersistence =
> 'u'
> 2013-05-28 09:56:05 CEST [3479]:[444] LOG: statement: COMMIT
> 
> In otherwise, when memory_cache_enabled = off, same postgresql log
> only show DCL queries, for example:
> 
> 2013-05-28 09:15:18 CEST [30353]:[514] LOG: statement: SET
> statement_timeout TO DEFAULT
> 2013-05-28 09:15:18 CEST [30320]:[441] LOG: statement: COMMIT
> 2013-05-28 09:15:18 CEST [30363]:[475] LOG: statement: BEGIN
> 2013-05-28 09:15:18 CEST [30353]:[515] LOG: statement: COMMIT
> 2013-05-28 09:15:18 CEST [30330]:[530] LOG: statement: BEGIN
> 2013-05-28 09:15:18 CEST [30363]:[476] LOG: statement: SET
> statement_timeout TO DEFAULT
> 2013-05-28 09:15:18 CEST [30330]:[531] LOG: statement: SET
> statement_timeout TO 120000
> 2013-05-28 09:15:18 CEST [30363]:[477] LOG: statement: COMMIT
> 2013-05-28 09:15:18 CEST [30330]:[532] LOG: statement: COMMIT
> 2013-05-28 09:15:18 CEST [30324]:[472] LOG: statement: BEGIN
> 2013-05-28 09:15:18 CEST [30324]:[473] LOG: statement: SET
> statement_timeout TO 120000
> 2013-05-28 09:15:18 CEST [30352]:[606] LOG: statement: BEGIN
> 2013-05-28 09:15:18 CEST [30352]:[607] LOG: statement: SET
> statement_timeout TO 120000
> 2013-05-28 09:15:18 CEST [30352]:[608] LOG: statement: COMMIT
> 2013-05-28 09:15:18 CEST [30319]:[520] LOG: statement: BEGIN
> 2013-05-28 09:15:18 CEST [30352]:[609] LOG: statement: BEGIN
> 
> Why this change of behavior by changing this parameter?

Those SELECTs are issued by pgpool-II itself to check PostgreSQL
system catalog, which are neccessary when on memory query cache is
enabled. In summary, they are normal.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> Regards,
> Toni
> 
> Al 28/05/13 01:15, En/na Tatsuo Ishii ha escrit:
>>> Hi,
>>>
>>> We have experimented an abnormal situation when set on memory
>>> cache. We have configured PgPool 3.2.3 version, in master-slave mode
>>> and disabled load balancing.
>>> I understand reading the documentation, if we have set on master-slave
>>> mode, although load balancing parameter was disabled, it has no
>>> effect, and Pgpool applied load query balancing. This is true?
>>>
>>> Agree, the configuration set master-slave mode with stream
>>> replication, and load balancing set off. The abnormal situation
>>> appears when I have set on or off memory cache. If we have disabled
>>> memory cache, Pgpool no applied load query balancing. In otherwise,
>>> when memory cache is enabled, PgPool applied load query
>>> balancing. This is normal situation, or there may be an bug?
>> Are you saying that "load_balance_mode = off" does not take effect if
>> "memory_cache_enabled = on"? Did not reproduce here. Can you please
>> show us more details.
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese: http://www.sraoss.co.jp
> 
> 
> -- 
> Toni Mas
> Universitat de les Illes Balears
> Ctra. Valldemossa Km 7,5
> 971 17 27 21
>