[Pgpool-general] Cancelled queries or empty results?

[Tatsuo Ishii]

> sorry for cross posting this - please the post in the hackers list.
> Thanks for the great work on pgpool-II !!!
> I have a hot streaming installation of PostgreSQL Master/Slave servers (9.0.2) 
> on two machines with OS X.
> 
> On the third machine, the web server, I wanted to use pgpool-II 3.0.1 for 
> load balancing and failover.
> The application uses Embperl/Modperl with DBI and DBD::pg.
> 
> While the load balancing basically works, there are queries that return empty 
> results, repeating them more often gives results - so i assumed these were 
> cancelled queries.

Are you sure that your queries are being canceled? If so, I think perl
would return errors and you should see error messages in the
PostgreSQL log.

My guess is there are too much replication delay. If my bet is
correct, you would want to set:

log_standby_delay = 'always'

to see how much the delay is. If there's too much delay, you have two
options:

1) Give more power to your standby server so that it quickly catches
   up the primary.

2) Make delay_threshold lower, rather than higer. This will redirect
   your SELECTs to the primary if replication delay is too much.

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

> However, changing parameters on the master and the slave did not cure the 
> problem.
> 
> Master:
> vacuum_defer_cleanup_age = 1000
> 
> Standby:
> max_standby_archive_delay = -1
> max_standby_streaming_delay = -1
> 
> There is not too much load on the machine at all - maybe 10-20 users at a time,
>  but the speedup would very much be appreciated.
> 
> Is there something I could do with pgpool that improves that behaviour?
> 
> Tried delay_threshold = 10000000 without success.
> 
> Please help me with your advice.
> 
> Cheers,
> Tom Spivey
> 
> _______________________________________________
> Pgpool-general mailing list
> Pgpool-general at pgfoundry.org
> http://pgfoundry.org/mailman/listinfo/pgpool-general