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 ?