sqlgrey-users — Users of SQLgrey

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

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

On Thu, 15 Feb 2007, Dan Faerch wrote:

> Michael Storz wrote:
> > last_seen > $self->past_tstamp($self->{sqlgrey}{awl_age},'DAY')
> >
> > However, this is not the case with all of the count subroutines, though
> > they are semantically and syntactically nearly identical.
> >
> How on earth did you stumble upon this? ;) Ive just looked through the

Through code analysis ;)

> source and i see what you mean, but i dont see it causing any problems.
> Or am i wrong?

It is normally not a severe problem. But imagine the case of throttling.
If you cleanup your database only once every 24 hours, e.g midnight, then
you can have the case, that throttling for an IP address is done 24 hours
longer than it should.

Actually the problem was, that I got inconsistent results between using
memcached and the SQL database.

> It would be "cleaner" if this is fixed, so ill put it on my TODO list..
>
> - Dan

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

[Sqlgrey-users] whitelist question

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

Hello there,

The problem i have, was previously discussed here by Santos and Dave : =
https://sourceforge.net/mailarchive/message.php?msg_id=3D14790517. =
Unfortunately I didn't find the answer.

So, can someone please say how can I whitelist all the mails comming =
from my country?=20

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=3D<co...@e-...> =
to=3D<axx...@tx...> proto=3DESMTP =
helo=3D<mail.seap.e-licitatie.ro>

Thank you in advance.

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

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

Michael Storz wrote:
> last_seen > $self->past_tstamp($self->{sqlgrey}{awl_age},'DAY')
>
> However, this is not the case with all of the count subroutines, though
> they are semantically and syntactically nearly identical.
>   
How on earth did you stumble upon this? ;) Ive just looked through the 
source and i see what you mean, but i dont see it causing any problems. 
Or am i wrong?
It would be "cleaner" if this is fixed, so ill put it on my TODO list..

- Dan

[Sqlgrey-users] sqlgrey-1.7.5 (dev)

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

sqlgrey 1.7.5 has just been released.

 - Changed db_cleanup. clean time stored in db for better handling,
   especially in  clustered environments
 - Fix for harmless warnings about "possible typo"
 - Fix for sqlgrey dying if syslog is offline
 - Filled feature req from Riaan Kok. Support "postfix attributes on both
   sides".. Ie:  "client_name !~ helo_name"

- Dan

Re: [Sqlgrey-users] Performance with Postfix and SQLGrey

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

Dave Strickler wrote the following on 12.02.2007 18:21 :

Please don't reply to old posts when creating new subjects, it hides
your post in threaded views and make following discussions difficult.

> I am seeing, under the load of about 1,000 Postfix connections per
> minute, SQLGrey slow down. Note the times here in the log below. The
> email is seen by Postfix at 10:44, but not finished and ready to send
> for about 30 seconds. I have about 350 threads open on this server,
> and they are typically all in play. The LA is fairly low (3 to 4).
>  
> Is this SQLGrey that's slowing things down? It has to spawn a lot of
> sessions (see the stats above). Or is it our Postgres DB that's
> slowing this down?
>  
> Feb 12 12:10:44 ib17 postfix/smtpd[14833]: 5E9FE1D3A481:
> client={removed}[{removed}.99]
> Feb 12 12:10:44 ib17 postfix/cleanup[15475]: 5E9FE1D3A481:
> message-id=<7FA493658BF13544BCDD9CF41F9BFC01BB5068@{removed}.com
> <mailto:7FA493658BF13544BCDD9CF41F9BFC01BB5068@%7Bremoved%7D.com>>
> Feb 12 12:10:45 ib17 postfix/qmgr[14681]: 5E9FE1D3A481: from=jsmith
> <mailto:from=jsmith@%7Bremoved%7D.com>@{removed}
> <mailto:from=lsegesvary@%7Bremoved%7D>.com, size=34936, nrcpt=2 (queue
> active)
> Feb 12 12:11:13 ib17 postfix/smtp[16934]: 5E9FE1D3A481:
> to=<kwilson@{removed}.com <mailto:kwilson@%7Bremoved%7D.com>>,
> relay={removed}[{removed}.27], delay=34, status=sent (250 Ok: queued
> as 27A6A5FB6723)

To know which of PostgreSQL or SQLgrey is responsible, you should
monitor at least one. SQLgrey can't really measure the time spent from
receiving a request to the actual response because of the design of
Net::Server::Multiplex, so your best bet is to monitor PostgreSQL.

You can log the queries which take more than 100ms to be completed and
you'll have more informations, like:
- is it always the same type of query (should you try to optimize your
PostgreSQL configuration to optimize this query)?
- does it occur only during short periods (look for other processes
hammering the disk and competing with PostgreSQL for data access or
other databases being used)?

More general monitoring advices: use top and vmstat if you know when
your system becomes unresponsive to get a better view of what's going on.

Oh, and if you don't do it yet: use pg-autovacuum. PostgreSQL needs to
run VACUUM/ANALYZE on busy tables to maintain good performance.

Lionel.

[Sqlgrey-users] Problem with count-subroutines

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

Hi Lionel and Dan,

since every record in connect and the AWLs has an (implizit)
expiration/purge-date, all routines, which check if a record is in one of
the tables, construct the select-statement in a way that expired records
are not found:

last_seen > $self->past_tstamp($self->{sqlgrey}{awl_age},'DAY')

However, this is not the case with all of the count subroutines, though
they are semantically and syntactically nearly identical.

Correct are:

- is_in_from_awl
- is_in_domain_awl
- in_connect
- recently_in_connect

Wrong are:

- count_from_awl
- count_src_domain_awl
- count_src_from_awl
- count_src_connect

Maybe correct, even without checking expiration ???:

- get_first_seen_in_from_awl
- get_reconnect_delay


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] -undef- sender_name and sender_domain

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

On Wed, 14 Feb 2007, [ISO-8859-2] Bart?omiej Syryjczyk wrote:

> What means -undef- in sender_name and sender_domain in grey_from_awl table?
>
>

It means an empty envelope mail from:

MAIL FROM:<>

Normally, this means the email was a DSN (Delivery Service Notification).

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

[Sqlgrey-users] -undef- sender_name and sender_domain

[<bsy...@ka...>]

What means -undef- in sender_name and sender_domain in grey_from_awl table?

-- 
_______________________________________________________________________
Bartłomiej Syryjczyk
1181 Sekcja Utrzymania Ruchu
Dział Bezpieczeństwa; Wydział Rozwoju
P.I.KAMSOFT; 40-235 Katowice, ul.1 Maja 133; tel.(48-32)209-07-05; fax 
209-07-15

Re: [Sqlgrey-users] Connecting problems but sqlgrey is running and answers some requests

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

Are you in a high-load setting? We are, and see these from time to time. I =
think it's a timeout as well.
=20
Dave Strickler
MailWise LLC
617-933-5810 (direct)
www.mailwise.com ( http://www.mailwise.com/ )
"Intelligent E-mail Protection"





>>> <Car...@db...> 12:36 PM Wednesday, January 24, =
2007 >>>
My logs show quite a lot of these messages:
Jan 24 18:25:46 hostname postfix/smtpd[6778]: [ID 947731 mail.warning] =
warning: problem talking to server 127.0.0.1:2501: No such file or =
directory

Jan 24 18:25:45 hostname postfix/smtpd[6123]: [ID 947731 mail.warning] =
warning: timeout on 127.0.0.1:2501 while reading input attribute name

and

Jan 24 17:57:53 hostname postfix/smtpd[23742]: [ID 947731 mail.warning] =
warning: connect to 127.0.0.1:2501: Connection timed out

But sqlgrey is up and running and produces normal log entries like as well

Jan 24 18:29:56 hostip sqlgrey: grey: new: a.b.c.d(a.b.c.d), test@local -> =
te...@lo...=20


Seems like the daemon doesn't answer fast enough or not at all - any ideas =
or fixes?


-------------------------------------------------------------------------
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=3Djoin.php&p=3Dsourceforge&CID=3DDE=
VDEV=20
_______________________________________________
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

[Sqlgrey-users] Performance with Postfix and SQLGrey

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

I am seeing, under the load of about 1,000 Postfix connections per minute, =
SQLGrey slow down. Note the times here in the log below. The email is seen =
by Postfix at 10:44, but not finished and ready to send for about 30 =
seconds. I have about 350 threads open on this server, and they are =
typically all in play. The LA is fairly low (3 to 4).
=20
Is this SQLGrey that's slowing things down? It has to spawn a lot of =
sessions (see the stats above). Or is it our Postgres DB that's slowing =
this down?
=20
Feb 12 12:10:44 ib17 postfix/smtpd[14833]: 5E9FE1D3A481: client=3D{removed}=
[{removed}.99]
Feb 12 12:10:44 ib17 postfix/cleanup[15475]: 5E9FE1D3A481: message-id=3D<7F=
A493658BF13544BCDD9CF41F9BFC01BB5068@{removed}.com>
Feb 12 12:10:45 ib17 postfix/qmgr[14681]: 5E9FE1D3A481: from=3Djsmith@{remo=
ved} ( mailto:from=3Dlsegesvary@removed ).com, size=3D34936, nrcpt=3D2 =
(queue active)
Feb 12 12:11:13 ib17 postfix/smtp[16934]: 5E9FE1D3A481: to=3D<kwilson@{remo=
ved}.com ( mailto:kw...@re... )>, relay=3D{removed}[{removed}.27], =
delay=3D34, status=3Dsent (250 Ok: queued as 27A6A5FB6723)
=20
TIA,
=20
=20
=20
=20
Dave Strickler
MailWise LLC
617-933-5810 (direct)
www.mailwise.com ( http://www.mailwise.com/ )
"Intelligent E-mail Protection"




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

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

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

Very clever regarding the expire time! With your explanation, I see where =
you are going.=20
=20
I think one thing is becoming clear, between the two of our results, we =
know memcache works well with SQLGrey ! ;-)
=20
Dave Strickler
MailWise LLC
617-933-5810 (direct)
www.mailwise.com ( http://www.mailwise.com/ )
"Intelligent E-mail Protection"





>>> Michael Storz <Mic...@lr...> 10:57 AM Monday, =
February 05, 2007 >>>
Hi Dave,

thanks for your tip. However I do not store SELECT statements in
memcached, but only the relevant information, e.g.

Feb  5 16:41:09 lxmhs25 lrzgrey_test[2773]: grey: _make_key: digest=3DjrVgw=
ImoOnIasrhipprBaw from from_awl:193.220.212.39,technical_support_op_291fv,f=
inanzen.sparkasse.de

And I use a carefully calculated expiration time, therefore the entry in
the cache times out at the same time as a entry in a AWL.

And as you can see, I get a tremendous hit rate for the states unknown und
throttle. This was my main goal. Getting hits for the AWLs is fine, but
for the overall performance not so relevant at least in our case.


On Mon, 5 Feb 2007, Dave Strickler wrote:

> Michael,
>

> 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).
>
> 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.
>
> I am currently getting a 40%-50% memcached hit rate with my alterations.
>
> Dave Strickler
> MailWise LLC
> 617-933-5810 (direct)
> www.mailwise.com ( http://www.mailwise.com/ )
> "Intelligent E-mail Protection"

Michael Storz
--
=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

Re: [Sqlgrey-users] multiple read/write database support patch

[Paul B. H. <he...@ac...>]

On Sun, 4 Feb 2007, Lionel Bouton wrote:

> I'm willing to test the patch, but I'd prefer to get one done against CVS
> if possible or at least in unified diff format to make it easier to
> integrate (the db_unavailable alone would be easy to handle but there is
> a piece of code in the rest of the patch I'm not yet very familiar with).

New patch in unified format attached... One minor change, my initial patch
resulted in the first connection being to the second server listed in the
configuration, which was nonintuitive. This patch connects to the first
listed server first.

Dan mentioned possibly changing the semantics of the existing db_host to
provide this functionality, which seemed to make sense, but would take a
bit more work to refactor all of the code currently depending on it.

Thanks...


-- 
Paul B. Henson  |  (909) 979-6361  |  http://www.csupomona.edu/~henson/
Operating Systems and Network Analyst  |  he...@cs...
California State Polytechnic University  |  Pomona CA 91768

Re: [Sqlgrey-users] multiple read/write database support patch

[Paul B. H. <he...@ac...>]

On Fri, 2 Feb 2007, Dan Faerch wrote:

> 1: Would allowing you to set the READ_HOSTS_NEVER_WRITE = 0 from config
> do the same for you? So ALL hosts defined are read & write hosts and
> switched between in a round robin manner.

I'm not sure, I didn't really look into DBIx. I don't really want to round
robin though; for any given instance of sqlgrey, I want it to use the same
database for all queries until a failure, and only switch to a different
one in the case of such a failure. I'm mostly interested in fault tolerance
at this point, not load balancing.


> 2: How scalable is your setup? We currently run 1 write-host and 7
> read-hosts. Can multi-master be used in such a setup? (a little off
> topic i guess;)

I believe you can scale to higher numbers of multimaster servers, but if I
remember correctly it is not a full mesh but they end up replicating
circularly so the failure of any one breaks the replication chain.


> If we need to specify more than 1 "write" hosts, would there be any
> logic in simply allowing multiple hosts to be defined on "db_host"
> instead (comma separated)? Just so we dont need an extra config
> directive to do the job that an existing directive could do.

That works for me, I just didn't want to change existing functionality.
Given that the degenerative case of setting db_hostlist to just one
instance is exactly the same as using db_host, changing the definition of
that configuration variable to accept a list does seem the simpler thing to
do.


-- 
Paul B. Henson  |  (909) 979-6361  |  http://www.csupomona.edu/~henson/
Operating Systems and Network Analyst  |  he...@cs...
California State Polytechnic University  |  Pomona CA 91768

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

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

Hi Dave,

thanks for your tip. However I do not store SELECT statements in
memcached, but only the relevant information, e.g.

Feb  5 16:41:09 lxmhs25 lrzgrey_test[2773]: grey: _make_key: digest=jrVgwImoOnIasrhipprBaw from from_awl:193.220.212.39,technical_support_op_291fv,finanzen.sparkasse.de

And I use a carefully calculated expiration time, therefore the entry in
the cache times out at the same time as a entry in a AWL.

And as you can see, I get a tremendous hit rate for the states unknown und
throttle. This was my main goal. Getting hits for the AWLs is fine, but
for the overall performance not so relevant at least in our case.


On Mon, 5 Feb 2007, Dave Strickler wrote:

> Michael,
>

> 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).
>
> 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.
>
> I am currently getting a 40%-50% memcached hit rate with my alterations.
>
> Dave Strickler
> MailWise LLC
> 617-933-5810 (direct)
> www.mailwise.com ( http://www.mailwise.com/ )
> "Intelligent E-mail Protection"

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] 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

[Sqlgrey-users] impressive results from using memcached

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

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
=========================================================
  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? :-)
--
======================================================
Leibniz-Rechenzentrum      |    <mailto:St...@lr...>
Boltzmannstr. 1            |    Fax: +49 89 35831-9700
85748 Garching / Germany   |    Tel: +49 89 35831-8840
======================================================

Re: [Sqlgrey-users] accepting mail if sqlgrey is down

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

> This certainly is a problem on the Postfix end, and nothing I have ever seen can 
> solve it other than a fix and a recompile of postfix.
>   
I thought (several times) about making a hyper small-and-clean "proxy" 
daemon to handle to connection between postfix and the policy daemons.. 
If the small proxy-daemon could be made robust enough, it could return 
"dunno" upon "no answer" from the policy-daemons.
This would be a somewhat unclean hack and im unsure if it would be too 
unclean to be of any use.

Just my 5 cents of thought.

- Dan

Re: [Sqlgrey-users] accepting mail if sqlgrey is down

[Andrew D. <and...@gm...>]

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html><head><title></title>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Dutf-8">
<meta http-equiv=3D"Content-Style-Type" content=3D"text/css">
<style type=3D"text/css"><!--
body {
  margin: 5px 5px 5px 5px;
  background-color: #ffffff;
}
/* ---------- Text Styles ---------- */
hr { color: #000000}
body, table /* Normal text */
{
 font-size: 9pt;
 font-family: 'Courier New';
 font-style: normal;
 font-weight: normal;
 color: #000000;
 text-decoration: none;
}
span.rvts1 /* Heading */
{
 font-size: 10pt;
 font-family: 'Arial';
 font-weight: bold;
 color: #0000ff;
}
span.rvts2 /* Subheading */
{
 font-size: 10pt;
 font-family: 'Arial';
 font-weight: bold;
 color: #000080;
}
span.rvts3 /* Keywords */
{
 font-size: 10pt;
 font-family: 'Arial';
 font-style: italic;
 color: #800000;
}
a.rvts4, span.rvts4 /* Jump 1 */
{
 font-size: 10pt;
 font-family: 'Arial';
 color: #008000;
 text-decoration: underline;
}
a.rvts5, span.rvts5 /* Jump 2 */
{
 font-size: 10pt;
 font-family: 'Arial';
 color: #008000;
 text-decoration: underline;
}
span.rvts6
{
 font-family: 'tahoma';
 font-weight: bold;
 color: #ffffff;
 background-color: #0000ff;
}
span.rvts7
{
 font-family: 'tahoma';
}
a.rvts8, span.rvts8
{
 font-family: 'tahoma';
 color: #0000ff;
 text-decoration: underline;
}
span.rvts9
{
 font-family: 'tahoma';
 font-weight: bold;
}
span.rvts10
{
}
span.rvts11
{
 font-size: 8pt;
 font-family: 'arial';
 font-style: italic;
 color: #c0c0c0;
}
/* ---------- Para Styles ---------- */
p,ul,ol /* Paragraph Style */
{
 text-align: left;
 text-indent: 0px;
 padding: 0px 0px 0px 0px;
 margin: 0px 0px 0px 0px;
}
.rvps1 /* Centered */
{
 text-align: center;
}
--></style>
</head>
<body>

<p>On Sunday, February 4, 2007, 11:27:38 AM, Dave Strickler wrote:</p>
<p><br></p>
<div><table border=3D0 cellpadding=3D1 cellspacing=3D2 style=3D"border-colo=
r: #000000; border-style: solid;">
<tr valign=3Dtop>
<td width=3D11 style=3D"background-color: #0000ff;">
<p><span class=3Drvts6>&gt;</span></p>
</td>
<td width=3D709 style=3D"background-color: #ffffff;">
<p><span class=3Drvts7>Andrew,</span></p>
<p><span class=3Drvts7>&nbsp;</span></p>
<p><span class=3Drvts7>It doesn't actually reject the email, it just defers=
 it with a "450" warning. However, it would be nice if it allowed the email=
 through, on the logic that if it can't filter, it should "pass by default"=
.</span></p>
<p><span class=3Drvts7>&nbsp;</span></p>
<p><span class=3Drvts7>This certainly is a problem on the Postfix end, and =
nothing I have ever seen can solve it other than a fix and a recompile of p=
ostfix.</span></p>
<p><span class=3Drvts7>&nbsp;</span></p>
</td>
</tr>
</table>
</div>
<p><br></p>
<p>That's what I needed to know. &nbsp;Thanks.</p>
<p><br></p>
<p><span class=3Drvts11>--&nbsp;</span></p>
<p><span class=3Drvts11>Best regards,</span></p>
<p><span class=3Drvts11>&nbsp;Andrew Diederich&nbsp;</span></p>

</body></html>

Re: [Sqlgrey-users] multiple read/write database support patch

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

Dan Faerch wrote the following on 03.02.2007 02:26 :
> Hey Paul.
>
> Thanks for the patch. Its pretty nice and clean :)
>
> Paul B. Henson wrote:
>   
> [...]
>   
>> Also, the patch moves the logging of the database error to before the
>> call to db_unavailable(). For some reason, DBI::errstr lost its value
>> during the call to db_unavailable(),

I see. disconnect() probably destroy $DBI:errstr. I didn't pay much
attention to this problem, but the solution seems obvious now :-)

>>  and with the logging message
>> afterwards, no error was printed.
>>   
>>     
> Im hoping Lionel has the time to double-check this. I dont know much 
> about that part of the code.:)
>   

I'm willing to test the patch, but I'd prefer to get one done against
CVS if possible or at least in unified diff format to make it easier to
integrate (the db_unavailable alone would be easy to handle but there is
a piece of code in the rest of the patch I'm not yet very familiar with).

Lionel.

Re: [Sqlgrey-users] Working on SQLgrey

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

Lionel Bouton wrote the following on 04.02.2007 22:11 :
> Syslog related crashes are fixed in CVS (I went the "eval" way for the
> curious). There is only one last problem : SQLgrey can't start if syslog
> doesn't work (nothing much to do here I believe).

Surprisingly it starts. I'm not sure it will connect to the daemon
afterwards though. You may have to restart SQLgrey to enable logging.
People willing to use CVS code are welcomed to comment on the results
they get.

Lionel

Re: [Sqlgrey-users] Working on SQLgrey

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

Lionel Bouton wrote the following on 04.02.2007 20:32 :
> Just a quick line.
>
> I commited some changes in the 1.7.x branch:
> - SQLgrey now optionally cache prepared statements (by default it no
> longer does). This is an attempt to workaround the memory leaks that
> occur with some DBI/DBD versions. People looking for the best
> performance will have to use db_prepare_cache = 1 in the configuration file.
> - Added "Reuse => 1". This should allow SQLgrey to restart more quickly.
> - Removed a warning log message when there's no entry in the config
> table (this occurs when we look for the db_last_cleanup entry for the
> first time).
>
> Lionel, now going after the syslog-related crash.
>   

Syslog related crashes are fixed in CVS (I went the "eval" way for the
curious). There is only one last problem : SQLgrey can't start if syslog
doesn't work (nothing much to do here I believe). It only support syslog
being shutdown during operation. This should take care of the problems
people reported though.

This is in the 1.7.x branch. 1.6 backport and a 1.6.8 release will
follow later.

Lionel

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

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

Riaan Kok wrote:
> Although.. (just got a random (he) idea here..)  instead of an
> invasive patch, can't we just sort the hash of rules upon creation by
> the second layer $rulenr value?  Is it possible?  (David?)
>
>   
Youd still need to make a new hash structure, since:
client_name -> rule_nr_4
                     - rule_nr_2
                     - rule_nr_5
helo_name ->  rule_nr_3
                     - rule_nr_7
                     - rule_nr_1

sorted would look like:

client_name -> rule_nr_2
                     - rule_nr_4
                     - rule_nr_5
helo_name ->  rule_nr_1
                     - rule_nr_3
                     - rule_nr_7

and be applied in that order.



- Dan

Re: [Sqlgrey-users] accepting mail if sqlgrey is down

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

On 04/02/07, Dave Strickler <dst...@ma...> wrote:
>
>  Riaan,
>
> Not being a Perl programmer on this end, can you explain the downside of
> not using "prepair_cached"? I would assume this means it would need to
> reconnect to the SQL server, or am I missing the point? What does
> prepair_cached buy you in performance?
>
> Dave Strickler
>

I'm no expert, but I'm a fair googler!  This page has an interesting
paragraph on this topic:
http://www.saturn5.com/~jwb/dbi-performance.html

"Every time DBI's prepare method is called, DBI parses the SQL command
looking for placeholder strings, and does some housekeeping work. Worse, a
context has to be built on the client and server sides of the connection
which the database will use to refer to the statement. These things take
time, and by eliminating these steps the time can be saved."

I have no idea what the real world penalty is for not using prepare_cached
in sqlgrey (or any) context, but for me it was either that or have a major
memory leak.  (In my DBI implementation, prepare_cached's cache didn't seem
to know about expiring old stuff, so the decision was easy..)  This is a
database and setup specific thing, so all I'm suggesting is that if you're
seeing a memory leak, consider the possibility of a faulty cache
implementation in your database library code.

I've had little success in finding information about prepare_cache's inner
workings..  how long cache elements stay valid, how they expire, how far it
will grow, or even what the expected performance gains might be, what (if
any) controls there are, how different databases are affected..  anybody
that could shed some light here, I'd appreciate it too!

Anyway, it did strike me that if the query changes, a different handle gets
returned, therefore any query that contains timestamp calculations outside
of a bound parameter will not benefit from the handle cache...

One thing I'll say for sure Dave, it works on a completely different level
from your memcache project (sql handle cache vs. data cache).  For a
specific sql statement, memcache could hold a few items already, and
prepare_cached could theoretically help a bit if new data is fetched from DB
using that same query.  So, they should co-exist happily!

Riaan

[Sqlgrey-users] Working on SQLgrey

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

Just a quick line.

I commited some changes in the 1.7.x branch:
- SQLgrey now optionally cache prepared statements (by default it no
longer does). This is an attempt to workaround the memory leaks that
occur with some DBI/DBD versions. People looking for the best
performance will have to use db_prepare_cache = 1 in the configuration file.
- Added "Reuse => 1". This should allow SQLgrey to restart more quickly.
- Removed a warning log message when there's no entry in the config
table (this occurs when we look for the db_last_cleanup entry for the
first time).

Lionel, now going after the syslog-related crash.

Re: [Sqlgrey-users] accepting mail if sqlgrey is down

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

Riaan,
=20
Not being a Perl programmer on this end, can you explain the downside of =
not using "prepair_cached"? I would assume this means it would need to =
reconnect to the SQL server, or am I missing the point? What does =
prepair_cached buy you in performance?
=20
Dave Strickler
MailWise LLC
617-933-5810 (direct)
www.mailwise.com ( http://www.mailwise.com/ )
"Intelligent E-mail Protection"





>>> "Riaan Kok" <ria...@gm...> 12:55 PM Sunday, February 04, 2007 =
>>>

On 03/02/07, Andrew Diederich <and...@gm... > wrote:

Slightly off-topic: I have to restart sqlgrey fairly often because of
a memory leak in the non-sqlgrey DBI portion.  I've seen that if
sqlgrey is down, because postfix can't connect to the daemon, it
rejects the mail.  Is it possible to, say, add DUNNO somehow so if=20
there is no result, postfix lets the mail through?  Here's what my
smtpd_recipient_restrictions looks like today.  Thanks for the help.

--
Andrew Diederich




We're out of luck there..=20
Here's what Wietse has to say on the topic:
http://archives.neohapsis.com/archives/postfix/2006-12/1573.html=20

However, my DBI setup also had a memory leak, which I traced to a =
not-quite-kosher prepare_cached implementation down the line.  Replacing =
"prepare_cached" in sqlgrey with "prepare" globally fixed it for me.  =
Maybe just maybe it helps for somebody else as well..

good luck,
Riaan


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