Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3 4   вперед  Ctrl      все
 Рефикторинг - с чего начать?  [new]
JustCurious
Member

Откуда: UA
Сообщений: 94
Добрый день.

Подскажите, в правильном ли направлении я двигаюсь?

Есть БД под управлением MS SQL Server 2008 R2 (Enterprise Edition). В двух словах - в основную БД сливаются данные с клиентских БД для анализа данных. Клиентские БД находятся на ветр.станциях и содержат показания датчиков. С ростом кол-ва станций всё отчётливее начало проявляться деградация производительности (в частности, благодаря GUID'ам AS Primary Keys и кластерных индексах на них). Год назад БД была подфикшена со стороны физики (стандартные пункты - добавление индексов, джобы по дефрагментации, переписывание тяжёлые запросов, разнос по дискам файлов данных и журнала, вынос tempdb на отдельный диск, сегментирование больших таблиц и т.д., изменение степени заполнения индексов, чтоб замедлить фрагментацию и т.д.)

Теперь ребята снова обратились, дословно "I think the idea is not to optimize on each individual SQL stmt, it's more to see if there is smth generally we can change in DB structure to give a performance boost" - изменение структуры БД.
Возможно, с СУБД MS SQL они будут спрыгивать, т.е. использовать какие-то подходы, специфичные для данной СУБД не рекомендуется.

Я наметил себе след. пункты:

1. поснимать всё-таки показания текущего состояния БД, чтоб вырисовать картину где именно сервер затыкается (потрейсить профайлером продакшн-БД, снять счётчики производительности, вытянуть инфу с dmv-тфблиц)
2. разобраться в структуре БД, как она взаимодействует с приложениями, унифицировать интерфейс БД <->приложение (например, перевести всё на хранимые процедуры, хотя, если СУБД может меняться в будущем, этот пункт под вопросом)
3. исходя из найденных боттлнеков в п.1 уже предпринимать какие-то действия по нормализации/денормализации данных и т.д.

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

Спасибо
15 сен 15, 12:44    [18150756]     Ответить | Цитировать Сообщить модератору
 Re: Рефикторинг - с чего начать?  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
JustCurious,

а как выражаются "тормоза" из текста ясно только, что есть желание оптимизировать? чтобы не было, как в наших больницах. пришел с прибитым пальцем, а тебя отправляют сдать анализ мочи и пройти флюрографию.
15 сен 15, 13:11    [18150900]     Ответить | Цитировать Сообщить модератору
 Re: Рефикторинг - с чего начать?  [new]
a_voronin
Member

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

1) Снимите профайлером трассу с фильтром по продолжительности запроса и сохраните трассу в БД на другом серваке. Не сохраняйте на прямую (сняли, сохранили в файл, скопировали на другой сервак, загрузили в другой сервер БД).

2) После этого проаггрегируйте её и найдите самые проблемные запросы.

3) Оптимизируйте их.
15 сен 15, 13:18    [18150953]     Ответить | Цитировать Сообщить модератору
 Re: Рефикторинг - с чего начать?  [new]
JustCurious
Member

Откуда: UA
Сообщений: 94
churupaha,

Вернулся с kick-off митинга, вот что удалось узнать. Ребята готовятся к потенциальным проблемам, связанным с ростом БД. Т.е. на данный момент всё еще более-менее сносно. Однако, должны зайти пару новых клиентов с большим кол-вом станций и тестовый сервер разработчиков уже начинает показывать лаги при эмуляции такого кол-ва станций.

Я БД ещё не видел в глаза, но корень зла вроде как обнаружен - есть некая табличка, хранящая исторические данные (они должны сохраняться в течение 30-40 лет) - показания всех турбин всех клиентов за всё время. Эта табличка и занимает 95% объема БД.

Первое, что приходит на ум
1. выносить данные старше какого-то периода из этой таблицы в архивную таблицу/другую бд.
2. создавать отдельную таблицу под каждую турбину + выносить архивные данные куда-то

Основные опасения клиента, как я понял - разрастание БД из-за хранения исторических данных. Опять-таки, они думают над изменением типа данных с varchar на сhar (для повышения производительности), что в свою очередь еще увеличит размер БД. Так что надо это всё каким-то образом замерить (как именно? посчитать на сколько больше байт считывается при изменении типа данных?) и понять, стоит ли овчинка выделки.
15 сен 15, 13:47    [18151179]     Ответить | Цитировать Сообщить модератору
 Re: Рефикторинг - с чего начать?  [new]
JustCurious
Member

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

Да, спасибо, трассу буду делать обязательно.
15 сен 15, 13:54    [18151229]     Ответить | Цитировать Сообщить модератору
 Re: Рефикторинг - с чего начать?  [new]
что приходит на у
Guest
JustCurious,

можно сразу начать с секционирования, но я бы при таком соотношении объемов всю историю в другую базу выдрал, а в текущей только последние данные хранил, соответственно, с периодической переливкой (раз в год или реже/чаще)

маленькая пусть остается маленькой и шустрой. и с бэкапами попроще/удобнее будет.
15 сен 15, 13:56    [18151245]     Ответить | Цитировать Сообщить модератору
 Re: Рефикторинг - с чего начать?  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
JustCurious
уже начинает показывать лаги


вот это бы расшифровали по подробнее. тогда можно о чем-то говорить.

JustCurious
Основные опасения клиента, как я понял - разрастание БД из-за хранения исторических данных.


сами по себе данные лежат ну и пусть лежат. partitioning вам поможет управлять ими. важно как вы их планируете обрабатывать. может, вам понадобится для аналитики грузить их в другое место

JustCurious
изменением типа данных с varchar на сhar (для повышения производительности),


а может и для замедления, читать-то больше прийдется, но вы это и сами понимаете

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


замеряйте сколько страниц занимает таблица с varchar и char. вы, скорее, проиграете, вам ведь потом данные только читать надо будет, и меняться они не будут.
15 сен 15, 14:04    [18151298]     Ответить | Цитировать Сообщить модератору
 Re: Рефикторинг - с чего начать?  [new]
JustCurious
Member

Откуда: UA
Сообщений: 94
что приходит на у
JustCurious,

можно сразу начать с секционирования, но я бы при таком соотношении объемов всю историю в другую базу выдрал, а в текущей только последние данные хранил, соответственно, с периодической переливкой (раз в год или реже/чаще)

маленькая пусть остается маленькой и шустрой. и с бэкапами попроще/удобнее будет.


а, да, с секционированием подход отпадает )) Как оказалось, Enterprise - только у разработчика. Однако клиенты могут покупать софт себе, разворачивать систему на своей стороне (т.е. главная БД на их сервере и клиентские БД на каждой турбине). Как мне сказали, не все клиенты готовы покупать Enterprise, поэтому ориентироваться надо на Standard (как главная БД) и Express (как клиентская).
15 сен 15, 14:13    [18151359]     Ответить | Цитировать Сообщить модератору
 Re: Рефикторинг - с чего начать?  [new]
JustCurious
Member

Откуда: UA
Сообщений: 94
churupaha
JustCurious
уже начинает показывать лаги


вот это бы расшифровали по подробнее. тогда можно о чем-то говорить.



Да, согдласен, как только будут трассы на руках - многое прояснится.
15 сен 15, 14:15    [18151368]     Ответить | Цитировать Сообщить модератору
 Re: Рефикторинг - с чего начать?  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
автор
Возможно, с СУБД MS SQL они будут спрыгивать, т.е. использовать какие-то подходы, [b]специфичные для данной СУБД не рекомендуется.[/b]


автор
есть некая табличка, хранящая исторические данные (они должны сохраняться в течение 30-40 лет


я не знаю, как часто с нимаются показания с ветряков и сколько их у вас, но если объймы большие за 30-40 лет. то это взаимоисключающие хотелки. вам прийдется завязаться на механизмы предлагаемые конкретной СУБД.

- partitioning
- online операции на секциях
- page compression (для секций не текущего периода)
- partial backups/partial restore
- ... м. б. columnstore (с) эта мантра сейчас вызовет в тред нужного человека.
...
15 сен 15, 14:17    [18151380]     Ответить | Цитировать Сообщить модератору
 Re: Рефикторинг - с чего начать?  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
JustCurious
а, да, с секционированием подход отпадает )) Как мне сказали, не все клиенты готовы покупать Enterprise, поэтому ориентироваться надо на Standard (как главная БД) и Express (как клиентская).


есть partitioned view в чем то даже лучше partitioned tables

секциями будут отдельные таблицы объединенные view. оптимизатор это понимает (при этом архивные таблицы могут иметь схему, отличную от online таблицы):

1) таблицы одной и той же базы
2) таблицы разных баз одного инстанса
3) таблицы в базах разных инстансов
...

и можете делать в случае (2) и (3) partial backup'ы... перемещать в разные места. это что касается управления. а что касается обработки этот вопрос самый главный 18151368
15 сен 15, 14:23    [18151414]     Ответить | Цитировать Сообщить модератору
 Re: Рефикторинг - с чего начать?  [new]
Konst_One
Member

Откуда:
Сообщений: 11621
я думаю, что ТС нам самое интересное не расказал: что и сколько данных хранят клиентские сервера и как осуществляется обмен с главным сервером. по фразам из первого сообщения видимно у них там репликация настроена. может в этом у них главная проблема?
15 сен 15, 14:26    [18151423]     Ответить | Цитировать Сообщить модератору
 Re: Рефикторинг - с чего начать?  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Это 17473098 в дополнение к 18151414
15 сен 15, 14:28    [18151441]     Ответить | Цитировать Сообщить модератору
 Re: Рефикторинг - с чего начать?  [new]
JustCurious
Member

Откуда: UA
Сообщений: 94
Konst_One
я думаю, что ТС нам самое интересное не расказал: что и сколько данных хранят клиентские сервера и как осуществляется обмен с главным сервером. по фразам из первого сообщения видимно у них там репликация настроена. может в этом у них главная проблема?


Вот, узнал. С каждой турбины пишется 300 переменных каждые 10 минут. Одна переменная вычисляется на основании 5 параметров (думаю, вычисляется на клиенте, в главную БД отправляется уже результат вычисления переменной). Вроде не так страшно. Опять-таки, на руках ничего нет с боевой БД, поэтому судить не берусь.
15 сен 15, 14:50    [18151522]     Ответить | Цитировать Сообщить модератору
 Re: Рефикторинг - с чего начать?  [new]
JustCurious
Member

Откуда: UA
Сообщений: 94
churupaha
есть partitioned view в чем то даже лучше partitioned tables


Вооо, спасибо! Совсем забыл о них, давно не работал. Кажется, это то, что надо.
15 сен 15, 14:52    [18151535]     Ответить | Цитировать Сообщить модератору
 Re: Рефикторинг - с чего начать?  [new]
Konst_One
Member

Откуда:
Сообщений: 11621
т.е. есть некий сервис на главном сервере с которым посредством http/https работают клиентские сервера?

- объём одного сеанса отправки данных до 1 мегабайта?
- клиентские базы как обслуживаются?
- или там у них копится инфа пока база в потолок 10Гб для express версии не упрётся?


если примерно так , то клиенты независимы от главного сервера и основная проблема - это сбор и хранение информации.
кстати, как она используется на главном сервере? OLAP?
15 сен 15, 14:56    [18151549]     Ответить | Цитировать Сообщить модератору
 Re: Рефикторинг - с чего начать?  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
JustCurious
churupaha
есть partitioned view в чем то даже лучше partitioned tables


Вооо, спасибо! Совсем забыл о них, давно не работал. Кажется, это то, что надо.


хранить 30-40 лет почему собираетесь? из-за бюрократии (законы требуют) или аналитики (считать что-то по всем данным за 30-40 лет)? в первом случае один подход, во втором другой... можно еще хранить рассчеты итоговые, или частичные рассчеты, из которых потом собирать итоги и т. п..
15 сен 15, 14:57    [18151553]     Ответить | Цитировать Сообщить модератору
 Re: Рефикторинг - с чего начать?  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
JustCurious
Вот, узнал. С каждой турбины пишется 300 переменных каждые 10 минут. Одна переменная вычисляется на основании 5 параметров (думаю, вычисляется на клиенте, в главную БД отправляется уже результат вычисления переменной). Вроде не так страшно. Опять-таки, на руках ничего нет с боевой БД, поэтому судить не берусь.


в год с ветряка 52560 строк с 300 колонками (это не вы тему недавно создавали), хм, а сколько ветряков?
15 сен 15, 15:04    [18151579]     Ответить | Цитировать Сообщить модератору
 Re: Рефикторинг - с чего начать?  [new]
JustCurious
Member

Откуда: UA
Сообщений: 94
churupaha
JustCurious
пропущено...


Вооо, спасибо! Совсем забыл о них, давно не работал. Кажется, это то, что надо.


хранить 30-40 лет почему собираетесь? из-за бюрократии (законы требуют) или аналитики (считать что-то по всем данным за 30-40 лет)? в первом случае один подход, во втором другой... можно еще хранить рассчеты итоговые, или частичные рассчеты, из которых потом собирать итоги и т. п..

Главная БД используется как OLAP, на основе её данных проводятся разные статистические вычисления.
15 сен 15, 16:31    [18152162]     Ответить | Цитировать Сообщить модератору
 Re: Рефикторинг - с чего начать?  [new]
Konst_One
Member

Откуда:
Сообщений: 11621
JustCurious
Главная БД используется как OLAP, на основе её данных проводятся разные статистические вычисления.


тогда вам надо СХД строить, потому что ROLAP на постоянно обновляемых данных с таким объемом будет жутко плохо работать
15 сен 15, 16:34    [18152176]     Ответить | Цитировать Сообщить модератору
 Re: Рефикторинг - с чего начать?  [new]
JustCurious
Member

Откуда: UA
Сообщений: 94
churupaha
JustCurious
Вот, узнал. С каждой турбины пишется 300 переменных каждые 10 минут. Одна переменная вычисляется на основании 5 параметров (думаю, вычисляется на клиенте, в главную БД отправляется уже результат вычисления переменной). Вроде не так страшно. Опять-таки, на руках ничего нет с боевой БД, поэтому судить не берусь.


в год с ветряка 52560 строк с 300 колонками (это не вы тему недавно создавали), хм, а сколько ветряков?


Нет, не я )

Оказывается, они сохраняют каждую переменную в отдельной строке. Причина - количество параметров величина конфигурируемая, на одной турбине это может быть 40 переменных, на другой - 400. Получается, если по 300 строк за одну загрузку, то за год набегает 15 млн. записей с турбины. Думаю, насколько оправданным будет подход с реализацией таблицы с 400 колонками. NULL-значения ведь не занимают места на странице, только в bitmap'ах выставляется флаг.
15 сен 15, 16:48    [18152268]     Ответить | Цитировать Сообщить модератору
 Re: Рефикторинг - с чего начать?  [new]
o-o
Guest
JustCurious
NULL-значения ведь не занимают места на странице, только в bitmap'ах выставляется флаг.

если поле фиксированной длины, то еще как занимает.
или у вас там сплошные varchar/varbinary?
15 сен 15, 17:02    [18152358]     Ответить | Цитировать Сообщить модератору
 Re: Рефикторинг - с чего начать?  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
JustCurious
churupaha
пропущено...


в год с ветряка 52560 строк с 300 колонками (это не вы тему недавно создавали), хм, а сколько ветряков?


Нет, не я )

Оказывается, они сохраняют каждую переменную в отдельной строке. Причина - количество параметров величина конфигурируемая, на одной турбине это может быть 40 переменных, на другой - 400. Получается, если по 300 строк за одну загрузку, то за год набегает 15 млн. записей с турбины. Думаю, насколько оправданным будет подход с реализацией таблицы с 400 колонками. NULL-значения ведь не занимают места на странице, только в bitmap'ах выставляется флаг.


Что лучше - зависит от ваших запросов к этой таблице. Если у вас показания - это fixed типы, то null вас не спасет для обычных колонок. NULL'ы для них занимают столько же места, сколько значения. Но по той же ссылке выше обсуждаются и sparse columns. Только ознакомьтесь с ограничениями.
15 сен 15, 17:02    [18152361]     Ответить | Цитировать Сообщить модератору
 Re: Рефикторинг - с чего начать?  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
вы самое главное не ответили - как проявляются т. н. "тормоза". как это ощущается, когда разработчики тестируют предполагаемую нагрузку на том, что есть. а то мы тыкаем пальцем в небо по большому счету.
15 сен 15, 17:07    [18152388]     Ответить | Цитировать Сообщить модератору
 Re: Рефикторинг - с чего начать?  [new]
JustCurious
Member

Откуда: UA
Сообщений: 94
churupaha
вы самое главное не ответили - как проявляются т. н. "тормоза". как это ощущается, когда разработчики тестируют предполагаемую нагрузку на том, что есть. а то мы тыкаем пальцем в небо по большому счету.

Сегодня ночью запущу сбор данных
15 сен 15, 17:28    [18152478]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3 4   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить