Работа над ошибками аппаратуры на стороне SQL сервера и пользе нагрузочных тестов

в 16:36, , рубрики: cursor, Microsoft SQL Server, oracle, snmp, sql, tsql

Некоторые наши пользователи стали сообшать что репорты иногда возврашают значение превышаюшее 100% для показа роста данных.

При этом выяснилось что сервис, доставляюший данные от аппаратуры иногда делает пропуски в значениях.
Там где сервер читал аппаратуру напрямую, вместо значений вставлял NULL, а там где читал с использованием SNMP, там вставлял 0.

То есть ряд значений счетчика был как: 4, 10, 20, NULL, NULL, 31, 0, 0, 0, 50 а теперь надо 4, 10, 20, 20, 20, 31, 31, 31, 31, 50

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

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

В начале, поскольку жаловались на один репорт, решение было простым — починить те данные, которые участвовали в отчете. Всеобьемлюший анализ сделать не могли, поскольку свои базы кастомеры (из-за обьемов) слать не могли, поэтому решение сделали на основании логов.

Первое решение был очевидным и простым.

для MSSQL 2008:

Update curr set curr.dev_counter_color=coalesce(curr.dev_counter_color, prev.dev_counter_color) from
device_counter  curr  left join device_counter prev on curr.dev_id = prev.dev_id and curr.dev_counter_color is null
   and prev.dev_counter_date = (select max(sub.dev_counter_date) from device_counter sub where sub.dev_counter_date < curr.dev_counter_date and dev_counter_color is not null);

для Oracle:

merge into DEVICE_COUNTER t using ( select ROWID as rid, last_value(dev_counter_duplex ignore nulls ) 
  over (partition by dev_id order by dev_counter_date, dev_counter_id) as new_cnt_value from DEVICE_COUNTER t ) v
  on (t.rowid = v.rid) when matched then update set dev_counter_duplex = new_cnt_value

И не прошло и полгода, как появился пользователь с базой на десяток тысяч девайсов с данными за несколько лет (около пяти миллионов записей) и стал жаловатся что не смог дождатся конца апгрейта базы.

Одновремено пришло письмо из техсапорта решивших предложить починить базу со всеми 57 счетчиками.

Казалось бы — чинишь запросом колонку за колонкой да и дело с концом.

Когда мы все таки стянули базу у клиента, оказалось запрос работает на одной колонке почти две минуты на средненьком виртуальном сервере, а слабенький лаптоп клиента, жаловавшегося на скорость апдейта, по нашим подсчетам делал все все 57 sql запросов за ~52 часа!..

По обыкновению мы рекомендуем нашим клиентам работать с техникой, параметры которой указаны в technical requirements, но неужели все так плохо в реляционных языках?

Пришлось вспомнить про старый добрый курсор, — собственно задача простая — пройтись по нескольким миллионам записей и если есть чего править — то править.

Если бы пропуски данных были во всех колонках — то простой проход с апдейтом всех строк занимает примерно тоже время что и первоначальный вариант — 38 минут против 92 при реляционном подходе…

И только тот факт, что пропуски редки — сократило апгрейд до 2! минут — это для 22,000 строк из 3 миллионнов.

Финальный код TSQL. Kоличество полей уменьшил до 2, чтобы код таким громозким не выглядел:

BEGIN
DECLARE @updated int = 0;
DECLARE @cur_dev_id int = NULL, @cur_id int = NULL
DECLARE @cur_counter_total_color int = NULL, @cur_counter_total_mono int = NULL;

DECLARE @next_dev_id int = NULL, @next_id int = NULL
DECLARE @next_counter_total_color int = NULL, @next_counter_total_mono int = NULL;

DECLARE UPDCURSOR CURSOR for select dev_id, dev_counter_id, 
dev_counter_total_color,
dev_counter_total_mono from device_counter d  order by dev_id, dev_counter_date
    
OPEN UPDCURSOR
FETCH NEXT FROM UPDCURSOR into @cur_dev_id, @cur_id, @cur_counter_total_color;

WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH NEXT FROM UPDCURSOR INTO @next_dev_id, @next_id
		, @next_counter_total_color, @next_counter_total_mono

IF @@FETCH_STATUS = 0 AND @cur_dev_id = @next_dev_id AND (
	((@next_counter_total_color IS NULL AND @cur_counter_total_color IS NOT NULL) OR (@next_counter_total_color = 0 AND (@cur_counter_total_color > 0 OR @cur_counter_total_color Is NULL ))) OR
	((@next_counter_total_mono IS NULL AND @cur_counter_total_mono IS NOT NULL) OR (@next_counter_total_mono = 0 AND (@cur_counter_total_mono > 0 OR @cur_counter_total_mono Is NULL ))) )
BEGIN
    SELECT
	@cur_counter_total_color = (CASE WHEN @next_counter_total_color IS NULL OR @next_counter_total_color = 0 THEN @cur_counter_total_color ELSE @next_counter_total_color END),
	@cur_counter_total_mono = (CASE WHEN @next_counter_total_mono IS NULL OR @next_counter_total_mono = 0 THEN @cur_counter_total_mono ELSE @next_counter_total_mono END);

    SET @updated = @updated + 1
    UPDATE device_counter SET 
		dev_counter_total_color = @cur_counter_total_color,
		dev_counter_total_mono = @cur_counter_total_mono
	WHERE CURRENT OF UPDCURSOR

END ELSE
	BEGIN
		SELECT
			@cur_counter_total_color = @next_counter_total_color,
			@cur_counter_total_mono = @next_counter_total_mono;
	END
	SET @cur_dev_id = @next_dev_id
END
CLOSE UPDCURSOR
DEALLOCATE UPDCURSOR
END

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

И особенно важно делать не только юнит тесты но нагрузочные тесты на максимально возможных обьемах.

Автор: BalinTomsk

Источник

* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js