{"id":11560,"date":"2019-03-14T12:31:42","date_gmt":"2019-03-14T12:31:42","guid":{"rendered":"http:\/\/www.appservgrid.com\/paw92\/?p=11560"},"modified":"2019-03-14T12:31:42","modified_gmt":"2019-03-14T12:31:42","slug":"15-useful-mysql-mariadb-performance-tuning-and-optimization-tips","status":"publish","type":"post","link":"https:\/\/www.appservgrid.com\/paw92\/index.php\/2019\/03\/14\/15-useful-mysql-mariadb-performance-tuning-and-optimization-tips\/","title":{"rendered":"15 Useful MySQL\/MariaDB Performance Tuning and Optimization Tips"},"content":{"rendered":"

MySQL<\/strong>\u00a0is a powerful open source\u00a0Relational Database Management System<\/strong>\u00a0or in short\u00a0RDBMS<\/strong>. It was released back in 1995 (20 years old). It uses\u00a0Structured Query Language<\/strong>\u00a0which is probably the most popular choice for managing content within a database. The latest MySQL version is\u00a05.6.25<\/strong>\u00a0and was released on\u00a029 May 2015<\/strong>.<\/p>\n

An interesting fact about MySQL is the fact that the name comes from\u00a0Michael Widenius\u2019s<\/strong>\u00a0(MySQL\u2019s creator) daughter\u00a0My. Even<\/strong>\u00a0though there are plenty of interesting facts about MySQL, this article is meant to show you some useful practices to help you manage your MySQL server.<\/p>\n

\n

\"MySQL<\/a><\/p>\n

MySQL Performance Tuning<\/p>\n<\/div>\n

In April\u00a02009<\/strong>\u00a0the MySQL project was bought by Oracle. As a result a MySQL community fork called\u00a0MariaDB<\/strong>\u00a0was created. The main reason for creating the fork was to keep the project free under the General Public License.<\/p>\n

Today\u00a0MySQL<\/strong>\u00a0and\u00a0MariaDB<\/strong>\u00a0are one of the most (if not the most) frequently used RDBMS used for web applications such as\u00a0WordPress<\/strong>,\u00a0Joomla<\/strong>,\u00a0Magento<\/strong>\u00a0and others.<\/p>\n

This article will show you some basic, yet useful tips how to optimize the fine tune the performance of MySQL\/MariaDB. Please keep in mind that this article assumes that you already have MySQL or MariaDB installed. If you are still wondering how to install them on your system, you can follow our extensive guides here:<\/p>\n

    \n
  1. Installing LAMP on RHEL\/CentOS 7<\/a><\/li>\n
  2. Installing LAMP on Fedora 22<\/a><\/li>\n
  3. Setting Up LAMP on Ubuntu 15.04<\/a><\/li>\n
  4. Installing MariaDB on Debian 8<\/a><\/li>\n
  5. Install MariaDB on Gentoo Linux<\/a><\/li>\n
  6. Install MariaDB on Arch Linux<\/a><\/li>\n<\/ol>\n

    Important<\/strong>: Before we start \u2013 do not accept this suggestions blindly. Each MySQL setup is unique and requires additional thought, before making any changes.<\/p>\n

    Things you need to know:<\/p>\n

      \n
    1. MySQL\/MariaDB configuration file is located in\u00a0\/etc\/my.cnf<\/code>. Every time you modify this file you will need to restart the MySQL service so the new changes can take effect.<\/li>\n
    2. For writing this article MySQL version\u00a05.6<\/strong>\u00a0has been used as template.<\/li>\n<\/ol>\n

      1. Enable InnoDB file-per-table<\/h3>\n

      First it is important to explain that\u00a0InnoDB<\/strong>\u00a0is a storage engine. MySQL and MariaDB use InnoDB as default storage engine. In the past MySQL used to keep database tables and indexes in a system tablespace. This approach was meant for servers which sole purpose is database processing and their storage disk is not used for any other purposes.<\/p>\n

      The InnoDB provides more flexible approach and each database information is kept in a\u00a0.ibd<\/code>\u00a0data file. Each\u00a0.ibd<\/strong>\u00a0file represents a tablespace of its own. That way database operations such as \u201cTRUNCATE<\/strong>\u201d can be completed faster and you may also reclaim unused space when dropping or truncating a database table.<\/p>\n

      Another benefit of this configuration is the fact that you can keep some of the database tables in a separate storage device. This can greatly improve the\u00a0I\/O<\/strong>\u00a0load on your disks.<\/p>\n

      The\u00a0innodb_file_per_table<\/strong>\u00a0is enabled by default in MySQL 5.6 and above. You can see that in\u00a0\/etc\/my.cnf<\/strong>\u00a0file. The directive looks like this:<\/p>\n

      innodb_file_per_table=1\r\n<\/pre>\n
      2. Store MySQL Database Data on Separate Partition<\/h3>\n
      Note<\/strong>: This setup only works with MySQL, but not with MariaDB.<\/p>\n
      Sometimes OS read\/writes can slow down the performance of your MySQL server, especially if located on same hard drive. Instead, I would recommend using separate hard drive (preferably SSD) for the MySQL service.<\/p>\n
      To complete, this you will need to attach the new drive to your computer\/server. For the purpose of this article, I will assume that the drive will be under\u00a0\/dev\/sdb<\/strong>.<\/p>\n
      The next step is to prepare the new drive:<\/p>\n
      # fdisk \/dev\/sdb\r\n<\/pre>\n
      Now press \u201cn<\/strong>\u201d to create new partition. Next press \u201cp<\/strong>\u201d to make the new partition primary. After that, set the partition number from\u00a01-4<\/strong>. After that you will select the partition size. Press enter here. On the next step you will need to configure the size of the partition.<\/p>\n
      If you wish to use the entire disk press enter once more. Otherwise you can manually set the size of the new partition. When ready press \u201cw<\/strong>\u201d to write the changes. Now we will need to create a filesystem for our new partition. This can be easily done with:<\/p>\n
      # mkfs.ext4 \/dev\/sdb1\r\n<\/pre>\n
      Now we will mount our new partition in a folder. I have named my folder \u201cssd<\/strong>\u201d and created in the root directory:<\/p>\n
      # mkdir \/ssd\/\r\n<\/pre>\n
      We are ready to mount the new partition we just made in the new folder:<\/p>\n
      # mount \/dev\/sdb1  \/ssd\/\r\n<\/pre>\n
      You can perform the mount at startup by adding the following line in\u00a0\/etc\/fstab<\/strong>\u00a0file.<\/p>\n
      \/dev\/sdb1 \/ssd ext3 defaults 0 0\r\n<\/pre>\n
      Now you are ready to move MySQL to the new disk. First stop the MySQL service with:<\/p>\n
      # service mysqld stop\r\n<\/pre>\n
      I would recommend you stopping Apache\/nginx as well to prevent any attempts to write in the databases:<\/p>\n
      # service httpd stop\r\n# service nginx stop\r\n<\/pre>\n
      Now copy the entire MySQL directory in the new drive:<\/p>\n
      # cp \/var\/lib\/mysql \/ssd\/ -Rp\r\n<\/pre>\n
      This may take a while depending on the site of your MySQL databases. Once this process is complete rename the MySQL directory:<\/p>\n
      # mv \/var\/lib\/mysql \/var\/lib\/mysql-backup\r\n<\/pre>\n
      Next we will create a symlink.<\/p>\n
      # ln -s \/ssd\/mysql \/var\/lib\/mysql\r\n<\/pre>\n
      Now you are ready to start your MySQL and web service:<\/p>\n
      # service mysqld start\r\n# service httpd start\r\n# service nginx start\r\n<\/pre>\n
      At this point your MySQL databases will be accessed from the new drive.<\/p>\n
      3. Optimizing InnoDB buffer pool Usage<\/h3>\n
      The\u00a0InnoDB<\/strong>\u00a0engine has a buffer pool used for caching data and indexes in memory. This of course will help your MySQL\/MariaDB queries be executed significantly faster. Choosing the proper size here requires some very important decisions and good knowledge on your system\u2019s memory consumption.<\/p>\n
      Here is what you need to consider:<\/p>\n
        \n
      1. How much memory you need for other processes. This includes your system processes, page tables, socket buffers.<\/li>\n
      2. Is your server dedicated for MySQL or you will be running other memory hungry services.<\/li>\n<\/ol>\n
        On a dedicated box, you would probably want to give about\u00a060-70%<\/strong>\u00a0of the memory to the\u00a0innodb_buffer_pool_size<\/strong>. If you plan on running more services on a single box, you should re-consider the amount of memory you dedicate for your\u00a0innodb_buffer_pool_size<\/strong>.<\/p>\n
        The value that you should edit in\u00a0my.cnf<\/strong>\u00a0is:<\/p>\n
        innodb_buffer_pool_size\r\n<\/pre>\n
        4. Avoid Swappiness in MySQL<\/h3>\n
        Swapping<\/strong>\u00a0is process that occurs when system moves part of memory to a special disk space called \u201cswap<\/strong>\u201d. The event usually appears when your system runs out of physical memory and instead of freeing up some RAM, the system pushed the information into disk. As you might have guess the disk is much slower than your RAM.<\/p>\n
        By default the option is enabled:<\/p>\n
        # sysctl vm.swappiness \r\n\r\nvm.swappiness = 60\r\n<\/pre>\n
        To disable swappiness, run the following command:<\/p>\n
        # sysctl -w vm.swappiness=0\r\n<\/pre>\n
        5. Set MySQL Max Connections<\/h3>\n
        The\u00a0max_connections<\/strong>\u00a0directive tells your server how many concurrent connections are permitted. The MySQL\/MariaDB server allows the value given in\u00a0max_connections + 1<\/strong>\u00a0for user with SUPER privileges. The connection is opened only for the time MySQL query is executed \u2013 after that it is closed and new connection can take its place.<\/p>\n
        Keep in mind that too many connections can cause high RAM usage and lock up your MySQL server. Usually small websites will require between\u00a0100-200<\/strong>\u00a0connections while larger may require\u00a0500-800<\/strong>\u00a0or even more. The value you apply here strongly depends on your particular MySQL\/MariaDB usage.<\/p>\n
        You can dynamically change the value of\u00a0max_connections<\/code>, without having to restart the MySQL service by running:<\/p>\n
        # mysql -u root -p\r\nmysql> set global max_connections := 300;\r\n<\/pre>\n
        6. Configure MySQL thread_cache_size<\/h3>\n
        The\u00a0thread_cache_size<\/code>\u00a0directive sets the amount of threads that your server should cache. As the client disconnects, his threads are put in the cache if they are less than the\u00a0thread_cache_size<\/strong>. Further requests are completed by using the threads stored in the cache.<\/p>\n
        To improve your performance you can set the\u00a0thread_cache_size<\/strong>\u00a0to a relatively high number. To find the thread cache hit rate, you can use the following technique:<\/p>\n
        mysql> show status like 'Threads_created';\r\nmysql> show status like 'Connections';\r\n<\/pre>\n
        Now use the following formula to calculate the thread cache hit rate percentage:<\/p>\n
        100 - ((Threads_created \/ Connections) * 100)\r\n<\/pre>\n
        If you get a low number, it means that most of the new mysql connections are starting new thread instead of loading from cache. You will surely want to increase the\u00a0thread_cache_size<\/strong>\u00a0in such cases.<\/p>\n
        The good thing here is that the\u00a0thread_cache_size<\/strong>\u00a0can be dynamically changed without having to restart the MySQL service. You can achieve this by running:<\/p>\n
        mysql> set global thread_cache_size = 16;\r\n<\/pre>\n
        7. Disable MySQL Reverse DNS Lookups<\/h3>\n
        By default MySQL\/MariaDB perform a DNS lookup of the user\u2019s IP address\/Hostname from which the connection is coming. For each client connection, the IP address is checked by resolving it to a host name. After that the host name is resolved back to an IP to verify that both match.<\/p>\n
        This unfortunately may cause delays in case of badly configured DNS or problems with DNS server. This is why you can disable the reverse DNS lookup by adding the following in your configuration file:<\/p>\n
        [mysqld]\r\n# Skip reverse DNS lookup of clients\r\nskip-name-resolve\r\n<\/pre>\n
        You will have to restart the MySQL service after applying these changes.<\/p>\n
        8. Configure MySQL query_cache_size<\/h3>\n
        If you have many repetitive queries and your data does not change often \u2013 use query cache. People often do not understand the concept behind the\u00a0query_cache_size<\/code>\u00a0and set this value to gigabytes, which can actually cause degradation in the performance.<\/p>\n
        The reason behind that is the fact that threads need to lock the cache during updates. Usually value of\u00a0200-300 MB<\/strong>\u00a0should be more than enough. If your website is relatively small, you can try giving the value of\u00a064M<\/strong>\u00a0and increase in time.<\/p>\n
        You will have to add the following settings in the MySQL configuration file:<\/p>\n
        query_cache_type = 1\r\nquery_cache_limit = 256K\r\nquery_cache_min_res_unit = 2k\r\nquery_cache_size = 80M\r\n<\/pre>\n
        9. Configure tmp_table_size and max_heap_table_size<\/h3>\n
        Both directives should have the same size and will help you prevent disk writes. The\u00a0tmp_table_size<\/code>\u00a0is the maximum amount of size of internal in-memory tables. In case the limit in question is exceeded the table will be converted to on-disk MyISAM table.<\/p>\n
        This will affect the database performance. Administrators usually recommend giving\u00a064M<\/strong>\u00a0for both values for every GB of RAM on the server.<\/center><\/p>\n
        [mysqld]\r\ntmp_table_size= 64M\r\nmax_heap_table_size= 64M\r\n<\/pre>\n
        10. Enable MySQL Slow query Logs<\/h3>\n
        Logging slow queries can help you determine issues with your database and help you debug them. This can be easily enabled by adding the following values in your MySQL configuration file:<\/p>\n
        slow-query-log = 1\r\nslow-query-log-file = \/var\/lib\/mysql\/mysql-slow.log\r\nlong_query_time = 1\r\n<\/pre>\n
        The first directive enables the logging of slow queries, while the second one tells MySQL where to store the actual log file. Use\u00a0long_query_time<\/code>\u00a0to define the amount of time that is considered long for MySQL query to be completed.<\/p>\n
        11. Check for MySQL idle Connections<\/h3>\n
        Idle connections consume resources and should be interrupted or refreshed when possible. Such connections are in \u201csleep<\/strong>\u201d state and usually stay that way for long period of time. To look for idled connections you can run the following command:<\/p>\n
        # mysqladmin processlist -u root -p | grep \u201cSleep\u201d\r\n<\/pre>\n
        This will show you list of processes that are in\u00a0sleep state<\/strong>. The event appears when the code is using persistent connection to the database. When using PHP this event can appear when using\u00a0mysql_pconnect<\/strong>\u00a0which opens the connection, after that executes queries, removes the authentication and leaves the connection open. This will cause any per-thread buffers to be kept in memory until the thread dies.<\/p>\n
        The first thing you would do here is to check the code and fix it. If you don\u2019t have access to the code that is being ran, you can change the\u00a0wait_timeout<\/code>\u00a0directive. The default value is\u00a028800<\/strong>\u00a0seconds, while you can safely decrease it to something like\u00a060<\/strong>:<\/p>\n
        wait_timeout=60<\/pre>\n
        12. Choosing Right MySQL Filesystem<\/h3>\n
        Choosing the right filesystem is vital for your databases. Most important things you need to consider here are \u2013 data integrity, performance and ease of administration.<\/p>\n
        As per MariaDB\u2019s 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.<\/p>\n
        Below you can find some useful information about the three filesystems:<\/p>\n\n\n\n\n\n
        Filesystems<\/b><\/td>\nXFS<\/b><\/td>\nExt4<\/b><\/td>\nBtrfs<\/b><\/td>\n<\/tr>\n
        Maximum filesystem size<\/td>\n8EB<\/td>\n1EB<\/td>\n16EB<\/td>\n<\/tr>\n
        Maximum file size<\/td>\n8EB<\/td>\n16TB<\/td>\n16EB<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
        The pros and cons of the Linux filesystems have been extensively covered in our article:<\/p>\n
          \n
        1. Linux Filesystem Explained<\/a><\/li>\n<\/ol>\n
          13. Set MySQL max_allowed_packet<\/h3>\n
          MySQL splits data into packets. Usually a single packet is considered a row that is sent to a client. The\u00a0max_allowed_packet<\/code>\u00a0directive defines the maximum size of packet that can be sent.<\/p>\n
          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.<\/p>\n
          14. Check MySQL Performance Tuning<\/h3>\n
          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.<\/p>\n
          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\u00a0mysqltuner<\/strong>.<\/p>\n
          To download and run it, use the following set of commands:<\/p>\n
          # wget https:\/\/github.com\/major\/MySQLTuner-perl\/tarball\/master\r\n# tar xf master\r\n# cd major-MySQLTuner-perl-993bc18\/\r\n# .\/mysqltuner.pl \r\n<\/pre>\n
          You will receive a detailed report about your MySQL usage and recommendation tips. Here is a sample output of default MariaDB installation:<\/p>\n
          \"MySQL<\/a><\/p>\n
          15. Optimize and Repair MySQL Databases<\/h3>\n
          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 \u2018mysqlcheck<\/strong>\u2018, which automatically check, repair and optimize databases of all tables in Linux.<\/p>\n
          # mysqlcheck -u root -p --auto-repair --check --optimize --all-databases\r\n# mysqlcheck -u root -p --auto-repair --check --optimize databasename\r\n<\/pre>\n
          That\u2019s 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.<\/p>\n
          Read Also<\/strong>:<\/p>\n
            \n
          1. 20 MySQL Commands for Database Administration<\/a><\/li>\n
          2. 4 Useful Tools to Monitor MySQL Performance<\/a><\/li>\n
          3. MySQL Backup and Restore Commands<\/a><\/li>\n<\/ol>\n
            Source<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
            MySQL\u00a0is a powerful open source\u00a0Relational Database Management System\u00a0or in short\u00a0RDBMS. It was released back in 1995 (20 years old). It uses\u00a0Structured Query Language\u00a0which is probably the most popular choice for managing content within a database. The latest MySQL version is\u00a05.6.25\u00a0and was released on\u00a029 May 2015. An interesting fact about MySQL is the fact that the … <\/p>\n
            Continue reading “15 Useful MySQL\/MariaDB Performance Tuning and Optimization Tips”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-11560","post","type-post","status-publish","format-standard","hentry","category-linux"],"_links":{"self":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts\/11560","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/comments?post=11560"}],"version-history":[{"count":1,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts\/11560\/revisions"}],"predecessor-version":[{"id":11561,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts\/11560\/revisions\/11561"}],"wp:attachment":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/media?parent=11560"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/categories?post=11560"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/tags?post=11560"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}