Каталог решений - Опыт оптимизации и контроля производительности в БД с 3000 пользователей

Текущее состояние автоматизируемой системы

 Для начала — несколько слов о системе и нагрузке на неё (*по состоянию на сентябрь 2014):

• База – самописная.
• Платформа – 8.2.

• На текущий момент у нас в базе работает более 3000 пользователей. На слайде — количество работающих пользователей с учетом COM- и веб-соединений:

• Суммарно за сутки записывается порядка миллиона объектов ссылочного типа. Из них:
  • 700 тысяч – это документы (перепроведение существующих и запись новых).
  • 300 тысяч – это справочники (также модификация существующих и запись новых).

• Количество формирований отчетов в сутки составляет порядка 250 тысяч. Это многочисленные оперативные и аналитические отчеты.
• Суммарная длительность запросов, выполняемых пользователями в базе за сутки, превышает 330 тысяч секунд (92 часа) – достаточно интенсивная нагрузка.

При этом компания продолжает развиваться, открываются новые подразделения, привлекаются новые сотрудники, новые клиенты.

Есть постоянная потребность по разработке и внедрению новых подсистем, по автоматизации бизнес процессов, по решению различных интеграционных вопросов.

Поэтому у нас и большой отдел разработки — более 40 человек программистов, а также аналитики, тестировщики, архитекторы, эксперты. В общей сложности более 100 человек.

Процесс оптимизации. Начало.

Вопрос необходимости оптимизации возник несколько лет назад, когда в процессе бурного роста нашей компании постоянно увеличивалось количество пользователей информационной базы и, соответственно, значительно возрос объем обрабатываемых ими данных. При этом архитектура базы не была готова к такой интенсивной работе, поскольку она рождалась достаточно давно (еще на 7.7) и не была на это рассчитана, а вопросами SQL-сервера занимался программист 1С.

Начальные условия:

• Платформа 8.1
• Число пользователей в базе: 1000
• Режим блокировок: атоматические
• APDEX — отсутствует
• Нагрузка ЦПУ сервера СУБД достигает 98%
• Постоянные жалобы пользователей на "подвисания"
• Количество разработчиков: 10
• Обновления конфигурации БД: еженедельно

Основная задача, которую надо было решить – снизить загрузку CPU сервера SQL и обеспечить принципиальную работоспособность. Решали её разными способами, начиная с самого легкого – upgrade железа. Пока было куда "апгрэйдить" это помогало. Когда и топовое железо перестало помогать, тогда и началась оптимизация.

Что сделали на первом этапе: 

1. Разработали план регламентных работ по перестроению и дефрагментации индексов.
2. Установили ЦУП и принялись за оптимизацию запросов.
3. Перешли на управляемые блокировки.
4. Внедрили APDEX (на мой взгляд – это обязательная штука, если, конечно, вы хотите знать что происходит с вашей базой).

На первое место в статистике ЦУПа вышли запросы обновления форм списков пары основных документов и справочника Контрагентов. Переделали их на формы поиска.
Основная суть – статичный список формируемый запросом (ПостроительОтчета), на форме блок предустановленных фильтров. Пользователь указывает отборы, жмет «Найти» и получает нужные данные. Это позволяет контролировать обязательные для заполнения поля поиска, избавиться от запросов при обновлении формы и скроллинге, а так же ограничить объем выводимой информации. Например, если мы хотим выводить не более 100 элементов, пишем в запросе "выбрать первые 101" и если запрос возвращает ровно 101 элемент, то пользователя просят уточнить отборы.

Конечно запросы из форм списков были не единственными, мы переписали досточно много запросов, а где то и архитектуру. Эта работа дала результат. Раньше, например, нагрузка на систему в понедельник и пятницу могла быть настолько высока, что приходилось отпускать домой некоторые отделы (менеджеров, колл-центр). А в результате проведенной оптимизации получилось снизить нагрузку в пятницу – проблем больше не было. Да и в понедельник мы тоже, со скрипом, но работали.

Второй этап оптимизации. "Разделяй и властвуй"

Тем не менее, мы прекрасно понимали, что расслабляться нельзя, надо действовать дальше, потому что количество пользователей к тому моменту опять выросло: их стало почти 2000. Штат разработчиков вырос до 20 человек, функционал наращивался очень быстро. И здесь нам помог случай и неудачный переход на 8.2 (8.2.13). Про переход на 8.2 можно многое рассказать, но так как тот опыт удачным не был, отмечу основную ошибку — не был проведен полноценный нагрузочный тест на 2000 рабочих мест.

Итак, не впадая в историзмы, у нас развивался свой сайт и от него поступал достаточно большой трафик запросов, которые "стучались" в боевую базу. Но эти запросы не требовали он-лайн данных и могли бы, например, подключаться к копии с актуальностью минус сутки. И у нас была подсистема репликации на планах обмена, которая разрабатывалась специально для перехода на 8.2 (на случай отката с 8.2 обратно на 8.1). Эта подсистема, используя план обмена, позволяет поддерживать в актуальном состоянии копию рабочей базы. Вот в эту копию мы и перенаправили часть соединений вэб сервисов. И заметили снижение нагрузки.  Да, было понятно, что нагрузка должна несколько снизиться, но мы не рассчитывали, что ее падение будет настолько существенным. Сами запросы от сайта были достаточно маленькие, просто их поток был очень большим.

Возник вопрос: нельзя ли то же самое сделать с отчетами? Первое, что приходит в голову – это запустить для пользователя две базы: первую использовать только для ввода данных, а вывод отчетов там запретить, а во второй разрешить формировать отчеты, но запретить ввод данных. С точки зрения юзабилити это негативный сценарий, пользователь должен работать в одной базе, но при этом запрос надо выполнять в копии. Поэтому пошли другим путем:

• Провели пару экспериментов, убедились, что и результат запроса и табличный документы отлично сериализуются. В табличном документе, переданном по COM посредством сериализации/десериализации, работают даже расшифровки.
• Разработали шаблон отчета, который позволяет передавать настройки отчета и получать его результат по СОМ соединению.
• Улучшили подсистему репликации и добились актуальности данных в 2 – 3 минуты, что значительно расширило список перенаправляемых отчетов. Доработки в основном были нужны потому, что стандартный метод "ВыбратьИзменения" планов обмена создает большое колличество блокировок.
• Переписали кучу отчетов, приведя их к единому стандарту.

Так образом, хоть и с оговорками и условностями, мы разделили базу на OLAP и OLTP:

• OLTP – это рабочая база, где вносят первичку.
• OLAP –это база, где формируются отчеты и куда перенаправляются запросы веб-сервисов. Или, например, в эту базу можно зайти разработчику, чтобы протестировать какой-то отчет, как он будет себя вести в боевой системе.

Через пол года примерно, мы без "отчетной" базы уже не могли нормально работать. На слайде ниже показан эффект от разделения базы на рабочую и отчетную. Голубыми вертикальными линиями выделен момент, когда нам пришлось перевести формирование отчетов обратно в боевую базу.

Нагрузка на CPU в этот период достигала 98%. Так же обращаю внимание на не линейный рост нагрузки, грубо говоря, 20%+30%=100%.

Очередное развитие отчетная база получила, когда вышел SQL Server 2012 и появилась технология AlwaysOn, которая позволяет средствами самой СУБД создавать и поддерживать несколько копий БД в актуальном состоянии (теперь у нас задержки всего лишь несколько миллисекунд). Сама по себе технология позволяет отказаться от репликации средствами 1С, но переписывать отчеты все равно придется.