Database performance optimization is a significant part of the ongoing service we provide for our clients. We recently found that a client had a query they performed regularly that was drastically slowing the system as a whole, and we investigated to see if we could help them resolve the issue.
The symptoms were confusing: though the server configuration was sized appropriately for the available memory, the server was per-allocating approximately 20GB of RAM immediately on a query, even when the query required nowhere near that allocation, leading to excessive swapping and performance hits.
We explored various options: Were there SET SESSION statements being executed improperly before the query? Were buffer_size parameters set correctly? Were there other variables we hadn’t considered?
As we investigated, we discovered something interesting: The MySQL Server (mysqld) sets a default memory allocation for MyISAM compressed tables, and that default setting is unnecessarily large (18446744073709547520). As a result, it maps into memory all of the MyISAM compressed tables. In our case, with 20GB tables, mysqld was suddenly allocating an unreasonable amount of memory to the process.
This default memory allocation variable was introduced in MySQL 5.1.43, and you can read more about it here.
We immediately tuned this down to a more manageable size for the actual queries being run, and are seeing the expected performance improvements in our client’s queries.