Оптимизация нагрузки при использовании
Quartz
Продвинутый
Скачать
Версия 1.9 
Симптомы
Quartz создает большую нагрузку на базу данных.
Решение
- Для решения проблемы в первую очередь рекомендуется создать пользовательские планировщики, чтобы распределить нагрузку между ними.
- После чего выполнить перенос таблиц, работающих с Quartz, в отдельную таблицу в рамках одной базы данных.
- Если существуют ограничения в ресурсах основной базы данных, рекомендуется выполнить перенос таблиц, работающих с Quartz, в отдельную базу данных.
Это дает следующие преимущества:
- Изоляцию блокировок: каждый планировщик работает со своими данными;
- Повышение производительности: каждый планировщик сканирует только свои записи;
- Безопасность: очистка одного планировщика не влияет на другие.
Перенос таблицы Quartz в рамках одной базы данных
Для перевода планировщика на другие таблицы в рамках одной базы данных выполните следующие шаги:
- Создайте новые таблицы для Quartz по аналогии с существующими (начинаются с qrtz_), но с собственным префиксом (например, custom_qrtz_: custom_qrtz_triggers и т. д.). Скрипты создания таблиц представлены в разделе Ресурсы.
- В блоке quartz для конкретного планировщика добавьте настройку для указания префикса таблицы:
<add key="quartz.jobStore.tablePrefix" value="custom_qrtz_" />
Перенос таблицы Quartz в отдельную базу данных
Для переноса таблицы выполните следующие шаги:
- В файле ConnectionStrings.config добавьте новую строку с названием, например, quartzDb:
<add name="quartzDb" connectionString="Server=..."/>
- В конфигурационном файле Web.config, который лежит в корневом каталоге приложения, в секции quartz, укажите новое название в параметре quartz.dataSource.SchedulerDb.connectionStringName:
<add key="quartz.dataSource.SchedulerDb.connectionStringName" value="quartzDb" />
Примечание. Значение параметра должно полностью совпадать с именем атрибута, заданным в шаге 1.
- Изменения нужно внести на всех экземплярах, чтобы сохранить кластерный режим.
- В базе данных, куда вы планируете перенаправить Quartz, должна быть идентичная основному приложению структура таблиц.
- Чтобы сохранить запланированные задания, необходимо заполнить таблицы данными.
Если потребуется перенос данных, рекомендуется следующий план действий:
- Остановите сайт и пул приложения BPMSoft.
- Создайте новую базу данных для Quartz и перенесите туда структуру и данные (можно просто скопировать основную базу данных сайта).
- Внесите изменения в файлы ConnectionStrings.config и Web.config сайта.
- Запустите сайт и пул приложения BPMSoft. Проверьте, что плановые задания выполняются корректно.
Если планировщик не запускается с первого раза и в логах нет ошибок, попробуйте перезайти под пользователем Supervisor:
- Войдите в систему под пользователем Supervisor.
- Нажмите кнопку «Выход».
- Повторно войдите в систему под пользователем Supervisor.
Ресурсы
Скрипт для Microsoft SQL Server
SET NOCOUNT ON;
SET XACT_ABORT ON;
GO
DECLARE
@schema sysname = N'dbo',
@prefix sysname = N'QRTZ_',
@useVarbinaryMax bit = 0;
DECLARE @blob nvarchar(20) = CASE WHEN @useVarbinaryMax = 1
THEN N'VARBINARY(MAX)' ELSE N'IMAGE' END;
DECLARE @sql nvarchar(max);
DECLARE @tbl sysname;
DECLARE @full nvarchar(400);
DECLARE @msg nvarchar(max);
IF @prefix IS NULL OR @prefix = N''
BEGIN
THROW 50000, N'@prefix must not be empty', 1;
END;
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = @schema)
BEGIN
SET @msg = N'Schema "' + @schema + N'" does not exist in database "'
+ DB_NAME() + N'"';
THROW 50000, @msg, 1;
END;
PRINT N'Quartz tables: database=' + DB_NAME()
+ N', schema=' + @schema + N', prefix=' + @prefix
+ N', blob type=' + @blob;
SET @tbl = @prefix + N'JOB_DETAILS';
SET @full = QUOTENAME(@schema) + N'.' + QUOTENAME(@tbl);
IF OBJECT_ID(@full, N'U') IS NULL
BEGIN
SET @sql = N'CREATE TABLE ' + @full + N' (
[SCHED_NAME] NVARCHAR(100) NOT NULL,
[JOB_NAME] NVARCHAR(150) NOT NULL,
[JOB_GROUP] NVARCHAR(150) NOT NULL,
[DESCRIPTION] NVARCHAR(250) NULL,
[JOB_CLASS_NAME] NVARCHAR(250) NOT NULL,
[IS_DURABLE] BIT NOT NULL,
[IS_NONCONCURRENT] BIT NOT NULL,
[IS_UPDATE_DATA] BIT NOT NULL,
[REQUESTS_RECOVERY] BIT NOT NULL,
[JOB_DATA] ' + @blob + N' NULL,
CONSTRAINT ' + QUOTENAME(N'PK_' + @tbl) + N' PRIMARY KEY CLUSTERED
([SCHED_NAME], [JOB_NAME], [JOB_GROUP])
)';
EXEC sys.sp_executesql @sql;
PRINT N' created ' + @tbl;
END;
SET @tbl = @prefix + N'TRIGGERS';
SET @full = QUOTENAME(@schema) + N'.' + QUOTENAME(@tbl);
IF OBJECT_ID(@full, N'U') IS NULL
BEGIN
SET @sql = N'CREATE TABLE ' + @full + N' (
[SCHED_NAME] NVARCHAR(100) NOT NULL,
[TRIGGER_NAME] NVARCHAR(150) NOT NULL,
[TRIGGER_GROUP] NVARCHAR(150) NOT NULL,
[JOB_NAME] NVARCHAR(150) NOT NULL,
[JOB_GROUP] NVARCHAR(150) NOT NULL,
[DESCRIPTION] NVARCHAR(250) NULL,
[NEXT_FIRE_TIME] BIGINT NULL,
[PREV_FIRE_TIME] BIGINT NULL,
[PRIORITY] INTEGER NULL,
[TRIGGER_STATE] NVARCHAR(16) NOT NULL,
[TRIGGER_TYPE] NVARCHAR(8) NOT NULL,
[START_TIME] BIGINT NOT NULL,
[END_TIME] BIGINT NULL,
[CALENDAR_NAME] NVARCHAR(200) NULL,
[MISFIRE_INSTR] INTEGER NULL,
[JOB_DATA] ' + @blob + N' NULL,
CONSTRAINT ' + QUOTENAME(N'PK_' + @tbl) + N' PRIMARY KEY CLUSTERED
([SCHED_NAME], [TRIGGER_NAME], [TRIGGER_GROUP]),
CONSTRAINT ' + QUOTENAME(N'FK_' + @tbl + N'_' + @prefix + N'JOB_DETAILS')
+ N' FOREIGN KEY ([SCHED_NAME], [JOB_NAME], [JOB_GROUP])
REFERENCES ' + QUOTENAME(@schema) + N'.'
+ QUOTENAME(@prefix + N'JOB_DETAILS')
+ N' ([SCHED_NAME], [JOB_NAME], [JOB_GROUP])
)';
EXEC sys.sp_executesql @sql;
PRINT N' created ' + @tbl;
END;
SET @tbl = @prefix + N'SIMPLE_TRIGGERS';
SET @full = QUOTENAME(@schema) + N'.' + QUOTENAME(@tbl);
IF OBJECT_ID(@full, N'U') IS NULL
BEGIN
SET @sql = N'CREATE TABLE ' + @full + N' (
[SCHED_NAME] NVARCHAR(100) NOT NULL,
[TRIGGER_NAME] NVARCHAR(150) NOT NULL,
[TRIGGER_GROUP] NVARCHAR(150) NOT NULL,
[REPEAT_COUNT] INTEGER NOT NULL,
[REPEAT_INTERVAL] BIGINT NOT NULL,
[TIMES_TRIGGERED] INTEGER NOT NULL,
CONSTRAINT ' + QUOTENAME(N'PK_' + @tbl) + N' PRIMARY KEY CLUSTERED
([SCHED_NAME], [TRIGGER_NAME], [TRIGGER_GROUP]),
CONSTRAINT ' + QUOTENAME(N'FK_' + @tbl + N'_' + @prefix + N'TRIGGERS')
+ N' FOREIGN KEY ([SCHED_NAME], [TRIGGER_NAME], [TRIGGER_GROUP])
REFERENCES ' + QUOTENAME(@schema) + N'.'
+ QUOTENAME(@prefix + N'TRIGGERS')
+ N' ([SCHED_NAME], [TRIGGER_NAME], [TRIGGER_GROUP])
ON DELETE CASCADE
)';
EXEC sys.sp_executesql @sql;
PRINT N' created ' + @tbl;
END;
SET @tbl = @prefix + N'SIMPROP_TRIGGERS';
SET @full = QUOTENAME(@schema) + N'.' + QUOTENAME(@tbl);
IF OBJECT_ID(@full, N'U') IS NULL
BEGIN
SET @sql = N'CREATE TABLE ' + @full + N' (
[SCHED_NAME] NVARCHAR(100) NOT NULL,
[TRIGGER_NAME] NVARCHAR(150) NOT NULL,
[TRIGGER_GROUP] NVARCHAR(150) NOT NULL,
[STR_PROP_1] NVARCHAR(512) NULL,
[STR_PROP_2] NVARCHAR(512) NULL,
[STR_PROP_3] NVARCHAR(512) NULL,
[INT_PROP_1] INT NULL,
[INT_PROP_2] INT NULL,
[LONG_PROP_1] BIGINT NULL,
[LONG_PROP_2] BIGINT NULL,
[DEC_PROP_1] NUMERIC(13,4) NULL,
[DEC_PROP_2] NUMERIC(13,4) NULL,
[BOOL_PROP_1] BIT NULL,
[BOOL_PROP_2] BIT NULL,
[TIME_ZONE_ID] NVARCHAR(80) NULL,
CONSTRAINT ' + QUOTENAME(N'PK_' + @tbl) + N' PRIMARY KEY CLUSTERED
([SCHED_NAME], [TRIGGER_NAME], [TRIGGER_GROUP]),
CONSTRAINT ' + QUOTENAME(N'FK_' + @tbl + N'_' + @prefix + N'TRIGGERS')
+ N' FOREIGN KEY ([SCHED_NAME], [TRIGGER_NAME], [TRIGGER_GROUP])
REFERENCES ' + QUOTENAME(@schema) + N'.'
+ QUOTENAME(@prefix + N'TRIGGERS')
+ N' ([SCHED_NAME], [TRIGGER_NAME], [TRIGGER_GROUP])
ON DELETE CASCADE
)';
EXEC sys.sp_executesql @sql;
PRINT N' created ' + @tbl;
END;
SET @tbl = @prefix + N'CRON_TRIGGERS';
SET @full = QUOTENAME(@schema) + N'.' + QUOTENAME(@tbl);
IF OBJECT_ID(@full, N'U') IS NULL
BEGIN
SET @sql = N'CREATE TABLE ' + @full + N' (
[SCHED_NAME] NVARCHAR(100) NOT NULL,
[TRIGGER_NAME] NVARCHAR(150) NOT NULL,
[TRIGGER_GROUP] NVARCHAR(150) NOT NULL,
[CRON_EXPRESSION] NVARCHAR(120) NOT NULL,
[TIME_ZONE_ID] NVARCHAR(80) NULL,
CONSTRAINT ' + QUOTENAME(N'PK_' + @tbl) + N' PRIMARY KEY CLUSTERED
([SCHED_NAME], [TRIGGER_NAME], [TRIGGER_GROUP]),
CONSTRAINT ' + QUOTENAME(N'FK_' + @tbl + N'_' + @prefix + N'TRIGGERS')
+ N' FOREIGN KEY ([SCHED_NAME], [TRIGGER_NAME], [TRIGGER_GROUP])
REFERENCES ' + QUOTENAME(@schema) + N'.'
+ QUOTENAME(@prefix + N'TRIGGERS')
+ N' ([SCHED_NAME], [TRIGGER_NAME], [TRIGGER_GROUP])
ON DELETE CASCADE
)';
EXEC sys.sp_executesql @sql;
PRINT N' created ' + @tbl;
END;
SET @tbl = @prefix + N'BLOB_TRIGGERS';
SET @full = QUOTENAME(@schema) + N'.' + QUOTENAME(@tbl);
IF OBJECT_ID(@full, N'U') IS NULL
BEGIN
SET @sql = N'CREATE TABLE ' + @full + N' (
[SCHED_NAME] NVARCHAR(100) NOT NULL,
[TRIGGER_NAME] NVARCHAR(150) NOT NULL,
[TRIGGER_GROUP] NVARCHAR(150) NOT NULL,
[BLOB_DATA] ' + @blob + N' NULL
)';
EXEC sys.sp_executesql @sql;
PRINT N' created ' + @tbl;
END;
SET @tbl = @prefix + N'CALENDARS';
SET @full = QUOTENAME(@schema) + N'.' + QUOTENAME(@tbl);
IF OBJECT_ID(@full, N'U') IS NULL
BEGIN
SET @sql = N'CREATE TABLE ' + @full + N' (
[SCHED_NAME] NVARCHAR(100) NOT NULL,
[CALENDAR_NAME] NVARCHAR(200) NOT NULL,
[CALENDAR] ' + @blob + N' NOT NULL,
CONSTRAINT ' + QUOTENAME(N'PK_' + @tbl) + N' PRIMARY KEY CLUSTERED
([SCHED_NAME], [CALENDAR_NAME])
)';
EXEC sys.sp_executesql @sql;
PRINT N' created ' + @tbl;
END;
SET @tbl = @prefix + N'PAUSED_TRIGGER_GRPS';
SET @full = QUOTENAME(@schema) + N'.' + QUOTENAME(@tbl);
IF OBJECT_ID(@full, N'U') IS NULL
BEGIN
SET @sql = N'CREATE TABLE ' + @full + N' (
[SCHED_NAME] NVARCHAR(100) NOT NULL,
[TRIGGER_GROUP] NVARCHAR(150) NOT NULL,
CONSTRAINT ' + QUOTENAME(N'PK_' + @tbl) + N' PRIMARY KEY CLUSTERED
([SCHED_NAME], [TRIGGER_GROUP])
)';
EXEC sys.sp_executesql @sql;
PRINT N' created ' + @tbl;
END;
SET @tbl = @prefix + N'FIRED_TRIGGERS';
SET @full = QUOTENAME(@schema) + N'.' + QUOTENAME(@tbl);
IF OBJECT_ID(@full, N'U') IS NULL
BEGIN
SET @sql = N'CREATE TABLE ' + @full + N' (
[SCHED_NAME] NVARCHAR(100) NOT NULL,
[ENTRY_ID] NVARCHAR(95) NOT NULL,
[TRIGGER_NAME] NVARCHAR(150) NOT NULL,
[TRIGGER_GROUP] NVARCHAR(150) NOT NULL,
[INSTANCE_NAME] NVARCHAR(200) NOT NULL,
[FIRED_TIME] BIGINT NOT NULL,
[SCHED_TIME] BIGINT NOT NULL,
[PRIORITY] INTEGER NOT NULL,
[STATE] NVARCHAR(16) NOT NULL,
[JOB_NAME] NVARCHAR(150) NULL,
[JOB_GROUP] NVARCHAR(150) NULL,
[IS_NONCONCURRENT] BIT NULL,
[REQUESTS_RECOVERY] BIT NULL,
CONSTRAINT ' + QUOTENAME(N'PK_' + @tbl) + N' PRIMARY KEY CLUSTERED
([SCHED_NAME], [ENTRY_ID])
)';
EXEC sys.sp_executesql @sql;
PRINT N' created ' + @tbl;
END;
SET @tbl = @prefix + N'LOCKS';
SET @full = QUOTENAME(@schema) + N'.' + QUOTENAME(@tbl);
IF OBJECT_ID(@full, N'U') IS NULL
BEGIN
SET @sql = N'CREATE TABLE ' + @full + N' (
[SCHED_NAME] NVARCHAR(100) NOT NULL,
[LOCK_NAME] NVARCHAR(40) NOT NULL,
CONSTRAINT ' + QUOTENAME(N'PK_' + @tbl) + N' PRIMARY KEY CLUSTERED
([SCHED_NAME], [LOCK_NAME])
)';
EXEC sys.sp_executesql @sql;
PRINT N' created ' + @tbl;
END;
SET @tbl = @prefix + N'SCHEDULER_STATE';
SET @full = QUOTENAME(@schema) + N'.' + QUOTENAME(@tbl);
IF OBJECT_ID(@full, N'U') IS NULL
BEGIN
SET @sql = N'CREATE TABLE ' + @full + N' (
[SCHED_NAME] NVARCHAR(100) NOT NULL,
[INSTANCE_NAME] NVARCHAR(200) NOT NULL,
[LAST_CHECKIN_TIME] BIGINT NOT NULL,
[CHECKIN_INTERVAL] BIGINT NOT NULL,
CONSTRAINT ' + QUOTENAME(N'PK_' + @tbl) + N' PRIMARY KEY CLUSTERED
([SCHED_NAME], [INSTANCE_NAME])
)';
EXEC sys.sp_executesql @sql;
PRINT N' created ' + @tbl;
END;
DECLARE @idx TABLE (idx_suffix nvarchar(100), tbl_suffix sysname, cols nvarchar(400));
INSERT INTO @idx (idx_suffix, tbl_suffix, cols) VALUES
(N'T_C', N'TRIGGERS', N'[SCHED_NAME],[CALENDAR_NAME]'),
(N'T_G_J', N'TRIGGERS', N'[TRIGGER_STATE],[SCHED_NAME],[JOB_GROUP],[JOB_NAME]'),
(N'T_N_G_STATE', N'TRIGGERS', N'[SCHED_NAME],[TRIGGER_GROUP],[TRIGGER_STATE]'),
(N'T_N_STATE', N'TRIGGERS', N'[SCHED_NAME],[TRIGGER_NAME],[TRIGGER_GROUP],[TRIGGER_STATE]'),
(N'T_NEXT_FIRE_TIME', N'TRIGGERS', N'[SCHED_NAME],[NEXT_FIRE_TIME]'),
(N'T_NFT_ST', N'TRIGGERS', N'[SCHED_NAME],[TRIGGER_STATE],[NEXT_FIRE_TIME]'),
(N'T_NFT_ST_MISFIRE', N'TRIGGERS', N'[SCHED_NAME],[MISFIRE_INSTR],[NEXT_FIRE_TIME],[TRIGGER_STATE]'),
(N'T_NFT_ST_MISFIRE_GRP', N'TRIGGERS', N'[SCHED_NAME],[MISFIRE_INSTR],[NEXT_FIRE_TIME],[TRIGGER_GROUP],[TRIGGER_STATE]'),
(N'T_STATE', N'TRIGGERS', N'[SCHED_NAME],[TRIGGER_STATE]'),
(N'FT_G_J', N'FIRED_TRIGGERS', N'[SCHED_NAME],[JOB_GROUP],[JOB_NAME]'),
(N'FT_G_T', N'FIRED_TRIGGERS', N'[SCHED_NAME],[TRIGGER_GROUP],[TRIGGER_NAME]'),
(N'FT_INST_JOB_REQ_RCVRY', N'FIRED_TRIGGERS', N'[SCHED_NAME],[INSTANCE_NAME],[REQUESTS_RECOVERY]');
DECLARE @iname sysname, @itbl sysname, @icols nvarchar(400);
DECLARE idx_cur CURSOR LOCAL FAST_FORWARD FOR
SELECT N'IDX_' + @prefix + idx_suffix, @prefix + tbl_suffix, cols FROM @idx;
OPEN idx_cur;
FETCH NEXT FROM idx_cur INTO @iname, @itbl, @icols;
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS (SELECT 1 FROM sys.indexes
WHERE name = @iname
AND object_id = OBJECT_ID(QUOTENAME(@schema) + N'.'
+ QUOTENAME(@itbl)))
BEGIN
SET @sql = N'CREATE INDEX ' + QUOTENAME(@iname) + N' ON '
+ QUOTENAME(@schema) + N'.' + QUOTENAME(@itbl)
+ N' (' + @icols + N')';
EXEC sys.sp_executesql @sql;
PRINT N' created index ' + @iname;
END;
FETCH NEXT FROM idx_cur INTO @iname, @itbl, @icols;
END;
CLOSE idx_cur;
DEALLOCATE idx_cur;
DECLARE @expected TABLE (tbl_suffix sysname, col_name sysname);
INSERT INTO @expected (tbl_suffix, col_name) VALUES
(N'JOB_DETAILS', N'SCHED_NAME'), (N'JOB_DETAILS', N'JOB_NAME'),
(N'JOB_DETAILS', N'JOB_GROUP'), (N'JOB_DETAILS', N'DESCRIPTION'),
(N'JOB_DETAILS', N'JOB_CLASS_NAME'), (N'JOB_DETAILS', N'IS_DURABLE'),
(N'JOB_DETAILS', N'IS_NONCONCURRENT'), (N'JOB_DETAILS', N'IS_UPDATE_DATA'),
(N'JOB_DETAILS', N'REQUESTS_RECOVERY'), (N'JOB_DETAILS', N'JOB_DATA'),
(N'TRIGGERS', N'SCHED_NAME'), (N'TRIGGERS', N'TRIGGER_NAME'),
(N'TRIGGERS', N'TRIGGER_GROUP'), (N'TRIGGERS', N'JOB_NAME'),
(N'TRIGGERS', N'JOB_GROUP'), (N'TRIGGERS', N'DESCRIPTION'),
(N'TRIGGERS', N'NEXT_FIRE_TIME'), (N'TRIGGERS', N'PREV_FIRE_TIME'),
(N'TRIGGERS', N'PRIORITY'), (N'TRIGGERS', N'TRIGGER_STATE'),
(N'TRIGGERS', N'TRIGGER_TYPE'), (N'TRIGGERS', N'START_TIME'),
(N'TRIGGERS', N'END_TIME'), (N'TRIGGERS', N'CALENDAR_NAME'),
(N'TRIGGERS', N'MISFIRE_INSTR'), (N'TRIGGERS', N'JOB_DATA'),
(N'SIMPLE_TRIGGERS', N'SCHED_NAME'), (N'SIMPLE_TRIGGERS', N'TRIGGER_NAME'),
(N'SIMPLE_TRIGGERS', N'TRIGGER_GROUP'), (N'SIMPLE_TRIGGERS', N'REPEAT_COUNT'),
(N'SIMPLE_TRIGGERS', N'REPEAT_INTERVAL'), (N'SIMPLE_TRIGGERS', N'TIMES_TRIGGERED'),
(N'SIMPROP_TRIGGERS', N'SCHED_NAME'), (N'SIMPROP_TRIGGERS', N'TRIGGER_NAME'),
(N'SIMPROP_TRIGGERS', N'TRIGGER_GROUP'), (N'SIMPROP_TRIGGERS', N'STR_PROP_1'),
(N'SIMPROP_TRIGGERS', N'STR_PROP_2'), (N'SIMPROP_TRIGGERS', N'STR_PROP_3'),
(N'SIMPROP_TRIGGERS', N'INT_PROP_1'), (N'SIMPROP_TRIGGERS', N'INT_PROP_2'),
(N'SIMPROP_TRIGGERS', N'LONG_PROP_1'), (N'SIMPROP_TRIGGERS', N'LONG_PROP_2'),
(N'SIMPROP_TRIGGERS', N'DEC_PROP_1'), (N'SIMPROP_TRIGGERS', N'DEC_PROP_2'),
(N'SIMPROP_TRIGGERS', N'BOOL_PROP_1'), (N'SIMPROP_TRIGGERS', N'BOOL_PROP_2'),
(N'SIMPROP_TRIGGERS', N'TIME_ZONE_ID'),
(N'CRON_TRIGGERS', N'SCHED_NAME'), (N'CRON_TRIGGERS', N'TRIGGER_NAME'),
(N'CRON_TRIGGERS', N'TRIGGER_GROUP'), (N'CRON_TRIGGERS', N'CRON_EXPRESSION'),
(N'CRON_TRIGGERS', N'TIME_ZONE_ID'),
(N'BLOB_TRIGGERS', N'SCHED_NAME'), (N'BLOB_TRIGGERS', N'TRIGGER_NAME'),
(N'BLOB_TRIGGERS', N'TRIGGER_GROUP'), (N'BLOB_TRIGGERS', N'BLOB_DATA'),
(N'CALENDARS', N'SCHED_NAME'), (N'CALENDARS', N'CALENDAR_NAME'),
(N'CALENDARS', N'CALENDAR'),
(N'PAUSED_TRIGGER_GRPS', N'SCHED_NAME'),
(N'PAUSED_TRIGGER_GRPS', N'TRIGGER_GROUP'),
(N'FIRED_TRIGGERS', N'SCHED_NAME'), (N'FIRED_TRIGGERS', N'ENTRY_ID'),
(N'FIRED_TRIGGERS', N'TRIGGER_NAME'), (N'FIRED_TRIGGERS', N'TRIGGER_GROUP'),
(N'FIRED_TRIGGERS', N'INSTANCE_NAME'), (N'FIRED_TRIGGERS', N'FIRED_TIME'),
(N'FIRED_TRIGGERS', N'SCHED_TIME'), (N'FIRED_TRIGGERS', N'PRIORITY'),
(N'FIRED_TRIGGERS', N'STATE'), (N'FIRED_TRIGGERS', N'JOB_NAME'),
(N'FIRED_TRIGGERS', N'JOB_GROUP'), (N'FIRED_TRIGGERS', N'IS_NONCONCURRENT'),
(N'FIRED_TRIGGERS', N'REQUESTS_RECOVERY'),
(N'LOCKS', N'SCHED_NAME'), (N'LOCKS', N'LOCK_NAME'),
(N'SCHEDULER_STATE', N'SCHED_NAME'),
(N'SCHEDULER_STATE', N'INSTANCE_NAME'),
(N'SCHEDULER_STATE', N'LAST_CHECKIN_TIME'),
(N'SCHEDULER_STATE', N'CHECKIN_INTERVAL');
DECLARE @problems nvarchar(max) = N'';
SELECT @problems = @problems + NCHAR(10) + N' table ' + @schema + N'.'
+ @prefix + t.tbl_suffix + N' is missing'
FROM (SELECT DISTINCT tbl_suffix FROM @expected) t
WHERE OBJECT_ID(QUOTENAME(@schema) + N'.'
+ QUOTENAME(@prefix + t.tbl_suffix), N'U') IS NULL;
SELECT @problems = @problems + NCHAR(10) + N' column ' + @prefix + e.tbl_suffix
+ N'.' + e.col_name + N' is missing'
FROM @expected e
WHERE OBJECT_ID(QUOTENAME(@schema) + N'.'
+ QUOTENAME(@prefix + e.tbl_suffix), N'U') IS NOT NULL
AND NOT EXISTS (
SELECT 1 FROM sys.columns c
WHERE c.object_id = OBJECT_ID(QUOTENAME(@schema) + N'.'
+ QUOTENAME(@prefix + e.tbl_suffix))
AND c.name = e.col_name);
IF @problems <> N''
BEGIN
SET @msg = N'Quartz schema is incomplete:' + @problems + NCHAR(10)
+ N'A table with an older structure already existed and was left '
+ N'untouched. Compare it with the main application database and '
+ N'fix it manually, or drop it and re-run this script.';
THROW 50000, @msg, 1;
END;
DECLARE @cnt int = (
SELECT COUNT(*) FROM (SELECT DISTINCT tbl_suffix FROM @expected) t
WHERE OBJECT_ID(QUOTENAME(@schema) + N'.'
+ QUOTENAME(@prefix + t.tbl_suffix), N'U') IS NOT NULL);
PRINT N'OK: ' + CAST(@cnt AS nvarchar(10)) + N' Quartz tables with prefix "'
+ @prefix + N'" present in ' + DB_NAME() + N'.' + @schema;
GO
Скрипт для PostgreSQL
DO $bpm_quartz$
DECLARE
v_schema text := 'public';
v_prefix text := 'qrtz_';
v_missing text := '';
v_created int := 0;
v_exists bool;
r record;
c_expected constant text[][] := ARRAY[
['job_details', 'sched_name,job_name,job_group,description,job_class_name,is_durable,is_nonconcurrent,is_update_data,requests_recovery,job_data'],
['triggers', 'sched_name,trigger_name,trigger_group,job_name,job_group,description,next_fire_time,prev_fire_time,priority,trigger_state,trigger_type,start_time,end_time,calendar_name,misfire_instr,job_data'],
['simple_triggers', 'sched_name,trigger_name,trigger_group,repeat_count,repeat_interval,times_triggered'],
['simprop_triggers', 'sched_name,trigger_name,trigger_group,str_prop_1,str_prop_2,str_prop_3,int_prop_1,int_prop_2,long_prop_1,long_prop_2,dec_prop_1,dec_prop_2,bool_prop_1,bool_prop_2,time_zone_id'],
['cron_triggers', 'sched_name,trigger_name,trigger_group,cron_expression,time_zone_id'],
['blob_triggers', 'sched_name,trigger_name,trigger_group,blob_data'],
['calendars', 'sched_name,calendar_name,calendar'],
['paused_trigger_grps','sched_name,trigger_group'],
['fired_triggers', 'sched_name,entry_id,trigger_name,trigger_group,instance_name,fired_time,sched_time,priority,state,job_name,job_group,is_nonconcurrent,requests_recovery'],
['locks', 'sched_name,lock_name'],
['scheduler_state', 'sched_name,instance_name,last_checkin_time,checkin_interval']
];
BEGIN
IF v_prefix IS NULL OR v_prefix = '' THEN
RAISE EXCEPTION 'v_prefix must not be empty';
END IF;
PERFORM 1 FROM pg_namespace WHERE nspname = v_schema;
IF NOT FOUND THEN
RAISE EXCEPTION 'Schema "%" does not exist', v_schema;
END IF;
RAISE NOTICE 'Quartz tables: schema=%, prefix=%', v_schema, v_prefix;
EXECUTE format($f$
CREATE TABLE IF NOT EXISTS %I.%I (
sched_name text NOT NULL,
job_name text NOT NULL,
job_group text NOT NULL,
description text,
job_class_name text NOT NULL,
is_durable boolean NOT NULL,
is_nonconcurrent boolean NOT NULL,
is_update_data boolean NOT NULL,
requests_recovery boolean NOT NULL,
job_data bytea,
CONSTRAINT %I PRIMARY KEY (sched_name, job_name, job_group)
)$f$,
v_schema, v_prefix || 'job_details',
v_prefix || 'job_details_pkey');
EXECUTE format($f$
CREATE TABLE IF NOT EXISTS %I.%I (
sched_name text NOT NULL,
trigger_name text NOT NULL,
trigger_group text NOT NULL,
job_name text NOT NULL,
job_group text NOT NULL,
description text,
next_fire_time bigint,
prev_fire_time bigint,
priority integer,
trigger_state text NOT NULL,
trigger_type text NOT NULL,
start_time bigint NOT NULL,
end_time bigint,
calendar_name text,
misfire_instr smallint,
job_data bytea,
CONSTRAINT %I PRIMARY KEY (sched_name, trigger_name, trigger_group),
CONSTRAINT %I FOREIGN KEY (job_group, sched_name, job_name)
REFERENCES %I.%I (job_group, sched_name, job_name)
)$f$,
v_schema, v_prefix || 'triggers',
v_prefix || 'triggers_pkey',
v_prefix || 'triggers_sched_name_fkey',
v_schema, v_prefix || 'job_details');
EXECUTE format($f$
CREATE TABLE IF NOT EXISTS %I.%I (
sched_name text NOT NULL,
trigger_name text NOT NULL,
trigger_group text NOT NULL,
repeat_count bigint NOT NULL,
repeat_interval bigint NOT NULL,
times_triggered bigint NOT NULL,
CONSTRAINT %I PRIMARY KEY (sched_name, trigger_name, trigger_group),
CONSTRAINT %I FOREIGN KEY (trigger_group, trigger_name, sched_name)
REFERENCES %I.%I (trigger_group, trigger_name, sched_name)
ON DELETE CASCADE
)$f$,
v_schema, v_prefix || 'simple_triggers',
v_prefix || 'simple_triggers_pkey',
v_prefix || 'simple_triggers_sched_name_fkey',
v_schema, v_prefix || 'triggers');
EXECUTE format($f$
CREATE TABLE IF NOT EXISTS %I.%I (
sched_name text NOT NULL,
trigger_name text NOT NULL,
trigger_group text NOT NULL,
str_prop_1 text,
str_prop_2 text,
str_prop_3 text,
int_prop_1 integer,
int_prop_2 integer,
long_prop_1 bigint,
long_prop_2 bigint,
dec_prop_1 numeric,
dec_prop_2 numeric,
bool_prop_1 boolean,
bool_prop_2 boolean,
time_zone_id text,
CONSTRAINT %I PRIMARY KEY (sched_name, trigger_name, trigger_group),
CONSTRAINT %I FOREIGN KEY (sched_name, trigger_name, trigger_group)
REFERENCES %I.%I (sched_name, trigger_name, trigger_group)
ON DELETE CASCADE
)$f$,
v_schema, v_prefix || 'simprop_triggers',
v_prefix || 'simprop_triggers_pkey',
v_prefix || 'simprop_triggers_sched_name_fkey',
v_schema, v_prefix || 'triggers');
EXECUTE format($f$
CREATE TABLE IF NOT EXISTS %I.%I (
sched_name text NOT NULL,
trigger_name text NOT NULL,
trigger_group text NOT NULL,
cron_expression text NOT NULL,
time_zone_id text,
CONSTRAINT %I PRIMARY KEY (sched_name, trigger_name, trigger_group),
CONSTRAINT %I FOREIGN KEY (trigger_group, trigger_name, sched_name)
REFERENCES %I.%I (trigger_group, trigger_name, sched_name)
ON DELETE CASCADE
)$f$,
v_schema, v_prefix || 'cron_triggers',
v_prefix || 'cron_triggers_pkey',
v_prefix || 'cron_triggers_sched_name_fkey',
v_schema, v_prefix || 'triggers');
EXECUTE format($f$
CREATE TABLE IF NOT EXISTS %I.%I (
sched_name text NOT NULL,
trigger_name text NOT NULL,
trigger_group text NOT NULL,
blob_data bytea,
CONSTRAINT %I PRIMARY KEY (sched_name, trigger_name, trigger_group),
CONSTRAINT %I FOREIGN KEY (sched_name, trigger_name, trigger_group)
REFERENCES %I.%I (sched_name, trigger_name, trigger_group)
ON DELETE CASCADE
)$f$,
v_schema, v_prefix || 'blob_triggers',
v_prefix || 'blob_triggers_pkey',
v_prefix || 'blob_triggers_sched_name_fkey',
v_schema, v_prefix || 'triggers');
EXECUTE format($f$
CREATE TABLE IF NOT EXISTS %I.%I (
sched_name text NOT NULL,
calendar_name text NOT NULL,
calendar bytea NOT NULL,
CONSTRAINT %I PRIMARY KEY (sched_name, calendar_name)
)$f$,
v_schema, v_prefix || 'calendars',
v_prefix || 'calendars_pkey');
EXECUTE format($f$
CREATE TABLE IF NOT EXISTS %I.%I (
sched_name text NOT NULL,
trigger_group text NOT NULL,
CONSTRAINT %I PRIMARY KEY (sched_name, trigger_group)
)$f$,
v_schema, v_prefix || 'paused_trigger_grps',
v_prefix || 'paused_trigger_grps_pkey');
EXECUTE format($f$
CREATE TABLE IF NOT EXISTS %I.%I (
sched_name text NOT NULL,
entry_id text NOT NULL,
trigger_name text NOT NULL,
trigger_group text NOT NULL,
instance_name text NOT NULL,
fired_time bigint NOT NULL,
sched_time bigint NOT NULL,
priority integer NOT NULL,
state text NOT NULL,
job_name text,
job_group text,
is_nonconcurrent boolean NOT NULL,
requests_recovery boolean,
CONSTRAINT %I PRIMARY KEY (sched_name, entry_id)
)$f$,
v_schema, v_prefix || 'fired_triggers',
v_prefix || 'fired_triggers_pkey');
EXECUTE format($f$
CREATE TABLE IF NOT EXISTS %I.%I (
sched_name text NOT NULL,
lock_name text NOT NULL,
CONSTRAINT %I PRIMARY KEY (sched_name, lock_name)
)$f$,
v_schema, v_prefix || 'locks',
v_prefix || 'locks_pkey');
EXECUTE format($f$
CREATE TABLE IF NOT EXISTS %I.%I (
sched_name text NOT NULL,
instance_name text NOT NULL,
last_checkin_time bigint NOT NULL,
checkin_interval bigint NOT NULL,
CONSTRAINT %I PRIMARY KEY (sched_name, instance_name)
)$f$,
v_schema, v_prefix || 'scheduler_state',
v_prefix || 'scheduler_state_pkey');
FOR r IN
SELECT * FROM (VALUES
('ft_job_group', 'fired_triggers', 'job_group'),
('ft_job_name', 'fired_triggers', 'job_name'),
('ft_job_req_recovery', 'fired_triggers', 'requests_recovery'),
('ft_trig_group', 'fired_triggers', 'trigger_group'),
('ft_trig_inst_name', 'fired_triggers', 'instance_name'),
('ft_trig_name', 'fired_triggers', 'trigger_name'),
('ft_trig_nm_gp', 'fired_triggers', 'sched_name, trigger_name, trigger_group'),
('j_req_recovery', 'job_details', 'requests_recovery'),
('t_next_fire_time', 'triggers', 'next_fire_time'),
('t_nft_st', 'triggers', 'next_fire_time, trigger_state'),
('t_state', 'triggers', 'trigger_state')
) AS x(idx_suffix, tbl_suffix, cols)
LOOP
EXECUTE format('CREATE INDEX IF NOT EXISTS %I ON %I.%I USING btree (%s)',
'idx_' || v_prefix || r.idx_suffix,
v_schema, v_prefix || r.tbl_suffix,
r.cols);
END LOOP;
FOR i IN 1 .. array_length(c_expected, 1) LOOP
SELECT EXISTS (
SELECT 1 FROM information_schema.tables
WHERE table_schema = v_schema
AND table_name = v_prefix || c_expected[i][1]
) INTO v_exists;
IF NOT v_exists THEN
v_missing := v_missing || format(E'\n table %s.%s is missing',
v_schema, v_prefix || c_expected[i][1]);
CONTINUE;
END IF;
FOR r IN SELECT unnest(string_to_array(c_expected[i][2], ',')) AS col LOOP
SELECT EXISTS (
SELECT 1 FROM information_schema.columns
WHERE table_schema = v_schema
AND table_name = v_prefix || c_expected[i][1]
AND column_name = r.col
) INTO v_exists;
IF NOT v_exists THEN
v_missing := v_missing || format(E'\n column %s.%s is missing',
v_prefix || c_expected[i][1], r.col);
END IF;
END LOOP;
END LOOP;
IF v_missing <> '' THEN
RAISE EXCEPTION 'Quartz schema is incomplete:%', v_missing
USING HINT = 'A table with an older structure already existed and was '
'left untouched by CREATE TABLE IF NOT EXISTS. '
'Compare it with the main application database and fix it '
'manually, or drop it and re-run this script.';
END IF;
SELECT count(*) INTO v_created
FROM information_schema.tables
WHERE table_schema = v_schema
AND table_name LIKE v_prefix || '%';
RAISE NOTICE 'OK: % tables with prefix "%" present in schema "%"',
v_created, v_prefix, v_schema;
END
$bpm_quartz$;
Рекомендуем изучить
Диагностика и решение проблем планировщика Quartz
Создание пользовательского планировщика Quartz
Материал был полезен для вас?