Настройка параллелизма в sql для 1с
На скорость работы 1С влияют разные факторы: от «кривых» запросов до оборудования. Это означает, что оптимизацию лучше проводить комплексно. А еще – чтобы разобраться с нуля во всех возможных причинах замедления, потребуется некоторое время.
Но есть несколько способов, которые заставят 1С работать быстрее минимальными усилиями: нужно просто поменять некоторые настройки, отключить кое-какие процессы… : )
В этой статье разберем 7 таких приемов. Минут 15 на чтение – и можно смело идти применять их на практике!
Даже если Вы пока не замечаете проблем с 1С, прочитайте статью. Убедитесь, что в настройках Вашей системы нет ошибок и все возможности платформы используются по максимуму – чтобы и в будущем проблем с производительностью не было : )
1. Настроить параллелизм в MS SQL Server
Если запрос читает большой объем данных, чаще всего это говорит о том, что он не очень оптимальный.
По правилам следовало бы такой запрос найти и оптимизировать, чтобы все жили долго и счастливо. К сожалению, наш мир не идеален, и далеко не всегда есть возможность переписать медленный запрос. Иногда с ним приходится мириться.
Для «тяжелых» запросов в MS SQL предусмотрен механизм распараллеливания. Он автоматически делит запрос, который читает большой объем данных, на несколько потоков и выполняет их параллельно.
Проблемы из-за распараллеливания
По идее распараллеливание должно ускорять выполнение запроса. Так и есть. Однако оно повышает нагрузку на процессор, поэтому, если использовать механизм необдуманно, это может приводить к негативным последствиям.
Например, бывает, что «тяжелый» запрос, разделившись на потоки, начинает загружать несколько ядер процессора, что сказывается на общей производительности системы.
Распараллеливание может стать причиной ожиданий на СУБД с типом CXPACKET: при параллельном выполнении запроса одни потоки будут ждать исполнения других, что также снизит скорость работы.
К тому же иногда из-за ошибки распараллеливания по вине СУБД происходит дедлок. Дедлок подразумевает, что разные потоки одного запроса блокируют друг друга.
Что предлагают в документации
В официальной документации говорится о том, что лучше вообще отключить механизм распараллеливания, установив для базы данных настройку Max degree of parallelism = 1. В этом случае один запрос будет выполняться в один поток, что позволит избежать проблем, описанных выше.
Рисунок 1. Настройка параллелизма в MS SQL Server для выполнения запроса на одном ядре (в один поток)
Ситуации, при которых не стоит отключать распараллеливание
Официальная рекомендация действительно справедлива, но лишь отчасти. Ее можно применить, если, например, постоянная нагрузка на процессор выше 30%. В этом случае включение параллелизма может еще больше нагрузить процессор и совсем «положить» его.
Но такие ситуации — редкость. Зачастую на современных серверах СУБД процессоры не загружены более чем на 20–30%. У них остается еще достаточный запас по мощности, поэтому внезапная нагрузка на сервер из-за «тяжелого» запроса несильно повлияет на остальных пользователей.
Приведем несколько ситуаций, при которых не стоит отключать распараллеливание:
- Описанный в документации случай, когда один запрос при распараллеливании замедляет всех — не самый распространенный паттерн. Чаще неоптимальный запрос выполняется медленно в один поток, хотя мог бы выполняться гораздо быстрее в несколько потоков, при этом несильно загружая процессор.
- Если отключить распараллеливание на уровне сервера, тогда и регламентные операции по умолчанию тоже будут выполняться в один поток. Чтобы этого избежать, нужно указывать настройку распараллеливания (MAXDOP = 0) для регламентного задания дефрагментации/реиндексации, чего многие, к сожалению, не делают.
- Новый механизм реструктуризации работает гораздо быстрее, если включено распараллеливание. Об этом сказано и в документации. Там же предлагается каждый раз перед реструктуризацией включать параллельность, а после обновления — отключать. Но все мы люди, и часто разработчики просто забывают установить параметр в нужное значение. В итоге реструктуризация больших таблиц даже с новым механизмом идет медленно.
- Ожидания CXPACKET зачастую интерпретируются неправильно. Их наличие далеко не всегда говорит о проблемах с распараллеливанием. Если хочется больше узнать об этом, подробности Вы найдете в статье Troubleshooting the CXPACKET wait type in SQL Server.
Так что же делать?
Мы разобрались, почему просто отключить механизм распараллеливания и забыть о нем — не лучшая идея. Что же тогда делать?
В качестве решения можно рассмотреть следующий подход: оставить настройку Max degree of parallelism = 0, но установить настройку Cost threshold for parallelism = 30.
Рисунок 2. Настройка параллелизма в MS SQL Server для выполнения запроса на нескольких ядрах (в несколько потоков)
Такая настройка означает, что MS SQL будет самостоятельно решать, сколько потоков использовать для выполнения одного запроса, но распараллеливание будет включаться, только если стоимость плана запроса будет выше 30.
Стоимость плана измеряется в неких условных единицах и отражает, насколько «тяжелым» является запрос для исполнения. Чем выше стоимость, тем сильнее запрос нагружает систему. При настройках по умолчанию параллельность включается, если стоимость плана выше 5. Значение 30 — не какая-то сакральная величина: просто, с точки зрения автора, оно подходит для большинства систем.
Благодаря такой настройке параллельность будет использоваться только для действительно сложных запросов. Запросы полегче продолжат выполняться в один поток — им распараллеливание все равно не дает заметного ускорения.
Следует отметить, что это решение не освобождает от необходимости оптимизировать «тяжелые» запросы, но оно хотя бы не мешает другим операциям выполняться быстро.
2. Проверить, сколько ядер используют MS SQL Server и 1С
Если у Вас мощный сервер — это еще не значит, что Вы используете его на полную мощность.
Причины у этого могут быть разные. Например, такое происходит, если при настройке окружения не учли ограничения MS SQL или приобрели не ту версию 1С.
Давайте разберем подробнее оба случая.
Проверить, сколько ядер используют MS SQL Server
Разные версии MS SQL имеют разные ограничения. Их важно учитывать при настройке окружения. Например, версия MS SQL Standard 2016 может использовать либо 4 сокета, либо 24 ядра, исходя из того, чего меньше (данные из таблицы на сайте Microsoft Ignite).
Рисунок 3. Ограничения разных версий MS SQL Server
Допустим, у Вас есть многопроцессорный сервер, т. е. сервер с несколькими сокетами. На нем размещена виртуальная машина, на которую установлен сервер MS SQL. При этом в виртуальной машине настроено 12 процессоров по 2 ядра. Сколько ядер будет использовать MS SQL?
MS SQL будет использовать 4 процессора, следовательно, всего 8 ядер из 24. Остальные ядра будут не загружены. Правильным решением будет настроить на виртуальной машине 4 процессора по 6 ядер на каждом, либо 2 по 12 ядер. В этом случае MS SQL сможет использовать все 24 ядра.
Рисунок 4. Неправильная и правильная настройки процессоров. В схеме № 1 MS SQL использует только 4 процессора из 12, а в схеме № 2 — все 12
Вот пример неверной настройки, когда вместо 12 ядер в виртуальной машине было настроено 12 сокетов — по одному ядру на каждый сокет. Видно, что загружены только 4 ядра из 12.
Рисунок 5. Неравномерная загрузка ядер при неправильной настройке виртуальной машины
Чтобы узнать, сколько логических процессоров (ядра, в том числе и гиперпоточные) задействует Ваш экземпляр MS SQL, следует выполнить следующий запрос
Рисунок 6. Отображение реального количества логических процессоров, используемых MS SQL Server
SQL Server detected 1 sockets with 4 cores per socket and 8 logical processors per socket, 8 total logical processors; using 8 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
Из этого текста следует, что все хорошо — число найденных ядер равно числу используемых.
SQL Server detected 4 sockets with 12 cores per socket and 12 logical processors per socket, 48 total logical processors; using 24 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
В этом случае половина процессорных мощностей простаивает.
Проверить, сколько ядер используют 1С
Важно помнить, что у 1С тоже есть ограничения по количеству используемых ядер. Например, версия ПРОФ может использовать только 12 ядер и, если поставить ее на 24-ядерный сервер, то половина процессорных мощностей будет простаивать.
Если на сервере больше 12 ядер и Вы хотите, чтобы 1С использовала их все, необходимо приобрести версию КОРП.
3. Проверить настройку Turbo Boost
Для сервера 1С критически важна частота процессора — чем она выше, тем лучше.
В большинстве процессоров есть возможность динамически менять частоту в зависимости от нагрузки — это позволяет процессору работать на частоте выше номинальной. У Intel такая технология называется Turbo Boost, у AMD — Turbo Core.
Рисунок 7. Схема работы технологии Turbo Boost
Turbo Boost включается в BIOS, но чтобы он полноценно работал, необходимо в настройках электропитания операционной системы установить режим электропитания Высокая производительность. Если этого не сделать, ОС будет занижать частоту процессора для экономии электричества.
Рисунок 8. Установка режима электропитания
4. Включить механизм версионирования в MS SQL для 8.2
Все еще много конфигураций работает в режиме совместимости с 8.2 или на самой платформе 8.2.
В управляемом режиме блокировок в 8.2 читающие запросы в транзакции устанавливают блокировки. Из-за этого при параллельной работе нескольких пользователей пишущие транзакции блокируют читающие, что приводит к излишним ожиданиям на блокировках.
Если у Вас такая конфигурация и в ней используется режим управляемых блокировок, необходимо обязательно в MS SQL включить режим версионирования READ_COMMITTED_SNAPSHOT.
Это можно сделать с помощью следующей команды:
При этом в базе не должно быть пользователей, иначе команда не будет выполнена.
После выполнения команды поведение системы будет аналогично 8.3: читающие запросы в транзакции не будут блокировать данные, следовательно, количество ожиданий на блокировках сократится.
Проблема в том, что периодически, например при обновлении, платформа будет возвращать прежний режим. Необходимо постоянно следить за тем, чтобы данный режим был включен. Например, можно сделать регламентное задание на MS SQL, которое будет регулярно выполнять вышеприведенных запрос.
5. Проверить выполнение регламентных операций СУБД и 1С
Как на СУБД, так и на 1С обязательно должны выполняться регламентные операции. Настройки регламентных операций — тема обширная и выходит за рамки этой статьи. Просто важно помнить, что без регламентных операций производительность системы будет падать.
Зачастую о регламентах забывают при апгрейде сервера, обновлениях, смене СУБД или смене паролей для запуска служб MS SQL, поэтому даже если все было настроено, лучше проверить еще раз, что регламентные операции по-прежнему выполняются.
Регламентные операции для СУБД
Для СУБД обязательными являются обновление статистики и дефрагментация/реиндексация индексов.
Обновление статистики — важнейшая регламентная операция. Если статистика не актуальна, то запросы на больших таблицах будут выполняться медленно.
Фрагментация индексов негативно влияет на скорость работы дисковой подсистемы и на объем оперативной памяти. Чем выше фрагментация на диске, тем больший объем оперативной памяти будут занимать данные.
Регламентные операции для 1С
В 1С в первую очередь имеются в виду сдвиг границы и пересчет итогов.
В идеале граница рассчитанных итогов должна быть на конец предыдущего месяца — тогда остатки за предыдущие периоды будут получаться быстро.
Рисунок 9. Установка периода рассчитанных итогов
Не стоит устанавливать период рассчитанных итогов далеко в будущем, иначе любая запись в регистр будет вызывать обновление итогов за все будущие месяцы. Это не только замедлит работу пользователя, но и увеличит время блокировки данных, что может привести к ожиданиям на блокировках.
Пересчет итогов также является важной регламентной операцией. При ее выполнении из таблицы удаляются нулевые итоги, которые могут занимать значительный объем. Также пересчет сворачивает строки, возникающие при работе механизма разделения итогов, что тоже уменьшает размер таблицы.
Полный пересчет итогов на больших базах занимает много времени, поэтому часто его предпочитают вообще не делать. Однако эту операцию необходимо проводить регулярно, чтобы не накапливать большое количество строк с нулевыми итогами. В качестве альтернативы можно выполнять пересчет только текущих итогов — эта операция выполняется гораздо быстрее.
6. Использовать таблицы итогов регистров сведений
Можно относительно легко ускорить запросы к срезам регистра сведений, если эти срезы выполняются без указания даты.
Ускорение достигается за счет механизма итогов для среза первых и последних. Если включить эту возможность, будут созданы отдельные таблицы, где хранятся только первые или только последние значения ресурсов по комбинации измерений.
Рисунок 10. Разрешение таблицы итогов регистра сведений
Включение этих флагов потребует времени для реструктуризации, особенно если таблица регистра сведений большая. Иногда это может занять очень много времени, поэтому лучше проводить такое обновление ночью или в выходные — так оно не помешает работе пользователей.
После обновления появятся отдельные таблицы срезов, обращение к которым будет идти, если в запросе не указана дата получения среза. Это сильно ускоряет чтение, особенно когда в регистре десятки миллионов строк и более.
У этого средства есть незначительный минус. На запись в регистр будет уходить чуть больше времени, т. к. помимо основной таблицы нужно будет обновлять и таблицы итогов.
7. Убрать лишнее
Этот способ ускорения системы заключается в том, чтобы отключить все, что не используется, но забирает ресурсы. Особенно это касается типовых конфигураций.
Кому-то покажется, что это совет от капитана Очевидность. Но стоит отбросить сарказм — есть множество баз, в которых работают все стандартные регламентные задания и оставлены настройки по умолчанию.
Проведите ревизию включенных регламентных заданий. Действительно ли все эти задания нужны? Может, часть относится к механизмам, которые Вы не используете?
Ситуация усугубляется, когда на сервере расположено большое количество баз: часть из них для разработки, часть — для тестов, еще часть кто-то когда-то зачем-то создал. В каждой из них выполняются задания, обновление полнотекстового поиска и т. д.
Все это никак не помогает производительности Вашей системы, поэтому стоит отключить все лишнее.
Первое, на что нужно обратить внимание при ревизии — это полнотекстовый поиск. Если он не используется, тогда лучше его отключить, чтобы он зря не «съедал» ресурсы системы.
(нажмите, чтобы увеличить картинку)
Рисунок 11. Отключение полнотекстового поиска 1С
Если же полнотекстовый поиск используется, тогда стоит оставить свойство «Полнотекстовый поиск» в значении Использовать только для тех реквизитов, где он действительно необходим. У остальных объектов следует его отключить.
Чек-лист быстрого ускорения системы
Итак, быстрые способы ускорить систему минимальными усилиями:
- Настроить параллелизм в MS SQL Server.
- Проверить, сколько ядер используют MS SQL Server и 1С.
- Проверить настройку Turbo Boost.
- Включить механизм версионирования в MS SQL для 8.2.
- Проверить выполнение регламентных операций СУБД и 1С.
- Использовать таблицы итогов регистров сведений.
- Убрать лишнее.
К любым рекомендациям следует относиться с осторожностью, в том числе и к тем, что представлены в этой статье. При изменении параметров обязательно наблюдайте за поведением системы, отмечайте произошедшие перемены, делай выводы и, исходя из них, решайте, что делать дальше.
Если 1С тормозит …
Но если Вы хотите подойти к вопросу оптимизации комплексно, рекомендуем наш курс «Ускорение и Оптимизация 1С. Базовый курс 2022».
Настройка параметра Max degree of parallelism при выполнении реструктуризации информационной базы
Использование параметра Max degree of parallelism Microsoft SQL Server с целью оптимизации скорости выполнения реструктуризации информационной базы с помощью нового механизма для Платформы 1С:Предприятие 8.3.11.
Параметр max degree of parallelism – дополнительная опция СУБД Microsoft SQL Server, которая определяет максимальное число процессоров, применяемых при выполнении одного запроса. Данная статья описывает сценарий его настройки для оптимизации выполнения реструктуризации информационной базы 1С:Предприятие с помощью нового механизма, который доступен, начиная с версии Платформы 8.3.11.
Немного теории
Одним из них является дополнительный параметр MS SQL Server Max degree of parallelism. Описание приведено в документации Microsoft https://technet.microsoft.com/ru-ru/library/ms181007(v=sql.105).aspx. Согласно нему, "параметр max degree of parallelism используется для ограничения числа процессоров, применяемых в планах параллельного выполнения. Чтобы разрешить серверу определять максимальную степень параллелизма, установите 0 в качестве значения данного параметра, то есть значение по умолчанию. Чтобы отключить создание параллельных планов, присвойте параметру max degree of parallelism значение 1.Параметр максимального значения степени параллелизма определяется выпуском SQL Server, типом ЦП и операционной системой. Если указано значение, превышающее количество доступных процессоров, используется фактическое число доступных процессоров. Если у компьютера только один процессор, то значение параметра max degree of parallelism учитываться не будет."
MS SQL Server определяет уровень параллелизма до начала выполнения запроса без необходимости перекомпиляции плана его выполнения, и один и тот же запрос в разное время в зависимости от условий на момент начала его выполнения может быть выполнен с разным уровнем параллелизма или вообще последовательно.
Практика применения параллелизма для ускорения выполнения реструктуризации базы данных
В общем случае мы рекомендуем устанавливать значение параметра max degree of parallelism равным 1, то есть запрещать Database Engine параллельное выполнение запросов. Почему?
- основная причина - исключить сценарий, при которой один длительный запрос с неэффективным планом выполнения создаст за счет параллелизма нагрузку на CPU, не позволяющую другим транзакциям выполняться с обычной для системы скоростью. Например, легко можно представить себе ситуацию, в которой пользователь захочет сформировать отчет, добавив в него "через точку" поле составного типа. В результате запрос, автоматически сгенерированный платформой 1С:Предприятие, особенно в случае добавления к нему ограничений по правам доступа на уровне записей (RLS), может оказаться очень длинным, содержать большое количество соединений и при этом хорошо поддаваться параллельной обработке, вызвав рост нгагрузки на систему, что скажется на ее работе в целом;
- поскольку запрос может иногда «распараллеливаться», а иногда – нет, время его выполнения будет варьироваться, что может мешать работе пользователей;
- параллелизм может вызывать негативные последствия, наличие и влияние которых необходимо специально оценивать. Прежде всего это – ожидания при синхронизации потоков выполнения, когда координатор потоков (поток 0) по тем или иным причинам при ждет передачи данных от «отстающих» потоков (вид ожиданий MS SQL Server CXPACKET ). Оценить наличие и величину этого ожидания можно, использовав данные DMV sys.dm_os_wait_stats , выполнив подобный запрос:
Кроме того, при слабой пропускной способности дисковой подсистемы параллелизм может приводить к замедлениям при выполнении запросов ввиду наличия ожиданий ввода-вывода, оценить которые можно, изменив условие в запросе выше на where waits.wait_type like N'%IO%' .
Крайне редки, но возможны появления взаимоблокировок потоков выполнения друг другом (intra-query parallelism).
Именно по этим причинам без дополнительного анализа и оснований мы рекомендуем отключать использование параллелизма в продуктивных системах и включать его осознанно тогда, когда это необходимо.
Пример того, когда параллельное выполнение запросов может принести ощутимый эффект – реструктуризация информационной базы, и в особенности - с использованием нового её механизма, доступного с версии 8.3.11 платформы 1С: Предприятие .
Реструктуризация – это совокупность операций с информационной базой, необходимость которых вызвана изменением структуры и состава ее таблиц . Эти операции включают в себя такие действия, как создание новых таблиц и перенос в них данных, добавление колонок в существующие таблицы, обновление их записей, пересоздание индексов таблиц и, конечно, пересчет итогов регистров после завершения операций с данными.
Добавление и удаление колонок, добавление, удаление и изменение индексов таблиц, обновление записей существующих таблиц и перенос данных в новые таблицы с помощью единых запросов UPDATE или INSERT - это именно те операции, которые выполняются при реструктуризации, и именно они как нельзя лучше подходят для параллельной обработки. Почти всегда эти операции выполняются над большими объемами данных, а сами запросы ввиду особенностей выполняемых действий имеют достаточно простой синтаксис, что позволяет Database Engine при наличии доступных процессорных ресурсов почти всегда предпочитать параллельный план выполнения последовательному и получать от этого существенный оптимизационный эффект.
Для того, чтобы это стало возможным, рекомендованное значение параметра max degree of parallelism - 1 - должно быть изменено. Рекомендация по определению его значения для многопроцессорной системы обычно звучит как «общее количество ядер процессора / 2». Значение 0 предоставляет СУБД возможность самостоятельного определения максимально возможного количества потоков исходя из доступности ресурсов, и применимо к выполнению реструктуризации мы рекомендуем использовать именно его.
Изменить значение параметра можно с использованием SSMS двумя способами:
- С помощью системной хранимой процедуры sp_configure, выполнив в среде Management Studio следующий код:
sp_configure 'show advanced options', 1;
go
reconfigure;
go
sp_configure 'max degree of parallelism', 0;
go
reconfigure;
go
- В пользовательском режиме. Для этого необходимо открыть свойства сервера Microsoft SQL на закладке Advanced в среде SSMS и изменить значение на желаемое :
Изменения вступят в силу сразу без необходимости перезапуска сервера баз данных.
После завершения реструктуризации мы рекомендуем в общем случае вернуть значение в исходное состояние. Конечно же, в процессе ее выполнения стоит выполнять мониторинг ожиданий СУБД, о котором сказано выше, а также производительности дисковой подсистемы при помощи, например, Performance Monitor.
Эффект от параллелизма при выполнении именно реструктуризации будет, конечно, индивидуален в зависимости от факторов, описанных выше, но главным образом – от размеров конкретной информационной базы и характера и количества структурных изменений. В завершение хотелось бы привести количественную оценку ускорения, которую нам удалось получить при тестировании параллелизма для нового механизма реструктуризации:
Ускорение maxdop=0 по
сравнению с maxdop=1, %
Изменение состава регистраторов для регистра бухгалтерии, БГУ, размер таблиц регистра > 170 Гб
Миграция данных
Обновление релиза конфигурации ERP с версии 2.1.3.77 на версию 2.2.4.67, размер ИБ > 200 Гб
Миграция данных
Пересчет итогов
Запускаем SQL server Profiler, устанавливаем соединение, отмечаем события и колонки как показано на рисунке 3.
Рисунок 3. Trace properties
Устанавливаем отбор для нашей базы
Рисунок 4. Фильтр по базе
· Max degree of parallelism – MDOP
· Сost threshold for parallelism - cost
Тестирование последовательного плана запроса (MDOP = 1)
Далее, в среде 1С Предприятие в тестовой базе запускаем консоль запросов и выполняем запрос (Рис.5)
Рисунок 5. Консоль запросов – время выполнения 20 сек.
Параметр SQL сервера “Max degree of parallelism” установлен в 1 (без параллелизма). Смотрим результат в профайлере (рис.6)
Рисунок 6. Последовательный план запроса
SQL сервер сформировал последовательный план запроса, при этом: общая загрузка CPU = 6,750 (сек), а время на выполнение запроса = 7,097(сек)
Тестирование параллельного плана запроса (MDOP = 0, cost =5)
Переводим SQL server в режим параллелизма (в SQL Query):
EXEC sp_configure 'show advanced option' , 1 ;
RECONFIGURE WITH OVERRIDE
exec sp_configure 'max degree of parallelism' , 0 ;
RECONFIGURE WITH OVERRIDE
Выполняем тот же запрос (Рисунок 7)
Рисунок 7. Консоль запросов – время выполнения 16 сек.
Проверяем результат в профайлере (Рисунок 8)
Рисунок 8. Параллельный план запроса
Сервер SQL в этот раз сформировал параллельный план запроса, при этом общая загрузка CPU = 7,905 сек, а длительность выполнения запроса = 3,458 сек
Тестирование последовательного плана запроса (MDOP = 0, cost = 150)
Попытаемся избавиться от параллельного плана, используя параметр «Сost threshold for parallelism». По умолчанию параметр установлен в 5. В нашем случае от формирования параллельного плана удалось избавиться при значении 150 (в SQL Query):
exec sp_configure 'cost threshold for parallelism' , 150 ;
Проверяем выполнение запроса в данных условиях (рис. 9)
Рисунок 9. Консоль запросов – время выполнения 20 сек.
Проверяем результат в профайлере (рис.10)
Рисунок 10. Последовательный план запроса.
Сервер SQL сформировал последовательный план запроса. Общая загрузка CPU = 7,171 сек, время выполнения запроса =7, 864 сек.
Выводы:
· Выполнение тестового запроса в среде 1С Предприятия с использованием SQL сервером параллельного плана запроса дает значительный прирост производительности по сравнению с последовательным планом (16 сек. против 20 сек. – выигрыш 4 сек.)
· Выполнения тестового запроса самим сервером SQL при использовании параллельного плана запроса происходит в два раза быстрее, чем при использовании последовательного плана запроса (3,5 сек. против 7,1 сек.)
· Параллелизм SQL сервера можно регулировать не только, используя параметр MDOP, но и параметр «Сost threshold for parallelism»
Специальные предложения
Почему-то после прочтения возникает ощущение исследования сферического коня в вакууме. Хотя читалось не без интереса и за труды не могу не поставить плюса. Но.
Во-первых, удручает практичность выводов. Учитывая то, что самим техсапом MS не рекомендуется менять значение параметра ctfp, а также то, что понятие стоимости запроса неизвестно в каких попугаях меряется (об этом красноречиво говорит разница между значением ctfp и фактическим временем исполнения запроса) - напрашивается вывод о нецелесообразности оперирования данным инструментом. Кстати, вопрос: а UPDATE STATISTICS перед замерами делался? а то может 150 и получилось из-за неверной предварительной расценки стоимости запроса?
Во-вторых, hash match по декартову произведению одной из крупнейших таблиц (в среднестатической ИБ) самой на себя вряд ли можно назвать достаточно репрезентативной операцией для оценки такой глобальной настройки, как ctfp.
Ну и в-третьих, если уж идти в отрыв от реальности и оценивать синтетические результаты, то куда интереснее посмотреть выигрыш при параллельном выполнении TABLE SCAN, а не при переборе упорядоченного индекса. И вообще пойдёт ли оптимизатор на распараллеливание при TABLE SCAN?
(1) с обновлением и без обновления статистик результаты схожие. Относительно ctfp и техсапа, хотелось бы увидеть ссылочку. Все выводы сделаны по конкретному запросу в конкретной тестовой среде.Про Table Scan пока не могу сказать. Параллелизм накладывает ряд ограничений. В одной из статей мне попадалась эта тема.
Операции с индексами, которые создают или перестраивают индекс или удаляют кластеризованный индекс и запросы, интенсивно использующие циклы ЦП, являются лучшими кандидатами для параллельного плана. Например, хорошими кандидатами являются соединения больших таблиц, больших статистических выражений и сортировка больших результирующих наборов. Простые запросы, часто находящиеся в приложениях обработки транзакций, находят дополнительную координацию, запрашиваемую для выполнения запроса в параллельном перевешивании возможного повышения производительности. Чтобы отличить запросы, которые выигрывают от параллелизма, и запросы, которые не выигрывают, компонент Database Engine сравнивает предполагаемую стоимость выполняемого запроса или операции с индексами со значением cost threshold for parallelism. Несмотря на то, что это не рекомендуется, пользователи могут менять значение по умолчанию 5 при помощи процедуры sp_configure.
(6) практически не сомневался в этом, потому как сегментировать перебор кучи вполне логично для оптимизатора при доступной возможности распараллеливания операций. Интересен размер эффекта. пусть даже на синтетическом примере.
(8) SELECT
Count(*)
FROM [ExchangeDB].[dbo].[_Employee_Changes] as Part1
LEFT JOIN [ExchangeDB].[dbo].[_Employee_Changes] as Part2 ON Part1._IDRRef = Part2._IDRRef
Описанный выше запрос на неиндексированной таблице при параллельном плане дает двойной прирост (0,250) против (0,573). Запрос возвращает 260603669
Статья удивила. Вообще max degree of parallelism ставят в 1 изначально потому что MS SQL при параллельном исполнении накладывал "непонятные блоикровки"и доходило до DeadLock - ов. На текущей версии (2012) ничего не поменялось. если только в 2014 что-то "доделали". Во-вторых 1С это же СКД + Консоли запросов/отчетов всякие. если в базе 300+ пользователей и куча разных "фич" то установка данного свойства не в "1" приводит к "убиению" SQL сервера неправильным запросом. или правильным, но 1 пользователь мешает работе остальных 300.
А показать что использование нескольких ядер вместо одного даёт преимущества можно к примеру и на MS Excel если его запустить с нужным Affinity.
(12) у нас работает на 2008 R2 SQL server проблем не отмечается. Все-таки, хотелось уточнить на каких конфигурациях сервера вы рекомендуете ставить MDOP в 1
(12) к тому же не все запросы выполняются параллельно и параллелизм можно регулировать параметром "Сost threshold for parallelism"
(12) comol, На 1С мне известен только один нормальный кейс когда возможно выставлять 0. Это обновление информационной базы с реструктуризацией. Лет 5 назад, еще до 8.3 и фонового обновления использовали такой вот хинт подсмотренный у DBA, которые так оптимизировали BULK INSERT операции.
код скрипта выглядел так на псевдо языке
В остальных случаях согласен - Maxdop = 1 на весь сервер. Чистые SQL специалисты играются с хинтами в запросах, у нас же такой возможности нет.
(16) lustin, соврал насчет нет возможности. Есть. Но очень дорого
(17) lustin, это параллелизм на уровне прикладного кода, а не запросов, и к обсуждаемой тематике отношения не имеет.
(12) кстати, столкнулся с тем, что не для всех очевидно "что использование нескольких ядер вместо одного даёт преимущества", поэтому пришлось проделать данную работу
Каким образом было снижено или исключено влияние процедурного и буферного кэшей на производительность выполняемых (в ходе тестов) запросов?
(19) уже лучше, но почему в статье об этом ничего не написано? А что насчет буферного кэша? Или на ваш взгляд его состояние (как и состояние процедурного) незначительно влияет на скорость выполнения запросов?
(21) уточнения по тому же запросу:
после
DBCC FREEPROCCACHE
GO
dbcc dropcleanbuffers
GO
MDOP = 1 первый раз - 40,739 сек, второй раз - 7,116 сек
MDOP = 0 первый раз - 15,803 сек, второй раз - 3,498 сек
(23) ну вот уже лучше. Теперь понятно что этот конкретный select в лабораторных условиях при mop<>1 выполняется быстрее. А теперь откройте тайну, какую практическую ценность несет эта информация?
(28) ну так возьмите стандартный нагрузочный тест и докажите, что применение параллелизма существенно повышает производительность при многопользовательской работе.
У пиндосов вот есть доказательства ввода РФ войск на Украину. Но они их тоже никому не показывают. Чем закончилась история с доказательствами наличия ОМП в Ираке, мы все знаем. Не уподобляйтесь.
(28) В таком случае я предлагаю перенести эти исследования в ваш личный блог. Потому как запуск одного запроса (отражающего специфику работы вашей системы) в лабораторных условиях, ничего не доказывает. Кроме того не раскрыты методы борьбы с ошибками СУБД:
(32) вы предлагаете в одной работе затронуть такие темы, каждой из которой посвящаются отдельные работы?
Intra-query parallelism caused your server command. зачастую не ошибки СУБД а кривой код.
Я бы с удовольствием, заблокировал эту статью, коль у Вас она вызывает такое раздражение, однако, я наблюдаю некоторый интерес со стороны других читателей и не хотелось бы их подводить
(33) не надо блокировать. Вы затронули достаточно больную и серьезную тему. В эту сторону так или иначе смотрели многие. Просто единого подхода так и не выработалось. Точнее скажу немного по другому "В Prodcution решениях на 1С многие отказались от исследования данной тематики"
Оказалось, что дешевле, по затратам времени и по затратам на поддержку, реалилизовывать парралельность на уровне кода ORM.
Предвосхищу, что настоящие DBA меня сейчас закидают "тряпками", но все же, я не зря дал выше пример кода от СофтПоинта на чистом 1С.
Обычно в 1С, да и не в 1С смотрят в сторону адаптации метаданных (таблиц СУБД) и кода контролера под многопоточность выборок. Здесь необходимо явно управлять блокировками по ключам, одновременно с этим обратить внимание на сортировку индексных выражений. Но зато слияние данных происходит на уровне сервера приложений - то есть там где также существует возможность организовать и кэш частично рассчитанных данных.
То есть фактически это такой MapReduce подход (с большой конечно натяжкой). И в этом случае задача сервера СУБД обеспечить многопоточное выполненение транзакций на чтение небольших наборов данных, а уже сервер приложений обеспечит агрегирование на уровне контролера или агрегатов таблиц: но в общем случае это более управляемый процесс, потому что код открыт для вас как разработчика.
Также при использовании кода фрэймворка для реализации парралельности выполнения операций с СУБД, оказалось проще "разруливать" те самые "дедлоки", так как явно понятно какой участок кода какой участок кода блокирует. А не разбираться между сессиями и кэшем SQL запросов, которые НЕ совсем прозрачны для этих целей.
Вместе с тем, я еще раз повторюсь - не обращайте внимание на наше ворчание в комментариях. Если вы решили исследовать этот вопрос - это уже круто, не каждый решится на такое. Да и мой комментарий не яляется истиной в последней инстанции.
целесообразно лишь при наличии уверенности в существенном положительном эффекте от использования параллелизма при многопользовательской работе. В чем лично я совсем не уверен.
Если речь о физических чтениях - мы по определению не можем их выполнять параллельно, шина-то одна. В целом параллелизм позволяет более эффективно использовать ресурсы нескольких CPU, но наращивая нагрузку, упираемся мы, как правило, совсем не в CPU и даже не на стороне SQL.
(34) lustin,
согласен с Вами,
чем ждать панацеи на уровне базы данных, лучше оптимизировать сами запросы;
особенно когда, как Вы заметили, источник блокировки ясен - зачем менять курс всего корабля :-)
проверил, SQL 2008 - на четырех серверах MDOP = 0.
настройки на серверах "по умолчанию", поэтому что-то не совсем понял на счет споров об отмене "MDOP = 1" -
изначально и так "MDOP = 0", разве нет? :-)
(24) zoytsa, MDOP=0 есть настройка, разрешающая параллелизм и не ограничивающая количество CPU, участвующих в запросе. MDOP=1 ограничивает количество CPU, участвующих в исполнении запроса, одним и таким образом запрещает параллелизм.
(41) при том, что это намного более "жизненное" тестирование, чем один огромный джоин. Для случая из статьи понятно, что параллелизм даст выигрыш. А вот для кучи мелких запросов, которые составляют большую часть алгоритмов прикладных решений, ситуация может быть другой (вплоть до замедления ;)).
Ну и до кучи - может быть вы попробуете многопоточный тест (который, конечно, тоже жутко синтетический) с разными настройками параллелизма и посмотрите, как изменится масштабируемость от этих настроек.
(42) мелкие запросы не параллелизируются, нужно очень сильно изгаляться, чтобы добиться этого - для этой задачи нужно пробовать другие механизмы, первое, что приходит на ум - ручной параллелизм или многопоточность
многопоточный попробую
(44) запрос приведен в качестве примера, готовых рецептов нет, нужно смотреть, экспериментировать, подбирать оптимальный вариант. Посмотрите предыдущие и последующие публикации на эту тему, возможно, попадется что-то жизненное
Я думаю станет понятно, что ускорять надо больше саму 1с + ускорять канал связи 1С с SQL.
Кто мне скажет что надо ставить 0 или 1 заплюю пока не захлебнётесь.
(49) Ну нет :). Просто при отключенном параллелизме книга продаж за месяц крутится более 6 часов при включенном около 30 - 35 минут.
При размещении 1С в облачной инфраструктуре и среде виртуализации наиболее важными и непростыми задачами являются повышение скорости работы платформы «1С» и настройка СУБД. Для достижения максимальной производительности инфраструктуры 1С рекомендуется правильно выбирать архитектуру инфраструктуры, режимы работы, проверить и выполнить ряд важных настроек.
В зависимости от количества пользователей, размера баз данных и ограничений бюджета (с учетом стоимости дополнительных лицензий на сервер «1С:Предприятие 8» и лицензий на СУБД) платформа «1С» может работать в файловом и клиент-серверном вариантах (на основе трехуровневой архитектуры «клиент-сервер» (рис. 1): клиентское приложение, кластер серверов «1С:Предприятия 8», СУБД).
Рис. 1
Как правильно выбрать вариант/режим работы 1С: файловый или SQL?
Обычно для 1-10 пользователей выбирается файловый режим
От 10 и более пользователей выбирается режим работы с использованием SQL
В файловом варианте все пользователи могут работать на одной виртуальной машине в облаке, например на терминальном сервере.
Для клиент-серверного варианта лучше выбрать не менее двух виртуальных машин:
Сервер с клиентским приложением, например терминальный сервер с клиентской частью «1С» (толстый клиент)
Сервер «1С» и СУБД (MS SQL или PostgreSQL)
Как рассчитать мощности сервера для 1С в файловом режиме работы?
В обоих вариантах: файловом и SQL, для работы с пользовательским приложением 1С в классическом режиме, например, «удаленного рабочего стола» (так называемый «толстый клиент»), необходимы следующие минимальные ресурсы виртуального сервера:
Количество виртуальных ядер CPU = 1 или 2 для ОС + 0,25 * количество пользователей
Объем памяти RAM = 1 или 2 ГБ для ОС + 0,5 ГБ * количество пользователей
Размер диска/хранилища HDD = 20-40 ГБ для ОС и приложений + (0,1-10) ГБ * количество пользователей. Для ОС и 1С рекомендуется использовать самые быстрые диски
Как рассчитать мощности сервера для 1С в варианте работы с SQL?
В клиент-серверном варианте работы 1С, в котором используется СУБД SQL, рекомендуется разместить 1С Сервер и сервер SQL на отдельном виртуальном сервере в общей с клиентским сервером локальной подсети. Необходимы следующие минимальные мощности для этого виртуального сервера:
Размер диска/хранилища HDD = 20-40 ГБ для ОС и приложений + (10-1000) ГБ в зависимости от объема и количества баз данных. Для ОС и СУБД рекомендуется использовать самые быстрые диски
------------
ОС - операционная система, например, Windows Server
Здесь Сервер 1С - ПО "сервер "1С:Предприятия 8"
Наиболее важными и непростыми задачами являются повышение продуктивности использования платформы «1С» в облаке и настройка СУБД. Типичные проблемы при развертывании и эксплуатации облачной инфраструктуры для «1С» следующие:
Неправильный выбор мощностей
Неквалифицированная настройка сервисов виртуальной инфраструктуры
Недостаточное внимание к тестированию производительности платформы «1С»
Для достижения максимальной производительности рекомендуется проверить и выполнить ряд настроек. Прежде всего необходимо исключить свопинг, для чего с помощью системы мониторинга следует обязательно удостовериться в том, что объем оперативной памяти достаточен для работы ВМ. Кроме того, файл подкачки ОС, профили пользователей, файлы баз данных, файлы логов транзакций (SQL) и tempDB (SQL) лучше разместить на дополнительных SSD-дисках, а для файла подкачки установить фиксированный размер.
На SQL-сервере необходимо выключить все ненужные службы, например FullText Search и Integration Services, установить максимально возможный объем оперативной памяти, максимальное количество потоков (Maximum Worker Threads) и повышенный приоритет сервера (Boost Priority), задать ежедневную дефрагментацию индексов и обновление статистики, настроить автоматическое увеличение файла базы данных (не менее 200 Мбайт) и файла лога (не менее 50 Мбайт), а также полную реиндексацию не реже одного раза в неделю. При размещении серверов SQL и «1С:Предприятие» на одной ВМ следует включить протокол Shared Memory.
Следуя перечисленным выше рекомендациям, можно добиться увеличения быстродействия платформы «1С» в облаке в 1,5–2 раза.
Квалифицированное размещение ИТ-сервисов, в том числе «1С», на облачной платформе позволяет:
Существенно сократить расходы
Повысить уровни безопасности (доступ к данным, резервное копирование, антивирусная защита и др.) и технического обслуживания
Обеспечить централизованное администрирование и мониторинг
Организовать эффективную и безопасную удаленную работу
Воспользоваться гибкими возможностями масштабирования, лицензирования и оперативного перехода на необходимые версии конфигураций «1С»
ЧЕК-ЛИСТ ПО ОПТИМИЗАЦИИ ИНФРАСТРУКТУРЫ 1С С MS SQL
1. Включить возможность мгновенной инициализации файлов (Database instant file initialization)
Это позволяет ускорить работу таких операций как:
Создание базы данных
Добавление файлов, журналов или данных в существующую базу данных
Увеличение размера существующего файла (включая операции автоувеличения)
Восстановление базы данных или файловой группы
Для включения настройки:
На компьютере, где будет создан файл резервной копии, откройте приложение Local Security Policy (secpol.msc)
Разверните на левой панели узел Локальные политики, а затем кликните пункт Назначение прав пользователей
На правой панели дважды кликните Выполнение задач по обслуживанию томов
2. Включить параметр «Блокировка страниц в памяти» (Lock pages in memory)
Эта настройка определяет, какие учетные записи могут сохранять данные в оперативной памяти, чтобы система не отправляла страницы данных в виртуальную память на диске, что может повысить производительность.
Для включения настройки:
В меню Пуск выберите команду Выполнить. В поле Открыть введите gpedit.msc
В консоли Редактор локальных групповых политик разверните узел Конфигурация компьютера, затем узел Конфигурация Windows
Разверните узлы Настройки безопасности и Локальные политики
Выберите папку Назначение прав пользователя
Политики будут показаны на панели подробностей
На этой панели дважды кликните параметр Блокировка страниц в памяти
В диалоговом окне Параметр локальной безопасности — блокировка страниц в памяти выберите «Добавить» пользователя или группу
В диалоговом окне Выбор: пользователи, учетные записи служб или группы добавьте ту учетную запись, под которой у вас запускается служба MS SQL Server
Чтобы изменения вступили в силу, перезагрузите сервер или зайдите под тем пользователем, под которым у вас запускается MS SQL Server
3. Включить каталоги с файлами базы данных в правила исключения для антивируса.
Если антивирус будет сканировать файлы базы, это может сильно замедлить работу СУБД.
Для опытных администраторов: антивирус на сервер СУБД лучше не устанавливать.
4. Включить каталоги с файлами базы данных в список исключений для системы автоматического копирования.
Если на сервере установлена система автоматического копирования файлов, то, когда она будет копировать файлы базы, это может привести к замедлению работы. Копии базы необходимо делать средствами самой СУБД.
5. Отключить механизм DFSS для дисков.
Механизм Dynamic Fair Share Scheduling отвечает за балансировку и распределение аппаратных ресурсов между пользователями. Иногда его работа может негативно сказываться на производительности 1С.
Чтобы отключить его только для дисков, нужно:
Найти в реестре ветку HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\TSFairShare\Disk
Установить значение параметра EnableFairShare в 0
6. Отключить сжатие данных для каталогов, в которых лежат файлы базы.
При включенном сжатии ОС будет пытаться дополнительно обрабатывать файлы при модификации, что замедлит сам процесс записи, но сэкономит место.
Чтобы отключить сжатие файлов в каталоге, необходимо:
Открыть свойства каталога
На закладке Общие нажать кнопку Другие
Снять флаг «Сжимать» содержимое для экономии места на диске
7. Установить параметр «Максимальная степень параллелизма» (Max degree of parallelism) в значение 1.
Данный параметр определяет, во сколько потоков может выполняться один запрос. По умолчанию параметр равен 0, это означает, что сервер сам подбирает число потоков. Для баз с характерной для 1С нагрузкой рекомендуется поставить данный параметр в значение 1, т.к. в большинстве случаев это положительно скажется на работе запросов.
Для настройки параметра необходимо:
Запустить Management Studio и подключиться к нужному серверу
Открыть свойства сервера и выбрать закладку Дополнительно
Установить значение параметра равное единице
8. Ограничить максимальный объем памяти сервера MS SQL Server.
Необходимо ограничить максимальный объем памяти, потребляемый MS SQL Server, особенно это критично, если роли сервера 1С и сервера СУБД совмещены. Максимальный объем памяти, рекомендуемый для MS SQL Server, можно рассчитать по следующей формуле:
Память для MS SQL Server = Память всего – Память для ОС – Память для сервера 1С
Например, на сервере установлено 64 ГБ оперативной памяти, необходимо понять, сколько памяти выделить серверу СУБД, чтобы хватило серверу 1С.
Для нормальной работы ОС в большинстве случаев более чем достаточно 4 ГБ, обычно – 2-3 ГБ.
Чтобы определить, сколько памяти требуется серверу 1С, необходимо посмотреть, сколько памяти занимают процессы кластера серверов в разгар рабочего дня. Этими процессами являются ragent, rmngr и rphost, подробно данные процессы рассматриваются в разделе, который посвящен кластеру серверов. Снимать данные нужно именно в период пиковой рабочей активности, когда в базе работает максимальное количество пользователей. Получив эти данные, необходимо прибавить к ним 1 ГБ – на случай запуска в 1С «тяжелых» операций.
Чтобы установить максимальный объем памяти, используемый MS SQL Server, необходимо:
Запустить Management Studio и подключиться к нужному серверу
Открыть свойства сервера и выбрать закладку Память
Указать значение параметра Максимальный размер памяти сервера
9. Включить флаг «Поддерживать» приоритет SQL Server (Boost SQL Server priority).
Данный флаг позволяет повысить приоритет процесса MS SQL Server над другими процессами.
Имеет смысл включать флаг только в том случае, если на компьютере с сервером СУБД не установлен сервер 1С.
Для установки флага необходимо:
Запустить Management Studio и подключиться к нужному серверу
Открыть свойства сервера и выбрать закладку Процессоры
Включить флаг «Поддерживать приоритет SQL Server (Boost SQL Server priority)» и нажать Ок
10. Установить размер авто увеличения файлов базы данных.
Автоувеличение позволяет указать величину, на которую будет увеличен размер файла базы данных, когда он будет заполнен. Если поставить слишком маленький размер авторасширения, тогда файл будет слишком часто расширяться, на что будет уходить время. Рекомендуется установить значение от 512 МБ до 5 ГБ.
Для установки размера авторасширения необходимо:
Запустить Management Studio и подключиться к нужному серверу
Открыть свойства нужной базы и выбрать закладку Файлы
Напротив каждого файла в колонке Автоувеличение поставить необходимое значение
Данная настройка будет действовать только для выбранной базы. Если вы хотите, чтобы такая настройка действовала для всех баз, нужно выполнить эти же действия для служебной базы model. После этого все вновь созданные базы будет иметь те же настройки, что и база model.
11. Разнести файлы данных mdf и файлы логов ldf на разные физические диски.
В этом случае работа с файлами может идти не последовательно, а практически параллельно, что повышает скорость работы дисковых операций. Лучше всего для этих целей подходят диски SSD.
Для переноса файлов необходимо:
Запустить Management Studio и подключиться к нужному серверу
Открыть свойства нужной базы и выбрать закладку Файлы
Запомнить имена и расположение файлов
Отсоединить базу, выбрав через контекстное меню Задачи – Отсоединить
Поставить флаг Удалить соединения и нажать Ок
Открыть Проводник и переместить файл данных и файл журнала на нужные носители
В Management Studio открыть контекстное меню сервера и выбрать пункт Присоединить базу
Нажать кнопку Добавить и указать файл mdf с нового диска
В нижнем окне сведения о базе данных в строке с файлом лога нужно указать новый путь к файлу журнала транзакций и нажать Ок
12. Вынести файлы базы TempDB на отдельный диск.
Служебная база данных TempDB используется всеми базами сервера для хранения, промежуточных расчетов, временных таблиц, версий строк при использовании RCSI и многих других вещей. Обычно обращений к этой базе очень много, и если она будет лежать на медленных дисках, это может замедлить работу системы.
Рекомендуется хранить базу TempDB на отдельном диске для повышения производительности работы системы.
Для переноса базы TempDB на отдельный диск необходимо:
Запустить Management Studio и подключиться к нужному серверу
Создать окно запроса и выполнить скрипт:
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'Новый_Диск:\Новый_Каталог\tempdb.mdf')
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'Новый_Диск:\Новый_Каталог\templog.ldf')
Перезапустить MS SQL Server
13. Включить Shared Memory, если сервер 1С расположен на том же компьютере, что и сервер СУБД.
Протокол Shared Memory позволит общаться приложениям через оперативную память, а не через протокол TCP/IP.
Для включения Shared Memory необходимо:
Запустить диспетчер конфигурации SQL Server
Зайти в пункт SQL Native Client – Клиентские протоколы – Общая память – Включено
Поставить значение Да и нажать Ок
Протокол Именованные каналы нужно выключить аналогичным образом
14. Перезапустить службу MS SQL Server
Внимание! Когда все настройки выполнены, необходимо перезапустить службу MS SQL Server
Читайте также: