Re: [Sqlgrey-users] impressive results from using memcached

[Dave S. <dst...@ma...>]

Michael,
=20
Be careful about the SELECT statements you store hits on in memcached. =
Many SQLGrey SQL statements use a time parameter, and thus, by their =
nature, will never be used again (eg. SELECT * FROM table WHERE utstamp > =
$now).
=20
I found when using memcached with SQLGrey that I had to alter the SQL =
calls so a result stored in memcahed was not 'time sensitive". Of course, =
the makes SQLGrey not as perfect at detecting exact times for expired =
records, etc, but I felt this was a worthwhile tradeoff.
=20
I am currently getting a 40%-50% memcached hit rate with my alterations.
=20
Dave Strickler
MailWise LLC
617-933-5810 (direct)
www.mailwise.com ( http://www.mailwise.com/ )
"Intelligent E-mail Protection"





>>> Michael Storz <Mic...@lr...> 4:04 AM Monday, =
February 05, 2007 >>>
Hi *,

I've again played around with my special version of sqlgrey and made a
prototype implementation for using memcached in the last days. This is the
statistc I got from running this code over the last 37 hours with my
spamtrap system:

    type       requests            hit             miss
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D
  domain_awl      17368        177   1 %      17191  98 %
     dsn_awl       1052        342  32 %        710  67 %
    from_awl      17188        163   0 %      17025  99 %
    rcpt_awl      17368          0   0 %      17368 100 %
    throttle     166355     139594  83 %      26761  16 %
     unknown     168870     150450  89 %      18420  10 %

Some remarks:

- my spamtrap system cannot be compared to a normal production system.
  This is a virtual email server which accepts only emails for some
  domains, which are no longer in use for more than 10 years. Therefore
  there should be no ligit emails coming in, only spam mails and DSNs for
  spam mails which used our old domains as originator. The rcpt_awl
  is empty and all the entries in domain_awl and from_awl are from spam
  mails, which were able to get through greylisting.

- the number of processed emails are the emails which are whitelisted plus
  the 168870 emails which are checked for ip state unknown.

- unknown is a new state. It means there is no entry in my 4 AWLs
  domain_awl, dsn_awl, from_awl, rcpt_awl with the ip address of this
  triplet. Therefore sqlgrey can skip the checks for these AWLs and
  immediately check for a reconnect.

The statistic means, for 83 % of the triplets no check of the AWLs was
made and for 83 % there were only 1 SQL SELECT and 2 memcached checks
needed and then immediately reject.

I suppose the situation could be similar with a production system and
therefore the usage of memcached especially for the 2 states unknown and
throttle would give Sqlgrey a real performance boost.

Michael Storz
PS: Lionel, can you now imagine why I found the mydie error? :-)
--
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D
Leibniz-Rechenzentrum      |    <mailto:St...@lr...>
Boltzmannstr. 1            |    Fax: +49 89 35831-9700
85748 Garching / Germany   |    Tel: +49 89 35831-8840
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D

-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job =
easier.
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as ( http://sel.as/ )-us.falkag.net/sel?cmd=3Dlnk&kid=3D120709&b=
id=3D263057&dat=3D121642
_______________________________________________
Sqlgrey-users mailing list
Sql...@li...=20
https://lists.sourceforge.net/lists/listinfo/sqlgrey-users

This message has been certified virus-free by MailWise Filter - The real-ti=
me, intelligent, e-mail firewall used to scan inbound and outbound messages =
for SPAM, Viruses and Content. =0A=0A  For more information, please visit: http:=
//www.mailwise.com=0A