[pgpool-general: 305] Re: Trouble with connection during long/large data queries
Matt Wise
matt at nextdoor.com
Tue Mar 27 10:03:07 JST 2012
I believe we may be seeing this problem as well, but with much shorter querytimes. We have a few jobs that run database transactions for up to 15minutes or so. They will do 500-1000 individual database queries, that range from 1-30 seconds in length. We continue to see failures when running through PGPool and end up having to run these through our legacy PGBouncer service directly on the database hosts.
We DO use SSL between PGPool and the backend PostgreSQL databases. We also use SSL in between PGPool and the clients (which is a local copy of PGBouncer on each client), but we do that with Stunnel so its transparent to the applications.
—Matt
On Mar 26, 2012, at 5:07 PM, Tatsuo Ishii wrote:
> 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
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general
More information about the pgpool-general
mailing list