Roland Rosenfeld wrote: > > 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 :-(
Policyd database entries can go into the millions, at least 10-20M. > 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? Not possible. _date(last|new) are not auto-incrementing indexes. > 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. Indeed, it can take quite some time. This should be done ~once a month, preferably at around 2am when things are quiet. > 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? What makes you believe the indexes are degraded? Cami ------------------------------------------------------------------------- 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
