Re: [Sqlgrey-users] Suggestions for improvement

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

Rene Joergensen wrote the following on 12/16/04 12:25 :

>Hi there.
>
>I'm working at an ISP, currently using Postgrey, but looking for an
>alternative due to the limitations in Postgrey, cleaning db takes 5-10
>minutes each night, in which Postfix is rejecting legitimate mail with
>450 errors.
>
>I've seen you expressing worries about the current size of SQLgrey, a
>place to start would be putting DB creation/alteration into a seperate
>perl-script, it's only done once, and running the seperate perlscript
>on installation/upgrade doesn't take much effort. Not much performance
>gained, but it sure would be prettier :)
>  
>

As SQLgrey is required to verify the database version at startup (and if 
you look at the code closely, you'll see that I paid in messy code the 
mistake of not storing the layout version from the start) I'm inclined 
to let SQLgrey handle the upgrade too.
What I was planning is to move out old update code. When SQLgrey 1.6 
will be out, the database layout will probably at version 2 instead of 1 
today in 1.4. I will then drop support for version 0 to version 1 
conversion, only supporting v1 to v2 conversion. In the less frequent 
case of a layout at version 0 SQLgrey will "die" with a message advising 
to use an external script to upgrade from version 0 to 1.

>We're currently receiving around 1.2 million mails a day, which Postgrey
>handles fine. But i like the idea about the auto-whitelist in SQLgrey,
>and the possibility of getting rid of the 5-10 minutes downtime a day.
>The ongoing cleaning that SQLgrey does (3 deletes pr. mail iirc) would
>mean 3+ million extra queries a day, which is quite a lot.
>
>Cleaning could be done in 3 (or more) ways:
>
>- Timed interval, eg. once every 2 hours.
>  
>

Supported in the code. Deactivated by mistake.

>- Interval based on queries, eg. once every 2000 queries
>  
>

Maybe. I'll have to think about that one.

>- Cleaning from cron, calling sqlgrey with --db-clean (or seperate
>  perlscript (but that would mean more code to maintain)), this is in my
>  eyes a pretty flexible way, and non-blocking without rewriting SQLgrey
>  to use Net::Server::Prefork, and then just tell SQLgrey that cleaning
>  is done externally, and it doesn't have to worry about it. Default
>  should be one of the other ways, to avoid people who doesn't RTFM
>  complaining about large databases.
>  
>

Currently I'm inclined to make the interval configurable in 1.4 with a 
more sensible default that "0" and store a last_cleanup timestamp in 
database in 1.6 to easily support multiple instances accessing the same 
database.

>Limiting the number of queries is always good, haven't really looked
>much into the queries, and i'm sure you're doing what you can to limit
>them.
>
>  
>

Yep, the order of the checks is designed to minimize the number of 
queries done to the database.

>The missing indexes could also become a problem as the connect-table
>probably will contain 500,000-1,000,000 rows, which is the current
>values for Postgrey, and the from_awl contains around 700,000 records.
>These values will probably be smaller when using autowhitelisting, but
>the tables will still need indexes to avoid full tablescans.
>  
>

If you could test how much time each index on the connect table saves, 
that would help me decide which ones to create.
See the FAQ file for questions I'm waiting answers for in order to 
decide on the indices. The db_performance_reports present the only 
report I have (not installed, you'll have to fetch it from the tar.bz2 
archive or the CVS tree).

>I've been close to testing it in production a couple of times, but
>chickened out each time as i'm in the process of moving and therefore is
>unable to keep an eye on it due to lack of IP in the new apartment.
>  
>

Wise decision. I would have reports of users with your level of trafic I 
could have said the default SQLgrey install would (not) have been suited 
for you, but you may be the first one trying with this kind of trafic, 
so it's better to be able to monitor the sytem for at least the first day.

I think I'll end up writing some perl scripts that simulate mail trafic 
by calling the policy daemon with adjustable trafic patterns (% of non 
reconnecting zombies, distribution of the reconnect delay of real MTAs, 
distribution of number of source IPs for each domain, distribution of 
number of e-mail addresses per domain for example) and rates. This 
should help people test SQLgrey *before* starting to greylist with it. 
It will help me fine tune indices on different databases too.
Another entry in my TODO list... Probably for the 1.4 releases.

Best regards,

Lionel.