Оптимизация настроек MySQL с помощью MySQLTuner. Скрипт оптимизации mysql

Оптимизация и восстановление баз данных MySQL с помощью mysqlcheck

Надо запастись либо умом, чтобы понимать, либо веревкой, чтобы повеситься (Антисфен).

В этой статье мы будем говорить о mysqlcheck, которая является инструментом обслуживания командной строки, что позволяет проверять, анализировать, ремонтировать, а также оптимизировать таблицы MySQL / MariaDB и базы данных.

Проверьте одну таблицу в базе данных

Следующая команда будет проверять сообщения таблицы в блоге базы данных:

$ mysqlcheck -c blog posts blog.posts OK

Если база данных защищена паролем , добавьте -u root -p в конце команды:

$ mysqlcheck -c blog posts -u root -p Enter password: blog.posts OK

Анализ всех таблиц в базе данных

Следующая команда будет проверять сообщения таблицы в блоге базы данных:

$ mysqlcheck -a blog posts blog.posts OK

Если сервер MySQL / MariaDB работает на удаленном хосте, добавьте -h в конце команды:

$ mysqlcheck -a blog posts -h remotehost.com blog.posts OK

Оптимизировать все таблицы во всех баз данных

$ mysqlcheck -o --all-databases blog.users note : Table does not support optimize, doing recreate + analyze instead status : OK mysql.time_zone_transition_type Table is already up to date

Table does not support optimize, doing recreate + analyze instead - означает, что мы делаем OPTIMIZE в таблицах InnoDB, который не поддерживает эту опцию. При выполнении OPTIMIZE в таблицах, InnoDB создает пустую таблицу, копирует все строки из существующей таблицы в новую, удаляет старую и переименовывает новую таблицу, а затем запускает ANALYZE в таблицах.Table is already up to date - Означает, что таблица актуальна, и нет никакой необходимости проверять её.

Восстановление нескольких баз данных

Следующая команда восстановит все таблицы в обоих базах данных:

$ mysqlcheck -r --databases blog blog2

Если вы видите: note : The storage engine for the table doesn't support repair, то это означает, что вы делаете REPAIR на InnoDB.

Оптимизация и ремонт всех таблиц во всех базах данных

Следующая команда будет проверять все таблицы во всех базах данных, и если какая-то таблица повреждена он будет автоматически исправит это эту таблицу:

$ mysqlcheck --auto-repair -o --all-databases

Большинство аргументов, используемых mysqlcheck

-c, --check	Проверить таблицу на наличие ошибок.
-a, --analyze	Анализировать данные таблицы.
-o --optimize	Оптимизация таблиц.
-r, --repair	Выполнение работ по ремонту, которые можно исправить почти все, за исключением уникальных ключей, которые не являются уникальными.
--auto-repair	Если проверенная таблица повреждена, автоматически восстановить ее. Ремонт будет сделан после того, как все таблицы были проверены.
-A, --all-databases	Проверьте все базы данных. Это то же самое, как -databases со всеми выбранными базами данных.
-B, --databases	Процесс все таблицы в названных баз данных. С помощью этой опции, все имена аргументов рассматриваются как имена баз данных, а не как имена таблиц.
--tables	Заменяет -databases или -B вариант таким образом, что все аргументы имени следующей опции рассматриваются как имена таблиц.
-g, --check-upgrade	Проверка таблицы для версии зависящих от изменений. Может использоваться с опцией -auto-repair для исправления таблиц, требующих версии зависящих от обновления.

PS. Если вам понравился этот пост, пожалуйста, поделитесь им с друзьями в социальных сетях с помощью кнопок, расположенных ниже поста или просто оставьте комментарий. Благодарю.

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Если статья понравилась, то поделитесь ей в социальных сетях:

andreyex.ru

Оптимизация настроек MySQL с помощью MySQLTuner

Не беда, если вы как и я не являетесь большим специалистом в области понимания тонкостей настроек MySQL. Специально для таких людей существует маленький, но очень полезный скрипт на языке Perl — MySQLTuner. Он хитрым образом анализирует статистику работы MySQL и выдает свои рекомендации по оптимизации настроек сервера.

Установка под Debian крайне проста.

apt-get install mysqltuner

Чтобы данные анализа были более корректными, сервер MySQL должен проработать некоторое время в боевом режиме, по рекомендации самого MySQLTuner не менее 24 часов. Желательно запускать скрипт с пользователем root.

mysqltuner --user root --pass xxxxxx

После секундного раздумья программа выдает примерно следующую информацию. Нас интересуют строки, помеченные символами [!!] и секция Recommendations.

-------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.1.66-0+squeeze1 [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 1G (Tables: 989) [--] Data in InnoDB tables: 98M (Tables: 275) [--] Data in MEMORY tables: 0B (Tables: 4) [!!] Total fragmented tables: 374 -------- Performance Metrics ------------------------------------------------- [--] Up for: 79d 1h 41m 0s (874M q [128.015 qps], 38M conn, TX: 14013B, RX: 163B) [--] Reads / Writes: 61% / 39% [--] Total buffers: 90.0M global + 3.1M per thread (200 max threads) [OK] Maximum possible memory usage: 702.5M (2% of installed RAM) [OK] Slow queries: 0% (187/874M) [OK] Highest usage of available connections: 85% (171/200) [OK] Key buffer size / total MyISAM indexes: 16.0M/808.4M [OK] Key buffer hit rate: 98.0% (23B cached / 472M reads) [OK] Query cache efficiency: 59.5% (269M cached / 453M selects) [!!] Query cache prunes per day: 189628 [OK] Sorts requiring temporary tables: 0% (8K temp sorts / 17M sorts) [!!] Joins performed without indexes: 612253 [!!] Temporary tables created on disk: 29% (2M on disk / 9M total) [OK] Thread cache hit rate: 99% (36K created / 38M connections) [!!] Table cache hit rate: 0% (256 open / 5M opened) [OK] Open file limit used: 34% (356/1K) [OK] Table locks acquired immediately: 99% (433M immediate / 434M locks) [!!] InnoDB data size / buffer pool: 98.8M/8.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Enable the slow query log to troubleshoot bad queries Adjust your join queries to always utilize indexes When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Increase table_cache gradually to avoid file descriptor limits Variables to adjust: query_cache_size (> 32M) join_buffer_size (> 512.0K, or always use indexes with joins) tmp_table_size (> 32M) max_heap_table_size (> 32M) table_cache (> 256) innodb_buffer_pool_size (>= 98M)

Общие рекомендации в принципе можно пропустить, смотрим рекомендации по изменению переменных (Variables to adjust). Открываем файл /etc/mysql/my.cnf и изменяем указанные переменные в соответствие с рекомендациями MySQLTuner. При отсутствии особых познаний в тонкостях настройки, я просто увеличиваю указанные переменные в два раза, жду сутки и смотрю что скажет программа при следующем запуске. Для того, чтобы изменения вступили в силу нужно перезапустить MySQL.

/etc/init.d/mysql restart

При последующих запусках особое внимание обращаем на строку с максимально допустимым потреблением памяти.

[OK] Maximum possible memory usage: 702.5M (2% of installed RAM)

Необходимо чтобы процентное соотношение не было слишком высоким. При опасном превышении MySQLTuner выделит строчку красным и выдаст предупреждение.

ramzes.ws

Как оптимизировать MySQL c помощью phpmyadmin и mysqltuner?

Сервер Баз данных очень часто грузит весь сервер, из-за этого плохо работают сайты, которые находятся на данном сервере. MySQL создает нагрузку не только на оперативную память, но и на сам жесткий диск и процессор.

Начнем пожалуй с phpmyadmin. Данный сервис может дать совет по оптимизации MySQL. Для начала нужно зайти в phpmyadmin, можно рута или от любого пользователя БД, далее ищем вкладку «Состояние», потом «Советчик». Смотрим что нам предлагают изменить:

Проблема:

Значение "long_query_time” равно 10 секундам или более, выходит, что только медленные запросы, которые больше по времени за 10 секунд, будут записаны в журнал.

Рекомендации по устранению проблемы:

Нужно понизить значение параметра long_query_time.

Предлагается оставить параметр равным 1-5 секунд.

Если решили прислушаться к совету, то заходим в конфигурационный файл MySQL:

vi /etc/my.cnf

И добавляем в него строки

long_query_time = 3

log-slow-queries = /var/log/mysql/mysql-slow.log

После чего сохраните файл и закройте его. Перегрузите сервер MySQL

/etc/init.d/mysqld restart

Все готово.

Теперь давайте познакомимся с утилитой Mysqltuner. Данный скрипт — собирает и выводит анализ работы Mysql, также как и советчик скрипт напишет вам, что лучше сделать для оптимизации Mysql.

Устанавливаем mysqltuner:

yum -y install mysqltuner

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

mysqltuner --user root --pass rootpassword

Запуск может быть просто mysqltuner, зависит от того как была установлена утилита.

Смотрим отчет:

В графе Recommendations смотрим те параметры, которые нам советуют изменить.

Если же какого то параметра не найдете в конфигурационном файле my.cnf - не отчаивайтесь, просто допишите его в свободной строчке.

Заходим в /etc/my.cnf, делаем изменения.

/etc/init.d/mysqld restart

Готово. Теперь наш MySQL сервер оптимизирован.

hostciti.net

Настройка и оптимизация MySQL сервера

Если вы, как и я, не бум-бум в тонких настройках MySQL сервера, то эта информация может вам быть полезна :-)

Стоит сразу отметить, что из коробки MySQL сервер настроен как-то не очень… Хотя тут уж как повезет. Но я выбрал сложный путь оптимизации сервера, поскольку его работа меня не устраивала.

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

Нам предлагают на выбор:

my-small.cnf — для серверов с малым кол-вом памяти (64 мб), где MySQL используется редко
my-medium.cnf — для серверов с малым кол-вом памяти (32-64 мб), где MySQL играет главную роль или серверов со 128 мб памяти, где MySQL используется совместно с другими программами (вроди web server)
my-large.cnf — для систем с большим кол-вом памяти (512 мб), где в основном используется MySQL

my-huge.cnf — для больших систем (1G-2G памяти), где в основном используется MySQL
my-innodb-heavy-4G.cnf — 4GB памяти, только InnoDB таблицы, ACID, много соединений, тяжелые запросы

Выбираем один из них и копируем поверх etc/my.cnf

Не забываем перезапускать сервер командой service mysqld restart

Обратите внимание, что если вы используете InnoDB, то в конфиг файлах нужно раскомментировать соответствующие строки. Делайте это с осторожностью, у меня некоторые из них не давали запускаться серверу.

Для дальнейшей настройки нужно дать поработать серверу хотя бы 24 часа, а лучше 48 и более.

После этого используем утилиты для оптимизации MySQL сервера.

Первой будет mysqltuner

Переходим куда-нибудь во временную директорию cd /tmp и запускаем скачивание скрипта

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

Если что-то не качается, то заходим на http://mysqltuner.pl/ и смотрим, куда переадресовывает нас сайт

После того, как скрипт скачался, запускаем его

perl mysqltuner.pl

Скрипт просит логин и пароль администратора MySQL, нужно их ввести. Далее проводит анализ и выводит результаты.

Так как сервера у всех индивидуальны, то разбирать отчет я не вижу смысла. Читайте и следуйте рекомендациям. Критические позиции выделены красными восклицательными знаками [!!]

Вторая утилита, которую стоит использовать — tuning primer

Качаем ее и запускаем

wget http://www.day32.com/MySQL/tuning-primer.sh
sh tuning-primer.sh

На все запросы нажимаем Enter и вводим логин и пароль администратора MySQL

Сиотрим рекомендации и выполняем.

Тут я могу сказать, что делать рекомендации нужно поочередно, а не все сразу. После каждого изменения перезапускаем MySQL сервер. Если что-то пошло не так и он не запускается — откатываем изменения. Так вы съэкономите себе кучу времени и нервов :-)

И еще один важный момент. Обе утилиты могут вам написать такое замечание:

Maximum possible memory usage xxx G (>100% of installed RAM) или Max memory limit exceeds 90% of physical memory

Это значит, что теоретически ваш сервер может зависнуть, при активной работе MySQL. Если говорить точнее, то при нехватке памяти процесс mysqld будет убит OOM Killer'ом и сайты будут недоступны, пока вы не заметите проблему.

Так вот, из отчета совершенно не понятно, как этот Max memory limit посчитать. Не ищите его в настройках my.cnf — его там нет. Рассчитывается он по формуле, в которой сам черт ногу сломит, поэтому проще воспользоваться калькулятором mysqlcalculator.com, который все сделает за вас. В него включены и настройки MySQL по умолчанию, которых в вашем my.cnf может и не быть. Чтобы узнать настройки mysql по умолчанию, нужно зайти в mysql в консоли и набрать команду (оперируя с mysql не забывайте ставить в конце точку с запятой)

mysql
SHOW VARIABLES;
или
SHOW GLOBAL VARIABLES LIKE 'read_buffer_size';
или
SELECT @@global.read_buffer_size;

Установить новое значение глобальной переменной можно так

SET GLOBAL read_buffer_size=8192;
или
SET @@global.read_buffer_size=8192;

Переменную для текущей сессии можно установить так

SET SESSION read_buffer_size=8192;
или
SET @@session.read_buffer_size=8192;
или
SET LOCAL read_buffer_size=8192;
или
SET read_buffer_size=8192;

Из калькулятора видно, что больше всего поджирает память key_buffer_size и innodb_buffer_pool_size, а так же параметр max_connections, который не просто суммируется, но и умножается на память, выделяемую одному соединению. Впрочем, значение этого параметра с легкостью подскажет вам tuning-primer, проанализировав ваши логи.

На этом пока всё, желаю удачи в ускорении вашего MySQL сервера :-)

amiweb.ru