15 Useful MYSQL/MARIADB performance tuning and optimization tips

Original address

MySQL is a powerful open source relational database management system (RDBMS). It was posted on 1995 (20 ago). It uses Structured Query Language (SQL), which may be the most popular choice in database content management. The latest MySQL version is 5.6.25, released on May 29, 2015.

An interesting fact about MySQL is that its name comes from the daughter "My" of Michael Widenius (the founder of MySQL). Although there are many interesting rumors about MySQL, this article is mainly to show you some useful practices to help you manage your MySQL server.

MySQL Performance Optimization

MySQL was acquired by Oracle in April 2009. The result is a division of the MySQL community that created a branch called MariaDB. The main reason for creating the branch is to keep the project free under the GPL.

Today, MySQL and MariaDB are one of the most popular RDMS (if not the most) for WordPress, Joomla, Magento, and other Web applications.

This article will show you some basic, but very useful tips on how to optimize MYSQL/MARIADB performance. Note that this article assumes that you have already installed MySQL or MariaDB. If you still don't know how to install them on your system, you can follow the instructions below to install them:

• Installing LAMP on Rhel/centos 7
• Installing LAMP on Fedora 22
• Installing LAMP in Ubuntu 15.04
• Installing MariaDB on Debian 8
• Installing MariaDB on Gentoo Linux
• Installing MariaDB on Arch Linux

Important: Don't blindly accept these suggestions before you start. Each MySQL setting is different and needs to be considered carefully before making any changes.

You need to understand this:

• The MYSQL/MARIADB configuration file is located in the /etc/my.cnf . You will need to restart the MySQL service each time you change this file for the changes to take effect.
• This article uses the MySQL 5.6 version.

1. Enable InnoDB per table one data file setting

First, there is an important explanation that InnoDB is a storage engine. MySQL and MariaDB use InnoDB as the default storage engine. Previously, MySQL used the system tablespace to hold tables and indexes in the database. This means that the only purpose of the server is database processing, and their storage disks are not used for other purposes.

InnoDB provides a more flexible way to store information for each database in a single .ibd data file. Each. idb file represents its own tablespace. In this way, a database operation similar to "TRUNCATE" can be done faster, and you can reclaim unused space when you delete or truncate a database table.

Another benefit of this configuration is that you can put some database tables on a separate storage device. This can greatly increase the I/O load on your disk.

MySQL version 5.6 and above is enabled by default innodb_file_per_table . You can see it in the/etc/my.cnf file. The directive looks like this:

1. innodb_file_per_table=1

2. Store MySQL database data on a separate partition

Note: This setting is only valid on MySQL and is not valid on MariaDB.

Sometimes operating system reads/writes can degrade the performance of your MySQL server, especially if the operating system and database data reside on the same disk. Therefore, I recommend that you use a separate disk (preferably an SSD) for the MySQL service.

To complete this step, you need to connect the new disk to your computer/server. For this article, I assume that the disk hangs in the/dev/sdb.

The next step is to prepare the new partition:

1. # fdisk /dev/sdb

Now press "N" to create a new partition. Then press "P" so that it is created as the primary partition. After this, set the partition number from 1-4. After that, you can select the partition size. Press ENTER here. In the next step, you need to configure the size of the partition.

If you want to use all of the disks, press ENTER one more time. Otherwise, you can manually set the size of the new partition. When you're ready, press "W" to save your changes. Now, we need to create a file system for our new partition. This can be done easily with the following command:

1. # mkfs.ext4 /dev/sdb1

Now we will mount the new partition to a directory. I created a directory named "SSD" under the root directory:

1. # mkdir /ssd/

Mount the new partition under the directory you just created:

1. # mount /dev/sdb1 /ssd/

You can add the following line to the boot auto mount in the/etc/fstab file:

1. /dev/sdb1 /ssd ext3 defaults 0 0

Now we move MySQL to the new disk

First stop the MySQL service:

1. # service mysqld stop

I suggest you?? Stop Apache/nginx at the same time to prevent any attempts to write to the database:

1. # service httpd stop
2. # service nginx stop

Now copy the entire MySQL directory into the new partition:

1. # cp /var/lib/mysql /ssd/ -Rp

This may take some time, depending on the size of your MySQL database. Once this process is complete rename the MySQL directory:

1. # mv /var/lib/mysql /var/lib/mysql-backup

Then create a symbolic Link:

1. # ln -s /ssd/mysql /var/lib/mysql

Now start your MySQL and Web services:

1. # service mysqld start
2. # service httpd start
3. # service nginx start

Your database will be accessed using the new disk in the future.

Original address

15 Useful MYSQL/MARIADB performance tuning and optimization tips