Оптимальная настройка mysql

Another Algorithm

This will set the main cache settings to the minimum; it could be important to systems with lots of other processes and/or RAM is 2GB or smaller.

Do SHOW TABLE STATUS for all the tables in all the databases.

Add up Index_length for all the MyISAM tables. Set no larger than that size.

Add up Data_length + Index_length for all the InnoDB tables. Set to no more than 110% of that total.

If that leads to swapping, cut both settings back. Suggest cutting them down proportionately.

Run this to see the values for your system. (If you have a lot of tables, it can take minute(s).)

SELECT  ENGINE,
        ROUND(SUM(data_length) 10241024, 1) AS "Data MB",
        ROUND(SUM(index_length)10241024, 1) AS "Index MB",
        ROUND(SUM(data_length + index_length)10241024, 1) AS "Total MB",
        COUNT(*) "Num Tables"
    FROM  INFORMATION_SCHEMA.TABLES
    WHERE  table_schema not in ("information_schema", "PERFORMANCE_SCHEMA", "SYS_SCHEMA", "ndbinfo")
    GROUP BY  ENGINE;

Тюнинг базы данных Mysql варианты

Итак что я меняю и что вижу при этом. Для начала выведу основные параметры которые считаю спорными в настройке.

PHP

key_buffer = 2G
key_cache_division_limit = 70
thread_stack = 192K
tmp_table_size = 1G
max_heap_table_size = 1G
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

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

key_buffer=2G key_cache_division_limit=70 thread_stack=192K tmp_table_size=1G max_heap_table_size=1G 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

How to Set Variables

In the text file my.cnf (my.ini on Windows), add or modify a line to say something like

= 5G

That is, VARIABLE name, «=», and a value. Some abbreviations are allowed, such as M for million (1048576), G for billion.

For the server to see it, the settings must be in the «» section of the file.

The settings in my.cnf or my.ini will not take effect until you restart the server.

Most settings can be changed on the live system by connecting as user root (or other user with SUPER privilege) and doing something like

SET @@global.key_buffer_size = 77000000;

Note: No M or G suffix is allowed here.

To see the setting a global VARIABLE do something like

SHOW GLOBAL VARIABLES LIKE "key_buffer_size";
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| key_buffer_size | 76996608 |
+-----------------+----------+

Note that this particular setting was rounded down to some multiple that MariaDB liked.

You may want to do both (SET, and modify my.cnf) in order to make the change immediately and have it so that the next restart (for whatever reason) will again get the value.

64-битные ОС и MariaDB

Только MyISAM: : использовать около 20% ОЗУ. Установите (в my.cnf / my.ini) = 0.

Только InnoDB: = 70% ОЗУ. Если у вас много оперативной памяти и вы используете 5.5 (или новее), рассмотрите возможность создания нескольких пулов. Рекомендуется 1–16 , каждый размером не менее 1 ГБ. (К сожалению, нет данных о том, насколько это поможет; вероятно, не очень.)

Между тем, установите = 20M (крошечный, но ненулевой)

Если у вас есть смесь двигателей,опустите оба числа.

В 5.6 (или MariaDB 5.5 ) необязательный пул потоков взаимодействует с . Это более сложная тема.

Редко случается переполнение стопки нитей.Если это произойдет,сделайте что-нибудь вроде thread_stack=256K

NUMA

OK, it’s time to complicate the architecture of how a CPU talks to RAM. NUMA (Non-Uniform Memory Access) enters the picture. Each CPU (or maybe socket with several cores) has a part of the RAM hanging off each. This leads to memory access being faster for local RAM, but slower (tens of cycles slower) for RAM hanging off other CPUs.

Then the OS enters the picture. In at least one case (RHEL?), two things seem to be done:

• OS allocations are pinned to the ‘first’ CPU’s RAM.]
• Other allocations go by default to the first CPU until it is full.

Now for the problem.

• The OS and MariaDB have allocated all the ‘first’ RAM.
• MariaDB has allocated some of the second RAM.
• The OS needs to allocate something.
  Ouch — it is out of room in the one CPU where it is willing to allocate its stuff, so it swaps out some of MariaDB. Bad.

dmesg | grep -i numa # to see if you have numa

Probable solution: Configure the BIOS to «interleave» the RAM allocations. This should prevent the premature swapping, at the cost of off-CPU RAM accesses half the time. Well, you have the costly accesses anyway, since you really want to use all of RAM. Older MySQL versions: numactl —interleave=all. Or: =1

Another possible solution: Turn numa off (if the OS has a way of doing that)

Overall performance loss/gain: A few percent.

Разбор параметров тюнинга Mysql

Разберёмся по порядку с каждым параметром настройки и вопросами которые есть при этом. Итак по пунктам.

key_buffer = 2Gkey_buffer_size = 4G Так и не смог я понять, различаются ли эти два параметра или первый является устаревшим значением второго.

max_connections = 500 и thread-cache-size = 500 По замерам выходило, что не более 90 одновременных подключений, так и поставил 500 с запасом. Тут следует учесть что следующий параметр thread-cache-size должен быть одинаковым числом с максимальным соединением. Поэтому там также стоит 500.

table-cache = 120000 и table-open-cache = 120000 Здесь я поставил по 120000, так как таблиц у меня достаточно много, если у вас не много сайтов, то этот параметр можно не повышать.

interactive-timeout = 360 Установил в 360, чтобы снимались запросы, которые находятся без активности 6 минут или 360 секунд.

query_cache_limit = 12Mquery_cache_size = 4Gjoin_buffer_size = 512M Следующие три параметра настроил исходя из следующих наблюдений. Пробовал ставить query_cache_size от 2 до 6 гигабайт, в итоге оптимально показалось 4. Обработка запросов до 12 мегабайт мне вполне хватало, поэтому оставил 12. Но есть такое мнение, что большой query_cache_size на самом деле сильно грузит систему и желательно держать кеш в memcashed, на практике я не заметил особо, чтобы он забирал мощность, а вот при проверке кеша, обнаружил, что много запросов проходит через него.

sort_buffer_size = 1Gread_buffer_size = 1Gread_rnd_buffer_size = 2G Буфера поставил побольше, так как несколько баз имеют большой размер, хотя есть риск переполнения памяти, тем не менее они настолько не забивали память.

Как спланировать нагрузку на Zabbix

Под небольшой структурой, упомянутой в начале, я подразумеваю 50-100 узлов (не сетевое оборудование с десятками портов) сети на мониторинге и примерно 2000-4000 активных элементов данных, которые записывают 20-40 новых значений в секунду. Под такую сеть вам будет достаточно небольшой виртуальной машины с 2 ядрами и 4 гб памяти. База данных на преимущественно стандартных шаблонах будет расти примерно на 2-4 Гб в год. Дальше еще меньше, так как будет автоматически очищаться.

Для мониторинга такой сети можно вообще не выполнять никаких дополнительных настроек. Мониторинг будет вполне нормально работать. Если же нагрузка начнет расти, то первое, с чем вы столкнетесь — это с размером и производительностью базы данных. База zabbix будет расти пропорционально подключению к ней хостов. И с этим придется что-то делать.

Для решения вопроса производительности нужно будет двигаться в двух направлениях:

1. Очистка базы от ненужных данных.
2. Увеличение производительности сервера mysql.

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

Как установить переменные

В текстовом файле my.cnf (my.ini на Windows),добавьте или измените строку,чтобы сказать что-то вроде

= 5 ГБ

То есть,ПЕРЕМЕННОЕ имя,»=»,и значение.Допускаются некоторые сокращения,такие как M для миллиона (1048576),G для миллиарда.

Для того,чтобы сервер его увидел,настройки должны быть в разделе «» файла.

Настройки в my.cnf или my.ini не вступят в силу до тех пор,пока вы не перезапустите сервер.

Большинство настроек может быть изменено в живой системе,подключившись как пользователь root (или другой пользователь с SUPER привилегиями)и сделав что-то вроде

SET @@global.key_buffer_size = 77000000;

Примечание:Здесь не допускается использование M-или G-суффикса.

Чтобы увидеть,как глобальная ПЕРЕМЕННАЯ ПЕРЕМЕННА делает что-то вроде…

SHOW GLOBAL VARIABLES LIKE "key_buffer_size";
+
| Variable_name   | Value    |
+
| key_buffer_size | 76996608 |
+

Обратите внимание,что эта конкретная настройка была округлена до нескольких,которые понравились MariaDB. Возможно,вы захотите сделать и то,и другое (SET,и изменить my.cnf),чтобы внести изменения немедленно и получить их так,чтобы следующий перезапуск (по какой бы то ни было причине)снова получил значение

Возможно,вы захотите сделать и то,и другое (SET,и изменить my.cnf),чтобы внести изменения немедленно и получить их так,чтобы следующий перезапуск (по какой бы то ни было причине)снова получил значение.

HyperThreading и многоядерность (ЦП)

Короткие ответы (для старых версий MySQL и MariaDB):

• Выключить HyperThreading
• Выключите любые ядра за пределами 8
• HyperThreading в основном уходит в прошлое,поэтому этот раздел может быть неприменим.

HyperThreading отлично подходит для маркетинга,паршиво для производительности.Она включает в себя совместное использование двух процессоров в одном аппаратном кэше.Если оба устройства делают одно и то же,кэш будет достаточно полезным.Если эти устройства делают разные вещи,они будут забивать записи в кэше друг друга.

Кроме того,MySQL не очень хорошо подходит для использования нескольких ядер.Поэтому,если вы отключите HT,остальные ядра будут работать немного быстрее.

Огромные страницы

Это еще один уловка аппаратного исполнения.

Для доступа процессора к оперативной памяти,особенно отображения 64-битного адреса куда-либо в,скажем,128 ГБ или ‘реальной’ оперативной памяти,используется TLB.(TLB=буфер поиска перевода.)Думайте о TLB как о таблице поиска аппаратной ассоциативной памяти;при 64-битном виртуальном адресе,что такое реальный адрес.

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

Обычно оперативная память «страничка» состоит из 4 КБ;TLB на самом деле сопоставляет верхние (64-12)биты в определенную страницу.Затем нижние 12 бит виртуального адреса переносятся нетронутыми.

Например,128 Гб оперативной памяти разбитых 4KB страниц означает 32M записей в таблице страниц.Это много,и,вероятно,намного превышает возможности TLB.Итак,введите трюк «Огромная страница».

С помощью как аппаратного обеспечения,так и операционной системы,можно иметь часть оперативной памяти на огромных страницах,скажем,4МБ (вместо 4КБ).Это приводит к гораздо меньшему количеству TLB записей,но это означает,что единица подкачки составляет 4МБ для таких частей оперативной памяти.Следовательно,огромные страницы,как правило,не опрашиваются.

Теперь оперативная память разбита на части с разбивкой на страницы и без нее; какие части могут быть не разбиты на страницы? В MariaDB буферный пул Innodb — идеальный кандидат. Итак, правильно настроив их, InnoDB может работать немного быстрее:

• Огромные страницы включены
• Скажите операционной системе,чтобы она выделила нужную сумму (а именно,чтобы она совпадала с buffer_pool).
• Скажите MariaDB использовать огромные страницы

использование innodb-памяти против подкачки

Эта нить имеет более подробную информацию о том,что искать и что устанавливать.

Общий прирост производительности:Несколько процентов.Зевок.Слишком много хлопот для слишком маленькой пользы.

Jumbo Pages? Выключи.

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

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

#open_files_limit = 2000

local-infile=0 innodb_file_per_table = 1

p > 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

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

Скорость работы баз данных

Чтобы оптимизация СУБД MySQL дала результат, нужно начать с анализа работы баз данных. Настройки сервиса содержатся в файле /etc/my.cnf.

С помощью настроек можно проверить, какие запросы выполняются медленно и что можно ускорить. Для этого в раздел добавляется следующий запрос:

log-slow-queries=/var/log/mariadb/slow_queries.log
long_query_time=5

Информация указана в строчках:

log-slow-queries=/var/log/mariadb//slow_queries.log
long_query_time=2

Во второй обозначено минимальное время внесения запроса в лог — 2 секунды.

Чтобы увидеть актуальные данные, сервер перезапускается. Сведения находятся в логе:

# systemctl restart mariadb
# tail -f /var/log/mariadb/slow-queries.log

В полученном списке будут представлены все запросы, время выполнения которых превышает указанный показатель. К примеру, больше 10 секунд может выполняться запрос:

SELECT option_name, option_value
FROM wp_options
WHERE autoload = 'yes'

Если при его выполнении в MySQL операция происходит более 3 секунд, запрос может считаться медленным.

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

Решить эту проблему может оптимизация таблиц или кода. В последнем случае из него убираются все сложные запросы. Это длительный процесс, описать который в рамках одной статьи крайне затруднительно. Поэтому здесь мы подробно рассмотрим именно первый вариант.

32-битные ОС и MariaDB

Во-первых,операционная система (и аппаратное обеспечение?)может вступить в сговор,чтобы не позволить вам использовать все 4 ГБ,если это то,что у вас есть.Если у вас более 4 ГБ оперативной памяти,то превышение 4 ГБ _totally_недоступно и непригодно для использования в 32-битной ОС.

Во-вторых,у ОС,вероятно,есть ограничение на то,какой объем оперативной памяти она позволит использовать любому процессу.

Пример:FreeBSD имеет maxdsiz,который по умолчанию равен 512MB.

Example:

$ ulimit -a
...
max memory size (kbytes, -m) 524288

Итак,как только вы определили,какой объем оперативной памяти доступен для mysqld,затем примените 20%/70%,но округлите некоторые.

Если вы получаете сообщение об ошибке типа , это, вероятно, означает, что MySQL превысил то, что ОС готова предоставить ему. Уменьшите настройки кеша.

Swappiness

RHEL, in its infinite wisdom, decided to let you control how aggressively the OS will pre-emptively swap RAM. This is good in general, but lousy for MariaDB.

MariaDB would love for RAM allocations to be reasonably stable — the caches are (mostly) pre-allocated; the threads, etc, are (mostly) of limited scope. ANY swapping is likely to severely hurt performance of MariaDB.

With a high value for swappiness, you lose some RAM because the OS is trying to keep a lot of space free for future allocations (that MySQL is not likely to need).

With swappiness = 0, the OS will probably crash rather than swap. I would rather have MariaDB limping than die. The latest recommendation is swappiness = 1. (2015)

Somewhere in between (say, 5?) might be a good value for a MariaDB-only server.

Replication Compatibility

Replication compatibility depends on:

• The MariaDB Server version
• The MySQL Server version
• The role of each server

Replication compatibility details are described below for each MySQL version that is still currently supported.

For replication compatibility details between MariaDB versions, see .

MySQL 5.7

MariaDB Server 10.2 and later can replicate from a MySQL 5.7 primary server.

MariaDB Server does not support the MySQL implementation of Global Transaction IDs (GTIDs), so the MariaDB replica server must use the binary log file and position for replication. If GTID mode is enabled on the MySQL primary server, the MariaDB replica server will remove the MySQL GTID events and replace them with MariaDB GTID events.

Although MariaDB Server and MySQL 5.7 are compatible at the replication level, they may have some incompatibilities at the SQL (detailed below). Those differences can cause replication failures in some cases. To decrease the risk of compatibility issues, it is recommended to set to . When you want to replicate from MySQL 5.7 to MariaDB Server, it is recommended to test your application, so that any compatibility issues can be found and fixed.

MariaDB can’t make any claims about whether a MySQL 5.7 replica server can replicate from a MariaDB primary server.

MySQL 8.0

MariaDB Server cannot replicate from a MySQL 8.0 primary server, because MySQL 8.0 has a binary log format that is incompatible.

Конфигурация MySQL:

25. Используйте innodb_flush_method = O_DIRECT, чтобы избежать двойного буфера при записи.
26. Избегайте файловой системы O_DIRECT и EXT3 – вы будете сериализовать все ваши записи.
27. Выделите достаточно innodb_buffer_pool_size для загрузки всего вашего файла InnoDB в память – меньше чтения с диска.
28. Не делайте innodb_log_file_size слишком большим, с более быстрыми и большими дисками – более частая очистка хороша и сокращает время восстановления при сбоях.
29. Не смешивайте переменные innodb_thread_concurrency и thread_concurrency – эти два значения несовместимы.
30. Выделите минимальное количество для max_connections – слишком много соединений могут использовать вашу оперативную память и заблокировать ваш сервер MySQL.
31. Держите thread_cache с относительно большим числом, около 16 – для предотвращения замедления при открытии соединений.
32. Используйте skip-name-Resolution – для удаления DNS-запросов.
33. Используйте кэш запросов, если ваши запросы повторяются и ваши данные меняются не часто – однако использование кеша запросов для данных, которые часто изменяются, приведет к снижению производительности.
34. Увеличить temp_table_size – для предотвращения записи на диск.
35. Увеличить max_heap_table_size – для предотвращения записи на диск.
36. Не устанавливайте свой sort_buffer_size слишком высоким – это для каждого соединения и может быстро израсходовать память.
37. Контролируйте key_read_requests и key_reads, чтобы определить размер вашего key_buffer – запросы на чтение ключа должны быть выше, чем ваши key_reads, иначе вы неэффективно используете свой key_buffer.
38. Установка innodb_flush_log_at_trx_commit = 0 улучшит производительность, но оставив ее по умолчанию (1), вы гарантируете целостность данных, а также гарантируете, что репликация не отстает.
39. Имейте тестовую среду, где вы можете проверить свои конфигурации, а также частые перезапуски без влияния на производительность.

Default Option File Locations

MariaDB reads option files from many different directories by default. See the sections below to find out which directories are checked for which system.

For an exact list of option files read on your system by a specific program, you can execute:

$program --help --verbose

For example:

$ mysqld --help --verbose
mysqld  Ver 10.3.13-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
The following groups are read: mysqld server mysqld-10.3 mariadb mariadb-10.3 client-server galera
....

The option files are each scanned once, in the order given by . The effect of the configuration options are as if they would have been given as command line options in the order they are found.

Default Option File Locations on Linux, Unix, Mac

On Linux, Unix, or Mac OS X, the default option file is called . MariaDB looks for the MariaDB option file in the locations and orders listed below.

The locations are dependent on whether the option was defined when MariaDB was built. This option is usually defined as when building RPM packages, but it is usually not defined when building DEB packages or binary tarballs.

When the DEFAULT_SYSCONFDIR cmake option was not defined, MariaDB looks for the MariaDB option file in the following locations in the following order:

Location	Scope
	Global
	Global
	Server
	Server
defaults-extra-file	File specified with , if any
	User

When the DEFAULT_SYSCONFDIR cmake option was defined, MariaDB looks for the MariaDB option file in the following locations in the following order:

Location	Scope
	Global
	Server (from MariaDB 10.6)
	Server
defaults-extra-file	File specified with , if any
	User

• (from MariaDB 10.6) or is the environment variable containing the path to the directory holding the server-specific file. If is not set, and the server is started with mysqld_safe, is set as follows:
  • If there is a file in the MariaDB data directory, but not in the MariaDB base directory, is set to the MariaDB data directory.
  • Else, is set to the MariaDB base directory.
• Note that if is set (from MariaDB 10.6), will not be used, even if set.

Default Option File Locations on Windows

On Windows, the option file can be called either or . MariaDB looks for the MariaDB option file in the following locations in the following order:

Location	Scope
	Global
	Global
	Global
	Global
	Global
	Global
	Server
	Server
	Server
	Server
	Server (from MariaDB 10.6)
	Server (from MariaDB 10.6)
	Server
	Server
defaults-extra-file	File specified with , if any

• The is the directory returned by the function. The value is usually . To find its specific value on your system, open and execute:

  echo %WINDIR%

• The is the directory returned by the function. The value may be a private for the application, or it may be the same as the returned by the function.
• is the parent directory of the directory where is located. For example, if is in , then would be .
• (from MariaDB 10.6) or is the environment variable containing the path to the directory holding the server-specific file.
• Note that if is set (from MariaDB 10.6), will not be used, even if set.

Default Option File Hierarchy

MariaDB will look in all of the above locations, in order, even if has already found an option file, and it’s possible for more than one option file to exist. For example, you could have an option file in with global settings for all servers, and then you could another option file in (i.e.your user account’s home directory) which will specify additional settings (or override previously specified setting) that are specific only to that user.

Option files are usually optional. However, if the option is set, and if the file does not exist, then MariaDB will raise an error. If the option is set, then MariaDB will only read the option file referred to by this option.

If an option or system variable is not explicitly set, then it will be set to its default value. See Server System Variables for a full list of all server system variables and their default values.

Решение 3

Here are 3 MySQL performance tuning settings that you should always look at. If you do not, you are very likely to run into problems very quickly.

innodb_buffer_pool_size: this is the #1 setting to look at for any installation using InnoDB. The buffer pool is where data and indexes are cached: having it as large as possible will ensure you use memory and not disks for most read operations. Typical values are 5-6GB (8GB RAM), 20-25GB (32GB RAM), 100-120GB (128GB RAM).

innodb_log_file_size: this is the size of the redo logs. The redo logs are used to make sure writes are fast and durable and also during crash recovery. Up to MySQL 5.1, it was hard to adjust, as you wanted both large redo logs for good performance and small redo logs for fast crash recovery. Fortunately crash recovery performance has improved a lot since MySQL 5.5 so you can now have good write performance and fast crash recovery. Until MySQL 5.5 the total redo log size was limited to 4GB (the default is to have 2 log files). This has been lifted in MySQL 5.6.

Starting with innodb_log_file_size = 512M (giving 1GB of redo logs) should give you plenty of room for writes. If you know your application is write-intensive and you are using MySQL 5.6, you can start with innodb_log_file_size = 4G.

max_connections: if you are often facing the ‘Too many connections’ error, max_connections is too low. It is very frequent that because the application does not close connections to the database correctly, you need much more than the default 151 connections. The main drawback of high values for max_connections (like 1000 or more) is that the server will become unresponsive if for any reason it has to run 1000 or more active transactions. Using a connection pool at the application level or a thread pool at the MySQL level can help here.