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