Menu
▾
▴
Re: [Sqlgrey-users] Performance: strict versus loose expiration time
|
From: Michael S. <Mic...@lr...> - 2007-02-28 17:27:45
|
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
======================================================
|
