недостаточно производительности сервера mysql

Применимость: Linux

Слова для поиска:

Вы замечаете, что производительности mysql не хватает

Проанализируйте общее состояние системы командами:

ps aux --sort=-%cpu | head -31
ps aux --sort=-rssize | head -20
df -h
netstat -ntu | wc -l
cat /proc/meminfo
free
iostat -x 5
top
mysqladmin -uroot -p ext proc

Типичный пример проблемного состояния конфигурации демона mysql на этом снимке:

Здесь совершенно очевидно, что сервер задыхается при минимальной нагрузке

После установки mysql файл конфигурации размещается в /etc/my.cnf и содержит минимальный набор параметров подходящий для самого скромного сервера.

В такой конфигурации работать он будет, но про производительную работу говорить не приходиться.

В директории /usr/share/mysql есть несколько заготовок (шаблонов) для файла конфигурации:

/usr/share/mysql/my-huge.cnf
/usr/share/mysql/my-innodb-heavy-4G.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-small.cnf

Выбираем шаблон под свой сервер с учетом количества памяти.

Но при этом учитывайте, что еще довольно много памяти потребуется для форков демона вашего вебсервера.

• my-small.cnf - конфигурация для систем с обьемом памяти менее 64Mb, где mysql используется время от времени.
• my-medium.cnf - под mysql может выделяться до 64Мb памяти.
• my-large.cnf - для систем где для mysql можно выделить до 512Мb.
• my-huge.cnf - для систем где для mysql можно выделить до 1-2Gb.

Сперва сохраните на всякий случай прежнюю конфигурацию:

cp /etc/my.cnf  /etc/my.cnf.orig

И замените существующий файл конфигурации выбранным шаблоном:

cp /usr/share/mysql/my-large.cnf  /etc/my.cnf

Cелайте рестарт сервера.

service mysqld restart

Проверьте работу вашего сайта, обязательно проверьте основные функции

Скачайте скрипт для оптимизации конфигурации:

wget  --no-check-certificate https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl

и запустите его командой:

perl mysqltuner.pl

Скрипт попросит имя и пароль MySQL

Внимательно изучите сообщения которые выдаст скрипт, выполните рекомендации которые получите.

При выполнении этого скрипта вы можете получить сообщение:

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance

#!/bin/bash
 
MYSQL_LOGIN='-u root --password=YOURPASSWORD'
 
for db in $(echo "SHOW DATABASES;" | mysql $MYSQL_LOGIN | grep -v -e "Database" -e "information_schema")
do
        TABLES=$(echo "USE $db; SHOW TABLES;" | mysql $MYSQL_LOGIN |  grep -v Tables_in_)
        echo "Switching to database $db"
        for table in $TABLES
        do
                echo -n " * Optimizing table $table ... "
                echo "USE $db; OPTIMIZE TABLE $table" | mysql $MYSQL_LOGIN  >/dev/null
                echo "done."
        done
done

Укажите ваш пароль вместо YOURPASSWORD, сделайте ваш скрипт исполняемым и запускайте для оптимизации.

chmod +x mysql.tab.opt.sh
./mysql.tab.opt.sh

Проверьте результат работы. Откорректируйте новый конфиг используя сведения из статьи Оптимизация mysql

Параметр thread_cache_size играет немаловажную роль в производительности нагруженного MySQL-сервера. В некоторых случаях можно увеличить производительность на 30-50%.

Этот параметр указывает количество тредов, уходящих в кеш при отключении клиента.

Выявить необходимость оптимизации параметра thread_cache_size можно командой:

mysqladmin -u root -p extended-status | grep Threads

Если значение Threads_created значительно больше, чем Threads_cached (на тысячи), значит thread_cache_size или слишком мал, или вовсе отключен.

Оптимальное значение thread_cache_size, при котором Threads_created держится на приемлемом уровне, колеблется от 8 до 100, в зависимости от нагрузки и количества памяти.

Рекомендуется постепенно повышать значение thread_cache_size и наблюдать за Threads_created до тех пор, пока Threads_created будет незначительно больше Threads_cached.

Установите, сконфигурируйте и используйте утилиту mytop

yum install mytop

или

apt-get  install mytop

Создайте файл в домашнем каталоге с таким содержимым

user=<имя пользователя бызы>
pass=<пароль>
host=localhost
db=<имя базы>
delay=5
port=3306
socket=
batchmode=0
header=1
color=1
idle=1

И теперь можно запускать команду mytop и анализировать вывод

Установите пакет sysbench

apt-get install sysbench

или

yum install sysbench

Для тестирования надо создать отдельную базу test1, пользователя с именем test1 и паролем test1drbdPXZ3

Создаём новую базу данных test1

mysqladmin -u root -p<PASSWORD> create test1

Создаем пользователя с именем test1

echo "GRANT ALL ON test1.* TO 'test1'@'localhost' IDENTIFIED BY 'test1drbdPXZ3'" | mysql -uroot mysql

Для измерения производительности MySQL создаем тестовую таблицу на 1000000 строк в базе данных test1:

sysbench --db-driver=mysql --test=oltp --oltp-table-size=1000000 \
--mysql-db=test1 --mysql-user=test1 --mysql-password=test1drbdPXZ3 prepare

Для оценки времени создания таблицы можно перед командой добавить time

time sysbench ......

После этого можно запустить тест MySQL следующим образом:

sysbench --db-driver=mysql --test=oltp --oltp-table-size=1000000 \
--mysql-db=test1 --mysql-user=test1 --mysql-password=test1drbdPXZ3 \
--max-time=60 --oltp-read-only=on --max-requests=0 --num-threads=8 run

Самый важный параметр в выводе теста - количество транзакций в секунду:

После анализа результатов можно очистить базу

sysbench --db-driver=mysql  --test=oltp --mysql-db=test1 --mysql-user=test1 \
--mysql-password=test1drbdPXZ3 cleanup 

• Оптимизация mysql
• бинарный журнал обновлений mysql
• http://www.upmeter.com/poll/li/russian/pn/rating/q/reduce-burden-mysql-server-achieve-loading-decrease/
• Тюнинг MySQL — thread_cache_size
• Настройка и оптимизация MySQL сервера
• MySQL Performance Blog
• MySQL-тюнинг. Настраиваем по-взрослому
• Оптимизация и тюнинг MySQL
• Скрипт для оптимизации MySQL
• Things I learned from "High Performance MySQL
• Remote MySQL Performance And Query Monitoring
• Еще 12 «рецептов приготовления» MySQL в Битрикс24
• —-

Актуальность: 2011/10/17 10:15