Re: performance problem of Failover Datasource? - Mailing list pgsql-jdbc

Hmm, there's some neat stuff in there, slave-only, slavefirst, etc.

But could you (and perhaps Mikko Tiihonen who originally proposed the
"Simple connection failover support") remind the rest of us why we want
this complexity inside the pgjdbc driver, rather than in a more robust and
featureful layer like pgpool-II?

At first glance, there are a couple of issues:

1. Double-Checked Locking in reportHostStatus, which is bad form

2. Synchronized code in a subclass that locks the base class

3. No need for 'volatile' if you're also using 'synchronized'

Taking a step back, it seems you have implemented a DNS-like static
(JVM-global) helper which performs lazy-caching of information about
servers. I would argue PGJDBC itself should only do simple single-host
connections, but perhaps provide a well-documented HostChooser interface
and a JVM-global (static) method such as Driver.setHostChooser(), similar
to Driver.setLogLevel(), so that applicaitons that need to override the
default "DNS lookups" (or "host choosing") may do so.

Applications that want the load balancing would use something like
"host=myvirtualpool" which would would obviously fail unless the you've
installed some sort of LoadBalanceHostChooser, which knows about all the
"realservers" that comprise the "myvirtualpool" and their
master/slave/OK/dead status. (Starts to sound more and more like pgpool-II
or other projects that already exist for this).

Side benefit is your LoadBalanceHostChooser could be designed to do
"eager" connection probing on worker threads so that when an application
thread needs a PGJDBC connection, you would avoid any of the slow
connection / dead server issues you were originally trying to solve.


On Mon, 24 Dec 2012, Chen Huajun wrote:

> Hi
>
> I have made a new patch(with my test). Please give a look.
> It support the following features
> 1) performance improve for fail over by avoiding dead hosts.
> 2) simple load balance by picking up the first host from multiple valid hosts
> randomly.
> 3) ability of choosing master or slave to connect to.
>
> And in the patch, three connection parameters were added.
>
> targetServerType = String
> Specifies what kind of server to connect.The value should be one of the
> following:
> any
> master
> slave
> slavefirst (Try connecting to the slaves first.If failed try the master)
> The default is 'any'.
>
> enableLoadBalance = boolean
> Enable or disable load balance when multiple hosts were specified;If load
> balance is enabled,specified multiple hosts will be picked up randomly.
> The default is false.
>
> failedHostCheckPeriod = int
> Specifies period(seconds) to check whether the failed hosts had been
> repaired, when load balance is enabled; 0 means never check.
> The default is 600 seconds.
>
>
> (2012/12/18 8:05), Chen Huajun wrote:
>>
>> Thanks for you advise.
>> I will try to made a new patch and add load balance supporting.
>>
>>
>
> --
> Best Regards,
> Chen Huajun
>
>