Locking: PeopleSoft на SQL Server
-
- Новичок
- Posts: 84
- Joined: 24 Jul 2002 20:42
- Location: Chicago
Locking: PeopleSoft на SQL Server
Имеем PeopleSoft на SQL Сервере. Когда одновременно гоняются reports и транзакции то происходит постоянный blocking и иногда даже deadlocks. Понятно что вызванно это shared locks на таблицах где гоняется репорт, но как этого избежать? Кто нибудь сталкивался с этой проблемой и как ее решить?
-
- Уже с Приветом
- Posts: 28294
- Joined: 29 Aug 2000 09:01
- Location: SPB --> Gloucester, MA, US --> SPB --> Paris
Наверное это в IT технологии
По делу
Так как переделать код PeopleSoft генерящий запросы вы не можете то похоже остается кардинальное решение
Поставить второй сервер
Превратить его в disaster recovery server
Отливать на него копию базы
Строить отчеты на этом сервере. Если есть OLAP, То его туда же
В принципе можно делать все и на одном сервере
Если такое подойдет то могу написать про детали
Есть еще трюкаческое решение... Расскажите про систему отчетов - как они описываются, как строются... Можно ли в них "влезть" ? Можно ли отчеты запускать от имени какого либо пользователя ?
В отчетах делается ли select ... from ИмяТаблицы
или
select ... from dbo.ИмяТаблицы (вместо dbo может быть какой нибудь другой пользователь)
По делу
Так как переделать код PeopleSoft генерящий запросы вы не можете то похоже остается кардинальное решение
Поставить второй сервер
Превратить его в disaster recovery server
Отливать на него копию базы
Строить отчеты на этом сервере. Если есть OLAP, То его туда же
В принципе можно делать все и на одном сервере
Если такое подойдет то могу написать про детали
Есть еще трюкаческое решение... Расскажите про систему отчетов - как они описываются, как строются... Можно ли в них "влезть" ? Можно ли отчеты запускать от имени какого либо пользователя ?
В отчетах делается ли select ... from ИмяТаблицы
или
select ... from dbo.ИмяТаблицы (вместо dbo может быть какой нибудь другой пользователь)
Зарегистрированный нацпредатель, удостоверение N 19719876044787 от 22.09.2014
-
- Новичок
- Posts: 84
- Joined: 24 Jul 2002 20:42
- Location: Chicago
Идея со вторым сервером не годится так как я не могу гарантировать real-time updates. Пользователи требуют самую свежую инфомацию, максимум 5 минут старше чем база с транзакциями.
Отчеты создаются консультантами через утилиты, но написаны они в SQR, так что менять их мы можем.
Я также пытался уговорить пользователей позволить dirty reads, но это тоже запретили, официальный ответ от PeopleSoft это только Read Committed.
отчеты выглядят как select * from dbo.table
Отчеты создаются консультантами через утилиты, но написаны они в SQR, так что менять их мы можем.
Я также пытался уговорить пользователей позволить dirty reads, но это тоже запретили, официальный ответ от PeopleSoft это только Read Committed.
отчеты выглядят как select * from dbo.table
-
- Уже с Приветом
- Posts: 15311
- Joined: 30 Apr 2003 16:43
-
- Новичок
- Posts: 84
- Joined: 24 Jul 2002 20:42
- Location: Chicago
-
- Уже с Приветом
- Posts: 15311
- Joined: 30 Apr 2003 16:43
-
- Новичок
- Posts: 84
- Joined: 24 Jul 2002 20:42
- Location: Chicago
-
- Уже с Приветом
- Posts: 28294
- Joined: 29 Aug 2000 09:01
- Location: SPB --> Gloucester, MA, US --> SPB --> Paris
-
- Уже с Приветом
- Posts: 15311
- Joined: 30 Apr 2003 16:43
Kon, вспоминая недавние дискусии на подобную тему, я бы сказал что в Оракл-е проблемы будут выглядеть иначе. В вашем случае самая вероятная проблема была бы проблема связанная с тем что результирущее множество запроса для репорта может быть модифицированно онлайн транзакциями. И единственным выходом из этой проблемы могло бы остаться использование уровня изоляции SERIALIZABLE. И все бы вернулось на те же круги. Если не хуже.
А к DB2 у вас как относятся?
А к DB2 у вас как относятся?
-
- Уже с Приветом
- Posts: 1928
- Joined: 26 Oct 2000 09:01
- Location: Ярославль - Канзас Сити
-
- Уже с Приветом
- Posts: 1928
- Joined: 26 Oct 2000 09:01
- Location: Ярославль - Канзас Сити
Dmitry67 wrote:Да, многие возможности отпали
Вопросы
1. А сколько таблиц проблемные ?
2. Можете ли вы менять таблицы ? Типа добавить новое поле ?
P.S
Интересно а что говорит сам support PeopleSoft ?
Дима если PeopleSoft implementation близка к ваниле, то не один прожект манаджер не пойдет на то чтобы модифицировать таблицы , unless там какие-то баги например с key structure. И в 90% случаев ( по крайне мере у нас ) подобные баги просто постят в PeopleSoft и ждут пока там починят.
Что говорит PeopleSoft действительно интересно. Вы у них case по этому поводу открыли ?
-
- Новичок
- Posts: 84
- Joined: 24 Jul 2002 20:42
- Location: Chicago
Только что закончил телефонную конференцию с MS и PeopleSoft.
Каждый катит бочки друг на друга.
Окончательный ответ это что мне надо ещё лучше настроить сервер что бы
сократить время транзакции и соответственно сократить жизнь locka. Таблицы я менять
не могу, так как я не смогу upgrade в будущем. Eдинственное что я могу менять это индеkсы.
PeopleSoft предложил поставить побольше индеkсoв, МС сразу предложил не забывать что у меня смешанная, OLTP/reporting, база и
что много индехов это плохо. Таблиц у меня кстати 40000, всего 120000 обектов, правда реально используется только ~200.
МС также сказал что 4 недели назад Хилтон Хотелс поставил себе PeopleSoft на SQL Сервер и что у них нет никаких проблем несмотря на то что у них тоже mixed environment.
Каждый катит бочки друг на друга.
Окончательный ответ это что мне надо ещё лучше настроить сервер что бы
сократить время транзакции и соответственно сократить жизнь locka. Таблицы я менять
не могу, так как я не смогу upgrade в будущем. Eдинственное что я могу менять это индеkсы.
PeopleSoft предложил поставить побольше индеkсoв, МС сразу предложил не забывать что у меня смешанная, OLTP/reporting, база и
что много индехов это плохо. Таблиц у меня кстати 40000, всего 120000 обектов, правда реально используется только ~200.
МС также сказал что 4 недели назад Хилтон Хотелс поставил себе PeopleSoft на SQL Сервер и что у них нет никаких проблем несмотря на то что у них тоже mixed environment.
-
- Уже с Приветом
- Posts: 15311
- Joined: 30 Apr 2003 16:43
Kon, I would still recommend to get closer look at replication. You say: "Replication wouldn't work, since the reports would hold the same shared locks as they do in the OLTP right now. Replication agent will sit..."
Replication agent, but not transactions! Transactions will run without contentions with reporting ".... and wait for the locks to be released. This can be very quick, but I can not guarantee that the latency will be under 5 minutes."
Any solutions, but dirty read, will give you unpredictable latency. Even though you say Oracle doesn't have such problems (locking problems), actually in case of Oracle you have the same latency because your report query will run over "freezed" (at a time when report starts) data. Look, Oracle with it's versionig is quite similar to what replication gives us here. Remember, transactions ("writers") do not block "readers", and "readers" do not block "writers", right?
Replication agent, but not transactions! Transactions will run without contentions with reporting ".... and wait for the locks to be released. This can be very quick, but I can not guarantee that the latency will be under 5 minutes."
Any solutions, but dirty read, will give you unpredictable latency. Even though you say Oracle doesn't have such problems (locking problems), actually in case of Oracle you have the same latency because your report query will run over "freezed" (at a time when report starts) data. Look, Oracle with it's versionig is quite similar to what replication gives us here. Remember, transactions ("writers") do not block "readers", and "readers" do not block "writers", right?
-
- Уже с Приветом
- Posts: 28294
- Joined: 29 Aug 2000 09:01
- Location: SPB --> Gloucester, MA, US --> SPB --> Paris
Репликация в MS SQL довольно отстойная и наверняка потребует модификации таблиц...
По поводу индексов я бы подумал
Вот запустите такой процесс
Создайте таблицу
Сделайте тест во время которого запустите процедуру LockWatcher
Потом остановите ее
Запустите LockRanges
Она покажет кто и кого и сколько ждал
Копайте подробнее по найденным таблицам
По поводу индексов я бы подумал
Вот запустите такой процесс
Создайте таблицу
Сделайте тест во время которого запустите процедуру LockWatcher
Потом остановите ее
Запустите LockRanges
Она покажет кто и кого и сколько ждал
Копайте подробнее по найденным таблицам
Code: Select all
CREATE TABLE ProfilerLockLog (
spid smallint NULL ,
dbid smallint NOT NULL ,
ObjId int NOT NULL ,
IndId smallint NOT NULL ,
Type nvarchar (4) NULL ,
Resource nvarchar (16) NULL ,
Mode nvarchar (8) NULL ,
Status nvarchar (5) NULL ,
name varchar (128) NULL ,
temps datetime NULL
)
GO
create procedure LockWatcher
as
set nocount on
create table #SpecStatistic (
spid smallint NULL ,
dbid smallint NOT NULL ,
ObjId int NOT NULL ,
IndId smallint] NOT NULL ,
Type varchar (4) ,
Resource varchar (16) ,
Mode varchar (8) NULL,
Status varchar (5) NULL )
loop:
truncate table #SpecStatistic
insert into #SpecStatistic (spid,dbid,objid,indid,type,resource,mode,status)
select convert (smallint, req_spid) As spid,
rsc_dbid As dbid,
rsc_objid As ObjId,
rsc_indid As IndId,
substring (v.name, 1, 4) As Type,
substring (rsc_text, 1, 16) as Resource,
substring (u.name, 1, 8) As Mode,
substring (x.name, 1, 5) As Status
from master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u
where master.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR'
and master.dbo.syslockinfo.req_status = x.number
and x.type = 'LS'
and master.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L'
and x.name<>'GRANT'
order by spid
-- tempdb
insert into ProfilerLockLog (spid,dbid,objid,indid,type,resource,mode,status,name,temps)
select spid,dbid,objid,indid,S.type,resource,mode,S.status,O.name,getdate()
from #SpecStatistic S, tempdb.dbo.sysobjects O
where ObjId>0 and dbid=2 and id=ObjId and O.name not like '%SpecStatistic%'
-- this db
insert into ProfilerLockLog (spid,dbid,objid,indid,type,resource,mode,status,name,temps)
select spid,dbid,objid,indid,S.type,resource,mode,S.status,O.name,getdate()
from #SpecStatistic S, sysobjects O
where ObjId>0 and dbid=db_id() and id=ObjId
waitfor delay '00:00:00.010'
goto loop
GO
create procedure LockRanges
as
CREATE TABLE #id (
n int identity,
spid smallint NULL ,
ObjId int NOT NULL ,
IndId smallint NOT NULL ,
Type nvarchar (4) NULL ,
Resource nvarchar (16) NULL ,
Mode nvarchar (8) NULL ,
name varchar (128) NULL ,
temps datetime NULL
)
CREATE TABLE #id2 (
n int identity,
spid smallint NULL ,
ObjId int NOT NULL ,
IndId smallint NOT NULL ,
Type nvarchar (4) NULL ,
Resource nvarchar (16) NULL ,
Mode nvarchar (8) NULL ,
name varchar (128) NULL ,
tbeg datetime NULL,
tend datetime NULL,
ntoset int NULL
)
insert into #id (spid,ObjId,IndId,Type,Resource,Mode,name,temps)
select spid,ObjId,IndId,Type,Resource,Mode,name,temps from ProfilerLockLog order by spid,temps
delete from #id where
exists(select * from #id N where
N.spid=#id.spid and N.ObjId=#id.ObjId and N.Type=#id.Type and N.Resource=#id.Resource and N.Mode=#id.Mode
and N.n=#id.n+1)
and
exists(select * from #id N where
N.spid=#id.spid and N.ObjId=#id.ObjId and N.Type=#id.Type and N.Resource=#id.Resource and N.Mode=#id.Mode
and N.n=#id.n-1)
insert into #id2 (spid,ObjId,IndId,Type,Resource,Mode,name,tbeg)
select spid,ObjId,IndId,Type,Resource,Mode,name,temps from #id order by spid,n
update #id2 set tend=(select tbeg from #id2 N where
N.spid=#id2.spid and N.ObjId=#id2.ObjId and N.Type=#id2.Type and N.Resource=#id2.Resource and N.Mode=#id2.Mode
and N.n=#id2.n+1)
delete from #id2 where tend is NULL
select name,spid,indId,Type,Resource,Mode,tbeg,tend,datediff(ms,tbeg,tend) as Dur_ms from #id2
GO
Зарегистрированный нацпредатель, удостоверение N 19719876044787 от 22.09.2014
-
- Новичок
- Posts: 84
- Joined: 24 Jul 2002 20:42
- Location: Chicago
Поговорил с Хилтоном. У них были те же проблемы что и у меня. После долгих проб и ошибок они решили проблему.
1) Включить флаг 1211 что бы выключить lock escalation.. Это создает добавочные проблемы с памятью и можно упереться в максимум дозволенных locks. Но это меньшее из зол. Ещё можно добавить /ЗGB
параметер в boot.ini что бы дать серверу больше памяти для оптимизатора и locks.
2) Создать добавочные файлы для tempdb. Количество файлов =2 х количество CPU. Все файлы должны быть
одного размера. Это поможет избежать waits в tempdb связанных сo cмешанными extents.
3) Выставить Network Packet size = 8192
4) Выставить Max Degree of Parallelism = 1
5) Также они предложили форматировать мой диск с 64К allocation unit size, что бы оно равнялось размеру extenta в SQL Servere.
Но мне кажется что это излишне.
1) Включить флаг 1211 что бы выключить lock escalation.. Это создает добавочные проблемы с памятью и можно упереться в максимум дозволенных locks. Но это меньшее из зол. Ещё можно добавить /ЗGB
параметер в boot.ini что бы дать серверу больше памяти для оптимизатора и locks.
2) Создать добавочные файлы для tempdb. Количество файлов =2 х количество CPU. Все файлы должны быть
одного размера. Это поможет избежать waits в tempdb связанных сo cмешанными extents.
3) Выставить Network Packet size = 8192
4) Выставить Max Degree of Parallelism = 1
5) Также они предложили форматировать мой диск с 64К allocation unit size, что бы оно равнялось размеру extenta в SQL Servere.
Но мне кажется что это излишне.
-
- Уже с Приветом
- Posts: 11019
- Joined: 15 May 2002 02:09
- Location: Boston, MA
Kon wrote:1) Включить флаг 1211 что бы выключить lock escalation.. Это создает добавочные проблемы с памятью и можно упереться в максимум дозволенных locks. Но это меньшее из зол. Ещё можно добавить /ЗGB
параметер в boot.ini что бы дать серверу больше памяти для оптимизатора и locks.
Kon, что такое boot.ini ?
-
- Уже с Приветом
- Posts: 1928
- Joined: 26 Oct 2000 09:01
- Location: Ярославль - Канзас Сити
ie wrote:Kon wrote:1) Включить флаг 1211 что бы выключить lock escalation.. Это создает добавочные проблемы с памятью и можно упереться в максимум дозволенных locks. Но это меньшее из зол. Ещё можно добавить /ЗGB
параметер в boot.ini что бы дать серверу больше памяти для оптимизатора и locks.
Kon, что такое boot.ini ?
Это файл ie, это файл ..
-
- Уже с Приветом
- Posts: 11019
- Joined: 15 May 2002 02:09
- Location: Boston, MA
-
- Уже с Приветом
- Posts: 1928
- Joined: 26 Oct 2000 09:01
- Location: Ярославль - Канзас Сити