Сегодня мы поговорим с Вами о настройке 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, запрос:

Код:

1. 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:

Код:

1. Query_cache_limit = 6MB

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

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

Код:

1. 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 и выполнить запрос на изменение тех или иных переменных.

Код:

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

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

Код:

1. 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 или выполните запрос любым, удобным для вас способом:

Код:

1. 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:

Код:

1. max_connections = 500

Код:

1. thread_cache_size = 501

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

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

Код:

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

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

Код:

1. SHOW VARIABLES LIKE 'max_connections';

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

Код:

1. SHOW VARIABLES LIKE '%max%';

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

Код:

1. SHOW VARIABLES;

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

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

Код:

1. 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:

Код:

1. table_cache = 1024

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

Код:

1. SHOW STATUS LIKE 'Opened_tables';

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

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

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

Код:

1. max_heap_table_size = 64MB

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

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

Код:

1. tmp_table_size = 64MB

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

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

Код:

1. SHOW STATUS LIKE 'Created_tmp_disk_tables';

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

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

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

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

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

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