Performance Zone is brought to you in partnership with:

I specialise MySQL Server performance as well as in performance of application stacks using MySQL, especially LAMP. Web sites handling millions of visitors a day dealing with terabytes of data and hundreds of servers is king of applications I love the most. Peter is a DZone MVB and is not an employee of DZone and has posted 263 posts at DZone. You can read more from them at their website. View Full User Profile

MySQL Wish for 2013 – Better Memory Accounting

01.03.2013
| 1477 views |
  • submit to reddit

With Performance Schema improvements in MySQL 5.6 I think we’re in the good shape with insight on what is causing performance bottlenecks as well as where CPU resources are spent. (Performance Schema does not accounts CPU usage directly but it is something which can be relatively easily derived from wait and stage information). Where we’re still walking blind with MySQL is resource usage – specifically Memory Usage.

I can’t count how many time I had to scratch my head with system configured to consume only few GBs in global buffers growing to consume much more for some unknown needs leaving me puzzled whenever it is user variables, complex stored procedures temporary tables or something else. Not only such connection related allocations are invisible but many global allocations are poorly visible too. Sure, we know how much memory was allocated for Innodb Buffer Pool or Query Cache but amount of memory used for example for Table Cache can be only guessed.

Not only lack of proper memory accounting is operational problem, it is problem for QA too as it makes it very hard to ensure the memory consumption by various database operations is reasonable. For example if some INFORMATION_SCHEMA query were to take couple of GB of memory we might not find it reasonable yet it is not a “memory leak” as it is freed after operation is completed.

What I would like to see is similar to Performance Schema “wait names” we could get something similar in terms of memory allocation target, which could be something like “Prepared Statement Cache”, “Table Cache” etc. For global pools we need just global accounting, for connection basics accounting should be done per connection but aggregated globally. This way it would be possible to see for example which connection is using most memory. We could also aggregate per purpose to understand what memory is currently used for.

One thing to note with memory allocation is it is rather volatile – a lot of memory might be allocated for very short period of time and hence not very well visible. To assist catching these situations it is good idea to track not only current amount allocated but also maximum, which can be reset as needed.

If such functionality is implemented not only it can help DBA to configure memory allocation a lot more successful but I also believe similar to Performance Schema helping to find many performance bottleneck such profiling will help to find a lot of wasteful memory allocation and help to get MySQL on the memory diet. It would be easy to extend QA suite to check for peak memory usage after each test and hence find memory usage regression – when new code unexpectedly requires a lot more memory than previous variant.

If memory accounting is implemented it can be used to help restricting memory usage in addition to monitoring it which I’m sure hosting providers, MySQL as a Service vendors will especially appreciate as currently they are on complete mercy of their users not being able to limit memory usage by individual MySQL users or even track how much these users are using.

Now as MySQL 5.6 is getting close to be done I imagine the planning and feature design is long started for MySQL 5.7 (or whatever next release is going to be called) and I really hope something along those lines will be picked up for inclusion. If not perhaps this is opportunity for MariaDB to pave the road ? Whatever way I’m sure MySQL community will appreciate it.

Happy New 2013 Year everyone !

Published at DZone with permission of Peter Zaitsev, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)