MariaDB Память

Настройка RAM для MariaDB — краткий ответ

Если используется только MyISAM, установите key_buffer_size на 20% от доступной оперативной памяти. (Плюс innodb_buffer_pool_size=0)

Если используется только InnoDB, установите innodb_buffer_pool_size на 70% от доступной оперативной памяти. (Плюс key_buffer_size = 10Мб, небольшое значение, но не нулевое.)

Правило большого пальца для настройки:

• Начните с резервной копии my.cnf / my.ini.
• Измените key_buffer_size и innodb_buffer_pool_size в соответствии с использованием движка и объёмом оперативной памяти.
• Медленные запросы обычно можно исправить с помощью индексов, изменений схемы или модификаций SELECT, а не настройкой.
• Не увлекайтесь кэшем запросов query cache, пока не поймете, что он может и не может делать.
• Не изменяйте ничего, если у вас нет проблем (например, с максимальным количеством подключений).
• Убедитесь, что изменения находятся в разделе [mysqld], а не в каком-то другом.

20%/70% предполагают, что у вас как минимум 4 ГБ оперативной памяти. Если у вас крошечный антикварный компьютер или крошечная виртуальная машина, то эти проценты слишком высоки.

Теперь о подробностях.

Как устранить проблемы с недостатком памяти

Если сервер MariaDB завершается из-за «недостатка памяти», вероятно, он неправильно настроен.

В MariaDB есть два вида буферов:

• Глобальные, которые выделяются только один раз за время работы сервера:
  • Буферы движков хранения (innodb_buffer_pool_size, key_buffer_size, aria_pagecache_buffer_size и т. д.)
  • Кэш запросов query_cache_size.

• Глобальные кэши, которые динамически увеличиваются и уменьшаются по мере необходимости до максимального значения:
  • max_user_connections
  • table_open_cache
  • table_definition_cache
  • thread_cache_size

• Локальные буферы, которые выделяются по мере необходимости
  • Внутренние, используемые при создании индексов движка (myisam_sort_buffer_size, aria_sort_buffer_size).
  • Внутренние буферы для хранения блобов.
    • Некоторые движки хранения будут хранить временный кэш для хранения самого большого блоба, увиденного при сканировании таблицы. Он будет освобожден в конце запроса. Обратите внимание, что временное хранилище блобов не включено в информацию о памяти в information_schema.processlist, а только в общем используемом объёме памяти (show global status like "memory_used").
  • Буферы и кэши, используемые во время выполнения запросов:

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

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

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

select information_schema.system_variables.variable_name,
information_schema.system_variables.default_value,
global_variables.variable_value from
information_schema.system_variables,information_schema.global_variables where
system_variables.variable_name=global_variables.variable_name and
system_variables.default_value <> global_variables.variable_value and
system_variables.default_value <> 0

Что такое буфер ключей?

MyISAM выполняет две разные операции кэширования.

• Блоки индексов (по 1 КБ каждый, структура B-дерева, из файла .MYI) хранятся в «буфере ключей».
• Кэширование блоков данных (из файла .MYD) оставляется операционной системе, поэтому убедитесь, что для этого есть достаточно свободного места. Замечание: некоторые версии ОС всегда сообщают об использовании более 90%, даже когда есть много свободного места.

SHOW GLOBAL STATUS LIKE 'Key%';

Затем вычислите Key_read_requests / Key_reads. Если это значение высокое (скажем, более 10), то буфер ключей достаточно большой, иначе необходимо отрегулировать значение key_buffer_size.

Что такое буферный пул?

InnoDB выполняет весь кэш в буферном пуле, размер которого контролируется innodb_buffer_pool_size. По умолчанию он содержит 16-килобайтные блоки данных и индексов из открытых таблиц (см. innodb_page_size), плюс некоторые затраты на обслуживание.

Начиная с MariaDB 5.5, допускается использование нескольких буферных пулов; это может помочь, так как для каждого пула существует один мьютекс, тем самым снижая проблемы с блокировкой мьютекса.

Дополнительная информация о настройке InnoDB

Другой алгоритм

Это позволит установить основные параметры кэша на минимальные значения; это может быть важно для систем с большим количеством других процессов и/или оперативной памятью 2 ГБ или меньше.

Используйте SHOW TABLE STATUS для всех таблиц во всех базах данных.

Сложите Index_length для всех таблиц MyISAM. Установите key_buffer_size не больше этого значения.

Сложите Data_length + Index_length для всех таблиц InnoDB. Установите innodb_buffer_pool_size не более чем на 110% от этой суммы.

Если это приведет к обмену данными, уменьшите оба значения.

Рекомендуется пропорционально уменьшить оба значения.

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

SELECT  ENGINE,
        ROUND(SUM(data_length) /1024/1024, 1) AS "Data MB",
        ROUND(SUM(index_length)/1024/1024, 1) AS "Index MB",
        ROUND(SUM(data_length + index_length)/1024/1024, 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;

Выделение памяти для запросов

Существует две переменные, определяющие, как MariaDB выделяет память во время анализа и выполнения запроса. query_prealloc_size определяет стандартный буфер для памяти, используемой для выполнения запросов, и query_alloc_block_size — размер блоков памяти, если query_prealloc_size недостаточно. Правильная настройка этих переменных уменьшит фрагментацию памяти на сервере.

Проблема с блокировкой мьютекса

MySQL был разработан в эпоху однопроцессорных компьютеров и проектировался для легкой портируемости на различные архитектуры. К сожалению, это привело к некоторой небрежности в механизме блокировки действий. Существует небольшое (слишком малое) количество «мьютексов» для получения доступа к нескольким важным процессам. Важно отметить:

• Буфер ключей MyISAM
• Кэш запросов
• Буферный пул InnoDB. На многоядерных системах проблема с мьютексом вызывает проблемы с производительностью. Как правило, после 4–8 ядер MySQL работает медленнее, а не быстрее. MySQL 5.5 и XtraDB от Percona немного улучшили ситуацию в InnoDB; практический предел для ядер составляет около 32, а производительность, как правило, достигает плато, а не снижается. 5.6 утверждает, что масштабируется до примерно 48 ядер.

Гиперпоточность и несколько ядер (процессоров)

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

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

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

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

32-битная ОС и MariaDB

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

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

Пример: maxdsiz FreeBSD, по умолчанию 512 МБ.

Пример:

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

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

Если вы получите ошибку, подобную [ERROR] /usr/libexec/mysqld: Out of memory (Needed xxx bytes), это, вероятно, означает, что MySQL превысил предел, который ОС готова предоставить. Уменьшите настройки кэша.

64-битная ОС с 32-битной MariaDB

ОС не ограничена 4 ГБ, но MariaDB — да.

Если у вас как минимум 4 ГБ оперативной памяти, эти значения могут быть хорошими:

• key_buffer_size = 20% от _всей_ оперативной памяти, но не более 3 ГБ
• innodb_buffer_pool_size = 3 ГБ

Вам, вероятно, следует обновить MariaDB до 64-битной версии.

64-битная ОС и MariaDB

Только MyISAM: key_buffer_size: Используйте около 20% оперативной памяти. Установите (в my.cnf/my.ini) innodb_buffer_pool_size=0 = 0.

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

Тем временем, установите key_buffer_size = 20М (небольшое, но ненулевое значение)

Если у вас смешанные типы хранилищ, уменьшите оба значения.

max_connections, thread_stack Каждый «поток» потребляет некоторое количество оперативной памяти. Раньше это было около 200 КБ; 100 потоков составляли бы 20 МБ, что не является значительным размером. Если у вас max_connections = 1000, то это 200 МБ, возможно, больше. Наличие такого количества подключений, вероятно, подразумевает другие проблемы, которые следует решить.

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

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

Больше информации о max_connections, wait_timeout, пулах подключений и т. д.

table_open_cache

(В более старых версиях это называлось table_cache)

У ОС есть определенный предел количества открытых файлов, которые она позволит процессу иметь. Для каждой таблицы требуется от 1 до 3 открытых файлов. Каждая РАЗДЕЛЁННАЯ ТАБЛИЦА фактически представляет собой таблицу. Большинство операций с разделяемой таблицей открывают _все_ разделы.

В *nix утилита ulimit показывает предел количества файлов. Максимальное значение исчисляется десятками тысяч, но иногда оно установлено только в 1024. Это ограничивает вас примерно 300 таблицами. Более подробная информация об ulimit

(Этот абзац оспаривается.) С другой стороны, кэш таблиц был (и есть) реализован неэффективно — поиск производился с помощью линейного сканирования. Поэтому установка table_cache в тысячи значений может фактически замедлить mysql. (Это показали бенчмарки.)

Вы можете оценить производительность вашей системы с помощью SHOW GLOBAL STATUS; и вычислить открытие в секунду с помощью Opened_files / Uptime. Если это значение больше, скажем, 5, то следует увеличить table_open_cache. Если значение меньше, скажем, 1, то можно получить улучшение, уменьшив table_open_cache.

Начиная с MariaDB 10.1, значение table_open_cache по умолчанию составляет 2000.

Кэш запросов

Короткий ответ: query_cache_type = OFF и query_cache_size = 0

Кэш запросов (QC) фактически представляет собой хеш-отображение инструкций SELECT в наборы результатов.

Длинный ответ... Существует множество аспектов «Кэша запросов», многие из которых отрицательные.

• Внимание начинающим! Кэш запросов абсолютно не связан с key_buffer и buffer_pool.
• Когда он полезен, Кэш запросов работает молниеносно. Несложно создать бенчмарк, который будет работать в 1000 раз быстрее.
• Для управления Кэшем запросов используется единственный мьютекс.
• Кэш запросов, если он не выключен и не равен 0, используется для _каждого_ SELECT.
• Да, мьютекс используется даже если query_cache_type = DEMAND (2).
• Да, мьютекс используется даже для SQL_NO_CACHE.
• Любое изменение запроса (даже добавление пробела) может (вероятно) привести к другому элементу в Кэше запросов.
• Если в my.cnf указано type=ON, а затем вы его выключаете, это не полностью отключает его. См.: https://bugs.mysql.com/bug.php?id=60696

«Очистка» является дорогостоящей и частой операцией:

• При любом изменении в таблице все записи в Кэше запросов для _этой_ таблицы удаляются.
• Это происходит даже на только для чтения Slave.
• Очистки выполняются с линейным алгоритмом, поэтому большой Кэш запросов (даже 200 МБ) может быть заметно медленным.

Чтобы увидеть, как работает ваш Кэш запросов, воспользуйтесь SHOW GLOBAL STATUS LIKE 'Qc%'; затем вычислите коэффициент попаданий в чтение: Qcache_hits / Qcache_inserts. Если он больше, скажем, 5, Кэш запросов может быть полезным.

Если вы решили, что Кэш запросов вам подходит, я рекомендую

• query_cache_size не более 50М
• query_cache_type = DEMAND
• SQL_CACHE или SQL_NO_CACHE во всех SELECT, исходя из того, какие запросы, скорее всего, извлекут выгоду из кэширования.

• Почему следует отключить Кэш запросов
• Обсуждение размера

thread_cache_size

Настраивать thread_cache_size начиная с MariaDB 10.2.0 не требуется. Раньше это была незначимая настраиваемая переменная. Ноль замедлит создание потоков (соединений). Небольшое (например, 10), ненулевое число — хорошо. Настройка практически не влияет на использование оперативной памяти.

Это количество дополнительных процессов, которые нужно сохранить. Он не ограничивает количество потоков; max_connections делает это.

Логи двоичных записей

Если вы включили логирование двоичных записей (через log_bin) для репликации и/или восстановления по состоянию на определенный момент времени, система будет создавать двоичные логи постоянно. То есть они могут постепенно заполнить диск. Рекомендуется установить expire_logs_days = 14, чтобы сохранить только логи за последние 14 дней.

Swappiness

RHEL, в своей бесконечной мудрости, решила дать вам возможность контролировать, насколько активно ОС будет предварительно выполнять подкачку оперативной памяти. Это хорошо в целом, но не лучшим образом подходит для MariaDB.

MariaDB желает, чтобы выделение оперативной памяти было достаточно стабильным — кэши (в основном) предварительно выделены; потоки и т. д. (в основном) ограничены по объёму. Любая подкачка, вероятно, серьезно повлияет на производительность MariaDB.

С высоким значением swappiness вы теряете часть оперативной памяти, поскольку ОС пытается сохранить много свободного места для будущих выделений (которые MySQL, вероятно, не потребует).

При swappiness = 0 ОС, скорее всего, аварийно завершит работу, вместо того, чтобы выполнять подкачку. Я бы предпочел, чтобы MariaDB работала со сбоями, чем завершилась. Последнее рекомендация — swappiness = 1. (2015)

Ещё подтверждений

Некоторое значение посередине (скажем, 5?) может быть хорошим для сервера, работающего только с MariaDB.

NUMA

В порядке, пора усложнить архитектуру взаимодействия процессора с оперативной памятью. Вступает в картину NUMA (Non-Uniform Memory Access). У каждого процессора (или, возможно, сокета с несколькими ядрами) есть часть оперативной памяти, подключенная к нему. Это приводит к тому, что доступ к памяти быстрее для локальной оперативной памяти, но медленнее (на десятки тактов) для оперативной памяти, подключенной к другим процессорам.

Затем вступает в дело ОС. По крайней мере, в одном случае (RHEL?) делаются две вещи:

• Выделения ОС привязаны к оперативной памяти «первого» процессора.
• Другие выделения по умолчанию идут на первый процессор, пока он не заполнится.

Теперь о проблеме.

• ОС и MariaDB выделили всю оперативную память «первого» процессора.
• MariaDB выделила часть оперативной памяти «второго» процессора.
• ОС нужно выделить что-то. Ой — у неё нет места на том процессоре, где она хочет выполнить выделение, поэтому она подкачивает часть памяти MariaDB. Плохо.

dmesg | grep -i numa # чтобы узнать, есть ли у вас numa

Вероятное решение: Настройте BIOS для «перемежения» выделения оперативной памяти. Это должно предотвратить преждевременную подкачку, за счет затрат на доступ к оперативной памяти на других процессорах в половине случаев. Ну, у вас все равно есть дорогостоящие доступы, так как вам нужно использовать всю оперативную память. Более ранние версии MySQL: numactl --interleave=all. Или: innodb_numa_interleave=1

Другое возможное решение: выключите numa (если у ОС есть способ это сделать)

Общее падение/повышение производительности: несколько процентов.

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

Это еще один трюк для повышения производительности оборудования.

Для того, чтобы процессор мог получить доступ к оперативной памяти, особенно для отображения 64-битного адреса в, скажем, 128 ГБ или «реальной» оперативной памяти, используется TLB. (TLB = Translation Lookaside Buffer.) Представьте себе TLB как таблицу быстрого поиска ассоциативной памяти; задан 64-битный виртуальный адрес, какой это реальный адрес.

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

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

Например, 128 ГБ оперативной памяти, разбитые на страницы по 4 КБ, означают 32 М записи таблицы страниц. Это много, и, вероятно, превосходит емкость TLB. Поэтому вступает в действие трюк «Огромные страницы».

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

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

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

• Использование памяти InnoDB против подкачки

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

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

Гигантские страницы? Отключить.

ENGINE=MEMORY

Двигатель кэширования памяти Memory Storage Engine — это малоиспользуемая альтернатива MyISAM и InnoDB. Данные не сохраняются, поэтому его применение ограничено. Размер таблицы MEMORY ограничен значением max_heap_table_size, которое по умолчанию составляет 16 МБ. Я упоминаю об этом на случай, если вы изменили это значение на очень большое; это может привести к нехватке оперативной памяти.

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

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

innodb_buffer_pool_size = 5ГБ

То есть, имя переменной, знак "=" и значение. Допускаются некоторые сокращения, такие как М для миллионов (1048576) и Г для миллиардов.

Для того, чтобы сервер видел эти настройки, они должны находиться в разделе "[mysqld]" файла.

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

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

SET @@global.key_buffer_size = 77000000;

Примечание: здесь не допускаются суффиксы М или Г.

Чтобы увидеть значение глобальной переменной, выполните что-то вроде

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

Обратите внимание, что это конкретное значение было округлено вниз до кратного значения, которое MariaDB предпочитает.

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

Веб-сервер

Веб-сервер, такой как Apache, использует несколько потоков. Если каждый поток открывает соединение с MariaDB, у вас может не хватить соединений. Убедитесь, что MaxClients (или эквивалент) установлен на разумное число (менее 50).

Инструменты

• MySQLTuner
• TUNING-PRIMER

Существует несколько инструментов, которые дают рекомендации по памяти. Одна из вводящая в заблуждение запись, которую они выдают:

Максимальное возможное использование памяти: 31,3 ГБ (266% от установленной оперативной памяти)

Не пугайтесь — используемые формулы чрезмерно консервативны. Они предполагают, что все значения max_connections используются и активны, а также выполняется задача, требующая много памяти.

Общее количество фрагментированных таблиц: 23 Это означает, что OPTIMIZE TABLE _может_ помочь. Я рекомендую это для таблиц с высоким процентом «свободного места» (см. SHOW TABLE STATUS) или для таблиц, для которых вы часто выполняете DELETE и/или UPDATE. Тем не менее, не стоит слишком часто оптимизировать. Единого раза в месяц может быть достаточно.

MySQL 5.7

5.7 хранит больше информации в оперативной памяти, что приводит к увеличению занимаемого места примерно на полгигабайта по сравнению с 5.6. См. Увеличение памяти в 5.7.

Postlog

Создано в 2010 году; обновлено в октябре 2012 года, январе 2014 года

Советы в этом документе применимы к MySQL, MariaDB и Percona.

См. также

• Настройка MariaDB для оптимальной производительности
• Более глубокий анализ: настройка Tocker для 5.6
• Основы оптимизации производительности InnoDB от Ирфана (redux)
• 10 настроек MySQL для настройки после установки
• Magento
• Взгляд Петра Зайцева на эту тему (май 2016 г.)

Рик Джеймс любезно разрешил нам использовать эту статью в базе знаний.

Сайт Рика Джеймса содержит другие полезные советы, руководства, рекомендации по оптимизации и советы по отладке.

Исходный источник: http://mysql.rjweb.org/doc.php/random