Re: [Sqlgrey-users] Resolution of my DB problem

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

Chris Hanlon wrote the following on 21.02.2005 22:12 :

> Indexing the connect table's ip_addr field was a good lead to solving 
> my performance issue.  But it actually took a little bit more then 
> that.  And that might be because I'm in a somewhat different situation 
> than many - in that I have a pretty limited range of sender IP 
> addresses.  My MX record for our domain actually points to a server 
> running MXLogic's spam filtering service.  All our mail traffic first 
> passes through their servers, then their servers forward it to my 
> internal filtering box, which is where I have SQLGrey installed.


Doesn't it defeat the whole purpose of greylisting ? If you have a real 
MTA sending you all of the incoming mails greylisting will only delay 
mails that will come back.
It could be slightly useful if you use RBLs, as you may delay them 
enough to have the RBLs becoming aware of them between the first and 
second message transfer attempts.

>   Now...that server gets a few connections that *don't* connect to the 
> MX listed mail server...but I think we can safely assume those are not 
> legitimate mail transfers.


If you can assume that, you should simply reject these connections. One 
way to study these "rogue" connections would be to start with a fresh 
SQLgrey database and whitelist your servers (see below), then you'll 
have a clean view in the SQLgrey database of who sends what (you may 
want to choose the 'full' greylisting algorithm to get the exact IP 
adresses).

>   But for the most part, as far as sqlgrey can see, our mail 
> originates entirely on one of about 15 servers, all on the same class C.
>  


In your case, I'll probably add each of them in a new 
/etc/sqlgrey/clients_ip_whitelist.local file (look into the existing 
clients_ip_whitelist in the same directory for examples, but don't 
modify this later one, create your own .local).
This will instantly (at file saving, since SQLgrey monitors this very 
file) stop SQLgrey from needlessly greylisting everything coming from 
them and stop hammering the database.

> I took your advice and added an index to the ip_addr column, and it 
> did help - but only until I got up to about 50K items.  What *does* 
> seem to have worked, though, was building an index of the ip_addr, 
> sender_name and rcpt columns.


Understandable as everything comes through only 15 adresses the ip_addr 
index only give you a limited benefit. The index you used shouldn't be 
needed on more "conventional" configurations. When you get so much 
connections from a single address you are either under DoS or should 
whitelist the address directly.

>   I've been running at over 160K items in the connect table with no 
> problems.


160K? Wow! The periodic cleanup may take some time... Look for "Spent 
<n>s cleaning ..." in your logs. SQLgrey currently delays incoming mails 
while cleaning (this will be dealt with during 1.5.x devs).

To sum it up, if everything comes from real MTAs, you probably don't 
need greylisting at all (or are you benchmarking SQLgrey before letting 
MXLogic know that you don't need their services anymore? :-) )

Lionel.