Коллеги,

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




Есть таблица: 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 часов ((

Есть мысли как сделать быстрее?

Гавриленко Сергей Алексеевич,

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

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

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

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

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

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

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

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

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

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

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

в целом идея понятна, спасибо!

Коллеги, еще вопрос в сабж:

у кого есть какие мысли насчет использования поля [id] bigint IDENTITY(1,1) PK_Clastered повсеместно в базе на больших таблицах (транзакции, логи и т.п.) я чел новый в конторе, разрабы внятно не могут ответить на этот вопрос, пока база была маленькая все летало, сейчас куча траблов с блокировками и производительностью - эти поля в селектах не используют...

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

Есть идея оставить только кластерный индекс в таблице log_htttpRequestArchive, предполагая что запросы будут крайне редкими в этой таблице. Оперативные логи будем хранить за сутки *куда навешано много селект-счетчиков* и будет таблица log_httpRequestHistory которая будет хранить актуальные логи за 3 месяца, предполагаем что селект запросы будут относительно не частыми..

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

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

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

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

alexeyvgKopilogusпропущено...


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

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

Вот я и сам теперь в сомнениях нужен ли мне этот геморрой...

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

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) никто не отменял :)