Re: [Sqlgrey-users] Frequent disconnect/reconnect issues

[Alex <mys...@gm...>]

Hi,

>>> I don't have answers to all your questions but could you explain why you
>>> are using db_cluster and how/why you set the replication to 5 MySQL
>>> databases? This seems overkill and needlessly complex.
>> It's actually three databases - two of them have multiple CNAMEs that
>> appear to be chosen on occasion.
>
> You shouldn't use 5 entries in read_hosts but only 2 or 3 depending on
> what kind of load distribution between your databases you want. But it's
> probably not relevant in your case.

It's not five. It's really only two, unless you count itself. The
other two are localhost and a CNAME entry.

>> I'm using db_cluster because I have three mail servers for the same
>> domain with the same weight,
>
> Do you have system load problems which mandates you having 3 mail
> servers? It's usually not a good idea to have several active mail
> servers unless you really need them.

Yes, for load and redundancy reasons. The three servers receive about
400k messages per day, at peak.

> It's a general recommendation but
> in the case of SQLgrey some spammers try to send mails to all servers at
> the same time and it can confuse SQLgrey which tries to insert 3 times
> the same entry because each instance can't known that the 2 others are
> trying too. In this case SQLgrey will handle this as a database error
> (because it can't guess if it's not one) and try to reconnect. With a
> single server this is not possible.

Okay, that's interesting. And you believe it's possible that's what
happening here?

>>  resulting in a reconnect potentially
>> reaching a different mail server, otherwise resulting in another 5
>> minute greylisting delay.
>>
>> I thought that was the correct way to address this situation?
>
> No: db_cluster is only meant to solve rare performance issues, has some
> drawbacks (replication delay of most replication solutions means slaves
> are out of sync with the master which can confuse SQLgrey in some cases)
> and requires a bit of DBA work to setup properly. Unless your master
> database is so loaded that SQLgrey starts to timeout you shouldn't use
> dbcluster.

Except if the master becomes inaccessible, no?

>>> Can you reproduce your problem with a single database and db_cluster off?
>> For the reason described above, I don't think it would be a good idea
>> to disconnect them from each other.
>
> I don't understand what you mean by "disconnect them from each other".
> Disconnecting databases from each other? Disconnecting mail servers from
> database servers?
>
> Your database should already be kept in sync by MySQL replication
> (although I learned not to trust it myself) so disabling db_cluster
> shouldn't have any impact on the database, you will simply write and
> read from your single master (ns1.example.com) instead of writing to it
> and reading from read_hosts. The slaves won't be used but you can keep
> them if you really need to re-enable db_cluster later.

I think I've been confusing MySQL replication and dbcluster as being
something that must be used together.

> Disconnecting mail servers from the database servers (other than
> ns1.example.com) should have zero impact: they are supposed to have the
> same data anyway (in fact that's not true there's a replication delay
> which can cause problems so disabling db_cluster can actually solve
> problems there).

Okay, great. I'll disable dbcluster and follow up. I was following the README:

    If [mail1] gets a new request, it'll greylist, respond "450" and
stick client into the "connect" table.
    Now heres the problem. Client backs off, and comes back later to
try again, but there is no
    garantee it'll get [mail1] again. Infact, if it doesnt, the
greylisting will happen all over. The solution
    is to use DBClustering. Each mailserver STILL has its own
SQL-server, but we add a master-sql
    and let the local sql-servers be replication slaves:

I don't have the load balancer, as shown in the drawing in the README.
I guess I don't fully understand the purpose of db_cluster.

Thanks so much,
Alex