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 = InnoDBUse 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/mysqltmpCorrect the file owenership:
chown mysql:mysql /var/lib/mysqltmpNote the uid & gid of MySQL on the system. This will be used to mount the folder in fstab.
id mysqlEdit /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 0Mount the newly created tmpfs:
mount /var/lib/mysqltmpEdit /etc/my.cnf and the following:
tmpdir=/var/lib/mysqltmpRestart MySQL to apply the changes:
systemctl restart mysqldYou 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/mysqltmpRelated Articles...
