Hi everyone,
I’m experiencing severe performance degradation on a Mailcow production environment with around 7,000 users. After importing alias entries via script, I’ve noticed the following problems:
Symptoms:
- Dovecot and MySQL containers consistently reach 100–170% CPU during operations such as password changes, alias creation, or login events.
- Even simple password updates take up to 2 minutes to complete.
- The
mailcow-mysql-mailcow-1 container stays at 100% CPU with relatively low memory and I/O usage.
- The
doveadm who output is minimal, yet the Dovecot container still consumes a lot of CPU.
Setup and Optimizations Already Applied:
innodb_buffer_pool_size = 32G- Slow query log enabled.
- Indices reviewed on the
alias table.
OPTIMIZE TABLE alias run.- Confirmed 14,000 rows in the
alias table (double the user base due to technical alias + display name).
- Views such as
grouped_mail_aliases use GROUP_CONCAT and REGEXP joins.
—
Main Bottleneck Identified:
After enabling the slow query log, I captured a major bottleneck during the insert/update into _sogo_static_view, which appears to be the most expensive operation. It runs repeatedly and takes 167 seconds per execution, even when affecting only 2 rows.
Log excerpt from /var/lib/mysql/<container>-slow.log:
Time: 250628 19:37:16
User@Host: mail[mail] @ localhost []
Thread_id: 43 Schema: maildb QC_hit: No
Query_time: 167.447493 Lock_time: 0.000065 Rows_sent: 0 Rows_examined: 58925
Rows_affected: 2 Bytes_sent: 0
use maildb;
SET timestamp=1751150236;
INSERT INTO _sogo_static_view (c_uid, domain, c_name, c_password, c_cn, mail, aliases, ad_aliases, ext_acl, kind, multiple_bookings)
SELECT
mailbox.username,
mailbox.domain,
mailbox.username,
‘{SSHA256}47DEQpj8HBSa+/TImW+5JCeuQeRkm5NMpJWZG3hSuFU=’,
mailbox.name,
mailbox.username,
IFNULL(GROUP_CONCAT(ga.aliases ORDER BY ga.aliases SEPARATOR ‘ ’), ''),
IFNULL(gda.ad_alias, ''),
IFNULL(external_acl.send_as_acl, ''),
mailbox.kind,
mailbox.multiple_bookings
FROM
mailbox
LEFT OUTER JOIN grouped_mail_aliases ga ON ga.username REGEXP CONCAT(‘(^|,)’, mailbox.username, ‘($|,)’)
LEFT OUTER JOIN grouped_domain_alias_address gda ON gda.username = mailbox.username
LEFT OUTER JOIN grouped_sender_acl_external external_acl ON external_acl.username = mailbox.username
WHERE
mailbox.active = ‘1’
GROUP BY mailbox.username
ON DUPLICATE KEY UPDATE
domain = VALUES(domain),
c_name = VALUES(c_name),
c_password = VALUES(c_password),
c_cn = VALUES(c_cn),
mail = VALUES(mail),
aliases = VALUES(aliases),
ad_aliases = VALUES(ad_aliases),
ext_acl = VALUES(ext_acl),
kind = VALUES(kind),
multiple_bookings = VALUES(multiple_bookings);
—
Questions:
- Is it expected for
_sogo_static_view to trigger this level of load per update?
- Would caching this data instead of recalculating it help?
- Has anyone successfully optimized this view or replaced the
REGEXP with more efficient logic?
Any insights or tuning recommendations would be greatly appreciated. I’m happy to provide additional logs or configuration if needed.
Thanks in advance!