View all posts

MySQL Optimisation – How to tune MySQL

MySQL is a free and open-source relational database management system. Its name is a combination of “My” (the name of co-founder Michael Widenius’s daughter) and “SQL”, which is the abbreviation for Structured Query Language.

MySQL can be used for anything from a simple shopping list to a picture gallery or the vast amounts of information in a corporate network. To add, access, and process data stored in a database, you need a database management system such as MySQL Server.

With large applications, the sheer amount of data can lead to performance issues.

Consider Hardware Resources

Storage (Disk)

First, if you’re using the older style spinning hard disk drives (HDD) you may want to upgrade to either SATA or NVMe solid-state drives (SSD) for a huge performance improvement.

There are various tools available to check disk input/output usage. The most common ones are:

  • iotop
  • atop
  • sar

Here is a screenshot of atop:

Processor (CPU)

The processor is an important choice. As MySQL is fully multithreaded and makes use of all CPUs made available to it, the more powerful and more processing cores, the better.

There are various tools available to check CPU usage. The most common ones are:

  • top
  • htop
  • atop
  • sar

Here is a screenshot of htop:

Memory (RAM)

To improve the performance of database operations, MySQL allocates buffers and caches to RAM. In the default configuration, MySQL is designed to be run on a computer that has at least 512MB of RAM. Several cache and buffer-related MySQL system variables can be configured to improve performance. MySQL can also be configured to run on systems with limited memory by modifying the default configuration.

Network (Internet)

If the MySQL server is hosted online, then it’s important to have a good network connection. A bad network connection can lead to increased latency, dropped packets, and even server outages.

Use InnoDB, Not MyISAM

MyISAM is one of the older database styles used by some MySQL databases. It is a less efficient database design. The newer version of InnoDB supports more advanced features and has optimisation mechanisms built-in.

Pages are kept in InnoDB clustered indexes and arranged chronologically in physical blocks. For values that are too large for one page, InnoDB moves them to another location before indexing them. With this feature, relevant data stays in the same location on the storage device, which means the physical hard drive can access the data more quickly.

default_storage_engine = InnoDB

Use the Latest Version of MySQL

A part of the ongoing development includes performance enhancements. Some common performance adjustments may be rendered obsolete by newer versions of MySQL. In general, it’s always better to use native MySQL performance enhancement over scripting and configuration files.

Tools

The MySQL Tuner is a Perl script that allows you to review a MySQL installation quickly and make changes to increase performance and stability. A brief overview of the current configuration variables and status data is given along with a few basic performance suggestions.

MySQLTuner supports ~300 indicators for MySQL/MariaDB/Percona Server in this last version.

You can run MySQLTuner with the following command:

perl <(curl -s https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl)

Once it has run, it will give you a list of recommended changes:

Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    join_buffer_size (> 256.0K, or always use indexes with JOINs)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_definition_cache(400) > 28747 or -1 (autosizing if supported)
    performance_schema = ON enable PFS
    innodb_buffer_pool_size (>= 7.0G) if possible.
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

Important! Always backup your MySQL configuration before making changes:

cp /etc/my.cnf{,.backup}

MySQL tmpdir as tmpfs

Another method of speeding up MySQL on well optimised servers is to set up a tmpfs volume to be used as MySQL’s tmpdir. This speeds up MySQL performance dramatically as any temporary queries are handled in RAM, not Disk.

First, create the tmpdir file:

mkdir /var/lib/mysqltmp

Correct the file owenership:

chown mysql:mysql /var/lib/mysqltmp

Note the uid & gid of MySQL on the system. This will be used to mount the folder in fstab.

id mysql

Edit /etc/fstab and add the following line, replacing XXX with your uid & gid:

tmpfs /var/lib/mysqltmp tmpfs rw,gid=XXX,uid=XXX,size=512M,nr_inodes=20k,mode=0700 0 0

Mount the newly created tmpfs:

mount /var/lib/mysqltmp

Edit /etc/my.cnf and the following:

tmpdir=/var/lib/mysqltmp

Restart MySQL to apply the changes:

systemctl restart mysqld

You should now see the newly mounted tmpfs:

[root@quickhostuk ~]# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda1        79G   60G   16G  79% /
tmpfs           512M     0  512M   0% /var/lib/mysqltmp

Related Articles...

How to sync server time

How to sync server time in CentOS & AlmaLinux

The NTP protocol synchronises the clocks of computers over a network. In this article, we will show you how to quickly sync the server time. Many systems-related tasks and processes require the correct timezone. For example, the cron daemon uses... Read more

This website uses cookies

We use cookies for the analysis of our visitor data, to improve our website, and to give you a great website experience. For more information about the cookies we use, please see our cookie policy.