Оптимизация mariadb centos 7

Обновлено: 03.07.2024

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

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

Скорость работы MySQL

Оптимизация без аналитики бессмысленна. Перед тем как переходить к оптимизации давайте посмотрим как работает база данных сейчас, есть ли запросы, которые выполняются очень медленно. Все настройки вашего сервиса mysql находятся в файле /etc/my.cnf. Чтобы включить отображение медленных запросов добавьте такие строки в my.cnf, в секцию [mysqld]:

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

Но это уже необязательно для проверки скорости и используется больше для отладки кода и правильности создания таблиц. Дальше перезапустите сервер баз данных и посмотрите лог:

systemctl restart mariadb

tail -f /var/log/mariadb/slow-queries.log


Мы можем видеть, что есть запросы, которые выполняются больше, чем 10 секунд. Это, например, запрос

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

Можно его выполнить отдельно, в консоли mysql:


Здесь тоже измеряется время, и мы видим результат - три секунды. Это очень много. И еще ничего, если такие запросы приходят редко, если ваш сайт постоянно под нагрузкой, то тремя секундами вы не отделаетесь, количество необработанных запросов будет расти, а скорость ответа увеличиваться до нескольких минут. Можно пойти двумя путями - оптимизировать код, убрать сложные запросы, или же нужна оптимизация mysql на сервере.

Оптимизация MySQL

Конфигурация MySQL достаточно сложная, но, к счастью, вам не нужно в нее сильно углубляться. Есть специальный скрипт под названием MySQLTunner, который анализирует работу MySQL и дает советы какие параметры нужно изменить и какие значения для них установить. Скрипт поддерживает большинство версий MariaDB, MySQL и Percona XtraDB. Нам понадобится загрузить три файла с помощью wget:


Первый из них - это сам скрипт, написанный на Perl, второй и третий - база данных простых паролей и уязвимостей. Они позволяют обнаружить проблемы с безопасностью. Дальше можно переходить к тестированию. Я использую сервер с настройками mysql по умолчанию, установленными панелью управления VestaCP.


Буквально за несколько минут скрипт выдаст полную статистику по работе MySQL. Количеству запросов, занимаемому объему памяти и эффективности работы буферов. Вы можете ознакомиться со всем этим, чтобы лучше понять в чем причина проблем. Проблемные места обозначены красными восклицательными знаками. Например, здесь мы видим, что размер буфера движка таблиц InnoDB (InnoDB buffer pool) намного меньше, чем должен быть для оптимальной работы:



Все параметры нужно добавлять в /etc/my.cnf. Еще раз замечу, что вы не копируете статью, а смотрите что вам выдала утилита. Начнем с query-cache.

query_cache_size=0
query_cache_type=0
query_cache_limit=1M

Скрипт рекомендует отключить кэш запросов. Query Cache - это кэш вызовов SELECT. Когда базе данных отправляется запрос, она выполняет его и сохраняет сам запрос и результат в этом кэше. И все бы ничего, но при использовании его вместе с InnoDB при любом изменении совпадающих данных кэш будет перестраиваться, что влечет за собой потерю производительности. И чем больше объем кэша, тем больше потери. Кроме того при обновлении кэша могут возникать блокировки запросов. Таким образом, если данные часто пишутся в базу данных - его надежнее отключить.

Оба параметра устанавливают размер памяти, которая используется для внутренних временных таблиц MySQL. Утилита рекомендует использовать объем больше 16 мегабайт, просто установите это ваше значение для обоих переменных, если у вас достаточно памяти, то можно выделить 32 или даже 64. Но важно чтобы оба значения совпадали, иначе будет использоваться минимальное.

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

Указывает, что не нужно пытаться определить доменное имя для подключений извне. Ускоряет работу, так как не тратится время на DNS запросы.

Этот параметр определяет размер буфера InnoDB в оперативной памяти, от этого размера очень сильно зависит скорость выполнения запросов. Значение зависит от размера ваших таблиц и количества данных в них. Если памяти недостаточно, запросы будут обрабатываться дольше. У меня используется стандартный объем 128, а нужно больше 652.


Размер файла лога innodb должен составлять 25% от размера буфера. В случае 800 мегабайт это будет 200М. Но тут есть одна проблема. Чтобы изменить размер лога нужно выполнить несколько действий. Поскольку мы изменили все нужные параметры перейдем к перезагрузке сервера. Для нашего лога нужно остановить сервис:

systemctl stop mariadb

Затем переместите файлы лога в /tmp:

mv /var/lib/mysql/ib_logfile[01] /tmp


И запустите сервис:

systemctl start mariadb

systemctl status mariadb


Тестирование результата

Готово оптимизация базы данных mysql завершена, теперь тестируем тот же запрос через клиент mysql:

> USE база_данных;
> SELECT option_name, option_value FROM wpfc_options WHERE autoload = 'yes';


Первый раз он выполняется долго, может даже дольше чем обычно, но все последующие разы буквально мгновенно. Результат с более 3 секунд до 0,15. А если брать статистику из slow-log, то от более 12. Если в выводе утилиты для вас были предложены и другие оптимизации, то их тоже стоит применить.

Выводы

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

На завершение лекция про производительность MySQL от Percona:

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

Введение

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

О том как работать с базами данных в консоли вы можете узнать из статьи MariaDB работа из консоли.

Установка MariaDB

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

Репозиторий разработчика

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

Рекомендую воспользоваться ссылкой выше и использовать код репозитория и команды установки исходя из вашей системы.

Например, для системы CentOS 8 с выбором версии 10.4 я получил код который размещу в необходимый файл:

Установка MariaDB

На странице, где указан код присутствует и команда которую необходимо выполнить для установки исходя из выбранного дистрибутива. Для CentOS 8 команда имеет такой вид:

Перед продолжением установки смотрим чтобы репозиторий был mariadb!

После установки запустим и добавим в автозагрузку выполнив команды:

Проверим статус выполнив команду:

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

Начальная конфигурация mysql

Для начальной конфигурации необходимо запустить скрипт и ответить на все вопросы исходя из ваших требований. Например, обычно я задаю пароль пользователя root и оставляю остальные параметры по-умолчанию.

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

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

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

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

Правильная оптимизация MariaDB возможно только при использовании правильных параметров исходя из вашей версии!

Файл настройки MariaDB

В фале /etc/my.cnf присутствует строка !includedir /etc/my.cnf.d говорящая о том что все настройки находятся в папке /etc/my.cnf.d

Все основные настройки необходимо вносить в раздел [mysqld] файла server.cnf.

Аналитика работы MariaDB

Без аналитики работы сервера проводить оптимизацию MariaDB сложно и очень неудобно. Самый важный момент который необходим при анализе работы, это медленные запросы к базе данных.

Включим отображение медленных запросов добавив следующие строки в /etc/my.cnf.d/server.cnf, в секцию [mysqld]:

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

Перезапустим сервер баз данных и посмотрим лог медленных запросов:

В нашем случае медленных запросов нет.

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

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

Для правильной оптимизации необходимо владеть всей необходимой информацией. Например, для меня это:

Работа с MySQLTunner

Существует perl-скрипт Mysqltuner, который анализирует статистику работы Mysql и выдает свои рекомендации что бы была проведена правильная оптимизация MariaDB.

Установка

Создадим папку, перейдем в неё и скачаем скрипт следующим образом:

Если возникает ошибка вида:

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

Базы простых паролей и уязвимостей будут использоваться только при запуске скрипта!

Также, можно произвести установку из репозиториев:

Оптимизация

Чтобы данные анализа и статистика были корректными, сервер Mysql должен проработать без смены параметров конфигурации и без перезагрузок, по рекомендации самого Mysqltuner не менее 24 часов.

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

Если производилось скачивание скрипта находясь в папке со скриптом необходимо выполнить команду:

Если производилась установка:

После введения данных пользователя root для MariaDB вы увидите примерно такой вывод:

Так выглядит вывод команды после 48 часов работы MariaDB без рабочих баз.

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

Для того чтобы была информация в разделе Log file Recommendations необходимо добавить следующий код:

  • query_cache_size (=0)
  • query_cache_type (=0)
  • query_cache_limit (> 1M, or use smaller result sets)

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

После внесения изменений выполним перезапуск MariaDB:

Теперь вывод примет вид:

Базовые параметры

Обычно я добавляю следующие параметры по умолчанию в свои сервера:

Обязательно после всех работ с настройкой баз данных выполняйте проверку статуса на предмет ошибок выполняя команду:

Ротация логов MariaDB

Откроем необходимый файл и сделаем необходимые изменения:

Мы указали что надо ротировать все логи и хранить 7 дней при условии что размер файла 5 Mегабайт.

Сохраним и применим изменения без перезагрузки:

Проверим правильность выполнив тестирование (опция -d):

Все логи не имеют необходимого размера для выполнения ротации.

Вывод

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

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

Исходя из статьи вы теперь знаете откуда брать информацию о всех параметрах вашего сервера баз данных.

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Понравилась статья? Поделитесь ей с друзьями! Пожалуйста, оставляйте свои комментарии

Читая их я получаю информацию которая позволяет мне улучшить качество написания статей. Кроме того, оставляя комментарии вы помогаете сайту получить более высокий рейтинг у поисковых систем. Давайте общаться.

2 комментариев для статьи “ MariaDB оптимизация и установка ”

К сожалению с репликациями пока не работал. Обязательно напишу статью если начну работать с репликациями баз mariadb.

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

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

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

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

Установка и знакомство с 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. Приступим к подготовке к установке:

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

Чтобы каждый раз не вводить логин и пароль, можно создать специальный файл

/.my.cnf содержащий данные администратора БД:

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

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

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

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

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

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

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

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

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

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

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

    Тюнинг innodb_buffer_pool_size в MySQL

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Настройка 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_doublewrite в MariaDB

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    В моём случае под кэш запросов по-умолчанию выделилось 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 запросы на сортировках и группировках.

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

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

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

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

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

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

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

    Тюнинг performance_schema в MySQL

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    И ещё одна интересная выжимка фактов о докере, которая поможет в кратчайшие сроки начать его продуктивное использование. Цель данной статьи…

    В продолжение прошлой статьи рассмотрим пример настройки ротации логов контейнеров на примере CentOs 7. В моём случае stdout и stderr контейнеров…

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

    Favorite

    Добавить в избранное

    Главное меню » Операционная система Linux » Команды Linux и его оболочки » Как установить, защитить и настроить производительность сервера базы данных MariaDB в Linux

    (1 оценок, среднее: 5,00 из 5)


    С ервер базы данных является критически важным компонентом сетевой инфраструктуры, необходимой для современных приложений. Не имея возможности хранения, извлечения, обновления и удаления данных (при необходимости), полезность и объем веб-и настольных приложений становится очень ограниченным.

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

    В этой статье мы кратко рассмотрим, как установить сервер базы данных MariaDB, и затем мы расскажем, как настроить его.

    Установка и обеспечение безопасности сервера MariaDB

    В CentOS 7.x , MariaDB заменен на MySQL, который до сих пор можно найти в Ubuntu (наряду с MariaDB). То же самое верно и для OpenSUSE .

    Для краткости мы будем использовать только MariaDB в этом учебнике, но обратите внимание, что помимо того, что различные имена и философии развития, как реляционная база данных системы управления ( РСУБД для краткости) практически идентичны.

    Это означает , что на стороне клиента команды одинаковы на обоих MySQL и MariaDB и конфигурационные файлы названы и расположены в одних и тех же местах.

    Чтобы установить MariaDB, сделайте следующее:

    Обратите внимание, что в Ubuntu, вам будет предложено ввести пароль для РСУБД пользователя root.

    После того, как были установлены вышеуказанные пакеты, убедитесь, что служба базы данных запущена и активирована для запуска при загрузке (в CentOS и OpenSUSE вам нужно будет выполнить эту операцию вручную, в то время как в Ubuntu процесс установки уже позаботились об этом для вас):

    Затем запустите скрипт mysql_secure_installation . Этот процесс позволит вам:

    1. Установить / сбросить пароль для РСУБД пользователя root
    2. Удалить анонимные логины (что позволяет только пользователям с допустимой учетной записью, чтобы войти в РСУБД)
    3. Отключить доступ root для целей, отличных от машин локального хоста
    4. Удалить тестовую базу данных (к который любой желающий может получить доступ)
    5. Активировать изменения, связанные с 1 по 4.

    Для более подробного описания этого процесса, вы можете обратиться к разделу установки в посте Установка MariaDB базы данных в RHEL / CentOS / Fedora и Debian / Ubuntu.

    Настройка сервера MariaDB

    Параметры конфигурации по умолчанию считываются из следующих файлов в заданном порядке: /etc/mysql/my.cnf , /etc/my.cnf , и

    Чаще всего существует только /etc/my.cnf . Именно в этом файле, в который мы установим общесерверных настройки (которые могут быть переопределены с теми же настройками

    /.my.cnf для каждого пользователя).

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

    Системные конфигурации сервера приведены в разделе [mysqld] , где, как правило, вы найдете только первые два параметра в приведенной ниже таблице. Остальные другие часто используемые параметры (если не указано иное, мы изменим значение по умолчанию на пользовательское значение по нашему выбору):

    В CentOS , нам нужно будет сообщить SELinux, чтобы позволить MariaDB слушать на нестандартном порту ( 20500 ) до перезапуска службы:

    Затем перезапустить службу MariaDB.

    Настройка производительности MariaDB

    Чтобы помочь нам в проверке и настройке конфигурации в соответствии с нашими конкретными потребностями, мы можем установить mysqltuner (скрипт , который будет предоставлять предложения по улучшению работы нашего сервера баз данных и повышения ее устойчивости):

    Затем измените каталог в папку, извлеченной из архива (точная версия может отличаться в вашем случае):

    и запустите его (вам будет предложено ввести данные учетной записи администратора MariaDB)

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

    Тюнинг производительности MariaDB

    Тюнинг производительности MariaDB

    Параметр query_cache_type указывает на то, отключен ли кэш запросов (0) или включен (1) . В этом случае mysqltuner советует нам, чтобы отключить его.

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

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

    Как установить, защитить и настроить производительность сервера базы данных MariaDB в Linux

    Резюме

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

    Не пропустите: 15 Полезных настроек производительности и советы по оптимизации в MariaDB

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

    Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

    Читайте также: