Оптимизация MySQL

Несколько шагов по оптимизации MySQL

1)
Перед оптимизацией таблиц необходимо посмотреть, какие таблицы самые большие
и в каких таблицах есть пустоты:
SELECT table_name, table_rows, ROUND(data_length/1024/1024,2) as mb, ROUND(data_free/1024/1024) as free_mb
FROM information_schema.tables
WHERE  table_schema = 'db_name' ORDER BY 3 DESC;
Пример вывода:
+-----------------------+------------+--------+---------+
| table_name            | table_rows | mb     | free_mb |
+-----------------------+------------+--------+---------+
| table1              |        1180771 | 304.83 |      56 |
| table2              |         468621 | 234.76 |     163 |
| table3              |         961347 | 159.02 |      56 |
Колонка mb показывает общий размер таблицы в MB
Колонка free_mb отображает общее неиспользуемое пространство.

Оптимизация таблицы tableN, заключающаяся в ликвидации пустот, выполняется командой:
OPTIMIZE TABLE tableN;
Вывод после нормальной оптимизации MyISAM
+----------------------+----------+----------+----------+
| Table                | Op       | Msg_type | Msg_text |
+----------------------+----------+----------+----------+
| db_name.tableN       | optimize | status   | OK       |
+----------------------+----------+----------+----------+
Вывод после нормальной оптимизации InnoDB
+----------------------+----------+----------+----------+
| Table                | Op       | Msg_type | Msg_text |
+----------------------+----------+----------+----------+
| db_name.tableN       | optimize | note     | Table does not support optimize, doing recreate + analyze instead       |
+----------------------+----------+----------+----------+
| db_name.tableN       | optimize | status   | OK       |
+----------------------+----------+----------+----------+

Во время выполнения на таблице InnoDB будут показываться проценты выполнения:
Stage: 1 of 2 'copy to tmp table'     58% of stage done
При выполнении оптимизации для таблиц типа InnoDB будет произведена фактически перезапись таблицы
и обновление ее метаданных.

2)
Для анализа возможного тюнинга MySQL модно использовать утилиту mysqltuner.
Скачиваем ее и запускаем:
# wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
# perl mysqltuner.pl
После запуска нужно будет вести логин и пароль root администратора базы MySQL
Утилита выдаст ряд рекомендаций, которые следует изучить и применить.

3) Параметры thread_cache_size и max_connections
Параметр max_connections определяет максимальное количество клиентов, которые подключаются к серверу одновременно.
Параметр thread_cache_size определяет размер кеша, содержащего соединения клиентов.
После закрытия соединения клиентом, соединение не закрывается сразу, а помещается в кеш
и при обращении к серверу соединение берется из кеша, при этом новое соединение не создается.
Этим достигается производительность.
Пример ненормальной ситуации:
# mysqladmin -u root -p extended-status | grep Threads
Enter password:
| Threads_cached                           | 0            |
| Threads_connected                        | 7            |
| Threads_created                          | 9375789      |
| Threads_running                          | 1            |
Пример нормальной ситуации
# mysqladmin -uroot -p extended-status | grep Threads
Enter password:
| Threads_cached                           | 25           |
| Threads_connected                        | 12           |
| Threads_created                          | 54           |
| Threads_running                          | 1            |
Количество клиентов в текущий момент времени показывается в параметре Threads_connected
Максимальное количество одновременных соединений, которое было за время работы сервереа mySQL:
# mysqladmin -uroot -p extended-status | grep connections
Enter password:
| Max_used_connections                     | 46           |
Установим thread_cache_size близкое к максимальному значению, которое показала база.
thread_cache_size = 35
max_connections = 100


4)
Основные векторы оптимизации в файле /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Отключение использование symbolic-links
symbolic-links=0
# Запрет обращения к DNS при каждом подклюючении клиента к серверу
skip-name-resolve
# Отключение моментального обновление статистики в INFORMATION_SCHEMA​
innodb_stats_on_metadata = OFF
#
##### Соединения с базой. Корректируем после анализа количества подключений к базе
thread_cache_size = 35
max_connections = 100
#
# Кодировка по умолчанию
collation_server=utf8_general_ci
character_set_server=utf8
#
# Таймауты
interactive_timeout=120
wait_timeout=120
#
##### Размер буфера для работы с таблицами InnoDB. Чем больше, тем лучше. Можно до 50% памяти сервера
innodb_buffer_pool_size = 3000M
##### Количество инстансов примерно равно количеству гигабайт выделенных под буфер innodb_buffer_pool_size
innodb_buffer_pool_instances = 3
# Размер Log файла
innodb_log_file_size = 375M
#
# Режим сброса данных на диск InnoDB. 2 - режим накопдения в течении пары секунд
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_file_per_table=1
#
# Размер кеша запросов. Отключен
query_cache_type=0
query_cache_size=0
query_cache_limit=0
#
#Рекомендованные значения для MyISAM таблиц
key_buffer_size=32M
#
# Максимальный размер памяти, выделяемой для временных таблиц
tmp_table_size=128M
max_heap_table_size=128M
# Максимальный размер данных, которые могут быть переданы за один запрос
max_allowed_packet=1G
# Количество символов, допустимых в объединении по команде CONCAT
group_concat_max_len = 16000
# Включение логирования медленных запросов и запросов не использующих индексы
slow_query_log  = 1
slow_query_log_file = /var/log/mariadb/mariadb-slow.log
log-queries-not-using-indexes


ПРИМЕЧАНИЕ:
При изменении параметра innodb_log_file_size остановите MySQL,
сделайте резервную копию файлов /var/lib/mysql/ib_logfile-n
измените значение параметра innodb_log_file_size и запустите MySQL. 

# mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0_backup
# mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1_backup