linux-notes.org. Оптимизация mysql linux

Оптимизация сервера MySql. Шаг первый. Утилита mysqltuner.

Так повелось, что в мире хостинга , самой популярной базой данных, де факто стала база 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 <[email protected]>

>> Bug reports, feature requests, and downloads at http://mysqltuner.com/

>> Run with ‘—help’ for additional options and output filtering

Please enter your MySQL administrative login: root

Please 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 InnoDB

Add skip-bdb to MySQL configuration to disable BDB

Run OPTIMIZE TABLE to defragment tables for better performance

MySQL started within last 24 hours — recommendations may be inaccurate

Enable the slow query log to troubleshoot bad queries

В первую очередь, на мой взгляд, стоит обратить внимание на строку

Highest usage of available connections: 5% (5/100)

Количество текущих соединений — 5, количество возможных соединений — 100. Параметр 100 — это параметр использующийся по умолчанию. Обычно этого значения маловато, именно из-за него появляются сообщения вида «Too many active connections». Увеличим этот параметр до 300. Открываем файл /etc/my.cnf в любимом редакторе и в секции [mysqld] добавляем строчку

max_connections = 300

Сохраняемся, выходим и перегружаем сервер mysql. Повторно запустив mysqltuner увидим соответствующие изменения в выводе.

Еще одним, наиболее важным параметром увеличения производительности mysql является key_buffer_size. Параметр определяет размер общего для всех пользовательских процессов буфера индексных блоков MyISAM таблиц. Обычно устанавливается в пределах 30-40% от общей выделенной под Mysql оперативной памяти.

Следует помнить, что рекомендации утилиты mysqltuner будут тем более оптимальны, чем дольше работает без перезагрузок сервер mysql. Скажем так: минимальный интервал в общем то должен составлять не менее 24 часов, о чем и предупреждает утилита после своего запуска.

Вкратце рассказал про первые шаги на поприще оптимизации работы базы данных mysql. Очень подробную информацию практически по каждому параметру в конфиге my.cnf можно найти вот тут. Единственный минус — ресурс англоязычный, но при необходимости, я думаю, разобраться можно.

Удачи всем в деле оптимизации mysql и быстрых вам SELECT-ов. 🙂

Взято: http://dedicatesupport.com/content/optimizatsiya-servera-mysql-shag-pervyi-utilita-mysqltuner

на Ваш сайт.

www.admins.kz

Тюнинг MySQL в Unix/Linux | linux-notes.org

Запуск MySQL c оптимальными настройками для конкретных сервисов помогает сбалансировать работу при большой нагрузки на сервер и предотвращает замедление сервера.

Тюнинг база данных — экспансивная тема, и это руководство «Тюнинг MySQL в Unix/Linux» поможет оптимизировать mysql. На моем готовом примере, покажу как это можно сделать.

УтилитыДля того, чтобы определить, если ваша база данных MySQL должна быть изменена, то лучше всего посмотреть, как ваши ресурсы выполняются в настоящее время. Это может быть выполнено с командой:

# echo [PID] [MEM] [PATH] && ps aux | awk '{print $2, $4, $11}' | sort -k2rn | head -n 20

# ps -eo pcpu,pid,user,args | sort -k 1 -r | head -20

MySQLTunerСкрипт MySQLTuner оценивает вашу настройку MySQL, а затем выводит предложения для повышения производительности и стабильности вашего сервера.

Скачиваем MySQLTuner:

# cd /usr/local/src && wget http://mysqltuner.pl/ -O mysqltuner.pl

Запустите скрипт:

# perl /usr/local/src/mysqltuner.pl

It outputs your results:

>> MySQLTuner 1.4.0 - Major Hayden <[email protected]> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering Please enter your MySQL administrative logiroot Please enter your MySQL administrative password: [!!] Currently running unsupported MySQL version 10.0.19-MariaDB [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MyISAM [--] Data in MyISAM tables: 40M (Tables: 8) [--] Data in InnoDB tables: 87M (Tables: 432) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52) [--] Data in MEMORY tables: 5M (Tables: 6) [!!] Total fragmented tables: 26 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 12d 17h 25m 17s (12M q [11.016 qps], 157K conn, TX: 130B, RX: 5B) [--] Reads / Writes: 69% / 31% [--] Total buffers: 1.1G global + 11.3M per thread (80 max threads) [OK] Maximum possible memory usage: 2.0G (32% of installed RAM) [OK] Slow queries: 0% (3/12M) [OK] Highest usage of available connections: 63% (51/80) [OK] Key buffer size / total MyISAM indexes: 32.0M/949.0K [OK] Key buffer hit rate: 100.0% (5M cached / 2K reads) [OK] Query cache efficiency: 26.1% (2M cached / 10M selects) [!!] Query cache prunes per day: 17574 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 134K sorts) [!!] Temporary tables created on disk: 90% (350K on disk / 387K total) [OK] Thread cache hit rate: 95% (6K created / 157K connections) [!!] Table cache hit rate: 0% (128 open / 16K opened) [OK] Open file limit used: 0% (11/8K) [OK] Table locks acquired immediately: 99% (8M immediate / 8M locks) [!!] Connections aborted: 24% [OK] InnoDB buffer pool / data size: 512.0M/87.3M [OK] InnoDB log waits: 0 -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Temporary table size is already large - reduce result set size Reduce your SELECT DISTINCT queries without LIMIT clauses Increase table_open_cache gradually to avoid file descriptor limits Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C Your applications are not closing MySQL connections properly Variables to adjust: query_cache_size (> 32M) table_open_cache (> 128)

MySQLTuner предлагает улучшение относительно ваших баз данных.

Тюнинг MySQLMySQLTuner является одним из самых безопасных способов улучшить производительность базы данных.

Файл, который вы изменение находится в /etc/mysql/my.cnf.

ПРИМЕЧАНИЕ! Перед обновлением конфигурации MySQL, рекомендую создать резервную копию файла my.cnf:

# cp /etc/mysql/my.cnf ~/my.cnf.backup

Лучшие делайте небольшие изменения чтобы контролировать сервер после каждого изменения. После каждого изменения перезапустить MySQL:

На Debian/Ubuntu:

# service mysql restart

На CentOS/Fedora:

# /etc/init.d/mysqld restart

Параметры.

key_bufferИзменение «key_buffer» влияет на выделение памяти для MySQL, может существенно ускорить базы данных, если у вас есть свободное память на сервере. Размер «key_buffer» как правило, должны занять не более 25% от системной памяти при использовании таблицы c MyISAM, и до 70% для InnoDB. Если значение слишком высокое, то ресурсы тратятся впустую. Согласно документации MySQL, сервера с 256 МБ оперативной памяти (или больше) -рекомендуется устанавливать 64M , если сервера имеют 128 Мб ОЗУ и меньше, то используется 16M, значения по умолчанию.

max_allowed_packetМаксимальный размер пакета который может быть отправлен. Если вы знаете, что ваш сервер MySQL будет выполнять обработку больших пакетов, то лучше поднять этот параметр. Если это значение установлено слишком маленьким, вы получите сообщение об ошибке в лог-журнале.

thread_stackЭто значение содержит размер стека для каждого потока. MySQL считает значения переменной thread_stack по умолчанию, достаточной для нормального использования; Однако, если возникает ошибка, относящаяся к thread_stack, то данный параметр может быть увеличен.

thread_cache_sizeЕсли thread_cache_size -выключен (установлен в 0), то все новые соединения, создают нужен новый поток, созданный для них и когда соединения завершается, то нить разрушается. В противном случае, это значение задает количество неиспользованных потоков для сохранения в кэше, пока они не должны быть использованы для соединения. Вообще этот параметр мало влияет на производительность, если вы не получаете сотни соединений в минуту, при которых время это значение должно быть поднято для больших соединений которые делаются на кэшированных потоков.

max_connectionsУстанавливает максимальное количество одновременных подключений. Лучше рассмотреть максимальное количество подключений. Обратите внимание, что это не означает, что это будет максимальное количество пользователей на вашем сайте в одно время, но это максимальное количество пользователей, делающих запросы одновременно.

table_cacheдолжен быть больше, чем значение open_tables. Чтобы определить это значение использования:

SHOW STATUS LIKE 'open%';

Вот некоторые решения.

Тюнинг выдал рекомендацию:

Run OPTIMIZE TABLE to defragment tables for better performance

Чтобы это исправить, используем:

$ mysqlcheck -uroot -p --optimize --all-databases

Вот и все, тема «Тюнинг MySQL в Unix/Linux» завершена.

linux-notes.org

Утилиты для оптимизации MySQLMariaDB. - Записки сисадмина

Рассмотрим утилиты для оптимизации и контроля производительности MySQL\MariaDB сервера. Некоторые из них известны широко, другие, к моему удивлению, администраторами используются редко…

1. При установке сервера БД, в директории /usr/share/mysql/ можно найти готовые конфигурационные файлы для разных конфигов сервера. Например владельцы VDS с 512 Mb памяти могут попробовать использовать отсюда конфиг my-small.cnf, либо, если его лимитов будет не достаточно, конфиг my-medium.cnf.

# ls -la /usr/share/mysql/my-* -rw-r--r-- 1 root root 4920 ноя 15 2016 /usr/share/mysql/my-huge.cnf -rw-r--r-- 1 root root 20438 ноя 15 2016 /usr/share/mysql/my-innodb-heavy-4G.cnf -rw-r--r-- 1 root root 4907 ноя 15 2016 /usr/share/mysql/my-large.cnf -rw-r--r-- 1 root root 4920 ноя 15 2016 /usr/share/mysql/my-medium.cnf -rw-r--r-- 1 root root 2846 ноя 15 2016 /usr/share/mysql/my-small.cnf

2. На сайте tools.percona.com имеется доступ к генератору конфигов сервера БД. Если предустановленного конфига по какой-то причине оказалось не достаточно, можно авторизоваться на сайте и попробовать сгенерировать конфигурацию здесь.

3. mysqladmin — клиент для работы с сервером БД. Удобный инструмент, который позволяет без запуска консоли сервера БД работать с базами и настройками MySQL. Очень удобен для использования в скриптах.

4. mysqltuner.pl — известный скрипт для оптимиации сервера БД. Скачиваем его, запускаем, и получаем анализ текущей ситуации и рекомендации по увеличению конкретных параметров в конфиге. Для получения оптимального результата, имеет смысл запускать анализ после того как сервер БД проработал без перезапуска более 24-36 часов.

# wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl # chmod u+x mysqltuner.pl # ./mysqltuner.pl

5. Известный в прошлом, но к сожалению, теряющий актуальность для последних версий сервера БД скрипт tuning-primer.sh. Однако некоторые администраторы всё ещё используют его в работе. Принцип работы со скриптом аналогичный — после запуска, скрипт анализирует параметры сервера БД и даёт рекомендации по их изменению.

# wget https://launchpadlibrarian.net/78745738/tuning-primer.sh # wget https://launchpadlibrarian.net/251274960/5.7.patch # patch -p0 -i ./5.7.patch patching file tuning-primer.sh Hunk #1 succeeded at 1277 with fuzz 1. # chmod u+x ./tuning-primer.sh # ./tuning-primer.sh

6. Утилита mytop. В CentOS устанавливается из репозитория EPEL. С помощью этой утилиты мы можем подключится к серверу БД нужным пользователем, при необходимости можем указать конкретную базу данных и посмотреть статистику по ней — какой запрос был сделан, к какой базе, сколько этот запрос выполнялся, от какого хоста, в каком состоянии запрос находится сейчас и т. п. Нужные опции утилите можно передать как через параметры запуска, так и через конфиг ~/.mytop. При необходимости, утилиту можно использовать в других скриптах, для этого у неё предусмотрен соответствующий режим работы.

# yum install epel-release # yum install mytop # mytop -u sysadmin_wtuser -p superpassword -d sysadmin_wtdbase

7. Утилита mtop. В CentOS ставится из репоитория rpmforge (он всё ещё жив частично, да). Аналогично с предыдущей утилитой, администратор может получить статистику по работе сервера БД, информацию о запросах к нему. Имеется возможность принудительно завершить нужный процесс, отфильтровать результаты только для определённого пользователя или хоста.

# wget http://repository.it4i.cz/mirrors/repoforge/redhat/el7/en/x86_64/rpmforge/RPMS/rpmforge-release-0.5.3-1.el7.rf.x86_64.rpm # yum install rpmforge-release-0.5.3-1.el7.rf.x86_64.rpm # yum install mtop # yum install yum-utils # yum-config-manager --disable rpmforge # mtop --dbuser=root --password=superpassword

8. Утилита innotop. В CentOS устанавливается из репозитория EPEL. Отличный инструмент для анализа и работы с базами, которые используют InnoDB. Аналогично предыдущим утилитам, innodb позволяет получить информацию о запросах к базе, конкретных таблицах, статистику репликации, информацию о буферах InnoDB и т. п.

# yum install epel-release # yum install innotop # innotop -u root -p superpassword

И mtop, и mytop, и innotop имеют поддержку ввода команд, которые позволяют администратору использовать весь доступный функционал утилит. Список команд можно получить введя уже в запущенном приложении символ знака вопроса — ?

sysadmin.pm