Topic: long runnig mysql queries on amavisd db

==== Required information ====
- iRedMail version:  iRedMail-0.8.5
- Store mail accounts in MySQL backend
- Linux/BSD distribution name and version: RHEL 6.4
- Related log if you're reporting an issue:

When  user log in to iRedAdmin-pro panel, then some mysql queries are executed and after these queries  are finished, then user is realy loged in and can show admin panel.
When amavisd dba tables have few records, this works fine and login delay is small and  does not interfere with work, but when amavisd db have many records, then is inpossible to log in to admin panel.
currently amavisd db 'msgrcpt' and 'msgs' tables have 2.5 millions records (0.5GB and 1.6GB data) each.

first mysql qery:
SELECT COUNT(msgs.mail_id) AS total                 FROM msgs                                  LEFT JOIN msgrcpt ON (msgs.mail_id = msgrcpt.mail_id)                 LEFT JOIN maddr AS sender ON (msgs.sid = sender.id)                 LEFT JOIN maddr AS recip ON (msgrcpt.rid = recip.id)                 WHERE                     msgs.content IS NOT NULL                     AND msgs.content NOT IN ('S', 's', 'Y', 'V')                      AND recip.domain IN ('lv.gov.iem.test3', 'lv.gov.iem', 'lv.gov.iem.ic', 'lv.gov.iem.ttt', 'lv.gov.iem.ttt2') AND msgs.time_num > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 86400 SECOND))                 ORDER BY msgs.time_num DESC;
finishes in some minutes.

but second mysql query is really long running:
DELETE FROM msgrcpt WHERE mail_id IN (SELECT mail_id FROM msgs WHERE content IN ('U', 'M', 'H', 'O', 'C')                         AND time_num < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY))                 );
now runs 102794 seconds and continue working.

in genereally, server not overloaded, mysql configuration are default, but some parameters are increased according to mysql-tuner recomendations. mysql use for this qery one cpu core for 99-100%.

Are any optimisations options avalable?
How we can accelerate these queries, at least this second querie ?


Re: long runnig mysql queries on amavisd db

This script was improved in the latest iRedAdmin-Pro stable release, did you try the latest iRedAdmin-Pro-MySQL-1.7.0?


Does my reply help a little? How about buying me a cup of coffee ($5) as an encouragement?

buy me a cup of coffee


Re: long runnig mysql queries on amavisd db

Really,  after upgrade to 1.7.0 seems this problem is solved, thanks.