The underlying storage technology and the file system format can impact performance significantly. In general, locally attached SSD storage will perform best. However, network based storage systems can perform well. As always, performance is dependent on the request profile coming from clients. You should spend some time studying and experimenting with different storage technologies and configuration options.
Aside from the storage technology, the file system format used with
MySQL can impact performance. The
xfs file system is a good starting point. The
ext4 file system is another good alternative.
There are different storage engines available for
MySQL. The default storage engine is
InnoDB is good for performance testing and tuning.
Information on alternative storage engines can be found in the MySQL documentation .
MySQL can benefit from adjustments to kernel parameters. Below is a list of kernel related settings that can have a positive impact on performance.
Linux-PAM limits can be changed in the
/etc/security/limits.conf file, or by using the
If you want more information about how to display and modify parameters check the documentation of the
To display all limits:
To display the
memlock (Max locked-in-memory address space) limit only:
memlock is the only PAM limit which is useful to adjust for
The suggested value for
unlimited when using huge pages with
Enabling huge pages can result in significant performance gains (discussed below).
The suggestion to set
memlock when huge pages are enabled can be found in the
Making changes to the Linux Virtual Memory subsystem can also improve performance.
These settings can be changed in the
/etc/sysctl.conf file, or by using the
If you want more information about how to display and modify virtual memory parameters check the documentation of the
Documentation on each of these parameters can be found in the admin-guide for sysctl in the Linux source code .
To list all kernel parameters available:
sudo sysctl -a
MySQL benefits from using huge memory pages. Huge pages reduce how often virtual memory pages are mapped to physical memory.
To see the current memory page configuration, run the following command on the host:
cat /proc/meminfo | grep ^Huge
The output should be similar to:
HugePages_Total: 0 HugePages_Free: 0 HugePages_Rsvd: 0 HugePages_Surp: 0 Hugepagesize: 2048 kB Hugetlb: 0 kB
Huge pages are not being used if
HugePages_Total is 0 (this is the default).
Also note that
Hugepagesize is 2MB which is the typical default for huge pages on Linux.
The kernel parameter that enables huge pages is shown below:
This parameter sets the number of huge pages you want the kernel to make available to applications.
The total amount of memory that will be used for huge pages will be this number (defaulted to 0) times the
As an example, if you want a total of 1GB of huge page space, then you should set
vm.nr_hugepages to 500 (500x2MB=1GB).
sudo sysctl -w vm.nr_hugepages=500
To make the change permanent:
sudo sh -c 'echo "vm.nr_hugepages=500" >> /etc/sysctl.conf'
You should set
vm.nr_hugepages to a value that gives a total huge page space slightly larger than the
MySQL buffer pool size (discussed later).
It should be slightly larger than the buffer pool because
MySQL will use additional memory for things like connection management.
More information on the different parameters that affect the configuration of huge pages can be found in the admin-guide for hugetlbpage in the Linux source code .
The easiest way to gain performance is to use the latest version of GCC. Aside from that, the flags
-flto can be used to potentially gain additional performance. Usage of these flags is explained in the
Migrating C/C++ applications section of the
Migrating applications to Arm servers learning path.
MySQL relies on OpenSSL for cryptographic operations. Thus, the version of OpenSSL used with MySQL (and the GCC version and switches used to compile it) can impact performance. Typically using the Linux distribution default version of OpenSSL is sufficient.
However, it is possible to use newer versions of OpenSSL which could yield performance improvements. This is achieved by building and installing OpenSSL before building MySQL.