Оптимизация mysql

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

---

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

Работа сервера базы данных Mysql возможна в двух режимах:

• С соединением через файл сокета.
• С соединением через localhost через порт.

Настройка параметров работы сервера базы данных MYSQL происходит с помощью конфигурационного файла расположенного по пути:

 /etc/my.cnf 

или

 /etc/mysql/my.cnf 

в зависимости от архитектуры операционной системы:

Базовые настройки

low-priority-updates — эта опция снижает приоритет операций INSERT/UPDATE по сравнению с SELECT. Актуально, если данные важно быстрее прочитать, чем быстрее записать.

skip-external-locking — опция установлена по умолчанию, начиная с версии 4. Указывает MySQL-серверу не использовать внешние блокировки при работе с базой. Внешние блокировки необходимы в ситуациях, когда несколько серверов работают с одними и теми же файлами данных, т.е. имеют одинаковую datadir, что на практике не используется.

skip-name-resolve — не определять доменные имена для IP-адресов подключающихся клиентов. При этом пользовательские разрешения нужно настраивать не на хосты, а на IP-адреса (за исключением localhost). Если вы соединяетесь с сервером только с локальной машины, то особого значения не имеет. Для внешних соединений ускорит установку соединения.

skip-networking — не использовать сеть, т.е. вообще не обрабатывать TCP/IP соединения. Общение с сервером при этом будет происходить исключительно через сокет. Рекомендуется, если у вас нет ПО, которое использует только TCP/IP для связи с сервером.

Ограничения

bind-address — интерфейс, который будет слушать сервер. В целях безопасности рекомендуется установить здесь 127.0.0.1, если вы не используете внешние соединения с сервером.

max_allowed_packet — максимальный размер данных, которые могут быть переданы за один запрос. Следует увеличить, если столкнетесь с ошибкой «Packet too large».

max_connections — максимальное количество параллельных соединений к серверу. Увеличьте его, если сталкиваетесь с проблемой «Too many connections».

max_join_size — запрещает SELECT операторы, которые предположительно будут анализировать более указанного числа строк или больше указанного числа поисков по диску. Используется для защиты от кривых запросов, которые пытаются считать миллионы строк. Значение по умолчанию более 4 миллиардов, поэтому вы скорее всего захотите его значительно уменьшить.

max_sort_length — указывает, сколько байт из начала полей типа BLOB или TEXT использовать при сортировке. Значение по умолчанию 1024, если вы опасаетесь некорректно спроектированных таблиц или запросов, то следует его уменьшить.

Настройки потоков

thread_cache_size — указывает число кэшируемых потоков. После обработки запроса сервер не будет завершать поток, а разместит его в кэше, если число потоков, находящих в кэше меньше, чем указанное значение. Значение по умолчанию 0, увеличьте его до 8 или сразу до 16. Если наблюдается рост значения переменной состояния

Threads_Created - следует еще увеличить thread_cache_size.

thread_concurrency — актуально только для Solaris/SunOS вопреки тому, что пишут в сети. «Подсказывает» системе сколько потоков запускать одновременно, выполняя вызов функции thr_setconcurrency. Рекомендованное значение — двойное или утроенное число ядер процессора.

Кэширование запросов

query_cache_limit — максимальный размер кэшируемого запроса.

query_cache_min_res_unit — минимальный размер хранимого в кэше блока.

query_cache_size — размер кэша. 0 отключает использование кэша. Для выбора оптимального значения необходимо наблюдать за переменной состояния Qcache_lowmem_prunes и добиться, чтобы ее значение увеличивалось незначительно. Также нужно помнить, что излишне большой кэш будет создавать ненужную нагрузку.

query_cache_type — (OFF, DEMAND, ON). OFF отключает кэширование, DEMAND – кэширование будет производиться только при наличии директивы SQL_CACHE в запросе, ON включает кэширование.

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

Кэш запросов можно представить себе как хэш-массив, ключами которого являются запросы, а значениями — результаты запросов. Кроме результатов, MySQL хранит в кэше список таблиц, выборка из которых за кэширована. Если в любой из таблиц, выборка из которой есть в кэше, происходят изменения, то MySQL удаляет из кэша такие выборки. Также MySQL не кеширует запросы, результаты которых могут измениться. При запуске MySQL выделяет блок памяти размером в query_cache_size. При выполнении запроса, как только получены первые строки результата сервер начинает кэшировать их: он выделяет в кэше блок памяти, равный query_cache_min_res_unit, записывает в него результат выборки. Если не вся выборка поместилась в блок, то сервер выделяет следующий блок и так далее. В момент начала записи MySQL не знает о размере получившейся выборки, поэтому если записанный в кэш размер выборки больше, чем query_cache_limit, то запись прекращается и занятое место освобождается, следовательно, если вы знаете наперед, что результат выборки будет большим, стоит выполнять его с директивой SQL_NO_CACHE.

Тайминги

interactive_timeout — время в секундах, в течение которого сервер ожидает активности со стороны интерактивного соединения (использующего флаг CLIENT_INTERACTIVE), прежде чем закрыть его.

log_slow_queries — указывает серверу логировать долгие («медленные») запросы (выполняющиеся дольшеlong_query_time). В качестве значения передается полное имя файла (например /var/log/slow_queries).

long_query_time — если запрос выполняется дольше указанного времени (в секундах), то он будет считаться «медленным».

net_read_timeout — время в секундах, в течение которого сервер будет ожидать получения данных, прежде чем соединение будет прервано. Если сервер не обслуживает клиентов с очень медленными или нестабильными каналами, то 15 секунд здесь будет достаточно.

net_write_timeout — время в секундах, в течение которого сервер будет ожидать получения данных, прежде чем соединение будет прервано. Если сервер не обслуживает клиентов с очень медленными или нестабильными каналами, то 15 секунд здесь будет достаточно.

wait_timeout — время в секундах, в течение которого сервер ожидает активности соединения, прежде чем прервет его. В общем случае 30 секунд будет достаточно.

Буферы

У всех буферов есть общая черта — если из-за установки большого размера буфера данные будут уходить в файл подкачки, то от буфера будет больше вреда, чем пользы. Поэтому всегда ориентируйтесь на доступный вам объем физической ОЗУ.

key_buffer_size — размер буфера, выделяемого под индексы и доступного всем потокам. Весьма важная настройка, влияющая на производительность. Значение по умолчанию 8 МБ, его однозначно стоит увеличить. Рекомендуется 15-30% от общего объема ОЗУ, однако нет смысла устанавливать больше, чем общий размер всех .MYI файлов. Наблюдайте за переменными состояния Key_reads и Key_read_requests, отношение

Key_reads/Key_read_requests должно быть как можно меньше (< 0,01). Если это отношение велико, то размер буфера стоит увеличить.

max_heap_table_size — максимальный допустимый размер таблицы, хранящейся в памяти (типа MEMORY). Значение по умолчанию 16 МБ, если вы не используете MEMORY таблиц, то установите это значение равным tmp_table_size.

myisam_sort_buffer_size — размер буфера, выделяемого MyISAM для сортировки индексов при REPAIR TABLE или для создания индексов при CREATE INDEX, ALTER TABLE. Значение по умолчанию 8 МБ, его стоит увеличить вплоть до 30-40% ОЗУ. Выигрыш в производительности соответственно будет только при выполнении вышеупомянутых запросов.

net_buffer_length — объем памяти, выделяемый для буфера соединения и для буфера результатов на каждый поток. Буфер соединения будет указанного размера и буфер результатов будет такого же размера, т.е. на каждый поток будет выделен двойной размер net_buffer_length. Указанное значение является начальным и при необходимости буферы будут увеличиваться вплоть до max_allowed_packet. Размер по умолчанию 16 КБ. В случае ограниченной памяти или использования только небольших запросов значение можно уменьшить. В случае же постоянного использования больших запросов и достаточного объема памяти, значение стоит увеличить до предполагаемого среднего размера запроса.

read_buffer_size — каждый поток при последовательном сканировании таблиц выделяет указанный объем памяти для каждой таблицы. Как показывают тесты, это значение не следует особо увеличивать. Размер по умолчанию 128 КБ, попробуйте увеличить его до 256 КБ, а затем до 512 КБ и понаблюдайте за скоростью выполнения запросов типа SELECT COUNT(*) FROM table WHERE expr LIKE «a%»; на больших таблицах.

read_rnd_buffer_size — актуально для запросов с «ORDER BY», т.е. для запросов, результат которых должен быть отсортирован и которые обращаются к таблице, имеющей индексы. Значение по умолчанию 256 КБ, увеличьте его до 1 МБ или выше, если позволяет память. Учтите, что указанное значение памяти также выделяется на каждый поток.

sort_buffer_size — каждый поток, производящий операции сортировки (ORDER BY) или группировки (GROUP BY), выделяет буфер указанного размера. Значение по умолчанию 2 МБ, если вы используете указанные типы запросов и если позволяет память, то значение стоит увеличить. Большое значение переменной состояния

Sort_merge_passes указывает на необходимость увеличения sort_buffer_size. Также стоит проверить скорость выполнения запросов вида SELECT * FROM table ORDER BY name DESC на больших таблицах, возможно увеличение буфера лишь замедлит работу.

table_cache (table_open_cache с версии 5.1.3) — количество кэшированных открытых таблиц для всех потоков. Открытие файла таблицы может быть достаточно ресурсоемкой операцией, поэтому лучше держать открытые таблицы в кэше. Следует учесть, что каждая запись в этом кэше использует системный дескриптор, поэтому возможно придется увеличивать ограничения на количество дескрипторов (ulimit). Значение по умолчанию 64, его лучше всего увеличить до общего количества таблиц, если их количество в допустимых рамках. Переменная состояния Opened_tables позволяет отслеживать число таблиц, открытых в обход кэша, желательно, чтобы ее значение было как можно ниже.

tmp_table_size — максимальный размер памяти, выделяемой для временных таблиц, создаваемых MySQL для своих внутренних нужд. Это значение также ограничивается переменной max_heap_table_size, поэтому в итоге будет выбрано минимальное значение из max_heap_table_size и tmp_table_size, а остальные временные таблицы будут создаваться на диске. Значение по умолчанию зависит от системы, попробуйте установить его равным 32 МБ и понаблюдать за переменной состояния Created_tmp_disk_tables, ее значение должно быть как можно меньше.

MYSQL log

По умолчанию, после установки MySQL в Debian информация пишется в лог:

 /var/log/syslog 

Можно задать путь до лога ошибок добавив файл /etc/mysql/my.cnf строки:

 log_error = /var/log/mysql/mysql.err 

log_warnings = 1

Mytop – мониторинг процессов MySQL в реальном времени. Всегда полезно знать чем занимается сервер MySQL. Для удобного мониторинга процессов сервера есть утилита mytop, похожая на top / htop для мониторинга процессов системы. Как происходит установка / запуск на разных дистрибутивах Linux.

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

Mytop входит в репозитории Fedora, так что это просто yum установка. Если вы используете Red Hat Enterprise Linux или CentOS, MyTOP то он доступен через репозиторий RPMForge. Теперь установите пакет MyTOP:

 # yum install mytop -y 

Монитор баз данных Теперь выполните следующую команду, чтобы открыть инструмент MyTOP. Он будет просить MySQL пароль рута для мониторинга баз данных:

 # mytop –prompt 

Если Вы хотите контролировать конкретную базу данных, используйте параметр-D. Например, для мониторинга данных с именем “test_DB” введите следующую команду:

 # mytop –prompt -d test_DB 

Mysqltuner - скрипт на perl, который анализирует показатели работе MySQL-сервера, накопленные за период времени и выдаёт статистическую информацию и рекомендации по оптимизации настроек. Получить скрипт можно командой:

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

При использовании под Debian какие-либо настройки не требуется, при использовании, к примеру, в CentOS, может понадобиться указать данные для доступа к MySQL. Пример вывода:

 # perl mysqltuner.pl
>>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.33-0+wheezy1
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1M (Tables: 82)
[--] Data in InnoDB tables: 96K (Tables: 6)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 6
-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 22h 56m 26s (674K q [8.169 qps], 83K conn, TX: 483M, RX: 65M)
[--] Reads / Writes: 99% / 1%
[--] Total buffers: 480.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 885.8M (25% of installed RAM)
[OK] Slow queries: 0% (0/674K)
[OK] Highest usage of available connections: 3% (6/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/640.0K
[OK] Key buffer hit rate: 99.2% (46K cached / 364 reads)
[OK] Query cache efficiency: 91.7% (311K cached / 340K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 12K sorts)
[OK] Temporary tables created on disk: 7% (118 on disk / 1K total)
[OK] Thread cache hit rate: 99% (6 created / 83K connections)
[!!] Table cache hit rate: 19% (129 open / 655 opened)
[OK] Open file limit used: 8% (212/2K)
[OK] Table locks acquired immediately: 100% (27K immediate / 27K locks)
[OK] InnoDB data size / buffer pool: 96.0K/128.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
    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
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    table_cache (> 1200) 

Далее остается подправить конфигурационный MySQL (/etc/mysql/my.cnf) и перезапустить его. Затем подождать около суток, снова запустить скрипт и следуя рекомендациям внести очередные коррективы в конфигурационный файл. Следует следить за максимальным значением, которое может использовать сервер баз данных, так как большинство изменений приводит к его увеличению.

---

Актуальность: 2015/01/27 10:19