sqlgrey-users — Users of SQLgrey

Re: [Sqlgrey-users] Error installing under Slack.11

[John S. <ke...@gm...>]

Lionel Bouton <lionel-dev@...> writes:

> 
> Ok, I got it, you must be in the sqlgrey user's directory. Init scripts 
> deal with it on debian/gentoo/redhat and derived distributions. It's 
> probably a bug, sqlgrey should change its working directory itself. 
> Until we fix this, please launch sqlgrey after "cd /var/lib/sqlgrey"
> 
> Lionel
> 
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share your
> opinions on IT & business topics through brief surveys-and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> 

You're right. Just look:

login as: root
root@192.168.0.247's password:
Last login: Thu Mar  1 08:32:40 2007
Linux 2.6.17.8.

We may hope that machines will eventually compete with men in all purely
intellectual fields.  But which are the best ones to start with?  Many people
think that a very abstract activity, like the playing of chess, would be
best.  It can also be maintained that it is best to provide the machine with
the best sense organs that money can buy, and then teach it to understand
and speak English.
                -- Alan M. Turing

root@slack11:~# cd /var/lib/sqlgrey
root@slack11:/var/lib/sqlgrey# sqlgrey
2007/03/01-21:23:03 sqlgrey (type Net::Server::Multiplex) starting! pid(1900)
Binding to TCP port 2501 on host localhost
Setting gid to "104 104"
Setting uid to "1002"
perf: spent 0s cleaning: from_awl (0) domain_awl (0) connect (0)
conf: warning: /etc/sqlgrey/clients_ip_whitelist.local not found or unreadable
conf: warning: /etc/sqlgrey/clients_fqdn_whitelist.local not found or unreadable
2007/03/01-21:24:27 Server closing!
root@slack11:/var/lib/sqlgrey# sqlgrey -d
root@slack11:/var/lib/sqlgrey# lsof -i tcp:2501
COMMAND  PID    USER   FD   TYPE DEVICE SIZE NODE NAME
sqlgrey 1902 sqlgrey    5u  IPv4   4196       TCP localhost:rtsclient (LISTEN)
root@slack11:/var/lib/sqlgrey#

Re: [Sqlgrey-users] Performance: strict versus loose expiration time

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

Michael Storz wrote the following on 28.02.2007 18:27 :
> On Wed, 28 Feb 2007, Lionel Bouton wrote:
>
>   
>> I don't believe this is a good example, I believe you get a benefit from
>> the query cache only if the whole query (not only the prepared
>> statement) is exactly the same. So in the example above you'll need to
>> have the same timestamp AND the same sender_name, sender_domain, src.
>> Losing the timestamp don't bring much benefits, because the rate at
>> which the from_awl is updated (entries added, updated or deleted)
>> probably is far higher than the rate at which the sender+src comes back.
>>
>>     
>
> Lionel,
>
> I am not talking about normal operation. What I am concerned about are
> spam attacks, where you have to react as fast as possible.
>
>   

I see your point. Smoothing out load spikes is clearly a good goal. I'll 
eagerly await your bench results :-)

Lionel.

Re: [Sqlgrey-users] Error installing under Slack.11

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

Ok, I got it, you must be in the sqlgrey user's directory. Init scripts 
deal with it on debian/gentoo/redhat and derived distributions. It's 
probably a bug, sqlgrey should change its working directory itself. 
Until we fix this, please launch sqlgrey after "cd /var/lib/sqlgrey"

Lionel

Re: [Sqlgrey-users] Error installing under Slack.11

[John S. <ke...@gm...>]

Lionel Bouton <lionel-dev@...> writes:

> 
> John Smith wrote the following on 28.02.2007 15:34 :
> > (...)
> >  
> > postfix:x:102:
> > postdrop:x:103:
> > sqlgrey:x:104:
> > and in /etc/passwd:
> > postfix:x:1001:102:Postfix MTA:/var/spool/postfix:/bin/false
> > sqlgrey:x:1002:104::/var/lib/sqlgrey:
> >  
> > I even create manually /var/lib/sqlgrey.
> >  
> > Now, the problem:
> >  
> > root <at> slack11:/etc <mailto:root <at> slack11:/etc># sqlgrey
> > 2007/02/28-16:29:30 sqlgrey (type Net::Server::Multiplex) starting! 
> > pid(4433)
> > Binding to TCP port 2501 on host localhost
> > Setting gid to "104 104"
> > Setting uid to "1002"
> > dbaccess: can't connect to DB: unable to open database file(1) at 
> > dbdimp.c line 94
> > Can't call method "do" on an undefined value at /usr/sbin/sqlgrey line 
> > 298.
> > root <at> slack11:/etc <mailto:root <at> slack11:/etc>#
> 
> Are /var/lib/sqlgrey permissions OK?
> It should belong to sqlgrey and be user writable.
> 
> Lionel
> 
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share your
> opinions on IT & business topics through brief surveys-and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> 

Hello Lionel,
and thanks very much for help...

you were right, the permissions wasn't OK!

but now, they are:

root@slack11:/var/lib# ls -l
total 60
......

drwxrwxrwx 2 sqlgrey sqlgrey 4096 2007-03-01 08:48 sqlgrey/
drwxrwx--T 2 root    nogroup 4096 2006-09-12 10:33 stunnel/
drwxr-xr-x 2 root    root    4096 2006-09-16 11:47 xdm/
drwxr-xr-x 2 root    root    4096 2007-03-01 08:14 xkb/
root@slack11:/var/lib#

But error is the same:

root@slack11:/var/lib# sqlgrey
2007/03/01-08:59:04 sqlgrey (type Net::Server::Multiplex) starting! pid(1517)
Binding to TCP port 2501 on host localhost
Setting gid to "104 104"
Setting uid to "1002"
dbaccess: can't connect to DB: unable to open database file(1) at dbdimp.c line 
94
Can't call method "do" on an undefined value at /usr/sbin/sqlgrey line 298.
root@slack11:/var/lib#


I even tryed using a sqlgrey.db file from another computer running under FC-5...
Error was the same.....

Thank's again

Re: [Sqlgrey-users] Performance: strict versus loose expiration time

[Michael S. <Mic...@lr...>]

On Wed, 28 Feb 2007, Lionel Bouton wrote:

>
> I don't believe this is a good example, I believe you get a benefit from
> the query cache only if the whole query (not only the prepared
> statement) is exactly the same. So in the example above you'll need to
> have the same timestamp AND the same sender_name, sender_domain, src.
> Losing the timestamp don't bring much benefits, because the rate at
> which the from_awl is updated (entries added, updated or deleted)
> probably is far higher than the rate at which the sender+src comes back.
>

Lionel,

I am not talking about normal operation. What I am concerned about are
spam attacks, where you have to react as fast as possible.

I just made a statistic from a spam attack a few minutes ago. In a 10
minute frame from 16:10 to 16:20 we got 16296 triplets from 52 ip
addresses, all from the same spammer. This is part of the log for 1 ip
address (I eliminated all attributes other than client_address and
sender):

Feb 28 16:15:58 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:15:58 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:15:58 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:15:58 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:15:58 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:15:58 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:15:58 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:15:58 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:15:58 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:15:58 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:18:21 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:18:21 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:18:21 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:18:21 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:18:21 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:18:21 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:18:21 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:18:21 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:18:21 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:18:21 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:14:43 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:14:43 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:14:43 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:14:43 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:14:43 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:14:43 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:14:43 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:14:43 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:14:43 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:14:43 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:17:02 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:17:02 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:17:02 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:17:02 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:17:02 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:17:02 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:17:02 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:17:02 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:17:02 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:17:02 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:19:50 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:19:50 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:19:50 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:19:50 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:19:50 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:19:50 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:19:50 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:19:50 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:19:50 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...
Feb 28 16:19:50 lxmhs25 sqlgrey[5736]: request: client_address=124.6.181.182 sen...@ti...

As you can see, the generated senders are used 10 times each. With loose
only the first query will go to from_awl, the other 9 queries will be
answered by the query cache.

On the other side you have to look for throttling. If none of the triplets
is accepted because of an entry in an AWL, 16296 decisions about
throttling have been made.

The code for the decision is:

    # Throttling too many connections from same new host
    if (defined $self->{sqlgrey}{connect_src_throttle} and $self->{sqlgrey}{connect_src_throttle} > 0) {
        if ($self->count_src_connect($cltid) >= $self->{sqlgrey}{connect_src_throttle}
            and $self->count_src_domain_awl($cltid) < 1
            and $self->count_src_from_awl($cltid) < $self->{sqlgrey}{connect_src_throttle}) {
                $self->mylog('grey', 2, "throttling: $cltid($addr), $sender_name\@$sender_domain -> $recipient");
                return ($self->{sqlgrey}{reject_first} . ' Throttling too many connections from new source - ' .
                ' Try again later.');
        }
    }

That means, for every decision 3 count subroutines are executed:

- $self->count_src_connect($cltid)
- $self->count_src_domain_awl($cltid)
- $self->count_src_from_awl($cltid)

In the 10 minute frame 1.475 triplets have been accepted because of
from_awl, domain_awl or reconnect ok, which means every time the query
cache of either from_awl or domain_awl and of connect is emptied.

This means from the nearly 50.000 count queries of the 3 tables only about
3.000 will not be answered by the cache.

The main question still is, is an answer from the cache really much faster
than from a table.

I hope this explains why I am looking into the issues of loose or strict
implementation.

Michael Storz
--
======================================================
Leibniz-Rechenzentrum      |    <mailto:St...@lr...>
Boltzmannstr. 1            |    Fax: +49 89 35831-9700
85748 Garching / Germany   |    Tel: +49 89 35831-8840
======================================================

Re: [Sqlgrey-users] Performance: strict versus loose expiration time

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

Michael Storz wrote the following on 28.02.2007 16:39 :
> On Wed, 28 Feb 2007, Dan Faerch wrote:
>
>   
>> Michael Storz wrote:
>>
>>     
>>> db_cleandelay seconds longer than the strict one. Taking the default this
>>> means it is just half an hour longer, which is at least not noticeable for
>>> the AWLs.
>>>
>>>
>>>       
>> Well. We cant really assume what ppl use for db_cleandelay.. I use 60
>> seconds. Someone might do it once every week. You never know ;).
>>     
>
> We use 1 hour. I think, because as you said, we do not know what people
> are using for db_cleandelay, we should either provide a strict
> implementation or the possibility for the user to choose which
> implementation he wants. As I said in another email, I think I will be
> able to provide a patch for such a choice.
>   

Hum if you can get away without loose, please do so. I consider the 
loose algorithm to be a bug. What I don't like with the possibility to 
choose between loose and strict algorithm is that it will be hard to 
explain to the user what she loses using loose...
I'd prefer users not to have to worry about such details. Unless you can 
demonstrate a significant performance difference between the two, please 
only provide 'strict' support.

>> I dont know excactly how thoose work, but why should there be any
>> difference when the actual where statement doenst change. I dont see
>> anywhere that ie. a timestamp is stuck into the statement. Its all
>> static information and thus, in my head, should still hit the query cache.
>>
>> Most select needs a WHERE statment. Eg. WHERE sender_name =, WHERE src
>> =, ect.ect. So i dont see how adding the max_connect_age,
>> reconnect_delay or awl_age values (which is static) will change how the
>> cache works.
>>
>>     
>
> The query cache uses the select statement as a "text" key. That means,
> using 'select' or 'SELECT' is already a different statement. If we
> implement strict expiration time, then every select of the is_in or count
> subroutines will have a timestamp:
>
> Example from sub is_in_from_awl:
>
>     my $sth = $self->prepare("SELECT 1 FROM $from_awl " .
>                              'WHERE sender_name = ? ' .
>                              'AND sender_domain = ? ' .
>                              'AND src = ? ' .
>                              'AND last_seen > ' .
>                              $self->past_tstamp($self->{sqlgrey}{awl_age},
>                                                 'DAY')
>                             );
> If now() is 2007-02-28 16:41:08, for MySQL you will get the statement
>
> SELECT 1 FROM from_awl WHERE sender_name = ? AND sender_domain = ? AND src
> = ? AND last_seen > timestamp '2007-02-28 16:11:08' - INTERVAL 36 DAY
>
> One second later, now changes to 2007-02-28 16:41:09 and you have a
> textual different select statement. For a loose implementation the part
> 'AND last_seen > timestamp '2007-02-28 16:11:08' - INTERVAL 36 DAY' will
> be ommitted and you always get the same statement (for the same src and
> sender).
>
>   

I don't believe this is a good example, I believe you get a benefit from 
the query cache only if the whole query (not only the prepared 
statement) is exactly the same. So in the example above you'll need to 
have the same timestamp AND the same sender_name, sender_domain, src. 
Losing the timestamp don't bring much benefits, because the rate at 
which the from_awl is updated (entries added, updated or deleted) 
probably is far higher than the rate at which the sender+src comes back.

Lionel

Re: [Sqlgrey-users] Performance: strict versus loose expiration time

[Dan F. <da...@ha...>]

Michael Storz wrote:
> SELECT 1 FROM from_awl WHERE sender_name = ? AND sender_domain = ? AND src
> = ? AND last_seen > timestamp '2007-02-28 16:11:08' - INTERVAL 36 DAY
>   
Aha.. Interesting. So how about staticly defining "seconds" to be 00 
always. Might be possible, and then the cache would expire only once a 
minute..

- Dan

Re: [Sqlgrey-users] Performance: strict versus loose expiration time

[Michael S. <Mic...@lr...>]

On Wed, 28 Feb 2007, Dan Faerch wrote:

> Michael Storz wrote:
>
> > db_cleandelay seconds longer than the strict one. Taking the default this
> > means it is just half an hour longer, which is at least not noticeable for
> > the AWLs.
> >
> >
> Well. We cant really assume what ppl use for db_cleandelay.. I use 60
> seconds. Someone might do it once every week. You never know ;).

We use 1 hour. I think, because as you said, we do not know what people
are using for db_cleandelay, we should either provide a strict
implementation or the possibility for the user to choose which
implementation he wants. As I said in another email, I think I will be
able to provide a patch for such a choice.

>
> > In the next step this select statement will hit the
> > query cache as long as the table involved hasn't changed. This could give
> > us a second gain especially for the tables from_awl or domain_awl which do
> > not change often.
> >
> You mean the query-cache like the one build into newer MySql's?

Right, since version 4.0.1 MySQL has a query cache.

> I dont know excactly how thoose work, but why should there be any
> difference when the actual where statement doenst change. I dont see
> anywhere that ie. a timestamp is stuck into the statement. Its all
> static information and thus, in my head, should still hit the query cache.
>
> Most select needs a WHERE statment. Eg. WHERE sender_name =, WHERE src
> =, ect.ect. So i dont see how adding the max_connect_age,
> reconnect_delay or awl_age values (which is static) will change how the
> cache works.
>

The query cache uses the select statement as a "text" key. That means,
using 'select' or 'SELECT' is already a different statement. If we
implement strict expiration time, then every select of the is_in or count
subroutines will have a timestamp:

Example from sub is_in_from_awl:

    my $sth = $self->prepare("SELECT 1 FROM $from_awl " .
                             'WHERE sender_name = ? ' .
                             'AND sender_domain = ? ' .
                             'AND src = ? ' .
                             'AND last_seen > ' .
                             $self->past_tstamp($self->{sqlgrey}{awl_age},
                                                'DAY')
                            );
If now() is 2007-02-28 16:41:08, for MySQL you will get the statement

SELECT 1 FROM from_awl WHERE sender_name = ? AND sender_domain = ? AND src
= ? AND last_seen > timestamp '2007-02-28 16:11:08' - INTERVAL 36 DAY

One second later, now changes to 2007-02-28 16:41:09 and you have a
textual different select statement. For a loose implementation the part
'AND last_seen > timestamp '2007-02-28 16:11:08' - INTERVAL 36 DAY' will
be ommitted and you always get the same statement (for the same src and
sender).


>
> - Dan
>
> ps. Based on this, are you saying i shouldnt apply the "is_active" patch
> you made?

As I said, I'm thinking about providing a more elaborated patch :-)

Michael Storz
--
======================================================
Leibniz-Rechenzentrum      |    <mailto:St...@lr...>
Boltzmannstr. 1            |    Fax: +49 89 35831-9700
85748 Garching / Germany   |    Tel: +49 89 35831-8840
======================================================

Re: [Sqlgrey-users] Error installing under Slack.11

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

John Smith wrote the following on 28.02.2007 15:34 :
> (...)
>  
> postfix:x:102:
> postdrop:x:103:
> sqlgrey:x:104:
> and in /etc/passwd:
> postfix:x:1001:102:Postfix MTA:/var/spool/postfix:/bin/false
> sqlgrey:x:1002:104::/var/lib/sqlgrey:
>  
> I even create manually /var/lib/sqlgrey.
>  
> Now, the problem:
>  
> root@slack11:/etc <mailto:root@slack11:/etc># sqlgrey
> 2007/02/28-16:29:30 sqlgrey (type Net::Server::Multiplex) starting! 
> pid(4433)
> Binding to TCP port 2501 on host localhost
> Setting gid to "104 104"
> Setting uid to "1002"
> dbaccess: can't connect to DB: unable to open database file(1) at 
> dbdimp.c line 94
> Can't call method "do" on an undefined value at /usr/sbin/sqlgrey line 
> 298.
> root@slack11:/etc <mailto:root@slack11:/etc>#

Are /var/lib/sqlgrey permissions OK?
It should belong to sqlgrey and be user writable.

Lionel

Re: [Sqlgrey-users] Performance: strict versus loose expiration time

[Michael S. <Mic...@lr...>]

On Wed, 28 Feb 2007, Lionel Bouton wrote:

> Michael Storz wrote the following on 22.02.2007 10:16 :
> > Hi Dan and Lionel,
> >
> > the last days I was thinking about the performance impact of implementing
> > a strict versus a loose expiration time. At the moment, Sqlgrey uses a
> > mixed implementation, the is_in_* subroutines use a strict implementation,
> > whereas the count subroutines use a loose implementation.
> >
> > strict means, every select has a condition in the where-clause which
> > results in returning only active entries from a table.
> >
> > loose means, there is no such condition in the where-clause. Expiration is
> > handled only by deleting expired entries from a table via a cleanup
> > subroutine.
> >
>
> strict seems better to me. It didn't seem to matter much when I coded
> the loose count subroutines, but looking back on the code I don't like
> the fact that db_cleandelay has an impact on the auto-whitelist behaviour.
>
> I vote for the "strict" behaviour.
>
> Lionel
>

How about this: I think, I would able to deliver a patch which decides at
compile/parse-time if a loose or strict implementation will be used (this
means code for the other possibility will be eliminated at parse time).
This could be triggered by a commandline flag. Default would be strict.
But before I'll do this I have to test if there is any noticeable speed
advantage of having a loose implementation (and this needs some time).

Michael Storz
--
======================================================
Leibniz-Rechenzentrum      |    <mailto:St...@lr...>
Boltzmannstr. 1            |    Fax: +49 89 35831-9700
85748 Garching / Germany   |    Tel: +49 89 35831-8840
======================================================

[Sqlgrey-users] Error installing under Slack.11

[John S. <ke...@gm...>]

Hello again,

I'm stucked trying to install SQLgrey undel Slackware 11.
Postfix is working, and all other components needed are installed(Perl, =
DBI, Net::Server::Multiplex, SQLite)
in /etc/sqlgrey/sqlgrey.conf i have:

## Database settings
# instead of Pg below use "mysql" for MySQL, "SQLite" for SQLite
# any DBD driver is allowed, but only the previous 3 have been tested
# db_type =3D Pg
db_type =3D SQLite
# db_name =3D sqlgrey
db_name =3D sqlgrey.db

in /etc/group there is:

postfix:x:102:
postdrop:x:103:
sqlgrey:x:104:

and in /etc/passwd:
postfix:x:1001:102:Postfix MTA:/var/spool/postfix:/bin/false
sqlgrey:x:1002:104::/var/lib/sqlgrey:

I even create manually /var/lib/sqlgrey.

Now, the problem:

root@slack11:/etc# sqlgrey
2007/02/28-16:29:30 sqlgrey (type Net::Server::Multiplex) starting! =
pid(4433)
Binding to TCP port 2501 on host localhost
Setting gid to "104 104"
Setting uid to "1002"
dbaccess: can't connect to DB: unable to open database file(1) at =
dbdimp.c line 94
Can't call method "do" on an undefined value at /usr/sbin/sqlgrey line =
298.
root@slack11:/etc#


Thanks allot

Re: [Sqlgrey-users] Performance: strict versus loose expiration time

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

Michael Storz wrote the following on 22.02.2007 10:16 :
> Hi Dan and Lionel,
>
> the last days I was thinking about the performance impact of implementing
> a strict versus a loose expiration time. At the moment, Sqlgrey uses a
> mixed implementation, the is_in_* subroutines use a strict implementation,
> whereas the count subroutines use a loose implementation.
>
> strict means, every select has a condition in the where-clause which
> results in returning only active entries from a table.
>
> loose means, there is no such condition in the where-clause. Expiration is
> handled only by deleting expired entries from a table via a cleanup
> subroutine.
>   

strict seems better to me. It didn't seem to matter much when I coded 
the loose count subroutines, but looking back on the code I don't like 
the fact that db_cleandelay has an impact on the auto-whitelist behaviour.

I vote for the "strict" behaviour.

Lionel

Re: [Sqlgrey-users] idea for improving Discrimination feature

[Dan F. <da...@ha...>]

Riaan Kok wrote:
> bad as I thought it might be.  The attached patch is against Sqlgrey
> 1.7.5.  I had to do some tricks to get a clean patch, but the code in


Got around to applying this today, but i cant get it to patch against 
1.7.5. Could you check that you send the right patch so lazy-old-me dont 
have to do it manually? :)

-Dan

Re: [Sqlgrey-users] Performance: strict versus loose expiration time

[Dan F. <da...@ha...>]

Michael Storz wrote:

> db_cleandelay seconds longer than the strict one. Taking the default this
> means it is just half an hour longer, which is at least not noticeable for
> the AWLs.
>
>   
Well. We cant really assume what ppl use for db_cleandelay.. I use 60 
seconds. Someone might do it once every week. You never know ;).

> In the next step this select statement will hit the
> query cache as long as the table involved hasn't changed. This could give
> us a second gain especially for the tables from_awl or domain_awl which do
> not change often.
>   
You mean the query-cache like the one build into newer MySql's?
I dont know excactly how thoose work, but why should there be any 
difference when the actual where statement doenst change. I dont see 
anywhere that ie. a timestamp is stuck into the statement. Its all 
static information and thus, in my head, should still hit the query cache.

Most select needs a WHERE statment. Eg. WHERE sender_name =, WHERE src 
=, ect.ect. So i dont see how adding the max_connect_age, 
reconnect_delay or awl_age values (which is static) will change how the 
cache works.


- Dan

ps. Based on this, are you saying i shouldnt apply the "is_active" patch 
you made?

Re: [Sqlgrey-users] Working on SQLgrey

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

Kenneth Marshall wrote the following on 27.02.2007 22:58 :
> Lionel,
>
> Do you have an idea as to what would be the best choice of a primary
> key for the connect table? All the other tables have one, and without
> it Slony1 replication will not work. The two more obvious choices would
> be to add an ID column with a default value from a sequence or to use
> a unique index on src, sender_domain, sender_name, rcpt.

The unique index is ok. I couldn't add it because MySQL didn't support
indexes for large columns (from memory the total size of the columns
must be less than ~500 chars).

PostgreSQL is OK with it so you can add it. Pay attention to future
schema migrations: your schema will be customized and if SQLgrey tries
to convert the connect table to another format you will lose your index
(as I didn't looked much into Slony1 I'm not sure that it would even
support the replication of a database on which the schema changes...).

Lionel.

Re: [Sqlgrey-users] Working on SQLgrey

[Kenneth M. <kt...@ri...>]

Lionel,

Do you have an idea as to what would be the best choice of a primary
key for the connect table? All the other tables have one, and without
it Slony1 replication will not work. The two more obvious choices would
be to add an ID column with a default value from a sequence or to use
a unique index on src, sender_domain, sender_name, rcpt. 

Ken Marshall

[Sqlgrey-users] Performance: strict versus loose expiration time

[Michael S. <Mic...@lr...>]

Hi Dan and Lionel,

the last days I was thinking about the performance impact of implementing
a strict versus a loose expiration time. At the moment, Sqlgrey uses a
mixed implementation, the is_in_* subroutines use a strict implementation,
whereas the count subroutines use a loose implementation.

strict means, every select has a condition in the where-clause which
results in returning only active entries from a table.

loose means, there is no such condition in the where-clause. Expiration is
handled only by deleting expired entries from a table via a cleanup
subroutine.

The patches I sent Dan, implement a strict expiration time for all
subroutines.

However, implementing loose expiration time instead of strict, could bring
some performance gains.

If a select does not have the expiration condition in the where-clause, it
will be characterwise identical for the same triplet (or part of it). This
allows to use prepare_cached instead of prepare, which gives us the first
performance gain. In the next step this select statement will hit the
query cache as long as the table involved hasn't changed. This could give
us a second gain especially for the tables from_awl or domain_awl which do
not change often.

In the loose implementation the maximum expiration time would be
db_cleandelay seconds longer than the strict one. Taking the default this
means it is just half an hour longer, which is at least not noticeable for
the AWLs.

The only problem I see at the moment is a race condition between cleanup
and is_in/update, where the update could try to update a deleted entry.
Therefore update must take this into account similar to the put_in
subroutines which take into account the possibility of the insertion of an
entry by another Sqlgrey daemon.

What is your opinion?

Michael Storz
--
======================================================
Leibniz-Rechenzentrum      |    <mailto:St...@lr...>
Boltzmannstr. 1            |    Fax: +49 89 35831-9700
85748 Garching / Germany   |    Tel: +49 89 35831-8840
======================================================

Re: [Sqlgrey-users] Problem with count-subroutines

[Michael S. <Mic...@lr...>]

On Wed, 21 Feb 2007, Dan Faerch wrote:

> Michael Storz wrote:
> > On Fri, 16 Feb 2007, Dan Faerch wrote:
> >
> > Hi Dan,
> >
> > did you get my patches, I sent you off-list?
> >
>
> You mean the 2 patches you sent to me Friday, right? Yes i did, thank

correct.

> you..  As you requested, ill look into testing the timing stuff on our
> cluster, i just need to find a "stale" moment at work to do this and
> this week definitely wont be it, as i'm back to work after a weeks
> vacation and everything is on fire ;)
> I am also very interested in looking at the timings myself.

Great to hear. Hope you'll have time soon.

Michael Storz
--
======================================================
Leibniz-Rechenzentrum      |    <mailto:St...@lr...>
Boltzmannstr. 1            |    Fax: +49 89 35831-9700
85748 Garching / Germany   |    Tel: +49 89 35831-8840
======================================================

Re: [Sqlgrey-users] Problem with count-subroutines

[Dan F. <da...@ha...>]

Michael Storz wrote:
> On Fri, 16 Feb 2007, Dan Faerch wrote:
>   
> Hi Dan,
>
> did you get my patches, I sent you off-list?
>   

You mean the 2 patches you sent to me Friday, right? Yes i did, thank 
you..  As you requested, ill look into testing the timing stuff on our 
cluster, i just need to find a "stale" moment at work to do this and 
this week definitely wont be it, as i'm back to work after a weeks 
vacation and everything is on fire ;)
I am also very interested in looking at the timings myself.

- Dan

Re: [Sqlgrey-users] Problem with count-subroutines

[Michael S. <Mic...@lr...>]

On Fri, 16 Feb 2007, Dan Faerch wrote:

> Michael Storz wrote:
> > you can have the case, that throttling for an IP address is done 24 hours
> > longer than it should.
> >
> >
> I see what you mean..
> > Actually the problem was, that I got inconsistent results between using
> > memcached and the SQL database.
> >
> >
> Which is why i asked how you stumbled upon this, as i couldnt really
> imagine what obscure situation that had occurred for you to notice this ;)..
>
> It definitely should be fixed in next release. If you've already fixed
> this id be really glad to receive a patch ;).
>

Hi Dan,

did you get my patches, I sent you off-list?

Michael Storz
--
======================================================
Leibniz-Rechenzentrum      |    <mailto:St...@lr...>
Boltzmannstr. 1            |    Fax: +49 89 35831-9700
85748 Garching / Germany   |    Tel: +49 89 35831-8840
======================================================

Re: [Sqlgrey-users] idea for improving Discrimination feature

[Riaan K. <ria...@gm...>]

On 04/02/07, Riaan Kok <ria...@gm...> wrote:
> On 02/02/07, Dan Faerch <da...@ha...> wrote:
> > Riaan Kok wrote:
> > > a case that I saw in the logs intrigued me, so I did a quick lookup of
> > > the
> > > keys() function and read a bit about hashes..  Perl's hash structure
> > > is not
> > > ordered in any way.  So, iterating through a hash returns the elements in
> > > undefined order..
> >
>
> It's just that, if you're curious about statistics, the random order
> of the hash list of rules means that the only way of knowing what
> percentage of connections get nailed by a rule is to have only one
> rule.
>
> Another advantage of knowing the order in which rules will execute is
> that, in production, you can place cheap and broad rules first, and
> more expensive rules last (such as that badass rule for catching
> dynamic IP client hostnames in dyn_fqdn.regexp).  If your traffic is
> sufficient, your CPU might just appreciate it..
>
> > > So, how about rather storing the list of rules in an array, which does
> > > away
> > > with the need for storing the $rulenr, and each array item like $rule
> > > containing:
> > > $rule->{attrib}
> > > $rule->{oper}
> > > $rule->{regexp}
> > Hmm well.. Its seems like a lot of work for a very small result. I dont
> > think ill be coding this anytime soon ;).. But if youre a perl coder,
> > patches are welcome.
>


Okay, I finally got some time to do my proposed change.  It wasn't as
bad as I thought it might be.  The attached patch is against Sqlgrey
1.7.5.  I had to do some tricks to get a clean patch, but the code in
my sqlgrey has been running fine now for a day (in the order of 100k
connections/day).

Some day when I get around to playing with my rules and stats I'll
post if there's anything interesting.

regards,
Riaan

[Sqlgrey-users] Performance of sqlgrey with and without memcached

[Michael S. <Mic...@lr...>]

Hi Dave and others,

in the meantime I did made some measurement of timeing using sqlgrey with
memcached and without. For our installation sqlgrey without support of
memcached is faster in processing triplets than with memcached.

I think, the reason for this is, that we use a dedicated server for
running sqlgrey and mysql. Since we use MySQL with the MyISAM engine and
the database is so small, that it fits into main memory, a search (sub
is_in_...) in one of the AWLs is nearly as fast as a single get of a value
from memcached (search about 4-5 microseconds, get about 2-4
microseconds).

The needed additional logic to incorporate memcached into sqlgrey and a
second search of an AWL for a memcached miss, makes the combination
sqlgrey + memcached slower than sqlgrey alone.

This could be different, if you do not have a dedicated MySQL server or if
you are using PostgreSQL, but I don't know.

With our dedicated server, we are able to process up to 1.000.000 triplets
per hour in a test environment. In production the upper limit we see is
about 830.000 triplets per hour, or about 0.0043 seconds per triplet. We
see this several times a week, when one spammer is hitting our server.
Normally we see about 2.000.000 triplets per hour.

At the upper limit, the bottleneck we are hitting is the bandwidth between
the main memory and the CPU. In tests with newer hardware we saw an
improvement of a factor of 2.8.

For the timing measurement I borrowed the module Amavis::Timing from
amavisd-new and put calls to section_time into sub smtpd_access_policy:

Sqlgrey::Timing::init();
    section_time('whitelist');
    section_time('optin/out');
    section_time('discrimination');
    section_time('dbnow');
    section_time('cleanup');
    section_time('domain');
    section_time('from');
    section_time('early');
    section_time('reconnect');
    section_time('throttling');
    section_time('new');
Sqlgrey::Timing::report()

A typical log line is:

Feb 16 17:10:57 lxmhs25 lrzgrey2: grey: TIMING [total 32 us] - whitelist:
2 (6/6%%), optin/out: 0 (1/7%%), discrimination: 0 (0/7%%), dbnow: 3
(10/17%%), cleanup: 0 (0/17%%), domain: 4 (13/30%%), from: 4 (12/41%%),
early: 5 (14/55%%), reconnect: 5 (15/71%%), throttling: 4 (13/84%%),
new: 5 (16/100%%)

I would be interested to hear from others, what their peak performance is.
Please tell us, if you have any data about how fast your setup processes
triplets.

Michael Storz
--
======================================================
Leibniz-Rechenzentrum      |    <mailto:St...@lr...>
Boltzmannstr. 1            |    Fax: +49 89 35831-9700
85748 Garching / Germany   |    Tel: +49 89 35831-8840
======================================================

Re: [Sqlgrey-users] whitelist question

[Dan F. <da...@ha...>]

Michael Storz wrote:
> On Fri, 16 Feb 2007, John Smith wrote:
>
> *.ro only works for whitelisting if an ip address has a reverse mapping
> and the nameservers can be reached and answer in time.
>   

Also, there is no guarantee that everyone from  your country reverses to 
.ro. You might have a big isp reversing to something like TeleRomania.com.

I use discrimination. Not for this excact problem, but i do filter based 
on tld's from well known spamming countries, especially from Asia. Also 
i filter everyone who does not have a reverse-dns-record.
Many mailservers in the world simply reject mail from you if you do not 
have reverse-dns, so i recon that greylisting them on my servers for not 
having that record is more than fair treatment.

It would be possible to make:
client_name !~ \.ro$
Basically same result as with the whitelist, however you would still 
benefit from other regex's to ie. catch dsl lines and such.

It can quickly gets a bit more hairy than the whitelist. Imagine you 
wanted to pass both .ro and teleromania.com and still catch everything 
called dsl, proxy and such. (just as an example)
client_name !~ (\.ro|teleromania\.com)$
client_name =~ (dsl|proxy|dailup)

But again. It all falls back to the dns-server actually answering.


Another thought would be to build in support for geoip lookups. However 
that might be really really heavy to do in high-traffic environments, 
but it should definitely be doable.


- Dan

Re: [Sqlgrey-users] Problem with count-subroutines

[Dan F. <da...@ha...>]

Michael Storz wrote:
> you can have the case, that throttling for an IP address is done 24 hours
> longer than it should.
>
>   
I see what you mean..
> Actually the problem was, that I got inconsistent results between using
> memcached and the SQL database.
>
>   
Which is why i asked how you stumbled upon this, as i couldnt really 
imagine what obscure situation that had occurred for you to notice this ;)..

It definitely should be fixed in next release. If you've already fixed 
this id be really glad to receive a patch ;).

- Dan

Re: [Sqlgrey-users] whitelist question

[Michael S. <Mic...@lr...>]

On Fri, 16 Feb 2007, John Smith wrote:

> Hello there,
>
> The problem i have, was previously discussed here by Santos and Dave :
> https://sourceforge.net/mailarchive/message.php?msg_id=14790517.
> Unfortunately I didn't find the answer.
>
> So, can someone please say how can I whitelist all the mails comming
> from my country?
>
> The file clients_fqdn_whitelist.local has a single line:
>             *.ro
>
> Note: since I created the clients_fqdn_whitelist.local file above, a lot
> of mails comming from .ro was whitelisted.... but NOT all of them, there
> are still mails rejected and greylisted:
> [root@nst log]# egrep "Feb 14.*licitatie" maillog
> Feb 14 16:40:40 nst sqlgrey: grey: new: 194.102.45.168(194.102.45.168),
> co...@e-... -> axx...@tx...
> Feb 14 16:40:40 nst postfix/smtpd[6389]: NOQUEUE: reject: RCPT from
> unknown[194.102.45.168]: 450 <unknown[194.102.45.168]>: Client host
> rejected: Greylisted for 3 minutes; from=<co...@e-...>
> to=<axx...@tx...> proto=ESMTP helo=<mail.seap.e-licitatie.ro>
>
> Thank you in advance.

Hi John Smith,

*.ro only works for whitelisting if an ip address has a reverse mapping
and the nameservers can be reached and answer in time.

The 3 nameservers for 194.102.45.168 (dig +norec 45.102.194.in-addr.arpa.)

;; AUTHORITY SECTION:
45.102.194.in-addr.arpa.  11h42m21s IN NS  ns.warpnet.ro.
45.102.194.in-addr.arpa.  11h42m21s IN NS  ns1.usv.ro.
45.102.194.in-addr.arpa.  11h42m21s IN NS  ns1.assist.ro.

do not answer queries at the moment:

dig +norecur -x 194.102.45.168 @ns1.usv.ro

; <<>> DiG 8.3 <<>> +norecur -x @ns1.usv.ro
; (1 server found)
;; res options: init defnam dnsrch
;; res_nsend to server ns1.usv.ro  80.96.120.1: Connection timed out

dig +norecur -x 194.102.45.168 @ns.warpnet.ro

; <<>> DiG 8.3 <<>> +norecur -x @ns.warpnet.ro
; (1 server found)
;; res options: init defnam dnsrch
;; res_nsend to server ns.warpnet.ro  217.156.25.1: Connection refused

dig +norecur -x 194.102.45.168 @ns1.assist.ro

; <<>> DiG 8.3 <<>> +norecur -x @ns1.assist.ro
; (1 server found)
;; res options: init defnam dnsrch
;; res_nsend to server ns1.assist.ro  194.102.130.1: Connection timed out

Therefore no reverse mapping exists and whitelisting does not work for
such ip addresses.

Michael Storz
--
======================================================
Leibniz-Rechenzentrum      |    <mailto:St...@lr...>
Boltzmannstr. 1            |    Fax: +49 89 35831-9700
85748 Garching / Germany   |    Tel: +49 89 35831-8840
======================================================