Author: Robert Agar
MySQL DBAs have a wide range of responsibilities related to keeping their databases available to the end-users and applications that depend on them. They need to ensure the integrity of the information contained in the databases by instituting robust security policies and ensuring that only authorized users can gain access. Verifying that the systems are backed up regularly and can be recovered when needed is another critically important task. Keeping the databases performing at peak efficiency can be added to the list of things that DBAs need to be concerned with every day.
For a database’s users, except for availability, its performance is its most important characteristic. Technical and administrative details like security and backups are irrelevant to the frazzled user waiting endlessly for the database to return the results of their query. Sales can be dramatically inversely affected by a slow-performing web application powered by your company’s MySQL databases. The difference in a page loading in one versus four seconds can lead to a 60% loss in visitors to a website.
At the backend of your applications, database performance can substantially impact their overall speed. Pressure from users and management can make optimizing the performance rise to the top of the database team’s priorities. There are many ways in which you can attempt to make your databases perform more quickly and efficiently. We are going to look at several things you can do to try and satisfy those impatient users.
MySQL Performance Tuning
Some general guidelines should be followed by anyone attempting to tune MySQL databases. Make sure that changes put into the configuration files are in the right section and that they are syntactically correct. Putting them in the wrong place or minor misspellings can stop your database from starting up after your modifications. You should also refrain from making more than one change at a time. It is much easier to determine the results of your updates when they are done in a controlled manner.
Here are some specific tactics to try when optimizing MySQL systems.
Change the storage engine
The MyISAM storage engine has a distinct disadvantage when compared to the optional InnoDB engine. While MyISAM only stores indexes in memory, InnoDB keeps both indexes and data in memory where it can be accessed much more quickly than if it is stored on disk. Based on your database architecture, this change can substantially improve database performance.
If you decide to make this change, there are related performance modifications that should be considered. Set the innodb_buffer_pool_size parameter to make use of 80% of your system’s memory. You should also try letting the storage engine multitask to help eliminate bottlenecks and improve database efficiency.
Enable indexing
Large MySQL tables benefit from being indexed to improve query performance. They provide a shortcut to locating information in a table by minimizing the number of rows that need to be inspected to return the desired data. If you are experiencing many long-running queries, your database may require indexing. Indexes are especially useful when using the SELECT statement in queries, but will produce enhanced performance with any read-related operations.
Optimizing data size
Proper database design can minimize the amount of data that needs to be transferred back and forth to disk storage. There are many ways to accomplish this such as declaring columns to be NOT NULL where possible. Using the smallest data type, you will save substantial space in a large database. Consider that declaring an entity as a MEDIUMINT rather than an INT achieves a 25% space reduction. Extrapolated over a large database, this can result in using a lot less storage and performing more efficient disk data transfers.
Monitoring to Find Optimization Targets
SQL Diagnostic Manager for MySQL is a comprehensive monitoring tool for MySQL systems which can point out areas that need to be addressed as well as letting you know if your modifications are working as intended. The tool provides over 600 prebuilt monitors that can be customized to investigate any aspect of your MySQL instances. Real-time monitoring alerts you to issues before they impact your users, giving you a chance to take corrective action. Identify long and locked queries to find the source of performance bottlenecks.
SQL Diagnostic Manager for MySQL enables you to create custom dashboards and charts that provide a visual guide to performance trends. This data can be instrumental in making intelligent decisions on where to focus your MySQL performance optimization efforts. A comparative analysis conducted periodically throughout an optimization project will show if the changes are making a difference in how your database performs. It’s an application that helps you keep your systems up and running as well as assisting in finding potential areas ripe for optimization.
