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

Bo Peng pengbo at sraoss.co.jp
Thu Aug 9 17:12:53 JST 2018


Ishii-san,

> 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?

Thank you for test this parameter.
Yes, I think we should add this to documetation.

On Thu, 09 Aug 2018 14:18:23 +0900 (JST)
Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> 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(-)
> > 
> _______________________________________________
> pgpool-hackers mailing list
> pgpool-hackers at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-hackers
> 


-- 
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS, Inc. Japan



More information about the pgpool-hackers mailing list