From: Lionel B. <lio...@bo...> - 2005-02-21 12:33:15
|
Michel Bouissou wrote the following on 21.02.2005 11:11 : >Le Lundi 21 F=E9vrier 2005 00:34, Lionel Bouton a =E9crit : > =20 > >>If you do an explain on this : >>select * from from_awl where last_seen < timestamp '2005-02-21 >>00:12:46.22726+01' - INTERVAL '30 DAY'; >> >>You'll find that PostgreSQL is doing the right thing. >> =20 >> > >Yes. But if we calculate the now() outside of the SELECT clause, we can = as=20 >well perform the interval calculation at the same time, which will make = the=20 >SQL query simpler. > > =20 > Wouldn't it be better to let the SQL database handle the timestamp=20 representation itself ? If we do it in SQLgrey we will have to support=20 every syntax we can get from the SELECT now() output. There are already=20 special cases because some databases aren't really standard compliant=20 (or the standard isn't clear enough...). I really don't want to make=20 things more complex. >>I considered doing the now() call outside the database earlier (in fact >>when SQLite support was added). I want to avoid this if I can because >>I'd like to allow for several SQLgrey instances accessing the same >>database. >> =20 >> > >What is the interest of having several SQLgrey servers access the same=20 >database ? Wouldn't it be more logical to rather have several MTAs acces= s the=20 >same SQLgrey server ? > > =20 > Fault-tolerance. I want to support people who maintain big architectures=20 with several MX with high availability needs. In this case, each MX=20 should have its own local SQLgrey process in order to protect themselves=20 from a single system crash. >First the network dialog between MTA <=3D> SQLgrey server will be much l= ighter=20 >than the network dialog SQLgrey server <=3D> SQL DB. > > =20 > What makes you believe this ? From my point of view they should be more=20 or less the same. There are several information pieces SQLgrey reveives=20 from the MTA which are simply throwed away (helo_name, protocol_name,=20 instance, protocol_state, queue_id, request, size). A few SQL queries=20 shouldn't be far more heavier... From a quick look, the best case=20 (whitelist) is 0 SQL query, the worst is 8 (accepted reconnection,=20 triggering a move from from_awl to domain_awl), the most typical cases=20 involve 2 queries (domain_awl match), 3 queries (from_awl match) and 5=20 queries (no AWL and reconnect match). There may be systems where the awl have such high successful hits that=20 SQLgrey <=3D> db transfers might be less verbose than MTA <=3D> SQLgrey. >Second, if we have several SQLgrey servers using the same DB, then we wi= ll=20 >have several SQLgrey servers that will in turn attempt to perform DB cle= anup=20 >which is a waste of ressource... > >(If we can get good performance from the cleanup SQL resquests by allowi= ng=20 >those requests to properly uses indexes, then cleanup will be real fast,= and=20 >it eliminates the need to put the cleanup process in a separate program,= =20 >which is currently mentioned in the TODO for 1.5.x...) > > =20 > This can be addressed. I've not put much thought into this yet, but we=20 can store in DB the last cleanup time and the delay itself so as to=20 synchronize SQLgrey servers in a way where only one will trigger the=20 cleanup at a time. Cheers, Lionel. |