From: Lionel B. <lio...@bo...> - 2009-08-18 13:58:39
|
Dan Faerch a écrit, le 08/18/2009 11:36 AM : > Lionel Bouton wrote: > >>> when running multiple servers against one database server. I needed that >>> only 1 specific server would do the cleanup, >>> >>> >> Why again (I don't remember the reason) ? Reading the following it seems >> this feature brings some problems. >> >> > I need it for 2 reasons. > First, it compensates for not using async by having an sqlgrey > cleanup-only process on the db-server (and having a localprocess trigger > cleanup by faking a connection to sqlgrey). > I believe there are other solutions. See my next mail. > Secondly, it keeps the cleanup-log entries ("spam:") on one box, which > makes it simpler to gather statistics, rather than having to either > remotelog Frankly, I'd use remote logging. syslog-ng can do that efficiently (you can match on the "spam:" to group all of them in a single file). > or gather logs from several servers. I use the spam: entries > for per-domain statistics. I know this is very heavy, but "the-man wants > his stats" ;) > > [...] >>> Also i want to add a "cleanup limiter", that is, the maximum number of >>> >>> >> That's risky. If you set it too low, the cleanups won't be able to keep >> with the new entries. >> > But burst of new entries are actually my problem. If a spam-botnet hits > the servers for an hour or so, a lot more entries will need to be > cleaned out. When this happens during primetime, it causes timeouts (the > "server configuration problem" error from postfix). > Yes, but this can be avoided almost entirely with a better cleandelay algorithm (see my next mail). >> [...] >> > I currently use 60 seconds. My idea was to make a LIMIT high enough to > minimize the timeout scenario, and if the limit is hit, cut cleanup time > You mean the db_cleandelay value ? Then we probably will agree. > down to eg. 30 seconds until limit isnt hit any more. This will give > sqlgrey breathing room to continue to handle users. I could keep doing > '"delay" * 0.5' for every time the limit is hit, thus going from 60 > seconds, to 30, to 15, 7, 4, 2 then "if (delay < 2) Remove_LIMIT clause". > You can avoid that by not doing : delay * 0.5 but something like delay * (last_clean_time / max_allowed_clean_time). And protect against unwanted fluctuations of the cleandelay by bounding the value of (last_clean_time / max_allowed_clean_time). Ie : if you have periodic botnet attack interleaved with calm periods, you don't want the delay to increase too fast but you want it to decrease fast. You may have a temporary problem when a more agressive botnet comes (ie : the current cleandelay will be too high on the first run), but SQLgrey would be ready to handle the load on the next cleanup. It's more clean this way : the LIMIT is a hack. Your real problem is the time it takes to cleanup, just concentrate on minimizing this and everything will be fine. > This will give it a chance to catch up nicely, but if it cant it will > force it self into normal cleanup (ie. full cleanup without the limit > statement). > > upon not hitting the limit anymore, reset cleanup to config value. > > What I don't like with the limit is that it's context dependent : on your servers you must clean tens of thousands of entries per pass so you'll have to set the limit in this range but less powerful installations would simply choke on that. What I'm looking for is a solution that works for everybody without forcing them into obscure tunings. Lionel |