Re: [Sqlgrey-users] RELEASE: 1.5.9 + with throttling

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

Michel Bouissou wrote:

>Le Mercredi 08 Juin 2005 11:05, Lionel Bouton a =E9crit :
> =20
>
>>"It doesn't hurt anyway" isn't enough. It must solve real world
>>problems. I'm aware that theoriticaly this is good to have less entries
>>in the connect table but as I said earlier the practical benefits aren'=
t
>>clear to me yet.
>>   =20
>>
>
>I strongly believe there are benefits, otherwise I wouldn't have asked f=
or it=20
>in the first time then coded it in the end ;-)
>
>Well, I know, I can be mistaken ;-)
>
> =20
>
>>Michel, could you give us a ratio between the results of:
>>grep "sqlgrey: grey: throttling: " | wc -l " (on a log spanning the las=
t
>>max_connect_age period)
>>and
>>select count(*) from connect
>>
>>on your configuration ? This would help measure the benefits of tarpitt=
ing.
>>   =20
>>
>
>I'm not sure my server is a good real-life example, as its traffic is re=
ally=20
>moderate.
>
>OTOH, I've already seen some tapitting in action since I installed it=20
>yesterday afternoon, and I recall my "connect" table size had been mutip=
lied=20
>by a factor 10 when the latest M$ worm came out... Hence the idea I had =
about=20
>tarpitting for fighting this kind of event.
>Guess we need another new M$ worm to figure out the benefits it gives wh=
en=20
>such an event occurs...
>
> =20
>
>>If other users could fetch Michel's build and test it in the same manne=
r
>>too that would be great.
>>   =20
>>
>
>Yep. I'd love to get some feedback.
>
> =20
>
>># connect cleanup
>>
>>I'm worrying about the LIKE. There are 2 problems with it:
>>- may hurt performance (I've no experience with it, I'm currently
>>guessing performance is OK),
>>   =20
>>
>
>It probably won't hurt, as the query still use the main index for IP and=
=20
>sender_domain, leaving the LIKE select a very small subset of entries in=
=20
>connect...
>
> =20
>
>>- I'll have to check SQLite to see if it supports this.
>>   =20
>>
>
>LIKE is a very standard SQL statement... I would be surprised if a decen=
t SQL=20
>system didn't implement it.
> =20
>

SQLite supports it (at least SQLite2 and SQLite3 do, which is enough for
me).
I'll take the cleanup code then. But don't start trusting the spam log
entries: there are cases where it will have false positives.

>BTW, have you considered creating the tables with "default 0" for timest=
amp=20
>columns ? "default 0" should be OK with any SQL, isn't it ? And it would=
=20
>prevent MySQL from performing auto-updates...
> =20
>

MySQL already changed its behaviour between versions regarding
timestamps. The  fact that "desc from_awl" doesn't output the CREATE
statement used by SQLgrey but a mangled one shows that MySQL
deliberately allows itself to change the CREATE statement. I don't want
to rely on the assumptions that the future MySQL versions won't
introduce other on the fly modifications of CREATE statements...

I prefer to solve this by forcing the databases to set first_seen to the
right value on each update. At least no database seems to modify UPDATE
statements on the fly! This is how it is done in 1.5.9.

Lionel