30 мая 2008 в 2:41
Задача
Проверить параметры и настройки базы данных, чтобы найти возможности для улучшения производительности.
Решение
Начнём с простых методов анализа.
Варианты
- Стандартные возможности Друпал
- Скрипт "tuning primer" (Matt Mongomery)
- Включение полного лога запросов
Включение полного лога запросов
Включение полного лога в my.cnf:
Стандартные возможности Друпал
Откройте в браузере страницу "SQL"
Друпал 6: admin/reports/status/sql
Друпал 5: admin/logs/status/sql
Вы увидите 3 таблицы:
- Счетчики команд - статистика запросов к базе данных
- Производительность запросов - должно быть по нулям. Иначе нужно что-то менять.
- Информация о кеше запросов - можно проверить использует ли MySQL кеш для запросов. Лучше, если использует.
Скрипт "tuning primer" (Matt Mongomery)
Matt Mongomery из MySQL написал отличный скрипт "tuning primer", который предоставляет общие настройки для улучшения производительности.
Скрипт можно скачать: http://www.day32.com/MySQL/
База данных MySQL должна быть в работе как минимум 48 часов, перед запуском скрипта, чтобы предложенные настройки были наилучшими.
Скрипт выдаёт рекомендации по таким вопросам:
- Slow Query Log
- Max Connections
- Worker Threads
- Key Buffer
- Query Cache
- Sort Buffer
- Joins
- Temp Tables
- Table (Open & Definition) Cache
- Table Locking
- Table Scans (read_buffer)
- Innodb Status
Запуск скрипта
Для использования этого скрипта нужен SSH (шел) доступ к серверу...
Выгрузить скрипт на сервер
Установить права доступа: chmod u+x tuning-primer.sh
Запустить скрипт из командной строки: bash /path-to-script/tuning-primer.sh или sh /path-to-file/tuning-primer.sh
Пример работы скрипта №1
Были вырезаны некоторые рекомендации, чтобы уместить на одной странице:
— MYSQL PERFORMANCE TUNING PRIMER –
- By: Matthew Montgomery -
Uptime = 5 days 10 hrs 46 min 5 sec
Avg. qps = 4
Total Questions = 2020809
Threads Connected = 1
Server has been running for over 48hrs.
It should be safe to follow these recommendations
———– вырезано ————–
QUERY CACHE
Query cache is enabled
Current query_cache_size = 8 M
Current query_cache_used = 7 M
Current query_cach_limit = 1 M
Current Query cache fill ratio = 89.38 %
However, 254246 queries have been removed from the query cache due to lack of memory
Perhaps you should raise query_cache_size
MySQL won't cache query results that are larger than query_cache_limit in size
———– вырезано ————–
Код выше показывает, что нужно увеличить кеш запросов. Он был равен 8Мб, но он очищается слишком часто.
———– вырезано ————–
TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 32 M
Of 35170 temp tables, 74% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.
———– вырезано ————–
Пример работы скрипта №2
--20:41:15-- http://www.day32.com/MySQL/tuning-primer.sh
=> `tuning-primer.sh'
Resolving www.day32.com... 209.61.186.89
Connecting to www.day32.com|209.61.186.89|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 34,309 (34K) [application/x-sh]
100%[====================================>] 34,309 --.--K/s
20:41:15 (279.14 KB/s) - `tuning-primer.sh' saved [34309/34309]
[root@server1 root]# bash tuning-primer.sh
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -
MySQL Version 3.23.58
Uptime = 23 days 21 hrs 28 min 54 sec
Avg. qps = 17
Total Questions = 36204146
Threads Connected = 5
Server has been running for over 48hrs.
It should be safe to follow these recommendations
To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html
SLOW QUERIES
Current long_query_time = 10 sec.
You have 526 out of 36204146 that take longer than 10 sec. to complete
The slow query log is NOT enabled.
Your long_query_time may be too high, I typically set this under 5 sec.
MAX CONNECTIONS
Current max_connections = 200
Current threads_connected = 5
Historic max_used_connections = 166
The number of used connections is 83% of the configured maximum.
Your max_connections variable seems to be fine.
WORKER THREADS
Current thread_cache_size = 0
Current threads_cached = 0
Current threads_per_sec = 1
Historic threads_per_sec = 0
Your thread_cache_size is fine
MEMORY USAGE
Max Memory Ever Allocated : 28 M
Configured Max Memory Limit : 30 M
Total System Memory : 2028 M
KEY BUFFER
Current MyISAM index space = 239 M
Current key_buffer_size = 7 M
Key cache miss rate is 1 / 126
Key buffer fill ratio = Unknown %
Your key_buffer_size seems to be fine
QUERY CACHE
You are using MySQL 3.23.58, no query cache is supported.
I recommend an upgrade to MySQL 4.0 or better
SORT OPERATIONS
Current sort_buffer_size = 2 M
Current record/read_rnd_buffer_size = 128.00 K
Sort buffer seems to be fine
JOINS
Current join_buffer_size = 128.00 K
You have had 108378 queries where a join could not use an index properly
You should enable "log-long-format"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
!>join_buffer_size to accomidate larger joins in one pass.
TABLE CACHE
Current table_cache value = 64 tables
You have a total of 842 tables
You have 64 open tables.
Current table_cache hit rate is 0%, while 100% of your table cache is in use
You should probably increase your table_cache
TEMP TABLES
Current tmp_table_size = 32 M
48% of tmp tables created were disk based
Perhaps you should increase your tmp_table_size
TABLE SCANS
Current read_buffer_size = 128.00 K
Current table scan ratio = 43 : 1
read_buffer_size seems to be fine
TABLE LOCKING
Current Lock Wait ratio = 1 : 3294
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform frequent updates consider setting 'low_priority_updates=1'
[root@server1 root]# free -m
total used free shared buffers cached
Mem: 1001 881 119 0 81 548
-/+ buffers/cache: 252 749
Swap: 1027 58 969
Внесение изменений в my.cnf
- Нужно сделать резервную копию:
cp -a /etc/my.cnf /etc/my.cnf.bak - Внести изменения
- Перезапустить сервер базы данных, чтобы внесённые изменения вступили в силу
Это можно сделать через панель управления или командную строку (в некоторых UNIX:/etc/rc.d/init.d/mysqld restart) - Через несколько дней снова запустите скрипт, чтобы убедиться, что вы ничего не упустили.
Использованные материалы
Полезные ссылки
Tools, tips, and links on optimizing mysql
Using a MySQL Performance Tuning Analyzer Script
Tuning MySQL for Drupal
Tuning a MySQL server in 5 minutes
MySQL 6.0 Reference Manual :: 5 MySQL Server Administration :: 5.1 The MySQL Server :: 5.1.3 System Variables
Оригинал статьи: Анализ производительности базы данных MySQL
Комментарии
Спасибо. Еще есть полезный модуль DB Maintenance (Executes an OPTIMIZE TABLE query on MyISAM, InnoDB, or BerkeleyDB database tables).
Да, я использую DB Maintenance для оптимизации таблиц, но он ведь не помагает анализировать таблицы... Или я не прав?
Вы правы.
полезно
поставьте размеры буферов myisam_sort_buffer_size join_buffer_size read_buffer_size sort_buffer_size 1 мегабайт и будете приятно удивлены )
Спасибо за полезную статью.
И еще такой вопрос: я посмотрел Производительность запросов (средставами друпал), и у меня в строчке Sort_scan значение 2, а должен быть 0, что это обозначает, и как можно исправить?
И кэш у меня почему-то не используется, как его включить?
Буду очень благодарен если подскажите хороший русскоязычный ФАК по настройке и оптимизации MySQL (желательно без избыточной теории).
mysql оптимизирована разработчиками и вряд ли кто то это сделает лучше, хотя есть случаи когда на сервере мало памяти и оно по причине наплыва посетителей или роботов виснет - вот для этого случая и надо уменьшить размер буферов.
Спасибо, полезно
все большие таблицы в иннодб, кэш иннодб больше чем размер всех таблиц-и вот оно,щастье. без всяких скриптов
А что, в этом случае быстрее будет?
у меня в
Производительность запросов
Переменная Значение Описание
Sort_scan 10 Количество сортировок, выполненных без использования индекса; должно быть нулем.
Table_locks_immediate 1562967 Количество раз, когда блокировка была нужна немедленно.
Table_locks_waited 802 Количество раз, когда серверу приходилось ожидать блокировку.
Что это значит полтора лимона?
Полтора лимона не значат в принципе ничего
А вот второе - это значит, что MyISAM даёт сбои
в иннодб можно задавать размер лога что бы не разростался - полностью отключать лог не нужно - быстродействие это не добавит, а восстановить базу после бага линукс ядра который приводит к сбоям sata винчестера - будет проще
иннодб медленнее но ее в случае наличия лога mysql-у проще исправить, хотя хранение всех баз в одном файле это плохая идея - грохнулся файл - и нет всех баз сразу - у меня было что при чтении этого файла mysql вываливалась в accertion failure ( и лога небыло правда потом как-то восстановить удалось.
иннодб медленнее
как обычно полный бред. курим доки про локи в myisam и innodb и приходим к пониманию того, что на нагруженных сайтах myisam для многих таблиц не работает
Мне кажется, что проще себе нормальный дедик для сайта взять. В этом случае прирост производительности будет большим.
Есть интенсивный путь развития, а есть экстенсивный.
Интенсивный - взять дедик, а экстенсивный - выжать максимум из того, что есть.
И тот, и другой имеют право на существование и в каждом конкретном случае нужно выбрать лучший путь.
вообще-то, настройки mysql по умолчанию совершенно неоптимальны, так что дедик сам по себе при большой посещаемости приблемы не решит
в mysql настроек не так много - размеры буферов и все, их поставить по меньше в 4 раза и будет быстрее работать - но это если мало памяти, а так я сколько не оптимизировал - все равно все одинаково
руки кривые...что поделать
голословные высказывания ничем не подтвержденные, типа "знаю но не скажу" )))
речь о настройках mysq а не о оптимизациях запросов и тд
и надо-же... какие глупые разработчики mysql - тоже ну никак не догадаются как же оптимизировать свою mysql и выставить по дефолту оптимальные настройки )))
и надо-же... какие глупые разработчики mysql - тоже ну никак не догадаются как же оптимизировать свою mysql и выставить по дефолту оптимальные настройки
лол
я бы даже комментировать этот очередной бред конечно не стал бы, ну да ладно
оптимальные настройки вообще-то зависят от количества оперативной памяти, числа процессоров
а для таких специалистов, как ты, в комплекте с мускулом идёт несколько типовых конфигов my-huge.cnf my-large.cnf my-medium.cnf my-small.cnf
это для тех, кто вообще не в теме, как работает mysql.
ну так и я о чем - отличаются конфиги исключительно размером буферов - один для сервера где 32-64M памяти, другой где 512M памяти, третий для 2-4G памяти, и что? есть варианты? сейчас все серверы идут 2-4G памяти, 512M уже наверно и не найти, но если друпал вдруг съест всю память, то уменьшение буферов позволит mysql-у как-то работать и он перестанет виснуть - вот собственно и вся оптимизация
вот собственно и вся оптимизация
ну если руки кривые то да,вот у вас оптимизация и кончилась - не начавшись
а человек более-менее грамотный настроит индексы, кэш открытых таблиц под конкретную базу и нагрузку, посмотрит, какие таблицы целесообразно вообще полностью вынести в память, пользуя innodb.
кроме того, огорчу вас ещё раз, очень важно правильно настроить число тредов и одновременных соединений с апачом - это чаще всего становится причиной медленной работы мускула.
одним словом, с вашими знаниями, а точнее, их отстутствием, и правда больше всего подходят типовые конфиги. они, собственно, для вас и писаны.
спасибо, а число одновременных соединений не настраивается а тупо ограничивается - в таком случае mysql отказывает в обработке запроса, а на сайте видим радостную надпись о том что " мы заняты, приходите завтра" - что бы это избежать надо настроить апач что бы в трудном случае он не замучал mysql а клиент просто ожидал пока рассосется...
про индексы - конечно они должны быть - это самое важное, но это относится больше к оптимизации запросов пользователями, конечно если в myisam они не слетели вдруг как это обычно бывает у таблиц sessions или cache - тогда mysql будет вешать сервер.
про innodb - в идеале все должно быть в ней, так как ничего лучше в mysql нет... к сожалению нет - так как она не идеальна, есть мнение что myisam легкая и быстрая - но использовать ее все равно не выходит - она вдруг может просто сломаться как это бывает с таблицей sessions, иногда даже ручное восстановление не срабатывает - а пару раз, не так часто конечно но пару раз за 6 лет таблицу не удавалось восстановить вообще и приходилось ее просто пересоздавать пустую, благо данные таблиц cache и sessions не имеют ценности, но такая ненадежность просто настораживает - то есть использовать для часто используемых таблиц - ее нельзя - myisam вся блокируется при записи, а использовать для хранения других данных - стремно даже при наличии бекапов - не хочется рисковать временем на последующее восстановление, по этому остается innodb
остальные варианты - когда на сервере больше десятка баз - настраивать бесполезно - с течением времени конфигурация баз и таблиц меняется и настроить сервер оптимально когда на сервере не один друпал а куча всего остального - просто пустая трата времени - лучше оптимизировать запросы и не создавать на сайте формочек привлекающих спамботов.
слушайте,хватит бредить,а?
я так понимаю, вы не работали ни с чем, кроме впс, а я уже 8 лет держу несколько собственных серверов
всё, что вы пишите - это какой-то реальный идиотизм
число одновременных соединений именно настраивается - исходя из числа процессоров и ядер на сервере, исходя из оптимального числа процессов апача на сервере(зависит от памяти)
индексы никуда не улетают. а на сервере можно настроить размер памяти под них, исходя из ОБЩЕГО ЧИСЛА БАЗ ДАННЫХ
таблицы на правильно настроенном сервере не бьются вообще никогда - ни innodb, ни myisam. они могут биться только на говновпсах и тупо настроенных дедиках/коло, как наверно у вас. Рейд + оператива +электропитание избавляют от этих проблем вообще навсегда
а бэкапы в любом случае должны делаться постоянно
вообще-то, чем больше разных БД и cms на сервере, тем эффективней тюнинг и тем больше можно выгадать. тем тщательней надо подходить к конфигурации различных БД на сервере. тем чаще надо смотреть, где у mysql затыки и исправлять их.
и на том спасибо за разъяснение, я действительно дилетант, хоть есть у меня и сервера, но все таки наконец мы пришли к единому мнению что лучший тюнинг это:
"Рейд + оператива +электропитание избавляют от этих проблем вообще навсегда"
Люблю слушать споры людей, которые знают больше меня - многому можно научиться! Спасибо!
мне тоже
кстати заметил в MySql одну неприятную вещь - таймаут "залипших" коннектов почему то по дефолту 8 часов стоит, а иногда они лавинообразно скапливаются и переполняют все лимиты - что бы это не происходило надо wait_timeout сделать меньше 120 секунд.