[pgpool-general: 637] Re: exclude EXPLAIN from the pgpool.
Tatsuo Ishii
ishii at postgresql.org
Thu Jun 14 11:36:10 JST 2012
> Dear Tatsuo,
>
> just another question, I saw in a realease notes in 2.3 version of pg-pool
> there is this enanchement:
>
> " - EXPLAIN and EXPLAIN ANALYZE for SELECT query are now load
> balanced. This will prevent unwanted kind mismatch errors when
>
> EXPLAIN produces slightly different plan "
> This is the error we got, so I would like to know how different explains
> should be to have a missmatch?
It turns out that there's a logic bug with the part. Please test
included patch. With the patch, pgpool should work as advertized
above.
Please note that EXPLAIN ANALYZE SELECT is not load balanced if it
calls writing functions specified either white_function_list or
black_function_list. For example, EXPLAIN ANALYZE SELECT
nextval('seq_name') should not be load balanced in your case, while
EXPLAIN ANALYZE SELECT current_timestamp can be load balanced.
> Is this feature tunable by configuration to prevent missmacth error?
No.
> If the only way to change this behaviour is to change code source, should
> you tell us how to fix and we try to implement the solution?
>
> Let us know.
> Best Regards and many thanks for your support.
> Matteo Serafino and Nicolas Amato
>
>
>
>
>
> 2012/6/13 Nicolas Amato <nicolas.amato at autoxy.it>
>
>> I have add to black_function_list = 'nextval,setval,to_tsquery,to_tsvector'
>>
>> but there is an error again. (ERROR: kind mismatch among backends)
>>
>>
>>
>>
>> 2012/6/13 Tatsuo Ishii <ishii at postgresql.org>
>>
>>> Don't you use to_tsquery()?
>>>
>>> But you have:
>>> >> >> > white_function_list = ''
>>> >> >> > black_function_list = 'nextval,setval'
>>>
>>> So the function should not prevent load balance the EXPLAIN.
>>> I will look into this...
>>> --
>>> Tatsuo Ishii
>>> SRA OSS, Inc. Japan
>>> English: http://www.sraoss.co.jp/index_en.php
>>> Japanese: http://www.sraoss.co.jp
>>>
>>> > No, the explains don't use function.
>>> >
>>> > This is the explain:
>>> >
>>> >
>>> > EXPLAIN SELECT a.adid AS a__adid, a.md5 AS a__md5, a.site AS a__site,
>>> > a.published AS a__published, a.imported AS a__imported, a.year AS
>>> a__year,
>>> > a.mileage AS a__mileage, a.price AS a__price, a.make AS a__make,
>>> a.model AS
>>> > a__model, a.location AS a__location, a.title AS a__title, a.description
>>> AS
>>> > a__description, a.specials AS a__specials, a.source_url AS
>>> a__source_url,
>>> > a.image_url AS a__image_url, a.num_images AS a__num_images, a.version AS
>>> > a__version, a.fuel AS a__fuel, a.colour AS a__colour, a.transmission AS
>>> > a__transmission, a.body AS a__body, a.category AS a__category
>>> > FROM insertion a
>>> > WHERE (a.make = 'fiat' AND a.i_fulltext @@ to_tsquery('italian',
>>> 'doblo')
>>> > AND a.adid NOT IN ('20120423.041004.362964') AND a.i_fulltext @@
>>> > to_tsquery('italian', '(metano)') AND a.i_location @@ 'puglia'::tsquery
>>> AND
>>> > a.published >= '2012/06/11') ORDER BY a.published DESC NULLS LAST LIMIT
>>> > 1000;
>>> >
>>> >
>>> > Result ---- Server A -------
>>> >
>>> >
>>> > Limit (cost=713.46..713.46 rows=1 width=952)
>>> > -> Sort (cost=713.46..713.46 rows=1 width=952)
>>> > Sort Key: published
>>> > -> Bitmap Heap Scan on insertion a (cost=669.45..713.45rows=1
>>> > width=952)
>>> > Recheck Cond: ((i_fulltext @@ '''dobl'''::tsquery) AND
>>> > (i_fulltext @@ '''met'''::tsquery) AND (i_location @@
>>> > '''puglia'''::tsquery))
>>> > Filter: (((adid)::text <> '20120423.041004.362964'::text)
>>> > AND (published >= '2012-06-11 00:00:00'::timestamp without time zone)
>>> AND
>>> > ((make)::text = 'fiat'::text))
>>> > -> BitmapAnd (cost=669.45..669.45 rows=11 width=0)
>>> > -> Bitmap Index Scan on insertion_i_fulltext_idx
>>> > (cost=0.00..37.84 rows=184 width=0)
>>> > Index Cond: ((i_fulltext @@
>>> '''dobl'''::tsquery)
>>> > AND (i_fulltext @@ '''met'''::tsquery))
>>> > -> Bitmap Index Scan on insertion_i_location_idx
>>> > (cost=0.00..631.36 rows=59648 width=0)
>>> > Index Cond: (i_location @@
>>> '''puglia'''::tsquery)
>>> > (11 rows)
>>> >
>>> >
>>> >
>>> > Result ---- Server B -------
>>> >
>>> >
>>> > Limit (cost=678.00..678.00 rows=1 width=957)
>>> > -> Sort (cost=678.00..678.00 rows=1 width=957)
>>> > Sort Key: published
>>> > -> Bitmap Heap Scan on insertion a (cost=37.63..677.99 rows=1
>>> > width=957)
>>> > Recheck Cond: ((i_fulltext @@ '''dobl'''::tsquery) AND
>>> > (i_fulltext @@ '''met'''::tsquery))
>>> > Filter: (((adid)::text <> '20120423.041004.362964'::text)
>>> > AND (i_location @@ '''puglia'''::tsquery) AND (published >= '2012-06-11
>>> > 00:00:00'::timestamp without time zone) AND ((make)::text =
>>> 'fiat'::text))
>>> > -> Bitmap Index Scan on insertion_i_fulltext_idx
>>> > (cost=0.00..37.63 rows=163 width=0)
>>> > Index Cond: ((i_fulltext @@ '''dobl'''::tsquery)
>>> AND
>>> > (i_fulltext @@ '''met'''::tsquery))
>>> > (8 righe)
>>> >
>>> >
>>> > I will resolve this problem (different result) soon. It's possible
>>> execute
>>> > explain only server A or only server B ?
>>> >
>>> > Regards
>>> >
>>> >
>>> > 2012/6/13 Tatsuo Ishii <ishii at postgresql.org>
>>> >
>>> >> Do those failed "complex" EXPLAINs contain funcation calls, especially
>>> >> nextval or setval? If so, I think it's the case where pgpool has a
>>> >> logical error I have just found. Pgpool first checks if the query is
>>> >> EXPLAIN (and not EXPLAIN ANALYZE). If so, it's ok to load
>>> >> balance. Problem is the next step. Pgpool looks into the EXPLAIN and
>>> >> if it finds a function call, it sends to all PostgreSQL nodes because
>>> >> the function might modify database. Of course this is nonsense, since
>>> >> plain EXPLAIN does nothing to database...
>>> >> --
>>> >> Tatsuo Ishii
>>> >> SRA OSS, Inc. Japan
>>> >> English: http://www.sraoss.co.jp/index_en.php
>>> >> Japanese: http://www.sraoss.co.jp
>>> >>
>>> >> > No, a few Explain cause errors.
>>> >> > The trivial explain is ok. The complex explain causes this error :(
>>> >> > In fact, as I have seen, even if the table has the same row/index,
>>> the
>>> >> > explain gives differen results. I will resolve this soon.
>>> >> > I want that the explain gets only done on the master node. Only one
>>> node.
>>> >> > Does the configuration “replicate_explain = off” exist ???
>>> >> >
>>> >> > Thanks for your answer.
>>> >> >
>>> >> > Nicolas
>>> >> >
>>> >> > 2012/6/13 Tatsuo Ishii <ishii at postgresql.org>
>>> >> >
>>> >> >> Even trivial explain like "EXPLAIN SELECT 1" causes errors?
>>> >> >> --
>>> >> >> Tatsuo Ishii
>>> >> >> SRA OSS, Inc. Japan
>>> >> >> English: http://www.sraoss.co.jp/index_en.php
>>> >> >> Japanese: http://www.sraoss.co.jp
>>> >> >>
>>> >> >> > Hi,
>>> >> >> > I would like to know if I can exclude the command EXPLAIN from the
>>> >> >> pgpool.
>>> >> >> > It generates a lot of errors:
>>> >> >> >
>>> >> >> > 2012-06-13 09:35:11 ERROR: pid 15204: read_kind_from_backend: 1 th
>>> >> kind C
>>> >> >> > does not match with master or majority connection kind D
>>> >> >> > 2012-06-13 09:35:11 ERROR: pid 15204: kind mismatch among
>>> backends.
>>> >> >> > Possible last query was: "EXPLAIN SELECT ...... " kind details
>>> are:
>>> >> 0[D]
>>> >> >> > 1[C]
>>> >> >> > 2012-06-13 09:35:11 LOG: pid 15204: do_child: exits with status
>>> 1
>>> >> due
>>> >> >> to
>>> >> >> > error
>>> >> >> >
>>> >> >> > pool-II-3.1.3
>>> >> >> > PostgreSQL 9.1.2
>>> >> >> > both Server same Hardware.
>>> >> >> >
>>> >> >> > Pgpool configuration:
>>> >> >> >
>>> >> >> > listen_addresses = '*'
>>> >> >> > port = 5432
>>> >> >> > socket_dir = '/var/run/postgresql'
>>> >> >> > pcp_port = 9898
>>> >> >> > pcp_socket_dir = '/tmp'
>>> >> >> > backend_hostname0 = 'localhost'
>>> >> >> > backend_port0 = 5434
>>> >> >> > backend_weight0 = 1
>>> >> >> > backend_data_directory0 = '/var/lib/postgresql/9.1/main'
>>> >> >> > backend_flag0 = 'ALLOW_TO_FAILOVER'
>>> >> >> > backend_hostname1 = '172.16.0.1'
>>> >> >> > backend_port1 = 5434 #doveva essere a 5434
>>> >> >> > backend_weight1 = 1
>>> >> >> > backend_data_directory1 = '/var/lib/postgresql/9.1/main'
>>> >> >> > backend_flag1 = 'ALLOW_TO_FAILOVER'
>>> >> >> > enable_pool_hba = off
>>> >> >> > authentication_timeout = 60
>>> >> >> > # - SSL Connections -
>>> >> >> > num_init_children = 32
>>> >> >> > max_pool = 10
>>> >> >> > child_life_time = 300
>>> >> >> > child_max_connections = 0
>>> >> >> > connection_life_time = 0
>>> >> >> > client_idle_limit = 0
>>> >> >> > log_destination = 'stderr'
>>> >> >> > print_timestamp = on
>>> >> >> > log_connections = off
>>> >> >> > log_hostname = on
>>> >> >> > log_statement = off
>>> >> >> > log_per_node_statement = off
>>> >> >> > log_standby_delay = 'none'
>>> >> >> > syslog_facility = 'LOCAL0'
>>> >> >> > syslog_ident = 'pgpool'
>>> >> >> > debug_level = 0
>>> >> >> > pid_file_name = '/var/run/pgpool/pgpool.pid'
>>> >> >> > logdir = '/tmp'
>>> >> >> > connection_cache = on
>>> >> >> > reset_query_list = 'ABORT; DISCARD ALL'
>>> >> >> > replication_mode = on
>>> >> >> > replicate_select = off
>>> >> >> > insert_lock = on
>>> >> >> > lobj_lock_table = ''
>>> >> >> > replication_stop_on_mismatch = off
>>> >> >> > failover_if_affected_tuples_mismatch = off
>>> >> >> > load_balance_mode = off
>>> >> >> > ignore_leading_white_space = on
>>> >> >> > white_function_list = ''
>>> >> >> > black_function_list = 'nextval,setval'
>>> >> >> > master_slave_mode = off
>>> >> >> > sr_check_period = 0
>>> >> >> > sr_check_user = 'nobody'
>>> >> >> > sr_check_password = ''
>>> >> >> > delay_threshold = 0
>>> >> >> > follow_master_command = ''
>>> >> >> > parallel_mode = off
>>> >> >> > enable_query_cache = off
>>> >> >> > pgpool2_hostname = 'localhost'
>>> >> >> > system_db_hostname = 'localhost'
>>> >> >> > system_db_port = 5434
>>> >> >> > system_db_dbname = 'pgpool'
>>> >> >> > system_db_schema = 'pgpool_catalog'
>>> >> >> > system_db_user = 'pgpool'
>>> >> >> > system_db_password = ''
>>> >> >> > health_check_period = 0
>>> >> >> > health_check_timeout = 20
>>> >> >> > health_check_user = 'nobody'
>>> >> >> > health_check_password = ''
>>> >> >> >
>>> >> >> > ------------------------------------
>>> >> >> >
>>> >> >> > The answers about these questions are welcome.
>>> >> >> >
>>> >> >> > Best Regards
>>> >> >> >
>>> >> >> > Nicolas
>>> >> >>
>>> >>
>>>
>>
>>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: is_select_query.patch
Type: text/x-patch
Size: 1602 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20120614/07f77bcd/attachment.bin>
More information about the pgpool-general
mailing list