Hi there, we are currently again running into trouble due to cleanup.
My colleague just tried to check why during cleanup time, all policyd requests to the SQL database result in "module=bypass". He found some MySQL document stating that with MyISAM tables, the complete table gets locked during database operations, whereas InnoDB type of table would only lock the particular table row (but be somewhat slower in use). Could there be some problem, with regards to DB or table locking and policyd accessing the tables at same time? It seems regardless of how much power (RAM, CPU) we give to the MySQL server, the cleanup (once each hour at ca. 8Mio entries in triplet table) takes about between 3 and up to 10 minutes which is *really* too long as we are hit by a huge load of spam traffic during unavailability/bypass of the greylist check. We also do OPTIMIZE TABLE already on a per-night basis. Autowhitelist is not an option for us - as we see it, this would not really make the triplet table smaller (still would have 6mio entries in it). Freundliche Grüsse / Kind regards, Thomas Mendorf -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Roland Rosenfeld Sent: Tuesday, March 18, 2008 1:12 PM To: policyd-users@lists.sourceforge.net Subject: Re: [policyd-users] cleanup performance optimization On Tue, 18 Mar 2008, Cami Sardinha wrote: > Policyd was test/written for MySQL 4.x. This doesn't mean it shouldn't > behave the same for v5. Unless i'm mistake (or things have changed > between versions), using "DELETE QUICK" on an auto-incrementing row is > where holes (/fragmentation) occurs. This was one of the reasons that > Policyd has no auto-incrementing fields. Is this no longer the case > with MySQL v5? As I understand this, the timestamp columns cause the same problem that auto-increment columns imply: You have values that drift only one way (from small to bigger), while old values will never reoccur. As far as I understand index structures, that are usually implemented using some tree structures (don't know how MyISAM implements this exactly), such a tree will degenerate, which means that the left side of the tree becomes empty while it grows only to the right (which means fragmentation and the index performance goes down from some log() value to something worse because the degenerated, unbalanced tree reaches a much bigger depth). > > Also the syslog output is broken, because the timestamps are created > > by syslogd, which writes to disk nearly a minute after the event > > happened (had to patch cleanup, now it writes its own timestamps to > > the log instead of syslog). > > You shouldn't have to patch cleanup. What is the output of: > # cat /etc/syslog.conf | grep mail But the problem is, that syslog uses the timestamp when it writes the line, not when it receives it. I experienced delay of nearly a minute here :-( (some I/O trouble I fear). Tschoeeee Roland -- Roland Rosenfeld - Application & Services - Technik NetCologne Gesellschaft für Telekommunikation mbH - HRB 25580, AG Köln Am Coloneum 9 50829 Köln Tel.: +49-221-2222-373 Fax: +49-221-2222-7373 Geschäftsführer: Werner Hanf, Karl-Heinz Zankel, Dipl. Ing. ------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ policyd-users mailing list policyd-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/policyd-users ------------------------------------------------------------------------- Check out the new SourceForge.net Marketplace. It's the best place to buy or sell services for just about anything Open Source. http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace _______________________________________________ policyd-users mailing list policyd-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/policyd-users
