Ну так кто спорит, что большие БД это не интересно? Проблема в том что таких БД 1%, и на всех DBA не хватает, интересно только одному проценту DBA. 😂
Про Highload DB с тысячами реквестов в секунду это одно, а десятки тысяч и миллионы, это уже совсем другая категория, это уже Ultra HighLoad. Подходы к решению проблем тут совершенно другие. Их применение для БД меньшей нагрузкой не будет оправданным.
Поэтому есть комменты, о "сугубо личном мега-костыле", а также мой комменты.
Предлагаю в самом начале статьи сразу писать что речь идет о БД с миллионами реквестов в секунду, это ясно обозначает категорию БД.
Вот теперь всё встало на свои места, это гигантский объём транзакций. Поэтому вам нужны специализированные решения, как для изменения схемы, так и для сессий.
Для остальных 99% DBA, решение это таймауты и оптимизация.
Плиз ответ кодом, конкретно, для данного примера какое самое оптимальное решение на ваш взгляд, хоть одно?
Может тут не совсем понятно, скрипт не оборачивается в транзакцию, транзакция только на момент переименований и удаления. И все локи, о которых вы говорите, занимают миллисекунды в этом скрипте, т.к. все операции проходят на уровне метаданных, это ключевой момент. Данные затрагиваются только на момент миграции (update statement), это может быть долгий процесс, но он не блокирует таблицу полностью, там блокировки строк/страниц только внутри батча, а количество блокировок зависит от размера батча, который настраивается.
Пример с индексом для SQL Server Enterprise, и я не знаю что может быть лучше этого.
drop table if exists large_table;
go
create table large_table
(
id int identity(1, 1) primary key
, column1 varchar(50) not null
, other_data varchar(8000)
);
go
create index ix_large_table__column1 on large_table ( column1 );
go
with t1 as (select 1 as n union all select 1), -- 2
t2 as (select 1 as n from t1 as a cross join t1 as b), -- 4
t3 as (select 1 as n from t2 as a cross join t2 as b), -- 16
t4 as (select 1 as n from t3 as a cross join t3 as b), -- 256
t5 as (select 1 as n from t4 as a cross join t4 as b), -- 65,536
t6 as (select 1 as n from t5 as a cross join t5 as b), -- plenty
nums as (select row_number() over (order by (select null)) as n from t6)
insert into large_table ( column1, other_data )
select top(1000000)
'value_' + cast(n as varchar(10))
, 'other_data' + cast(n as varchar(10)) + replicate('a', n % 4000)
from nums;
go
-- create new column with the new data type
alter table large_table
add column1_new nvarchar(100) null;
go
-- trigger for data migration
create trigger trg_sync_columns
on large_table
after insert, update
as
begin
set nocount on;
if update(column1)
begin
update t
set t.column1_new = i.column1
from large_table t
join inserted i on t.id = i.id;
end;
end;
go
-- batch update loop using primary key
set nocount on;
declare @batch_size int = 10000;
declare @last_id int = -1;
while 1 = 1
begin
with cte_source
as (
select top(@batch_size) *
from dbo.large_table
where id > @last_id
order by id
)
, cte_data
as (
select *, max(id) over () as max_id
from cte_source
)
update cte_data
set column1_new = column1
, @last_id = max_id
where id > @last_id;
if @@rowcount = 0 break;
raiserror('@last_id=%d', 0, 0, @last_id) with nowait;
end;
go
create index ix_large_table__column1_new
on dbo.large_table ( column1_new )
with ( online = on );
go
-- Short LOCK
set xact_abort on;
begin tran;
drop trigger trg_sync_columns;
drop index ix_large_table__column1 on dbo.large_table;
alter table dbo.large_table drop column column1;
exec sys.sp_rename
@objname = 'dbo.large_table.column1_new'
, @newname = 'column1'
, @objtype = 'column';
exec sys.sp_rename
@objname = 'dbo.large_table.ix_large_table__column1_new'
, @newname = 'ix_large_table__column1'
, @objtype = 'index';
commit;
go
select top(10) *
from dbo.large_table;
go
Триггер по таблице в высоко нагрушенной БД сразу положит СУБД.
Чуть подробнее, пожалуйста, триггер про который я говорил, делает минимум действий, и добавит небольшой процент к нагрузке. Назовите причину по которой он положит базу?
не так делается и это вредно так делать, но что создаётся таблица рядом с нужным полем верно
Напишите как делается, и что тут вредного? И про таблицу рядом с нужным полем я не говорил.
Как вы обычно меняете тип столбца в БД которая работает 24/7? 24/7 предполагает что эта операция online и никого не блокирует.
Предположу что у вас SQL Server Standard и/или вы не используете online операции (к примеру при создании ребилде индксов и т.д.). Во всех остальных случаях проблемы решаются.
Ключевые моменты для OLTP системы где таких проблем нет:
ВСЕ запросы выполняются в рамках таймаута (стандартно в пределах 30 секунд, если таймаут больше, скорее всего уже что-то не так).
тут важный момент, что все запросы оптимизированы, и на них не забивают, если мы выставили таймаут в 30 секунд, это не значит что все запросы выполняются на пределе, большинство работают миллисекунды.
нельзя обновлять/вставлять большие объемы данных, это занимает время и приводит к блокировкам. Решение - обновление батчами.
если у вас система работает 24/7, то любые изменения схемы должны быть онлайн.
Пример: Нужно изменить тип столбца в большой таблице, создаете новый столбец, переносите данные из старого столбца в новый (батчами), дропаете старый, переименовываете новый.
Также добавляем временный триггер на таблице, чтобы любые изменения приходящие извне обновляли также и данные в новом столбце.
для SQL Server Standard, должно быть окно для обслуживания, и обновлений (ночью или в выходные дни). Тут допустимы длинные таймауты.
Может быть у вас какая-то legacy база, которая используется для всего сразу, это и OLTP и Warehouse и левые пользователи на прямую к базе подключаются и произвольные запросы выполняют, то возможно да, ваше решение решает проблему этого хаоса.
У вас включен RCSI? Поможет решить часть ваших проблем с блокировками...
В этом тренажёре сначала выдаются слова составленные из букв центрального ряда "ФЫВАПРОЛДЖЭ". Это самый простой уровень, дальше подключаются другие ряды в зависимости от скорости печати т.е. опыта ученика. Самое главное достоинство тренажёра, это проработка ошибок, если ученик ошибается, тренажёр подкидывает слова чтобы проработать ошибки. Также он анализирует проблемные участки и тоже подкидывает слова чтобы их проработать.
Учитель это тоже может сделать, но это затратно если студентов много.
TypoCode - это клавиатурный тренажёр, который адаптируется под пользователя: анализирует ошибки, выявляет проблемные участки и на основе этих данных формирует последующие упражнения. Его можно использовать с нуля, постепенно развивая скорость и точность печати.
Тоже самое и у меня было. 4 года назад в новую КВ купил разных светодиодных ламп. Гаусы все подсохли первыми в первый же год (лампы разной мощности), хотя стоили значительно дороже конкурентов. Самые стойкие оказались какие-то самые дешёвые Feron, до сих пор большинство ламп этого бренда работают. У них просто выгорает люминофор и светят со временем слабее.
Данные сразу будут записаны в LOB хранилище. И это правильно, т.к. в большинстве случаев если мы читаем var...(max) поле, то мы читаем его полностью, значит нет смысла часть его хранить в таблице а часть в LOB. И второе, достаточно часто в запросах читаются все поля кроме var...(max), т.е. больше поля читаются как правило точеными запросами, типа получения объекта по его идентификатору. А значит большинство запросов выигрывают в случае включения этой опции, т.к. читая страницы таблицы мы не читаем багаж в виже части LOB полей.
1 = varchar(max), nvarchar(max), varbinary(max), xml and large user-defined type (UDT) columns in the table are stored out of row, with a 16-byte pointer to the root.
0 = varchar(max), nvarchar(max), varbinary(max), xml and large UDT values are stored directly in the data row, up to a limit of 8000 bytes and as long as the value can fit in the record. If the value does not fit in the record, a pointer is stored in-row and the rest is stored out of row in the LOB storage space. 0 is the default value.
К примеру в таблице 15 столбцов, один из столбцов (DataStatus) содержит 50% одинаковых значений (значение Completed), и что вы предлагаете, как нормализовать такую таблцу?
Перенести Completed записи в другую таблицу? А потом мучиться с отчетами, и с процессами по переносу данных и одной таблиы в другую. Может кто-то захочит перевести статус в Active? А если нужно обновить данные другого столбца то придется обновлять две таблицы, делать два запроса, писать "умный" DataAccess. В общем это проблема из ничего.
Нормализация хороша в теории, на практике выше 3-й нормальной формы заморачиватся не стоит.
Все нормально работает. Просто нужно указывать точный фильтр в запросе, такой же как и в фильтрованном индексе. Будет работать и для сложных условий по нескольким столбцам тоже.
create index IDX_VAL on Unlucky (VAL) where VAL <> 'n/a'
go
create or alter procedure SmartGetVal
@val varchar(32)
as
if @val = 'n/a'
select *
from dbo.Unlucky
where VAL = 'n/a' -- not @val!!!
else
select *
from dbo.Unlucky with ( index = IDX_VAL )
where VAL = @val
and VAL <> 'n/a'
go
exec dbo.SmartGetVal @val = '1val'
go
print @@version
Microsoft SQL Server 2017 (RTM-GDR) (KB4583456) - 14.0.2037.2 (X64)
Nov 2 2020 19:19:59
Copyright (C) 2017 Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19044: )
При использовании типа varchar(n) / nvarchar(n), sql server предполагает что средний размет строки будет n/2, и исходя из этого выделяет память для выполнения запросов. Поэтому при выборе значения n идеальным будет удвоенное значение средней длины строк хранящихся в этом столбце. Для varcha(max) / nvarchar(max) он предполагает что средняя длина равна 4000 символам, т.е. если вы скажем будете хранить строки размером в 100 символов в таком столбце, то памяти будет выделяться в 40 раз больше чем нужно для запроса, что в свою очередь плохо скажется на производительности.
Скажем у вас в таблице есть столбцы с данными, и один или несколько столбцов varcha(max) / nvarchar(max). По умолчанию данные varcha(max) / nvarchar(max) хранятся IN_ROW если объем их меньше 8000 байт. Иногда имеет смысл поменять это поведение, и сразу хранить все эти данные как LOB_DATA. Это улучшит производительность запросов которые не читают LOB столбцы из таблицы. Такое поведение можно включить через опцию large value types out of row
exec sys.sp_tableoption
@TableNamePattern = 'dbo.TableName'
, @OptionName = 'large value types out of row'
, @OptionValue = '1'
Не работает так, как вы описываете. Без кластерного индекса такое же поведение.
Все также висит X (exclusive) блокировка на некластерном индексе во время апдейта, и она не совместима с S (shared) блокировкой при чтении, вторая сессия ждет завершения первой.
Может быть описываемое вами поведение это просто баг/фича в какой-нибудь старой версии sql server?
Тестировалось на:
Microsoft SQL Server 2019 (RTM-GDR) (KB4583458) - 15.0.2080.9 (X64) Nov 6 2020 16:50:01
Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit)
on Windows 10 Pro 10.0 <X64> (Build 19044: )
Я правильно понимаю что мы говорим о двух сессиях, первая обновляет строку, вторая читает ее в обычном read committed?
-- table definition
create table dbo.test1 (
id int primary key clustered
, data1 varchar(100)
, data2 char(100)
, data3 nchar(100)
)
go
create index idx_test1_allincluded
on dbo.test1 ( id ) include ( data1, data2, data3 )
go
insert into dbo.test1 ( id, data1, data2, data3 )
values (1, 'data1 1', 'data2 1', 'data3 1')
, (2, 'data1 2', 'data2 2', 'data3 2')
, (3, 'data1 3', 'data2 3', 'data3 3')
go
-- session 1
update dbo.test1 set data1 = 'new data 1' where id = 2
go
-- session 2
select * from dbo.test1 where id = 2
go
Если да, то в данном случае не важно есть ли дополнительный индекс или нет, т.к. эксклюзивная блокировка ключа (id=2) накладывается и в кластерном индексе и в дополнительном некластерном. Вторая сессия будет ждать завершения первой в любом случае, и с дополнительным индеком и без него.
Ну так кто спорит, что большие БД это не интересно?
Проблема в том что таких БД 1%, и на всех DBA не хватает, интересно только одному проценту DBA. 😂
Про Highload DB с тысячами реквестов в секунду это одно, а десятки тысяч и миллионы, это уже совсем другая категория, это уже Ultra HighLoad. Подходы к решению проблем тут совершенно другие. Их применение для БД меньшей нагрузкой не будет оправданным.
Поэтому есть комменты, о "сугубо личном мега-костыле", а также мой комменты.
Предлагаю в самом начале статьи сразу писать что речь идет о БД с миллионами реквестов в секунду, это ясно обозначает категорию БД.
Вот теперь всё встало на свои места, это гигантский объём транзакций. Поэтому вам нужны специализированные решения, как для изменения схемы, так и для сессий.
Для остальных 99% DBA, решение это таймауты и оптимизация.
Мы видимо говорим о разных нагрузках на БД.
Для какого объема Write TPS (transaction per second) вы используете подход с копией таблицы?
Плиз ответ кодом, конкретно, для данного примера какое самое оптимальное решение на ваш взгляд, хоть одно?
Может тут не совсем понятно, скрипт не оборачивается в транзакцию, транзакция только на момент переименований и удаления. И все локи, о которых вы говорите, занимают миллисекунды в этом скрипте, т.к. все операции проходят на уровне метаданных, это ключевой момент. Данные затрагиваются только на момент миграции (update statement), это может быть долгий процесс, но он не блокирует таблицу полностью, там блокировки строк/страниц только внутри батча, а количество блокировок зависит от размера батча, который настраивается.
и да, триггер удаляется в скрипте миграции.
Постоянные триггеры - зло, временные - добро.
Пример с индексом для SQL Server Enterprise, и я не знаю что может быть лучше этого.
Чуть подробнее, пожалуйста, триггер про который я говорил, делает минимум действий, и добавит небольшой процент к нагрузке. Назовите причину по которой он положит базу?
Напишите как делается, и что тут вредного? И про таблицу рядом с нужным полем я не говорил.
Как вы обычно меняете тип столбца в БД которая работает 24/7?
24/7 предполагает что эта операция online и никого не блокирует.
Предположу что у вас SQL Server Standard и/или вы не используете online операции (к примеру при создании ребилде индксов и т.д.). Во всех остальных случаях проблемы решаются.
Ключевые моменты для OLTP системы где таких проблем нет:
ВСЕ запросы выполняются в рамках таймаута (стандартно в пределах 30 секунд, если таймаут больше, скорее всего уже что-то не так).
тут важный момент, что все запросы оптимизированы, и на них не забивают, если мы выставили таймаут в 30 секунд, это не значит что все запросы выполняются на пределе, большинство работают миллисекунды.
нельзя обновлять/вставлять большие объемы данных, это занимает время и приводит к блокировкам. Решение - обновление батчами.
если у вас система работает 24/7, то любые изменения схемы должны быть онлайн.
Пример: Нужно изменить тип столбца в большой таблице, создаете новый столбец, переносите данные из старого столбца в новый (батчами), дропаете старый, переименовываете новый.
Также добавляем временный триггер на таблице, чтобы любые изменения приходящие извне обновляли также и данные в новом столбце.
для SQL Server Standard, должно быть окно для обслуживания, и обновлений (ночью или в выходные дни). Тут допустимы длинные таймауты.
Может быть у вас какая-то legacy база, которая используется для всего сразу, это и OLTP и Warehouse и левые пользователи на прямую к базе подключаются и произвольные запросы выполняют, то возможно да, ваше решение решает проблему этого хаоса.
У вас включен RCSI? Поможет решить часть ваших проблем с блокировками...
Интересно, а почему не использовать обычные тайм-ауты запросов? Они же стандартно решают такие проблемы без дополнительных телодвижений?
В этом тренажёре сначала выдаются слова составленные из букв центрального ряда "ФЫВАПРОЛДЖЭ". Это самый простой уровень, дальше подключаются другие ряды в зависимости от скорости печати т.е. опыта ученика. Самое главное достоинство тренажёра, это проработка ошибок, если ученик ошибается, тренажёр подкидывает слова чтобы проработать ошибки. Также он анализирует проблемные участки и тоже подкидывает слова чтобы их проработать.
Учитель это тоже может сделать, но это затратно если студентов много.
TypoCode - это клавиатурный тренажёр, который адаптируется под пользователя: анализирует ошибки, выявляет проблемные участки и на основе этих данных формирует последующие упражнения. Его можно использовать с нуля, постепенно развивая скорость и точность печати.
Посмотрите на этот клавиатурный тренажёр, наша семейная разработка 😄.
Адаптируется под ученика.
https://github.com/zacoders/typocode
Тоже самое и у меня было. 4 года назад в новую КВ купил разных светодиодных ламп. Гаусы все подсохли первыми в первый же год (лампы разной мощности), хотя стоили значительно дороже конкурентов. Самые стойкие оказались какие-то самые дешёвые Feron, до сих пор большинство ламп этого бренда работают. У них просто выгорает люминофор и светят со временем слабее.
Мы используем ноутбуки и git, у нас есть итерации бранят и continuous delivery. Такой вариант всех устраивает.
Databricks + Azure DevOps.
Данные сразу будут записаны в LOB хранилище. И это правильно, т.к. в большинстве случаев если мы читаем var...(max) поле, то мы читаем его полностью, значит нет смысла часть его хранить в таблице а часть в LOB. И второе, достаточно часто в запросах читаются все поля кроме var...(max), т.е. больше поля читаются как правило точеными запросами, типа получения объекта по его идентификатору. А значит большинство запросов выигрывают в случае включения этой опции, т.к. читая страницы таблицы мы не читаем багаж в виже части LOB полей.
Из доков:
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-tableoption-transact-sql?view=sql-server-ver15#arguments
large value types out of row
1 = varchar(max), nvarchar(max), varbinary(max), xml and large user-defined type (UDT) columns in the table are stored out of row, with a 16-byte pointer to the root.
0 = varchar(max), nvarchar(max), varbinary(max), xml and large UDT values are stored directly in the data row, up to a limit of 8000 bytes and as long as the value can fit in the record. If the value does not fit in the record, a pointer is stored in-row and the rest is stored out of row in the LOB storage space. 0 is the default value.
Можно ставить "метку" на запрос.
В данном примере для n/a значения будет другой текст запроса, а значит и другой план (даже коммент в текте запроса меняет его хеш).
Как видите, тут два разных плана. Причем их будет только 2, а не миллион как с инлайном где также присутствует проблема sql injection.
К примеру в таблице 15 столбцов, один из столбцов (DataStatus) содержит 50% одинаковых значений (значение Completed), и что вы предлагаете, как нормализовать такую таблцу?
Перенести Completed записи в другую таблицу? А потом мучиться с отчетами, и с процессами по переносу данных и одной таблиы в другую. Может кто-то захочит перевести статус в Active? А если нужно обновить данные другого столбца то придется обновлять две таблицы, делать два запроса, писать "умный" DataAccess. В общем это проблема из ничего.
Нормализация хороша в теории, на практике выше 3-й нормальной формы заморачиватся не стоит.
Все нормально работает. Просто нужно указывать точный фильтр в запросе, такой же как и в фильтрованном индексе. Будет работать и для сложных условий по нескольким столбцам тоже.
Еще пара моментов которые надо учитывать.
При использовании типа varchar(n) / nvarchar(n), sql server предполагает что средний размет строки будет n/2, и исходя из этого выделяет память для выполнения запросов. Поэтому при выборе значения n идеальным будет удвоенное значение средней длины строк хранящихся в этом столбце.
Для varcha(max) / nvarchar(max) он предполагает что средняя длина равна 4000 символам, т.е. если вы скажем будете хранить строки размером в 100 символов в таком столбце, то памяти будет выделяться в 40 раз больше чем нужно для запроса, что в свою очередь плохо скажется на производительности.
Скажем у вас в таблице есть столбцы с данными, и один или несколько столбцов varcha(max) / nvarchar(max). По умолчанию данные varcha(max) / nvarchar(max) хранятся IN_ROW если объем их меньше 8000 байт.
Иногда имеет смысл поменять это поведение, и сразу хранить все эти данные как LOB_DATA. Это улучшит производительность запросов которые не читают LOB столбцы из таблицы.
Такое поведение можно включить через опцию
large value types out of rowНе работает так, как вы описываете. Без кластерного индекса такое же поведение.
Все также висит X (exclusive) блокировка на некластерном индексе во время апдейта, и она не совместима с S (shared) блокировкой при чтении, вторая сессия ждет завершения первой.
Может быть описываемое вами поведение это просто баг/фича в какой-нибудь старой версии sql server?
Я правильно понимаю что мы говорим о двух сессиях, первая обновляет строку, вторая читает ее в обычном read committed?
Если да, то в данном случае не важно есть ли дополнительный индекс или нет, т.к. эксклюзивная блокировка ключа (id=2) накладывается и в кластерном индексе и в дополнительном некластерном. Вторая сессия будет ждать завершения первой в любом случае, и с дополнительным индеком и без него.