Hello, Roland: You didn't mention what your retention times were for the triplets, etc. You might try cutting the expiration limits down to be no more than about twice the limit value (so, say, 4 hours and 2 hours). We get over 1 M messages a day and even though my DBs grow to as much as 50 MB in size with close to 200k entries for some tables (like throttle_from_instance), the cleanup operation is very fast (I run it hourly). How many rows do you have in your biggest tables?
You also didn't mention what version of mySQL DB you were running. Is this for polixyd 1.8x or 2.x ? If it's for 2.x, sorry, all I've written above probably won't help address your issues. --Tobias Roland Rosenfeld wrote: > 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 ------------------------------------------------------------------------- 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
