ГлавнаяОптимизацияОптимизация mysql

Оптимизация MySQL - что это и для чего нужно? Оптимизация mysql


Оптимизация производительности MySQL | Losst

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

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

Содержание статьи:

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

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

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

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

log-queries-not-using-indexes=1

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

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:

wget http://mysqltuner.pl/ -O mysqltuner.plwget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txtwget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv

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

perl ./mysqltuner.pl

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

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

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

query_cache_size=0query_cache_type=0query_cache_limit=1M

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

tmp_table_size=16Mmax_heap_table_size=16M

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

thread_cache_size=16

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

skip-name-resolve=1

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

innodb_buffer_pool_size=800M

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

innodb_log_file_size=200M

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

systemctl stop mariadb

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

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

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

systemctl start mariadb

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

systemctl status mariadb

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

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

mysql

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

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

Выводы

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

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

Оцените статью:

Загрузка...

losst.ru

Оптимизация сервера MySql. Шаг первый. Утилита mysqltuner.

Так повелось, что в мире хостинга ,  самой популярной базой данных, де факто стала база mysql. Простота установки , бесплатность, а главное, высокая скорость работы,  сделали ее одной из наиболее часто используемых баз данных в мире хостинга.  Она может многое, работает быстро, но, без должной изначальной настройки может стать очень узким местом. А отсюда может тянуться и медленная загрузка страниц сайта, и ошибки на страницах вида "Too many active connections".

После установки mysql на выделенный сервер, изначально его файл конфигурации размещается в  /etc/my.cnf  и выглядит весьма скромно.  В такой конфигурации работать то он будет, но про какую-либо оптимальную работу говорить не приходиться. Вот и попробуем немного улучшить наши настройки сервера mysql, для его более оптимальной работы. Первым делом нам необходимо взять более менее нормальный конфигурационный файл my.cnf, более подходящий для нашего сервера. А найти его можно в /usr/share/mysql. Там есть четыре необходимых нам файлика: my-small.cnf, my-medium.cnf, my-large.cnf и my-huge.cnf.  Выбираем под наш сервер, нашу память, наши запросы наиболее подходящий файл.  my-small.cnf - конфигурация для систем с обьемом памяти менее 64Mb, где mysql используется время от времени. Это не для нас. medium.cnf - чуть лучше, под mysql может выделяться до 64Мb памяти. В общем то это тоже обычно не для нас, разве что очень маломощные VDS, VPS. my-large.cnf - уже посерьезнее,  для систем с оперативной памятью от 512Мb. my-huge.cnf - для систем  с оперативной памятью 1-2Gb.  Выбираем одну из наиболее подходящих нам конфигураций и переписываем ее вместо текущей my.cnf . Например:

 cp /usr/share/mysql/my-large.cnf  /etc/my.cnf

После чего делаем рестарт сервера.

service mysqld restart

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

 

Одной из таких утилит, которая может помочь в выявлении проблемных мест конфигурации сервера mysql, является утилита mysqltuner. Утилита представляет собой скрипт написанный на языке perl. Инсталляции не требует. Его просто нужно скачать  

 wget http://mysqltuner.pl/

 и запустить 

perl mysqltuner.pl

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

 [root@host 1]# perl mysqltuner.pl

 

 >>  MySQLTuner 1.0.1 - Major Hayden <[email protected]>

 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/

 >>  Run with '--help' for additional options and output filtering

Please enter your MySQL administrative login: root

Please enter your MySQL administrative password:

 

-------- General Statistics --------------------------------------------------

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.0.82sp1-log

[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

 

-------- Storage Engine Statistics -------------------------------------------

[--] Status: -Archive +BDB -Federated +InnoDB -ISAM -NDBCluster

[--] Data in MyISAM tables: 19M (Tables: 90)

[!!] InnoDB is enabled but isn't being used

[!!] BDB is enabled but isn't being used

[!!] Total fragmented tables: 18

 

-------- Performance Metrics -------------------------------------------------

[--] Up for: 16m 37s (6K q [6.059 qps], 146 conn, TX: 54M, RX: 665K)

[--] Reads / Writes: 62% / 38%

[--] Total buffers: 298.0M global + 6.3M per thread (100 max threads)

[OK] Maximum possible memory usage: 929.2M (26% of installed RAM)

[OK] Slow queries: 0% (0/6K)

[OK] Highest usage of available connections: 5% (5/100)

[OK] Key buffer size / total MyISAM indexes: 256.0M/2.3M

[!!] Key buffer hit rate: 91.3% (1K cached / 101 reads)

[OK] Query cache efficiency: 97.6% (5K cached / 5K selects)

[OK] Query cache prunes per day: 0

[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 8 sorts)

[OK] Temporary tables created on disk: 5% (1 on disk / 17 total)

[OK] Thread cache hit rate: 96% (5 created / 146 connections)

[OK] Table cache hit rate: 95% (115 open / 121 opened)

[OK] Open file limit used: 21% (222/1K)

[OK] Table locks acquired immediately: 100% (286 immediate / 286 locks)

 

-------- Recommendations -----------------------------------------------------

General recommendations:

    Add skip-innodb to MySQL configuration to disable InnoDB

    Add skip-bdb to MySQL configuration to disable BDB

    Run OPTIMIZE TABLE to defragment tables for better performance

    MySQL started within last 24 hours - recommendations may be inaccurate

    Enable the slow query log to troubleshoot bad queries

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

Highest usage of available connections: 5% (5/100) 

Количество текущих соединений - 5, количество возможных соединений - 100. Параметр 100 - это параметр использующийся по умолчанию. Обычно этого значения маловато, именно из-за него появляются сообщения вида  "Too many active connections". Увеличим этот параметр до 300. Открываем файл /etc/my.cnf в любимом редакторе и в секции  [mysqld] добавляем строчку   

max_connections = 300 

Сохраняемся, выходим и перегружаем сервер mysql.  Повторно запустив mysqltuner увидим соответствующие изменения в выводе. 

 

Еще одним, наиболее важным параметром увеличения производительности mysql является key_buffer_size.  Параметр определяет размер общего для всех пользовательских процессов  буфера индексных блоков MyISAM таблиц. Обычно устанавливается в пределах 30-40% от общей выделенной под Mysql оперативной памяти.  

 

Следует помнить, что рекомендации утилиты mysqltuner будут тем более оптимальны, чем дольше работает без перезагрузок сервер mysql. Скажем так: минимальный интервал в общем то должен составлять не менее 24 часов, о чем и предупреждает утилита после своего запуска.

 

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

Удачи всем в деле оптимизации mysql и быстрых вам SELECT-ов. :-) 

 

 

 

dedicatesupport.com

Оптимизация MySQL – как сделать все правильно?

От автора: один мой знакомый решил оптимизировать свой автомобиль. Сначала одно колесо снял, потому крышу спилил, затем мотор… В общем, сейчас он пешком ходит. Это все последствия неправильного подхода! Поэтому, чтобы ваша СУБД продолжала «ездить», оптимизация MySQL должна проходить правильно.

Когда оптимизировать и зачем?

Лишний раз лезть в настройки сервера и изменять значения параметров (особенно, если не знаете, чем это может закончиться) не стоит. Если рассматривать данную тему с «колокольни» улучшения производительности веб-ресурсов, то она настолько обширная, что ей нужно посвящать целое научное издание в 7 томах.

Но такого писательского терпения у меня явно нет, да и у вас читательского тоже. Мы поступим проще, и постараемся лишь слегка углубиться в чащи оптимизации MySQL сервера и его составляющих. С помощью оптимальной установки всех параметров СУБД можно достигнуть нескольких целей:

Увеличить скорость выполнения запросов.

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

Бесплатный курс по PHP программированию

Освойте курс и создайте динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC

В курсе 39 уроков | 15 часов видео | исходники для каждого урока

Получить курс сейчас!

Уменьшить время ожидания загрузки страниц ресурса.

Снизить потребление серверных мощностей хостинга.

Снизить объем занимаемого дискового пространства.

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

Зачем настраивать сервер

В MySQL оптимизацию производительности следует начинать с сервера. Прежде всего, следует ускорить его работу и уменьшить время обработки запросов. Универсальным средством для достижения всех перечисленных целей является включения кэширования. Не знаете, «what is it»? Сейчас все поясню.

Если на вашем экземпляре сервера включено кэширование, то система MySQL автоматически «запоминает» введенный пользователем запрос. И в следующий раз при его повторении данный результат запроса (на выборку) будет не обработан, а взят из памяти системы. Получается, что таким образом сервер «экономит» время на выдачу ответа, и вследствие чего скорость реагирования сайта повышается. В том числе это касается и общей скорости загрузки.

В MySQL оптимизация запросов применима к тем движкам и CMS, которые работают на основе данной СУБД и PHP. При этом код, написанный на языке программирования, для генерации динамической веб-страницы запрашивает некоторые ее структурные части и содержимое (записи, архивы и другие таксономии) из БД.

Благодаря включенному кэшированию в MySQL выполнение запросов к серверу СУБД происходит намного быстрее. За счет чего и повышается скорость загрузки всего ресурса в целом. А это положительно отражается и на пользовательском опыте, и на позиции сайта в выдаче.

Включаем и настраиваем кэширование

Но давайте вернемся от «скучной» теории к интересной практике. Дальнейшую оптимизацию базы MySQL продолжим с проверки состояния кэширования на вашем сервере БД. Для этого с помощью специального запроса мы выведем значения всех системных переменных:

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

Чтобы получить нужные для оптимизации таблиц MySQL данные следует использовать какой-то более точно «нацеленный» запрос на конкретные строки. Например, такой:

SHOW VARIABLES LIKE '%query_cache%';

SHOW VARIABLES LIKE '%query_cache%';

Бесплатный курс по PHP программированию

Освойте курс и создайте динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC

В курсе 39 уроков | 15 часов видео | исходники для каждого урока

Получить курс сейчас!

Совсем другое дело.

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

have_query_cache – значение показывает «ВКЛ» кэширование запросов или нет.

query_cache_type – отображает активный тип кэша. Нам нужно значение «ON». Это говорит о том, что кэширование включено для всех видов выборки (команда SELECT). Кроме тех, в которых используется параметр SQL_NO_CACHE (запрещает сохранение информации об этом запросе).

У нас все настройки заданы правильно.

Отмеряем кэш под индексы и ключи

Теперь нужно проверить, сколько отведено оперативной памяти под индексы и ключи. Рекомендуется устанавливать этот важный для оптимизации БД MySQL параметр на 20-30% от объема оперативки, доступной для сервера. Например, если под экземпляр СУБД выделено 4 «гектара», то смело ставьте 32 «метра». Но все зависит от особенностей определенной базы и ее структуры (типов) таблиц.

Для установки значения параметра нужно отредактировать содержимое конфигурационного файла my.ini, который в Денвере находится по следующему пути: F:\Webserver\usr\local\mysql-5.5

Файл открываем с помощью Блокнота. Затем находим в нем параметр key_buffer_size и устанавливаем оптимальный для вашей системы ПК (в зависимости от «гектаров» оперативки) размер. После этого нужно перезапустить сервер БД.

В СУБД используется несколько дополнительных подсистем (нижнего уровня), и все основные их настройки также задаются в данном файле конфигурации. Поэтому, если нужно провести в MySQL InnoDB оптимизацию, то добро пожаловать сюда. Более подробно эту тему мы изучим в одном из наших следующих материалов.

Измеряем уровень индексов

Использование индексов в таблицах значительно повышает скорость обработки и формирования ответа СУБД на введенный запрос. MySQL постоянно «измеряет» уровень применения индексов и ключей в каждой БД. Для получения данного значения используйте запрос:

SHOW STATUS LIKE 'handler_read%'

SHOW STATUS LIKE  'handler_read%'

В полученном результате нас интересует значение в строке Handler_read_key. Если указанное там число маленькое, то это говорит о том, что индексы почти не используются в данной базе. А это плохо (как у нас ).

Также не забывайте об MySQL Explain оптимизации. С помощью данной команды СУБД объясняет нам, как оптимальнее построить введенный запрос. Она указывается в начале запроса на выборку. Например:

EXPLAIN SELECT * FROM `user_animal`

EXPLAIN SELECT * FROM `user_animal`

В столбце possible_keys MySQL выводятся возможные варианты индексов, которые можно использовать в данной таблице. В конце хотелось бы отметить, что «доводка» производительности – СУБД дело очень тонкое и скрупулезное. При этом каждый из движков, использующий MySQL имеет свой рецепт «тонкой настройки». Например, принципы оптимизации MySQL в Joomla могут быть категорически «не применимы» к другой CMS. И это стоит учитывать, иначе получится не оптимизация, а «конемобиль» какой-то!

Бесплатный курс по PHP программированию

Освойте курс и создайте динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC

В курсе 39 уроков | 15 часов видео | исходники для каждого урока

Получить курс сейчас!

Хотите изучить MySQL?

Прямо сейчас посмотрите 24-х часовой курс по базе данных MySQL!

Смотреть курс

webformyself.com

Производительность MySQL. Часть 1. Анализ и оптимизация запросов. Хостинг в деталях

Страницы сайта генерируются медленно? Возникают ошибки 502 bad gateway и 504 gateway timeout? Хостер говорит, что сайт создает слишком большую нагрузку на процессор? Скорее всего, проблемы связаны с базой данных. В этой статье рассмотрим вопросы оптимизации производительности MySQL.

Как понять, что дело именно в MySQL

Если сайт работает на популярной CMS, то можно воспользоваться отчетом по SQL-запросам, выполняемым при генерации страницы. Например, в Drupal такой отчет доступен в модуле Devel, в Joomla – в режиме отладки, в Wordpress – в расширении Debug bar. Если специальных инструментов нет, то можно до и после выполнения каждого SQL-запроса вызвать PHP-функцию microtime() и посчитать разность.

Drupal Devel

Если сайт размещается на VPS или выделенном сервере, аналогичные данные можно получить и непосредственно из MySQL. Например, из журнала медленных запросов.

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

С чего начать оптимизацию

Итак, вы определили, какие запросы выполняются при генерации страницы. Дальше возможны варианты:

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

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

Во втором случае имеет смысл заняться углубленным анализом логов и тонкой настройкой MySQL. На виртуальном хостинге сделать это не получится, только на VPS и выделенных серверах.

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

Кеш запросов

В кеше запросов (query cache) сохраняются пары запрос-ответ. Когда запрос уже есть в кеше, ответ отдается практически мгновенно. Если данные в таблицах меняются не слишком часто, происходит ощутимый прирост производительности (в противном случае кеш быстро сбрасывается).

По умолчанию кеширование выключено. Включить его можно, добавив в конфигурационный файл my.cnf строчку вида query_cache_size = 64M . Через переменную query_cache_size задается размер оперативной памяти, выделяемой под кеш, в данном случае - 64 мегабайта.

Теперь нужно перезапустить MySQL. Сделать это можно из некоторых панелей управления (в ISPmanager: Management tools - Services), либо по SSH из командной строки примерно так:/usr/local/etc/rc.d/mysql-server stop/usr/local/etc/rc.d/mysql-server start

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

Есть еще несколько переменных для настройки кеша:

Пример my.cnf для небольшого VPS:query_cache_size = 64Mquery_cache_limit = 2Mquery_cache_type = 1query_cache_min_res_unit = 2K

Посмотреть текущее состояние кеша можно в phpMyAdmin на вкладке Status, либо из командной строки:

# mysql -u root -p Password: ******** mysql> SHOW GLOBAL STATUS LIKE ‘Qcache%’; +----------------------------+------------+ | Variable_name | Value | +----------------------------+------------+ | Qcache_free_blocks | 130 | | Qcache_free_memory | 56705448 | | Qcache_hits | 57092 | | Qcache_inserts | 10412 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 5036 | | Qcache_queries_in_cache | 1023 | | Qcache_total_blocks | 2409 | +----------------------------+------------+ 8 rows in set (0.01 sec)

Долю закешированных запросов от их общего числа можно посчитать по формуле Qcache_hits / (Com_select + Qcache_hits). Степень использования кеша - Qcache_hits / Qcache_inserts.

О нюансах работы кеша MySQL можно почитать на mysqlperformanceblog.com (англ.)

Оптимизация отдельных запросов

Чтобы оптимизировать тяжелый запрос, сначала его нужно исследовать. Для этого допишите перед SELECT слово EXPLAIN, и MySQL покажет план выполнения запроса. В первую очередь интерес представляет информация об использовании индексов.

Результат работы оператора EXPLAIN

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

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

Индексы могут быть составными, в этом случае важен порядок столбцов.

Разбирая вывод EXPLAIN, обратите особое внимание на столбцы

Описание всех значений и пример оптимизации запроса можно посмотреть в документации.

Добавить индексы можно из phpMyAdmin или с помощью запросов вида ALTER TABLE table_name ADD INDEX index_name (column_name)

Журнал медленных запросов

Если определить тяжелые запросы «на глаз» не получается, нужно собрать более обширную статистику. В этом поможет журнал медленных запросов (slow query log).

Для включения журнала в MySQL, начиная с версии 5.1.29, задайте переменной slow_query_log значение 1 или ON; для отключения журнала - 0 или OFF. В более старых версиях используется log-slow-queries = /var/db/mysql/slow_queries.log (путь можно задать другой).

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

Пара полезных дополнительных настроек:

Пример для записи в журнал всех запросов, выполняющихся дольше 50 миллисекунд:slow_query_log = 1slow_query_log_file = /var/db/mysql/slow_queries.loglong_query_time = 0.05log-queries-not-using-indexes = 1

Пример для старых версий MySQL, все запросы дольше 1 секунды:log-slow-queries = /var/db/mysql/slow_queries.loglong_query_time = 1

Для анализа журнала используются утилиты mysqldumpslow, mysqlsla и mysql_slow_log_filter. Они парсят журнал и выводят агрегированную информацию о медленных запросах.

mysqldumpslow – утилита из состава MySQL. Вызывается таким образом: mysqldumpslow [параметры] [файл_журнала ...] . Пример:

mysqldumpslow

Reading mysql slow query log from /usr/local/mysql/data/mysqld51-apple-slow.logCount: 1 Time=4.32s (4s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhostinsert into t2 select * from t1

Count: 3 Time=2.53s (7s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhostinsert into t2 select * from t1 limit N

Count: 3 Time=2.13s (6s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhostinsert into t1 select * from t1

Count – сколько раз был выполнен запрос данного типа. Time – среднее время выполнения запроса, дальше в скобках – суммарное время выполнения всех запросов данного типа.

Некоторые параметры mysqldumpslow:

mysqlsla – еще одна утилита для анализа логов MySQL с аналогичной функциональностью. Пример использования:

mysqlsla -lt slow /tmp/slow_queries.log

Подробности в документации.mysql_slow_log_filter - perl-скрипт с похожей функциональностью. Пример использования:

tail –f mysql-slow.log | mysql_slow_log_filter –T 0.5 –R 1000

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

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

Вторая часть статьи будет посвящена тонкой настройке MySQL. Материал находится в разработке.

---Евгений Демин, http://unixzen.ruДмитрий Сергеев, http://hosting101.ru

hosting101.ru

Как оптимизировать MySQL сервер?

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

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

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

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

Как выглядит MySQL-оптимизация производительности?

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

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

Чтобы оптимизировать таблицы, убрать фрагментарность данных в них, а также избавиться от возникающих из-за добавления и удаления записей потерь пространства, нужно использовать следующую команду в режиме восстановления: shell> myisamchk -r tbl_name, где tbl_name - имя таблицы, которую нужно оптимизировать.

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

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

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

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

Давайте рассмотрим каждый из этих вариантов подробнее.

Оптимизация настроек сервера при помощи MySQLTuner

MySQLTuner - это скрипт, написанный на языке Perl, предназначенный для выявления проблемных мест в настройке сервера MySQL. Его не нужно устанавливать, достаточно просто скачать и запустить. Для запуска используется команда perl mysqltuner.pl. После запуска скрипт запросит логин и пароль администратора сервера, а после ввода проведет анализ и выдаст результат в нескольких секциях:

Следуя рекомендациям этой утилиты можно оптимизировать работу вашего сервера MySQL. Основное внимание следует уделять символам [!!] и последней секции, то есть блоку -Recommendations-. Именно здесь показаны рекомендуемые настройки.

В секции -Recommendations- есть два поля - General recommendations и Variables to adjust. Все параметры, указанные после Variables to adjust, нужно найти в файле конфигурации my.cnf и изменить на рекомендуемые. После внесения всех изменений следует перезапустить сервер MySQL для корректного сохранения конфигурации.

Оптимизация таблиц при помощи phpMyAdmin

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

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

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

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

Оптимизация запросов при помощи Slow query log

Очевидно, что для оптимизирования запросы нужно сначала найти, и новичку достаточно сложно самостоятельно определить, "тяжелый" этот запрос или нет. Именно для этого существует Slow query log - журнал медленных запросов.

Для включения этого журнала нужно:

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

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

У вас нет необходимых навыков или знаний для того, чтобы самостоятельно заниматься оптимизацией сайта на хостинге RigWEB? Вы можете обратиться за помощью к нашим специалистам! Сотрудники техподдержки в течение 30 минут ответят на любые ваши вопросы в рамках своей компетенции. Не стесняйтесь задавать вопросы, особенно если вам предстоит MySQL-оптимизация больших таблиц.

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

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

Заказать VDS

VPS/VDS за 375 руб. от RigWEB.RU с администрированием

VPS/VDS серверы

Помогла ли вам статья?

Да Нет 23

раз уже помогла

Please enable JavaScript to view the comments powered by Disqus.

rigweb.ru

Оптимизация MySQL с помощью настроек в my.cnf

Хочу рассказать о настройках, которые применял, но не все из них помогли, просто опишу их.

Введение

Как я успел разобраться, при каждом соединении с MySQL создается mysqld (демон), который и обрабатывает все запросы соединения. Вот в блоке [mysqld] описывается именно настройки таких демонов.

Итак, давайте рассмотрим настройки демона [mysqld].

Выставить кодировку по умолчанию можно так:

character-set-server    = utf8collation-server        = utf8_unicode_ci

Защитить сервер от кривых рук программиста, способного join`ом на 10 миллионов записей похоронить даже 4-х процессорный сервер, можно так:

max_join_size = 1000000

Буфер можно выставить 25% от общего объема оперативной памяти:

key_buffer_size     = 2048M

как я понял, это буфер обмена для всех демонов, т.е. реально будет: key_buffer_size / кол-во демонов = ???M

Размер стека для каждого потока (демона):

thread_stack            = 512K

стек - это место для хранения списка задач (открыть таблицу, выполнить запрос, закрыть и т.п.)

Кол-во потоков, которые сервер должен поместить в кэш для повторного использования:

thread_cache_size     = 32

т.е. если к примеру есть часто повтояющийся SELECT * FROM myTable, то он попадет в кэш, чтобы не выполняться каждый раз.

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

tmp_table_size          = 512M

Установить максимальный размер таблиц типа MEMORY ( HEAP ) можно так:

max_heap_table_size     = 256M

Размер буфера, выделяемого демону при выполнении операций сортировки. Для ускорения операций ORDER BY, GROUP BY рекомендуется увеличить данное значение

sort_buffer_size        = 4M

Размер буфера выделяемого для сортировки MyISAM индексов с помощью оператора REPAIR TABLE или при создании индексов операторами CREATE TABLE, ALTER TABLE:

myisam_sort_buffer_size = 256M

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

read_buffer_size        = 4M

Размер буфера, выделяемого для чтения строк после сортировки, что-бы избежать повторного поиска на диске. Увеличение значения данной переменной может существенно увеличить эффективность конструкции ORDER BY. Имейте в виду, так как данный буфер выделяется для каждого демона, не следует устанавливать чересчур большое значение.

read_rnd_buffer_size    = 8M

Размер буфера использующегося при операциях объединения таблиц ( если не используются индексы ). Буфер устанавливается один раз во время каждой операции объединения

join_buffer_size        = 8M

Величина буфера, который используется для индексов, всех демонов. Если используется много DELETE или INSERT запросов к таблицам с большим кол - индексов, то увеличение значения повысит скорость выполнения таких запросов. Для достижения еще большей скорости нужно использовать LOCK TABLES. Советуют устанавливать не больше чем 1/3 озу и не больше объема всех б.д.

key_buffer = 2048M

Максимально количество соединений клиентов с сервером

max_connections        = 35

Задает максимально количество неудачных попыток подключения с хоста. Значение по-умолчанию 10. При достижении данного значения, хост блокируется. Разблокировать хост можно с помощью: mysql> FLUSH HOSTS

max_connect_errors      = 50

Максимальное число одновременных подключений для одной учетной записи MySQL. Значение по-умолчанию 0, отсутствие каких-либо ограничений

max_user_connections    = 25

table_cache старое название для переменной table_open_cache, в нем указывается количество открытых таблиц для всех демонов. Увеличение значения приведет к увеличению количества используемых дескрипторов файла. Советуют рассчитывать по формуле: количество одновременных соединений * количество открытых таблиц в соединении. Т.е. для каждого соединения используется свои ячейки из кэша. Для проверки можно запустить mysqltuner.pl

table_cache            = 128

Количество одновременно запускаемых демонов, советуют формулу: количество ядер процессора умножаем на 2

thread_concurrency = 16

Размер буфера для соединений, устанавливаемый сервером в промежутках между запросами

net_buffer_length       = 1024

Максимальный объем одного SQL-запроса к серверу. Изначально буфер сообщений имеет размер net_buffer_length и при необходимости, автоматически увеличивается до значения данной переменной.

max_allowed_packet      = 512M

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

max_sort_length         = 512

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

query_cache_limit = 2M

Полезная настройка: объем памяти, выделенной для кэширования результатов запросов. По-умолчанию данный кэш отключен, значение - 0

query_cache_size        = 16M

Полезная настройка: вид кэширования:

0 - ничего не кэшировать (по-умолчанию)1 - кэшировать все запросы, кроме SELECT SQL_NO_CACHE2 - кэшировать только запросы, начинающихся с конструкции SELECT SQL_CACHE

query_cache_type  = 2

Настройки innodb (извините, что без пояснений, просто оставлю их тут, чтобы не забыть):

innodb_data_home_dir = /var/lib/mysqlinnodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextendinnodb_log_group_home_dir = /var/lib/mysqlinnodb_buffer_pool_size         = 64Minnodb_additional_mem_pool_size = 32Minnodb_file_io_threads          = 8innodb_lock_wait_timeout        = 50innodb_log_buffer_size          = 8Minnodb_flush_log_at_trx_commit  = 2innodb_flush_method             = O_DIRECT

Битрикс рекомендует:

transaction-isolation   = READ-COMMITTEDdefault-character-set = utf8

Настройки блока для создания дампов

[mysqldump]

default-character-set = utf8

[mysql]

Старое название следующей настройки: character-set-server = utf8 выдает ошибку: /usr/bin/mysql_upgrade: unknown variable 'character-set-server=utf8

default-character-set = utf8

Надеюсь, кому-нибудь помог разобраться, удачки в освоении MySQL.

yapro.ru

Оптимизация конфигурации MySQL | MySQL

Данная статья является 2 из 3 частей цикла «Оптимизация MySQL»:

  1. Оптимизация индексов MySQL;
  2. Оптимизация конфигурации MySQL;
  3. Оптимизация проблемных моментов MySQL.

Вступление

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

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

Мы будем использовать в основном инструменты из Percona Toolkit. В этой статье я расскажу об оптимизации конфигурации.

Как изменить конфигурацию MySQL

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

Однако если вы хотите внести изменения во время выполнения, можно использовать запросы SET GLOBAL и SET SESSION. Обратите внимание, что не все переменные конфигурации доступны для изменения во время выполнения, и данные изменения не будут постоянными.

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

Находим возможности для улучшения конфигурации

Percona Toolkit выпустили инструмент под названием pt-variable-advisor. С его помощью вы можете проанализировать текущую конфигурацию MySQL и получить обратные данные. pt-variable-advisor проверит конфигурацию на основе заданного набора правил, определяемого Percona Toolkit.

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

# WARN delay_key_write: MyISAM index blocks are never flushed until necessary. (# ПРЕДУЖПРЕДЕНИЕ delay_key_write: Блок индекса MyISAM никогда не отключается без необходимости.) # WARN innodb_log_file_size: The InnoDB log file size is set to its default value, which is not usable on production systems. # ПРЕДУПРЕЖДЕНИЕ innodb_log_file_size: Размера лог-файла InnoDB по умолчанию задан так, что это отражается на производительности системы. # NOTE log_warnings-2: Log_warnings must be set greater than 1 to log unusual events such as aborted connections. # ОБРАТИТЕ ВНИМАНИЕ log_warnings-2: Значение предупреждений логов должно быть задано числом больше одного, чтобы система могла записывать логи необычных событий, таких как оборванные соединения. # NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB. # ОБРАТИТЕ ВНИМАНИЕ max_binlog_size: Значение max_binlog_size меньше, чем 1 ГБайт по умолчанию. # NOTE max_connect_errors: max_connect_errors should probably be set as large as your platform allows. # ОБРАТИТЕ ВНИМАНИЕ max_connect_errors: значение max_connect_errors может быть установлено настолько большим, насколько позволяет ваша платформа. # WARN slave_net_timeout: This variable is set too high. # ПРЕДУПРЕЖДЕНИЕ slave_net_timeout: Задано слишком высокое значение переменной. # NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it. # ОБРАТИТЕ ВНИМАНИЕ sort_buffer_size-1: Значение переменной sort_buffer_size, как правило, следует оставлять установленным по умолчанию, кроме тех случаев, когда эксперты считают, что им необходимо его изменить. # NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later. # ОБРАТИТЕ ВНИМАНИЕ innodb_data_file_path: Автоматически расширяемые файлы InnoDB используют слишком много дискового пространства, и это позже очень трудно исправить. # NOTE innodb_flush_method: Most production database servers that use InnoDB should set innodb_flush_method to O_DIRECT to avoid double-buffering, unless the I/O system is very low performance. # ОБРАТИТЕ ВНИМАНИЕ innodb_flush_method: Для большинства серверов баз данных, использующих InnoDB следует задавать для innodb_flush_method значение O_DIRECT, чтобы избежать двойной буферизации, кроме I/O систем, которые имеют очень низкую производительность. # WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible. # ПРЕДУПРЕЖДЕНИЕ log_bin: Сейчас двоичное ведение журнала отключено, поэтому восстановление данных на определенный момент времени и репликация невозможны.

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

MySQLTuner

Еще один инструмент, который мы будем использовать, он не является частью Percona Toolkit, это MySQLTuner. Этот инструмент проанализирует производительность вашего сервера MySQL и предложит изменения.

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

Давайте запустим ./mysqltuner.pl и посмотрим, насколько производительна наша текущая конфигурация MySQL:

>> MySQLTuner 1.2.0 - Major Hayden <[email protected]> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [OK] Logged in using credentials from debian maintenance account. -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.5.35-0ubuntu0.12.04.2-log [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 941M (Tables: 399) [--] Data in InnoDB tables: 2G (Tables: 891) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [!!] Total fragmented tables: 913 -------- Performance Metrics ------------------------------------------------- [--] Up for: 4d 1h 41m 1s (64K q [0.183 qps], 791 conn, TX: 119M, RX: 71M) [--] Reads / Writes: 71% / 29% [--] Total buffers: 192.0M global + 2.8M per thread (151 max threads) [OK] Maximum possible memory usage: 607.2M (10% of installed RAM) [OK] Slow queries: 4% (3K/64K) [OK] Highest usage of available connections: 3% (6/151) [!!] Key buffer size / total MyISAM indexes: 16.0M/309.5M [!!] Key buffer hit rate: 86.9% (14M cached / 1M reads) [OK] Query cache efficiency: 58.9% (31K cached / 53K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 808 sorts) [OK] Temporary tables created on disk: 6% (99 on disk / 1K total) [OK] Thread cache hit rate: 99% (6 created / 791 connections) [!!] Table cache hit rate: 6% (400 open / 5K opened) [OK] Open file limit used: 3% (695/20K) [OK] Table locks acquired immediately: 99% (47K immediate / 47K locks) [!!] InnoDB data size / buffer pool: 2.0G/128.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Increase table_cache gradually to avoid file descriptor limits Variables to adjust: key_buffer_size (> 309.5M) table_cache (> 400) innodb_buffer_pool_size (>= 2G)

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

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

И посмотреть, улучшилось ли состояние системы, или нужно дополнительное вмешательство.

Сравнение конфигурации на нескольких серверах

Если вы работаете с несколькими серверами MySQL, вы возможно захотите сконфигурировать их все одинаково. Проверка вручную может потребовать много труда и времени. К счастью, в Percona существует инструмент под названием pt-config-diff.

С помощью него можно взять два конфигурационных файла и сравнить их. Рассмотрите следующие два примера:

[mysqld] log-slow-queries = /var/log/mysql/mysql-slow.log long_query_time = 1 log-queries-not-using-indexes = 1 [mysqld] log-slow-queries = /var/log/mysql/mysql-slow.log long_query_time = 1 log-queries-not-using-indexes = 0

Если теперь мы запустим pt-config-diff /server1/etc/mysql/my.cnf /server2/etc/mysql/my.cnf, то на выходе получим уже одинаковые результаты:

1 config difference Variable /server1/etc/mysql/my.cnf /server2/etc/mysql/my.cnf ========================= =========== ==== log_queries_not_using_... 1 0

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

Заключение

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

Используете ли вы какие-либо инструменты для оптимизации конфигурации MySQL? Если да, то какие именно? Мне очень хотелось бы услышать ваши мнения в комментариях.

Перевод статьи «Optimizing MySQL Configuration» был подготовлен дружной командой проекта Сайтостроение от А до Я.

www.internet-technologies.ru

Prostoy-Site | Все права защищены © 2018 | Карта сайта