Ключевые слова:mysql, tune, optimization, sql, database, buffer, (найти похожие документы)
From: mor.hosting-design.net
Date: Mon, 18 Apr 2004 14:31:37 +0000 (UTC)
Subject: Тюнинг и оптимизация производительности MySQL
Оригинал: http://mor.hosting-design.net/index.php?option=articles&task=viewarticle&artid=117&Itemid=3
Возможно пригодится кому.
Один из самых важных аспектов для производительности - написание
заведомо правильных и адаптированных под конкретную задачу и среду
приложений. Никакой тюнинг не спасет вас в случае бесконечного цикла в
коде или таймаутов. Соcтояние MySQL можно отслеживать с помощью
различных ключей утилиты mysqladmin (SHOW PROCESSLIST, SHOW STATUS) в
сочетании с мониторингом всей системы утилитами top и ps. Для удобства
можно использовать phpMyAdmin.
Условно можно выделить 3 группы проблем:
а) соединение клиента с сервером;
б) проблемы в самой базе;
в) проблемы дискового ввода/вывода.
Для начала, необходимо посмотреть параметры, заданные по умолчанию в
MySQL при компиляции. Для этого можно воспользоваться готовым
программным обеспечением (phpMyAdmin), либо помощью утилит имеющихся в
системе. В командной строке наберем
#mysqladmin -p variables, или запишем сразу в файл
#mysqladmin -p variables > temp.txt (опция p указывает на
необходимость ввода пароля)
Переменные, которые заслуживают пристального внимания:
- max_connections - число одновременных соединений разрешенное сервером;
- table_cache - буфер хранения данных наиболее частых обращений;
- key_buffer - буфер хранения последних использовавшихся ключей;
- back_log - количество одновременных подключений по TCP/IP стеку;
- skip-locking - редко встречаемая трабла в BSD, но лучше выставить
(решает проблемы с блокировкой файлов).
Устанавливая систему из портов, вы получите несколько готовых к
применению конфигурационных файлов. Они рассчитаны для разного железа
и количества пользователей. Необходимо выбрать наиболее близкий вашей
системе вариант и отредактировать файл руками. Достаточно скопировать
готовый файл под именем my.cnf в каталог /usr/var/db/mysql и
перегрузить базу. Возможное содержание этого файла из расчета на 512
MB оперативной памяти выглядит так:
# Example def's MySQL config file [my.cnf]
# The MySQL server
[mysqld]
set-variable = max_connections=300
set-variable = back_log=120
skip-locking
set-variable = key_buffer=256M
set-variable = max_allowed_packet=1M
set-variable = table_cache=256
set-variable = sort_buffer=1M
set-variable = record_buffer=1M
set-variable = myisam_sort_buffer_size=64M
set-variable = thread_cache=8
# При многопроцессорной системе параметр умножить на число CPU
set-variable = thread_concurrency=2
log-bin
server-id = 1
# Меняем стандартные пути
tmpdir = /tmp/
# Ускоряем архивацию
[mysqldump]
quick
set-variable = max_allowed_packet=16M
# Оптимизация и проверка таблиц
[isamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
[mysqlhotcopy]
interactive-timeout
Для начала достаточно, двинемся дальше. О проблемах дискового
ввода/вывода. Главное, помните, что swap и /var должны по возможности
быть на разных физических HDD - базы mySQL находятся в каталоге
/var/db/mysql, а mySQL регулярно свопится.
В FreeBSD вы обязательно найдете утилиту isamchk, которая
предназначена для анализа размещения данных в таблицах, их
оптимизации, нахождению и исправлению ошибок. Утилита имеет кучу
параметров, но есть пара простых соображений по части её
использования.
а) при каждом удвоении базы запускайте isamchk -a;
б) редко, но обязательно запускайте isamchk -d. Этот параметр покажет
вам число удаленных блоков, и если их количество велико, следует после
запускать isamchk -r.
При большой нагрузке может оказаться, что пользователи получат отказ
даже при заведомо выставленных правильно параметрах max_connections и
back_log. Дело в том, что существует еще ограничение дескрипторов
файлов и процессов в Unix. Эта проблема решается обязательной
пересборкой ядра и выставлением опции maxusers = xxx, но это уже
отдельная тема для разговора, а на сегодня все.
Вообще интересно как можно рассчитать значения для таких параметров как key_buffer, table_cache на основе данных статистики mysql за некий период и объема опертавной памяти ) Ведь можно подойти к данному вопросу с точки зрения математики?
