Newrelic?is an application monitoring tool that truly is developer’s best friend. It’s such a true statement. ?I’ve used Newrelic to pinpoint and successfully troubleshoot numerous applications. ?I’ve recently switched all database tables from MyISAM to InnoDB for a couple of reasons that are outside the scope of this post. What came to my intention is that my DB server’s disk usage went from about 20% to 70% in a few months. ?Knowing the size of databases on the server and the traffic I was getting, data growth seemed way too fast and the estimated time until I ran out of space was 4 months. Thank you NewRelic, I’m gonna have to do something about that today! After a quick analysis it turns out that my Percona server (optimised version of MySQL), contained lots of?Mysql-bin files that took up most of the disk space.?Mysql-bin files are binary log files, that are typically used for replication. ?I don’t use replication and therefore,?Mysql-bin files are unnecessary and will save up A LOT of disk space. They are typically stored in numeric order like mysql-bin0000xx and so on.
Mysql-bin files cleanup
So, the way to go about removing?Mysql-bin files is:
1. Edit my.cnf file, typically in /etc/my.cnf and comment out these 2 lines
#log-bin=mysql-bin #binlog_format=mixed
2. restart mysql server
3. remove all Mysql-bin files using rm
After doing this my used disk space went from %70 to %20 pictured below:
Remember, remove Mysql-bin files only if you don’t have replication enabled. If you do have replication turned on, then binary logging is mandatory and you will need to allocate more space on your server.
Couple of other settings that you want to check in your my.cnf file would be: innodb_buffer_pool_size and?innodb_log_file_size. ?innodb_log_file_size should be about 25% of your buffer pool size. And your buffer pool size shouldn’t exceed your RAM size of course. Mine is set to about 50% of total RAM available on the server.