sqlgrey-users — Users of SQLgrey

Re: [Sqlgrey-users] Frequent disconnect/reconnect issues

[Alex <mys...@gm...>]

Hi guys,

> I'd expect sqlgrey to be configured to log to the
> mail log, although I suppose yours could be different.
> This will give you timestamps to use when looking through
> the db log.

I've figured out some new information on this problem.

Aug 18 06:06:19 mail01 sqlgrey: warning: Use of uninitialized value
$DBI::errstr in concatenation (.) or string at /usr/sbin/sqlgrey line
250.
Aug 18 06:06:19 mail01 sqlgrey: dbaccess: warning: couldn't do
query:#012INSERT INTO from_awl (sender_name, sender_domain, src,
first_seen, last_seen)
VALUES('return','nl2go.com','91.121.21','2015-08-18
05:59:38',NOW()):#012, reconnecting to DB
Aug 18 06:06:19 mail01 sqlgrey: dbaccess: Using DBIx:DBCluster
Aug 18 06:06:19 mail01 sqlgrey: dbaccess: Read_hosts: localhost,
ns1.example.com, mail02.example.com, mail03.example.com,
mail01.example.com

Aug 17 01:43:40 mail02 sqlgrey: dbaccess: warning: couldn't do
query:#012INSERT INTO from_awl (sender_name, sender_domain, src,
first_seen, last_seen)
VALUES('financeiro','teamplayy.com.br','91.121.21','2015-08-17
01:37:00',NOW()):#012, reconnecting to DB
Aug 17 01:53:29 mail01 sqlgrey: dbaccess: warning: couldn't do
query:#012INSERT INTO from_awl (sender_name, sender_domain, src,
first_seen, last_seen)
VALUES('shefdemwp','uwerim.ru','91.121.21','2015-08-17
01:46:49',NOW()):#012, reconnecting to DB
Aug 17 05:57:58 mail02 sqlgrey: dbaccess: warning: couldn't do
query:#012INSERT INTO from_awl (sender_name, sender_domain, src,
first_seen, last_seen)
VALUES('conor.burrell','businessandfinance.com','91.121.21','2015-08-17
05:51:17',NOW()):#012, reconnecting to DB
Aug 17 09:43:48 mail01 sqlgrey: dbaccess: warning: couldn't do
query:#012INSERT INTO from_awl (sender_name, sender_domain, src,
first_seen, last_seen)
VALUES('claudinei.moreira','alper.com.br','91.121.21','2015-08-17
09:37:12',NOW()):#012, reconnecting to DB
Aug 17 10:26:43 mail01 sqlgrey: dbaccess: warning: couldn't do
query:#012UPDATE domain_awl SET last_seen = NOW(), first_seen =
first_seen WHERE sender_domain = NULL AND src = NULL:#012Can't connect
to MySQL server on 'ns1.example.com' (111), reconnecting to DB
Aug 17 12:44:38 mail01 sqlgrey: dbaccess: warning: couldn't do
query:#012INSERT INTO from_awl (sender_name, sender_domain, src,
first_seen, last_seen)
VALUES('memberservices=example-marketplace.com__#-21wpwe5yvrgp4h','rlie15guji343ap6.mnrzh7tg8d0vr194.m8vlizz.8-amgceai.na28.bnc.salesforce.com','136.146.208','2015-08-17
12:39:23',NOW()):#012, reconnecting to DB

Our server was changed to "example" in the above. It looks like maybe
some records were corrupted? Multiple entries contain the 91.121.21
address. Should I just truncate all the tables and let them rebuild
themselves with new email data or perhaps there's a more precise fix?

Thanks,
Alex

Re: [Sqlgrey-users] Frequent disconnect/reconnect issues

[Karl O. P. <ko...@me...>]

On Sat, 15 Aug 2015 19:56:06 +0200
Jean-Michel Pouré - GOOZE <jm...@go...> wrote:

> If you need reliable solutions,
> use a software made and released by communities.

I would say the following: If you need a reliable, by which
I mean long-term, solution use software which
has a community _and_ can be forked by that community.

Even better, use software with a copyleft license.
Copyleft prevents proprietary interests from taking
the software and competing with the community with
the intent to marginalize it. (E.g. The BSD license
let Sun and later Apple marginalize the BSD based OSs.)


Karl <ko...@me...>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein

Re: [Sqlgrey-users] Frequent disconnect/reconnect issues

[Jean-Michel P. - G. <jm...@go...>]

Le samedi 15 août 2015 à 10:49 -0500, Karl O. Pinc a écrit :
> There is MariaDB, the future of MySQL
> moving forward.

Quoting website :***© 2015 MariaDB Corporation Ab except as noted. MariaDB is a trademark or registered trademarks of MariaDB Corporation Ab in the European Union and United States of America and/or other countries. This website is proud to run on MariaDB. MySQL is a trademark of Oracle Corporation Inc. Trademarks Statement | Privacy Policy***
IMHO, This is a company picking-up free software, not a real community relying on individuals, like Kernel.org, X.Org, PostgreSQL.org.
If you need reliable solutions, use a software made and released by communities. If you have access to a dedicated server, justinstall SQLgrey and PostgreSQL and you will be able to run thousands of queries per second, reliably.
The big issue with MySQL is that it dies silently and you don't know what happened to you data, because at some pointMySQL is using query caching and you cannot trace.
Anyway, I wron't start a flame war. Go and use MariaDB if you think this is the future. But don't expect to run more than 10.000 queries per second reliably.

Re: [Sqlgrey-users] Frequent disconnect/reconnect issues

[Alex <mys...@gm...>]

Hi,

Thanks everyone for your help. It worked with mariadb for months and
months without this problem. I'm going to try and dump the database
and restore it, since that's the easiest.

Thanks again,
Alex

On Sat, Aug 15, 2015 at 11:49 AM, Karl O. Pinc <ko...@me...> wrote:
> On Sat, 15 Aug 2015 13:18:02 +0200
> Jean-Michel Pouré - GOOZE <jm...@go...> wrote:
>
>> Le vendredi 14 août 2015 à 12:58 -0400, Alex a écrit :
>> > Would rebuilding the database help?
>>
>> MySQL is dead, has no community, no future (belongs to Oracle)and we
>> cannot loose time debugging which does not and will not ever work.
>
> That's not entirely fair.  There is MariaDB, the future of MySQL
> moving forward.
>
> I have technical issues with MySQL/MaraiaDB, but putting those aside
> the serious problem that has always existed with them is
> that their documentation is not free (as in Libre).  A db is no
> good without documentation, which matters if you care
> about vendor lock-in.
>
>
>
> Karl <ko...@me...>
> Free Software:  "You don't pay back, you pay forward."
>                  -- Robert A. Heinlein
>
> ------------------------------------------------------------------------------
> _______________________________________________
> Sqlgrey-users mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users

Re: [Sqlgrey-users] Frequent disconnect/reconnect issues

[Karl O. P. <ko...@me...>]

On Sat, 15 Aug 2015 13:18:02 +0200
Jean-Michel Pouré - GOOZE <jm...@go...> wrote:

> Le vendredi 14 août 2015 à 12:58 -0400, Alex a écrit :
> > Would rebuilding the database help?
> 
> MySQL is dead, has no community, no future (belongs to Oracle)and we
> cannot loose time debugging which does not and will not ever work.

That's not entirely fair.  There is MariaDB, the future of MySQL
moving forward.

I have technical issues with MySQL/MaraiaDB, but putting those aside
the serious problem that has always existed with them is
that their documentation is not free (as in Libre).  A db is no 
good without documentation, which matters if you care
about vendor lock-in.



Karl <ko...@me...>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein

Re: [Sqlgrey-users] Frequent disconnect/reconnect issues

[Jean-Michel P. - G. <jm...@go...>]

Le vendredi 14 août 2015 à 12:58 -0400, Alex a écrit :
> Would rebuilding the database help?

MySQL is dead, has no community, no future (belongs to Oracle)and we cannot loose time debugging which does not and will not ever work. 
Maybe you should make a clean installation of PostgreSQL and use pgAdmin3 GUI to query the database remotely. 
This is a very clean interface which will give all information. 
Kind regards,Jean-Michel

Re: [Sqlgrey-users] Frequent disconnect/reconnect issues

[Karl O. P. <ko...@me...>]

I prefer to stay far away from MySQL/MariaDB.  
Having said that it seems
like the first thing you should do is instrument the db
so that you can tell when connections might be lost for
some reason.  You get the message when a db connection
goes bad for some reason or another.  (For me, it's when
my Postgres DB restarts due to security update.)

You could ask the MariaDB people for help with this.

If you followed the install directions your db was created
the same way as everybody else's.  You could re-create
it the same way, but I don't see a point until you
know what's wrong.

I'd expect sqlgrey to be configured to log to the
mail log, although I suppose yours could be different.
This will give you timestamps to use when looking through
the db log.

On Fri, 14 Aug 2015 12:58:14 -0400
Alex <mys...@gm...> wrote:

> Hi guys,
> 
> I sent the message below a few days ago, and haven't received any
> responses. I'm still having difficulty with this and really don't know
> how to resolve it. It appears to happen to all three systems, two of
> which are on the same gigabit network, and the other is on a remote
> cable connection (which is the one that least frequently fails).
> 
> Would rebuilding the database help? I haven't even been able to find
> an error in the logs related to this, only the email that is sent
> every time it happens.
> 
> Thanks,
> Alex
> 
> 
> On Tue, Aug 11, 2015 at 7:13 PM, Alex <mys...@gm...> wrote:
> > Hi,
> >
> > I've been running sqlgrey on fedora for quite a while, set up to use
> > DB_Cluster with a single master set up with mysql (mariadb).
> > Recently, I've started to receive a large number of "sqlgrey
> > encountered an SQL error and triggered a reconnection" messages,
> > followed immediately by a reconnection message.
> >
> > It seems to happen at all times during the day, not just when the
> > network or server is congested or busy.
> >
> > There are three read_hosts, including the master itself, but one of
> > the hosts has multiple DNS names, so it may be listed twice.
> >
> > I don't exactly remember how the database schema was set up, which
> > is kind of a problem if I ever had to set it up again.
> >
> > I've included the sqlgrey.conf config below, in hopes someone had
> > some ideas how to troubleshoot this. This is the config that's been
> > working fine for more than a year.
> >
> > # cat sqlgrey.conf |grep '^[a-z|A-Z]'
> > loglevel = 3
> > log_override = whitelist:1,grey:3,spam:2
> > reconnect_delay = 5
> > awl_age = 31
> > group_domain_level = 10
> > db_type = mysql
> > db_name = sqlgrey
> > db_host = ns1.example.com;mysql_connect_timeout=1
> > db_port = default
> > db_user = sqlgrey
> > db_pass = mypass
> > db_cleanup_hostname=ns1.example.com
> > db_cleandelay = 1800
> > clean_method = sync
> > db_cluster = on
> > read_hosts=localhost,ns1.example.com,mail02.example.com,mail03.example.com,mail01.example.com
> > prepend = 1
> > admin_mail = 212...@me...
> >
> > Thanks,
> > Alex
> 
> ------------------------------------------------------------------------------
> _______________________________________________
> Sqlgrey-users mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
> 





Karl <ko...@me...>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein

Re: [Sqlgrey-users] Frequent disconnect/reconnect issues

[Alex <mys...@gm...>]

Hi guys,

I sent the message below a few days ago, and haven't received any
responses. I'm still having difficulty with this and really don't know
how to resolve it. It appears to happen to all three systems, two of
which are on the same gigabit network, and the other is on a remote
cable connection (which is the one that least frequently fails).

Would rebuilding the database help? I haven't even been able to find
an error in the logs related to this, only the email that is sent
every time it happens.

Thanks,
Alex


On Tue, Aug 11, 2015 at 7:13 PM, Alex <mys...@gm...> wrote:
> Hi,
>
> I've been running sqlgrey on fedora for quite a while, set up to use
> DB_Cluster with a single master set up with mysql (mariadb). Recently,
> I've started to receive a large number of "sqlgrey encountered an SQL
> error and triggered a reconnection" messages, followed immediately by
> a reconnection message.
>
> It seems to happen at all times during the day, not just when the
> network or server is congested or busy.
>
> There are three read_hosts, including the master itself, but one of
> the hosts has multiple DNS names, so it may be listed twice.
>
> I don't exactly remember how the database schema was set up, which is
> kind of a problem if I ever had to set it up again.
>
> I've included the sqlgrey.conf config below, in hopes someone had some
> ideas how to troubleshoot this. This is the config that's been working
> fine for more than a year.
>
> # cat sqlgrey.conf |grep '^[a-z|A-Z]'
> loglevel = 3
> log_override = whitelist:1,grey:3,spam:2
> reconnect_delay = 5
> awl_age = 31
> group_domain_level = 10
> db_type = mysql
> db_name = sqlgrey
> db_host = ns1.example.com;mysql_connect_timeout=1
> db_port = default
> db_user = sqlgrey
> db_pass = mypass
> db_cleanup_hostname=ns1.example.com
> db_cleandelay = 1800
> clean_method = sync
> db_cluster = on
> read_hosts=localhost,ns1.example.com,mail02.example.com,mail03.example.com,mail01.example.com
> prepend = 1
> admin_mail = 212...@me...
>
> Thanks,
> Alex

Re: [Sqlgrey-users] Testing a middle-size to large SQLgrey installation

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

On Wed, Aug 12, 2015 at 12:29:56PM +0200, Jean-Michel Pouré - GOOZE wrote:
> Dear all,
> 
> I would like to build a middle/big size SQLgrey database for testing,
> in a situation of a middle-size ISP.
> 
> My first settings show that for each user, 50 to 100 records are added
> everyday to the database. Records are added and removed, so we need to
> monitor insertion, deletion, query.
> 
> If I consider 1 million users, 1 day to reconnect, it is a database of:
> 1.000.000 x 50 = 50 million records added and deleted everyday.
> 
> Is this the kind of size that SQLgrey is facing? If this is the case,
> we probably need very special indexing (or no indexing at all).
> 
> In your opinion, what is the size of a large SQLgrey database?
> 
> Kind regards,
> Jean-Michel

Hi Jean-Michel,

50m adds/deletes = 100m transactions/day which works out to 1157 per sec.
Even with a standard hard drive, with enough memory and with sync_commit
off, this is easily manageable for a completely in-memory DB and it is
not even large.

Regards,
Ken

Re: [Sqlgrey-users] Congratulations & PostgreSQL issue

[Jean-Michel P. - G. <jm...@go...>]

Le mercredi 12 août 2015 à 14:56 +0200, Lionel Bouton a écrit :
> You only have to use EXPLAIN on queries done by SQLgrey on your 
> database
> to verify this.

Sorry, I replied before reading this. 
I am convinced, your index is very interesting indeed.

Kind regards,
Jean-Michel

Re: [Sqlgrey-users] Congratulations & PostgreSQL issue

[Jean-Michel P. - G. <jm...@go...>]

Le mercredi 12 août 2015 à 14:52 +0200, Lionel Bouton a écrit :
> You don't need these indexes and switching from the from_awl_pkey to
> these indexes would slow SQLgrey. Run EXPLAIN on any query done by
> SQLgrey and you should see that it uses the from_awl_pkey index (if 
> it's
> not, your database is probably not in a sane state).

Okay, thanks. I am not sure that an index on several columns is useful as a primary index."from_awl_pkey" PRIMARY KEY, btree (src, sender_domain, sender_name)
This is not the same as three indexes and can be very fast when a query is being run on all those three columns. Otherwize, it should be slow.
I will try to create a fake database and will report later.
Kind regards,Jean-Michel

Re: [Sqlgrey-users] Congratulations & PostgreSQL issue

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

Le 12/08/2015 12:10, Jean-Michel Pouré - GOOZE a écrit :
> Another issue about:
> CREATE INDEX connect_idx
>   ON "connect"
>   USING btree
>   (src , sender_domain , sender_name);
>
> This is an index on three columns. It is effective only if the SQL
> query includes these three columns.

No, that's not the case. It is used when only the first or the first and
second column are used in a query.

>  It can be very fast, but it should
> not be considered the same as THREE different indexes. I agree it is
> not very well documented in PostgreSQL documentation, but all databases
> work the same and this is only effective in a limited number of
> queries.

And the order of the columns in this index was used so that all SQLgrey
queries can be sped up by this index. Having three indexes instead of
one would be both slower when updating or inserting data in the database
and when selecting records based on conditions on multiple columns like
SQLgrey does.
You only have to use EXPLAIN on queries done by SQLgrey on your database
to verify this.

Best regards,

Lionel.

Re: [Sqlgrey-users] Congratulations & PostgreSQL issue

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

Le 12/08/2015 10:57, Jean-Michel Pouré - GOOZE a écrit :
> [...]
> * If we take the example of from_awl table, there are only two indexes:
> main OID

No:

sqlgrey=> \d from_awl
                 Table "public.from_awl"
    Column     |            Type             | Modifiers
---------------+-----------------------------+-----------
 sender_name   | character varying(64)       | not null
 sender_domain | character varying(255)      | not null
 src           | character varying(39)       | not null
 first_seen    | timestamp without time zone | not null
 last_seen     | timestamp without time zone | not null
Indexes:
    "from_awl_pkey" PRIMARY KEY, btree (src, sender_domain, sender_name)
    "from_awl_lseen" btree (last_seen)


There is no index on OID in the SQLgrey database.

>  and lastseen. Were need more:
> CREATE INDEX from_awl_sendername
>   ON from_awl
>   USING btree
>   (sender_name);
>
> CREATE INDEX from_awl_senderdomain
>   ON from_awl
>   USING btree
>   (sender_domain);
>
> CREATE INDEX from_awl_src
>   ON from_awl
>   USING btree
>   (src);
>   
> CREATE INDEX from_awl_fseen
>   ON from_awl
>   USING btree
>   (first_seen);

You don't need these indexes and switching from the from_awl_pkey to
these indexes would slow SQLgrey. Run EXPLAIN on any query done by
SQLgrey and you should see that it uses the from_awl_pkey index (if it's
not, your database is probably not in a sane state).

Best regards,

Lionel

[Sqlgrey-users] Testing a middle-size to large SQLgrey installation

[Jean-Michel P. - G. <jm...@go...>]

Dear all,

I would like to build a middle/big size SQLgrey database for testing,
in a situation of a middle-size ISP.

My first settings show that for each user, 50 to 100 records are added
everyday to the database. Records are added and removed, so we need to
monitor insertion, deletion, query.

If I consider 1 million users, 1 day to reconnect, it is a database of:
1.000.000 x 50 = 50 million records added and deleted everyday.

Is this the kind of size that SQLgrey is facing? If this is the case,
we probably need very special indexing (or no indexing at all).

In your opinion, what is the size of a large SQLgrey database?

Kind regards,
Jean-Michel

Re: [Sqlgrey-users] Congratulations & PostgreSQL issue

[Jean-Michel P. - G. <jm...@go...>]

Another issue about:
CREATE INDEX connect_idx
  ON "connect"
  USING btree
  (src , sender_domain , sender_name);

This is an index on three columns. It is effective only if the SQL
query includes these three columns. It can be very fast, but it should
not be considered the same as THREE different indexes. I agree it is
not very well documented in PostgreSQL documentation, but all databases
work the same and this is only effective in a limited number of
queries.

Anyway, I don't have enough data to start a performance test. I will
write another message on the list to launch a small testing project.


Kind regards,
Jean-Michel

Re: [Sqlgrey-users] Congratulations & PostgreSQL issue

[Jean-Michel P. - G. <jm...@go...>]

Dear Lionel,

Thanks for your answers. I agree with most of them, except the indexing
issue. I a completely opposed to database clusters, they do not work
and never will. 

PostgreSQL (and other databases) work like that:

* Each SQL query is parsed and analysed before execution. The SQL
optimizer rewrites the query and executes the best "guessed" query
plan. A query plan is a number of steps needed to execute the query.

* There are basically two steps in a query plan : indexes lookups (aka
memory) and disc lookups.

* Disc lookups will trigger more CPU cycles. If your table "from_awl"
has 1.000.000 entries and you need to go through all records without
indexing, it will take you at least 1.000.000 CPU cycles if you using
an SSD disc and ten times more if using a SATA disc. On the converse,
indexes allow the database to pick-up exactly the needed records. So if
there are only let's say 5 records in the result and you are using
indexes, it will eat-up only 5 CPU cycles. 

The number of CPU cycles is approximate, but the idea behind it there.

Even if a table has a unique index on OID, it will not speed-up a
search on a table. You need indexing on EACH colum which is used in
queries where speed matters.

* If we take the example of from_awl table, there are only two indexes:
main OID and lastseen. Were need more:

CREATE INDEX from_awl_sendername
  ON from_awl
  USING btree
  (sender_name);

CREATE INDEX from_awl_senderdomain
  ON from_awl
  USING btree
  (sender_domain);

CREATE INDEX from_awl_src
  ON from_awl
  USING btree
  (src);
  
CREATE INDEX from_awl_fseen
  ON from_awl
  USING btree
  (first_seen);

A nice PotgreSQL server with enough memory and SSD disc can reach a
huge amount of queries. I think 100.000 queries each second should not
be a problem.

If you need to reconnect for each query, you can create a dedicated
user with read-only access to tables and allow authentication without
password in PostgreSQL. 

There are also bottlenecks on client side. I did not read the Perl
code, but all data searching should be done at SQL level and avoid
searching using Perl code.

To know the real performance of a database, it is possible to log slow
queries. In PostgreSQL it is done by setting in postgresql.conf:
log_min_duration_statement = 30

This is 30 milliseconds.

It should be possible to create a small script and populate the
database with millions of records and see how it works on high loads.
No SQL query should run in more than 30 ms.

Kind regards,
Jean-Michel

Re: [Sqlgrey-users] Congratulations & PostgreSQL issue

[Karl O. P. <ko...@me...>]

On Wed, 12 Aug 2015 00:28:30 +0200
Lionel Bouton <lio...@bo...> wrote:

> Le 11/08/2015 22:26, Jean-Michel Pouré - GOOZE a écrit :

> > There might be modifications needed to stand "huge" loads under
> > PostgreSQL. For example, I think of as much as 10.000 queries per
> > second. This does not happen very often, I agree.
> 
> In theory you should be able to reach these levels with PostgreSQL
> easily even with write-intensive situations like SPAM storms if you
> use enterprise level SSD to store your database. I've not tested
> SQLgrey itself at these levels though (you may need to distribute the
> workload on multiple instances).

You could always throw hardware at it.  The latest Postgres
(at least) has multi-master capability.



Karl <ko...@me...>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein

Re: [Sqlgrey-users] An entry for clients_fqdn_whitelist

[Karl O. P. <ko...@me...>]

On Tue, 11 Aug 2015 12:58:29 -0500
"Karl O. Pinc" <ko...@me...> wrote:

> FYI
> 
> # Retries too rapidly, then retries one time each host from a large
> # pool of hosts all within a single class C subnet.
> *.hostedemail.com

Sorry, the proper description is:

# Retries instantly from a second host, then retries
# with delay once from each host of a large pool of hosts all
# within a single class C subnet.
*.hostedemail.com

Karl <ko...@me...>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein

[Sqlgrey-users] Frequent disconnect/reconnect issues

[Alex <mys...@gm...>]

Hi,

I've been running sqlgrey on fedora for quite a while, set up to use
DB_Cluster with a single master set up with mysql (mariadb). Recently,
I've started to receive a large number of "sqlgrey encountered an SQL
error and triggered a reconnection" messages, followed immediately by
a reconnection message.

It seems to happen at all times during the day, not just when the
network or server is congested or busy.

There are three read_hosts, including the master itself, but one of
the hosts has multiple DNS names, so it may be listed twice.

I don't exactly remember how the database schema was set up, which is
kind of a problem if I ever had to set it up again.

I've included the sqlgrey.conf config below, in hopes someone had some
ideas how to troubleshoot this. This is the config that's been working
fine for more than a year.

# cat sqlgrey.conf |grep '^[a-z|A-Z]'
loglevel = 3
log_override = whitelist:1,grey:3,spam:2
reconnect_delay = 5
awl_age = 31
group_domain_level = 10
db_type = mysql
db_name = sqlgrey
db_host = ns1.example.com;mysql_connect_timeout=1
db_port = default
db_user = sqlgrey
db_pass = mypass
db_cleanup_hostname=ns1.example.com
db_cleandelay = 1800
clean_method = sync
db_cluster = on
read_hosts=localhost,ns1.example.com,mail02.example.com,mail03.example.com,mail01.example.com
prepend = 1
admin_mail = 212...@me...

Thanks,
Alex

Re: [Sqlgrey-users] Congratulations & PostgreSQL issue

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

Le 11/08/2015 22:26, Jean-Michel Pouré - GOOZE a écrit :
> Dear Lionel,
>
> First, I would like to congratulate you for bringing SqlGrey to the
> community. I just discovered SqlGrey and it solved my SPAM problems.

Happy to be of service. Numerous others have helped too (see the CONTRIB
file).

>
> Together with SPF validation, DNSBL and other filtering techniques, I
> now have nearly zero spam.
>
> I am running Debian and I am a big fan of PostgreSQL. The automatic
> population of the database and the SQL code using prepared queries was
> a very nice surprise. 
>
> Previously, I tested other grey listing solutions and they don't have
> the simplicity and power of SqlGrey. I am surprised to see that some
> SqlGrey databases have been running years without incident.

This is a testament to the robustness of both the database systems and
SQLgrey.

>
> There might be modifications needed to stand "huge" loads under
> PostgreSQL. For example, I think of as much as 10.000 queries per
> second. This does not happen very often, I agree.

In theory you should be able to reach these levels with PostgreSQL
easily even with write-intensive situations like SPAM storms if you use
enterprise level SSD to store your database. I've not tested SQLgrey
itself at these levels though (you may need to distribute the workload
on multiple instances).

>
> First, I think it should be interesting to :
>
> * Use special the inet type for IPs and subnets
> http://www.postgresql.org/docs/current/static/datatype-net-types.html
>
> I believe that using the inet type should be compatible with current
> code in a fresh database.
>
> SELECT '216.34.181'::cidr =>  "216.34.181.0/24"
> SELECT '216.34.181.6'::inet => "216.34.181.6"

I considered this very early (and in fact I ran some code using these
datatypes the very first day I started developing SQLgrey). PostgreSQL
is by far my favourite RDBMS but I wanted SQLgrey to avoid being a
PostgreSQL-only project so I refrained from using proprietary datatypes.
The performance difference should be negligible as a string
representation doesn't have much storage overhead (probably <3x) and
allows us to use a very simple (and fast) string operation in Perl and
equality for matching class C networks using the src column. It is used
like this when using greymethod "smart" (default) or "classc". Using a
cidr datatype could be faster but I'm not aware of a single SQLgrey
installation where this would make a difference.

Switching to the cidr datatype could probably give a minor speedup in
PostgreSQL, but would stop SQLgrey support (or make it complex) for
SQLite and MySQL/MariaDB and would probably bring a minor slowdown in
the Perl part.

>
> * IP4r type could also be used from managing ranges of servers and
> spammers.IP4r reported 6000 queries per second on complex projects:
> https://www.siafoo.net/article/53

I'm not familiar with the IP4r module but I guess that my answer above
would apply to it too.

>
> * Add indexes on domain_awl and from_awl tables.

They are already indexed: the primary key automatically adds a unique
index which should be used by all queries (at least it was designed to).


>  The advantage of CIDR
> is that it will be very well indexed.

The string index is very efficient too, the only real drawback is the
storage. If you have a SQLgrey database where shaving half of the
storage requirement would be beneficial the cidr datatype might be
interesting to look at but I seriously doubt that such a database exists.

Best regards,

Lionel

[Sqlgrey-users] Congratulations & PostgreSQL issue

[Jean-Michel P. - G. <jm...@go...>]

Dear Lionel,

First, I would like to congratulate you for bringing SqlGrey to the
community. I just discovered SqlGrey and it solved my SPAM problems.

Together with SPF validation, DNSBL and other filtering techniques, I
now have nearly zero spam.

I am running Debian and I am a big fan of PostgreSQL. The automatic
population of the database and the SQL code using prepared queries was
a very nice surprise. 

Previously, I tested other grey listing solutions and they don't have
the simplicity and power of SqlGrey. I am surprised to see that some
SqlGrey databases have been running years without incident.

There might be modifications needed to stand "huge" loads under
PostgreSQL. For example, I think of as much as 10.000 queries per
second. This does not happen very often, I agree.

First, I think it should be interesting to :

* Use special the inet type for IPs and subnets
http://www.postgresql.org/docs/current/static/datatype-net-types.html

I believe that using the inet type should be compatible with current
code in a fresh database.

SELECT '216.34.181'::cidr =>  "216.34.181.0/24"
SELECT '216.34.181.6'::inet => "216.34.181.6"

* IP4r type could also be used from managing ranges of servers and
spammers.IP4r reported 6000 queries per second on complex projects:
https://www.siafoo.net/article/53

* Add indexes on domain_awl and from_awl tables. The advantage of CIDR
is that it will be very well indexed.

Indexes allow PostgreSQL planner to make clean search plans, mainly on
indexes and reducing disc access to what should be zero.

Just my 2 cents!
Kind regards,
Jean-Michel

[Sqlgrey-users] An entry for clients_fqdn_whitelist

[Karl O. P. <ko...@me...>]

FYI

# Retries too rapidly, then retries one time each host from a large
# pool of hosts all within a single class C subnet.
*.hostedemail.com




Karl <ko...@me...>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein

Re: [Sqlgrey-users] Auto-whitelisting recipient email addresses

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

Hi,


Le 23/07/2015 15:40, Bruce Bodger a écrit :
> [...]
> Lionel,
>
> Does this comment from the distributed clients_fqdn_whitelist still apply?
>
> # Note you need the following two lines to allow both
> # <lots of mtas>.example.com and example.com
> # *.example.com
> # example.com

Yes it does.
To be honest I wasn't happy about this little quirk when I coded the
whitelists support and with experience I would not do it again the same
way if I were starting from scratch. IIRC (I didn't look at SQLgrey code
for quite some time) this is needlessly linked to internal SQLgrey
structures involved in the whitelisting: regexp, globs and identity
matching are done separately for performance reasons. I think I should
have used a cleaner syntax and made the effort to translate it to the
internal structures instead of looking for a direct mapping.
As it didn't seem too bad and cleaner solutions would have broken
backward compatibility this was left like this (and the comment was
added to make sure people reading documentation like you were aware of it).

Best regards,

Lionel

Re: [Sqlgrey-users] Auto-whitelisting recipient email addresses

[Bruce B. <bb...@bo...>]

On Jul 3, 2015, at 5:26 PM, Lionel Bouton <lio...@bo...> wrote:

> On 07/04/15 00:21, Bruce Bodger wrote:
>> [...]
>> 
>> P.S.  Even though I've been running sqlgrey for 6 years I've never been 
>> sure what the whitelist requires.  Is   troubled.com equivalent to  
>> *.troubled.com?   My intention is to add all servers and users in the 
>> troubled.com domain.  I've been adding both.
> 
> The whitelisting is about the ips or fqdns of the mail servers
> contacting yours and not the addresses of the sender (so they might not
> share the same domain). If you are not sure about what they are you
> should be able to find them in your postfix logs.

Lionel,

Does this comment from the distributed clients_fqdn_whitelist still apply?

# Note you need the following two lines to allow both
# <lots of mtas>.example.com and example.com
# *.example.com
# example.com

Thank you,
Bruce

Re: [Sqlgrey-users] Auto-whitelisting recipient email addresses

[Bruce B. <bb...@bo...>]

On 7/3/15 5:26 PM, Lionel Bouton wrote:
> The whitelisting is about the ips or fqdns of the mail servers
> contacting yours and not the addresses of the sender (so they might not
> share the same domain). If you are not sure about what they are you
> should be able to find them in your postfix logs.
>
Thank you, Lionel.  Understood.

Regards,
~Bruce