How Monitoring Can Help With Optimizing & Tuning MySQL / MariaDB Databases
Optimizing MySQL / MariaDB can be a very tedious process filled with trial and error. There is no default set of configuration options that you can use to achieve optimal performance because the optimized buffer sizes and settings are dependent upon your dataset and usage patterns. There are some tools to help make this process easier, so in this guide I’ll show you how to use them to simplify the optimization process and get your database server humming along nicely.
Since both are extremely similar, for brevity I will be referring to both servers as simply MySQL.
Overview of the Tuning Process
Before we get to database-specific parameters and tuning, let’s ensure we have a baseline of our current database status and performance metrics. Using an enterprise application monitoring tool like Opsview Monitor, we can easily view current metrics like Log pending writes, InnoDB Waits, Memory Consumption, CPU Utilization, and Connections. Use graphing to easily and quickly view the information, and use Graph Center to create a series of customized charts from any period, past to present. It’s important that we know where we started before changing parameters.
Now let’s run some basic analyses and get some recommendations on optimal configuration for database performance.
Server Tuning Preparation
The first step in tuning your database server configuration is to download Matthew Montgomery’s open-source MySQL Tuning Primer. While it was originally designed for MySQL, I have found it to work fine on MariaDB installations as well. To download the script, visit the MySQL Tuning Primer Script website and click the green download button on the right-hand side, or run (via SSH or the console):
(make sure this command is executed all in one line)
Before you can run the script, you must make sure that its requirements are met and that you can access your server with the mysql command. To install the bc program that it needs, run:
# Debian / Ubuntu
apt-get install bc
# CentOS / RHEL / Fedora
yum install bc
Once you have that installed, create (or edit) a file called ~/.my.cnf in your home directory. This file should contain the following:
user = MYSQL_USERNAME
pass = MYSQL_PASSWORD
host = localhost
Please change MYSQL_USERNAME to your username, MYSQL_PASSWORD to your MySQL password, and edit the localhost entry if needed, then save the file.
To protect it, run:
chmod 0600 ~/.my.cnf
and verify operation with the mysql command. If it connects, then you are ready to run the primer script. Running:
in the script’s directory will execute the primer and analyze your existing MySQL configuration. If you have a large dataset, the script may take a while, but when finished it will present recommendations.
Most of these values can be adjusted in the following file:
While the values presented will vary based on your system and dataset, there are some general recommendations I can provide.
The first step to database server optimization is to examine your application’s table structure to ensure that any conditional column used in queries by your application is indexed. You can easily add indexes with phpMyAdmin or MySQL workbench, or via the SQL command:
ALTER TABLE `table_name` ADD INDEX `column_name` (`column_name`)
In the above example, modify table_name to be the name of the table in question, and column_name to be the name of the column that is used by a WHERE or JOIN clause. A properly indexed database can dramatically improve performance. Once you have ensured your databases are well-indexed, then it is time to examine and optimize your server configuration.
If you make extensive use of joins, you can increase the join_buffer_size value. However, if you find yourself needing to increase this value considerably (say, more than 16M), you may want to reexamine your indexes.
Open Files and Table Cache
MySQL caches frequently used table to eliminate the I/O overhead associated with opening and closing files. If you have multiple tables and the tuning primer script recommends increasing your table cache, it might be a good idea to go ahead and bump up your open_files_limit to at least 65535. While this seems like a lot, it doesn’t use any additional resources and ensures that MySQL can open the files it needs without having to juggle file descriptors.
If you’re using InnoDB tables, then the innodb_buffer_pool_size value is critical to your database’s performance. If you have the RAM to spare, give it as much as you can, at least enough to cover at least the InnoDB index space, and optimally the entire dataset.
If you use MyISAM tables, the query cache is important. On InnoDB-only setups, this value is often turned off or set low, but if you make use of MyISAM, you’ll want to make sure that enough memory is allocated to the query cache. If the tuning primer says that the query_cache_used value is close to the query_cache_size limit, then raising this value would be helpful.
The key_buffer_size setting allows you to adjust the index buffers in memory in MySQL. Pay close attention to the Key buffer free ratio value presented by the tuning primer. If that figure drops below 20%, it would be advisable to increase the key_buffer_size if you have enough memory on the server to do so.
If MySQL ran close to the max_connections value during its current run, the tuning primer script will advise you to increase it. While this is a good idea if max_connections is set to a low value, raising this past what your server can handle will have no positive effect.
The max_heap_table_size and tmp_table_size dictate the sizes of temporary tables that can be constructed in memory. If the tuning primer recommends raising this value, it is advisable to do so if you can spare the memory. While its likely you can’t completely prevent these tables from being written to disk, reducing the disk percentage will increase performance.
Applying the Changes
Once you have adjusted the /etc/my.cnf file, you will need to restart MySQL. To do so, run:
service mysql restart
(You may need to change mysql to mysql-server or mysqld, depending on your system)
Monitoring and Configuration Change Analysis
You will want to monitor your system for the next 24–48 hours to see if your changes made any performance improvements. Again, using enterprise monitoring like Opsview Monitor allows you to quickly view current performance metrics.
If you get less than stellar results, re-run the script after a few hours (ideally 24 hours) to see if any further adjustments need to be made. Even if you are satisfied with the changes, I still recommend running the primer script once more to see if any further tweaks are necessary.
Once you’re sure that your application database queries and server configuration are optimized, continued monitoring of your server with Opsview for predictive analysis of performance and failure alerting is essential to ensure optimum performance and reliability.
Ready to get started with Opsview Monitor? Get your free trial today
by Robert W. Oliver II, Jul 14, 2017