Mailcow-dockerized: performance problems with the mysql container

[l1f]

Describe the bug, try to make it reproducible
We are currently examining the use of mailcow on a larger scale.
However, we have noticed big performance problems with the mysql container. Currently we have about 500 domains and about 14800 mailboxes.

So creating another mailbox takes about 40 seconds via the API and the MySQL container has 100% CPU usage.

Is there a way to improve the performance of MySQL in the container? Honestly, I have never worked with MySQL in a Docker container before.
What I noticed was that perf outputs the following after the mailboxes were created via the API:

  76,88%  swapper          [kernel.kallsyms]                        [k] native_safe_halt
   3,95%  mysqld           mysqld                                   [.] Item_time_literal::clone_item
   3,59%  mysqld           mysqld                                   [.] Item_datetime_literal::clone_item
   2,55%  mysqld           mysqld                                   [.] Item::safe_charset_converter
   1,10%  mysqld           mysqld                                   [.] DTCollation::aggregate
   0,70%  mysqld           mysqld                                   [.] Item_func_maketime::get_date
   0,51%  mysqld           mysqld                                   [.] Item_ident::Item_ident

How to reproduce
Create a new Mailcow instance and use the API to create about 520 domains and 14800 mailboxes.

System information and quick debugging
General logs:
No relevant logs
Further information (where applicable):

• Your OS (is Apparmor or SELinux active?): Debian, no Apparmor or SELinux
• Your virtualization technology (KVM/QEMU, Xen, VMware, VirtualBox etc.): KVM
• Your server/VM specifications (Memory, CPU Cores): 8 Cores and 50 Gig Ram
• Don't try to run mailcow on a Synology or QNAP NAS, do you? No
• Docker and Docker Compose versions: Docker Version: 18.09.6 docker-compose version 1.24.1, build 4667896b
• Output of git diff origin/master, any other changes to the code?: no changes

[andryyy]

Can you try to remove...

  if ($_action != 'get' && in_array($_type, array('domain', 'alias', 'alias_domain', 'mailbox', 'resource'))) {
    update_sogo_static_view();
  }

...from data/web/inc/functions.mailbox.inc.php and try again? You need to re-add it later. Just want to know if that's the problem.

[l1f]

Okay. Wow. It's a lot faster now.

[Braintelligence]

So basically we could try to defer that codeblock, right? 🤔

[andryyy]

No. This should be run on every change. If you don't want to run it on every change, you can trigger it every hour or something like that.

I don't plan to add something like that. Feel free to create a PR to optionally run this hourly or whenever.

[Braintelligence]

By deferring I meant triggering not instantly but rather like 5 minutes later.
Something like this:

• Mailbox is created -> start 5 minute timer
• If a mailbox is created within that timeframe, no new update sogo task is created

Or maybe this could be circumvented by a bulk-creation of mailboxes?

[andryyy]

I know. Create a PR to make it optionally.

[Braintelligence]

@l1f There you have it 😺. Feel free to provide a new env variable (maybe something like MAILCOW_HEAVY_USERBASE with default False?) whose first purpose would be to defer the codeblock in question somehow. The laziest solution would be to comment out that codeblock and make it run manually via a button in the Web GUI instead IMHO.

Maybe we'll encounter other possible settings that are only convenient and necessary for heavy userbase-installations.

[andryyy]

I don't know why it would take so long though. We should check the query to update the table and change the database layout (perhaps it's a missing index?).

[andryyy]

One of the biggest mistakes was putting aliases like "asd,dsa,miau" in the alias table. We need to run REGEX in some queries, what probably kills the performance. Just like this in sogo_view:

LEFT OUTER JOIN grouped_mail_aliases ga ON ga.username REGEXP CONCAT('(^|,)', mailbox.username, '($|,)')

[andryyy]

A second table like "goto" with a reference to the "alias" ID would probably do it. We need to change a lot of code though. :(

[andryyy]

Is anybody with a slow installation able to run this for me? =>

docker-compose exec sogo-mailcow /bin/bash
mysql --socket=/var/run/mysqld/mysqld.sock -u ${DBUSER} -p${DBPASS} ${DBNAME} << EOF
SELECT mailbox.username, mailbox.domain, mailbox.username, if(json_extract(attributes, '$.force_pw_update') LIKE '%0%', if(json_extract(attributes, '$.sogo_access') LIKE '%1%', password, 'invalid'), 'invalid'), mailbox.name, mailbox.username, IFNULL(GROUP_CONCAT(ga.aliases SEPARATOR ' '), ''), IFNULL(gda.ad_alias, ''), 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
WHERE mailbox.active = '1'
GROUP BY mailbox.username
EOF

Is this query slow?

[l1f]

I can run my test stack up again tomorrow and do it for you.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.