Процы 2 xeon e5630. 64 ГБ оперативы.
Система на sas дисках там же системные базы.
одна база на sas диске. остальные на SSD по sata2.
Логи на обычном sata 4 ТБ.
Проблемы - очень часто вылетают блокировки по таймаутам - при этом чаще всего нет очередей к дикам и диски не загружены даже на 50 процентов.
Плюс при этом часто ужасно тормозит 1с. Работаем толстыми клиентами. на сервере 1с проц не загружен и оперативы съедено около 5 гб из 16. Т.е. нажимаю проведение документа реализации с 1 строчкой и могу ждать около 10 секунд.
Создается ощущение что sql сервер очень медленно обрабатывает запросы. Т.е. очередей нет, загрузки большой нет, но работает всё очень медленно. Все рекомендуемые настройки SQL я уже выполнил. Может быть есть еще какие-то тонкости?
(47) asved.ru, "Заметных на глаз отличий от УБ без снапшотинга нет."
Вы в этом уверены что нет отличий ? Просто не факт что при конвертации из 8.2 на 8.3 база переходит в снапшотинг.
Вероятно снапшотинг включается в 8.3 только для новых баз, причем создаваемых на MS SQL средствами самой платформы.
Просто не факт что при конвертации из 8.2 на 8.3 база переходит в снапшотинг
1) При какой еще конвертации?
2) При обновлении платформы снапшотинг не включается. Это факт.
Вы в этом уверены что нет отличий ?
Не "нет отличий", а "нет заметных на глаз отличий". Если собрать lock_asquired, отличия при существенном проценте ожиданий есть.
Вероятно
Вот чтобы было не "вероятно", а точно, откройте свойства базы в SSMS и посмотрите. Да, раньше флаг read_committed_snapshot при создании базы не ставился, сейчас - ставится. allow_snapshot_isolation, впрочем, не ставится и сейчас.
чтобы уменьшить ожидание на блокировках, нужно переписывать работу с управляемыми блокировками в типовых конфигурациях
Не совсем так. В read_committed при выполнении запроса накладываются S блокировки на читаемые записи таблиц. Накладываются они на период исполнения запроса, а так как запросы, как правило, выполняются по таблицам итогов, то эти итоги другой транзакцией изменены быть не могут, и возникает ожидание. Все не так печально, как при автоблокировках, где мы имеем repetable_read и удержание блокировок до конца транзакции, но тем не менее ожидания есть.
Read_committed_snapshot же режим не накладывает блокировок при исполнении читающего запроса вообще, т.к. читаются по сути не данные, а их срез на момент начала транзакции, неизменный по определению.
А вот грязное чтение вне транзакции 1С по-прежнему проблемой не считает и allow_snapshot_isolation не включает.
(51) asved.ru, "Read_committed_snapshot же режим не накладывает блокировок при исполнении читающего запроса вообще, т.к. читаются по сути не данные, а их срез на момент начала транзакции, неизменный по определению."
Я написал про то что надо вносить изменения в управляемые блокировки (это уровень сервера приложений), а вы пишете про Read_committed_snapshot (это уровень СУБД). С точки зрения пользователя ему все равно на каком из уровней происходит ожидание на блокировке (на СУБД или на сервере приложений). Нужно уменьшать время блокировки и убирать избыточные блокировки. Read_committed_snapshot решает проблему наложения избыточныз блокировок на уровне СУБД. Для полного счастья нам нужно еще убрать избыточные блокировки на уровне сервера приложений 1С и исключить возникновение взаимоблокировок.
(53) asved.ru, "Огласите, пожалуйста, методику. Очень интересно. Даже не столько определить, что некие УБ являются избыточными, а именно как их убрать"
Основная идея - минимизировать время на которое наложена блокировка. Т.е. убрать избыточность во времени наложения блокировки. Не устанавливать управляемые блокировки явно в коде проведения (т.к. они сохранятся до конца транзакции, т.е. проведения), убрать из кода явную запись в регистры. Блокировки будут наложены автоматически системой в самом конце проведения на время записи в регистры. Идея не новая, но почему то мало кто ее понимает :-) Проблема производительности 1С - проблема "длинных транзакций".
(2) nickpugachev, родные 1с я поотключал. Только ночные. два раза в неделю делаю тест и исправление (1 раз со всем галками, 2 раз без реиндексации). На SQL выставлены регламентные по рекомендации (т.е. реиндексация, дефрагментация, обновление статистики, бэкапы транзакций).
режим восстановления полный. Пробовал простой - без изменений.
Конфигурация УПП (остальные слабо загружены, по ним очень мало работают). так же работает ТЦУ и тоже с блокировками.
К примеру сейчас. реализация в 6 позиций проводится 16 секунд. Активность винчестера с базой практически нулевая.
1. если проводите какие-либо регламенты 1с, то отключайте реструктуризацию везде - ее sql выполняет (ребилд индексов - это оно и есть)
2. если базы в полном режиме, то диск, на котором расположены логи - должен быть не менее быстрым, чем диск с базой
3. запустить на sql сервере счетчики на память, диски, сеть, процессор и посмотреть пики и средние за период, когда все работают.
(5) asved.ru, Ничего особо не изменяется. Похоже на проблему именно с серваком.
Судя по многочисленным прочитанным форумом могу предположить, что проблема в том, что практически все наши пользователи двигаются по одному и тому же регистру.
блокировки у нас автоматические, т.к. пока не нашел ни одной толковой инструкции, как переписать блокировки.
(9) squall84, смотрите ожидания
этот запросик даст топ-10 ожиданий по серверу с момента его перезагрузки
SEL ECT TOP 10
wait_type AS [Wait Type,
wait_time_ms / 1000 AS [Wait time (s)],
CONVERT(DECIMAL(12,2), wait_time_ms * 100.0 / SUM(wait_time_ms) OVER()) AS [% waiting]
FR OM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
ORDER BY wait_time_ms DESC;
ну и конкретно ожидания на блокировках - тут либо техжурнал, ЦУП/Гилев/EI с красивыми картинками, либо руками
SELECT TOP 30
(total_elapsed_time - total_worker_time) / qs.execution_count AS [Average Time Blocked],
total_elapsed_time - total_worker_time AS [Total Time Blocked],
qs.execution_count AS [Execution count],
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS [Individual Query],
qt.text [Parent Query],
DB_NAME(qt.dbid) AS [Database name]
FR OM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
// этот кусок отберет по базе, если надо по всем - удалить
WH ERE DB_NAME(qt.dbid)='ИмяБазы'
ORDER BY [Average Time Blocked] DESC;
21.
Gilev.Vyacheslav
191116.07.14 12:42 Сейчас в теме
(6) squall84, конфигурацию рекомендуется перевести на управляемые блокировки в любом случае http://www.gilev.ru/ctrllock/ у фирмы 1С в типовые решения на управляемых блокировках
(21) Gilev.Vyacheslav, но некоторые переведены чисто формально - поменяли режим и забили :) - это в сторону УПП камушек
(22) Gilev.Vyacheslav, вот кстати да, неплохой сервис.
(24) nickpugachev, не совсем понял, что Вы имеете в виду касательно УПП. Текущая версия полностью работает в управляемом режиме, и на проблемы, связанные с несоблюдением формального repeatable_read (к примеру, списание в минус при параллельном проведении) никто не жалуется.
(25) asved.ru, repeatable read 1с нигде и не обещало :), они read commited сломали и то ничего (в 8.3 вроде починили, не проверял)
а вот блокировки в старых документах расставлять никто не стал
repeatable read 1с нигде и не обещало :), они read commited сломали и то ничего (в 8.3 вроде починили, не проверял)
Ничего не понял.
блокировки в старых документах расставлять никто не стал
А они там нужны вообще?
Грубо говоря, специальная установка управляемой блокировки требуется только если сначала идет чтение данных, влияющих на транзакцию, а потом - запись изменяемых транзакцией данных. Транзакция на SQL выполняется в read_committed, т.е. на уровне SQL никто не гарантирует, что данные останутся неизменными (к примеру, остаток товара достаточным) до завершения транзакции. Это минус, зато плюс в сильном снижении объема удерживаемых блокировок.
Вот как раз проблему вышеуказанного минуса и решает ручное наложение управляемой блокировки. Она отрабатывает не на уровне СУБД, а на уровне платформы, и проверяется первой.
Если же мы сначала пишем данные, а потом их проверяем и при нужде откатываем запись, то все необходимые блокировки будут наложены системой самостоятельно.
repeatable read - это тоже уровень изоляции транзакций, причем более жесткий, чем read commited :) - его 1с и не обещала никому. а вот про read commited пишет везде и всюду. а теперь вопрос на засыпку - при уровне изоляции read commited возможно грязное чтение?
Грубо говоря, специальная установка управляемой блокировки требуется только если сначала идет чтение данных, влияющих на транзакцию
я именно про это - не нашел блокировок при партионном списании. может искал плохо? блокировки в УПП ставятся, но далеко не везде, где должны бы быть.
И тем не менее, в режиме автоматических блокировок он напропалую используется, что собственно и является основной проблемой этого режима.
а вот про read commited пишет везде и всюду
Естественно, как принципиальное отличие режима управляемых блокировок от автоматических, позволяющее существенно повысить параллельность.
а теперь вопрос на засыпку - при уровне изоляции ТРАНЗАКЦИИ read commited возможно грязное чтение?
Ответ на засыпку: легко воспроизводимая ситуация грязного чтения в 1С проявляется только вне транзакции. Т.е. вы можете получить грязные данные в отчете, но никогда их не получите в транзакции обработки проведения. И то если у нас нет snapshot_isolation в параметрах базы, т.е. если она не работает как версионник - иначе и в отчете грязные данные получить не выйдет.
Так что никто ничего не ломал, вы просто забыли про слово, выделенное жирным в вашем вопросе.
И тем не менее, в режиме автоматических блокировок он напропалую используется
там используется serializable - он несколько слабее repeatable read - блокировка ставится только если в запросе было специально указано, что данные могут измениться
Ответ на засыпку: легко воспроизводимая ситуация грязного чтения в 1С проявляется только вне транзакции
для начала - любой запрос в sql выполняется в транзакции :), явно или неявно она выставлена - не имеет значения. также можете поэкспериментировать с чтением данных в транзакции 1с - результат от этого не изменится, грязь останется. 1с так же точно пихает хинт with (nolock) в запросы.
Оно не контролируется, просто партионный учет при нужде восстанавливается
тому, кто это придумал, надо постучать по одному месту :) - восстановите партионный учет по десятку тысяч документов при сервисном окне в час-два
(31) squall84, сначала на тестовой - не всегда это нормально работает
там используется serializable - он несколько слабее repeatable read - блокировка ставится только если в запросе было специально указано, что данные могут измениться
Щито? RTFM.
любой запрос в sql выполняется в транзакции
Щито? RTFM.
тому, кто это придумал, надо постучать по одному месту
Тот, кто это придумал, в отличие от Вас, представляет себе механику работы блокировок и последствия предварительно блокируемого списания для многопользовательской работы.
(33) asved.ru, про RTFM - я могу вам посоветовать сделать это как минимум по второму пункту :), по первому - да, ошибся слегка, rr фантома вернуть может в отличие от s.
по второму - я не замечал за 1с выполнения set implicit_transactions, а без него - будет транзакция с уровнем изоляции, заданным в подключении, по умолчанию - read commited
вот как раз хинты with(nolock) в запросах 1с и убирают влияние этого режима изоляции, позволяя получить грязь
Тот, кто это придумал, в отличие от Вас, представляет себе механику работы блокировок и последствия предварительно блокируемого списания для многопользовательской работы.
дядьку, я вижу это понимание на базе с 900 пользователями. и на базе с десятком тысяч документов в день. и еще на нескольких больших/нагруженных базах.
будет транзакция с уровнем изоляции, заданным в подключении, по умолчанию - read commited
В трассировке покажете? только не на tempdb ;)
Откройте уже учебник и почитайте определение транзакции. Ключевые слова - "из одного связного состояния в другое связное состояние".
Хинт with nolock разрешает грязное чтение по отношению к другим транзакциям, выполняющимся в системе, и его применение закономерно, поскольку в реальных условиях редко кому нужны, к примеру, остатки на "прям щас" вне транзакции, а кому нужны - никто не мешает транзакцию открыть. Как следствие, пользователи, формирующие отчеты и проводящие документы друг друга не ждут.
Что же касается УПП, если бы мы ставили блокировку перед чтением партий, то объем и длительность блокировок по операции списания партий ничем бы не отличались от автоматического режима. Видимо, в 1С решили огород не городить, поскольку партии можно пересчитать регламентным заданием и вообще есть РАУЗ, а кто ведет партионный учет - сами себе злобные буратины.
(37) asved.ru, мне кажется nickpugachev путает транзакцию и управление транзакцией.
set implicit_transactions это указание выполнение транзакции в режиме неявных транзакций, платформа 1С же использует режим явных транзакций. При любых модификациях данных или при явном указании на языке 1С НачатьТранзакцию(), платформа шлет BEGIN TRAN.
(39) nickpugachev, ну так о чем и речь
платформа всегда шлет явные команды BEGIN TRANSACTION и т.д
опять же set implicit_transactions ни ON ни OFF нигде не фигурирует в транслируемых запросах, соответственно режим автоматической фиксации и неявных транзакцией не используется.
Выполните простой запрос 1С из консоли, и DBCC OPENTRAN и вы увидите что транзакция не начинается.
(28) nickpugachev, PS потыкал палочкой в списание партий. Оно не контролируется, просто партионный учет при нужде восстанавливается, а какие конкретно партии были списаны конкретным документом - на момент списания не важно, оно важно только при расчете себестоимости, если, конечно, у вас не РАУЗ.
(31) squall84, можете, но при этом скорее всего что-то потеряете в бизнес-логике.
Вкратце можно сказать, что без изменений можно переводить документы, делающие движения прихода, расхода без нужды проверки остатков или проверяющие остатки после записи движений. В документах, проверяющих остатки перед записью движений, следует установить эксклюзивные блокировки перед чтением остатков. Также обратите внимание на запросы, в которых встречается директива "ДЛЯ ИЗМЕНЕНИЯ" - сохранение существующей логики также требует ручного наложения управляемых блокировок.
(6) squall84, "блокировки у нас автоматические, т.к. пока не нашел ни одной толковой инструкции, как переписать блокировки"
Я думаю готовой инструкции никто писать и не будет. Тема мало кому интересна. Даже на инфостарте не с кем обсудить тему.
Попытки обсуждения тем производительности превращаются в меряние "у кого длиннее" :-)
Что-то ничего не помогает. Никаких видимых причин. Подскажите, может какие-то регистры надо чистить? или еще какие-то действия проводить? пересчет итогов и тест с исправлением я делаю регулярно. Правда версионирование включено с февраля по всем документам. Может оно влиять на скорость работы самой 1с?
(12) nickpugachev,
Кинул файлик. но как я понял, самые-самые запросы у нас по базе ТЦУ. Она лежит на отдельном сасовском диске.
На серваке SQL ничего не крутится. Сегодня создал РАМ диск и переложил на него tempDB (до этого был на диске С, там тоже сас диски).
Так же значение lock_timeout у меня -1. Моэет это каким либо образом влиять на блокировки? В одной книге прочитал, что значение -1 заставляет инструкцию ждать всегда. В другой, что это значение означает, что sql не будет ждать вообще.
lock_timeout в -1 - ожидание всегда, чтобы вообще не ждало - 0 должен быть. и -1 - это значение по умолчанию.
ну и еще пару запросиков для лучшего понимания
1. статистика по чтению/записи по файлам баз
USE master
go
SET NOCOUNT ON
DECLARE @crDate DateTime;
DECLARE @hours DECIMAL(18,3), @Days int;
DECLARE @FinalHours int, @FinalMinutes int, @FinalSeconds int, @total_seconds int;
-- Determine uptime by checking Tempdb creation datetime:
SEL ECT top 1 @crdate=create_date FR OM sys.databases WH ERE NAME='tempdb'
SEL ECT @hours = DATEDIFF(ss,@crDate,GETDATE())/CAST(60 AS Decimal)/CAST(60 AS Decimal);
PRINT 'SQL Server instance '+ @@SERVERNAME + '\' + @@SERVICENAME + ' is Up since: ' + CAST (@crdate as varchar) ;
-- From hours to days:
SEL ECT @Days = @hours/CAST(24 AS Decimal);
-- Determine the remaining part of the hours:
SELECT @FinalHours = @hours - (@Days*CAST(24 AS Decimal))
-- Remaining minutes:
SELECT @FinalMinutes = (@hours - (@Days*CAST(24 AS Decimal)) - @FinalHours ) * 60;
-- Remaining seconds:
SELECT @FinalSeconds = (((@hours - (@Days*CAST(24 AS Decimal)) - @FinalHours ) * 60) - @Finalminutes) * 60;
PRINT 'Or: '+ CAST(@Days as varchar) + ' Days, ' + CAST(@FinalHours as varchar) + ' Hours,'
+ CAST(@FinalMinutes as varchar) + ' Minutes and ' + CAST(@FinalSeconds as varchar) + ' Seconds.'
SELECT @total_seconds = (CAST(@Days AS decimal(12,2))*24*3600 + CAST(@Finalhours AS decimal(12,2))*3600 + CAST(@Finalminutes AS decimal(12,2))*60 )
+ CAST(@Finalseconds AS decimal(12,2))
PRINT 'Total uptime in seconds: '+ CONVERT(VARCHAR(20) ,@total_seconds )
SELECT @@SERVERNAME as Hostname, @@SERVICENAME as Instancename, @crdate AS SQL_Start_Date_Time , @total_seconds as TotalSeconds_Up
SELECT DB_NAME(database_id) AS [Database Name] ,
file_id ,
io_stall_read_ms ,
num_of_reads ,
(num_of_bytes_read / 1024 / 1024 /1024) as GB_Read_Total,
num_of_bytes_read / @total_seconds * 3600 * 24 /1024/1024/1024 as AVG_GB_read_per_day_ESTIMATE,
CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1))
AS [avg_read_stall_ms] ,
io_stall_write_ms ,
num_of_writes ,
num_of_bytes_written / 1024 / 1024/1024 as GB_Written_Total,
num_of_bytes_written /@total_seconds * 3600 * 24 /1024/1024/1024 as AVG_GB_Written_per_day_ESTIMATE,
CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1))
AS [avg_write_stall_ms] ,
io_stall_read_ms + io_stall_write_ms AS [IO_Stalls] ,
num_of_reads + num_of_writes AS [Total_IO] ,
CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads
+ num_of_writes)
AS NUMERIC(10,1)) AS [AVG_IO_stall_ms]
FR OM sys.dm_io_virtual_file_stats(NULL, NULL)
--ORDER BY avg_io_stall_ms DESC ;
order by GB_read_total DESC
Показать
2. логические чтения/записи по базам
SELECT TOP 10
[Total Reads] = SUM(total_logical_reads)
,[Execution count] = SUM(qs.execution_count)
,DatabaseName = DB_NAME(qt.dbid)
FR OM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Total Reads] DESC;
SELECT TOP 10
[Total Writes] = SUM(total_logical_writes)
,[Execution count] = SUM(qs.execution_count)
,DatabaseName = DB_NAME(qt.dbid)
FR OM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Total Writes] DESC;
В файлике результат запроса. Кстати после перезагрузки серверов sql и 1c и переноса tempDB на РАМ ситуация немного улучшилась, но возможно к вечеру будет тоже.
Судя по всему по этим запросам, которые вы даете, можно составить примерную картину использования баз sql, но нигде так и не нашел путевой инструкции по полному анализу sql сервера. -(
вот рестарт сервера перед запросом был несколько зря :) - DMV показывают данные только с последнего старта сервера, лучше повторить их хотя бы завтра для более правильных данных
Но, в общем, даже по такой статистике можно сказать, что у вас очень нагруженная tempdb, соответственно ее лучше на ssd или в ram (но вот в ram может оказаться не самым лучшим решением - часто лучше отдать оперативку sql)
можете посмотреть по таблице, которую вернул первый запрос из (14) - в колонке avg_read_stall_ms - среднее время на чтение, avg_write_stall_ms - среднее время выполнения записи - смотрите на них и соответственно перемещайте файлы. file_id 1 - это mdf, 2 - ldf
а путевая инструкция - как всегда - SQL Books Online, в данном случае - раздел Dynamic Management Views
(19) mpudy, Почитаю, спасибо.
(22) Gilev.Vyacheslav, Я у вас зарегистрирован и не единожды пытался подключиться к разным сервисам (пробовал по блокировкам и по длинным запросам) - ничего не получилось. добавил файлик с настройкой прокси к 1с, но пр проверке я не могу связаться с веб-сервисом. Пишу сетевые имена \\0.0.0.0\log и \\0.0.0.0\conf с полными правами для всех. но, судя по всему всё-равно ничего не вижу. Пробовал локально на компьютере - Не создается файлик с настройками журнала. В общем неудачная попытка. -( А по поводу блокировок сейчас читаю=скорее всего будем переходить на них.
А то у нас иногда работают в базе 3 человека - проводят реализации товаров и через раз получают таймаут блокировок.