Так повелось, что в мире хостинга , самой популярной базой данных, де факто стала база mysql. Простота установки , бесплатность, а главное, высокая скорость работы, сделали ее одной из наиболее часто используемых баз данных в мире хостинга. Она может многое, работает быстро, но, без должной изначальной настройки может стать очень узким местом. А отсюда может тянуться и медленная загрузка страниц сайта, и ошибки на страницах вида "Too many active connections".
После установки mysql на выделенный сервер, изначально его файл конфигурации размещается в /etc/my.cnf и выглядит весьма скромно. В такой конфигурации работать то он будет, но про какую-либо оптимальную работу говорить не приходиться. Вот и попробуем немного улучшить наши настройки сервера mysql, для его более оптимальной работы. Первым делом нам необходимо взять более менее нормальный конфигурационный файл my.cnf, более подходящий для нашего сервера. А найти его можно в /usr/share/mysql. Там есть четыре необходимых нам файлика: my-small.cnf, my-medium.cnf, my-large.cnf и my-huge.cnf. Выбираем под наш сервер, нашу память, наши запросы наиболее подходящий файл. my-small.cnf - конфигурация для систем с обьемом памяти менее 64Mb, где mysql используется время от времени. Это не для нас. medium.cnf - чуть лучше, под mysql может выделяться до 64Мb памяти. В общем то это тоже обычно не для нас, разве что очень маломощные VDS, VPS. my-large.cnf - уже посерьезнее, для систем с оперативной памятью от 512Мb. my-huge.cnf - для систем с оперативной памятью 1-2Gb. Выбираем одну из наиболее подходящих нам конфигураций и переписываем ее вместо текущей my.cnf . Например:
cp /usr/share/mysql/my-large.cnf /etc/my.cnf
После чего делаем рестарт сервера.
service mysqld restart
В общем случае эффект даже от такого, казалось бы простого шага сразу должен почувствоваться. Дальнейшая оптимизация производительности сервера mysql может проводиться с помощью использования специальных утилит.
Одной из таких утилит, которая может помочь в выявлении проблемных мест конфигурации сервера mysql, является утилита mysqltuner. Утилита представляет собой скрипт написанный на языке perl. Инсталляции не требует. Его просто нужно скачать
wget http://mysqltuner.pl/
и запустить
perl mysqltuner.pl
Скрипт попросит имя и пароль MySQL администратора, после чего выведет результаты своей работы. Вывод результатов работы утилиты примерно такой:
[root@host 1]# perl mysqltuner.pl
>> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>>> Bug reports, feature requests, and downloads at http://mysqltuner.com/>> Run with '--help' for additional options and output filteringPlease enter your MySQL administrative login: rootPlease enter your MySQL administrative password:-------- General Statistics --------------------------------------------------[--] Skipped version check for MySQLTuner script[OK] Currently running supported MySQL version 5.0.82sp1-log[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM-------- Storage Engine Statistics -------------------------------------------[--] Status: -Archive +BDB -Federated +InnoDB -ISAM -NDBCluster[--] Data in MyISAM tables: 19M (Tables: 90)[!!] InnoDB is enabled but isn't being used[!!] BDB is enabled but isn't being used[!!] Total fragmented tables: 18-------- Performance Metrics -------------------------------------------------[--] Up for: 16m 37s (6K q [6.059 qps], 146 conn, TX: 54M, RX: 665K)[--] Reads / Writes: 62% / 38%[--] Total buffers: 298.0M global + 6.3M per thread (100 max threads)[OK] Maximum possible memory usage: 929.2M (26% of installed RAM)[OK] Slow queries: 0% (0/6K)[OK] Highest usage of available connections: 5% (5/100)[OK] Key buffer size / total MyISAM indexes: 256.0M/2.3M[!!] Key buffer hit rate: 91.3% (1K cached / 101 reads)[OK] Query cache efficiency: 97.6% (5K cached / 5K selects)[OK] Query cache prunes per day: 0[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 8 sorts)[OK] Temporary tables created on disk: 5% (1 on disk / 17 total)[OK] Thread cache hit rate: 96% (5 created / 146 connections)[OK] Table cache hit rate: 95% (115 open / 121 opened)[OK] Open file limit used: 21% (222/1K)[OK] Table locks acquired immediately: 100% (286 immediate / 286 locks)-------- Recommendations -----------------------------------------------------General recommendations:Add skip-innodb to MySQL configuration to disable InnoDBAdd skip-bdb to MySQL configuration to disable BDBRun OPTIMIZE TABLE to defragment tables for better performanceMySQL started within last 24 hours - recommendations may be inaccurateEnable the slow query log to troubleshoot bad queries
Highest usage of available connections: 5% (5/100)
max_connections = 300
Для первых шагов, вполне
Для первых шагов, вполне хороший вариант, а дальше уже лучше копать, разбираться и настраивать каждый параметр самому.
У меня после нескольких дней оптимизации по каждому параметру для MyISAM на средненагруженном сервере в итоге вышел такой конфиг (по возможно убрано всё лишнее и оставлены лишь необходимые мне настройки):
[mysqld]
default-storage-engine=MyISAM
innodb=OFF
skip-symbolic-links
skip-networking
skip-name-resolve
skip-federated
bind-address = 127.0.0.1
max_connect_errors = 1K
max_join_size=200M
max_allowed_packet=16M
key_buffer_size=128M
query_cache_size = 256M
max_heap_table_size=256M
tmp_table_size=256M
max_connections = 160
thread_cache_size = 160
read_buffer_size = 128K
read_rnd_buffer_size = 256K
join_buffer_size = 256K
sort_buffer_size = 128K
myisam_sort_buffer_size = 512M
table_open_cache = 1K
interactive_timeout = 60
wait_timeout = 60
connect_timeout =20
character-set-server=utf8
collation-server=utf8_general_ci
log_slow_queries = 1
slow_query_log_file = /var/log/mysql-bin.log
long_query_time = 4
#log-queries-not-using-indexes
Подробности:
1) Ставим MyISAM по умолчанию и отключаем ненужные нам вещи:
default-storage-engine=MyISAM
innodb=OFF
skip-symbolic-links
skip-networking
skip-name-resolve
skip-federated
2) Привязываем базу для использования только на нашем сервере:
bind-address = 127.0.0.1
3) Выставляем необходимые максимальные ограничения:
max_connect_errors = 1K
max_join_size=200M
max_allowed_packet=16M
4) Устанавливаем общий буфер, кэш для запросов и max размер для временных таблиц:
key_buffer_size=128M
query_cache_size = 256M
max_heap_table_size=256M
tmp_table_size=256M
5) Максимальное число соединений к базе и ожидающих их процессов:
max_connections = 160
thread_cache_size = 160
6) Буферы на каждый процесс (большие значения типа 1Mb и выше для нас это явно перебор).
read_buffer_size = 128K
read_rnd_buffer_size = 256K
join_buffer_size = 256K
sort_buffer_size = 128K
7) Служебный сортировочный буфер (память постоянно не занимает) и кэш открытых таблиц.
myisam_sort_buffer_size = 512M
table_open_cache = 1K
8) Необходимые таймауты, чтобы отсекать долгие ожидания.
interactive_timeout = 60
wait_timeout = 60
connect_timeout =20
9) Установка кодировки UTF8:
character-set-server=utf8
collation-server=utf8_general_ci
10) По желанию можно добавить логи медленных запросов или неиспользуемых индексов (создайте доступным на запись файл для лога).
log_slow_queries = 1
slow_query_log_file = /var/log/mysql-bin.log
long_query_time = 4
log-queries-not-using-indexes
Быстрой Вам базы данных!
Обратите внимание на "6.3M
Обратите внимание на "6.3M per thread (100 max threads)". Увеличив количество max_connections в 3 раза, у вас сильно увеличиться использование оперативной памяти, вплоть до неприличного. Поэтому не стоит ставить max_connections про запас. Посмотрите свои текущие метрики, сколько коннектов в пике бывает. Прибавьте процентов 10-20 и нормально.
Спасибо большое за статью,
Спасибо большое за статью, очень помогла, хотя простые вещи, но надо знать.
Кстати использование тюнера привело к зависанию сервера, периодически.
Использую, рекомендую стандартный /usr/share/mysql/my-huge.cnf без правок.
Команда:
cp /usr/share/mysql/my-huge.cnf /etc/my.cnf
Спасибо! :)
Спасибо! :)
Ответить на комментарий Оптимизация сервера MySql. Шаг первый.
Скиньте сылку плиз на скачку
Странная ссылка, но это именно так.
http://mysqltuner.pl/
wget http://mysqltuner.pl/ -O
wget http://mysqltuner.pl/ -O mysqltuner.pl