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:
|Maximum filesystem size||8EB||1EB||16EB|
|Maximum file size||8EB||16TB||16EB|
The pros and cons of the Linux filesystems have been extensively covered in our article:
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.