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:
- Digest Query Samples in Performance_Schema
- meta-data added to Performance_Schema’s Instruments
- Memory Consumption and Open Prepare Statements
- MySQL and Memory: a love story (part 2)
- Statements Latency Histograms
- Data Locking Visibility
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:
- MySQL 8.0: changing configuration easily and cloud friendly !
- What configuration settings did I change on my MySQL Server ?
- and Marco’s recent article: SET PERSIST in MySQL: A Small Thing for Setting System Variable Values
Thanks…good article.