Как оптимизировать базы MySQL/MariaDB | Сеть без проблем
В этой статье мы будем исследовать некоторые методы сжатия таблицы / базы данных и дефрагментации в MySQL / MariaDB, что поможет вам сэкономить место на диске , база данных расположена на.
Базы данных крупных проектов со временем безмерно разрастаются, и всегда возникает вопрос, что с ними делать. Есть несколько способов решить проблему. Вы можете уменьшить объем данных в базе данных, удалив старую информацию, разделив базу данных на более мелкие, увеличив размер диска на сервере или сжав / сжав таблицы.
Еще один важный аспект функционирования базы данных — необходимость время от времени дефрагментировать таблицы и базы данных для повышения их производительности.
Сжатие и оптимизация таблиц InnoDB
Файлы ibdata1 и ib_log
Большинство проектов с таблицами InnoDB имеют проблемы с большими файлами ibdata1 и ib_log. В большинстве случаев это связано с неправильной конфигурацией MySQL/MariaDB или архитектурой БД.
Вся информация из таблиц InnoDB хранится в файле ibdata1, пространство которого само не используется. Я предпочитаю хранить данные таблицы в отдельных файлах ibd*. Для этого добавьте в my.cnf следующую строку:
innodb_file_per_table
или
innodb_file_per_table = 1
Если ваш сервер настроен и у вас есть продуктивные базы данных с таблицами InnoDB, сделайте следующее:
- Сделайте резервную копию всех баз данных на вашем сервере (кроме mysql и performance_schema). Вы можете получить дамп базы данных с помощью этой команды:
# mysqldump -u [username] –p[password] [database_name] > [dump_file.sql] - После создания резервной копии базы данных остановите сервер mysql/mariadb;
- Измените настройки в my.cfg;
- Удалите файлы ibdata1 и ib_log;
- Запустите демон mysql/mariadb;
- Восстановить все базы из резервной копии:
# mysql -u [username] –p[password] [database_name] < [dump_file.
sql]
sql]После этого все таблицы InnoDB будут храниться в отдельных файлах, и ibdata1 перестанет экспоненциально расти.
Сжатие таблиц InnoDB
Вы можете сжимать таблицы с текстовыми данными / данными BLOB и экономить довольно много места на диске.
У меня есть база данных innodb_test, содержащая таблицы, которые потенциально могут быть сжаты, и поэтому я могу освободить место на диске. Прежде чем что-либо делать, я рекомендую сделать резервную копию всех баз данных. Подключитесь к серверу mysql:
# mysql -u root -p
Выберите нужную базу данных в консоли mysql:
# use innodb_test;
Чтобы отобразить список таблиц и их размеры, используйте следующий запрос:
SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size in (MB)" FROM information_schema.TABLES WHERE table_schema = "innodb_test" ORDER BY (data_length + index_length) DESC;
Где innodb_test — имя вашей базы данных.
Некоторые таблицы могут быть сжаты. Возьмем для примера таблицу b_crm_event_relations. Запустите этот запрос:
mysql> ALTER TABLE b_crm_event_relations ROW_FORMAT=COMPRESSED;
После его запуска вы можете увидеть, что размер таблицы уменьшился с 26 МБ до 11 МБ из-за сжатия.
Сжимая таблицы, вы можете сэкономить много дискового пространства на вашем хосте. Однако при работе со сжатыми таблицами нагрузка на процессор возрастает. Используйте сжатие для таблиц db, если у вас нет проблем с ресурсами процессора, но есть проблема с дисковым пространством.
Сжатие таблиц MyISAM в MySQL / MariDB
Для сжатия таблиц Myisam используйте специальный запрос в консоли сервера вместо консоли mysql. Чтобы сжать таблицу, запустите следующее:
# myisampack -b /var/lib/mysql/test/modx_session
Где /var/lib/mysql/test/modx_session — это путь к вашей таблице. К сожалению, у меня не было большой таблицы и пришлось сжимать маленькие, но результат все равно можно было увидеть (файл был сжат с 25 МБ до 18 МБ):
# du -sh modx_session.
MYD25M modx_session.MYD
# myisampack -b /var/lib/mysql/test/modx_session
Compressing /var/lib/mysql/test/modx_session.MYD: (4933 records)
— Calculating statistics
— Compressing file
29.84%
Remember to run myisamchk -rq on compressed tables
# du -sh modx_session.MYD
18M modx_session.MYD
Я использовал в команде ключ -b. Когда вы добавляете его, таблица создается перед сжатием и помечается меткой OLD:
# ls -la modx_session.OLD
-rw-r----- 1 mysql mysql 25550000 Dec 17 15:20 modx_session.OLD
# du -sh modx_session.OLD
25M modx_session.OLD
Оптимизация таблиц и баз данных в MySQL и MariaDB
Для оптимизации таблиц и баз данных рекомендуется их дефрагментировать. Убедитесь, что в базе данных есть таблицы, требующие дефрагментации.
Откройте консоль MySQL, выберите базу данных и выполните этот запрос:
select table_name, round(data_length/1024/1024) as data_length_mb, round(data_free/1024/1024) as data_free_mb from information_schema.
tables where round(data_free/1024/1024) > 50 order
by data_free_mb;Таким образом, вы отобразите все таблицы с не менее 50 МБ неиспользуемого пространства:
+-------------------------------+----------------+--------------+ | TABLE_NAME | data_length_mb | data_free_mb | +-------------------------------+----------------+--------------+ | b_disk_deleted_log_v2 | 402 | 64 | | b_crm_timeline_bind | 827 | 150 | | b_disk_object_path | 980 | 72 |
data_length_mb — общий размер стола
data_free_mb — неиспользуемое место в столе
Это таблицы, которые мы можем дефрагментировать. Проверьте, сколько места они занимают на диске:
# ls -lh /var/lib/mysql/innodb_test/ | grep b_
-rw-r ----- 1 mysql mysql 402M 17 октября, 12:12 b_disk_deleted_log_v2.MYD -rw-r ----- 1 mysql mysql 828M 17 октября 13:23 b_crm_timeline_bind.MYD -rw-r ----- 1 mysql mysql 981M 17 октября, 11:54 b_disk_object_path.MYD
Чтобы оптимизировать эти таблицы, выполните следующую команду в консоли mysql:
# OPTIMIZE TABLE b_disk_deleted_log_v2, b_disk_object_path, b_crm_timeline_bind;
После успешной дефрагментации вы увидите следующий результат:
+ ------------------------------- + ---------------- + -------------- + | TABLE_NAME | data_length_mb | data_free_mb | + ------------------------------- + ---------------- + -------------- + | b_disk_deleted_log_v2 | 74 | 0 | | b_crm_timeline_bind | 115 | 0 | | b_disk_object_path | 201 | 0 |
Как видите, data_free_mb теперь равно 0, а размер таблицы значительно уменьшился (в 3-4 раза).
Вы также можете запустить дефрагментацию, используя mysqlcheck в консоли сервера:
# mysqlcheck -o innodb_test b_workflow_file -u root -p innodb_test.b_workflow_file
Где innodb_test ваша база данных и b_workflow_file название таблицы.
Чтобы оптимизировать все таблицы в базе данных, запустите эту команду в консоли сервера:
# mysqlcheck -o innodb_test -u root -p
Где innodb_test — имя базы данных
Или запустите оптимизацию всех баз на сервере:
# mysqlcheck -o --all-databases -u root -p
Если вы проверите размер базы данных до и после оптимизации, вы увидите, что общий размер уменьшился:
# du -sh
2,5 г
# mysqlcheck -o innodb_test -u root -p
innodb_test.
b_admin_notify
note : Table does not support optimize, doing recreate + analyze instead
status : OK
innodb_test.b_admin_notify_lang
note : Table does not support optimize, doing recreate + analyze instead
status : OK
innodb_test.b_adv_banner
note : Table does not support optimize, doing recreate + analyze instead
status : OK# du -sh
Таким образом, чтобы сэкономить место на вашем сервере, вы можете время от времени оптимизировать и сжимать свои таблицы и базы данных MySQL/MariDB. Не забудьте создать резервную копию базы данных перед выполнением любой работы по оптимизации.
Насколько публикация полезна?
Нажмите на звезду, чтобы оценить!
Средняя оценка / 5.
Оценок пока нет. Поставьте оценку первым.
Статьи по теме:Производительность MySQL. Часть 1. Анализ и оптимизация запросов. Хостинг в деталях
Страницы сайта генерируются медленно? Возникают ошибки 502 bad gateway и 504 gateway timeout? Хостер говорит, что сайт создает слишком большую нагрузку на процессор? Скорее всего, проблемы связаны с базой данных. В этой статье рассмотрим вопросы оптимизации производительности MySQL.
Как понять, что дело именно в MySQL
Если сайт работает на популярной CMS, то можно воспользоваться отчетом по SQL-запросам, выполняемым при генерации страницы. Например, в Drupal такой отчет доступен в модуле Devel, в Joomla – в режиме отладки, в WordPress – в расширении Debug bar. Если специальных инструментов нет, то можно до и после выполнения каждого SQL-запроса вызвать PHP-функцию microtime() и посчитать разность.
Drupal Devel
Если сайт размещается на VPS или выделенном сервере, аналогичные данные можно получить и непосредственно из MySQL.
Например, из журнала медленных запросов.
Заняться оптимизацией однозначно стоит в случаях, когда при генерации страницы суммарное время выполнения запросов к базе данных превышает 1 секунду.
С чего начать оптимизацию
Итак, вы определили, какие запросы выполняются при генерации страницы. Дальше возможны варианты:
- Есть тяжелые запросы, занимающие сотни миллисекунд.
- Запросов много, но все они выполняются достаточно быстро.
В первом случае можно попробовать оптимизировать отдельные запросы. Здесь поможет SQL-оператор EXPLAIN и знания об индексах. Это решение применимо ко всем сайтам, в том числе размещенным на виртуальном хостинге.
Во втором случае имеет смысл заняться углубленным анализом логов и тонкой настройкой MySQL. На виртуальном хостинге сделать это не получится, только на VPS и выделенных серверах.
Но прежде, чем углубляться в детали, стоит сказать о кеше запросов – быстром и простом способе снять многие проблемы. Возможность включить кеш запросов есть у владельцев VPS и выделенных серверов.
Кеш запросов
В кеше запросов (query cache) сохраняются пары запрос-ответ. Когда запрос уже есть в кеше, ответ отдается практически мгновенно. Если данные в таблицах меняются не слишком часто, происходит ощутимый прирост производительности (в противном случае кеш быстро сбрасывается).
По умолчанию кеширование выключено. Включить его можно, добавив в конфигурационный файл my.cnf строчку вида query_cache_size = 64M . Через переменную query_cache_size задается размер оперативной памяти, выделяемой под кеш, в данном случае — 64 мегабайта.
Теперь нужно перезапустить MySQL. Сделать это можно из некоторых панелей управления (в ISPmanager: Management tools — Services), либо по SSH из командной строки примерно так:/usr/local/etc/rc.d/mysql-server stop
/usr/local/etc/rc.d/mysql-server start
Всё, кеш включен. Можно попробовать открыть страницу сайта и потом обновить. Во второй раз должна загрузиться быстрее.
Есть еще несколько переменных для настройки кеша:
- query_cache_type задает режим работы кеша, когда query_cache_size установлен больше нуля. Допустимые значения query_cache_type: 0 или OFF — кеширование выключено; 1 или ON — кеширование включено для всех выражений, кроме начинающихся с SELECT SQL_NO_CACHE; 2 или DEMAND — кеширование включено только для запросов, начинающихся с SELECT SQL_CACHE.
- query_cache_limit – максимально допустимый размер, при котором результат выполнения запроса будет сохранен в кеше.
- query_cache_min_res_unit – минимальный размер блоков памяти, выделяемых под кеш. По умолчанию 4 Кб. Если у вас много результатов значительно меньшего объема, query_cache_min_res_unit можно понизить, чтобы память использовалась эффективнее. Подходящее значение можно рассчитать по формуле (query_cache_size — Qcache_free_memory) / Qcache_queries_in_cache.
Допустимые значения query_cache_type: 0 или OFF — кеширование выключено; 1 или ON — кеширование включено для всех выражений, кроме начинающихся с SELECT SQL_NO_CACHE; 2 или DEMAND — кеширование включено только для запросов, начинающихся с SELECT SQL_CACHE.Пример my.cnf для небольшого VPS:query_cache_size = 64M
query_cache_limit = 2M
query_cache_type = 1
query_cache_min_res_unit = 2K
Посмотреть текущее состояние кеша можно в phpMyAdmin на вкладке Status, либо из командной строки:
# mysql -u root -p Password: ******** mysql> SHOW GLOBAL STATUS LIKE ‘Qcache%’; +----------------------------+------------+ | Variable_name | Value | +----------------------------+------------+ | Qcache_free_blocks | 130 | | Qcache_free_memory | 56705448 | | Qcache_hits | 57092 | | Qcache_inserts | 10412 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 5036 | | Qcache_queries_in_cache | 1023 | | Qcache_total_blocks | 2409 | +----------------------------+------------+ 8 rows in set (0.
01 sec)- Qcache_free_blocks – количество свободных блоков в кеше.
- Qcache_free_memory – объем свободной ОЗУ, отведенной под кеш.
- Qcache_hits – количество запросов, результаты которых были взяты из кеша.
- Qcache_inserts – количество запросов, которые были добавлены в кеш.
- Qcache_lowmem_prunes – количество запросов, которые были удалены из кеша из-за нехватки памяти.
- Qcache_not_cached – количество запросов, которые не были записаны в кеш (с SQL_NO_CACHE или некешируемые по другим причинам).
- Qcache_queries_in_cache – количество запросов, которые находятся в кеше.
- Qcache_total_blocks – общее количество блоков.
Долю закешированных запросов от их общего числа можно посчитать по формуле Qcache_hits / (Com_select + Qcache_hits). Степень использования кеша — Qcache_hits / Qcache_inserts.
О нюансах работы кеша MySQL можно почитать на mysqlperformanceblog.com (англ.)
Оптимизация отдельных запросов
Чтобы оптимизировать тяжелый запрос, сначала его нужно исследовать.
Для этого допишите перед SELECT слово EXPLAIN, и MySQL покажет план выполнения запроса. В первую очередь интерес представляет информация об использовании индексов.
Результат работы оператора EXPLAIN
Индексы – это структуры данных, создаваемые с целью повышения производительности поиска записей в таблицах. Таблицы в базе данных могут иметь большое количество строк, которые хранятся в произвольном порядке, и их поиск по заданному критерию путем последовательного просмотра таблицы строка за строкой может занимать много времени. Индекс формируется из значений одного или нескольких столбцов таблицы и указателей на соответствующие строки таблицы и, таким образом, позволяет искать строки, удовлетворяющие критерию поиска. [источник]
Индексы — ключ к высокой производительности MySQL, их важность увеличивается по мере роста объема данных в базе. Индексы нужно создавать для столбцов, по которым
- производится поиск в части WHERE
- соединяются таблицы при JOIN
- сортируются и группируются записи при ORDER BY и GROUP BY
- производится поиск MIN() и MAX()
Индексы могут быть составными, в этом случае важен порядок столбцов.
Разбирая вывод EXPLAIN, обратите особое внимание на столбцы
- type (значение ALL — плохо)
- key (NULL — плохо)
- ref (NULL — плохо)
- extra (Using filesort, Using temporary, Using where — плохо)
Описание всех значений и пример оптимизации запроса можно посмотреть в документации.
Добавить индексы можно из phpMyAdmin или с помощью запросов вида ALTER TABLE table_name ADD INDEX index_name (column_name)
Журнал медленных запросов
Если определить тяжелые запросы «на глаз» не получается, нужно собрать более обширную статистику. В этом поможет журнал медленных запросов (slow query log).
Для включения журнала в MySQL, начиная с версии 5.1.29, задайте переменной slow_query_log значение 1 или ON; для отключения журнала — 0 или OFF. В более старых версиях используется log-slow-queries = /var/db/mysql/slow_queries.log (путь можно задать другой).
Вторая важная настройка — long_query_time — порог времени выполнения, при превышении которого запрос считается медленным и записывается в журнал.
Начиная с MySQL 5.1.21 может задаваться в микросекундах и может быть равен нулю.
Пара полезных дополнительных настроек:
- log-queries-not-using-indexes – запись в журнал запросов, не использующих индексы.
- slow_query_log_file – имя файла журнала. По умолчанию host_name-slow.log
Пример для записи в журнал всех запросов, выполняющихся дольше 50 миллисекунд:slow_query_log = 1
slow_query_log_file = /var/db/mysql/slow_queries.log
long_query_time = 0.05
log-queries-not-using-indexes = 1
Пример для старых версий MySQL, все запросы дольше 1 секунды:log-slow-queries = /var/db/mysql/slow_queries.log
long_query_time = 1
Для анализа журнала используются утилиты mysqldumpslow, mysqlsla и mysql_slow_log_filter. Они парсят журнал и выводят агрегированную информацию о медленных запросах.
mysqldumpslow – утилита из состава MySQL. Вызывается таким образом: mysqldumpslow [параметры] [файл_журнала . . Пример:
..]
mysqldumpslow
Reading mysql slow query log from /usr/local/mysql/data/mysqld51-apple-slow.log
Count: 1 Time=4.32s (4s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
insert into t2 select * from t1
Count: 3 Time=2.53s (7s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
insert into t2 select * from t1 limit N
Count: 3 Time=2.13s (6s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
insert into t1 select * from t1
Count – сколько раз был выполнен запрос данного типа. Time – среднее время выполнения запроса, дальше в скобках – суммарное время выполнения всех запросов данного типа.
Некоторые параметры mysqldumpslow:
- -t N – отображать только первые N запросов.
- -g pattern — анализировать только запросы, которые соответствуют шаблону (как grep).
- -s sort_type — как сортировать вывод. Значения sort_type: t или at — сортировать по суммарному или среднему времени выполнения запросов, c — по количеству выполненных запросов данного типа.
mysqlsla – еще одна утилита для анализа логов MySQL с аналогичной функциональностью. Пример использования:
mysqlsla -lt slow /tmp/slow_queries.log
Подробности в документации.
mysql_slow_log_filter — perl-скрипт с похожей функциональностью. Пример использования:
tail –f mysql-slow.log | mysql_slow_log_filter –T 0.5 –R 1000
Эта команда в реальном времени покажет запросы, выполняющиеся дольше 0,5 секунды или сканирующие больше 1000 строк.
Выявленные медленные запросы дальше можно оптимизировать, используя EXPLAIN и индексы.
Вторая часть статьи будет посвящена тонкой настройке MySQL. Материал находится в разработке.
—
Евгений Демин, http://unixzen.ru
Дмитрий Сергеев, http://hosting101.ru
советов по настройке производительности MySQL (которые работают в 2023 году)
MySQL, будучи самой популярной системой управления реляционными базами данных, все еще время от времени требует оптимизации.
Более того, в случае
больших и сложных наборов данных регулярные действия по оптимизации необходимы для правильной работы системы.
Оптимизация производительности MySQL обычно включает настройку, профилирование и мониторинг производительности на нескольких уровнях. Чтобы настроить производительность MySQL, вы не обязательно иметь обширный опыт и глубокое понимание SQL.
В этой статье мы познакомим вас с основными методами настройки производительности, чтобы вы могли обеспечить стабильность, надежность и скорость ваши приложения, управляемые базой данных.
Проверьте рекомендуемые требования к аппаратному и программному обеспечению для MySQL
Первое, что нужно сделать, особенно если вы владелец слабого ПК, это проверить оптимальные аппаратные и программные требования для MySQL,
поскольку аппаратные ограничения могут существенно повлиять на производительность.
Минимальные аппаратные требования к серверу базы данных MySQL (для версий 5.7–8.0):
- Процессор 1 ГГц
- 512 МБ ОЗУ
- Место на жестком диске в зависимости от размера базы данных
Также стоит упомянуть, что лучше использовать самую последнюю официальную версию MySQL, если это возможно.
Оптимизация использования памяти, диска и процессора
На аппаратном уровне вы можете предпринять ряд действий для улучшения аппаратных и программных ресурсов.
Место на диске Если вы используете традиционный жесткий диск (HDD) и хотите повысить производительность, вам следует подумать о переходе на SSD.
Официальная документация MySQL явно не определяет параметры дискового пространства или памяти, необходимые для эффективной работы сервера MySQL, поскольку
в первую очередь они зависят от размера потенциальной базы данных или баз данных. Тем не менее, было бы неплохо контролировать производительность вашего диска, используя sar и iostat средства повышения производительности системы, например. Если использование диска значительно превышает использование других ресурсов, вам следует
определенно добавьте больше памяти или обновитесь до более быстрой.
ОЗУ Нехватка памяти также может серьезно повлиять на производительность базы данных. Это может показаться банальным, но если на вашем сервере регулярно не хватает памяти и
Производительность RAM Disk не удовлетворяет, стоит добавить больше памяти.
Когда у вас заканчивается оперативная память, сервер MySQL кэширует физическую память, что снижает производительность. Таким образом, оптимизация памяти MySQL чрезвычайно важна.
ЦП Оптимизация использования процессора MySQL должна начинаться с тщательного анализа процессов MySQL, происходящих на вашем компьютере, и требуемого процента использования процессора. Обновление ЦП недешево, однако, если это узкое место, обновление будет необходимо.
Интернет-соединение Сеть является важной частью инфраструктуры MySQL, и важно отслеживать и анализировать сетевой трафик, чтобы убедиться, что у вас достаточно ресурсов.
для управления вашими рабочими нагрузками. Убедитесь, что у вас хорошее и стабильное подключение к Интернету для правильной работы сервера MySQL.
Инструменты для настройки производительности программного обеспечения
Как мы уже упоминали, вы можете оптимизировать производительность MySQL на аппаратном и программном уровнях. Давайте теперь посмотрим на настройку производительности программного обеспечения MySQL.
Настройка производительности MySQL с точки зрения программного обеспечения включает в себя настройку параметров сервера MySQL, повышение производительности запросов MySQL, настройку MySQL индексы, переход на движок хранения MySQL InnoDB и т. д. Рассмотрим все это подробно.
Использование индекса MySQL для повышения производительности
Правильная индексация для повышения производительности непроста и требует определенного уровня знаний, однако это одно из лучших улучшений производительности, которое вы можете сделать для повышения производительности.
ваша база данных.
MySQL использует индексы в качестве книжного указателя или дорожной карты для быстрого поиска значений для заданного запроса. Без индексов MySQL будет сканировать всю таблицу построчно, чтобы найти нужные данные. Таким образом, оптимизация индекса направлена на ускорение извлечения данных. Индексы невидимы для пользователей и содержат информацию о том, где хранятся фактические данные. Это также стоит отметив, что длина индекса MySQL для таблиц InnoDB имеет ограничения в зависимости от формата строки.
Индексы MySQL чрезвычайно полезны для больших наборов данных, и настройка индекса — это правильное решение, если ваша база данных быстро растет. Индексы особенно полезны для следующих
операции: поиск строк, соответствующих предложению WHERE, получение данных с помощью JOIN, сортировка и группировка данных с помощью ORDER BY и GROUP BY.
Так почему бы тогда не вставить как можно больше индексов? Это было бы плохой идеей — ненужные индексы занимают место и тратят время системы, не говоря уже о том, что они также увеличивают стоимость запросов, поскольку необходимо обновлять индексы. Таким образом, вы должны найти правильный баланс для достижения оптимального использования индекса MySQL.
Повышение производительности с помощью InnoDB
Одним из первых советов по настройке для тех, у кого большая нагрузка на базу данных, будет попытка переключиться на InnoDB с механизма хранения MyISAM. Имея кластеризованный индекс с данными на страницах и последовательными физическими блоками, InnoDB имеет лучшую производительность для больших объемов данных по сравнению с MyISAM.
InnoDB также может похвастаться богатым набором переменных и дополнительных параметров, которые можно настроить для дальнейшего повышения производительности MySQL.
Параметры производительности InnoDB
более обширны, и поэтому существует больше способов настроить InnoDB для повышения производительности по сравнению с настройкой MyISAM.
Оптимизация запросов MySQL
Теперь давайте посмотрим, как оптимизировать запрос MySQL для повышения производительности и скорости. Для тех, кто хочет улучшить запросы MySQL, это было бы рекомендуется следовать следующим методам оптимизации.
Добавьте индексы к столбцам, используемым в предложениях WHERE, ORDER BY и GROUP BY
Таким образом, вы повысите производительность запросов MySQL, поскольку сервер MySQL будет извлекать результаты из базы данных значительно быстрее.
Укажите необходимые столбцы в операторах SELECT
Старайтесь избегать использования SELECT * FROM, так как он извлекает все столбцы таблицы, что создает дополнительную нагрузку на сервер и снижает его производительность.
Возьмите за правило всегда указывать столбцы в операторах SELECT.
Используйте DISTINCT и UNION с осторожностью
Еще один хороший совет по настройке запросов — использовать операторы DISTINCT и UNION только в случае необходимости, поскольку запросы с ними приводят к накладным расходам на сервер и, как правило, увеличивают нагрузку на сервер.
время отклика. Попробуйте заменить UNION на UNION ALL и DISTINCT на GROUP BY, чтобы повысить эффективность процесса.
Избегайте использования подстановочных знаков в начале шаблонов LIKE
Запросы MySQL с операторами LIKE часто приводят к падению производительности сервера, поэтому их следует использовать осторожно. MySQL не может использовать индексы, когда LIKE
шаблон начинается с подстановочного знака, например, ‘%xyz’, и в этом случае выполняет полное сканирование таблицы.
Вы должны помнить об этом при оптимизации запросов MySQL и
попробуйте вместо этого использовать ‘xyz%’, когда это возможно.
Использовать ВНУТРЕННИЕ СОЕДИНЕНИЯ вместо ВНЕШНИХ СОЕДИНЕНИЙ
Используйте OUTER JOIN только при необходимости. MySQL выполняет гораздо больше работы по получению результатов для ВНЕШНИХ СОЕДИНЕНИЙ по сравнению с ВНУТРЕННИМИ СОЕДИНЕНИЯМИ. Рекомендуем проверить работоспособность
ваших запросов JOIN, и в случае, если это вас не устраивает, начните преобразовывать ваши ВНЕШНИЕ СОЕДИНЕНИЯ во ВНУТРЕННИЕ СОЕДИНЕНИЯ, когда это возможно.
Оптимизация MySQL JOIN может привести к драматическим последствиям.
улучшение производительности.
Настройка параметров сервера для повышения производительности
Теперь давайте сосредоточимся на том, как оптимизировать параметры сервера MySQL с точки зрения настройки производительности.
Для этого вам нужно будет настроить файл конфигурации (my.cnf/my.ini).
innodb_buffer_pool_size
Этот параметр указывает объем памяти, выделенный MySQL для пула буферов InnoDB. Рекомендуемое значение этого параметра составляет 70-80% от доступного.
Память. Чем больше ваши наборы данных, тем больше должно быть значение.
макс_соединение
Этот параметр определяет максимально допустимое количество одновременных клиентских подключений и имеет значение по умолчанию 151. Во избежание получения сообщения «Слишком много подключений»
ошибка, значение может быть увеличено. Однако имейте в виду, что слишком много открытых подключений может повлиять на производительность.
query_cache_size
Этот параметр задает общий объем памяти, выделенный для кэша запросов.
Оптимальное значение для него зависит в первую очередь от вашего рабочего случая и должно быть
определяется ориентировочно. Идея состоит в том, чтобы начать с очень малого, например, 10 МБ, а затем постепенно увеличивать его до 100–200 МБ. Настройка query_cache_size,
не забудьте включить кеш запросов (тип кеша запросов ON). Обратите внимание, что большой размер кэша запросов может привести к серьезному снижению производительности.
innodb_io_capacity
Этот параметр указывает количество операций ввода-вывода в секунду, разрешенное для задач, выполняемых в фоновом режиме, и имеет значение по умолчанию 200. Как правило,
значение около 100 подходит для жестких дисков среднего уровня, тогда как для более быстрых и современных устройств хранения данных более высокие значения будут выгодны.
innodb_log_file_size
Этот параметр указывает размер в байтах для каждого файла журнала повторов MySQL в группе журналов и имеет значение по умолчанию 134 217 728 (около 128 МБ).
innodb_log_files_in_group
Параметр, в свою очередь, указывает количество лог-файлов в лог-группе и имеет значение по умолчанию 2.
Если значение innodb_log_file_size мало для вашей рабочей нагрузки, а ваше приложение интенсивно записывает,
мы рекомендуем увеличить его. Однако слишком большое значение innodb_log_file_size увеличит время восстановления после сбоя. Так что вам придется найти его оптимальный размер.
Профилирование MySQL и оптимизация запросов с помощью
dbForge Studio для MySQL
dbForge Studio для MySQL поставляется с расширенным профилировщиком MySQL, который позволяет собирать максимально полную статистику о выполненных запросах, обнаружение медленных запросов и устранение проблем с производительностью любого рода.
С помощью инструмента настройки производительности dbForge MySQL вы можете:
- Оптимизация запросов с помощью плана EXPLAIN
- Статистика сеанса мониторинга
- Сравнение результатов профилирования запросов
- Определить самые дорогие запросы
Правила оптимизации производительности MySQL
- Всегда проверяйте результат своих усилий по оптимизации в тестовой среде
- Никогда не оптимизируйте без сравнительного анализа
- Оптимизировать таблицы
- Меняйте только одну вещь за раз
- Добавьте мониторинг производительности в свою повседневную жизнь
- Задокументировать результаты
Еще 10 важных советов по настройке производительности MySQL
Форум новых технологий
org/Person» itemprop=»author»> Тибор Кёроч и Франсиско Борденав, ИнфоМир |О компании |
Анализ новых технологий технологами
Дизайн схемы, индексы, запросы, конфигурации, ввод-вывод… что может пойти не так? Следуйте этим 10 важным советам, чтобы ваши серверы MySQL работали.
Thinkstock Содержание- Совет по производительности MySQL № 1. Дизайн схемы так же важен, как и любые другие настройки MySQL
- Совет по производительности MySQL № 2: вторичные ключи вам не враги
- Совет по производительности MySQL № 3: строки могут обслуживаться из индексов
- Совет по производительности MySQL № 4: проверка запросов, проверка запросов, проверка запросов
- Совет по производительности MySQL № 5: Видимость имеет значение
- Совет по производительности MySQL № 6: будьте осторожны с инструментами настройки
- Совет по производительности MySQL № 7: операции ввода-вывода по-прежнему обходятся дорого
- Совет по повышению производительности MySQL № 8. Воспользуйтесь преимуществами общих табличных выражений
- Совет по производительности MySQL № 9: Помните об облаке
- Совет по повышению производительности MySQL № 10. Поддерживайте актуальность реплик
Воспользуйтесь преимуществами общих табличных выраженийПоказать больше
MySQL является наиболее широко используемой базой данных с открытым исходным кодом в мире и занимает второе место по популярности среди баз данных в целом. Это эффективная система управления реляционными базами данных, которая уже много лет лежит в основе популярных приложений. Тем не менее, это может быть сложно в использовании, и есть много возможностей для повышения производительности.
За последние несколько лет для MySQL также произошли некоторые важные изменения. Эта статья обновляет предыдущий набор советов по настройке производительности MySQL, предоставленных бароном Шварцем. Хотя предыдущая статья по-прежнему актуальна, есть дополнительные шаги, которые вы можете предпринять для достижения наилучшей производительности при развертывании MySQL.
Вот еще 10 советов по настройке производительности MySQL, которые можно добавить в свой список.
Совет по производительности MySQL № 1. Дизайн схемы так же важен, как и любые другие настройки MySQL
Разработка схемы — одна из самых важных вещей, которые вы будете делать в своей базе данных. Это принцип технологии кросс-реляционных баз данных, поскольку нормальные формы были введены еще в 1970-х годах. Поскольку MySQL перешел на InnoDB в качестве механизма хранения по умолчанию в версии 5.6, дизайн схемы становится еще более важным.
Почему это? В InnoDB все является первичным ключом! Это относится к тому, как InnoDB организует данные. В InnoDB первичный ключ сгруппирован, и каждый вторичный ключ добавляет к первичному ключу указатель входа. Если вы не примете это во внимание при разработке схемы, это негативно скажется на вашей производительности.
Данные также хранятся с использованием индексов B-дерева, поэтому вставка данных упорядоченным образом (т.
е. с использованием квазипоследовательных значений) предотвращает фрагментацию первичного ключа и, таким образом, сокращает количество операций ввода-вывода, необходимых для поиска листовых узлов.
В некоторых случаях использование последовательных первичных ключей не является правильным выбором — хорошим примером здесь является универсальный уникальный идентификатор или UUID. Здесь вы можете найти более подробное описание проблем, связанных с UUID и первичными ключами. Однако, вообще говоря, мы рекомендуем использовать последовательные первичные ключи для большинства случаев использования.
Совет по производительности MySQL № 2: Вторичные ключи вам не враги
Вторичные ключи обновляются фоновым процессом. В результате влияние на производительность не так серьезно, как можно было бы ожидать. Вместо этого проблема заключается в занимаемом месте на диске, поскольку добавление вторичных ключей увеличит требования к хранилищу.
Фильтрация по полю, не имеющему индекса, может привести к полному сканированию таблицы при каждом выполнении запроса.
Это, конечно, может привести к огромному влиянию на производительность. Поэтому лучше иметь вторичный ключ, чем упустить его.
При этом вам не следует чрезмерно индексировать свои базы данных, так как выполнение большого количества индексов может не обеспечить желаемого повышения производительности. В то же время эти дополнительные индексы могут увеличить ваши затраты на хранение, и InnoDB должен выполнять множество фоновых операций, чтобы поддерживать их в актуальном состоянии.
Совет по производительности MySQL № 3: Строки могут обслуживаться из индексов
InnoDB может находить и фактически обслуживать строки непосредственно из индексов, тогда как вторичный ключ указывает на первичный ключ, а первичный ключ содержит саму строку. Если буферный пул InnoDB достаточно велик, он также может хранить большую часть данных в памяти. Вы даже можете использовать составные ключи, которые обычно более эффективны для запросов, чем отдельные ключи для каждого столбца. MySQL может использовать один индекс для доступа к таблице, поэтому, если вы выполняете запросы с таким предложением, как ГДЕ x=1 и y=2 тогда лучше иметь индекс над x,y , чем отдельные индексы над каждым столбцом.
Кроме того, составной индекс поверх x,y также может повысить производительность следующего запроса: что в памяти.
На практике вы можете повысить производительность, используя составной индекс, когда у вас есть такая возможность. Всякий раз, когда вы разрабатываете индексы, вам нужно думать о них так, как они читаются. Это означает, что индексы всегда читаются слева направо, поэтому при таком запросе:
ВЫБЕРИТЕ a,b,c ИЗ таблицы, ГДЕ a=1 и b=2
Тогда индекс над a,b поможет с запросом. Но если запрос в таком формате:
SELECT a,b,c FROM table WHERE b=2
Тогда индекс будет бесполезен и вызовет полное сканирование таблицы. Идея всегда читать индексы слева применима и к некоторым другим случаям. Например, для следующего запроса:
SELECT a,b,c FROM table WHERE a=1 and c=2
Тогда индекс выше a,b,c будет читать только первый столбец, потому что нет фильтрации предложения WHERE по столбцу b .
Таким образом, в этом случае MySQL может частично прочитать индекс, что лучше, чем полное сканирование таблицы, но все же недостаточно для получения наилучшей производительности запроса.
Еще один элемент, связанный с дизайном запроса, — это метод крайнего левого индекса, так как это обычная оптимизация, используемая в MySQL. Например, индекс a,b,c не будет охватывать такой запрос, как 9.0228 выберите a,c, где c=x , потому что запрос не может пропустить первую часть индекса, то есть a,b . То же самое касается запроса типа select c,count(c) где a=x group by c . Этот запрос не может использовать индекс a,b,c для группы по , поскольку он не может пропустить индекс b . Однако, если у вас есть запрос типа , выберите c,count(c) где a=x и b=y сгруппируйте по c , который фильтрует a,b и выполняет группу по на c , то один индекс на a,b,c может помочь как с фильтрацией, так и с группой по .
Совет по производительности MySQL № 4: Запрашивайте обзоры, запрашивайте обзоры, запрашивайте обзоры
Наличие автомобиля Формулы-1 еще не означает победы в гонке. Нет, если посадишь за руль неопытного водителя, и он разобьется на первом же повороте. Точно так же у вас может быть самый настроенный сервер MySQL в мире, но если у вас будут плохие запросы, ваша база данных будет работать медленнее, чем должна быть.
Вы должны регулярно пересматривать дизайн своего запроса по мере того, как ваше приложение меняется с появлением новых функций и исправлением ошибок. Набор данных и шаблоны использования приложения, вероятно, также со временем изменятся, и все это может повлиять на производительность запросов.
Выделение времени для просмотра запросов и контроля времени выполнения запросов очень важно. Для этого вы можете использовать журнал медленных запросов или Performance Schema, но внедрение инструмента мониторинга поможет вам получить еще более качественные данные.
Имейте в виду, что не всегда самый медленный запрос является самым важным для исправления. Например, у вас может быть запрос, который занимает 30 секунд, но выполняется два раза в день, а также запрос, который занимает одну секунду и выполняется 100 раз в минуту. Для большой победы вам следует начать оптимизировать второй запрос, так как его улучшение может сэкономить много времени и ресурсов в долгосрочной перспективе.
Совет по производительности MySQL № 5: Видимость имеет значение
Мониторинг — один из ключевых элементов настройки производительности. Не зная текущей нагрузки и закономерностей, трудно дать какие-либо конкретные рекомендации. В последние годы MySQL улучшил представление низкоуровневых метрик MySQL/InnoDB, что может помочь в понимании рабочей нагрузки.
Например, в более ранних версиях схема производительности была узким местом и оказывала значительное влияние, особенно если у вас было много таблиц. В последних версиях MySQL многие изменения, такие как новый словарь данных, повысили производительность, и теперь у вас может быть много таблиц без существенного влияния.
Большинство современных инструментов мониторинга так или иначе используют Performance Schema, поэтому рекомендуется проверить эти инструменты и выбрать тот, который лучше всего соответствует вашим потребностям. Эта видимость данных о производительности может быть огромным преимуществом в ваших расследованиях.
Совет по производительности MySQL № 6: будьте осторожны с инструментами настройки
Некоторые общие рекомендации, данные инструментами настройки, будут работать в большинстве случаев. Однако каждая рабочая нагрузка и каждая схема отличаются друг от друга. В некоторых случаях общие рекомендации инструментов настройки не работают, и разумно быть осторожным, доверяя этим рекомендациям. Даже innodb_dedicated_server , который является собственным инструментом Oracle и доступен в MySQL, может внести сомнительные изменения в конфигурацию.
Например, установка innodb_buffer_pool_size на 75% от общего объема оперативной памяти является хорошим общим практическим правилом.
Однако в настоящее время у вас могут быть серверы с сотнями гигабайт оперативной памяти. Если у вас 512 ГБ ОЗУ, 128 ГБ останутся свободными и не будут выделены для пула буферов, что является большой тратой.
innodb_log_file_size и innodb_log_files_in_group также определяются на основе объема оперативной памяти. На серверах с более чем 128 ГБ ОЗУ этот параметр не имеет особого смысла, поскольку он создаст 64 файла журнала повторного выполнения (да, 64!) по 2 ГБ каждый. В результате на диске будет храниться 128 ГБ журналов повторного выполнения. В большинстве случаев нет необходимости в таких больших файлах журналов повторного выполнения даже в самых загруженных средах. Поэтому это не очень хорошая рекомендация.
innodb_flushing_method — единственное правильно сконфигурированное значение, когда включена автоматическая настройка. Эта переменная задает для метода сброса значение O_DIRECT_NO_FSYNC , что является рекомендуемым методом при использовании файловых систем Ext4 или XFS, поскольку позволяет избежать двойной буферизации данных.
Хорошей рекомендацией было бы установить innodb_buffer_pool_size на 75% или 80% на выделенных серверах. На серверах с большим объемом оперативной памяти, т. е. более 128 ГБ, увеличьте это значение до 9.0% и даже больше при правильном профилировании потребления памяти. Аналогично, в большинстве случаев с innodb_log_file_size и innodb_log_files_in_group начните с двух файлов по 2 ГБ каждый и отслеживайте операции записи журнала. Обычно при определении размера журналов повторного выполнения рекомендуется покрывать примерно один час операций записи.
Что касается innodb_flush_method , этот параметр должен быть установлен либо на O_DIRECT , либо на O_DIRECT_NO_FSYNC для современных файловых систем Linux, таких как Ext4 или XFS.
Совет по производительности MySQL № 7: Операции ввода-вывода по-прежнему обходятся дорого
MySQL и InnoDB пытаются свести к минимуму количество выполняемых ими операций ввода-вывода, поскольку доступ к слою хранения дорого обходится с точки зрения производительности приложений.
Есть несколько параметров, которые могут повлиять на количество операций ввода-вывода, выполняемых InnoDB. Два из этих параметров часто понимают неправильно, и их изменение часто вызывает проблемы с производительностью.
innodb_io_capacity и innodb_io_capacity_max — это переменные, связанные с количеством операций ввода-вывода для очистки в фоновом режиме. Многие клиенты увеличивают значения этих параметров, чтобы воспользоваться преимуществами современных твердотельных накопителей, которые могут обеспечить очень высокую пропускную способность ввода-вывода при относительно низких задержках. Хотя идея кажется логичной, увеличение параметров пропускной способности ввода-вывода может привести к нескольким проблемам.
Первая проблема заключается в снижении производительности из-за того, что InnoDB слишком быстро сбрасывает грязные страницы, что снижает возможность изменения страницы более одного раза перед сбросом. Хранение грязных страниц в памяти может значительно сократить количество операций ввода-вывода, необходимых для записи данных в хранилище.
Во-вторых, у твердотельных накопителей есть ожидаемое количество операций записи, прежде чем они увидят падение производительности. Таким образом, увеличение количества операций записи может повлиять на срок службы ваших твердотельных накопителей, даже если вы используете диски высокого класса.
Облачный хостинг в наши дни популярен, и запуск вашего экземпляра службы MySQL в облаке может быть очень полезным. Однако серверы в облаке часто будут иметь ограничения на ввод-вывод или будут взимать дополнительную плату за использование большего количества операций ввода-вывода. Зная об этих ограничениях, вы можете тщательно настроить эти параметры, чтобы убедиться, что эти ограничения не достигнуты, а операции ввода-вывода сведены к минимуму.
Важно также упомянуть innodb_lru_scan_depth , потому что этот параметр определяет, насколько далеко вниз по списку страниц LRU пула буферов сканирует поток очистки страниц на наличие грязных страниц для очистки. Если у вас есть рабочая нагрузка с большим количеством операций записи с большим пулом буферов и множеством экземпляров пула буферов, вы можете попробовать уменьшить эту переменную, чтобы использовать меньше операций ввода-вывода.
Хорошей рекомендацией для является сохранение значений по умолчанию, если вы не знаете, что их нужно изменить .
Также стоит отметить, что новейшие твердотельные накопители специально оптимизированы для транзакционных баз данных. Одним из примеров является Western Digital, которая обратилась за помощью к экспертам, чтобы помочь им выполнить требования для новой волны создаваемых приложений.
Совет по повышению производительности MySQL № 8. Воспользуйтесь преимуществами общих табличных выражений
В MySQL 8.0 появились общие табличные выражения (CTE), которые помогают избавиться от вложенных запросов, создающих производные таблицы. Эта новая функция позволяет создавать настраиваемые запросы и ссылаться на результаты, как если бы они были временной таблицей или представлением. Разница в том, что на CTE можно ссылаться несколько раз в транзакции без необходимости их явного создания и удаления.
Учитывая, что CTE материализуются только один раз, они, как правило, работают быстрее в сложных транзакциях, выполняющих несколько запросов.
Кроме того, поддерживается рекурсия CTE, поэтому вы можете легко создавать сложные структуры на языке SQL, такие как иерархические модели и ряды. Если вам нужна дополнительная информация о CTE, вы найдете введение здесь.
Совет по повышению производительности MySQL № 9. Помните об облаке
Существует множество различных облачных вариантов развертывания MySQL, от реализации экземпляра сервера MySQL в виртуальной машине, которой вы управляете, до использования базы данных в качестве службы ( DBaaS) решение. Диапазон вариантов огромен.
Многие из этих сервисов обещают значительно повысить производительность и решить все ваши проблемы. В некоторых простых случаях использования это может быть правдой. Однако даже в облаке вы должны знать и понимать основные принципы работы с базами данных, иначе ваши затраты значительно увеличатся. Это увеличение стоимости часто происходит потому, что вы, по сути, решаете проблемы, добавляя больше оборудования для решения проблемы, а не исправляя дизайн.