Оптимизация и тюнинг производительности mariadb mysql сервера внутри docker

Как ускорить чтение

Допустим, диски загружены запросами на чтение. Что можно сделать, чтобы ускорить отдачу данных? Закэшировать данные в памяти. MySQL предоставляет возможность использования разных хранилищ, или движков (storage engines), для доступа к данным, поэтому подход к кэшированию разный. Рассмотрим два наиболее популярных движка: MyISAM и InnoDB.

Движок InnoDB имеет встроенный кэш для данных и индексов — так называемый Buffer Pool. Его размер регулируется переменной innodb_buffer_pool_size. В идеале размер Buffer Pool должен быть как минимум такого объёма, чтобы в нём полностью можно было разместить все данные и индексы плюс 30%-60% от их размера. Дополнительная память используется для служебных нужд и Insert Buffer, а также для обеспечения запаса памяти на будущее. Переменная innodb_buffer_pool_size — не динамическая, поэтому после её изменения в конфигурационном файле потребуется перезапуск MySQL.

Движок MyISAM не имеет кэша для данных. Но мы по-прежнему можем ускорить чтения из таблиц MyISAM. Дело в том, что ядро Linux кэширует все прочитанные файлы в области оперативной памяти, которая называется pagecache. Разумеется, файлы с таблицами MyISAM также попадают в этот кэш. Объём pagecache можно узнать из вывода команды free:

$ free -m
             total       used       free     shared    buffers     cached
Mem:        257934     255969       1964          0       4354     157772
-/+ buffers/cache:      93841     164092
Swap:            0          0          0
$

Максимальной производительности чтения можно добиться, если объём pagecache равен объёму данных MyISAM.

По умолчанию под pagecache выделяется почти вся незанятая процессами память, поэтому увеличить его объём можно лишь установкой дополнительных планок RAM. Однако память — недорогой по сравнению с ЦПУ и дисками ресурс, при этом эффект от увеличения кэша может привести к значительному увеличению производительности. Ниже представлен график %iowait — доли времени, в течение которого ЦПУ ожидает ввода/вывода. График снят с рабочего нагруженного сервера. Думаю, комментарии здесь излишни.

Исходные данные для настройки

Итак рассматриваем систему с установленным ISP manager на котором стоит Centos и MariaDB. Задача, оптимизировать работу Mysql и ускорить тем самым обработку запросов на сайтах. Для начала я приведу, пример своего my.cnf который находится по адресу etc/my.cnf, если у вас стоит Debian то смотреть надо в папке другой. Итак вот так выглядит настроенный файл, но иногда я все таки еще изменяю некоторые настройки, о которых расскажу ниже.

PHP

#open_files_limit = 2000
local-infile=0
innodb_file_per_table = 1
pid-file = /var/run/mysqld/mysqld.pid
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
ignore-db-dir=lost+found
max_allowed_packet = 1024M
skip-external-locking
skip-name-resolve
key_buffer = 2G

key_cache_division_limit = 70
thread_stack = 192K
tmp_table_size = 2G
max_heap_table_size = 2G
key_buffer_size = 4G
sort_buffer_size = 1G
read_buffer_size = 1G
read_rnd_buffer_size = 2G

myisam-recover = BACKUP
max_connections = 500
table-cache = 120000
table-open-cache = 120000

thread-cache-size = 500
thread-cache-size = 500
interactive-timeout = 360
query_cache_limit = 12M
query_cache_size = 4G
join_buffer_size = 512M

#log_slow_queries = /var/log/mysql/mysql-slow.log

expire_logs_days = 10
max_binlog_size = 100M
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
symbolic-links=0
bind-address = 127.0.0.1

log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

!includedir /etc/my.cnf.d

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

mysqld

#open_files_limit = 2000

local-infile=

innodb_file_per_table=1

pid-file=varrunmysqldmysqld.pid

datadir=varlibmysql

socket=varlibmysqlmysql.sock

ignore-db-dir=lost+found

max_allowed_packet=1024M

skip-external-locking

skip-name-resolve

key_buffer=2G

key_cache_division_limit=70

thread_stack=192K

tmp_table_size=2G

max_heap_table_size=2G

key_buffer_size=4G

sort_buffer_size =1G

read_buffer_size =1G

read_rnd_buffer_size=2G

myisam-recover=BACKUP

max_connections=500

table-cache=120000

table-open-cache=120000

thread-cache-size=500

interactive-timeout=360

query_cache_limit=12M

query_cache_size =4G

join_buffer_size=512M

#log_slow_queries = /var/log/mysql/mysql-slow.log

expire_logs_days=10

max_binlog_size =100M

innodb_buffer_pool_size=4G

innodb_buffer_pool_instances=4

innodb_flush_log_at_trx_commit=2

innodb_flush_method=O_DIRECT

symbolic-links=

bind-address=127.0.0.1

mysqld_safe

log-error=varlogmariadbmariadb.log

pid-file=varrunmariadbmariadb.pid

!includediretcmy.cnf.d

Approach 1. Rule of Thumb Method

The most commonly followed practice is to set this value at 70% – 80% of the system RAM. Though it works well in most cases, this method may not be optimal in all configurations. Let’s take the example of a system with 192GB of RAM. Based on the above method, we arrive at about 150GB for the buffer pool size. However, this isn’t really an optimal number as it does not fully leverage the large RAM size that’s available in the system, and leaves behind about 40GB of memory. This difference can be even more significant as we move to systems with larger configurations where we should be utilizing the available RAM to a greater extent.

Динамическое изменение размера innodb_buffer_pool_size в MySQL

Начиная с версии MySQL 5.7.5 допускается динамическое изменение размера innodb_buffer_pool_size (без перезапуска MySQL).

Как это правильно сделать — читаем ниже.

Исходные данные: Oracle MySQL 5.7.29Задача: Изменить размер innodb_buffer_pool_size с 64M до 128M

Начиная с версии MySQL 5.7.5 допускается динамическое изменение размера innodb_buffer_pool_size (без перезапуска MySQL), однако необходимо помнить, что размер innodb_buffer_pool_size должен быть пропорционален innodb_buffer_pool_chunk_size

Подключимся к MySQL под root и запросим текущий размер innodb_buffer_pool_size:

mysql> SELECT @@innodb_buffer_pool_size/1024/1024 AS 'InnoDB Buffer Pool Size in MByte';
+----------------------------------+
| InnoDB Buffer Pool Size in MByte |
+----------------------------------+
|                      64.00000000 |
+----------------------------------+
1 row in set (0.00 sec)

Размер innodb_buffer_pool_size у нас 64 МБайт.

Теперь запросим размер innodb_buffer_pool_chunk_size:

mysql> SELECT @@innodb_buffer_pool_chunk_size/1024/1024 AS 'InnoDB Buffer Pool Chunk Size in MByte';
+----------------------------------------+
| InnoDB Buffer Pool Chunk Size in MByte |
+----------------------------------------+
|                            64.00000000 |
+----------------------------------------+
1 row in set (0.00 sec)

Получили значение 64 МБайт.

Нам требуется динамически установить размер innodb_buffer_pool_size в 128M, соответственно 128M / 64M = 2, установим значение:

SET GLOBAL innodb_buffer_pool_size = (SELECT @@innodb_buffer_pool_chunk_size) * 2;

Если например нам потребуется установить размер innodb_buffer_pool_size в 1G, то расчет будет таким: 1024M / 64M = 16

Проверим для начала лог error.log, в нем должен отобразиться процесс увеличения innodb_buffer_pool_size, пример:

2020-03-08T16:34:08.748349Z 17665  InnoDB: Requested to resize buffer pool. (new size: 134217728 bytes)
2020-03-08T16:34:08.748346Z 0  InnoDB: Resizing buffer pool from 67108864 to 134217728 (unit=67108864).
2020-03-08T16:34:08.751825Z 0  InnoDB: Disabling adaptive hash index.
2020-03-08T16:34:08.758286Z 0  InnoDB: disabled adaptive hash index.
2020-03-08T16:34:08.759495Z 0  InnoDB: Withdrawing blocks to be shrunken.
2020-03-08T16:34:08.760689Z 0  InnoDB: Latching whole of buffer pool.
2020-03-08T16:34:08.761892Z 0  InnoDB: buffer pool 0 : resizing with chunks 1 to 2.
2020-03-08T16:34:08.767128Z 0  InnoDB: buffer pool 0 : 1 chunks (4096 blocks) were added.
2020-03-08T16:34:08.768977Z 0  InnoDB: Completed to resize buffer pool from 67108864 to 134217728.
2020-03-08T16:34:08.771059Z 0  InnoDB: Re-enabled adaptive hash index.
2020-03-08T16:34:08.773007Z 0  InnoDB: Completed resizing buffer pool at 200308 21:34:08.

Еще раз запросим размер innodb_buffer_pool_size:

mysql> SELECT @@innodb_buffer_pool_size/1024/1024 AS 'InnoDB Buffer Pool Size in MByte';
+----------------------------------+
| InnoDB Buffer Pool Size in MByte |
+----------------------------------+
|                     128.00000000 |
+----------------------------------+
1 row in set (0.00 sec)

Мы видим, что динамическое увеличение размера innodb_buffer_pool_size прошло успешно.

На этом все, до скорых встреч. Если у Вас возникли вопросы или Вы хотите чтобы я помог Вам, то Вы всегда можете связаться со мной разными доступными способами.

Информация о действующих параметрах

Для просмотра всех возможных параметров и настойках по умолчанию можно посмотреть выполнив команду:

mysqld --verbose --help
= часть вывода команды с пояснениями =
!!! в консоли у меня не показывается первая часть вывода и как её увидеть я сказу ниже!!!
= в верхней части вы увидите где находится файл настойки =
mysqld Ver 10.3.12-MariaDB-log for Linux on x86_64 (MariaDB Server)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Starts the MariaDB database server.

Usage: mysqld 

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

= в этой части увидите вывод всех возможных параметров = 
--thread-handling=name 
                      Define threads usage for handling queries. One of: 
                      one-thread-per-connection, no-threads, pool-of-threads
  --thread-pool-idle-timeout=# 
                      Timeout in seconds for an idle thread in the thread
                      pool.Worker thread will be shut down after timeout
  --thread-pool-max-threads=# 
                      Maximum allowed number of worker threads in the thread
                      pool

= в этой части параметры которые используются по умолчанию =
Variables (--variable-name=value)
and boolean options {FALSE|TRUE}                           Value (after reading options)
---------------------------------------------------------- ---------------
allow-suspicious-udfs                                      FALSE
alter-algorithm                                            DEFAULT
aria                                                       ON
aria-block-size                                            8192
aria-checkpoint-interval                                   30
aria-checkpoint-log-activity                               1048576
aria-encrypt-tables                                        FALSE
aria-force-start-after-recovery-failures                   0
aria-group-commit                                          none
aria-group-commit-interval                                 0
aria-log-dir-path                                          /var/lib/mysql/
aria-log-file-size                                         1073741824
aria-log-purge-type                                        immediate
= в конце информация о том как посмотреть текущие параметры =

To see what values a running MySQL server is using, type
'mysqladmin variables' instead of 'mysqld --verbose --help'.

перевод
Чтобы увидеть, какие значения использует работающий сервер MySQL, введите 'mysqladmin variables' вместо 'mysqld --verbose --help'.

Вся информация в консоли не покажется поэтому лучше вывод сделать в файл:

 mysqld --verbose --help > mysqld--verbose--help.txt

Какие значения использует работающий сервер тоже лучше вывести в файл:

mysqladmin variables -u root -p > mysqladmin-variables.txt
Enter password:

innodb_old_blocks_pct and innodb_old_blocks_time

The default 37% reserved for the old list can be adjusted by changing the value of . It can accept anything between between 5% and 95%.

The variable specifies the delay before a block can be moved from the old to the new sublist. means no delay, while the default has been set to .

Before changing either of these values from their defaults, make sure you understand the impact and how your system currently uses the buffer. Their main reason for existence is to reduce the impact of full table scans, which are usually infrequent, but large, and previously could clear everything from the buffer. Setting a non-zero delay could help in situations where full table scans are performed in quick succession.

Temporarily changing these values can also be useful to avoid the negative impact of a full table scan, as explained in .

Overview

In MariaDB Enterprise Server, the InnoDB storage engine uses the Buffer Pool as an in-memory cache. The Buffer Pool caches pages that were recently accessed. If a lot of pages are being accessed sequentially, the Buffer Pool also preemptively caches nearby pages. Pages are evicted using a least recently used (LRU) algorithm.

The contents of the Buffer Pool can be reloaded at startup, so that InnoDB does not have to function with a «cold» cache after a restart. To support this, the page numbers of all pages in the Buffer Pool can be dumped at shutdown. During startup, the page numbers are read from the dump, and InnoDB uses the page numbers to reload each page from its corresponding data file.

The size of each page in the Buffer Pool depends on the value of the innodb_page_size system variable.

In versions up to MariaDB Enterprise Server 10.4 and MariaDB Community Server 10.4, the Buffer Pool is divided into multiple instances. The number of instances is configured by the innodb_buffer_pool_instances system variable. Starting with ES 10.5 and CS 10.5, the Buffer Pool always has a single instance.

For additional information, see «InnoDB Buffer Pool».

Results on SATA SSD

Let’s see what results I’ve got individually for each of innodb_buffer_pool_instances:

innodb_buffer_pool_instances=1

innodb_buffer_pool_instances=2

innodb_buffer_pool_instances=4

innodb_buffer_pool_instances=8

innodb_buffer_pool_instances=16

innodb_buffer_pool_instances=32

innodb_buffer_pool_instances=64

What seems apparent is that as we increase innodb_buffer_pool_instances, it has a positive effect on variation in the throughput. We can condense the results in a single chart to see it closely:

And if we want to compare the throughput and deviation, let’s compare the results for the last 2500 sec:

So actually, innodb_buffer_pool_instances=64 showed the best throughput and less variability. Recommended innodb_buffer_pool_instances=8 seems better compared to 1-4 values in a sense of variability, but it does not produce the best throughput.

Новые изменения настройки my.cnf в 2021 году

Времена идут, знания становятся лучше, поэтому я уже практически во многом перенастроил свои файлы конфигов базы данных. Сразу скажу, что в основном этот конфиг рассчитан по моим базам данных, это порядка 14 гигабайт данных на серверах с 32 памяти оперативной, ssd дисками и собственно выкручено все на соотношение скорость работы + стабильность. Поэтому вот такой конфиг сейчас использую на Centos 8 с указанными параметрами серверов.

PHP

collation-server = utf8_general_ci
character-set-server = utf8
local-infile=0
innodb_file_per_table = 1
skip-log-bin = true
symbolic-links=0
skip-external-locking
skip-name-resolve
max-connect-errors = 1000
low-priority-updates=1
max_allowed_packet = 32M
open_files_limit = 165536
key_buffer_size = 512m
max_connections = 150
thread-cache-size = 150
wait_timeout = 90
interactive_timeout = 90
innodb_buffer_pool_instances = 16
innodb_buffer_pool_size = 16G
innodb_thread_concurrency = 32
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb-log-files-in-group = 2
innodb_log_file_size = 2g
innodb_log_buffer_size = 16M

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

collation-server=utf8_general_ci

character-set-server=utf8

local-infile=

innodb_file_per_table=1

skip-log-bin=true

symbolic-links=

skip-external-locking

skip-name-resolve

max-connect-errors=1000

low-priority-updates=1

max_allowed_packet=32M

open_files_limit=165536

key_buffer_size=512m

max_connections=150

thread-cache-size=150

wait_timeout=90

interactive_timeout=90

innodb_buffer_pool_instances=16

innodb_buffer_pool_size=16G

innodb_thread_concurrency=32

innodb_flush_log_at_trx_commit=

innodb_flush_method=O_DIRECT

innodb-log-files-in-group=2

innodb_log_file_size=2g

innodb_log_buffer_size=16M

В принципе производительность и уровень работы меня устраивает. Если у вас есть вопросы или конфигурация сервера другая, желательно написать все таки вопрос, я помогу разобраться какие параметры следует учесть. Из основного: сделан упор на работу Innodb + конфиги учитывают максимально настройки устраняющие узкие места в работе базы данных.

Percona Server for MySQL 5.7.x and 8.0.x

innodb_cleaner_lsn_age_factor

Percona Server for MySQL has a different default adaptive flushing algorithm, high_checkpoint, which essentially allows more dirty pages. This behavior is controlled by the variable . You can restore the default MySQL algorithm by setting the variable to legacy. Since you should aim to have as many dirty pages as possible without running into flush storms and stalls, the high_checkpoint algorithm will help you. If you have extreme bursts of checkpoint age, maybe the legacy algorithm would fare better. For more information on this topic, see our previous post.

innodb_empty_free_list_algorithm

This variable controls the behavior of InnoDB when it struggles at finding free pages in a buffer pool instance. It has two possible values: legacy and backoff. It is only meaningful if you often have “close to 0 in “show engine innodb status\G”. In such a case, the LRU manager thread may hammer the free list mutex and cause contention with other threads. When set to backoff, the thread will sleep for some time after a failure to find free pages to lower the contention. The default is backoff and normally it should be fine in most cases.

A rule of thumb

However, for the sake of argument, let’s say the 80% rule is a starting point. A rule of thumb to help us get a quick tuning number to get the server running. Assuming we don’t know anything really about the workload on the system yet, but we know that the system is dedicated to InnoDB, how might our 80% rule play out?

Total Server RAM	Buffer pool with 80% rule	Remaining RAM
1G	800MB	200MB
16G	13G	3G
32G	26G	6G
64G	51G	13G
128G	102G	26G
256G	205G	51G
512G	409G	103G
1024G	819G	205G

At lower numbers, our 80% rule looks pretty reasonable. However, as we get into large servers, it starts to seem less sane. For the rule to hold true, it must mean that workload memory consumption increases in proportion to the needed size of the buffer pool, but that usually isn’t the case. Our server that has 1TB of RAM likely doesn’t need 205G of that to handle things like connections and queries (likely MySQL couldn’t handle that many active connections and queries anyway).

So, if you really just spent all that money on a beefy server do you really want to pay a 20% tax on that resource because of this rule of thumb?

Interesting scenarios

Increasing size in the config file

Let’s suppose one day you get up willing to change or tune some variables in your server, and you decide that as you have free memory you will increase the buffer pool. In this example, we are going to use a server with
innodb_buffer_pool_instances=16 and 2GB of buffer pool size which will be increased to 2.5GB

So, we set in the configuration file:

Shell

innodb_buffer_pool_size = 2684354560

1	innodb_buffer_pool_size=2684354560

But then after a restart, we found:

Shell

1
2
3
4
5
6
7
8

mysql>show global variables like’innodb_buffer_pool_%size’;

+——————————-+————+

|Variable_name|Value|

+——————————-+————+

|innodb_buffer_pool_chunk_size|134217728|

|innodb_buffer_pool_size|4294967296|

+——————————-+————+

2rows inset(0.00sec)

And the error log says:

Shell

2018-05-02T21:52:43.568054Z 0 InnoDB: Initializing buffer pool, total size = 4G, instances = 16, chunk size = 128M

1	2018-05-02T215243.568054ZNoteInnoDBInitializing buffer pool,total size=4G,instances=16,chunk size=128M

So, after we have set innodb_buffer_pool_size in the config file to 2.5GB, the database gives us a 4GB buffer pool, because of the number of instances and the chunk size. What the message doesn’t tell us is the number of chunks, and this would be useful to understand why such a huge difference.

Let’s take a look at how that’s calculated.

Conclusion

There are other variables impacting InnoDB writes, but these are among the most important ones. As a general rule, don’t over-tune your database server and try to change one setting at a time.

This is our understanding of the InnoDB variables affecting the flushing. The findings are backed by the pretty unique exposure we have at Percona to a wide variety of use cases. We also spend many hours reading the code to understand the inner moving parts of InnoDB. As usual, we are open to comments but if possible, try to back any argument against our views with either a reference to the code or a reproducible use case.

Reducing the buffer pool

Let’s start reducing the buffer pool:

Shell

| innodb_buffer_pool_size | 2147483648 |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_chunk_size | 134217728 |

mysql> set global innodb_buffer_pool_size=1073741824;
Query OK, 0 rows affected (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14

|innodb_buffer_pool_size|2147483648|

|innodb_buffer_pool_instances|8|

|innodb_buffer_pool_chunk_size|134217728|

mysql>set global innodb_buffer_pool_size=1073741824;

Query OK,rows affected(0.00sec)

mysql>show global variables like’innodb_buffer_pool_size’;

+————————-+————+

|Variable_name|Value|

+————————-+————+

|innodb_buffer_pool_size|1073741824|

+————————-+————+

1row inset(0.00sec)

If we try to decrease it to 1.5GB, the buffer pool will not change and a warning will be showed:

Shell

mysql> set global innodb_buffer_pool_size=1610612736;
Query OK, 0 rows affected, 1 warning (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

mysql>set global innodb_buffer_pool_size=1610612736;

Query OK,rows affected,1warning(0.00sec)

mysql>show warnings;

+———+——+———————————————————————————+

|Level|Code|Message|

+———+——+———————————————————————————+

|Warning|1210|InnoDBCannot resize buffer pool tolesser than chunk size of134217728bytes.|

+———+——+———————————————————————————+

1row inset(0.00sec)

mysql>show global variables like’innodb_buffer_pool_size’;

+————————-+————+

|Variable_name|Value|

+————————-+————+

|innodb_buffer_pool_size|2147483648|

+————————-+————+

1row inset(0.01sec)

What uses the memory on your server?

Before we question such advice, let’s consider what can take up RAM in a typical MySQL server in their broad categories. This list isn’t necessarily complete, but I think it outlines the large areas a MySQL server could consume memory.

OS Usage: Kernel, running processes, filesystem cache, etc.
MySQL fixed usage: query cache, InnoDB buffer pool size, mysqld rss, etc.
MySQL workload based usage: connections, per-query buffers (join buffer, sort buffer, etc.)
MySQL replication usage: binary log cache, replication connections, Galera gcache and cert index, etc.
Any other services on the same server: Web server, caching server, cronjobs, etc.

There’s no question that for tuning InnoDB, the innodb_buffer_pool_size is the most important variable. It’s expected to occupy most of the RAM on a dedicated MySQL/Innodb server, but of course, other local services may affect how it is tuned. If it (and other memory consumption on the server) is too large, swapping can kick in and degrade your performance rapidly.

Further, the workload of the MySQL server itself may cause a lot of variation. Does the server have a lot of open connections and active query workload consuming memory? The memory consumption caused by this can be dramatically different server to server.

Finally, replication mechanisms like Galera have their own memory usage pattern and can require some adjustments to your buffer pool.

We can see clearly that the 80% rule isn’t as nuanced as reality.