[pgpool-hackers: 2925] Re: [pgpool-committers: 4777] pgpool: Add new feature to enable specifying SQL patterns lists that sh

[Tatsuo Ishii]

Peng,

I have done a quick performance test when this feature is enabled,
because with this feature every single query is processed by a regular
expression checking. The pattern I have tested is a small one:

black_query_pattern_list = 'select upper\(\'.*\'\)'

when the feature is turned off, I got about 21033 TPS.

$ pgbench -p 11000 -c 10 -T 300 -S test
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
duration: 300 s
number of transactions actually processed: 6310059
latency average = 0.475 ms
tps = 21033.471245 (including connections establishing)
tps = 21033.880210 (excluding connections establishing)

If the feature is turned on, I got abiut:
tps = 20755.725205 (excluding connections establishing) - first try
tps = 20608.044662 (excluding connections establishing) - second try

This is 98-99% of the original performance, so I could say like "with
this feature subtle performance regression is expected (1-2%, might
vary depending on the query or pattern)".

Maybe we should add something like this to the docs? What do you think, Peng?

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

> Add new feature to enable specifying SQL patterns lists that should not be load-balanced.
> 
> Even though currently we can do this by adding the
> /*NO LOAD BALANCE*/ comment to queries, this requires
> modifying application codes and this is not always
> possible.
> 
> This feature enables specifying SQL patterns lists
> that should not be load-balanced.
> 
> -------------
> New parameter
> -------------
> black_query_pattern_list = ''
> 
> You can specify a semicolon separated list of SQL patterns
> that should be sent to primary node only.
> 
> SQL that matched patterns specified in this list are not load balanced.
> Only Maste Slave mode is supported.
> 
> You can use regular expression to match SQL patterns,
> to which ^ and $ are automatically added.
> When using characters such as "'", ";" or "*" in SQL patterns,
> you need to escape them using "\".
> 
> Branch
> ------
> master
> 
> Details
> -------
> https://git.postgresql.org/gitweb?p=pgpool2.git;a=commitdiff;h=83906d1d5024e5f68ef9dd9dff9fda7f5720be4e
> 
> Modified Files
> --------------
> doc.ja/src/sgml/loadbalance.sgml                   |  94 +++++++++++++++++
> doc/src/sgml/loadbalance.sgml                      |  65 ++++++++++++
> src/config/pool_config.l                           |  31 +++++-
> src/config/pool_config_variables.c                 | 111 ++++++++++++++++++++-
> src/context/pool_query_context.c                   |  12 ++-
> src/include/pool_config.h                          |   7 ++
> src/sample/pgpool.conf.sample                      |   7 +-
> src/sample/pgpool.conf.sample-logical              |   6 ++
> src/sample/pgpool.conf.sample-master-slave         |   6 ++
> src/sample/pgpool.conf.sample-replication          |   6 ++
> src/sample/pgpool.conf.sample-stream               |   6 ++
> src/test/regression/tests/001.load_balance/test.sh |  45 +++++++++
> src/utils/pool_process_reporting.c                 |  13 +++
> src/utils/pool_select_walker.c                     |   6 ++
> 14 files changed, 410 insertions(+), 5 deletions(-)
>