Re: [Sqlgrey-users] SQL Query optimization

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

Michel Bouissou wrote the following on 21.02.2005 14:01 :

>>>What is the interest of having several SQLgrey servers access the same
>>>database ? Wouldn't it be more logical to rather have several MTAs access
>>>the same SQLgrey server ?
>>>      
>>>
>>Fault-tolerance. I want to support people who maintain big architectures
>>with several MX with high availability needs. In this case, each MX
>>should have its own local SQLgrey process in order to protect themselves
>>from a single system crash.
>>    
>>
>
>As long as you share a single SQL DB, you have a single point of failure. 
>Running the SQLgrey server and SQL DB on the same machine, and sharing the 
>SQLgrey server between MTAs, or running a SQLgrey server on each MTA server, 
>and sharing the same SQL DB, you're down anyway if the DB server crashes...
>  
>

Not at all. If the database goes down, SQLgrey will switch off 
greylisting and warn the postmaster.

>>>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 allowing
>>>those requests to properly uses indexes, then cleanup will be real fast,
>>>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 ;-)
>
>  
>

We'll have a cache of the next_scheduled_cleanup value, and only when it 
will be in the past will we chack the DB. This one we can afford again, 
althoug a bit more complex for the DB than the SELECT now() :-)

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

Makes configuration more tricky (and makes it easy losing the cleanup 
process without being aware of it). I'd like to allow the same 
configuration file being used on each SQLgrey instance and let them sort 
this out.

Lionel.