wrk 2025-01-31 08-09-38

mariadb: Тонкие настройки

SHOW VARIABLES:
Эта команда позволяет получить текущее значение переменных конфигурации, связанных с памятью.
sql
SHOW VARIABLES LIKE 'innodb%';
SHOW STATUS:
Для получения информации о текущем состоянии InnoDB, включая использование памяти.
sql
SHOW STATUS LIKE 'Innodb_buffer_pool%';
SHOW PROCESSLIST:
Эта команда отображает текущие процессы и может помочь выявить блокировки или долгие запросы.
sql
SHOW PROCESSLIST;
Важные параметры конфигурации
innodb_buffer_pool_size:
Этот параметр определяет размер пула буферов InnoDB и должен составлять около 70-80% от доступной оперативной памяти, если используется только InnoDB.
text
innodb_buffer_pool_size = 8G # Пример для 8 ГБ
innodb_log_file_size:
Размер лог-файлов InnoDB. Рекомендуется устанавливать его на 25% от размера пула буферов.
text
innodb_log_file_size = 2G # Пример для 2 ГБ
innodb_buffer_pool_instances:
Определяет количество экземпляров пула буферов, что может помочь уменьшить конфликты при высокой нагрузке.
text
innodb_buffer_pool_instances = 8 # Рекомендуется для больших пулов
innodb_flush_method:
Определяет метод сброса данных на диск. Например, O_DIRECT может уменьшить использование кэша операционной системы.
text
innodb_flush_method = O_DIRECT
Применение изменений
Чтобы применить изменения в настройках, используйте следующую команду:
sql
SET GLOBAL <имя параметра> = <значение>;
Например:
sql
SET GLOBAL innodb_buffer_pool_size = 8589934592; -- Установка размера пула в 8 ГБ
Эти команды и параметры помогут вам лучше понять и оптимизировать использование памяти в MariaDB с InnoDB.


===== рассчет на 16G ===========
[mysqld]
innodb_buffer_pool_size = 12G # Используем около 75% от объема оперативной памяти
innodb_log_file_size = 3G # Увеличиваем до около 25% от innodb_buffer_pool_size.
innodb_buffer_pool_instances = 8 # Устанавливаем на более разумное значение.
innodb_flush_method = O_DIRECT # Для улучшения производительности на SSD.
key_buffer_size = 32M # Достаточно небольшой, если MyISAM не используется.
query_cache_size = 0 # Отключаем кэширование запросов.
tmp_table_size = 128M # Максимальный размер временной таблицы в оперативной памяти.
max_heap_table_size = 128M # Максимальный размер таблицы типа MEMORY в оперативной памяти.
innodb_buffer_block_size=16K # Размер блока InnoDB по умолчанию.
innodb_additional_memory_for_lru_page = 4M # Достаточно для управления страницами LRU.


---------
Механизм работы
O_DIRECT
Описание: Этот метод позволяет InnoDB записывать данные напрямую на диск, минуя кэш операционной системы (OS cache). Это уменьшает двойную буферизацию, что может повысить производительность.
Преимущества:
Уменьшение задержек при записи, так как данные не проходят через кэш ОС.
Снижение нагрузки на память, так как избегается использование дополнительного кэша.
Недостатки:
Может привести к увеличению количества операций ввода-вывода (I/O), так как каждая запись требует подтверждения (fsync), что может замедлить выполнение некоторых операций.
O_DSYNC
Описание: Этот метод также использует прямую запись, но с некоторыми отличиями в обработке подтверждений. Он выполняет меньше операций записи и может работать быстрее в определенных сценариях.
Преимущества:
Более высокая скорость записи по сравнению с O_DIRECT, так как не все операции требуют подтверждения.
Подходит для систем с резервным копированием, где потеря данных менее критична.
Недостатки:
Меньшая надежность по сравнению с O_DIRECT, так как не все записи проверяются.
Рекомендации по выбору
Используйте O_DIRECT, если:
Ваша система не имеет RAID или других механизмов резервного копирования.
Вы хотите максимизировать производительность и снизить нагрузку на память.
Ваша база данных критична к задержкам записи.
Используйте O_DSYNC, если:
У вас есть механизмы резервного копирования и вы можете позволить себе небольшую потерю данных.
Вы хотите улучшить производительность при высоких нагрузках на запись.

===================================

Гуру-Пример конфигурации для MariaDB на 16 ГБ ОЗУ

[mysqld]
innodb_buffer_pool_size = 12G # Используем 75% от объема оперативной памяти (рекомендуется 70-80%)
innodb_log_file_size = 3G # Обычно 25% от innodb_buffer_pool_size
innodb_buffer_pool_instances = 8 # Разделяем пул на 8 экземпляров (по 1.5 ГБ каждый)
innodb_flush_method = O_DIRECT # Улучшает производительность на SSD
max_connections = 500 # Настройка в зависимости от ожидаемой нагрузки
query_cache_size = 0 # Отключаем кэширование запросов для повышения производительности
tmp_table_size = 128M # Максимальный размер временной таблицы в оперативной памяти
max_heap_table_size = 128M # Максимальный размер таблицы типа MEMORY в оперативной памяти
innodb_flush_log_at_trx_commit = 2 # Устанавливаем значение для улучшения производительности (0 - максимальная скорость, но риск потери данных)
key_buffer_size = 32M # Размер буфера ключей (не критично, если не используете MyISAM)
thread_cache_size = 16 # Количество потоков, которые могут быть повторно использованы
table_open_cache = 2000 # Количество открытых таблиц, которые могут быть кэшированы
table_definition_cache = 2000 # Кэширование определения таблицы


Комментарии к параметрам
innodb_buffer_pool_size: Рекомендуется выделять от 70% до 80% от общей оперативной памяти для этого параметра. В данном случае выбрано значение в 12 ГБ.
innodb_log_file_size: Установка лог-файла на уровне около 25% от размера пула буферов — это стандартная практика.
innodb_buffer_pool_instances: Рекомендуется делить пул на экземпляры, чтобы уменьшить конкуренцию между потоками. В данном случае установлено значение 8.
innodb_flush_method: Использование O_DIRECT позволяет избежать двойной буферизации и улучшает производительность на SSD.
max_connections: Установите это значение в зависимости от ожидаемой нагрузки на сервер. Для большинства приложений значение в диапазоне от 500 до 1000 будет разумным.
query_cache_size: Отключение кэширования запросов рекомендуется для систем с высокой нагрузкой, так как это может предотвратить блокировки.
tmp_table_size и max_heap_table_size: Эти параметры устанавливаются равными для оптимизации использования временных таблиц.
innodb_flush_log_at_trx_commit: Значение 2 обеспечивает баланс между производительностью и безопасностью данных. Если важна максимальная скорость, можно установить 0, но это увеличивает риск потери данных при сбоях.
key_buffer_size: Этот параметр важен только если вы используете MyISAM. Если вы используете только InnoDB, его можно оставить на минимальном уровне.
thread_cache_size: Увеличение этого значения помогает уменьшить время создания новых потоков при подключении клиентов.
table_open_cache и table_definition_cache: Эти параметры помогут улучшить производительность при большом количестве открытых таблиц и определений.

========= Как установить лимит на соединения для конкретного пользователя =============

Использование команды GRANT:
Чтобы установить лимит на количество соединений для определенного пользователя, выполните следующую команду в консоли MariaDB:
sql
GRANT USAGE ON *.* TO 'username'@'host' WITH MAX_USER_CONNECTIONS 10;
Здесь:
username — имя пользователя, для которого вы хотите установить лимит.
host — хост, с которого пользователь будет подключаться (например, localhost или % для всех хостов).
10 — максимальное количество одновременных соединений, которое может установить этот пользователь.
Проверка текущих настроек:
Чтобы проверить, какие лимиты установлены для конкретного пользователя, используйте следующую команду:
sql
SELECT User, Host, max_user_connections FROM mysql.user WHERE User = 'username';
Изменение лимитов:
Если вам нужно изменить лимиты для уже существующего пользователя, вы можете использовать команду GRANT с новыми значениями:
sql
GRANT USAGE ON *.* TO 'username'@'host' WITH MAX_USER_CONNECTIONS 20; -- Увеличиваем до 20
Сброс значений:
Если вы хотите сбросить лимиты для пользователя, установите значение MAX_USER_CONNECTIONS в 0:
sql
GRANT USAGE ON *.* TO 'username'@'host' WITH MAX_USER_CONNECTIONS 0; -- Без ограничений