Настройка MYSQL под Linux

Сегодня мы поговорим с Вами о настройке mysql под linux (unix, freebsd) на VPS/VDS сервере. Я не буду касаться аспектов установки 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, после чего на английской раскладке нажимаем «О», зажимаем «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:

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:

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