Нагрузка на mysql что
Ограничения по нагрузке
Как считается нагрузка
Лимиты нагрузки
По умолчанию суммарная нагрузка в день не должна превышать следующие показатели:
| Тариф | Нагрузка на CPU | Нагрузка на MySQL |
|---|---|---|
| Year+ | 50 | 1000 |
| Optimo+ | 300 | 5000 |
| Century+ | 400 | 7000 |
| Millennium+ | 600 | 10000 |
| Eterno | 1000 | 15000 |
| Premium | 3000 | 25000 |
| 1Сайт | 1000 | 15000 |
С дополнительной услугой «Увеличение лимита нагрузки» лимиты могут быть расширены до следующих показателей:
Лимиты по умолчанию:
Лимиты с учетом максимально возможного увеличения:
110 cp на CPU, 2200 единиц на MySQL для обычного хостинга и тарифов «Старт» CMS-хостинга;
Отображение нагрузки в ПУ
На графиках в панели управления (раздел «Нагрузка на сервер») можно увидеть динамику нагрузки за последние 2 часа / 24 часа / 30 дней. Превышение лимита отрисовывается на графике желтым цветом.
Обратите внимание, что суточные превышения будут отображены только на графике «30 дней»; на графиках за 2 и 24 часа превышений видно не будет (если, конечно, суточный лимит нагрузки не был превышен за 15 минут или 1 час соответственно).
Как ускорить работу 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-лога в меньшей степени).
Вывод
Разумеется, все эти советы не являются исчерпывающими. Ключом к быстрой работе БД является понимание ваших данных, грамотно спроектированная схема и удачно составленные запросы. Тем не менее ряд эффективных оптимизаций можно произвести на уровне сервера.
Оптимизация производительности 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:
Нагрузка на mysql что
Причины нагрузки на сервер баз данных Mysql
Если Вы используете базу данных для своего сайта, то рано или поздно обязательно столкнётесь с проблемой нагрузки на сервер со стороны Вашей базы данных.
Первое время возможно Вы не будете испытывать подобных проблем, однако как только размер Вашей базы данных возрастёт, а количество её таблиц увеличится в несколько раз, Вам нужно будет регулярно анализировать работу базы данных.
Многие говорят, что если сайт использует надёжную CMS систему, то в таком случае работать с базами данных одно удовольствие и работа баз продумана до мельчайших подробностей. Это так, однако это не может гарантировать отсутствие нагрузки со стороны БД на сервере.
Есть 2 способа решения данной проблемы: самостоятельное и с помощью разработчика сайта.
Какой бы надёжной ни была структура базы данных, со временем она начнет создавать нагрузку на Ваш сервер. Причиной может быть накопление большего количества ненужного материала в базах данных, также невыполнение регулярной оптимизации базы, под которой подразумевается возобновление таблиц, дефрагментация, обновление статистики и сортировка индексов.
Если у Вас есть желание и навыки работы с базами данных, можете попробовать сначала самостоятельно оптимизировать запросы и таблицы для Вашей базы.
Быстрее всего информацию по процессам для базы данных на сервере можно получить с помощью панели управления WHM
и функции MySQL Process List.
Также такую информацию можно получить, подключившись к серверу по SSH, используя команду mysqladmin proc stat
( опять же если у вас root доступ к серверу) :
mysqladmin proc stat
| Id | User | Host | db | Command | Time | State | Info |
| 44327 | eximstats | localhost | eximstats | Sleep | 0 | | |
| 44639 | DELAYED | localhost | eximstats | Delayed insert | 0 | Waiting for INSERT | |
| 44643 | burzhuy_burzhuy | localhost | burzhuy_burzhuy | Query | 0 | Sending data | SELECT * FROM ext_releases where label=’Elux Records’ order by id desc LIMIT 0,30 |
| 44644 | vseprovs_pasha | localhost | vseprovs_wordpress | Sleep | 0 | | |
| 44646 | ilta_user | localhost | ilta_ilta | Query | 0 | Sorting result | SELECT * FROM pages WHERE parent=’1178′ AND enable_ru=1 AND type!=’13’ AND type!=’26’ AND type!=’39’ |
| 44647 | ilta_user | localhost | ilta_ilta | Query | 0 | Sorting result | SELECT * FROM pages WHERE parent=’1187′ AND enable_ru=1 AND type!=’13’ AND type!=’26’ AND type!=’39’ |
| 44648 | root | localhost | | Query | 0 | | show processlist |
Более полную статистику по запросам к базе данных сайта также можно подучить с помощью PHPMYADMIN:
Проверку и оптимизацию таблиц базы данных можно выполнить через панель управления cPanel c помощью опций Check DB, Repair DB.
Сначала выполняем проверку базы данных и если всё нормально, переходим к самой оптимизации.
После запуска и завершения процесса оптимизации и восстановления повреждённых таблиц базы данных, мы можем увидеть, что процесс завершился успешно, однако в нашем случае одна из таблиц базы данных всё таки не оптимизировалась. Это мы увидели благодаря сообщению: note : The storage engine for the table doesn’t support repair
Появление ОК напротив таблицы базы данных показывает, что оптимизация таблицы прошла успешно ( например, lovingst_imedicaldirectory.idx_default_field OK), появление Table is already up to date показывает, что таблица БД не нуждается в оптимизации так как уже оптимизирована.
Подобные оптимизации желательно делать несколько раз в месяц.
Если же и после оптимизации нагрузка не будет снижена, то, вероятно, вы исчерпали возможности Вашего хостинга и Вам нужно
переходить на более мощный тарифный план.
5 стратегий работы с высокими нагрузками в MySQL
MySQL — проверенная и очень мощная технология. В том числе и для построения систем с большой нагрузкой. Даже Facebook использует MySQL для управления огромными объемами данных. Рассмотрим основные стратегии для построения нагруженных систем на основе MySQL.
Оптимизация и индексы
Прежде всего убедитесь, что вы используете все стандартные возможности базы данных. Правильная работа с индексами даст огромные приросты в производительности. Сотни и даже тысячи раз. Освобождая при этом ресурсы для других задач. Сегодня стоимость жестких дисков постоянно снижается, а требования к скорости постоянно растут.
Индексы – это эффективный механизм перенести нагрузку с процессора на жесткий диск в правильных пропорциях.
Не спешите оптимизировать все запросы заранее. Используйте лог медленных запросов, чтобы понять, где существуют реальные проблемы.
Сразу после установки MySQL не забудьте оптимизировать основные параметры. Стандартная настройка очень базовая и ориентирована на скромное железо и жесткие требования к сохранности.
Подстройка стандартных параметров даст существенный прирост в ускорении не только операций чтения, а и записи.
Кэширование
Очень популярным методом оптимизации производительности является кэширование.
Внутренний кэш MySQL
Лучше не включать внутренний кэш Mysql в средах с большим количеством записей/обновлений.
Внешние решения
Более гибкое решение — использование внешних инструментов кэширования, вроде Memcache либо Redis. Есть целый ряд техник кэширования данных в приложениях.
Однако будьте осторожны. Кэширование — это часто не решение проблемы, а ее откладывание. Медленный запрос становится еще медленнее, а его влияние (при сбросе кэша) — менее прогнозируемым.
Кэширование лучше использовать только как промежуточные решения. В конце концов следует избавляться от медленных запросов.
Репликация
Несмотря на то, что репликация может помочь справиться с нагрузкой, ее лучше для этого не применять. Нужно помнить, что наряду с масштабированием у вас всегда будет стоять вопрос доступности. Если реплика, которая помогает обслуживать запросы, выйдет из строя, что случится с системой?
С другой стороны, реплика как раз позволяет обеспечить высокую доступность. Один из подходов выглядит так:
Таким образом, в новой схеме мастер и слейв поменялись местами, а приложение (то есть его пользователи) не заметило никаких проблем.
Репликацию стоит использовать только как механизм резервирования. Не для масштабирования под нагрузки.
Шардинг
Шардинг — это принцип масштабирования базы данных, когда данные разделяются по разным серверам. В нашем распоряжении есть два подхода:
Вертикальный шардинг
Горизонтальный шардинг
Этот тип шардинга следует использовать следующим этапом. На этом этапе очень большие таблицы, которые перестают помещаться на одном сервере, разделяют на части и помещают разные их части на разных серверах. Это усложняет логику приложения, однако мир еще не придумал лучших механизмов масштабирования.
Шардинг — единственный подход для масштабирования действительно больших данных.
Другие задачи
Следует отметить, что есть задачи, с которым MySQL справляется крайне плохо. Один из примеров — выборки уникальных значений в разных диапазонах. Либо полнотекстовый поиск.
Обратите внимание на Handlersocket, который может стать заменой любого NoSQL-решения, в случае если оно используется для простых Key-Value операций.
MySQL — мощное, но не универсальное решение. Redis, Elastic и другие технологии помогут решить дополнительные задачи.
Самое важное
MySQL вместе с современными подходами к оптимизации и масштабированию — это мощная платформа для построения огромных систем. Не забывайте использовать другие технологии для смежных задач, c которыми MySQL справляется не так эффективно.
Что такое индексы в Mysql и как их использовать для оптимизации запросов
Как исправить ошибку доступа к базе 1045 Access denied for user
Основные понятия о шардинге и репликации
Настройка Master-Master репликации на MySQL за 6 шагов
Примеры ad-hoc запросов и технологии для их исполнения
Анализ медленных запросов (профилирование) в MySQL с помощью Percona Toolkit
Типы и способы применения репликации на примере MySQL
Как создать и использовать составной индекс в Mysql
Настройка Master-Slave репликации на MySQL за 6 простых шагов
Синтаксис и оптимизация Mysql LIMIT
Правильная настройка Mysql под нагрузки и не только. Обновлено.
И как правильно работать с длительными соединениями в MySQL
Запрос для определения версии Mysql: SELECT version()
Check-unused-keys для определения неиспользуемых индексов в базе данных
3 примера установки индексов в JOIN запросах
Анализ медленных запросов с помощью EXPLAIN
Что значит и как это починить
Описание, рекомендации и значение параметра query_cache_size
Быстрый подсчет уникальных значений за разные периоды времени
Использование партиций для ускорения сложных удалений
Правила выбора типов данных для максимальной производительности в Mysql
Включение и использование логов ошибок, запросов и медленных запросов, бинарного лога для проверки работы MySQL