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 >
Try selecting/deleting a triplet from MySQL and see how long does it take .. I used to have 6 postfix instances with ~450,000 SMTP connection on each server / day. Database is an old machine : 2GB - 1.6Ghz Xeon - RAID5 SCSI drive Cleanup process took around 2 second and run every minutes. My secret is to make triplet table as small as possible, I have average triplet table around ~300,000 entry (50 day tripplet time out) suggestion : - Make sure that policyd is called after recipient verification, blacklist, and other check. - separate incoming mail and outgoing mail environtment. - Use Whitelist ! I have hundreds of IPs in my greylist whitelist table. - Try using following DNS_Whitelist. It will help you cut down the number of triplet in your DB and allow ~80% of email coming from proper mail server. ----------------------------+-------------------------------------------------------------+---------+ | _whitelist | _description | _expire | +----------------------------+-------------------------------------------------------------+---------+ | bigfish.com | # bigfish.com has smtp servers behind multiple ips | 0 | | %mail% | mail server | 0 | | %smtp% | mail server | 0 | | %.server-web.com | webcentral web servers | 0 | | %.iserver.net | Verio Webhosting | 0 | | %.messagelabs.net | MessageLabs | 0 | | %.ev1servers.net | # ev1 hosting company | 0 | | %hosting% | # big possibility of running proper mail server | 0 | | %mx%.% | # big possibility of running proper mail server | 0 | | %server% | # big possibility of running proper mail server | 0 | | %post% | # big possibility of running proper mail server | 0 | | %exchange% | # big possibility of running proper mail server | 0 | | %return% | # big possibility of running proper mail server | 0 | | ns1% | # big possibility of running proper mail server | 0 | | ns2% | # big possibility of running proper mail server | 0 | | %google.com | # Google | 0 | | %yahoo.com% | # Yahoo | 0 | | %hotmail.com% | # Hotmail | 0 | | %mta% | # likely to e a proper mail server | 0 | | %pobox.com | # pobox | 0 | | %smarthost% | # big possibility of running proper mail server | 0 | | %relay% | # big possibility of running proper mail server | 0 | | %proxy% | # big possibility of running proper mail server | 0 | | %list% | big possibility of mailing list server | 0 | | %bounce% | big possibility of mailing list server | 0 | | %.shared.server-system.net | # ATO outgoing mail server | 0 | | %www% | # big possibility of running proper mail server | 0 | | ns3% | # big possibility of running proper mail server | 0 | | %.lnk.telstra.net | # Telstra business IP address | 0 | | %gw1% | # big possibility of running proper mail server | 0 | | %gw2% | # big possibility of running proper mail server | 0 | | %gw-% | # big possibility of running proper mail server | 0 | | %outbound% | # big possibility of running proper mail server | 0 | | %filter% | # big possibility of running proper mail server | 0 | Hope this help. Regards, Rianto Wahyudi ------------------------------------------------------------------------- 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
