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

[Lionel B. <lio...@bo...>]

Le 20/08/2015 03:09, Alex a écrit :
> 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.

Then you shouldn't have 5 entries in read_hosts as I already said.
Seriously if you don't understand why 5 entries for 3 servers can cause
problems you shouldn't even think about touching db_cluster: you are
clearly using it wrong.

>
>>> 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.

This is only <5 messages per second. Hardly something a single server
can't handle (unless you use costly antispam measures that you can't
delegate to backend servers).

This kind of redundancy is mostly superfluous with SMTP: if you have a
spare server ready to replace your mail server (ie: affect it the public
IP of your single MX to it or redirect it if you have some kind of
reverse proxy), you only have to start it to get the messages queued at
the origin delivered to it.

>> 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?

It's a known problem so yes it's possible.

>
>>>  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?

Even with db_cluster on if the master becomes inaccessible SQLgrey stops
greylisting and let everything pass. If it can't write to the database
there's no way it can do its job.

>
>>>> 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.

I don't know why you think this after our exchanges. They don't have to
work *together* but if the replication doesn't work properly SQLgrey
with db_cluster will surely fail to work properly.

>> 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.

db_cluster support wasn't written by me and I never used it so I just
read its README again to check.
I think it doesn't really explain when you should use it properly, just
ignore any advice in it not linked to database load problems.

Best regards,

Lionel