Где же хранятся настройки 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:

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

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

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

Больше информации о веб технологиях можно узнать из нашего перечня всех статей на сайте:

Комментарии   

 
0 #3 Александр 18.09.2014 06:55
Посоветуйте что нибудь (это за 2 дня):
Qcache_free_blocks 1829
Qcache_free_memory 3840720
Qcache_hits 517982
Qcache_inserts 301320
Qcache_lowmem_prunes 61647
Qcache_not_cached 24271
Qcache_queries_in_cache 4403
Qcache_total_blocks 26995

При таких настройках:
Query_cache_limit = 6MB
Query_cache_siz e = 256М
max_connections = 500
thread_cache_size = 501

Посещаемость около 7к уников. CMS joomla 2.5. Я так понимаю, что нужно увеличить Query_cache_siz e , но он и так 256м, у меня вся база данных 30м
Цитировать
 
 
0 #2 blackFFFFFF 05.07.2013 11:33
Это не время подключения к базе. Это время на выполнение скрипта. Попробуйте:

ini_set('max_execution_t ime',1800);

Для nginx также будет ограничено временем коннекта к socket у (до 120 секунд максимум)

Также сервер может запрещать переопределение через php скрипт переменных внутреннего окружения.

Можете попробовать через .htaccess
(если появятся ошибки 500, то удалите эти строчки):

php_value max_execution_t ime 1800
Цитировать
 
 
0 #1 Егор 05.07.2013 06:27
Подскажите, пожалуйста, как можно изменить время подключения к базе. Имеется php скрипт, который запускается по заданию в cron. Но до конца не обрабатывается. В скрипте mysql запрос результат, которого порядка 5 млн. записей. На самом сервере этот запрос занимает около 3 минут. А через php скрипт запрос не выполняется.
Цитировать
 

Добавить комментарий


Яндекс.Метрика