Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Update новой колонки 2.5 млрд строк  [new]
Kopilogus
Member

Откуда:
Сообщений: 49
Коллеги,

поставили задачу оптимизировать таблицу хранения логов в которой 2.5 млрд строк.
Первый этап - изменить поля Method на MethodID и ServerName на ServerID - оба поля tinyint, структура исходной талицы:

CREATE TABLE [dbo].[log_HttpRequest](
	[id] [bigint] NOT NULL,
	[Method] [varchar](16) NOT NULL,
	[RefererId] [bigint] NULL,
	[UriID] [bigint] NOT NULL,
	[UserAgentId] [bigint] NOT NULL,
	[UserID] [bigint] NULL,
	[SessionID] [bigint] NULL,
	[AspSessionID] [varchar](32) NULL,
	[TimeStamp] [datetime] NOT NULL,
	[ProcessTime] [int] NOT NULL,
	[ServerName] [varchar](32) NULL,
	[RequestTraceGUID] [uniqueidentifier] NULL,
	[ClientIP] [varbinary](16) NULL,
	[NSClientIP] [varbinary](16) NULL,
	[RawUriID] [bigint] NULL,
	[ActualUriId] [bigint] NULL,
	[RequestParameters] [varchar](1024) NULL,
	[SecurityTagId] [bigint] NULL,
	[ServerID] [tinyint] NULL,
	[MethodID] [tinyint] NULL,
 CONSTRAINT [PK_log_HttpRequest] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


Есть таблица: tb_Framework_Servers(ServerID tinyint, Servername varchar(32)) подготовленная заранее - содержит 68 записей

Сначала делаю ALTER TABLE ADD ServerID tinyint

затем запустил:

update log_HttpRequest
Set log_HttpRequest.ServerID = tb_Framework_Servers.ServerID
from tb_Framework_Servers
where log_HttpRequest.ServerName = tb_Framework_Servers.ServerName

Выполняется уже более 5 часов ((

Есть мысли как сделать быстрее?
2 фев 18, 19:35    [21161731]     Ответить | Цитировать Сообщить модератору
 Re: Update новой колонки 2.5 млрд строк  [new]
msLex
Member

Откуда:
Сообщений: 8091
Подготовьте заранее все справочники, а потом перелейте (bulk-ом) в новую таблицу с заменой названий на id
2 фев 18, 19:37    [21161735]     Ответить | Цитировать Сообщить модератору
 Re: Update новой колонки 2.5 млрд строк  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36989
Kopilogus
Есть мысли как сделать быстрее?
В связи с потенциальной возможностью расщепления страниц при апдейте, переливка данных в новую таблицу может оказаться быстрее.
2 фев 18, 19:38    [21161736]     Ответить | Цитировать Сообщить модератору
 Re: Update новой колонки 2.5 млрд строк  [new]
Kopilogus
Member

Откуда:
Сообщений: 49
Гавриленко Сергей Алексеевич,

Мысль понял, но есть и второй этап:

избавиться от бесполезного суррогатного ключа [id] который не используется и построить кластерный индекс по полю [TimeStamp] которое активно используют в запросах, опять же вопрос - лить в уже подготовленную таблицу с кластерным индексом или залить кучу (что явно быстрее) а уже после построить индекс?

Есть еще сомнения относительно полей ServerID и MethodID, стоит ли их включать в кластерный индекс или создать отдельно некластеризованный по каждому полю?

Селекты в основном 3х видов: как правило у всех присутствует поиск по TimeStamp, очень часто TimeStamp + ServerID, реже все три поля (и есть особенность колонки MethodID - часто ищут по MethodID=3 который не особенно селективен, 40% записей это "3" есть также и NULL значения).

Стоит ли использовать секционирование + сжатие данных?
Таблица по требованию бизнеса должна хранить данные за 5 лет, один месяц примерно 350 млн записей, средний размер строки 0.5KB данные не могут модифицироваться, т.е. только INSERT с последующим (не факт что частым SELECT - разбор инцидентов по запросу)
2 фев 18, 19:55    [21161775]     Ответить | Цитировать Сообщить модератору
 Re: Update новой колонки 2.5 млрд строк  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36989
Kopilogus
ить в уже подготовленную таблицу с кластерным индексом или залить кучу (что явно быстрее) а уже после построить индекс?
Если лить с минимальным логированием, сразу в кластерный индекс будет быстрее.

Kopilogus
Таблица по требованию бизнеса должна хранить данные за 5 лет, один месяц примерно 350 млн записей, средний размер строки 0.5KB данные не могут модифицироваться, т.е. только INSERT с последующим (не факт что частым SELECT - разбор инцидентов по запросу)
Если через пять лет не хотите проблем с удалением, то лучше секционировать сразу.

Kopilogus
Селекты в основном 3х видов: как правило у всех присутствует поиск по TimeStamp
Если поиск по TimeStamp по диапазону, то добавлять какие-либо поля после этого поля в ключ смысла не имеет.

Kopilogus
+ сжатие данных?
sp_estimate_compression_savings в помощь.
2 фев 18, 20:03    [21161797]     Ответить | Цитировать Сообщить модератору
 Re: Update новой колонки 2.5 млрд строк  [new]
Kopilogus
Member

Откуда:
Сообщений: 49
Гавриленко Сергей Алексеевич
Kopilogus
ить в уже подготовленную таблицу с кластерным индексом или залить кучу (что явно быстрее) а уже после построить индекс?
Если лить с минимальным логированием, сразу в кластерный индекс будет быстрее.

Kopilogus
Таблица по требованию бизнеса должна хранить данные за 5 лет, один месяц примерно 350 млн записей, средний размер строки 0.5KB данные не могут модифицироваться, т.е. только INSERT с последующим (не факт что частым SELECT - разбор инцидентов по запросу)
Если через пять лет не хотите проблем с удалением, то лучше секционировать сразу.

Kopilogus
Селекты в основном 3х видов: как правило у всех присутствует поиск по TimeStamp
Если поиск по TimeStamp по диапазону, то добавлять какие-либо поля после этого поля в ключ смысла не имеет.

Kopilogus
+ сжатие данных?
sp_estimate_compression_savings в помощь.


вчера пробовал лить в уже подготовленные секции с функцией по полю TimeStamp - каждый год в одну секцию, сутки работало, не дождался... Правда таблицу джойнил со справочниками, чтобы выдергивать ServerID и MethodID и лил по 50 тыс записей, т.к. все это на боевом сервере, чтобы не нагружать систему.

удалять будем полюбому, т.к. схдшники не дадут столько ресурсов под логи..

в целом идея понятна, спасибо!
2 фев 18, 20:16    [21161841]     Ответить | Цитировать Сообщить модератору
 Re: Update новой колонки 2.5 млрд строк  [new]
Kopilogus
Member

Откуда:
Сообщений: 49
Коллеги, еще вопрос в сабж:

у кого есть какие мысли насчет использования поля [id] bigint IDENTITY(1,1) PK_Clastered повсеместно в базе на больших таблицах (транзакции, логи и т.п.) я чел новый в конторе, разрабы внятно не могут ответить на этот вопрос, пока база была маленькая все летало, сейчас куча траблов с блокировками и производительностью - эти поля в селектах не используют...
2 фев 18, 20:26    [21161863]     Ответить | Цитировать Сообщить модератору
 Re: Update новой колонки 2.5 млрд строк  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33700
Блог
раз это логи, то колоночный индекс сделайте, если бизнес-логика это допускает,
весить будет в разы меньше, чем раньше
2 фев 18, 21:45    [21162022]     Ответить | Цитировать Сообщить модератору
 Re: Update новой колонки 2.5 млрд строк  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
Гавриленко Сергей Алексеевич
Если лить с минимальным логированием, сразу в кластерный индекс будет быстрее.

Если через пять лет не хотите проблем с удалением, то лучше секционировать сразу.
Хм, а если таблица будет секционироваться, разве не быстрее лить в таблицы кучи по секциям, потом строить на них индекс, и потом присоединять к секционированной таблице?

Тут, правда, вопрос, как выбирать данные в диапазоне по полю TimeStamp, индекса же по нему нет, да и был бы - не легче, получать данные лукапом...
2 фев 18, 22:28    [21162096]     Ответить | Цитировать Сообщить модератору
 Re: Update новой колонки 2.5 млрд строк  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
Kopilogus
Стоит ли использовать секционирование + сжатие данных?
Будет медленный bulk insert
2 фев 18, 22:31    [21162099]     Ответить | Цитировать Сообщить модератору
 Re: Update новой колонки 2.5 млрд строк  [new]
Kopilogus
Member

Откуда:
Сообщений: 49
Критик
раз это логи, то колоночный индекс сделайте, если бизнес-логика это допускает,
весить будет в разы меньше, чем раньше


Есть идея оставить только кластерный индекс в таблице log_htttpRequestArchive, предполагая что запросы будут крайне редкими в этой таблице. Оперативные логи будем хранить за сутки *куда навешано много селект-счетчиков* и будет таблица log_httpRequestHistory которая будет хранить актуальные логи за 3 месяца, предполагаем что селект запросы будут относительно не частыми..
2 фев 18, 23:18    [21162178]     Ответить | Цитировать Сообщить модератору
 Re: Update новой колонки 2.5 млрд строк  [new]
Kopilogus
Member

Откуда:
Сообщений: 49
alexeyvg
Гавриленко Сергей Алексеевич
Если лить с минимальным логированием, сразу в кластерный индекс будет быстрее.

Если через пять лет не хотите проблем с удалением, то лучше секционировать сразу.
Хм, а если таблица будет секционироваться, разве не быстрее лить в таблицы кучи по секциям, потом строить на них индекс, и потом присоединять к секционированной таблице?

Тут, правда, вопрос, как выбирать данные в диапазоне по полю TimeStamp, индекса же по нему нет, да и был бы - не легче, получать данные лукапом...


вопрос со старыми архивными данными, вернее селект к ним, сейчас не столько важен, сколько свободное место на железном сервере где хранят логи, каждый день приносит прирост порядка 20Гиг, т.е.уже пожар пора тушить, разрабы не особо парятся что лить в логи, как DBA и кодер в прошлом могу сказать, что можно было бы сделать лучше, но бизнес логика диктует свои законы удалять нельзя, вот и извращаемся с тем что имеем...
2 фев 18, 23:24    [21162185]     Ответить | Цитировать Сообщить модератору
 Re: Update новой колонки 2.5 млрд строк  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
Kopilogus
alexeyvg
пропущено...
Хм, а если таблица будет секционироваться, разве не быстрее лить в таблицы кучи по секциям, потом строить на них индекс, и потом присоединять к секционированной таблице?

Тут, правда, вопрос, как выбирать данные в диапазоне по полю TimeStamp, индекса же по нему нет, да и был бы - не легче, получать данные лукапом...


вопрос со старыми архивными данными, вернее селект к ним, сейчас не столько важен, сколько свободное место на железном сервере где хранят логи, каждый день приносит прирост порядка 20Гиг, т.е.уже пожар пора тушить, разрабы не особо парятся что лить в логи, как DBA и кодер в прошлом могу сказать, что можно было бы сделать лучше, но бизнес логика диктует свои законы удалять нельзя, вот и извращаемся с тем что имеем...
Это всё понятно. Я говорил о процессе первоначальной переливки, о том, как за минимальное время на имеющемся оборудовании и месте на стораджах изменить таблицу.

А так, вообще, если данные не удаляются, то может и секционирование не нужно?....
3 фев 18, 11:48    [21162585]     Ответить | Цитировать Сообщить модератору
 Re: Update новой колонки 2.5 млрд строк  [new]
Kopilogus
Member

Откуда:
Сообщений: 49
alexeyvg
Kopilogus
пропущено...


вопрос со старыми архивными данными, вернее селект к ним, сейчас не столько важен, сколько свободное место на железном сервере где хранят логи, каждый день приносит прирост порядка 20Гиг, т.е.уже пожар пора тушить, разрабы не особо парятся что лить в логи, как DBA и кодер в прошлом могу сказать, что можно было бы сделать лучше, но бизнес логика диктует свои законы удалять нельзя, вот и извращаемся с тем что имеем...
Это всё понятно. Я говорил о процессе первоначальной переливки, о том, как за минимальное время на имеющемся оборудовании и месте на стораджах изменить таблицу.

А так, вообще, если данные не удаляются, то может и секционирование не нужно?....


Вот я и сам теперь в сомнениях нужен ли мне этот геморрой...
4 фев 18, 18:47    [21164723]     Ответить | Цитировать Сообщить модератору
 Re: Update новой колонки 2.5 млрд строк  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Гавриленко Сергей Алексеевич
Kopilogus
ить в уже подготовленную таблицу с кластерным индексом или залить кучу (что явно быстрее) а уже после построить индекс?
Если лить с минимальным логированием, сразу в кластерный индекс будет быстрее.


автор
вчера пробовал лить в уже подготовленные секции с функцией по полю TimeStamp - каждый год в одну секцию, сутки работало, не дождался...


Гавриленко писал про минимальное логирование.
для кластерного это означает лить с таблоком,
да еще и отсортированные данные.
у вас ни того, ни другого нет
5 фев 18, 12:20    [21166436]     Ответить | Цитировать Сообщить модератору
 Re: Update новой колонки 2.5 млрд строк  [new]
Kopilogus
Member

Откуда:
Сообщений: 49
В итоге залил в уже подготовленную таблицу с кластерным индексом и включенным Page сжатием.
Процесс занял 20 часов, эффект от сжатия составил 260%.
9 фев 18, 11:44    [21179264]     Ответить | Цитировать Сообщить модератору
 Re: Update новой колонки 2.5 млрд строк  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33700
Блог
нужно был кластерный колоночный индекс делать, все равно же это логи,
тогда эффект занятому по месту приближался бы к х10
9 фев 18, 12:04    [21179400]     Ответить | Цитировать Сообщить модератору
 Re: Update новой колонки 2.5 млрд строк  [new]
архивариус
Member

Откуда:
Сообщений: 150
Критик
нужно был кластерный колоночный индекс делать, все равно же это логи,
тогда эффект занятому по месту приближался бы к х10

+1 (стоило посмотреть в сторону колумсторе)
и для логов можно получить выигрыш в скорости выполнения запросов потом ( с агрегированием которые)
9 фев 18, 12:27    [21179528]     Ответить | Цитировать Сообщить модератору
 Re: Update новой колонки 2.5 млрд строк  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Kopilogus
В итоге залил в уже подготовленную таблицу с кластерным индексом и включенным Page сжатием.
Процесс занял 20 часов, эффект от сжатия составил 260%.

балк в таблицу со сжатием всегда медленнее,
чем просто балк + компрессия потом.

К сообщению приложен файл. Размер - 13Kb
9 фев 18, 12:29    [21179536]     Ответить | Цитировать Сообщить модератору
 Re: Update новой колонки 2.5 млрд строк  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33700
Блог
Yasha123,

при чем тут это?
"потом" можно как включить сжатие, так и создать колоночный индекс с COLUMNSTORE_ARCHIVE

а можно было создать промежуточную таблицу, в которой все это делать, а потом переключать ее как секцию в основную архивную
9 фев 18, 13:05    [21179673]     Ответить | Цитировать Сообщить модератору
 Re: Update новой колонки 2.5 млрд строк  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33700
Блог
Yasha123,

я думал, вы мне ответили, не то отписал
9 фев 18, 13:06    [21179679]     Ответить | Цитировать Сообщить модератору
 Re: Update новой колонки 2.5 млрд строк  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4740
Kopilogus,

Версия какая? В районе 2014 появился функционал оптимального добавления колонки с дефолтовым или нулловым зачением, при которым не происходит перестроение таблиц.
9 фев 18, 13:39    [21179853]     Ответить | Цитировать Сообщить модератору
 Re: Update новой колонки 2.5 млрд строк  [new]
Kopilogus
Member

Откуда:
Сообщений: 49
a_voronin,

Microsoft SQL Server 2014 (SP2-CU7) (KB4032541) - 12.0.5556.0 (X64)
Aug 17 2017 12:07:38
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

По условию перед заливкой нужно было еще заменить поля ServerName И MethodName на ServerID и MethodID:
left join к 2м справочным таблицам с проверкой ISNULL(ServerID, 0) никто не отменял :)
9 фев 18, 13:51    [21179919]     Ответить | Цитировать Сообщить модератору
 Re: Update новой колонки 2.5 млрд строк  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
a_voronin
Kopilogus,

Версия какая? В районе 2014 появился функционал оптимального добавления колонки с дефолтовым или нулловым зачением, при которым не происходит перестроение таблиц.

фича появилась в 2012.
но при чем туд добавление колонки с дефолтом-константой?
он же меняет тип колонки путем переноса имеющегося
в подготовленную колонку другого типа.
какая уж тут константа
9 фев 18, 13:56    [21179943]     Ответить | Цитировать Сообщить модератору
 Re: Update новой колонки 2.5 млрд строк  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4740
Yasha123
a_voronin
Kopilogus,

Версия какая? В районе 2014 появился функционал оптимального добавления колонки с дефолтовым или нулловым зачением, при которым не происходит перестроение таблиц.

фича появилась в 2012.
но при чем туд добавление колонки с дефолтом-константой?
он же меняет тип колонки путем переноса имеющегося
в подготовленную колонку другого типа.
какая уж тут константа


Добавить новые колонки
Порционно перенеси туда значения
Порционно обнулить старые
Попробовать дропнуть пустые -- возможно будет не так страшно
9 фев 18, 14:42    [21180207]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить