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:

Filesystems XFS Ext4 Btrfs
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:

  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:

MySQL Performance Tuning

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
Best Affordable Linux and WordPress Services For Your Business
Outsource Your Linux and WordPress Project and Get it Promptly Completed Remotely and Delivered Online.

If You Appreciate What We Do Here On TecMint, You Should Consider:

  1. Stay Connected to: Twitter | Facebook | Google Plus
  2. Subscribe to our email updates: Sign Up Now
  3. Get your own self-hosted blog with a Free Domain at ($3.45/month).
  4. Become a Supporter - Make a contribution via PayPal
  5. Support us by purchasing our premium books in PDF format.
  6. Support us by taking our online Linux courses

We are thankful for your never ending support.

Marin Todorov

I am a bachelor in computer science and a Linux Foundation Certified System Administrator. Currently working as a Senior Technical support in the hosting industry. In my free time I like testing new software and inline skating.

Your name can also be listed here. Got a tip? Submit it here to become an TecMint author.

RedHat RHCE and RHCSA Certification Book
Linux Foundation LFCS and LFCE Certification Preparation Guide

You may also like...

14 Responses

  1. benyamin says:

    Hello ,

    I got a dedicated server with the following characteristics.

    Cpu:Intel(R) Xeon(R) CPU E3-1231 v3 @ 3.40GHz
    Ram:16 GB DDR3
    Hard:1TB Sata3
    

    On Linux server with Centos7 installs and more detailed specifications below.

    DirectAdmin 1.54.1
    Dovecot 2.3.3 (dcead646b)
    Exim 4.91
    MySQL 10.0.36
    Named 9.9.4
    Nginx 1.15.1
    php 7.0.32
    php2 7.1.22
    Pure-FTPd 1.0.47
    

    The problem with this is that I do not know the correct configuration for the error problem 504 Gateway Timeout Error, and I usually have this error on sites.

    Someone guides me?

    What are the best settings & config for “my.cnf” “nginx.cnf” “php.ini”? Guidance if possible?

    thanks .

  2. Jason Randle says:

    I have been learning more and more about tuning/hardening from your website when referencing Maria/DB. Thanks for the great presentations and the easy to copy one-liners for my SSH session. Cheers!

  3. Ashish Chauhan says:

    I really appreciate your explanation and problem solving methodologies. It’s Awesome article. Thank you very much for the time and effort.

Got something to say? Join the discussion.

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.