[pgpool-general: 304] Re: Trouble with connection during long/large data queries

[Tatsuo Ishii]

What pgpoo version are you using?

Also if you disable SSL between pgpool and PostgreSQL, do you see any
difference?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> We are using pgpool in front of a postrgesql 9.1 database and,
> generally, we are experiencing tremendous improvement in performance
> (and even stability) over accessing the database directly.  We are
> seeing one behavior, however, which is troubling: long queries which
> produce much resulting data seem to kill (or are killed by) the pgpool
> process handling the connection.  By long/large, I mean connections
> lasting ninety minutes or more (often comprising just a handful of
> long running queries) and returning >1GB of data.  Specifically, this
> is happening when querying (and returning) large, global sets of
> geographic data (which are not conducive to being run as smaller,
> independent queries), though we can reproduce this problem with any
> query taking sufficient time and returning sufficient data.
> 
> The workaround is to run these queries against the database directly,
> bypassing pgpool.  We would, however, like to require nearly all
> access to the database to go through pgpool, restricting direct access
> to things of an administrative nature.
> 
> Ideas, advice, and criticism welcome, thanks,
> 
> Joe Barnett
> 
> --- Errors and logs follow ---
> 
> ### Error returned when connection goes away (from Ruby script):
> 
> (with SSL enabled)
> ./bin/shapes.rb:150:in `exec': SSL SYSCALL error: EOF detected
> (PG::Error)
> 
> (with SSL disabled)
> ./bin/shapes.rb:150:in `exec': PG::Error
> 
> ### Excerpts from pgpool.log:
> 
> 2012-03-26 13:16:35 LOG: pid 14860: statement: SELECT
> max(octet_length(hist_file_path::text)) from temp_shapes_daily
> 2012-03-26 13:16:35 LOG: pid 14860: DB node id: 0 backend pid: 16005
> statement: SELECT max(octet_length(hist_file_path::text)) from
> temp_shapes_daily
> 2012-03-26 13:16:36 LOG: pid 14860: statement: SELECT
> max(octet_length(hist_file_name::text)) from temp_shapes_daily
> 2012-03-26 13:16:36 LOG: pid 14860: DB node id: 0 backend pid: 16005
> statement: SELECT max(octet_length(hist_file_name::text)) from
> temp_shapes_daily
> 2012-03-26 13:16:37 LOG: pid 14860: statement: SELECT *
>                         FROM temp_shapes_daily
>                         WHERE mission_phase_name = 'COMMISSIONING'
>                         AND original_product_id LIKE 'nac%'
> 
> 2012-03-26 13:16:37 LOG: pid 14860: DB node id: 0 backend pid: 16005
> statement: SELECT *
>                         FROM temp_shapes_daily
>                         WHERE mission_phase_name = 'COMMISSIONING'
>                         AND original_product_id LIKE 'nac%'
> 
> 2012-03-26 13:17:28 LOG: pid 14860: statement: SELECT *
>                         FROM temp_shapes_daily
>                         WHERE mission_phase_name = 'COMMISSIONING'
>                         AND original_product_id LIKE 'wac%'
> 
> 2012-03-26 13:17:28 LOG: pid 14860: DB node id: 0 backend pid: 16005
> statement: SELECT *
>                         FROM temp_shapes_daily
>                         WHERE mission_phase_name = 'COMMISSIONING'
>                         AND original_product_id LIKE 'wac%'
> 
> 2012-03-26 13:17:33 LOG: pid 14846: connection received:
> host=localhost port=39636
> 2012-03-26 13:18:08 LOG: pid 14860: statement: SELECT *
>                         FROM temp_shapes_daily
>                         WHERE mission_phase_name = 'NOMINAL MISSION'
>                         AND original_product_id LIKE 'nac%'
> 
> 2012-03-26 13:18:08 LOG: pid 14860: DB node id: 0 backend pid: 16005
> statement: SELECT *
>                         FROM temp_shapes_daily
>                         WHERE mission_phase_name = 'NOMINAL MISSION'
>                         AND original_product_id LIKE 'nac%'
> 
> 2012-03-26 13:22:38 LOG: pid 14860: statement: SELECT *
>                         FROM temp_shapes_daily
>                         WHERE mission_phase_name = 'NOMINAL MISSION'
>                         AND original_product_id LIKE 'wac%'
> 
> 2012-03-26 13:22:38 LOG: pid 14860: DB node id: 0 backend pid: 16005
> statement: SELECT *
>                         FROM temp_shapes_daily
>                         WHERE mission_phase_name = 'NOMINAL MISSION'
>                         AND original_product_id LIKE 'wac%'
> 
> 2012-03-26 13:22:43 ERROR: pid 14860: pool_read2: EOF encountered with
> backend
> 2012-03-26 13:22:43 LOG: pid 14860: do_child: exits with status 1 due
> to error
> 
> (I have pgpool logs with DEBUG info as well, but they are very large
> (and I am not certain how much useful information they contain).  I
> can certainly provide excerpts from those logs if anyone thinks they
> are useful--perhaps suggest things for which I might look.)
> 
> ### Excerpts from postgresql log:
> 
> LOG:  could not send data to client: Connection reset by peer
> STATEMENT:                      SELECT *
>                                 FROM temp_shapes_daily
>                                 WHERE mission_phase_name = 'NOMINAL MISSION'
>                                 AND original_product_id LIKE 'wac%'
> 
> LOG:  could not send data to client: Broken pipe
> STATEMENT:                      SELECT *
>                                 FROM temp_shapes_daily
>                                 WHERE mission_phase_name = 'NOMINAL MISSION'
>                                 AND original_product_id LIKE 'wac%'
> 
> LOG:  SSL error: bad write retry
> LOG:  could not send data to client: Connection reset by peer
> 
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general