I was testing out the latest MySQL 5.6 on Windows (having used the auto-installer) and happened to notice my RAM usage (via Windows Task Manager) was reporting quite a high value, when I had very modest ram/buffer settings (should have been around 40M, but instead it was around 400M).
After double/triple-checking my settings to make sure I didn’t overlook something obvious, I searched the bugs database, and ran across bug #68287:
Turns out, using the auto-installer set the value of table_definition_cache=1400, when the minimum value is 400. Reducing it to 400, and restarting MySQL immediately lowered the RAM usage, and is the “work-around” identified in the bug report.
Having encountered this, I thought I’d double-check the RAM usage of my other instances and noticed my 5.5 and 5.7 instances were behaving in a similar manner. However, I added the explicit table_definition_cache=400, restarted, and the usage was still high. At this point, I mentioned it in the bug report, and Shane Bester replied asking me to check what the following reported for total RAM:
SHOW ENGINE PERFORMANCE_SCHEMA STATUS;
Voila! That reported the instances were using ~400M, and in fact, it was due to the performance schema itself. In these 2 instances, the performance_schema was enabled, and thus using the extra RAM.
As soon as I disabled the performance schema (performance_schema=0), the memory dropped to the level I was expecting. Now don’t get me wrong, I really like the performance schema, and have re-enabled it – I just wanted to know where this RAM was being used. Note the performance schema is enabled by default in 5.6 and 5.7, but not 5.5 (need to add “performance_schema” to the [mysqld] section, and restart, in 5.5).
For those looking for even more information about the SHOW ENGINE PERFORMANCE_SCHEMA STATUS command and the outputs it returns, I would recommend reviewing the following 2 pages (both contain good and relevant info about the variables, such as “performance_schema.memory”):
And for more on the performance schema in general, here is the link to the main section in the manual:
And lastly, you can configure many aspects of the performance schema, and there is a nice blog post about that here:
Hope this helps.
Tags: (pfs_table).memory, (pfs_table_share).row_size, events_waits_current.row_size, events_waits_history.memory, high memory usage mysql windows, high ram usage windows mysql, mysql performance schema, performance schema, performance_schema, performance_schema.memory, threads.memory