Troubleshooting High Memory Usage with MySQL on Windows

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:

“High Memory Usage with MySQL 5.6.12 GA in ‘Development Machine’ mode”

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”):

http://dev.mysql.com/doc/refman/5.6/en/show-engine.html
http://dev.mysql.com/doc/refman/5.6/en/performance-schema-status-monitoring.html

And for more on the performance schema in general, here is the link to the main section in the manual:

http://dev.mysql.com/doc/refman/5.6/en/performance-schema-configuration.html

And lastly, you can configure many aspects of the performance schema, and there is a nice blog post about that here:

http://marcalff.blogspot.com/2013/04/on-configuring-performance-schema.html

Hope this helps. :)

 
 

Tags: , , , , , , , , , ,

Comments are closed.


Period Panties by Period Panteez Menstrual Underwear Menstruation PMS Panty