Mysql оптимизация при помощи mysqltuner. Linux mysql оптимизация

Оптимизация MySQL на Linux своими руками

Сегодня мы поговорим с Вами о настройке mysql под linux (unix, freebsd) на VPS/VDS сервере. Я не буду касаться аспектов установки mysql на сервер, благо, в интернете достаточно информации. А постараюсь обобщить информацию по тонкой настройке параметров Mysql во время работы сервера баз данных, от которых напрямую зависит нагрузка на сервер во время наплыва посетителей. И приведу набор переменных, мониторинг значения которых сможет «подсказать», правильно ли Вы осуществили настройку mysql в том или ином случае.

Где же хранятся настройки mysql ?

На Вашем сервере настройки mysql могут находиться или в /etc/my.cnf, или в /etc/mysql/my.cnf, в крайнем случае используйте команду locate, find или им подобные с заданным именем файлаКак изменить настройки mysql?

Итак, файл найден, открыть его можно непосредственно через mc (midnight commander) + F4 или же используя VI(vim): vi my.cnf.

В случае с mc перед Вами будет старый добрый «Norton Commander», если же Вы не знаете, как пользоваться vi, Вам поможет man viКогда требуется настройка mysql? Анализ нагрузки mysql.

Подсоединитесь с правами администратора базы данных к консоли mysql или же выполните запрос любым удобным для Вас способом, например через phpmyadmin, запрос:

Код:

show processlist;

Внимание: все запросы к mysql для проверки значения (мониторинга) тех или иных параметров необходимо выполнять из под пользователя с правами администратора Вашего mysql сервера

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

Также показателем к оптимизации mysql может быть вывод команды top, выполненный через linux консоль:

Вводим в консоли Top, после чего на английской раскладке нажимаем o, зажимаем shift и нажимаем K до тех пор, пока %CPU не окажется вначале списка. Зажимаем shift и N и двигаем в начало списка %MEM. После чего нажимаем Enter. Если во главе списка у Вас оказывается mysql и показатели в столбце %CPU и %MEM довольно существенны (под 100% загрузка на процессор и почти полностью используется память), Вам точно необходима оптимизация mysql.Тонкая настройка mysql. Кэширование средствами mysql.

Перейдем к тюнингу mysql. Откройте файл my.cnf. Найдите раздел mysqld, все последующие переменные мы будем размещать именно в этом разделе, после строки:

[mysqld]

Настраиваем кэш MYSQL:

Внутренний кэш запросов mysql:

Query_cache_limit - «ограничиться» максимальным размером данных, которые можно поместить в кэш. Скажу Вам по опыту, в очень редких ситуациях mysql запросы будут возвращать данные размером большие 10 MB. Обычно и размера в 2-6 MB хватит с головой.

Например, укажите в my.cnf:

Код:

Query_cache_limit = 6MB

Query_cache_size – здесь Вы можете указать, сколько памяти выделить для внутреннего кэша запросов mysql. В кэш будет добавляться результат запроса целиком («таблица», полученная в результате запроса).

Например, укажите в my.cnf:

Код:

Query_cache_size = 64M

Выбор значения query_cache_size.

Совет первый: не указывайте слишком большое значение query_cache_size. Обычно указывается значение, равное одной десятой, одной пятой от размера доступной физической оперативной памяти.

Совет второй: указание также большого значения может существенно снизить эффективность использования кэша при частом обращении к нему при поиске данных. Тем более, если максимальный размер данных для помещения в кэш ограничен слишком «малым» значением query_cache_limit: поиск среди блоков небольших фрагментированных данных становится гораздо медленнее при большем объеме используемой памяти.Как оптимально подобрать значения для query_cache_size, query_cache_limit?

После настройки my.cnf и перезапуска mysql (обычно: /etc/init.d/mysql restart, /etc/rc.d/mysql restart).

Совет: впрочем, перезапускать mysql после изменения my.cnf нет надобности. Достаточно войти в консоль управления mysql с правами администратора или корневого пользователя root и выполнить запрос на изменение тех или иных переменных.

Код:

set @@global.[название] =[новое значение my.cnf];

Например, для query_cache_size:

Код:

set @@global.query_cache_size=64*1024*1024;

Какие mysql запросы не кэшируются (qcache_not_cached)?insert, update запросы, по существу они приводят к очистки кэша таблицы, для которой выполняются,запросы с применением пользовательских функций и процедур,запросы, использующие временные таблицы,запросы с включением локальных переменных,запросы, использующие SELECT ... FOR UPDATE, SELECT ... INTO OUTFILE, SELECT ... IN SHARE MODE, SELECT * FROM ... WHERE autoincrement_col IS NULL, SELECT ... INTO DUMPFILE,запросы без обращения к таблицам,запросы с включением некоторых недетерминированных функций: SLEEP(), NOW(),CURTIME(), LAST_INSERT_ID(), RAND() в случае, если пользователь имеет права только на часть таблицы: некоторые ее колонки и т.п.запросы с генерацией предупреждений (warnings).

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

Код:

SHOW GLOBAL STATUS LIKE 'Qcache%'

Здесь нас интересуют следующие переменные:

qcache_not_cached – количество запросов, не подлежащих кэшированию

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

qcache_hits – показывает количество результатов mysql запросов, извлеченных из кэша, без реального обращения к базе данных.

qcache_free_memory – показывает свободную «доступную» память для кэширования.

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

Эффективностью работы кэша является соотношение qcache_inserts к qcache_hits, которое показывает отношение результатов запросов помещенных в кеш, к результатам запросов, извлеченным из кеша.

Также «эффективность» работы кэширования можно рассчитать по формуле:

Qcache_hits / (Qcache_inserts + Qcache_not_cached)Как узнать, что query_cache_size был выбран верно?

На это обычно указывает qcache_free_memory, отличный от нуля. При этом желательно, чтобы параметр qcache_lowmem_prunes стремился к 0. Если же qcache_lowmem_prunes очень велик, рекомендую увеличить query_cache_size.Настраиваем многопоточность в mysql.

thread_concurrency – количество одновременных процессов, «обрабатывающих» конкурентные запросы к mysql. По документации советуют установить это значение, равное процессорам (ядрам) системы, умноженное на два. Но и советуют обращать внимание на количество винчестеров, которое использует система, чтобы избежать излишней нагрузки на файловую систему. Тоесть, если Ваш сервер оснащен четырьмя Intel Xeon по 2.8 ГГЦ с hyper Threading, тогда Вам следует установить значение в my.cnf:?1 Thread_concurrency = 8

Как понять, что значение thread_concurrency установлено верно?

Во время большой нагрузки на сервер после изменения параметра thread_concurrency (наплыва посетителей или при помощи эмуляции нагрузки (например, при помощи Apache Bench с другого сервера)) понаблюдайте за количеством свободной оперативной памяти при помощи той же команды top. Кроме этого обратите внимание на параметр в строке Cpu(s): %wa. Если значение этого параметра после изменения thread_concurrency выросло, и дошло до 60-90%, советую Вам снизить количество thread_concurrency. Обычно высокое значение %wa свидетельствует о возрастающей нагрузке на файловую подсистему (винчестер).

thread_cache_size – число потоков, которые сервер будет держать в кэше открытыми для обслуживания новых подсоединений. Можно установить равным значению max_connections + 1 (максимально возможному количеству соединений с б.д. +1). Но, чтобы достигнуть максимальной производительности, потребуется мониторинг переменной max_used_connections во время длительного промежутка времени (см. далее).

Также советую Вам просмотреть логии Mysql: обычно /var/log/mysql.log на предмет too many connections, когда mysql сервер отвергает подсоединение к базе данных из за того, что было достигнуто максимальное количество разрешенных подсоединений.

Например, при помощи команды grep, выполненной из ssh консоли linux:?1 grep 'Too many connections' /var/log/mysql.log | more

Совет: путь к логу mysql Вы сможете найти в файле my.cnf.

Если Вы нашли несколько строк с подобной ошибкой, тогда советую Вам увеличить значение max_connections, thread_cache_size, back_log, thread_concurrency:

Например для max_connections, thread_cache_size укажите в my.cnf:

Код:

max_connections = 500

Код:

thread_cache_size = 501

Как узнать текущее значение параметра MYSQL, если оно не указано в my.cnf?

Для этого в консоли mysql с правами администратора mysql можно выполнить запрос:

Код:

SHOW VARIABLES LIKE '[имя переменных или wild card]';

Например, текущее значение max_connections можно узнать так

Код:

SHOW VARIABLES LIKE 'max_connections';

Если Вы хотите вывести все переменные, содержащие в своем названии max, можно сформировать такой запрос в консоли mysql:

Код:

SHOW VARIABLES LIKE '%max%';

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

Код:

SHOW VARIABLES;

Как подобрать оптимальное значение thread_cache_size?

Выполните из консоли mysql с правами рута или администратора баз данных запрос:

Код:

SHOW STATUS LIKE 'Max_used_connections';

И постоянно отслеживайте переменную max_used_connections через определенные промежутки времени, ее значение. Если значение max_used_connections = 72, то устанавливаем значение thread_cache_size = 100 и выше (немногим больше max_used_connections).Настраиваем «очередь» конкурентных запросов back_log на подсоединение к mysql серверу.

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

table_cache (с версии Mysql с 5.1.3 - table_open_cache) — количество открытых таблиц для всех потоков. Дело в том, что открытие таблиц – очень ресурсоемкий процесс, поэтому есть смысл «держать» определенное количество таблиц открытыми в кэше. Если у Вас на сервере используется большое количество таблиц одновременно, можно начать со значения в 1000:

Укажите в my.cnf:

Код:

table_cache = 1024

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

Код:

SHOW STATUS LIKE 'Opened_tables';

Opened_tables характеризует число таблиц, открытых в обход кэша, желательно, чтобы ее значение стремилось к 0.Таблицы какого размера хранить в памяти?

max_heap_table_size — максимальный допустимый размер временной таблицы (типа MEMORY (HEAP)), хранящейся в памяти. При превышении этого раз мера таблица будет «создана» на жестком диске.

Например, укажите в my.cnf:

Код:

max_heap_table_size = 64MB

tmp_table_size — максимальный размер памяти для временных таблиц, создаваемых MySQL, которые «хранятся» в оперативной памяти. Если размер временной таблицы превышает указанный, тогда таблица будет «создана» на диске.

Попробуйте установить значение в my.cnf равным 32 - 128 МБ:

Код:

tmp_table_size = 64MB

Понаблюдайте также за состоянием created_tmp_disk_tables, ее значение должно стремиться к 0.

Для этого нужно выполнить запрос в консоли mysql:

Код:

SHOW STATUS LIKE 'Created_tmp_disk_tables';

Если значение created_tmp_disk_tables гораздо больше нуля, попробуйте увеличить параметр tmp_table_size

Об интересном: если Вам нужен качественный монитор, обратите внимание на обзор asus pa246q

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

Внимание! - Эта инструкция написана для совсем чайников в целях ознакомления, для опытных эти настройки производятся исходя из логов и личного опыта, без мануалов.С уважением 9CaraTT.

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

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

webhamster.ru

Оптимизация настроек MySQL – mysqld исчерпывает память Ubuntu Linux

Общий ответ от wiki сервера :

MySQL Tuner

MySQL Tuner – полезный инструмент, который будет подключаться к исполняемому экземпляру MySQL и предлагать рекомендации по его оптимальной настройке для вашей рабочей нагрузки. Чем дольше работает сервер, тем лучше может помочь совет mysqltuner. В рабочей среде подумайте о том, чтобы вы ожидали по крайней мере за 24 часа до запуска инструмента. Вы можете установить mysqltuner из репозиториев Ubuntu:

sudo apt-get install mysqltuner

Затем, как только он будет установлен, запустите его:

mysqltuner

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

-------- Рекомендации ----------------------------------------- ------------ Общие рекомендации: Запустите OPTIMIZE TABLE для дефрагментации таблиц для лучшей производительности Постепенно увеличивайте значение table_cache, чтобы избежать ограничений файлового дескриптора Переменные для настройки: key_buffer_size (> 1.4G) query_cache_size (> 32M) table_cache (> 64) innodb_buffer_pool_size (> = 22G)

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

Посмотрите раздел показателей производительности отчета mysqltuner. Максимально возможная память до 50%.
tmp_table_size , max_heap_table_size variable : сохранить эти равные и высокие.
join_buffer_size , увеличение в небольших количествах; он будет умножен на max_connections .
innodb_buffer_pool_size . Сделайте это высоко.

ubuntu.fliplinux.com

man linux - справочник linux » MySQL

В файле my.cnf (по умолчанию /etc/my.cnf) существует достаточно много настроек, которые влияют на быстродействие MySQL, при этом универсального рецепта нет. На серверах, где много оперативной памяти, можно попробовать уменьшить количество операций с жестким диском (которые будут выполняться дольше и потреблять больше ресурсов процессора) путем увеличения работы с RAM и так далее. В общем для разных конфигураций железа, будут разные конфигурации my.cnf, однако сама настройка my.cnf должна иметь общие принципы и зависимости. Правильная настройка требует концептуального понимания, процессов происходящих в MySQL, а эта информация не лежит на поверхности. Но все же оптимальные настройки прямо сейчас - так хочется! И я начал делать первые шаги в этом направлении…Для начала, обнаружил замечательный perl скрипт mysqltuner.pl, скачать который можно на сайте www.mysqltuner.comДанный скрипт, при запуске анализирует текущее состояние MySQL и дает рекомендации по настройкам.

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

Upd:Рекомендации - рекомендациями, однако все равно, необходимо понимать, что делаешь и взаимосвязи действий и последствий.Например, параметр table_cacheСкрипт tuning-primer.sh показывает процент использования данного кеша, например разрешено 100 таблиц, открыто 100 таблиц, таким образом кеш использован на 100%, а далее рекомендация гласит “вероятно вам стоит увеличить table_cache”. Здесь есть один нюанс, о котором не сказано в скрипте - table_cache влияет на общее количество файлов, которые могут быть mysql и как следствие на общее количество файлов, которые в какой-то момент смогут быть открытыми в операционной системе. Однако же есть общее ограничение в ОС, и слишком большое значение tablce_cache может привести к тому, что будут использованы все доступные файловые дескрипторы.Команда:

sysctl fs.file-max

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

sysctl -w fs.file-max=399086

Посмотреть же, сколько файлов открыто в системе в текущий момент можно командой:

lsof | wc -l

Это позволит сориентироваться в том, какое значение table_cache допустимо.

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

man-linux.ru

Mysql оптимизация при помощи mysqltuner

MySQLTuner (http://mysqltuner.com) представляет собой Perl скрипт,анализирующий статистику работы MySQL сервера и на ее основе дающийрекомендации по оптимизации настойки СУБД. И так, скачиваем сам скрипт:

wget http://mysqltuner.com/mysqltuner.pl chmod +x mysqltuner.pl

запускаем:

perl mysqltuner.pl

вас попросят ввести логин и пароль root-a. В итоге вы получите подобный результат:

-------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.1.56 [OK] Operating on 32-bit architecture with less than 2GB RAM -------- Storage Engine Statistics ------------------------------------------- [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 104M (Tables: 339) [--] Data in InnoDB tables: 15M (Tables: 40) [!!] Total fragmented tables: 90 -------- Performance Metrics ------------------------------------------------- [--] Up for: 16d 20h 48m 45s (2M q [1.535 qps], 84K conn, TX: 55B, RX: 429M) [--] Reads / Writes: 43% / 57% [--] Total buffers: 106.0M global + 9.2M per thread (500 max threads) [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability [!!] Maximum possible memory usage: 4.6G (231% of installed RAM) [OK] Slow queries: 0% (3/2M) [OK] Highest usage of available connections: 6% (32/500) [OK] Key buffer size / total MyISAM indexes: 16.0M/13.9M [OK] Key buffer hit rate: 100.0% (65M cached / 3K reads) [OK] Query cache efficiency: 74.4% (1M cached / 1M selects) [!!] Query cache prunes per day: 626 [OK] Sorts requiring temporary tables: 0% (289 temp sorts / 42K sorts) [!!] Joins performed without indexes: 52872 [OK] Temporary tables created on disk: 24% (24K on disk / 99K total) [OK] Thread cache hit rate: 99% (32 created / 84K connections) [!!] Table cache hit rate: 9% (305 open / 3K opened) [OK] Open file limit used: 17% (459/2K) [OK] Table locks acquired immediately: 99% (947K immediate / 947K locks) [!!] InnoDB data size / buffer pool: 15.1M/8.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Enable the slow query log to troubleshoot bad queries Adjust your join queries to always utilize indexes Increase table_cache gradually to avoid file descriptor limits Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** query_cache_size (> 64M) join_buffer_size (> 2.0M, or always use indexes with joins) table_cache (> 1024) innodb_buffer_pool_size (>= 15M)

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

Оригинал: eddnet.org/?p=1784

Запись опубликована в рубрике *mysql. Добавьте в закладки постоянную ссылку.

www.skleroznik.in.ua

Mysql оптимизация при помощи mysqltuner

wget http://mysqltuner.com/mysqltuner.pl chmod +x mysqltuner.pl

запускаем:

perl mysqltuner.pl

вас попросят ввести логин и пароль root-a. В итоге вы получите подобный результат:

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

Оригинал: eddnet.org/?p=1784

Запись опубликована в рубрике *mysql. Добавьте в закладки постоянную ссылку.