15 Useful MySQL/MariaDB Performance Tuning and Optimization Tips

12. Choosing Right MySQL Filesystem

Choosing the right filesystem is vital for your databases. Most important things you need to consider here are – data integrity, performance and ease of administration.

As per MariaDB’s recommendations, the best file systems are XFS, Ext4 and Btrfs. All of them are enterprise journaling filesystems that can be used with very large files and large storage volumes.

Below you can find some useful information about the three filesystems:

The pros and cons of the Linux filesystems have been extensively covered in our article:

1. Linux Filesystem Explained

13. Set MySQL max_allowed_packet

MySQL splits data into packets. Usually a single packet is considered a row that is sent to a client. The max_allowed_packet directive defines the maximum size of packet that can be sent.

Setting this value too low can cause a query to stall and you will receive an error in your MySQL error log. It is recommended to set the value to the size of your largest packet.

14. Check MySQL Performance Tuning

Measuring your MySQL/MariaDB performance is something that you should do on regular basis. This will help you see if something in the resource usage changes or needs to be improved.

There are plenty of tools available for benchmarking, but I would like to suggest you one that is simple and easy to use. The tool is called mysqltuner.

To download and run it, use the following set of commands:

# wget https://github.com/major/MySQLTuner-perl/tarball/master
# tar xf master
# cd major-MySQLTuner-perl-993bc18/
# ./mysqltuner.pl 

You will receive a detailed report about your MySQL usage and recommendation tips. Here is a sample output of default MariaDB installation:

15. Optimize and Repair MySQL Databases

Sometimes MySQL/MariaDB database tables get crashed quite easily, especially when unexpected server shut down, sudden file system corruption or during copy operation, when database is still accessed. Surprisingly, there is a free open source tool called ‘mysqlcheck‘, which automatically check, repair and optimize databases of all tables in Linux.

# mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
# mysqlcheck -u root -p --auto-repair --check --optimize databasename

That’s it! I hope you have found the above article useful and help you tune up your MySQL server. As always if you have any further questions or comments, please submit them in the comment section below.

Read Also:

1. 20 MySQL Commands for Database Administration
2. 4 Useful Tools to Monitor MySQL Performance
3. MySQL Backup and Restore Commands