MySQL DevOps: MySQL High Load Operation (Russian)

This presentation was given by ProxySQL's Senior Consultant Vlad Fedorkov and ProxySQL's Communications Manager Anastasia Raspopina at DevOps Pro Moscow 2019 on November 21, 2019. NOTE: the slides are in Russian.

Talk abstract:

MySQL is often the bottleneck of the infrastructure due to the implementation of the DBMS. In our presentation, we will talk about the most important steps that must be taken in order for the MySQL-based storage system to cope with the load.
In addition, as part of the presentation, we will consider specific examples from real life, and determine how and when the DevOps engineer should interact with the developers for optimal system performance.

Transcript

1. MySQL DevOps: эксплуатация MySQL под высокой нагрузкой Владимир Федорков (ProxySQL), Анастасия Распопина (ProxySQL) DevOps Pro, 20 ноября 2019, Москва
2. • Скучать, сидя в аудитории из вежливости • Мешать другим • Пить чай и кофе • Снисходительно посматривать на докладчиков • Заходить, выходить • Задавать вопросы в любое время Правила поведения DevOpsPro 2019, Москва Proxysql.com
3. О докладчиках DevOpsPro 2019, Москва Proxysql.com • Владимир Федорков • 19+ лет опыта с MySQL. • Вытягиваю проекты из сложных жизненных ситуаций – в высоконагруженных проектах простых не бывает • Специализация - MySQL – Также – ProxySQL и полнотекстовый поиск • Анастасия Распопина • Коммуникации в СУБД- индустрии. • Придумываю интересные доклады (DevRel) • ориентирую экспертов в их морях смыслов • Специализация – мероприятия • В РФ и за рубежом
4. MySQL в контексте DevOps • Один из самых неудобных элементов • Требователен к железу • Плохо переносит виртуализацию • Отвратительно масштабируется –По сравнению с любыми фронтами –Откуда-то куда-то постоянно реплицируется DevOpsPro 2019, Москва Proxysql.com
5. Почему всё так сложно?! • MySQL — это stateful-система • Содержит много «тяжелых» операций • Жестоко заоптимизирован • Всё это требуется для бережного хранения данных DevOpsPro 2019, Москва Proxysql.com
6. Бережное хранение: ACID • A – Atomicity Всё или ничего. Даже если отключат свет. • C – Consistency Данные логически непротиворечивы. Всегда. • I – Isolation Изменения не видны, пока не закончены. • D – Durability Записанное можно гарантированно прочитать. DevOpsPro 2019, Москва Proxysql.com
7. Дополнительные затраты • Сохранение корректного состояния в любой момент времени. Его обеспечивают – блокировки; – мультиверсионность. • Восстановление состояния после отказов – сети; – дисков; – питания. DevOpsPro 2019, Москва Proxysql.com
8. Кому, зачем и где нужен такой тюнинг? DevOpsPro 2019, Москва Proxysql.com
9. Там, где данные критичны • Финансовые приложения • Торговля (магазины и склады) • Учебные заведения • Хранилища документов • … назовите сами) DevOpsPro 2019, Москва Proxysql.com
10. «Что?» Определяем задачи • Эксплуатация: – Железо – Настройки OS – Настройки MySQL • Разработка: – Выбор подсистемы хранения (storage engine) – InnoDB, MyRocks, MyISAM, Etc • Всем вместе: – Запросы DevOpsPro 2019, Москва Proxysql.com
11. «Как?» Работаем с данными • Какие настройки использовать? • Какие индексы делать? • Как проектировать базу? • Как потом её менять? DevOpsPro 2019, Москва Proxysql.com
12. «Где» Вопросы размещения • Глобальный вопрос всего проекта – Масштабирование – Расходы – Перспектива роста • Регион • Платформа – «Чистое» железо – Виртуализация – Облако DevOpsPro 2019, Москва Proxysql.com
13. Нужна ли база данных вообще? • Зависит от того: – Как данные читаются – Как данные записываются – Какие выборки используются – Как данные масштабируются – Насколько данные ценны – Какая нужна скорость доступа – Насколько важна отказоустойчивость – Насколько значима безопасность – Есть ли географическая распределённость DevOpsPro 2019, Москва Proxysql.com
14. Конфигурация MySQL • Конфигурация MySQL – 5% настроек обеспечивают 95% производительности • Что тюнить – Буферы / Память – Тонкие настройки подсистемы хранения (storage engine) – Кэши – Сеть – Репликацию DevOpsPro 2019, Москва Proxysql.com
15. Репликация: важно • Бинлоги – log_bin – log_slave_updates • Настройки реплики – read_only – skip_slave_start DevOpsPro 2019, Москва Proxysql.com
16. Ура, сконфигурили! • Правильные настройки – не мешают работе MySQL – oбеспечивают отказоустойчивость • только на уровне операционной системы! – не обеспечивают производительности. • Допустим, всё работает… DevOpsPro 2019, Москва Proxysql.com
17. Запросы • Обеспечивают 99,99% тормозов – и головную боль админов • Лучший MySQL-запрос – тот, который до MySQL не дошёл! – MySQL не сможет выполнить некоторые запросы быстро! – Просто потому что эта СУБД так спроектирована. DevConf 2017, Москва ASTELLAR.COM
18. А что с ним не так, турецкий? • Ограничение дизайна MySQL – ACID медленный! – Запрос обрабатывается одним ядром. – B-Tree работает только для особых случаев. – Есть полнотекстовые индексы, но они медленные. – Про репликацию можно говорить часами. • Что и делаем на собеседованиях! – Если вышеперечисленное для вас большая проблема, вы используете неправильный инструмент. Вам нужен не MySQL! • Если вы не Facebook! DevOpsPro 2019, Москва Proxysql.com
19. Как написать тормозной запрос? SELECT * FROM table … • WHERE DAY(FROM_UNIXTIME(`ts`)) = 205 • WHERE deleted != 1 • WHERE id NOT IN (1,2,3,…,10) • WHERE url LIKE ‘%чтототам%’ – Не путать с LIKE ‘чтототам%’ ! • ORDER BY RAND() DevOpsPro 2019, Москва Proxysql.com
20. Чем плох full table scan? • Индексы не помогают! – Значение функции считается для всех строк – B-Tree не эффективен • Читать всю таблицу долго! • Пока читаем, вымываем память базы ненужными данными. • Как бороться? – Пересматривать логику запросов. – Использовать предварительно аггрегированные данные вместо расчёта функций «на лету» – Использовать deleted = 0 вместо deleted != 1 DevOpsPro 2019, Москва Proxysql.com
21. Высокоселективные запросы • SELECT * / COUNT(*) FROM users WHERE active=1 – Здравствуй, вся таблица! • Как бороться? – Читаем только то, что показываем • Используем LIMIT – Делаем агрегацию для счётчиков – Кэшируем всё, что можем • Не в MySQL query cache! – Используем внешние инструменты (Mongo, Redis, Sphinx, etc.) DevOpsPro 2019, Москва Proxysql.com
22. Временные таблицы • Плохо – Если попадает на диск – очень плохо! • Если есть поля TEXT или BLOB, на диск таблица попадёт. • Когда создаются – GROUP BY – Подзапросы – DISTINCT + ORDER BY DevOpsPro 2019, Москва Proxysql.com
23. Что делать? • Тюнить буферы временных таблиц – tmp_table_size – max_heap_table_size • Запускать «тяжёлые» запросы на отдельной реплике – Например, для генерации отчетов DevOpsPro 2019, Москва Proxysql.com
24. Если нагрузка большая • Query cache • Thread cache • Table cache • Slow query log • wait_timeout • connection pooling • Репликация и шардинг DevOpsPro 2019, Москва Proxysql.com
25. Важно отслеживать состояние • Смотреть на состояние CPU, IO & RAM • Регулярно собирать все запросы – Не только медленные! – В идеале все 24 часа • Выполнять SQL review для любой новой функциональности – Один запрос может «положить» базу DevOpsPro 2019, Москва Proxysql.com
26. Как понять, что происходит? • PMM для ежедневной работы – Grafana + Анемометр • https://github.com/vlad-github/mysql-health-check – Для снятия моментального профиля нагрузки и текущей конфигурации • Регулярный сбор всех запросов – Блоками по несколько минут, чтобы поймать все кроны • Ходить на мастер-классы, задавать вопросы :) DevOpsPro 2019, Москва Proxysql.com
27. Между Dev и Ops • Коммуникация – это ключ к успеху! • Разработка – должна понимать как работают запросы, а также – тестировать функционал на фактическом объеме данных и – предупреждать о новой функциональности. • Эксплуатация – должна понимать, что меняют, – какой будет нагрузка завтра, – что можно отключить в случае перегруза. • В идеале – вплоть до парного программирования. DevOpsPro 2019, Москва Proxysql.com
28. От сервера кластеру • Один сервер мало, два – плохо. – Репликация медленная • Работает в один поток (исправили в версии 5.6). – Репликация хрупкая (не исправили до сих пор) • Но можно использовать GTID – Доверия репликации – нет • Консистентность данных требует проверок – Даже если видимых сбоев не было – pt-table-checksum + pt-table-sync • Есть варианты – Galera, Group Replication, PXC, etc. DevOpsPro 2019, Москва Proxysql.com
29. Прелести кластера • Если у тебя упал единственный сервер, это трагедия… – А если один из десяти? • Можно использовать реплики для бэкапа. • Можно балансировать нагрузку. DevOpsPro 2019, Москва Proxysql.com
30. Как балансировать нагрузку? • Использовать мастер только для записи – и чтения абсолютно критичных к времени данных. • Распределять запросы по репликам. • Сделать отдельную реплику для «тяжёлых» запросов. • Использовать балансировщики трафика: – ProxySQL, HA Proxy, MySQL Proxy и т.п. DevOpsPro 2019, Москва Proxysql.com
31. Красивая диаграмма О том, как всё могло бы быть
32. Что ещё умеет кластер • Встать колом полностью от вовремя запущенного ALTER TABLE • Эффектно среплицировать команду DROP DATABASE • С разной скоростью выполнять одинаковые запросы на разных нодах DevOpsPro 2019, Москва Proxysql.com
33. Ежедневная рутина • Отказы железа • Баги софта • Надежные бэкапы и восстановление • Мониторинг • Апгрейды и настройка репликации • Настройка сети и безопасности DevOpsPro 2019, Москва Proxysql.com
34. Облака: без админов обойдемся! • На примере Amazon RDS – Relational database service • MySQL, Oracle & MSSQL – MySQL (InnoDB) и Aurora • Что позволяет? – Создавать/удалять ноды и реплики – Выделять реплики из кластера – Автоматизировать развёртывание БД и фронтов • Вплодь до полного скриптования DevOpsPro 2019, Москва Proxysql.com
35. Что это значит для нас? • Полностью автоматизированное развёртывание приложения с использованием RDS и EC2 • Гибкий контроль производительности и стоимости с помощью добавления и удаления машин – В зависимости от времени дня – В зависимости от текущей нагрузки • Изменение параметров кластера «на лету» DevOpsPro 2019, Москва Proxysql.com
36. Что мы можем контролировать? • Выбирать регион для инстанса • Конфигурировать MySQL • Выбирать тип инстанса • Открывать/закрывать доступ по сети • Выбирать параметры дисковой подсистемы DevOpsPro 2019, Москва Proxysql.com
37. Availability zone • В каждом регионе несколько AZ • Можно сделать AZ мастер – Поможет в случае сбоя основного мастера • Репликация может быть сломана DevOpsPro 2019, Москва Proxysql.com
38. Storage • SSD & Magnetic диски – Полностью разные архитектурно и физически • Вы в облаке DevOpsPro 2019, Москва Proxysql.com
39. Настройка MySQL: Parameter groups • Содержит все возможные настройки MySQL • Настройки по умолчанию не оптимальны – Нужно создать свою группу и поменять как надо – Число PG не бесконечно • Некоторые настройки поменять нельзя • Статические и динамические настройки почти как в «родном» MySQL DevOpsPro 2019, Москва Proxysql.com
40. Добро пожаловать в облако! • Железо можеть быть разное – Даже на каждом запуске бенчмарков • IO зависит от сети • Стоят ограничители CPU & IO • Ты не знаешь своих соседей DevOpsPro 2019, Москва Proxysql.com
41. Как жить? • Всегда ориентируемся на худший случай. • Максимально уменьшаем нагрузку на IO. • Агрессивный шардинг – наш друг и верный друг и товарищ! • Внимательно следим за – нагрузкой на CPU/IO и – использованием памяти. – Стараемся вовремя обнаруживать битые реплики. DevOpsPro 2019, Москва Proxysql.com
42. Когда использовать облако? • Когда не очень много данных – или когда вы можете их зашардить… • Когда сложно предугадывать нагрузку. • Когда быстро нужно много ресурсов – или когда ресурсы нужны ненадолго. • RDS, когда не хватает рук для администрирования. DevOpsPro 2019, Москва Proxysql.com
43. Как со всем этим живут гиганты? • Строят свои датацентры и каналы. • Содержат команды инженеров и программистов. • Переделывают MySQL под свои нужды. • Меняются патчами и публикуют их. – Можно их найти и использовать! • Но если вы не Твиттер, не Фейсбук и не Google, переживать вам пока рано! DevOpsPro 2019, Москва Proxysql.com
44. Что дальше? • Анонсы митапов: канал @ru_mysql в Телеграме • Анонсы мастер-классов по MySQL: FB.com/vfedorkov • Посмотреть другие доклады на astellar.com • Прочитать книги: –«MySQL по максимуму» (Бэрон Шварц, Пётр Зайцев, Вадим Ткаченко); –«Отладка производительности MySQL» (Света Смирнова). DevOpsPro 2019, Москва Proxysql.com
45. ВОПРОСЫ! DevOpsPro 2019, Москва Proxysql.com
46. СПАСИБО! DevOpsPro 2019, Москва Proxysql.com