Оптимизация и тюнинг производительности MariaDB MySQL сервера внутри Docker

Пожалуй, это единственное актуальное и максимально полное руководство на русском языке по оптимизации MySQL сервера в docker-контейнерах. По сути большая часть советов отлично подойдёт и тем, кто не работает с Docker. Оптимизацию базы данных можно разделить на 3 слоя:

Оптимизация запросов, таблиц и индексов
Тюнинг параметров сервера баз данных
Оптимальная настройка сервера, операционной и файловой систем

В этой замете рассмотрим второй пункт: тюнинг параметров сервера баз данных. И конечно же первым советом будет не использовать Docker для контейнеризации MySQL и других хранилищ! Я серьёзно, если вы используете базу данных находящуюся в контейнере и беспокоитесь о тюнинге производительности, то первым же делом вынесите его на отдельный полноценный сервер. Однако, есть и преимущество при запуске MySQL в Docker: можно для каждого приложения оптимально сконфигурировать настройки.

Как это не удивительно, но официальный image от MariaDB для Docker уже сконфигурирован с некоторыми оптимизациями, в том числе и для работы в контейнерах. В файле /etc/mysql/my.cnf уже включено innodb_file_per_table = 1, а в конфиге /etc/mysql/conf.d/docker.cnf присутствуют сроки:

[mysqld]
skip-host-cache
skip-name-resolve

Установка и знакомство с MySQLTuner

MySQLTuner достаточно интересный и полезный инструмент для тюнинга и оптимизации таких серверов баз данных: MySQL 5.7, MySQL 5.6, MySQL 5.5, MariaDB 10.1, MariaDB 10.0, Percona Server 5.6, Percona XtraDB cluster. Также он частично поддерживает MySQL 3.23, 4.0, 4.1, 5.0, 5.1, но они помечены как deprecated. Приступим к подготовке к установке:

apt update
apt install wget nano -y

Установка MySQLTuner достаточно тривиальна:

cd /
wget https://github.com/major/MySQLTuner-perl/tarball/master
tar xf master
rm master
cd /major-MySQLTuner-perl-9cf48b5/

Чтобы каждый раз не вводить логин и пароль, можно создать специальный файл ~/.my.cnf содержащий данные администратора БД:

[client]
user=someusername
pass=thatuserspassword

./mysqltuner.pl --defaults-file=~/my.cnf

С настройками по-умолчанию отчёт будет выглядеть примерно так:

>> MySQLTuner 1.7.8 - Major Hayden <[email protected]>
 >> Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.2.10-MariaDB-10.2.10+maria~jessie
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/lib/mysql/ec3476d51dbf.err(0B)
[!!] Log file /var/lib/mysql/ec3476d51dbf.err doesn't exist
[!!] Log file /var/lib/mysql/ec3476d51dbf.err isn't readable.
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE +SPHINX
[--] Data in Aria tables: 3M (Tables: 1)
[--] Data in InnoDB tables: 45G (Tables: 108)
[!!] Total fragmented tables: 1
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'app@%' hasn't specific host restriction.
[--] There are 612 basic passwords in the list.
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 2d 17h 8m 9s (252K q [1.075 qps], 7K conn, TX: 88M, RX: 31M)
[--] Reads / Writes: 57% / 43%
[--] Binary logging is disabled
[--] Physical Memory : 31.0G
[--] Max MySQL memory : 1.3G
[--] Other process memory: 17.7M
[--] Total buffers: 616.0M global + 7.5M per thread (100 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 646.1M (2.04% of installed RAM)
[OK] Maximum possible memory usage: 1.3G (4.32% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/252K)
[OK] Highest usage of available connections: 4% (4/100)
[OK] Aborted connections: 0.26% (19/7425)
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 48.1% (192K cached / 399K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 23 sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 62% (167 on disk / 267 total)
[OK] Thread cache hit rate: 99% (11 created / 7K connections)
[OK] Table cache hit rate: 95% (123 open / 129 opened)
[OK] Open file limit used: 0% (29/65K)
[OK] Table locks acquired immediately: 100% (24 immediate / 24 locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 8 thread(s).
[--] Using default value is good enough for your version (10.2.10-MariaDB-10.2.10+maria~jessie)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/124.0K
[!!] Read Key buffer hit rate: 87.5% (16 cached / 2 reads)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 256.0M/45.3G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (37.5 %): 48.0M * 2/256.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 2 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 92.19% (1313501 hits/ 1424772 total)
[!!] InnoDB Write Log efficiency: 64.27% (35911 hits/ 55871 total)
[OK] InnoDB log waits: 0.00% (0 waits / 19960 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1.4M
[!!] Aria pagecache hit rate: 91.7% (2K cached / 167 reads)
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.
-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.
-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
 Run OPTIMIZE TABLE to defragment tables for better performance
 OPTIMIZE TABLE `app`.`log`; -- can free 9493 MB
 Total freed space after theses OPTIMIZE TABLE : 9493 Mb
 Restrict Host for user@% to user@SpecificDNSorIp
 When making adjustments, make tmp_table_size/max_heap_table_size equal
 Reduce your SELECT DISTINCT queries which have no LIMIT clause
 Performance should be activated for better diagnostics
 Read this before changing innodb_log_file_size and/or innodb_log_files_in_group: http://bit.ly/2wgkDvS
Variables to adjust:
 query_cache_size (=0)
 query_cache_type (=0)
 tmp_table_size (> 32M)
 max_heap_table_size (> 32M)
 performance_schema = ON enable PFS
 innodb_buffer_pool_size (>= 45G) if possible.

Установка MySQL Tuning Primer Script

Ещё один скрипт для автоматической проверки конфигурации MySQL-сервера, который так же даёт некоторые советы по оптимизации.

apt install bc net-tools -y
wget https://launchpadlibrarian.net/78745738/tuning-primer.sh
chmod +x tuning-primer.sh

./tuning-primer.sh

Результат работы скрипта примерно такой:

-- MYSQL PERFORMANCE TUNING PRIMER --
 - By: Matthew Montgomery -
MySQL Version 10.2.10-MariaDB-10.2.10+maria~jessie x86_64
Uptime = 1 days 6 hrs 28 min 15 sec
Avg. qps = 13
Total Questions = 1464526
Threads Connected = 79
Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations
To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/10.2/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service
SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10.000000 sec.
You have 0 out of 1464560 that take longer than 10.000000 sec. to complete
Your long_query_time seems to be fine
BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/10.2/en/point-in-time-recovery.html
WORKER THREADS
Current thread_cache_size = 100
Current threads_cached = 23
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine
MAX CONNECTIONS
Current max_connections = 100
Current threads_connected = 79
Historic max_used_connections = 101
The number of used connections is 101% of the configured maximum.
You should raise max_connections
No InnoDB Support Enabled!
MEMORY USAGE
Max Memory Ever Allocated : 8.93 G
Configured Max Per-thread Buffers : 753 M
Configured Max Global Buffers : 8.19 G
Configured Max Memory Limit : 8.93 G
Physical Memory : 30.96 G
Max memory limit seem to be within acceptable norms
KEY BUFFER
Current MyISAM index space = 124 K
Current key_buffer_size = 128 M
Key cache miss rate is 1 : 42
Key buffer free ratio = 81 %
Your key_buffer_size seems to be fine
QUERY CACHE
Query cache is enabled
Current query_cache_size = 64 M
Current query_cache_used = 23 M
Current query_cache_limit = 128 K
Current Query cache Memory fill ratio = 36.27 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size
SORT OPERATIONS
Current sort_buffer_size = 4 M
Current read_rnd_buffer_size = 1 M
Sort buffer seems to be fine
JOINS
./tuning-primer.sh: 401: local: 2097152: bad variable name
root@2cb99988447a:/# nano +943 tuning-primer.sh
root@2cb99988447a:/#
root@2cb99988447a:/#
root@2cb99988447a:/# ./tuning-primer.sh
-- MYSQL PERFORMANCE TUNING PRIMER --
 - By: Matthew Montgomery -
MySQL Version 10.2.10-MariaDB-10.2.10+maria~jessie x86_64
Uptime = 1 days 6 hrs 29 min 49 sec
Avg. qps = 13
Total Questions = 1478085
Threads Connected = 6
Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations
To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/10.2/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service
SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10.000000 sec.
You have 0 out of 1478106 that take longer than 10.000000 sec. to complete
Your long_query_time seems to be fine
BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/10.2/en/point-in-time-recovery.html
WORKER THREADS
Current thread_cache_size = 100
Current threads_cached = 96
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine
MAX CONNECTIONS
Current max_connections = 100
Current threads_connected = 6
Historic max_used_connections = 101
The number of used connections is 101% of the configured maximum.
You should raise max_connections
No InnoDB Support Enabled!
MEMORY USAGE
Max Memory Ever Allocated : 8.93 G
Configured Max Per-thread Buffers : 753 M
Configured Max Global Buffers : 8.19 G
Configured Max Memory Limit : 8.93 G
Physical Memory : 30.96 G
Max memory limit seem to be within acceptable norms
KEY BUFFER
Current MyISAM index space = 124 K
Current key_buffer_size = 128 M
Key cache miss rate is 1 : 43
Key buffer free ratio = 81 %
Your key_buffer_size seems to be fine
QUERY CACHE
Query cache is enabled
Current query_cache_size = 64 M
Current query_cache_used = 23 M
Current query_cache_limit = 128 K
Current Query cache Memory fill ratio = 36.27 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size
SORT OPERATIONS
Current sort_buffer_size = 4 M
Current read_rnd_buffer_size = 1 M
Sort buffer seems to be fine
JOINS
Current join_buffer_size = 260.00 K
You have had 2 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.
Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.
OPEN FILES LIMIT
Current open_files_limit = 65536 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine
TABLE CACHE
Current table_open_cache = 400 tables
Current table_definition_cache = 400 tables
You have a total of 192 tables
You have 400 open tables.
Current table_cache hit rate is 35%
, while 100% of your table cache is in use
You should probably increase your table_cache
TEMP TABLES
Current max_heap_table_size = 32 M
Current tmp_table_size = 32 M
Of 13212 temp tables, 2% were created on disk
Created disk tmp tables ratio seems fine
TABLE SCANS
Current read_buffer_size = 2 M
Current table scan ratio = 31 : 1
read_buffer_size seems to be fine
TABLE LOCKING
Current Lock Wait ratio = 0 : 1478289
Your table locking seems to be fine

Если в конце вывода вы видите что-то на подобии:

./tuning-primer.sh: 401: local: 2097152: bad variable name

то нужно вручную поправить скрипт, т.к. в нём до сих пор присутствует баг, который зарепортили ещё в 2013 году! Возникает он при использовании MariaDB, а не чистого MySQL сервера. Открываем файл uning-primer.sh на 943 строке:

nano +943 tuning-primer.sh

и заменяем

mysql_variable \'join_buffer%\' join_buffer_size

на:

mysql_variable \'join_buffer_size%\' join_buffer_size

После чего можно запускать скрипт повторно.

Первичная оптимизация MySQL сервера

Под первичной оптимизацией я подразумеваю тот тюнинг, который можно произвести зная только информацию о железе, по большей части объёме доступной оперативной памяти, а также о объёме и структуре данных. Фактически вся оптимизация сводится к двум действиям:

хранить все значимые данные и индексы в оперативной памяти
как можно меньше изменять данные на диске:
- все изменения писать в лог операций, который периодически накатывать на хранилище данных

Некоторые из «оптимизаций» могут быть не рекомендуемыми либо требующими особых условий эксплуатации, например, настроенной репликации.

Смотрим и изучаем отчёт. Какой можно сделать из этого вывод? Сразу же можно смотреть последние секции General recommendations и Variables to adjust, а так же все пункты с пометкой [!!]. Давайте начнём с конца.

Тюнинг innodb_buffer_pool_size в MySQL

Тюнер предлагает увеличить размер параметра innodb_buffer_pool_size до 45G и более. Давайте посмотрим текущее значением запросом:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Скорее всего его значение будет 268435456, т.е. 256 Мб, что очень мало для современных приложений, вот тюнер и предлагает увеличить его до 45 Гб. Но откуда он взял такую цифру? Всё просто, именно такой объём в данный момент занимают данные в InnoDB хранилище, о чём было указано выше:

[--] Data in InnoDB tables: 45G (Tables: 108)

Параметр innodb_buffer_pool_size отвечает за максимальный объём оперативной памяти, которая будет выделена для хранения данных и индексов InnoDB-таблиц. Фактически тюнер рекомендует выделить столько RAM, сколько занимают все данные. По хорошему к этому значению нужно добавить ещё 15-25%, т.к. размер базы данных со временем увеличивается. Однако, MySQLTuner не учитывает, что не все данные одинаково полезны, а некоторые и вовсе не нужны.

Узнать размер каждой конкретной таблицы можно с помощью запроса:

SELECT table_schema, table_name,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.tables ORDER BY data_length + index_length DESC;

В моём случае объём полезных данных приложения занимает порядка 3 Гб, всё остальное — логи, несущие только историческую ценность. Если брать с запасом, то 8 Гб должно хватить с хорошим запасом.

Как отредактировать конфиг MariaDB в Docker?

Конечно, ваше право, можно не заморачиваться и редактировать конфигурацию напрямую в файле /etc/mysql/my.cnf, однако, лучше потратить немного времени и вынести конфигурацию приложения в отдельный файл.

Проблема в том, что нельзя просто так взять и отредактировать конфиг внутри docker-контейнера. т.к. при его пересоздании все эти данные потеряются, то нужно прокидывать конфиг внутрь контейнера из постоянного хранилища. Создадим файл storage/mariadb/etc/mysql/conf.d/app.cnf с содержимым:

[mysqld]
innodb_buffer_pool_size = 8GB

Затем добавим этот файл как волюм в docker-compose.yml:

mariadb:
 volumes:
 - ./storage/mariadb/etc/mysql/conf.d/app.cnf:/etc/mysql/conf.d/app.cnf

После чего пересоберём и перезапустим контейнер с MySQL:

/usr/local/bin/docker-compose up -d --no-deps --build mariadb

И проверим:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Значение изменилось и в моём случае стало 8589934592, т.е. 8 Гб.

Настройка innodb_log_file_size в MySQL

Следующая по важности опция для оптимизации. MySQLTuner советует установить размер этого параметра равным 25% от buffer pool size, в моём случае 25% от 8Гб это 2 Гб. По-умолчанию он имеет размер 50 Мб:

SHOW VARIABLES LIKE 'innodb_log_file_size';

Этот параметр устанавливает размер лога операций и влияет на скорость записи данных на диск. Чем больше размер лога, тем быстрее будет происходить запись данных. MySQL имеет сразу 2 файла с логом, а опция влияет на размер каждого файла, т.е. установив значение 1 Гб выделится 2 Гб по одному на каждый лог. Есть и обратная сторона, чем больше файл с логом, тем больше времени система будет восстанавливаться во время сбоев т.к. будет много данных которые нужно применить из лога операций.

Собственно в файл storage/mariadb/etc/mysql/conf.d/app.cnf добавляем строку:

innodb_log_file_size = 1GB

И перезапускаем MySQL сервер, пересоздавать контейнер на этот раз не нужно:

/usr/local/bin/docker-compose restart mariadb

Настройка innodb_log_buffer_size

Параметр отвечает за размер буфера ещё незакомиченных транзакций. Значение стоит увеличивать если вы используете большие поля вроде BLOB или TEXT. По-умолчанию составляет 8 Мб, чего хватает для большинства приложений.

Тюнинг innodb_flush_log_at_trx_commit

Параметр innodb_flush_log_at_trx_commit определяет, как именно MySQL сервер будет писать в лог на диске данные о транзакциях и имеет три допустимых значения: 0, 1, 2. Тюнинг этого параметра повысит скорость записи в базу данных в десятки и сотни раз. По-умолчанию это значение установлено в значение 1, что даёт самые надежные гарантии сохранности данных, но и является при этом самым медленным режимом.

Если потерять даже 0.000000000001% записей для вашей БД критично — то оставляйте значение 1. Такая настройка будет идеальна для приложений работающих с деньгами или имуществом.

Если же небольшая потеря данных в экстремальных условиях не критична, то смело выставляйте innodb_flush_log_at_trx_commit в значение 2. В этом режиме транзакции будут сохраняться в кэш операционной системы, а запись лога на диск остаётся на совести ОС. Данные могут быть утеряны лишь в случае краха ОС и лишь за несколько секунд, что зависит от настроек операционной системы. Такой случай подойдёт для социальных сетей и прочих приложений, в которых пользователи совершают действия. Потеря нескольких лайков не окажет никакого влияния и скорее всего этого никто не заметит.

При значении равном 0 лог сбрасывается на диск один раз в секунду, вне зависимости от происходящих транзакций. Скорость записи возрастает до космических масштабов, но так же растёт и риск эти данные потерять. Данные могут быть утеряны как при крахе ОС, так и при крахеMySQL сервера и обычно не более, чем за 1-2 последних секунды. Этот режим идеально подойдёт для тех ситуаций, когда вы легко сможете восстановить данные, например из реплики. Либо вы работаете с API-сервисами и при потере данных сможете их перезапросить.

Изменяем конфиг storage/mariadb/etc/mysql/conf.d/app.cnf:

innodb_flush_log_at_trx_commit = 2

Рестартим:

/usr/local/bin/docker-compose restart mariadb

Проверяем:

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

Оптимизация innodb_doublewrite в MariaDB

Ещё одна интересная опция включенная по-умолчанию:

SHOW VARIABLES LIKE 'innodb_doublewrite';

Doublewrite представляет собой буфер двойной записи и используется в InnoDB чтобы изменённые страницы были записаны в файл данных. Позволяет избежать потери данных при внезапном сбое сервера. В этом режиме InnoDB перед записью страниц в основной файл данных предварительно записывает их в непрерывную область — doublewrite. Только после записи в этот буфер производится запись страниц на соответствующие позиции в файле данных. Если произошёл сбой операционной системы в процессе записи страницы, то при восстановлении InnoDB движок возьмёт копию страницы из буфера doublewrite.

Если на сервере используется файловая система ZFS, то буфер двойной записи можно смело отключать, т.к. у этой ФС есть свой механизм обеспечения целостности данных. В целом, хоть параметр и содержит в своём названии слово double, его отключение не ускоряет процесс записи в 2 раза. В среднем пользователи отмечают только 5-10% прирост производительности. Рисковать ли данными ради этого — решайте сами.

skip-innodb_doublewrite

или:

innodb_doublewrite = 0

В целом не рекомендуется отключать на продакшене при работе с ценными данными, т.к. в результате сбоя сервера повреждается файл с данными без возможности сделать repair. В случае повреждения файлы спасёт только бэкап или реплика.

Тюнинг с помощью уровней изоляции транзакций

По-умолчанию уровень изоляции транзакций выставлен в REPEATABLE-READ. Сильнее только SERIALIZABLE. А что, если понизить его до READ COMMITTED? Для некоторых приложений это позволит ещё немного уменьшить время на выполнение запросов. Однако, нужно быть уверенным, что смена уровня изоляции не нарушит консистентность данных в приложении. В некоторых ситуациях можно вообще перейти на самый низкий уровень изоляции — READ UNCOMMITED. Например, во время обслуживания базы данных: загрузки дампов, и т.п.

SHOW VARIABLES LIKE 'tx_isolation';

transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED
                         | REPEATABLE-READ | SERIALIZABLE}

Можно менять уровень изоляции для отдельно взятой сессии или нового соединения таким образом:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

При использовании ключевого слова SESSION устанавливается уровень изоляции по-умолчанию для всех будущих транзакций, выполняемых в текущем соединении.

По-умолчанию уровень изоляции устанавливается для следующей (ещё не начальной) транзакции. При использовании ключевого слова GLOBAL устанавливается уровень изоляции по-умолчанию глобально для всех новых соединений. Чтобы изменить изоляцию глобально необходима привилегия SUPER.

Настройка query_cache_size в MySQL

Параметр определяет объём оперативной памяти выделяемый сервером под кэш запросов. На практике этот механизм работает не очень эффективно, т.к. кэш запросов для таблицы очищается каждый раз, когда в таблице проиcходят вставка или изменение строк. Такой подход может оказаться неэффективным для приложений с большим количеством запросов на изменение таблиц. Это приводит к тому, что таблицы блокируются в режиме Waiting for query cache lock.

Изменяем конфиг storage/mariadb/etc/mysql/conf.d/app.cnf:

query_cache_size = 0

Рестартим:

/usr/local/bin/docker-compose restart mariadb

Проверяем:

SHOW VARIABLES LIKE 'query_cache_size';

Если кэш запросов всё же включен, то можно посмотреть его статистику с помощью запроса:

SHOW STATUS LIKE 'Qcache%';

В более удобном виде эту информацию выдаёт MySQL Tuning Primer Script:

QUERY CACHE
Query cache is enabled
Current query_cache_size = 64 M
Current query_cache_used = 11 M
Current query_cache_limit = 128 K
Current Query cache Memory fill ratio = 17.70 %
Current query_cache_min_res_unit = 4 K
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
MySQL won't cache query results that are larger than query_cache_limit in size

В моём случае под кэш запросов по-умолчанию выделилось 64 Мб, однако используется всего 11 Мб.

Тюнинг max_heap_table_size и tmp_table_size

Чаще всего эти два параметра настраиваются вместе и устанавливаются в одно и то же значение. Параметр max_heap_table_size отвечает за максимально допустимый размер таблицы типа MEMORY хранящейся в оперативной памяти. Значение по умолчанию 32 Мб, если ваше приложение не использует MEMORY таблицы, то установите это значение равным tmp_table_size.

Параметр tmp_table_size отвечает за максимальный размер оперативной памяти выделяемой для временных служебных таблиц. Это значение также зависит от значения max_heap_table_size, и в итоге будет выбрано минимальное значение между max_heap_table_size и tmp_table_size, а остальные временные таблицы будут создаваться на диске. Значение по-умолчанию так же равняется 32 Мб.

Необходимые значения сильно зависят от ваших данных, от методов их обработки, от количества клиентов и частоты выполнения сложных запросов. Попробуйте экспериментальным способом найти значения, при которых запросы не будут создавать временных файлов на диске. Именно создание временных таблиц на диске сильнее всего тормозит сложные SELECT запросы на сортировках и группировках.

tmp_table_size = 64M
max_heap_table_size = 64M

А если позволяют ресурсы, то можно и:

tmp_table_size = 2048M
max_heap_table_size = 2048M

Параметры wait_timeout и interactive_timeout

В параметре interactive_timeout указано время в секундах, отвечающих за ожидание активности со стороны интерактивного соединения (использующего флаг CLIENT_INTERACTIVE), прежде чем закрыть его.

Значение wait_timeout содержит количество секунд, в течение которых сервер ожидает запросов, прежде чем прервать соединение. Т.е. если от клиента за указанный интервал не поступало запросов, то сервер принудительно закрывает соединение, чтобы освободить его для других клиентов.

SHOW VARIABLES LIKE 'wait_timeout';
SHOW VARIABLES LIKE 'interactive_timeout';

По-умолчанию оба параметра установлены в 28 800 секунд, что составляет 8 часов. Далеко не каждое приложение может похвастаться таким временем жизни запущенного скрипта. Для большинства приложений с запасом хватит и 30 секунд. Для веб-сайтов вряд ли имеет смысл выставлять это значение больше 3-5 секунд.

wait_timeout = 5
interactive_timeout = 5

Вторичная оптимизация конфига MySQL

Под вторичной оптимизацией я подразумеваю тот тюнинг, который можно произвести только зная профиль нагрузки на БД: соотношение операций чтения и записи, долгие запросы и т.п.

Тюнинг performance_schema в MySQL

Опция performance_schema производит мониторинг всей БД, на что расходуется некоторая часть ресурсов, держать эту опцию постоянно включенной в продакшене крайне не рекомендуется, т.к. может замедлять время выполнения запросов до 25%. Объем потребляемых ресурсов зависит от конфигурации схемы, которую можно посмотреть выполнив запрос:

SHOW VARIABLES LIKE 'performance%';

А если выполнить от имени администратора БД этот запрос:

SHOW ENGINE performance_schema STATUS;

С помощью этого запроса можно понять все ли данные мониторятся, или что-то пропадает:

SHOW STATUS LIKE 'performance%';

Если какой-то счетчик оказался выше ноля, то нужно увеличить соответствующий параметр.

Именно на данных из performance_schema и основана вся фишка MySQLTuner! Чем дольше собираются данные, тем точнее будут рекомендации по оптимизации MySQL и MariaDB. Стоит учесть, что данные performance_schema обнуляются после каждой перезагрузки сервера, поэтому сначала лучше выполнить первичную конфигурацию, после чего оставить сервер под боевой нагрузкой на сутки для последующего анализа.

Работа с данными performance_schema

Переходим в базу данных performance_schema :

USE performance_schema;

И смотрим какие таблицы здесь есть:

SHOW TABLES;

Обратим внимание на наблицы с префиксом setup_, например:

SELECT * FROM setup_consumers;
SELECT * FROM setup_instruments;

В них содержатся настройки того, что будет мониториться. С помощью UPDATE можно менять значение колонки ENABLED с NO на YES и наоборот.

Самые горячие таблицы

С помощью этого запроса можно узнать к каким таблицам происходит наибольшее число чтений и записей:

select substring_index(file_name, '/', -1) file_name, event_name, count_read, count_write from file_summary_by_instance where COUNT_READ+COUNT_WRITE > 0 order by COUNT_READ+COUNT_WRITE desc limit 30;

А этим запросом можно узнать статистику по блокировкам:

select event_name, source, sum(timer_wait) timer_wait from events_waits_history_long where event_name not like 'wait/io/file%' group by event_name, source order by 3 desc limit 30;

Тюнинг MySQL для самых маленьких

Если вы впервые сталкиваетесь с оптимизацией сервера MySQL, то эти пункты помогут встать на истиный путь:

Переходите на InnoDB
Используйте индексы
Используйте персистентные соединения в приложении
Включите опцию innodb_file_per_table = 1, если она ещё не была включена.
Анализируйте нагрузку в режиме реального времени с помощью утилит mytop или mtop.
Отключение резолвинга доменных имён в ip-адреса позволит поднять производительность до 20%. Просто добавьте в конфиг опцию skip-name-resolve.
Если приложение и сервер базы данных находятся на одном сервере — используйте socket-соединение, а не TCP. Это позволит сократить время ответа до 30%. Добавьте в конфиг socket=/tmp/mysql.sock и skip-networking.
Если в вашем приложении преобладают запросы SELECT — 90% и более запросов, то попробуйте включить опцию low-priority-updates для повышения приоритета select запросов.
Включите логгирование медленных запросов:
log_error=/var/log/mysql/error.log
#log_slow_queries = /var/log/mysql/slow.log
slow_query_log_file = /var/log/mysql/slow.log
slow_query_log = ON
long_query_time = 5
log-queries-not-using-indexes