Re: [Sqlgrey-users] SQL Query optimization

[Michel B. <mi...@bo...>]

Le Lundi 21 F=E9vrier 2005 13:33, Lionel Bouton a =E9crit :
> >
> >Yes. But if we calculate the now() outside of the SELECT clause, we ca=
n as
> >well perform the interval calculation at the same time, which will mak=
e
> > the SQL query simpler.
>
> Wouldn't it be better to let the SQL database handle the timestamp
> representation itself ? If we do it in SQLgrey we will have to support
> every syntax we can get from the SELECT now() output. There are already
> special cases because some databases aren't really standard compliant
> (or the standard isn't clear enough...). I really don't want to make
> things more complex.

That makes sense. So you would issue a "select now();" once, put the resu=
lt=20
into a (let's call it $perlnow) Perl variable, and then reinject it in a=20
"select * from from_awl where last_seen < '$perlnow' - INTERVAL '30 DAY'"=
=20
without having to pay attention to the contents or syntax inside...

Gotten from SQL, fed back into SQL, and let SQL understand what it gave a=
nd=20
perform the calculation... That's smart.

On the other hand, we don't have support for such a big number of SQL DBs=
=20
(MySQL, PostgreSQL and SQLite), so we have only 3 possible different=20
syntaxes... That's why doing everything in Perl isn't that difficult (exc=
ept=20
for the possible synchronization issue you rised, if several SQLgrey serv=
ers=20
share the same DB. But ntp is an easy solution, and is probably already i=
n=20
place at every big site using MTA pools).

> >What is the interest of having several SQLgrey servers access the same
> >database ? Wouldn't it be more logical to rather have several MTAs acc=
ess
> > the same SQLgrey server ?
>
> Fault-tolerance. I want to support people who maintain big architecture=
s
> with several MX with high availability needs. In this case, each MX
> should have its own local SQLgrey process in order to protect themselve=
s
> from a single system crash.

As long as you share a single SQL DB, you have a single point of failure.=
=20
Running the SQLgrey server and SQL DB on the same machine, and sharing th=
e=20
SQLgrey server between MTAs, or running a SQLgrey server on each MTA serv=
er,=20
and sharing the same SQL DB, you're down anyway if the DB server crashes.=
..

> >First the network dialog between MTA <=3D> SQLgrey server will be much
> > lighter than the network dialog SQLgrey server <=3D> SQL DB.
>
> What makes you believe this ? From my point of view they should be more
> or less the same. There are several information pieces SQLgrey reveives
> from the MTA which are simply throwed away (helo_name, protocol_name,
> instance, protocol_state, queue_id, request, size).

Basically, the dialog between the MTA and SQLgrey server can be described=
 as=20
"One question - One yes/no answer". Even though the question does include=
=20
some elements we don't need, it doesn't make it that big.

OTOH, as you state, dialog between the SQLgrey server and SQL DB is a ser=
ies=20
of queries - answers, in the best case one, but in most cases several (ev=
en=20
if not always 8 ;-), not event talking about periodical DB cleanup.

> >Second, if we have several SQLgrey servers using the same DB, then we =
will
> >have several SQLgrey servers that will in turn attempt to perform DB
> > cleanup which is a waste of ressource...
> >
> >(If we can get good performance from the cleanup SQL resquests by allo=
wing
> >those requests to properly uses indexes, then cleanup will be real fas=
t,
> > and it eliminates the need to put the cleanup process in a separate
> > program, which is currently mentioned in the TODO for 1.5.x...)
>
> This can be addressed. I've not put much thought into this yet, but we
> can store in DB the last cleanup time and the delay itself so as to
> synchronize SQLgrey servers in a way where only one will trigger the
> cleanup at a time.

Yes. But it would cause one more SQL request ;-)

Another possibility would be to add a perform/don't perform DB cleanup as=
 a=20
config parameter, and if several servers share the same DB, then only one=
 of=20
them should be configured to perform its cleanup.

Cheers.

--=20
Michel Bouissou <mi...@bo...> OpenPGP ID 0xDDE8AC6E