Ксения Томак "Маленькие данные начинают расти"

Ксения расскажет о том, что делать, когда СУБД перестает справляться с растущими нагрузками и объемами данных, предложит решение этой проблемы посредством тюнинга SQL Server, а также рассмотрит кардинальный способ: замену движка БД.

Transcript

1. МАЛЕНЬКИЕ ДАННЫЕ начинают расти Томак Ксения. Старший программист ЗАО «Связь инжиниринг М»
2. Предметная область Сбор данных о потреблении электричества и мониторинг состояния объектов. 2
3. 3 С чего все начиналось
4. System.DataSqlClient.SqlException: Timeout expired. 4
5. SQL. Пытаемся спасти ситуацию. 5 • Перегруженный сервер • Неоптимальный запрос • Плохая архитектура • Нет индекса • Фрагментация индекса
6. SQL. Перегруженный сервер System resource monitor SQL Server Activity monitor SQL Server instance standard reports System views (sys.*) 6
7. SQL. Неоптимальный запрос 7 + поломанная статистика А оно здесь надо? Построение индекса Вынос агрегатов
8. SQL. Неоптимальный запрос 8 SET STATISTICS IO { ON | OFF } SET STATISTICS TIME { ON | OFF }
9. SQL. Индекс и фрагментация индекса 9
10. SQL. Секционирование 10
11. SQL. Секционирование 11 Partition 18 2016-02 Partition 17 2016-01 Partition 1 2012-01 + - 2016-04-01 2016-01-01 ... Делим таблицу на секции по каждому кварталу
12. SQL. Секционирование. Переключение секций 12 Partition 18 2016-02 Partition 17 2016-04 Partition 1 2012-01 ... Active Partition 13 2015-01 Partition 17 2016-01 Partition 1 2012-01 Switched
13. SQL. Секционирование. Bulk inserts 13 Transaction Log INSERT [dbo].[Table] SELECT * FROM [dbo].[NonPartitionedTable] GO with(TABLOCK) USE [DB] GO ALTER DATABASE [DB] SET RECOVERY BULK_LOGGED GO ALTER DATABASE [DB] SET RECOVERY FULL GO
14. SQL. Очистка таблицы TRUNCATE TABLE db.schema.table *имеет ряд ограничений 14
15. SQL. Секционирование 15 Pros Секция – отдельная компонента обслуживания Включение/выключение секций Быстрое удаление всей секции, вставка в пустые секции Cons Администрирование Неравномерное распределение записей До SQL Server 2016 – в EE версии
16. Смена хранилища 16
17. Схема системы 17 ID счетчика Дата/время Показание 1 1 ... 150000 2018-01-01 00:00:00 10.0 2018-01-01 00:30:00 10.5 ... ... 2018-06-01 13:30:00 12455.1
18. О системе 7 млрд записей интенсивность вставок до 200 записей/сек 18 SQL server 2008+ MongoDB 3.0+ Около 30 различных продакшенов
19. Пример сервера БД CPU 12 CPUs (2.66 GHz) RAM 36Gb Disk HDD Fiber Channel 19
20. Смена хранилища Выбор хранилища Подготовка бекенда Перенос данных 20
21. Выбор хранилища Работа с БД (или кластером) Сообщество Драйвер .NET Хорошая документация 21
22. Выбор хранилища Параметр Примечание CAP A - (failover ~ max 5 minutes) ACID (AC - single document, ID - depends) Indexes Queries (range queries, where) In-Memory Concurrency Optimistic 22
24. Тесты скорости  Вставки в непустую таблицу  Стараться эмулировать естественную нагрузку  Перед тестами прочитать мануалы и сконфигурировать СУБД  24 VS
25. Конфигурация хранилища и драйвера • Помнить о структуре данных • Настройки логирования • Настройки драйвера БД 25 { "datetime": "2017-01-01T00:00:00", "equipmentid": 101, "value": 0 } { "d": "2017-01-01T00:00:00", "e": 101, "v": 0 }
26. Подготовка backend’а 26
27. Перенос данных Если инструмента переноса нет: • Важны не фасады, а скорость; • ADO вместо EF; • Разные уровни логирования; • Возможность начать перенос с какого-то ключа (shit happens). 27
28. Перенос данных. Большое количество данных Mongo Shards Partition 13 2015-01 Partition 12 2014-04 Partition 1 2012-01 ... Active Partition 13 2015-01 Partition 12 2014-04 Partition 1 2012-01 Switched Partition 1 2012-01 View 28
29. Смена хранилища с SQL на MongoDB 29 Pros Скорость Не нужна лицензия MS SQL Нужно меньше ресурсов сервера Гибкая схема БД Cons Другой подход Много конфигурации Целостность
30. Скорости выгрузки типовых отчетов 30 0 30 60 90 120 150 180 Составной тип Интервал 1 месяц top 1 по счетчику Точечные выгрузки мин SQL (old) SQL (tuned) MongoDB
31. Итоги Скорость выгрузки основного представления выросла в 60 раз. Скорость выгрузки отчетов выросла в 1,5-2 раза. Ресурсов сервера хватило еще на x6 объемов данных. Размер БД уменьшился в 1,5 раза 31
32. SQL. Пытаемся спасти ситуацию. Почитать • Ritesh Shah, Bihag Thaker - Microsoft SQL Server 2012 Performance Tuning Cookbook • Strate J., Krueger T. - Expert Performance Indexing for SQL Server 2012 (The Expert Voice in SQL Server) • Kalen Delaney's Weekly Webinar - Episode 4 (Data storage) and later • BRENT OZAR • О самой частой причине выбора неэффективного плана запроса. Часть 1. • Check SQL Server a specified database index fragmentation percentage (но можно дополнить до генерации dynamic SQL) 32
33. SQL. Секционирование. Почитать • Partitioned Table and Index Strategies Using SQL Server 2008 • Project REAL: Data Lifecycle – Partitioning • SQL Server Table Partitioning: Resources 33
34. Смена хранилища. Почитать • NoSQL Distilled • Polyglot Persistence • Monolithic Persistence antipattern • Документации ваших любимых СУБД 34
35. Вопросы 35
36. Томак Ксения tomak.ksenia@gmail.com 36