MySQL Optimization¶

How To Avoid Performance Issuses When MySQL Data is Stored on HDD¶

It is recommended to use SSD to store the data in the MySQL 5.X database. However, if you do need to use the HDD, follow the steps described in the optimizing InnoDB Disk I/O article to avoid performance issues and set the following configuration parameters in the /etc/my.cnf file:

[mysqld]
innodb_file_per_table = 0
wait_timeout = 28800

Usage of The utf8mb4 Character Set (The Full 4-Byte UTF-8 Unicode Encoding) in MySQL¶

To store supplementary characters (such as 4-byte emojis), configure the options file to use the utf8mb4 character set. Put the following configuration settings in your options file /etc/my.cnf:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

MySQL also can load default options from another file (not /etc/my.cnf). In such cases, you have to put the configuration settings in this file. To find out which configuration files your MySQL server uses, run the following command:

$ mysqld --help --verbose 2> /dev/null | grep -A1 'Default options'

You will get the output with MySQL config files names similar to this:

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf

If you use the version of MySQL that is older than 5.7, the following configuration parameters should be set in the /etc/my.cnf file:

[mysqld]
innodb_file_format = Barracuda
innodb_large_prefix = 1

Since MySQL 5.7, these parameters are set by default.

You can find more information on MySQL configuration in the Unicode Support and InnoDB File-Format Management articles.

You can also change the defaults for Doctrine so that the generated SQL uses the correct character set. To achieve this, put the following configuration into the config/config.yml file:

doctrine:
    dbal:
        charset: utf8mb4
        default_table_options:
            charset: utf8mb4
            collate: utf8mb4_unicode_ci

For more details, please see the Setting up the Database to be UTF8 article.