[pgpool-hackers: 3359] Re: [pgpool-committers: 5948] pgpool: Multiple performance enhancements especially for of the large

[Tatsuo Ishii]

Thank you Usama!

I have updated the 4.1 release note to reflect this patch.
Compiled HTML files can be seen at:
http://tatsuo-ishii.github.io/pgpool-II/current/release-4-1.html

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

From: Muhammad Usama <m.usama at gmail.com>
Subject: [pgpool-committers: 5948] pgpool: Multiple performance enhancements especially for of the large
Date: Thu, 01 Aug 2019 20:27:36 +0000
Message-ID: <E1htHfk-0006TZ-63 at gothos.postgresql.org>

> Multiple performance enhancements especially for of the large
> INSERT and UPDATE statements
> 
> Pgpool-II only needs very little information, especially for the INSERT and
> UPDATE statements to decide where it needs to send the query.
> For example: In master-slave mode, for the INSERT statements Pgpool-II only
> requires the relation name referenced in the statement while it doesn't care
> much about the column values and other parameters. But since the parser we use
> in Pgpool-II is taken from PostgreSQL source which parses the complete query
> including the value lists which seems harmless for smaller statements but in
> case of INSERT and UPDATE with lots of column values and large data in value
> items, consumes significant time.
> 
> So the idea here is to short circuit the INSERT and UPDATE statement parsing as
> soon as we have the required information. For that purpose, the commit adds the
> second minimal parser that gets invoked in master-slave mode and tries to
> extract the performance for large INSERT and UPDATE statements.
> 
> Apart from the second parser addition, following changes aiming towards the
> performance enhancements are also part of the commit.
> 
> 1-Some of the if statements in pool_where_to_send() function are re-arranged to
> make sure the more expensive functions calls, pattern_compare()
> and pool_has_function_call() should only be made when they are
> absolutely necessary.
> 
> 2- Eliminates the raw_parser() calls in case of un-recognized queries. Instead
> of invoking the parser on "dummy read" and "dummy write" statements, the commit
> adds the functions to return the pre-built parse_trees for these dummy queries.
> 
> 3-- strlen() call is removed from scanner_init() function and is passed to it
> as an argument. The reason being we already have the query length in most cases
> before invoking the parser so why waste CPU cycles on it. Again this becomes
> significant in case of large query strings.
> 
> 4- Removes some of the unnecessary calls of pool_is_likely_select() function.
> 
> Branch
> ------
> master
> 
> Details
> -------
> https://git.postgresql.org/gitweb?p=pgpool2.git;a=commitdiff;h=310c5c4a289cbe6cee01abef7d2e7bc3550944fb
> 
> Modified Files
> --------------
> Makefile.in                                        |     1 +
> configure                                          |    59 +
> configure.ac                                       |    12 +
> doc.ja/Makefile.in                                 |     1 +
> doc.ja/src/Makefile.in                             |     1 +
> doc.ja/src/sgml/Makefile.in                        |     1 +
> doc/Makefile.in                                    |     1 +
> doc/src/Makefile.in                                |     1 +
> doc/src/sgml/Makefile.in                           |     1 +
> src/Makefile.in                                    |     1 +
> src/context/pool_query_context.c                   |    42 +-
> src/include/Makefile.in                            |     1 +
> src/include/parser/gramparse.h                     |     6 +
> src/include/parser/parser.h                        |     5 +-
> src/include/parser/scanner.h                       |     1 +
> src/include/protocol/pool_proto_modules.h          |     2 -
> src/libs/Makefile.in                               |     1 +
> src/libs/pcp/Makefile.in                           |     1 +
> src/parser/Makefile.am                             |    10 +-
> src/parser/Makefile.in                             |    37 +-
> src/parser/README                                  |    49 +
> src/parser/gram.c                                  |  3255 +-
> src/parser/gram.y                                  |     7 +-
> src/parser/gram_minimal.c                          | 46971 +++++++++++++++++++
> src/parser/gram_minimal.h                          |  1053 +
> src/parser/gram_minimal.y                          | 16403 +++++++
> src/parser/gram_template.y                         | 16492 +++++++
> src/parser/parser.c                                |    84 +-
> src/parser/scan.c                                  |     2 +-
> src/parser/scan.l                                  |     2 +-
> src/protocol/pool_process_query.c                  |     2 +-
> src/protocol/pool_proto_modules.c                  |    35 +-
> .../tests/010.rewrite_timestamp/timestamp/main.c   |     2 +-
> .../regression/tests/017.node_0_is_down/test.sh    |     4 +-
> src/tools/Makefile.in                              |     1 +
> src/tools/pcp/Makefile.in                          |     1 +
> src/tools/pgenc/Makefile.in                        |     1 +
> src/tools/pgmd5/Makefile.in                        |     1 +
> src/tools/pgproto/Makefile.in                      |     1 +
> src/watchdog/Makefile.in                           |     1 +
> 40 files changed, 82852 insertions(+), 1700 deletions(-)
>