Как ускорить update mysql
Ускоряем MySQL insert/update в 5-10 раз
Немного теории. В операционных системах UNIX существует раздел файловой системы, который физически находится в оперативной памяти, но позволяет работать с ним как с обычным дисковым накопителем. Скорость доступа к блоку жесткого диска приблизительно равна 1 мс. Скорость доступа к памяти — 0.001 мс. Попробуем применить это к БД MySQL, чтобы выжать максимум из операций insert/update.
Сперва проверим скорость случайной записи на жесткий диск:
Теперь то же самое для shared memory (/run/shm или /dev/shm):
Сравним результаты и увидим, что время создания 1000 файлов уменьшилось в 574 раза. Хорошо. Значит, следует ожидать прирост скорости записи в БД.
1) Проверяем размер и свободное место для /run/shm
2) Проверяем сколько места занимает БД
Значит, что базу мы можем перенести в /run/shm
3) Останавливаем MySQL:
4) Создаем директории и копируем данные:
* Если сервис не стартует — смотрим /var/log/mysql/error.log
Теперь самое интересное. Проверяем, что получилось.
Тест на жестком диске я провел заранее, поэтому сразу привожу результаты.
Update выполнялся по случайному интервалу [1 000 000 — 9 000 000] для первичного ключа (id). Крайние значения отброшены, чтобы движок «копался» внутри таблицы.
Существенный прирост скорости на INSERT и еще больший на UPDATE.
Меньше для вставки, т.к. MySQL производит пересчет индексов и организацию данных.
В настройках MySQL выставлено:
innodb_buffer_pool_size = 1024M
Если ставить меньше, то скорость UPDATE для HDD естественно падает.
innodb_flush_log_at_trx_commit = 2
Как таковых транзакций здесь у нас нет и на скорость это не влияет. Тем не менее, оставляем это значение равным 2.
При такой схеме критически важно писать бинарный лог и регулярно делать бэкап. Максимально снизить издержки записи мы можем только указав для бинарника отдельный жесткий диск. Последовательная скорость записи на винчестер намного выше случайной. Поэтому ставим в систему дополнительный жесткий диск, монтируем его, например, в /mnt/hddbin/, и указываем в my.ini путь для бинарного лога: log_bin = /mnt/hddbin/mysql-bin.log
Не забываем добавить скрипты для перезагрузки и останова системы. Смотрим папку /etc/rc*. Обычно это 0 (отключение системы) и 6 (перезагрузка). Мануал, как добавить скрипты легко найти в гугле. Скрипт перед перезагрузкой или выключением системы останавливает MySQL, затем копирует папку /run/shm/mysql-lib на жесткий диск. При включении системы скрипт восстанавливает данные с жесткого диска в папку /run/shm/mysql-lib и после запускает MySQL.
Так же добавляем простенький bash или perl скрипт для мониторинга свободной памяти в /run/shm. Можно подключить Zabbix.
Как ускорить работу MySQL и снять нагрузку с дисковой подсистемы
Любой успешный проект рано или поздно сталкивается с проблемой роста. Число посетителей веб-сайта увеличивается, веб-сервер обрабатывает бóльшее количество соединений, растёт поток запросов к базе данных. В определённый момент времени отзывчивость сайта снижается: страницы загружаются медленнее, что, согласно многочисленным исследованиям, влияет на конверсию.
Причины увеличения времени загрузки страниц могут быть самыми разными. В этой статье мы рассмотрим одну из наиболее типичных ситуаций, а именно запросы к базе данных MySQL выполняются долго, и присутствует высокая нагрузка на дисковую подсистему.
В первую очередь следует выяснить характер нагрузки на диски. В этом поможет утилита iostat. В Ubuntu она устанавливается с пакетом sysstat:
Как ускорить чтение
Допустим, диски загружены запросами на чтение. Что можно сделать, чтобы ускорить отдачу данных? Закэшировать данные в памяти. MySQL предоставляет возможность использования разных хранилищ, или движков (storage engines), для доступа к данным, поэтому подход к кэшированию разный. Рассмотрим два наиболее популярных движка: MyISAM и InnoDB.
Движок MyISAM не имеет кэша для данных. Но мы по-прежнему можем ускорить чтения из таблиц MyISAM. Дело в том, что ядро Linux кэширует все прочитанные файлы в области оперативной памяти, которая называется pagecache. Разумеется, файлы с таблицами MyISAM также попадают в этот кэш. Объём pagecache можно узнать из вывода команды free:
Максимальной производительности чтения можно добиться, если объём pagecache равен объёму данных MyISAM.
Как ускорить запись
Увеличить производительность MySQL при большом объёме записи можно с помощью тонкой настройки параметров сервера.
По умолчанию InnoDB сбрасывает изменённые данные на диск с помощью системного вызова fsync(). При этом операционная система не гарантирует, что данные попадут в хранилища сию секунду, т.к. данные сперва проходят через буфер, поддерживаемый ядром. Буферизация необходима для ускорения ввода/вывода.
При задействовании кэша RAID-контроллера повысить производительность операций записи в БД можно, отключив ненужную буферизацию на уровне операционной системы. Для этого требуется выставить переменную MySQL innodb_flush_method в значение O_DIRECT, после чего перезагрузить систему управления базы данных. Снизить нагрузку на диски также может изменение переменной innodb_flush_log_at_trx_commit. Для соответствия требованиям ACID движок InnoDB хранит логи транзакций, или redo-логи, в которые записываются все запросы на изменение данных. Эти логи используются в процессе восстановления после аварийного останова системы управления базами данных.
Оптимизировать дисковые операции записи помогает правильный выбор размера redo-логов. Для этого есть несложное правило. Достаточно замерить объём данных, который записан в лог за одну минуту. Эту операцию нужно выполнять в момент дневной пиковой нагрузки:
Из примера видно, что за минуту в лог InnoDB записывается 2,44 Мб данных. Объём лога следует подбирать таким образом, чтобы в него умещался объём данных за час. В таком случае у InnoDB будет достаточно времени, чтобы изменить порядок запросов на ввод/вывод для достижения последовательной записи. В нашем примере за один час через redo-логи проходит 150 Мб данных, поэтому переменную innodb_log_file_size следует выставить в значение не менее 75M. Если объём лога выбрать слишком большим, то увеличится время InnoDB Crash Recovery, что увеличит даунтайм при аварийном перезапуске (стоит отметить, что в MySQL 5.5 время Crash Recovery зависит от размера InnoDB-лога в меньшей степени).
Вывод
Разумеется, все эти советы не являются исчерпывающими. Ключом к быстрой работе БД является понимание ваших данных, грамотно спроектированная схема и удачно составленные запросы. Тем не менее ряд эффективных оптимизаций можно произвести на уровне сервера.
Медленный UPDATE в MySQL? Разгоним!
Небольшое введение
Забыл оговориться, что этот проект я разрабатывал на CMS 1С-Битрикс. Это был не типовой интернет-магазин, это была уникальная разработка закрытой системы заказа запчастей с кучей нюансов.
И вот настал тот день, когда нужно было наладить полноценную обработку присылаемых выгрузок.
Пути решения поставленной задачи
Мысленно понимая, что в один шаг скрипт мне не обработает такое количество информации (как оказалось позже, это была опрометчивая мысль), было принято решение написать пошаговое обновление каталога, используя AJAX.
Запускаю скрипт второй версии. На сей раз я поставил время шага 60 секунд. Жду. «Обновлено 232 элементов из 24274». Хм.. Ну, что ж, уже лучше. Но не идеально же! Причем ого-го как не идеально! В чем же дело? Почему так медленно? И я вежливо ушел курить интернет.
Теория и практика
Изучив достаточное количество материалов, я начал анализировать. И начал я с анализа запросов к базе данных.
Небольшое отступление. Я предварительно изменил место хранения свойств элементов инфоблока в отдельной таблице. Результата это не дало. Но только на первом этапе. Идем далее.
Итак, воспользовавшись встроенным инструментом выполнения SQL-запросов Битрикса, я выполнил свой запрос на обновление одного элемента:
Гребаная печальбеда, 0.3 секунды! На один запрос! И это в лучшем случае. При повторении процедуры несколько раз, бывало выходило и 0.8. Отсюда мы и получаем около 40 запросов за 30 секунд, что просто ни о чем.
Хорошо, надо собраться с силами и продолжать! Я, честно признаться, не сильный знаток в области глубокого анализа и оптимизации MySQL баз. Но делать нечего, придется вникать.
Я честно не знал, что значат все эти поля, просто догадывался. Поэтому ушел гуглить. И нагуглил. Мать честная! Это что же получается!
Оптимизация и индексы
Нам важны 2 столбца из этой таблицы: это столбцы key и rows. Начну со второго. Rows указывает на то, сколько строк было затронуто при запросе. В моем случае были затронуты ВСЕ строки. Каждый раз, при каждом UPDATE, MySQL выбирал мне 235 451 запись, проходился по ним, находил нужную и обновлял. Та-дам! Вот тебе и 0.3 секунды на один запрос. Еще бы, обойди попробуй быстрее такое количество информации.
Как же решить эту проблему? Оказывается, существует такая вещь, как индексы таблиц. Они отображаются в столбце key.
Индексы бывают разных типов и для разных целей. Смысл их в том, чтобы проиндексировать необходимые вам столбцы таблицы. Нужно это для того, чтобы базе данных не пришлось пробегаться снова по всем записям в поиске нужной, а сразу обратиться к ней. В моем случае я создал индекс таблицы по столбцу с артикулом, так как он уникальный у каждого товара и обновление идет именно по нему (в условии WHERE).
Помните, я говорил, что вынес все свойства инфоблока в отдельную таблицу? Это оказалось чертовски удобно, ведь не нужно совершать дополнительных запросов на: поиск товара по артикулу, затем на поиск свойств, которые надо обновить по найденному ID товара, и уже только потом непосредственно запрос на обновление. В моем раскладе все свойства были в одной таблице и достаточно было выполнить лишь один запрос UPDATE.
Вот сейчас предельно важная информация! Никогда, запомните, никогда не выполняйте запрос на создание индекса через админку Битрикса. Сайт рухнет и все записи сотрутся. Именно так и случилось со мной, пришлось восстанавливать из бэкапа, благо он был.
Для создания индексов таких больших таблиц лучше использовать командную строку и утилиту mysql. Просто подключаетесь по SSH к серверу, подключаетесь к mysql и выполняете запрос на создание индекса.
Вот запрос на создание простого индекса, который я использовал:
Финальные замеры
После всего проделанного снова проводим замер запроса:
Примерно в 500 раз быстрее, Карл! Ну ничего себе! А что нам скажет EXPLAIN:
Rows стало равным 1, как раз то, чего мы и добивались. Теперь MySQL знает, где искать эту запись и ей не надо вытаскивать все. Также в столбце key и possible_keys мы видим название нашего созданного индекса.
Ну, что ж, осталось запустить обновление тех 24274 товаров и посмотреть, за сколько же пройдет у нас обновление. Время шага оставляю 60 секунд. Жамкаю «Обновить».
Послесловие
Вот таким нехитрым. хотя. вполне себе хитрым способом, я смог разогнать скорость обновления товаров более, чем в 500 раз, и сократить время обновления 24 000 товаров с 5 часов до феноменальных 4 секунд.
Оптимизация производительности MySQL
В сегодняшней статье мы поговорим о том, как выполняется оптимизация производительности mysql. Какие программы для этого лучше использовать и как это работает.
Скорость работы MySQL
Оптимизация без аналитики бессмысленна. Перед тем как переходить к оптимизации давайте посмотрим как работает база данных сейчас, есть ли запросы, которые выполняются очень медленно. Все настройки вашего сервиса mysql находятся в файле /etc/my.cnf. Чтобы включить отображение медленных запросов добавьте такие строки в my.cnf, в секцию [mysqld]:
Но это уже необязательно для проверки скорости и используется больше для отладки кода и правильности создания таблиц. Дальше перезапустите сервер баз данных и посмотрите лог:
systemctl restart mariadb
Мы можем видеть, что есть запросы, которые выполняются больше, чем 10 секунд. Это, например, запрос
SELECT option_name, option_value FROM wp_options WHERE autoload = ‘yes’;
Можно его выполнить отдельно, в консоли mysql:
Оптимизация MySQL
Конфигурация MySQL достаточно сложная, но, к счастью, вам не нужно в нее сильно углубляться. Есть специальный скрипт под названием MySQLTunner, который анализирует работу MySQL и дает советы какие параметры нужно изменить и какие значения для них установить. Скрипт поддерживает большинство версий MariaDB, MySQL и Percona XtraDB. Нам понадобится загрузить три файла с помощью wget:
Буквально за несколько минут скрипт выдаст полную статистику по работе MySQL. Количеству запросов, занимаемому объему памяти и эффективности работы буферов. Вы можете ознакомиться со всем этим, чтобы лучше понять в чем причина проблем. Проблемные места обозначены красными восклицательными знаками. Например, здесь мы видим, что размер буфера движка таблиц InnoDB (InnoDB buffer pool) намного меньше, чем должен быть для оптимальной работы:
Кроме того, в самом конце вывода утилита предоставит список рекомендаций как исправить ситуацию. Мы рассмотрим все сообщения утилиты из этого примера и почему нужно использовать именно их, а не другие.
Все параметры нужно добавлять в /etc/my.cnf. Еще раз замечу, что вы не копируете статью, а смотрите что вам выдала утилита. Начнем с query-cache.
query_cache_size=0
query_cache_type=0
query_cache_limit=1M
Оба параметра устанавливают размер памяти, которая используется для внутренних временных таблиц MySQL. Утилита рекомендует использовать объем больше 16 мегабайт, просто установите это ваше значение для обоих переменных, если у вас достаточно памяти, то можно выделить 32 или даже 64. Но важно чтобы оба значения совпадали, иначе будет использоваться минимальное.
Этот параметр отвечает за количество потоков, которые будут закэшированны. После того, как работа с подключением будет завершена, база данных не разорвет его, а закэширует, если количество кэшированных потоков не превышает ограничение. Утилита рекомендует больше четырех, например, 16.
Указывает, что не нужно пытаться определить доменное имя для подключений извне. Ускоряет работу, так как не тратится время на DNS запросы.
Этот параметр определяет размер буфера InnoDB в оперативной памяти, от этого размера очень сильно зависит скорость выполнения запросов. Значение зависит от размера ваших таблиц и количества данных в них. Если памяти недостаточно, запросы будут обрабатываться дольше. У меня используется стандартный объем 128, а нужно больше 652.
Размер файла лога innodb должен составлять 25% от размера буфера. В случае 800 мегабайт это будет 200М. Но тут есть одна проблема. Чтобы изменить размер лога нужно выполнить несколько действий. Поскольку мы изменили все нужные параметры перейдем к перезагрузке сервера. Для нашего лога нужно остановить сервис:
systemctl stop mariadb
Затем переместите файлы лога в /tmp:
mv /var/lib/mysql/ib_logfile[01] /tmp
И запустите сервис:
systemctl start mariadb
Когда размер лога меняется сервис видит поврежденный лог, выдает ошибку и не запускается. Поэтому сначала нужно удалить старый. После этого смотрите есть ли сообщения об ошибках:
systemctl status mariadb
Тестирование результата
Готово оптимизация базы данных mysql завершена, теперь тестируем тот же запрос через клиент mysql:
> USE база_данных;
> SELECT option_name, option_value FROM wpfc_options WHERE autoload = ‘yes’;
Первый раз он выполняется долго, может даже дольше чем обычно, но все последующие разы буквально мгновенно. Результат с более 3 секунд до 0,15. А если брать статистику из slow-log, то от более 12. Если в выводе утилиты для вас были предложены и другие оптимизации, то их тоже стоит применить.
Выводы
Как видите, оптимизация mysql это достаточно просто благодаря такому скрипту, но, в то же время, такая операция может очень сильно помочь, особенно высоконагруженным проектам. Еще лучше ускорить работу может только оптимизация запросов mysql. Не забывайте время от времени проверять параметры, чтобы быть уверенным что все в порядке. Если у вас остались вопросы, спрашивайте в комментариях!
На завершение лекция про производительность MySQL от Percona:
10 лучших приемов для оптимизации работы с MySQL
1. LIMIT 1 Когда нужно извлечь из таблицы уникальную строку
Иногда, формируя запрос, вы уже знаете, вам нужна только одна уникальная строка в таблице. Вы можете сформировать выборку по уникальной записи. Или вы можете просто запустить проверку на существование любого количества записей, которые удовлетворяют вашему условию.
В таких случаях, использование метода LIMIT 1 может существенно увеличить производительность:
2. Оптимизация работы с базой с помощью обработки кэша запросов
Большинство серверов MySQL поддерживают функцию кэширования запросов. Это один из наиболее эффективных методов повышения производительности, с которым движок базы данных справляется без проблем.
Когда один и тот же запрос выполняется несколько раз, то, результат будет получен из кэша. Без необходимости обрабатывать снова все таблицы. Это значительно ускоряет процесс.
3. Индексация полей поиска
Индексы предназначены не только для присвоения первичному или уникальному ключам. Если в таблице есть столбцы, по которым вы производите поиск, их практически в обязательном порядке следует индексировать.
Как вы понимаете, это правило также распространяется на часть строки поиска: такую как «l ast_name LIKE ‘% ‘». Когда поиск производится по началу строки, MySQL может использовать для этого столбца индексацию.
Вы также должны понимать, какие виды запросов не могут использовать обычные индексы. Например, при поиске слова (например, «WHERE post_content LIKE ‘%tomato%’»), применение обычного индекса вам ничего не даст. В таком случае лучше будет использовать поиск MySQL на полное соответствие или создать свой собственный индекс.
4. Индексирование и использование столбцов одинакового типа при объединении
Если ваше приложение содержит много запросов на объединение, необходимо убедиться, что столбцы в обеих таблицах, которые вы объединяете, проиндексированы. Это влияет на оптимизацию внутренних операций MySQL по объединению.
Кроме того, столбцы, которые объединяются, должны быть одинакового типа. Например, если вы объединяете столбец типа DECIMAL из одной таблицы и столбец типа INT из другой, MySQL не сможет использовать по крайней мере один из индексов.
Даже кодировка символов должна быть того же типа для соответствующих строк объединяемых столбцов.
5. По возможности не используйте запросы типа SELECT *
Чем больше данных в таблице обрабатывается при запросе, тем медленнее выполняется сам запрос. Время уходит на дисковые операции. Кроме того, когда сервер базы данных разделен с веб-сервером, возникают задержки при передаче данных между серверами.
6. Пожалуйста, не используйте метод сортировки ORDER BY RAND()
Это один из тех приемов, которые на первых порах кажутся неплохими, и многие программисты-новички попадаются на эту удочку. Вы даже не представляете, какую ловушку расставляете сами себе же, как только начинаете использовать в запросах этот фильтр.
Если вам действительно нужно отсортировать некоторые строки в результатах поиска, то есть гораздо более эффективные способы сделать это. Допустим, что вам нужно добавить дополнительный код к запросу, но из-за данной ловушки вы не сможете этого сделать, что приведет к уменьшению эффективности обработки данных, по мере того, как база будет разрастаться в размерах.
Проблема в том, что MySQL будет выполнять операцию RAND () (которая использует вычислительные ресурсы сервера) перед сортировкой для каждой строки в таблице. При этом выбираться будет всего одна строка.
Таким образом, вы выберете меньшее количество результатов поиска, после чего сможете применить метод LIMIT, описанный в пункте 1.
7. Используйте столбцы типа ENUM вместо VARCHAR
Если у вас есть некоторое поле, которое содержит несколько разных значений одного вида, то вместо столбцов типа VARCHAR лучше использовать ENUM. Например, это может быть столбец « Статус », который содержит только такие значения, как « активно », « неактивно », « ожидание », « срок действия истек » и т.д.
Используйте для хранения IP-адресов поля типа UNSIGNED INT
8. Вертикальное секционирование (разделение)
Вертикальное секционирование представляет собою процесс, когда структура таблицы разделяется по вертикали из соображений оптимизации работы с базой данных.
Пример 1: Допустим, у вас есть таблица пользователей, в которой в числе прочего содержатся их домашние адреса. Данная информация используется очень редко. Вы можете разделить вашу таблицу и хранить данные по адресам в другой таблице.
Таким образом, ваша основная таблица пользователей заметно уменьшится в размерах. А как вы знаете, меньшие таблицы, обрабатываются быстрее.
Пример 2: У вас в таблице есть поле « last_login » (последний логин). Оно обновляется каждый раз, когда пользователь входит в систему под своим именем пользователя. Но каждое изменение таблицы записывается в кэш запросов к этой таблице, который хранится на диске. Вы можете переместить это поле в другую таблицу, чтобы уменьшить количество обращений к вашей основной таблице пользователей.
Однако вы должны быть уверены в том, что обе таблицы, которые получились после секционирования, не будут в дальнейшем использоваться одинаково часто. В противном случае это существенно снизит производительность.
9. Меньшие столбцы – быстрее
Для движков баз данных дисковое пространство, пожалуй, самое узкое место. Поэтому хранить информацию более компактно, как правило, полезно с точки зрения производительности. Это уменьшает количество обращений к диску.
Однако все же убедитесь, что на перспективу вы оставили себе достаточно пространства для развития. Иначе в какой-то момент может произойти что-то типа обвала.
10. Правильно выбирайте движок
InnoDB – это более сложный механизм и может медленнее, чем MyISAM работать с большинством приложений. Но он поддерживает функции, которые позволяют более эффективно работать с базами больших размеров.