Sometimes the defaults that come with a product are exactly what you want. Other times they aren’t and you need to make a few changes. When it comes to MySQL, you’ll probably get along just fine if you never change a thing, but you can probably do better than the defaults with a few tweaks.
Last week I began to talk about MySQL as part of a larger series about the performance of dynamic websites. I talked about the different schemas used by MySQL and briefly mentioned why you might want to turn off MySQL DNS lookups and the configuration variable to do that. I mentioned I’d have more to say about configuring MySQL.
Today I want to share some thoughts about configuring MySQL and walk you through a handful of the many variables you can change from their defaults. I’ll also point you to some resources for more information about a number of other variables I don’t mention here.
MySQL Configuration Variables
Before jumping in, let me first say you aren’t likely to see a major impact on performance through tweaking MySQL configuration variables, certainly not when compared to other things you might tweak. At the same time, it’s likely some of the default configuration values aren’t what you want and a little optimization is worth your time.
Just know that beyond a certain point any additional performance gains might not be worth the effort and you’d be better served by optimizing something else. It’s also possible to misconfigure MySQL and cause some performance problems so you do want to be careful with these variables.
Whether you decide to make any changes or not, I think it’s worth the time to generally understand some of these variables so at the very least you can know their default values and possibly identify any potential causes for issues your site might have.
Before making any changes you might want to first know what values the variables currently hold. You can do that through a single SQL command:
[code type=”MySQL”]
SHOW VARIABLES
[/code]
Your hosting account likely provides access to the phpMyAdmin interface to manage your database. You can type the command above on the SQL tab of phpMyAdmin and you’ll receive a long list of variables and their values.
You can also make use of other tools to connect to and query the database, or you can use scripts like MySQLTuner or other tools that will find the variables and values and make recommendations to increase performance and stability.
To make changes you’ll need access to the my.cnf file for your database. The location of the file could be a little different depending on your server software and the operating system of the server, but on *nix systems, it’s probably located at etc/my.cnf or nearby.
You might also need to check with your web host to see if you have access to the file and can edit it yourself or if your hosting company needs to make changes for you. Either way, I think it’s worth looking at a few variables and how you might want them set.
First let me mention one of the variables I talked about last week, default_storage_engine. As the name implies the variable sets the default storage engine. It’s likely already set to InnoDB, but in case it isn’t, you can set InnoDB as the default.
[code type=”MySQL”]
default_storage_engine = InnoDB
[/code]
Now let’s talk about some variables I didn’t mention last week.
key_buffer_size allocates memory (RAM) to MySQL for MyISAM tables. If you have the memory available on your server, increasing the key_buffer_size can significantly improve database speed. MySQL documentation recommends a setting of 64M for servers with RAM greater than 256MB and a database that contains many tables. For MyISAM tables a value no greater than 25% of the RAM on your server is recommended.
[code type=”MySQL”]
key_buffer_size = 64M
[/code]
A corresponding variable for InnoDB tables is innodb_buffer_pool_size which can be set closer to 70% of server RAM. A good value is between 50% and 70% of RAM, but no larger than the size of the database
[code type=”MySQL”]
innodb_buffer_pool_size = 256M
[/code]
max_connections sets the maximum amount of concurrent connections (151 by default). A higher setting allows more concurrent requests. If you see a “too many connections” error, it’s possible you may want to increase this value to allow more connections. The downside is that more concurrent connections require more of your hardware and it’s possible to bring a server to a crawl by allowing too many connections at once.
[code type=”MySQL”]
max_connections = 250
[/code]
table_cache sets the amount of memory available for the table cache, the total number of tables MySQL can hold open at any given time. It should be set higher than the value for open_tables and the MySQL manual recommends setting the value using the formula:
[code type=”MySQL”]
table_cache = max_connections x N
[/code]
where N is the number of tables in a typical join.
thread_cache_size sets the number of unused threads to store in cache until needed. New connections need new threads, which are then destroyed when the connection closes. The thread_cache_size value sets how many threads can be stored in cache for future connections.
If set to 0, it effectively turns the cache off. The value isn’t an issue, unless you receive a lot of connections, hundreds per minute.
[code type=”MySQL”]
thread_cache_size = 192
[/code]
query_cache_type turns the query cache on and off. There are better ways to cache MySQL databases and you likely want to set this to off.
[code type=”MySQL”]
query_cache_type = OFF
[/code]
Assuming you decide to turn the query_cache_type off, you’d probably also want to set the query_cache_size variable to 0 to disallow any queries from being stored in cache. If interaction between your site and your database is on the lighter side and your site tends to read from the database more than writing to it, you might want to keep the query_cache_type on and set the query_cache_size to something like 128M or 256M
[code type=”MySQL”]
query_cache_size = 128M
[/code]
innodb_flush_log_at_trx_commit has a default value of 1, which makes the database fully ACID (atomicity, consistency, isolation, and durability) compliant. You can change the value to 0 or 2, both of which offer performance gains at the expense of less reliability.
[code type=”MySQL”]
innodb_flush_log_at_trx_commit = 1
[/code]
innodb_log_file_size sets the size of the InnoDB log file. Here’s an article on determining the how big you should set the size.
[code type=”MySQL”]
innodb_log_file_size = 128M
[/code]
innodb_log_buffer_size sets the buffer size for the log files. Depending on your data and traffic, the buffer can fill up quickly and so raising this value could help with performance.
[code type=”MySQL”]
innodb_log_buffer_size = 8M
[/code]
Additional Resources
Those are a few of the variables I commonly see mentioned as the ones you might want to tweak. If it wasn’t enough variables for your liking, here are some additional resources where you can read more about the configuration variables mentioned here and plenty of others I didn’t talk about.
- Server System Variables
- MySQL Configuration Variables
- Optimizing Server Settings for Improved MySQL Performance
- How to Optimize MySQL Performance Using MySQLTuner
- MySQL 5.7 Performance Tuning Immediately After Installation
- Performance Tuning and Configurations for your MySQL Server
- Important MYSQL Performance Tuning and Settings after Installation
- 15 Useful MySQL/MariaDB Performance Tuning and Optimization Tips
- 17 Key MySQL Config File Settings
Closing Thoughts
Not all sites and servers are the same and so MySQL configuration defaults shouldn’t be the same for all sites and servers. It’s possible the defaults aren’t right for your particular case and so it makes sense to be aware of how things are set. You can run the SHOW VARIABLES command or use a script to show you results and also make suggestions for changes.
You probably won’t see huge performance gains from making any of these configuration tweaks and so you don’t want to spend too much time with them, but some of the variables are worth knowing and they’re easy enough to change and change back.
I have a couple more MySQL topics I want to cover in the coming weeks. The next will be about using indexes to reduce the time to read information from MySQL (or any relational database). I’ll talk about the advantages and disadvantages of using indexes, how to create and remove different types of indexes, and I’ll offer some general thoughts for working with indexes.
Download a free sample from my book, Design Fundamentals.