Настройка системных переменных MySQL для высокой производительности

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


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

Это суровая реальность, что даже разработчики с 6-8 лет за поясом изо всех сил пытаются объяснить тонкости оптимизатора запросов и предпочитают смотреть в небо, когда их спрашивают о настройка базы данных.

Почему?

Удивительно, но причина не в лени (хотя в некоторой части это так).

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

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

Любопытно? Давайте погрузимся в!

Не любопытно? Ну, так или иначе, погрузись, потому что от этого зависит твоя карьера! ��

Оптимизировать кеш запросов MySQL

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

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

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

MariaDB [(нет)]> ПОКАЗАТЬ ПЕРЕМЕННЫЕ, КАК ‘have_query_cache’;
+——————+——-+
| Переменное_имя | Значение |
+——————+——-+
| have_query_cache | ДА |
+——————+——-+

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

Теперь давайте посмотрим, действительно ли у меня включен кеш запросов:

MariaDB [(нет)]> SHOW VARIABLES LIKE ‘query_cache_type’;
+——————+——-+
| Переменное_имя | Значение |
+——————+——-+
| query_cache_type | ON |
+——————+——-+

Да. Но если вы этого не сделаете, вы можете включить его, сказав:

MariaDB [(нет)]> SET GLOBAL query_cache_type = ON;

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

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

Другая интересная переменная здесь – query_cache_size, функция которой не требует пояснений:

MariaDB [(нет)]> ПОКАЗАТЬ ПЕРЕМЕННЫЕ, КАК ‘query_cache_size’;
+——————+———-+
| Переменное_имя | Значение |
+——————+———-+
| query_cache_size | 16777216 |
+——————+———-+

Итак, у меня кеш запросов размером около 16 МБ. Обратите внимание, что даже если кэширование запросов включено, но этот размер равен нулю, кэширование фактически отключено. Вот почему проверки только одной переменной недостаточно. Теперь вы должны установить размер кэша запроса, но сколько он должен быть? Прежде всего, обратите внимание, что для функции кэширования запросов потребуется 4 КБ для хранения метаданных, поэтому все, что вы выберете, должно быть выше.

Допустим, вы установили размер кэша запросов равным 500 КБ:

MariaDB [(нет)]> SET GLOBAL query_cache_size = 500000;

Делает ли это достаточно много? Ну, нет, потому что то, как на самом деле обработчик запросов будет работать, зависит еще от нескольких вещей:

  • Прежде всего, переменная query_cache_size должна быть достаточно большой, чтобы содержать результаты ваших запросов. Если он слишком мал, ничто не будет кешировано.
  • Во-вторых, если для query_cache_size задано слишком большое число, возникнут два типа проблем: 1) Движок должен будет выполнить дополнительную работу по сохранению и поиску результатов запроса в этой большой области памяти. 2) Если большинство запросов приводит к гораздо меньшим размерам, кэш будет фрагментирован, а преимущества от использования кеша будут потеряны.

Откуда вы знаете, что кеш фрагментируется? Проверьте общее количество блоков в кэше следующим образом:

MariaDB [(нет)]> показать статус как ‘Qcache_total_blocks’;
+———————+——-+
| Переменное_имя | Значение |
+———————+——-+
| Qcache_total_blocks | 33 |
+———————+——-+

Если число очень велико, кэш фрагментирован и его необходимо очистить..

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

Потоки, пулы потоков, ожидание и время ожидания

Это, пожалуй, самая интересная часть того, как работает MySQL, и сделать его правильно означает сделать ваше приложение в несколько раз быстрее.!

Резьбонарезной

MySQL – это многопоточный сервер. Это означает, что каждый раз, когда появляется новое соединение с сервером MySQL, он открывает новый поток с данными соединения и передает его дескриптор клиенту (на тот случай, если вам интересно, что это за поток, см. это). Затем клиент отправляет все запросы через этот поток и получает результаты. Это заставляет нас задать естественный вопрос: сколько потоков может раскрутить MySQL? Ответ лежит в следующем разделе.

Пул потоков

Ни одна программа в компьютерной системе не может открыть столько потоков, сколько она хочет. Причина двойная: 1) Потоки стоят памяти (ОЗУ), а операционная система просто не позволит вам сходить с ума и съесть все это. 2) Управление, скажем, миллионами потоков само по себе является огромной задачей, и если сервер MySQL сможет создать такое количество потоков, он умрет, пытаясь справиться с накладными расходами..

Чтобы избежать этих проблем, MySQL поставляется с пулом потоков – фиксированным числом потоков, которые являются частью пула в начале. Новые запросы на соединение заставляют MySQL выбрать один из этих потоков и вернуть данные о соединении, и если все потоки израсходованы, новые соединения естественно отклоняются. Давайте посмотрим, насколько велик пул потоков:

ariaDB [(нет)]> показать переменные вроде ‘thread_pool_size’;
+——————+——-+
| Переменное_имя | Значение |
+——————+——-+
| thread_pool_size | 4 |
+——————+——-+

Итак, моя машина допускает максимум четыре соединения одновременно. Интересно отметить, что число 4 связано с тем, что у меня есть четырехъядерный процессор, что означает, что мой компьютер может одновременно выполнять только 4 параллельные задачи (я говорю здесь о действительно параллельных задачах, а не о параллельных). В идеале, это предел, который должен определять значение thread_pool_size, но на более мощных компьютерах его увеличение приносит пользу в определенной степени. Если вы не хотите, чтобы все новые подключения ожидали, и все в порядке с некоторой потерей производительности (опять же, это область, которую вы можете судить лучше всего, исходя из производительности вашего приложения под нагрузкой), неплохо было бы увеличить ее до 8..

Тем не менее, если вы установите 32-ядерный компьютер, его установка на 16 – ужасная идея, поскольку производительность значительно снижается. Кроличья нора пулов потоков в MySQL углубляется, но если вам интересно, вот более подробное обсуждение.

Ожидание и тайм-ауты

После того, как поток был создан и присоединен к клиенту, было бы напрасной тратой ресурсов, если бы клиент не отправлял запросы в течение следующих нескольких секунд (или минут). В результате MySQL завершает соединение после периода бездействия. Это контролируется переменной wait_timeout:

MariaDB [(нет)]> показывать переменные вроде ‘wait%’;
+—————+——-+
| Переменное_имя | Значение |
+—————+——-+
| wait_timeout | 28800 |
+—————+——-+

Полученное значение в секундах. Так что да, по умолчанию MySQL настроен на ожидание более 8 часов, прежде чем разорвать шнур! Это может быть хорошо, если у вас есть длительные запросы и вы действительно хотите их ждать (но даже тогда восемь часов – это абсурд!), Но в большинстве случаев это ужасно. Когда выполняется запрос, это значение устанавливается равным 0 (то есть навсегда), но обычно это значение должно быть установлено на очень низкое значение (например, 5 секунд или даже меньше), чтобы освободить соединение для других процессов..

Тюнинг временных таблиц

Давайте начнем с того, что временные таблицы в MySQL.

Предположим, у нас есть MySQL, который структурно выглядит следующим образом: TABLE A UNION (TABLE B INNER JOIN C). То есть мы заинтересованы в объединении таблиц B и C и последующем объединении результата с таблицей A. Теперь MySQL сначала приступит к объединению таблиц B и C, но прежде чем он сможет выполнить объединение, ему необходимо хранить эти данные где-нибудь. Вот где появляются временные таблицы – MySQL использует их для временного хранения данных на промежуточных этапах в сложных запросах, и после завершения запроса эта временная таблица отбрасывается.

Теперь вопрос: почему мы должны беспокоиться обо всем этом?

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

Есть две переменные, которые управляют этим поведением:

MariaDB [(нет)]> показать переменные типа ‘MariaDB [(нет)]> показать переменные вроде ‘tmp_table_size’;
+—————-+———-+

| Переменное_имя | Значение |

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

| tmp_table_size | 16777216 |

+—————-+———-+
«;
+———————+———-+
| Переменное_имя | Значение |
+———————+———-+
| max_heap_table_size | 16777216 |
+———————+———-+

MariaDB [(нет)]> показать переменные вроде ‘tmp_table_size’;
+—————-+———-+
| Переменное_имя | Значение |
+—————-+———-+
| tmp_table_size | 16777216 |
+—————-+———-+

Первый, max_heap_table_size, говорит нам, сколько ОЗУ может быть использовано таблицей MySQL (здесь «куча» относится здесь к структуре данных, используемой при распределении и управлении ОЗУ – подробнее Вот), а второй, tmp_table_size, показывает максимальный размер временной таблицы. В моем случае, оба установлены на 16 МБ, хотя я пытаюсь сделать так, чтобы увеличение только tmp_table_size не работало в целом, MySQL все равно будет ограничен max_table_heap_size.

Теперь наступает момент: если создаваемые временные таблицы превышают предел, разрешенный этими переменными, MySQL будет вынужден записать их на жесткий диск, что приведет к крайне низкой производительности. Теперь наша задача проста: постараться угадать наиболее точный размер данных для временных таблиц и настроить эти переменные до этого предела. Однако я хотел бы предостеречь от абсурда: установка этого предела на 16 ГБ (при условии, что у вас достаточно ОЗУ), когда большинство ваших временных таблиц имеют размер менее 24 МБ, является глупостью – вы просто тратите ОЗУ, которое может « использовались другими запросами или частями системы (кеш, например).

Вывод

Невозможно охватить все системные переменные в одной статье или даже все важные в одной статье, когда сама документация MySQL охватывает несколько тысяч слов. Хотя мы рассмотрели некоторые универсальные переменные здесь, я рекомендую вам взглянуть на системные переменные для используемого вами движка (InnoDB или MyISAM).

Мой самый желательный результат для написания этой статьи – убрать три вещи:

  1. MySQL – это типичная часть программного обеспечения, которая работает в пределах, установленных операционной системой. Это не таинственная программа, которая делает бог-знает-что и которую невозможно приручить. Кроме того, к счастью, не так сложно понять, как он настроен и управляется системными переменными..
  2.  Не существует единой настройки, которая сделает установку MySQL увеличенной. У вас нет другого выбора, кроме как смотреть в свои работающие системы (помните, что оптимизация наступает после того, как приложение запущено в производство, а не раньше), делать лучшие предположения и измерения и жить с реальностью, которая никогда не будет идеальной.
  3. Настройка переменных – не единственный способ оптимизировать MySQL – эффективная запись запросов – еще одна большая проблема, но об этом я расскажу в другой статье. Но дело в том, что даже если вы провели богоподобный анализ и настроили эти параметры в лучшую сторону, вам все равно удастся свести все на нет.

Какая ваша любимая системная переменная для настройки? ��

TAGS:

  • База данных

Jeffrey Wilson Administrator
Sorry! The Author has not filled his profile.
follow me
    Like this post? Please share to your friends:
    Adblock
    detector
    map