When I began my journey of running a website about 5 years ago, I had very little experience working with servers. Since then, I’ve spent hundreds of hours researching and optimizing the Custom PC Review server which has spanned several types of web servers, several types of database servers, multiple caching softwares, firewalls, and probably a couple more pieces of software that I completely forgot about. Of all the different things that need to be tuned in a server however, I’ve learned that one of the biggest pieces is the database, which if configured incorrectly, can literally make even the most powerful server in the world crumble on the lightest workloads.
Ever since the inception of Custom PC Review, we’ve been running on WordPress, which runs on the MySQL database. Last year, we switched over to MariaDB which is a drop-in replacement for MySQL meaning it’s fully compatible with MySQL databases and MySQL queries. In my experience, MariaDB has provided better performance compared to MySQL, so I highly recommend trying them out if you’re still running MySQL. That said, let’s get down to business and talk about some of the important settings I’ve learned about tuning MySQL from our database.
Now before I begin, I do want to mention that last year I converted our database from MyISAM to InnoDB tables so most of the settings below will be geared towards databases that use mostly InnoDB tables. If you’re still on MyISAM tables, I strongly suggest converting to InnoDB as there are many benefits associated with using InnoDB tables. That’ll however be another article for another time.
As far as server specs, we’re currently running a quad core Xeon E5-2609V2, 32GB Kingston DDR3L ECC memory, and dual 400GB Samsung 845DC Pro drives in RAID 1. For those interested in our server setup, check out the specs in our server update article.
innodb_buffer_pool_size – This setting determines the size of the InnoDB index and data buffer in memory. This is the most important setting for applications that predominantly use InnoDB tables. The default is generally 128M, but if your database is mostly InnoDB data, I’d recommend setting this at 65-75% total system memory then adjusting it from there.
Since our database is primarily InnoDB, we settled on a value of 24G for this setting. That said, our database is nowhere near that size, so we’ll be revisiting this setting once we figure out how else to better use the memory.
innodb_buffer_pool_size = 24G
innodb_log_file_size – This is the size of the undo/redo logs in InnoDB. This allows InnoDB to either undo a transaction or redo a transaction in the event of a database crash. I recommend setting this somewhere between default and 4GB depending on how write intensive your application is. For less write intensive applications, the default of 50MB can be enough, but if you have a relatively capable server, I’d recommend starting at 128MB then adjusting up to 4GB.
Although our system is very read heavy, we weren’t short on memory, so we went with a value of 2GB for this setting. We’ll probably adjust it lower down the line if we can find a better use for the available system resources.
innodb_log_file_size = 2048M
innodb_flush_log_at_trx_commit – By default this is set at a value of 1 which is ACID (Atomicity, Consistency, Isolation, Durability) compliant. In this mode, the log buffer is written out to the log file at each transaction commit and the log file is flushed to disk. When the setting is set to 2, the log buffer is written out to the log file at each transaction commit, but the log file is only flushed to disk once per second. When the setting is set to 0, nothing is done at each transaction commit, but the log buffer is written out to the log file which is then flushed to disk every second. As such, a setting of 0 will give you the highest performance while a setting of 1 will give you the greatest reliability. A setting of 2 will give you a little bit of both worlds. You’ll definitely want to make sure your database is on some sort of UPS backup. Sudden power failure with a setting of 0 and 2 on innodb_flush_log_at_trx_commit can possibly wipe out 1 or more seconds of data written in the buffer.
Since our system is colocated in a state of the art facility with UPS (Uninterrupted Power Supply) systems in place, we decided to select a value of 0 on this one for maximum performance.
innodb_flush_log_at_trx_commit = 0
skip-name-resolve – This setting disables DNS lookups. If your database server is sitting on the same server as your web server or you exclusively use IP addresses to communicate, add this setting to improve response times.
As our system has the database running on the localhost, we added in the skip-name-resolve setting to eliminate DNS lookups altogether.
skip-name-resolve
tmpdir – If your database server is riddled with tons of slow queries, moving the MySQL temporary directory from disk to tmpfs (memory) may dramatically improve your database speed as slow queries need to be written to the temporary directory causing slowdowns. For a typical unoptimized WordPress database with loads of slow queries, this can make a huge difference. In our CentOS system, a tmpfs drive is setup by the OS which can be found using the “df -i” command in shell. Simply set the tmpdir setting to the location of the tmpfs directory and you’ll be experiencing significantly faster performance.
As we’re running on WordPress with plenty of plugins and no code modification, there are some pretty bad queries that run very slowly. This reduced response times by quite a bit however, implementing the tmpdir and pointing it to our tmpfs drive greatly improved performance.
tmpdir = /tmp/wpcache
Final Thoughts
So from optimizing our own databases here, these are what we believe to be the five most important settings to review when optimizing a database that’s primarily InnoDB. Keep in mind, I’m still learning about the world of web and database servers, so anything posted here is for informational purposes only and I am not responsible for any damage you do to your system. I also want to say that each database configuration will work best when customized for your specific server setup and application, so I’d recommend taking my suggestions here as guidelines.
If you have any questions or have any suggestions on other settings to check out, please let me know in the forums. Until next time, stay tuned!
