Group replication message cache size not configurable

[Mert Kirpici]

The system variable group_replication_message_cache_size not being configurable sometimes leads to OOM killer to be triggered on the mysqld process on nodes with limited system memory. It currently defaults to 1G, however MySQL 8 documentation[1] specifies that for certain MySQL versions the value can have a much lower value.

Syslog showing recurring OOM kills for mysqld every ~ 12 hours

ubuntu@juju-bae646-mertkirpici-10:~$ journalctl -k -p 0..3
-- Logs begin at Mon 2022-05-30 14:23:41 UTC, end at Fri 2022-06-03 10:26:22 UTC. --
May 31 13:51:27 juju-bae646-mertkirpici-10 kernel: Out of memory: Killed process 66704 (mysqld) total-vm:2976504kB, anon-rss:1701244kB, file-rss:0kB, shmem-rss:0kB, UID:113 pgtables:4>
Jun 01 13:02:50 juju-bae646-mertkirpici-10 kernel: Out of memory: Killed process 293375 (mysqld) total-vm:2901672kB, anon-rss:1705792kB, file-rss:0kB, shmem-rss:0kB, UID:113 pgtables:>
Jun 02 12:46:40 juju-bae646-mertkirpici-10 kernel: Out of memory: Killed process 533598 (mysqld) total-vm:2849900kB, anon-rss:1724524kB, file-rss:0kB, shmem-rss:0kB, UID:113 pgtables:>

Here is the result of a query taken from MySQL docs[2] that shows the aggragated memory usage for different subsystems, from a node with 2G system memory:

mysql> SELECT SUBSTRING_INDEX(event_name,'/',2) AS
    -> code_area, FORMAT_BYTES(SUM(current_alloc))
    -> AS current_alloc
    -> FROM sys.x$memory_global_by_current_bytes
    -> GROUP BY SUBSTRING_INDEX(event_name,'/',2)
    -> ORDER BY SUM(current_alloc) DESC;
+---------------------------+---------------+
| code_area | current_alloc |
+---------------------------+---------------+
| memory/innodb | 644.31 MiB |
| memory/group_rpl | 454.70 MiB |
| memory/performance_schema | 246.47 MiB |
| memory/sql | 55.81 MiB |
| memory/mysys | 33.96 MiB |
| memory/temptable | 12.00 MiB |
| memory/mysqld_openssl | 4.14 MiB |
| memory/mysqlx | 3.25 KiB |
| memory/myisam | 728 bytes |
| memory/csv | 120 bytes |
| memory/blackhole | 120 bytes |
| memory/vio | 80 bytes |
+---------------------------+---------------+

Current value:
mysql> select @@group_replication_message_cache_size/1024/1024;
+--------------------------------------------------+
| @@group_replication_message_cache_size/1024/1024 |
+--------------------------------------------------+
| 1024.00000000 |
+--------------------------------------------------+

Current implementation defaults a similar variable, innodb_buffer_pool_size, to a sane value if not set explicitly (min(%50 of system memory, 512MB)), but there is no mechanism for the group replication message cache.

I suggest that also needs to be exposed through the charm configuration.

---
[1] https://dev.mysql.com/doc/refman/8.0/en/group-replication-options.html#sysvar_group_replication_message_cache_size
[2] https://dev.mysql.com/doc/refman/8.0/en/memory-use.html

[Liam Young (gnuoy)]

As long as we don't break anyone on charm upgrade I think this make sense. I propose that the new charm config is unset by default. The charm can then check what it is set to and if it's unset continue with the current behaviour of defaulting to 1Gb.

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.opendev.org/c/openstack/charm-mysql-innodb-cluster/+/845893

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/c/openstack/charm-mysql-innodb-cluster/+/845893
Committed: https://opendev.org/openstack/charm-mysql-innodb-cluster/commit/a6552a0bed893a0aac30fb2970b1114dcc054096
Submitter: "Zuul (22348)"
Branch: master

commit a6552a0bed893a0aac30fb2970b1114dcc054096
Author: Mert Kırpıcı <email address hidden>
Date: Wed Jun 8 11:54:30 2022 +0400

    Add configuration option group-replication-message-cache-size

    The default value for this variable in MySQL is 1G. However according to
    the documentation[1], lower values are allowed for certain patch versions.
    This might come in handy for setups with strict memory availability.

    [1] https://dev.mysql.com/doc/refman/8.0/en/group-replication-options.html

    Closes-bug: #1977500

    Change-Id: I1daa85a97a391d1efb537f5791c76e84a3f92f4c
    Signed-off-by: Mert Kırpıcı <email address hidden>