sqlgrey-users

[Sqlgrey-users] Can't locate object method "connect"

[James M. <jm...@ae...>]

Hello,

I did the install for sqlgrey following directions in the INSTALL and  
HOWTO. My first errors, when trying to start, was it could not find  
DBI.pm where it was looking. I found it and manually copied it to the  
correct location, which may have been a mistake. Now my error is  
"Can't locate object method "connect" via package "DBI" at /usr/sbin/ 
sqlgrey line 805." Any advice would be appreciated.

SQLgrey 1.7.5 with Postfix 2.1.5 on a Mac G5 desktop OS 10.4.9

Thanks,
Jim

Re: [Sqlgrey-users] Can't locate object method "connect"

[Daniel J M. <dan...@au...>]

On Thu, 2007-03-29 at 11:24 -0500, James Medley wrote:
> Hello,
> 
> 
> I did the install for sqlgrey following directions in the INSTALL and
> HOWTO. My first errors, when trying to start, was it could not find
> DBI.pm where it was looking. I found it and manually copied it to the
> correct location, which may have been a mistake. Now my error is
> "Can't locate object method "connect" via package "DBI"
> at /usr/sbin/sqlgrey line 805." Any advice would be appreciated.

You need a DBD:: package to go with DBI specific to your database.

> 
-- 
Daniel J McDonald, CCIE # 2495, CISSP # 78281, CNX
Austin Energy
http://www.austinenergy.com

[Sqlgrey-users] Using multiple Connect tables

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

We have a lot (read: millions) of records in our Connect table inside of =
24 hours. Purging it down to a reasonable size for speed is very difficult =
as we are using Postgres. We are considering having multiple Connect =
tables in different databases - perhaps databases that begin with A =
through Z, thus giving us Connect tables that are 26 times as small, and =
thus very fast and manageable.
=20
Has anyone attempted this code change?
=20
Comments?
=20
Thanks,
=20
Dave

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] Using multiple Connect tables

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

Dave Strickler wrote the following on 29.03.2007 20:54 :

Please don't answer other threads to start new ones...

> We have a lot (read: millions) of records in our Connect table inside
> of 24 hours. Purging it down to a reasonable size for speed is very
> difficult as we are using Postgres.

What is the problem?

> We are considering having multiple Connect tables in different
> databases - perhaps databases that begin with A through Z, thus giving
> us Connect tables that are 26 times as small, and thus very fast and
> manageable.

Do you really want multiple servers ? You may get roughly the same
performance by adding more RAM to your existing server or tuning
PostgreSQL (26 less data should mean only 3x or 4x speedup, not 26x as
the query time follows a log(tablesize) rule and I very much doubt you'd
want 26 database servers to maintain just for greylisting). Plus SQLgrey
will have to regularly clean multiple tables instead of only one...

Note that the connect table usually shrinks down a little after 24 hours
(the autowhitelists kick in).

Do you use pg_autovacuum (or the integrated autovacuum daemon in 8.2 or
later versions)? It should help.

Lionel.

Re: [Sqlgrey-users] Using multiple Connect tables

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

On Thu, Mar 29, 2007 at 09:09:58PM +0200, Lionel Bouton wrote:
> Dave Strickler wrote the following on 29.03.2007 20:54 :
> 
> Please don't answer other threads to start new ones...
> 
> > We have a lot (read: millions) of records in our Connect table inside
> > of 24 hours. Purging it down to a reasonable size for speed is very
> > difficult as we are using Postgres.
> 
> What is the problem?
> 
> > We are considering having multiple Connect tables in different
> > databases - perhaps databases that begin with A through Z, thus giving
> > us Connect tables that are 26 times as small, and thus very fast and
> > manageable.
> 
> Do you really want multiple servers ? You may get roughly the same
> performance by adding more RAM to your existing server or tuning
> PostgreSQL (26 less data should mean only 3x or 4x speedup, not 26x as
> the query time follows a log(tablesize) rule and I very much doubt you'd
> want 26 database servers to maintain just for greylisting). Plus SQLgrey
> will have to regularly clean multiple tables instead of only one...
> 
> Note that the connect table usually shrinks down a little after 24 hours
> (the autowhitelists kick in).
> 
> Do you use pg_autovacuum (or the integrated autovacuum daemon in 8.2 or
> later versions)? It should help.
> 
> Lionel.
> 
Dave,

I agree with Lionel. You would be better off adding a bit more memory to
your DB server and tuning it rather than trying to split it up like that.
How long is a typical connect table lookup taking? an insert? an update?
You should definitely be using a recent PostgreSQL and be running with
autovacuum enabled. You should also tune it to be more agressive than the
default settings. How many queries/sec are you seeing? What is your bottle-
neck, I/O or CPU? Please post a few more configuration details, both hardware
and software, and we can provide more germane recommendations.

Ken

Re: [Sqlgrey-users] Using multiple Connect tables

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

Dave Strickler wrote the following on 29.03.2007 21:47 :
> (sorry on the answering-old-threads-to-start-new-ones... It's a old,
> bad habit and I will stop)
>  
> The problem we are having is a very old one. In about 24 hours, our
> Connect table will grow to 6 million records. We can purge it down,
> but that takes time, and what really hurts us is doing a VACUUM FULL
> which would write/read lock the database for long periods of time,
> therefore pulling SQLGrey "offline". We are not using pg_autovacuum,
> and perhaps this would be the solution for now.

Do not do a VACUUM FULL. This is for extreme conditions only (when the
disk space used becomes huge). VACUUM ANALYZE should be enough and won't
block SQLgrey (but probably slow it down a bit). pg_autovacuum will do
it for you but not as soon as you launch it, as it is designed to wait
for enough changes to a table to analyze it, so you'll have to launch a
VACUUM ANALYZE manually the first time.
1/ Note that if you stop PostgreSQL or prevent SQLgrey to connect to it,
it will switch to passthrough (handy if you need to take PostgreSQL
offline to tweak it for a few seconds/minutes).
2/ If you never ANALYZE, PostgreSQL might very well do full table scans
instead of using indexes which obviously will slow down SQLgrey
tremendously.

>  
> However, I have a larger question that asks, "what do we do when the
> Connect table reaches 10 million, or 20 million?" How large can it
> grow before I'm in trouble? Since I don't know it's upper limits, I
> fear I may run into them randomly one day.

It depends on you actual setup. I've run one old PostgreSQL 7.1 instance
with around this number of lines years ago and it didn't break. But it
could get too slow. You should compare the time needed to query the
connect table and the rate of connections to your SMTP servers. If your
query time is less than one tenth of the average delay between 2
connexions you should be safe.

If not, time to optimize. Throw memory at PostgreSQL and increase :
- shared_buffer (attention au delà d'un certain seuil l'OS ne peut pas
suivre et PostgreSQL ne démarrera pas), which should help maintain
index  in memory
- max_fsm_pages and max_fsm_relations (which should prevent too much
fragmentation of the on-disk data), run *VACUUM VERBOSE to check if
these parameters are enough at the end you will see something like:*
*INFO:  free space map: 12 relations, 2319 pages stored; 2464 total
pages needed*
*DETAIL:  Allocated FSM size: 1000 relations + 20000 pages = 182 kB
shared memory.*
****

be sure to be at least around the values reported by the INFO line.
**

- commit_demay, should help commit several writes at the same time,
- reduce commit_siblings to 2 (with SQLgrey you can have at most as much
sibling as SQLgrey servers, if you have only one, commit_* won't change
your performance),

Lionel.

Re: [Sqlgrey-users] Using multiple Connect tables

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

As usual, excellent advice from you - Thanks for your help !
 
We will try this out over the next week and let you know the results.
 
Dave Strickler
MailWise LLC
617-933-5810 (direct)
www.mailwise.com ( http://www.mailwise.com/ )
"Intelligent E-mail Protection"





>>> Lionel Bouton <lio...@bo...> 4:28 PM Thursday, March 29,
2007 >>>
Dave Strickler wrote the following on 29.03.2007 21:47 :
> (sorry on the answering-old-threads-to-start-new-ones... It's a old,
> bad habit and I will stop)
>  
> The problem we are having is a very old one. In about 24 hours, our
> Connect table will grow to 6 million records. We can purge it down,
> but that takes time, and what really hurts us is doing a VACUUM FULL
> which would write/read lock the database for long periods of time,
> therefore pulling SQLGrey "offline". We are not using pg_autovacuum,
> and perhaps this would be the solution for now.

Do not do a VACUUM FULL. This is for extreme conditions only (when the
disk space used becomes huge). VACUUM ANALYZE should be enough and
won't
block SQLgrey (but probably slow it down a bit). pg_autovacuum will do
it for you but not as soon as you launch it, as it is designed to wait
for enough changes to a table to analyze it, so you'll have to launch
a
VACUUM ANALYZE manually the first time.
1/ Note that if you stop PostgreSQL or prevent SQLgrey to connect to
it,
it will switch to passthrough (handy if you need to take PostgreSQL
offline to tweak it for a few seconds/minutes).
2/ If you never ANALYZE, PostgreSQL might very well do full table
scans
instead of using indexes which obviously will slow down SQLgrey
tremendously.

>  
> However, I have a larger question that asks, "what do we do when the
> Connect table reaches 10 million, or 20 million?" How large can it
> grow before I'm in trouble? Since I don't know it's upper limits, I
> fear I may run into them randomly one day.

It depends on you actual setup. I've run one old PostgreSQL 7.1
instance
with around this number of lines years ago and it didn't break. But it
could get too slow. You should compare the time needed to query the
connect table and the rate of connections to your SMTP servers. If
your
query time is less than one tenth of the average delay between 2
connexions you should be safe.

If not, time to optimize. Throw memory at PostgreSQL and increase :
- shared_buffer (attention au delà d'un certain seuil l'OS ne peut pas
suivre et PostgreSQL ne démarrera pas), which should help maintain
index  in memory
- max_fsm_pages and max_fsm_relations (which should prevent too much
fragmentation of the on-disk data), run *VACUUM VERBOSE to check if
these parameters are enough at the end you will see something like:*
*INFO:  free space map: 12 relations, 2319 pages stored; 2464 total
pages needed*
*DETAIL:  Allocated FSM size: 1000 relations + 20000 pages = 182 kB
shared memory.*
****

be sure to be at least around the values reported by the INFO line.
**

- commit_demay, should help commit several writes at the same time,
- reduce commit_siblings to 2 (with SQLgrey you can have at most as
much
sibling as SQLgrey servers, if you have only one, commit_* won't
change
your performance),

Lionel.

-------------------------------------------------------------------------
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=join.php&p=sourceforge&CID=DEVDEV

_______________________________________________
Sqlgrey-users mailing list
Sql...@li... 
https://lists.sourceforge.net/lists/listinfo/sqlgrey-users

This message has been certified virus-free by MailWise Filter - The real-time, intelligent, e-mail firewall used to scan inbound and outbound messages for SPAM, Viruses and Content. 

  For more information, please visit: http://www.mailwise.com

Re: [Sqlgrey-users] Can't locate object method "connect"

[Bob A. <apt...@cy...>]

Hi,

James Medley wrote:
> I did the install for sqlgrey following directions in the INSTALL and
> HOWTO. My first errors, when trying to start, was it could not find
> DBI.pm where it was looking. I found it and manually copied it to the
> correct location, which may have been a mistake.

Rather than copying the *.pm files, add

  use lib /path/to/alternate/libs

to sqlgrey. Documentation for the 'use lib' pragma is not super obvious
- see `perldoc perlmodlib` for more info.

hth,

-- Bob

Re: [Sqlgrey-users] Using multiple Connect tables

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

(sorry on the answering-old-threads-to-start-new-ones... It's a old, bad =
habit and I will stop)
=20
The problem we are having is a very old one. In about 24 hours, our =
Connect table will grow to 6 million records. We can purge it down, but =
that takes time, and what really hurts us is doing a VACUUM FULL which =
would write/read lock the database for long periods of time, therefore =
pulling SQLGrey "offline". We are not using pg_autovacuum, and perhaps =
this would be the solution for now.
=20
However, I have a larger question that asks, "what do we do when the =
Connect table reaches 10 million, or 20 million?" How large can it grow =
before I'm in trouble? Since I don't know it's upper limits, I fear I may =
run into them randomly one day.
=20
To be clear, SQLGrey is running great. It's when Postgres slows in dealing =
with a "not VACUUMed in the past 6 hours" Connect table that the problems =
start to arrise, and SQLGrey slows down because of this.
=20
Again, maybe your pg_autovacuum idea is the right choice... I think we =
should try that first.

>>> Lionel Bouton <lio...@bo...> 3:09 PM Thursday, March 29, =
2007 >>>
Dave Strickler wrote the following on 29.03.2007 20:54 :

Please don't answer other threads to start new ones...

> We have a lot (read: millions) of records in our Connect table inside
> of 24 hours. Purging it down to a reasonable size for speed is very
> difficult as we are using Postgres.

What is the problem?

> We are considering having multiple Connect tables in different
> databases - perhaps databases that begin with A through Z, thus giving
> us Connect tables that are 26 times as small, and thus very fast and
> manageable.

Do you really want multiple servers ? You may get roughly the same
performance by adding more RAM to your existing server or tuning
PostgreSQL (26 less data should mean only 3x or 4x speedup, not 26x as
the query time follows a log(tablesize) rule and I very much doubt you'd
want 26 database servers to maintain just for greylisting). Plus SQLgrey
will have to regularly clean multiple tables instead of only one...

Note that the connect table usually shrinks down a little after 24 hours
(the autowhitelists kick in).

Do you use pg_autovacuum (or the integrated autovacuum daemon in 8.2 or
later versions)? It should help.

Lionel.

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

Re: [Sqlgrey-users] Using multiple Connect tables

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

Lionel Bouton wrote the following on 29.03.2007 22:28 :
> [...]
> - shared_buffer (attention au delà d'un certain seuil l'OS ne peut pas
> suivre et PostgreSQL ne démarrera pas),

I must be tired, I switched to French without realizing it. Translation :
beware that past a threshold, the OS will return an error and PostgreSQL
won't start.

Re: [Sqlgrey-users] Using multiple Connect tables

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

Not a problem - Merci bien de votre aide !! ;-)
 
Dave Strickler
MailWise LLC
617-933-5810 (direct)
www.mailwise.com ( http://www.mailwise.com/ )
"Intelligent E-mail Protection"





>>> Lionel Bouton <lio...@bo...> 4:40 PM Thursday, March 29,
2007 >>>
Lionel Bouton wrote the following on 29.03.2007 22:28 :
> [...]
> - shared_buffer (attention au delà d'un certain seuil l'OS ne peut
pas
> suivre et PostgreSQL ne démarrera pas),

I must be tired, I switched to French without realizing it. Translation
:
beware that past a threshold, the OS will return an error and
PostgreSQL
won't start.


-------------------------------------------------------------------------
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=join.php&p=sourceforge&CID=DEVDEV

_______________________________________________
Sqlgrey-users mailing list
Sql...@li... 
https://lists.sourceforge.net/lists/listinfo/sqlgrey-users

This message has been certified virus-free by MailWise Filter - The real-time, intelligent, e-mail firewall used to scan inbound and outbound messages for SPAM, Viruses and Content. 

  For more information, please visit: http://www.mailwise.com

Re: [Sqlgrey-users] Using multiple Connect tables

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

On Thu, Mar 29, 2007 at 03:47:40PM -0400, Dave Strickler wrote:
>  
> However, I have a larger question that asks, "what do we do when the Connect table reaches 10 million, or 20 million?" How large can it grow before I'm in trouble? Since I don't know it's upper limits, I fear I may run into them randomly one day.
>  
Dave,

Like Lionel stated, you need to avoid VACUUM FULL and use just a regular
VACUUM instead. Autovacuum will run it for you and ANALYZE your tables
when needed. As I previously mentioned, you should reduce the size of
the autovacuum thresholds so that it will work when a smaller fraction
of the table has changed. The size at which performance will start to
drop is when the working set of data that is being changed is larger
than your system cache footprint. The lower bound is going to be the
size of the index for the connection table, since that is what is used
to speed access to the data in the table. If you calculate the average
size of each field that comprises the index and add them together. That
will give you the size of a typical index entry. Then divide that number
into the system memory size to generate an absolute lower bound on the
size. 

Ken

Re: [Sqlgrey-users] Using multiple Connect tables

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

Hi Dave,

On Thu, 29 Mar 2007, Dave Strickler wrote:

>  The problem we are having is a very old one. In about 24 hours, our
> Connect table will grow to 6 million records. We can purge it down, but
> that takes time, and what really hurts us is doing a VACUUM FULL which
> would write/read lock the database for long periods of time, therefore
> pulling SQLGrey "offline". We are not using pg_autovacuum, and perhaps
> this would be the solution for now.
>

In addition to get a smooth cleaning and restructuring, you could try to
reduce the amount of entries in the connect table. Do you use throttling?
We use it and in our case instead of having more than 2 million entries in
connect we only have 800.000 (statistic from today). If you are not using
throttling, you could analyze your connect table to find out how many
entries could be avoided.

Another thing would be to use more aggressive tests before a request is
made to Sqlgrey.

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