Hi! I'm fighting against performance problems with cleanup on our setup (2 Postfix machines, 3.5M rejects/day, 300k accepts/day each machine, MySQL server 4 GB RAM, 6M entries in triplet table).
The old database server with less RAM and a small MySQL configuration (small key_buffer_size etc.) went very slow (15 minutes cleanup every hour), so I set up new MySQL server with 4 GB RAM and 2 GB key_buffer_size. At the beginning cleanup took 2-5 seconds every hour and I was very happy, that policyd on the mail servers rejected nearly all spam. After only 3 days now the database extended from 3M to 6M in the triplet table while cleanup takes 3-5 minutes now, while policyd handles all mails as pass through and accepts very much spam :-( So I decided to have a look into the cleanup code. I see that it uses "DELETE QUICK" everywhere, which should do the deletion without rebalancing the index trees (see http://dev.mysql.com/doc/refman/5.0/en/delete.html). As far as I understand this documentation, this may cause trouble with the _datelast and _datenew indexes, because these contain data, which isn't equally distributed over time but contains data that continuously increases. As far as I understand the MySQL documentation, this will result in degenerated index trees. Is it possible that this is the center of my performance issues? The MySQL manual suggests to do "OPTIMIZE TABLE" do fix the degenerated index structures, but with my database I expect this to run for some minutes, while the database is unusable, which I try to avoid. Any other idea? I thought about replacing the cleanup process by a little perl script that does simple DELETE (without QUICK) calls with a much smaller limit (maybe LIMIT 500 but running every 10 seconds?). But for this I need an optimal index, while I also found "DELETE QUICK" calls in greylist.c and helo.c, which could degenerate my indexes, too... Okay, I can also change them, but does it really solve my problems? I know, that Cami discourages from using "OPTIMIZE TABLE", because the free space is reused by MySQL later, but how else can I fixup the degenerated index structures of the _datelast/_datenew indexes? And why does cleanup takes now 60 times as much time than 3 days ago with an optimized half size database? Tschoeeee Roland ------------------------------------------------------------------------- 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
