[pgpool-hackers: 596] Re: Proposal: primary preference

[Tatsuo Ishii]

I have committed this. FYI here is the excerpt from the manual:
-----------------------------------------------------------------------------------------------
 You can use database name and application name for smaller granularity in specifying load balance.

database_redirect_preference_list V3.4 〜

    you can set "database name:node id" pair to specify the node id when connecting to the database. For example, by specifying "test:1", pgpool-II always redirects SELECT to node 1 in case of connecting to database "test". You can specify multiple "database name:node id" pair by separating them using comma (,). Regular expressions are accepted for database name. Special keyword "primary" indicates the primary node and "standby" indicates one of standby nodes.

    Here is an example.

    database_redirect_preference_list = 'postgres:primary,mydb[01]:1,mydb2:standby'
    	

    SELECTs will be redirected to primary if you connect to postgres database. Connecting to mydb0 or mydb1 will redirect SELECTs to node 1. Connecting to mydb2 will redirect SELECTs to one of standby nodes.

    You need to reload pgpool.conf if you change this directive.
app_name_redirect_preference_list V3.4 〜

    you can set "application name:node id" pair to specify the node id when the application is used. "Application name" is a name specified by a client when it connects to database. You can use it in PostgreSQL 9.0 or later. For example, application of psql command is "psql". pgpool-II recognize application names only when clients sends a start up packet. Clients can send application names later on but pgpool-II will not recognize them.

    The notion of app_name_redirect_preference_list is same as database_redirect_preference_list. Thus you can use regular expressions for application name.

    Here is an example.

    app_name_redirect_preference_list = 'psql:primary,myapp1:1,myapp2:standby'
    	

    In this example, psql sends SELECTs to primary node, myapp1 sends to node 1, and myapp2 sends to one of standby nodes.

    app_name_redirect_preference_list takes precedence over database_redirect_preference_list. See the next example.

    		database_redirect_preference_list = 'bigdb:primary'
    		app_name_redirect_preference_list = 'myapp:2'
    	  

    Applications connecting to bigdb database send SELECTs to primary node. However myapp sends SELECTs to node 2 even if it connects to bigdb. This is useful in a scenario: myapp2 sends very heavy SELECTs to execute analysis jobs. You want to use node 2 solely for analysis purpose.

    You need to reload pgpool.conf if you change this directive.
-----------------------------------------------------------------------------------------------

While working on this I noticed:

1) Implementation of black_function_list, white_function_list,
   black_memqcache_table_list, white_memqcache_table_list has lots of
   redundant code. For example, growFunctionPatternArray and
   growMemqcacheTablePatternArray have many duplicated code. This is
   because those functions have built in knowledge about the storage to
   store precompiled regex data. Also this breakes modularity of
   code. I believe regex_arrary.c, a new infrastructure to handle
   regex array will make them lot simpler. If I have spare time, I
   would like to work on it.

2) watchdog regression test fails. Probably this is due recent elog
   changes not by my commit I think. The test expected "wd_escalation:
   escalated to master pgpool successfully".

   DETAIL:  escalating to master pgpool
   2014-08-17 20:08:23: pid 22219: LOG:  watchdog escalation successful

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

>> Hi,
>> 
>> In the first pgpool-II developer meeting, we discussed on enhancing
>> load balance. As a conclusion we decided to implement two load balance
>> preference types: 1) "application name" 2) database.  For example if
>> client connects to particular database, then the SELECT query sends to
>> primary server unconditionally. Note that we agreed that this should
>> not be called "load balance", rather "redirect" or "preference"
>> because this aims to route queries to particular node which meets
>> particular condition. Note that this feature only applicatable for
>> streaming replication mode.
>> 
>> Here is my proposal for the syntax.
>>
>> 1) application name
>> 
>> I propose a directive name "app_name_redirect_preference_list" for this
>> purpose. Proposed syntax is:
>> 
>> app_name1:node_id1,app_name2:node_id2,app_name3:node_id3...
>> 
>> where app_nameN is "application name". Regular expression can be
>> used. node_idN is the PostgreSQL server node id. Special keyword
>> "primary" can be used to represent the primary node.
> 
> I would like to add a new keyword "standby". This represents any of
> standby servers. This is more handy than specifying the each node
> id. Also by using this, when you want to add new node later on, you
> don't need to add new node id to the list.
> 
>> Example:
>> 
>> app_name_redirect_preference_list 'psql:primary,myapp[0-4]:1,myapp[5-9]:2'
> 
> app_name_redirect_preference_list 'psql:primary,myapp[0-4]:1,myapp[5-9]:standby'
> 
>> 2) database
>> 
>> I propose a directive name "database_redirect_preference_list" for
>> this purpose. Proposed syntax is:
>> 
>> database1:node_id1,database2:node_id2,database3:node_id3...
>> 
>> Regular expression can be used for database. node_idN is same as above.
>> 
>> Example:
>> 
>> database_redirect_preference_list 'postgres:primary,mydb[0-4]:1,mydb[5-9]:2'
>> 
>> 3) what if "app_name_redirect_preference_list" and
>> database_redirect_preference_list conflicts each other?
>> 
>> What if clients application name is "psql" and database is "mydb0"?
>> Probably we should take priority application name over database
>> because database is more "broad" specification. So in this case the
>> SELECTs will be sent to primary node, rather than node 1.
>> 
>> Comments and suggestions are welcome.
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp