mailwatch_milter_relay: very long execution time and high sql load

[dneuhaeuser]

When running 'mailwatch_milter_relay.php' I observed that a run with '--refresh' (e.g. via cron) takes longer and longer, rising while mail.log is growing, from several minutes ultimatively to hours.
In this time there are very high loads on the mysql process.

When looking at the script I ask myself whether the code from line 143 onward really needs to be executed for each and every line in the logfile (each execution of function 'process_entries').

MailWatch/tools/Postfix_relay/mailwatch_milter_relay.php

Line 143 in ca1d81b

// Scan queue for delivery attempts in queue and matching maillog entries

I believe it should probably be sufficient to run the codelines 143-163 only ONCE AFTER all logfile lines are processed.
All necessary informations for this are in the $idqueue array, right?

Hi @dneuhaeuser That would make for a good optimization, I believe. This process has never been super efficient. I am open to ideas.

[dneuhaeuser]

I'm currently working on improving this scripts performance...

On an installation with about ~500 emails in the mail.log (filesize ~2,5 MB)
running mailwatch_milter_relay currently takes 36 minutes here with 100% cpu load on mysqld.

Just bear in mind under normal conditions --refresh should only be run once, preferrably after a logrotate even. I look forward to your improvements.

[dneuhaeuser]

please look at the PR #1266

it is a very simple change but effective:
the execution which took 36+ minutes before now completes in about 10 seconds.

probably there is still room for more optimization...

the cron file ('tools/Postfix_relay/mailwatch-milter-relay') executes the script hourly with --refresh

EDIT: there is actually a suggestion for the 'tail' mode in 'INSTALL.milter'

MailWatch/tools/Postfix_relay/INSTALL.milter

Line 33 in ca1d81b

if ps -C php -o args h | grep mailwatch_milter_relay.php

I wonder why this method is not used in the suggested cron-file

MailWatch/tools/Postfix_relay/mailwatch-milter-relay

Line 12 in ca1d81b

/usr/bin/php -q /usr/local/bin/mailwatch_milter_relay.php --refresh

@dneuhaeuser you are right, we need to fix that discrepancy. I actually use a systemd unit for this. It may make even more sense for us to include both a cron with the tail method and a systemd unit file that also uses the tail method.