MySQL 8.0 Memory Consumption on Small Devices

on

Recently, PeterZ pointed a huge difference in memory usage of MySQL 8.0 compare to MySQL 5.7. This can be an issue for small instances if the same configuration for buffers like the buffer pool are not changed.

As explained in Peter’s article, this can lead to the awakening of the so feared OOM Killer !

MorganT, pointed accurately in his comment what is the source of such difference and how this was then caused by the new instrumentation added in MySQL 8.0.

Nothing is free, even as a beer. There is always a cost for more features.

This is a small non exhaustive list relating some additions in Performance_Schema:

However, if you plan to use MySQL 8.0 on small devices and still benefit of Performance_Schema, you can reduce its memory consumption.

Here is an example of the memory consumption of a fresh installed MySQL 8.0.17 with Peter’s config (adapted for native MySQL 8.0):

+---------------------------+---------------+
| code_area                 | current_alloc |
+---------------------------+---------------+
| memory/innodb             | 319.66 MiB    |
| memory/performance_schema | 268.40 MiB    | <--
| memory/mysys              | 8.58 MiB      |
| memory/sql                | 3.59 MiB      |
| memory/temptable          | 1.00 MiB      |
| memory/mysqld_openssl     | 134.50 KiB    |
| memory/mysqlx             | 3.44 KiB      |
| memory/vio                | 912 bytes     |
| memory/myisam             | 696 bytes     |
| memory/csv                | 88 bytes      |
| memory/blackhole          | 88 bytes      |
+---------------------------+---------------+

The total memory consumption is the following:

MySQL 8.0> select * from sys.memory_global_total;
+-----------------+
| total_allocated |
+-----------------+
| 615.54 MiB      |
+-----------------+

Now let’s adapt the Performance_Schema configuration to reduce it’s memory consumption and see the result:

+---------------------------+---------------+
| code_area                 | current_alloc |
+---------------------------+---------------+
| memory/innodb             | 319.66 MiB    |
| memory/performance_schema | 89.88 MiB     | <--
| memory/mysys              | 8.58 MiB      |
| memory/sql                | 3.59 MiB      |
| memory/temptable          | 1.00 MiB      |
| memory/mysqld_openssl     | 134.50 KiB    |
| memory/mysqlx             | 3.44 KiB      |
| memory/vio                | 912 bytes     |
| memory/myisam             | 696 bytes     |
| memory/csv                | 88 bytes      |
| memory/blackhole          | 88 bytes      |
+---------------------------+---------------+

We can see the total memory used:

MySQL 8.0> select * from sys.memory_global_total;
+-----------------+
| total_allocated |
+-----------------+
| 437.39 MiB      |
+-----------------+

These are the changes performed:

MySQL 8.0> SELECT t1.VARIABLE_NAME, VARIABLE_VALUE 
           FROM performance_schema.variables_info t1 
           JOIN performance_schema.global_variables t2 
             ON t2.VARIABLE_NAME=t1.VARIABLE_NAME 
          WHERE t1.VARIABLE_SOURCE = 'PERSISTED'; 
+----------------------------------------------------------+----------------+
| VARIABLE_NAME                                            | VARIABLE_VALUE |
+----------------------------------------------------------+----------------+
| performance_schema_digests_size                          | 1000           |
| performance_schema_error_size                            | 1              |
| performance_schema_events_stages_history_long_size       | 1000           |
| performance_schema_events_statements_history_long_size   | 1000           |
| performance_schema_events_transactions_history_long_size | 1000           |
| performance_schema_events_waits_history_long_size        | 1000           |
| performance_schema_max_cond_classes                      | 80             |
| performance_schema_max_digest_length                     | 512            |
| performance_schema_max_mutex_classes                     | 210            |
| performance_schema_max_rwlock_classes                    | 50             |
| performance_schema_max_sql_text_length                   | 512            |
| performance_schema_max_stage_classes                     | 150            |
| performance_schema_max_thread_classes                    | 50             |
+----------------------------------------------------------+----------------+

So, indeed it’s the default instrumentation settings may not fit small instances but it’s not very complicated to modify them. This is how I modified them using the new SET PERSIST statement:

set persist_only performance_schema_events_waits_history_long_size=1000;

If you are interested in this new statement, please check these articles:

One thought on “MySQL 8.0 Memory Consumption on Small Devices

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

recent
categories