Гигантская тормозная база, секционирование и ускорение
От: Слава  
Дата: 18.01.23 02:03
Оценка: 1 (1)
Есть база MS SQL на полтора терабайта, а может и больше, я не видел сам прод.

В ней имеется 9 миллионов клиентов, у которых имеются сотни тысяч транзакций (в бизнес-смысле). Это данные о проезде автомобилей через ворота, условно говоря.

Всякие гриды в тормозном веб-интерфейсе тормозят ещё сильнее во время выборки. А выборка в типовой хранимке происходит так: заполняем временную таблицу уймой запросов по всем данным заданного клиента, затем фильтруем временную таблицу по значениям фильтра из грида, по всем 50 колонкам, по всему миллиону строк, вот в таком духе:

                AND (
                    (@ipv_biTransactionID IS NULL AND @ipv_vcTransactionOperator IS NULL)
                    OR (@ipv_vcTransactionOperator = 's%' AND Y.CustTrxnID LIKE (CAST(@ipv_biTransactionID AS VARCHAR(MAX)) + '%'))
                    OR (@ipv_vcTransactionOperator = 'e%' AND Y.CustTrxnID LIKE ('%' + CAST(@ipv_biTransactionID AS VARCHAR(MAX))))
                    OR (@ipv_vcTransactionOperator = '=' AND Y.CustTrxnID = @ipv_biTransactionID)
                    OR (@ipv_vcTransactionOperator = '!=' AND Y.CustTrxnID != @ipv_biTransactionID)
                    OR (@ipv_vcTransactionOperator = 'IN' AND Y.CustTrxnID LIKE ('%' + CAST(@ipv_biTransactionID AS VARCHAR(MAX)) + '%'))
                    OR (@ipv_vcTransactionOperator = 'NOT IN' AND Y.CustTrxnID NOT LIKE ('%' + CAST(@ipv_biTransactionID AS VARCHAR(MAX)) + '%'))
                )            
                AND (
                    (@ipv_iTrxnStatus IS NULL AND @ipv_vcTrxnStatusOperator IS NULL)
                    OR (@ipv_vcTrxnStatusOperator = 's%' AND Y.TrxnStatusID LIKE (CAST(@ipv_iTrxnStatus AS VARCHAR(MAX)) + '%'))
                    OR (@ipv_vcTrxnStatusOperator = 'e%' AND Y.TrxnStatusID LIKE ('%' + CAST(@ipv_iTrxnStatus AS VARCHAR(MAX))))
                    OR (@ipv_vcTrxnStatusOperator = '=' AND Y.TrxnStatusID = @ipv_iTrxnStatus)
                    OR (@ipv_vcTrxnStatusOperator = '!=' AND Y.TrxnStatusID != @ipv_iTrxnStatus)
                    OR (@ipv_vcTrxnStatusOperator = 'IN' AND Y.TrxnStatusID LIKE ('%' + CAST(@ipv_iTrxnStatus AS VARCHAR(MAX)) + '%'))
                    OR (@ipv_vcTrxnStatusOperator = 'NOT IN' AND Y.TrxnStatusID NOT LIKE ('%' + CAST(@ipv_iTrxnStatus AS VARCHAR(MAX)) + '%'))
                )        
            WHERE x.RowNumber BETWEEN @v_iStartIndex AND @v_iEndIndex
            ORDER BY x.RowNumber;



Транзакции могут быть переданы между клиентами, но чаще всего они происходят в рамках одного клиента. Исторические данные никак от текущих не отделены, в базе есть и 2005 год.

Как бы эту базу распилить, чтобы не тормозила? Если у большей части самых гигантских таблиц есть accountId, то логично было бы применить секционирование по этому id. Но 9 миллионов секций не поддерживаются MS SQL, это слишком много.

Секционирование можно сделать например по 2 последним цифрам в id клиента. Хорошая ли это идея? Что вообще с такой базой можно сделать?
Отредактировано 18.01.2023 2:19 Слава . Предыдущая версия .
Re: Гигантская тормозная база, секционирование и ускорение
От: capgreen  
Дата: 18.01.23 10:13
Оценка: +3
Здравствуйте, Слава, Вы писали:

1. Сомнительно, что при такой конструкции запроса будут ипользоваться индексу, даже если они есть. Советую применить "динамический" sql для формирования запросов в зависимости от параметров.
2. Можно попробовать соорудить кластеризованный индекс по accountId.
Re[2]: Гигантская тормозная база, секционирование и ускорение
От: Alex.Che  
Дата: 18.01.23 10:32
Оценка:
Здравствуйте, capgreen, Вы писали:


C>1. Сомнительно, что при такой конструкции запроса будут ипользоваться индексу, даже если они есть.

C>Советую применить "динамический" sql для формирования запросов в зависимости от параметров.

+1
выборку нужно ограничивать "до того как", а не "после того".
Re: Гигантская тормозная база, секционирование и ускорение
От: gandjustas Россия http://blog.gandjustas.ru/
Дата: 18.01.23 11:19
Оценка: 77 (2) +1
Здравствуйте, Слава, Вы писали:

С>Есть база MS SQL на полтора терабайта, а может и больше, я не видел сам прод.

С>В ней имеется 9 миллионов клиентов, у которых имеются сотни тысяч транзакций (в бизнес-смысле). Это данные о проезде автомобилей через ворота, условно говоря.
Это не очень много на самом деле для современного железа.

С>Всякие гриды в тормозном веб-интерфейсе тормозят ещё сильнее во время выборки.

Это означает что у вас не используются индексы.

С>А выборка в типовой хранимке происходит так: заполняем временную таблицу уймой запросов по всем данным заданного клиента, затем фильтруем временную таблицу по значениям фильтра из грида, по всем 50 колонкам, по всему миллиону строк

Это уже плохо пахнет.
База данных работает быстро тогда, когда не пытается тянуть все данные таблицы для выполнения запроса. Обычно используют индексы, которые не обходить все строки таблицы в поисках нужных значений (O(N)), а найти в индексе нужные строки и выдать только их (O(log N).
У вас заранее выборка пессимизирована, так как вытягиваются все данные по клиенту, да еще и пишутся на диск (внезапно временные таблицы записываются в tempdb), а потом используются запросы, которые заведомо не оптимизируются индексами.

С>вот в таком духе:

С>
С>                AND (
С>                    (@ipv_biTransactionID IS NULL AND @ipv_vcTransactionOperator IS NULL)
С>                    OR (@ipv_vcTransactionOperator = 's%' AND Y.CustTrxnID LIKE (CAST(@ipv_biTransactionID AS VARCHAR(MAX)) + '%'))
С>                    OR (@ipv_vcTransactionOperator = 'e%' AND Y.CustTrxnID LIKE ('%' + CAST(@ipv_biTransactionID AS VARCHAR(MAX))))
С>                    OR (@ipv_vcTransactionOperator = '=' AND Y.CustTrxnID = @ipv_biTransactionID)
С>                    OR (@ipv_vcTransactionOperator = '!=' AND Y.CustTrxnID != @ipv_biTransactionID)
С>                    OR (@ipv_vcTransactionOperator = 'IN' AND Y.CustTrxnID LIKE ('%' + CAST(@ipv_biTransactionID AS VARCHAR(MAX)) + '%'))
С>                    OR (@ipv_vcTransactionOperator = 'NOT IN' AND Y.CustTrxnID NOT LIKE ('%' + CAST(@ipv_biTransactionID AS VARCHAR(MAX)) + '%'))
С>                )            
С>                AND (
С>                    (@ipv_iTrxnStatus IS NULL AND @ipv_vcTrxnStatusOperator IS NULL)
С>                    OR (@ipv_vcTrxnStatusOperator = 's%' AND Y.TrxnStatusID LIKE (CAST(@ipv_iTrxnStatus AS VARCHAR(MAX)) + '%'))
С>                    OR (@ipv_vcTrxnStatusOperator = 'e%' AND Y.TrxnStatusID LIKE ('%' + CAST(@ipv_iTrxnStatus AS VARCHAR(MAX))))
С>                    OR (@ipv_vcTrxnStatusOperator = '=' AND Y.TrxnStatusID = @ipv_iTrxnStatus)
С>                    OR (@ipv_vcTrxnStatusOperator = '!=' AND Y.TrxnStatusID != @ipv_iTrxnStatus)
С>                    OR (@ipv_vcTrxnStatusOperator = 'IN' AND Y.TrxnStatusID LIKE ('%' + CAST(@ipv_iTrxnStatus AS VARCHAR(MAX)) + '%'))
С>                    OR (@ipv_vcTrxnStatusOperator = 'NOT IN' AND Y.TrxnStatusID NOT LIKE ('%' + CAST(@ipv_iTrxnStatus AS VARCHAR(MAX)) + '%'))
С>                )        
С>            WHERE x.RowNumber BETWEEN @v_iStartIndex AND @v_iEndIndex
С>            ORDER BY x.RowNumber;
С>

Если у вас эта процедура не имеет флага RECOMPILE или вызывается без RECOMPILE, то она 100% НЕ будет использовать индексы.


С>Как бы эту базу распилить, чтобы не тормозила? Если у большей части самых гигантских таблиц есть accountId, то логично было бы применить секционирование по этому id. Но 9 миллионов секций не поддерживаются MS SQL, это слишком много.

С>Секционирование можно сделать например по 2 последним цифрам в id клиента. Хорошая ли это идея?
Нет, идея нерабочая. Откуда вообще взялась идея, что можно что-то в базе распилить и оно не будет тормозить? Тем более на таких скромных объемах.
Возможно partitioning таблицы транзакций по датам может помочь, и то непонятно как

С>Что вообще с такой базой можно сделать?

1) Убрать ужас со временно таблицей, переписать на with
2) сделать процедуру recompile
3) Покрыть индексами, настроить статистику
4) Переписать процедуру на разные запросы, генерируемые из программы, обратить внимание на предложение SELECT
Re: Гигантская тормозная база, секционирование и ускорение
От: BlackEric http://black-eric.lj.ru
Дата: 18.01.23 13:57
Оценка:
Здравствуйте, Слава, Вы писали:

С>Секционирование можно сделать например по 2 последним цифрам в id клиента. Хорошая ли это идея? Что вообще с такой базой можно сделать?


Если быстро, то посмотреть планы запросов попробовать настроить индексы.
А так нужно уходить от временной таблицы. Сразу вытягивайте на клиента то, что нужно показать в гриде.
https://github.com/BlackEric001
Re: Гигантская тормозная база, секционирование и ускорение
От: VladiCh  
Дата: 19.01.23 09:59
Оценка:
Здравствуйте, Слава, Вы писали:

С>Есть база MS SQL на полтора терабайта, а может и больше, я не видел сам прод.


Сотни тысяч транзакций на одного клиента? Или на всех?
Вообще если загрузка данных идет по конкретному клиенту, то да, имеет смысл сделать партишенинг по accountId, там уж сами решайте по его части или по хэшу от него,
тогда эта загрузка не вызовет сканирования всех таблиц целиком, только относительно небольшой части.
На тыщу партиций можно же разделить, а то и на десяток тысяч. Я не знаю сколько максимум MSSQL поддерживает и сколько имеет смысл задать в качестве максимума,
но похоже что чем больше тем лучше (опять же если загрузка идет исключительно по одному клиенту и никогда в виде общего списка, иначе все будет плохо).
Это если в коде не хочется ничего особо менять. Но лучше конечно переписать это все, если есть возможность, выглядит оно очень печально...
 
Подождите ...
Wait...
Пока на собственное сообщение не было ответов, его можно удалить.