Падение производительности ХП в 6 раз на FB 2.1.3

Совместимость InterBase, Firebird, Yaffil между собой и по версиям

Модераторы: kdv, Alexey Kovyazin

Ответить
folko
Сообщения: 16
Зарегистрирован: 27 янв 2012, 13:02

Падение производительности ХП в 6 раз на FB 2.1.3

Сообщение folko » 18 фев 2015, 06:24

Добрый день. Перевожу проект с FB 1.5 на FB 2.1 (Перевод осуществляется путем бэкапа на 1.5 и рестора на 2.1, ошибок не возникает). Появились жалобы на замедление работы приложения. Выяснил конкретное место падения производительности, это хранимая процедура. Поставил на одном компьютере два сервера FB 1.5 и 2.1 на разных портах, оба в режиме SuperServer. Подключаюсь к ним через IBExpert. Запускаю на обоих серверах эту процедуру с одинаковыми параметрами и получаю следующие результаты в Perfomance Analysis:

2.1.7
Plan
------------------------------------------------
PLAN SORT ((GET_PAY_NACH_ON_FS NATURAL))
Query Time
------------------------------------------------
Prepare : 0,00 ms
Execute : 136 141,00 ms
Avg fetch time: 3 889,74 ms
Memory
------------------------------------------------
Current: 10 056 984
Max : 12 458 136
Buffers: 2 048
Operations
------------------------------------------------
Read : 2 138 073
Writes : 0
Fetches: 87 952 817
Marks : 0
Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
| Table Name | Records Total | Indexed reads | Non-Indexed reads | Updates | Deletes | Inserts | Backouts | Purges | Expunges |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
| PR_12 | 415351 | 43850908 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+

1.5.6
Plan
------------------------------------------------
PLAN SORT ((GET_PAY_NACH_ON_FS NATURAL))
Query Time
------------------------------------------------
Prepare : 16,00 ms
Execute : 21 171,00 ms
Avg fetch time: 604,89 ms
Memory
------------------------------------------------
Current: 1 090 576
Max : 3 804 348
Buffers: 2 048
Operations
------------------------------------------------
Read : 641 526
Writes : 0
Fetches: 1 713 026
Marks : 0
Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
| Table Name | Records Total | Indexed reads | Non-Indexed reads | Updates | Deletes | Inserts | Backouts | Purges | Expunges |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
| PR_12 | 415351 | 425293 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+

Из чего видно, что идет увеличение операций чтения таблицы приблизительно в 100 раз при идентичных результатах самого выполнения...

В хранимой процедуре используется execute statement for и в его цикле еще штук 10 execute statement, возвращающих одно значение. Как я понимаю, оба сервера должны исходить из того, что execute statement по сути не детерминирован и оба сервера должны постоянно перечитывать данные. Но 1.5.6 этого не делает (предполагаю наличие или использование какого-то кэша запросов), в то время как 2.1 - делает.

Возможно я не правильно предполагаю. Так как я проделал опыт с возвращением одной записи в execute statement for и тут количество чтений у 1.5.6 осталось неизменным (т.е. цикл по одной записи с вложенным в него десятком execute statement и цикл по 100 равны между собой по количеству операций чтений), в то время как у 2.1.7 количество чтений стало меньше чем у 1.5.6.

Собственно вопрос, есть ли возможность установкой каких-либо опций сервера 2.1.7 вернуть поведение сервера 1.5.6 в плане работы с execute statement или можно сразу начинать переделывать проект без использования этой процедуры с ее execute statement-ми (этот вариант хотелось бы использовать в последнюю очередь, хотя он, наверное, самый правильный)?

kdv
Forum Admin
Сообщения: 6575
Зарегистрирован: 25 окт 2004, 18:07
Контактная информация:

Re: Падение производительности ХП в 6 раз на FB 2.1.3

Сообщение kdv » 18 фев 2015, 10:52

1. у процедур не бывает плана. Вернее, он является суммарным для всех запросов. Если у вас в процедуре один указанный план, значит, по идее, один-единственный запрос?
Если нет, то проверять планы и производительность нужно у всех запросов процедуры (вытаскивая их в SQL Editor ИБЭксперта поштучно).

2
PLAN SORT ((GET_PAY_NACH_ON_FS NATURAL))
Read : 2 138 073
Fetches: 87 952 817
PR_12 | 415351 | 43850908

PLAN SORT ((GET_PAY_NACH_ON_FS NATURAL))
Read : 641 526
Fetches: 1 713 026
PR_12 | 415351 | 425293

Отсюда видно, что на 2.1 гораздо большее количество чтений страниц, и больше индексного доступа (возможно лишнего).
Так что, проверяйте планы ваших запросов в этой процедуре. Оптимизатор у 2.x отличается, и скорее всего какой-то индекс подцепляется как лишний.

3. Не вижу совершенно никакого смысла с 1.5.6 переходить на 2.1. Зачем вам 2.1, когда уже давно существует 2.5?
В хранимой процедуре используется execute statement for и в его цикле еще штук 10 execute statement, возвращающих одно значение.
- поздравляю. вы излишне увлеклись ES.
предполагаю наличие или использование какого-то кэша запросов
кэша запросов не существует в ФБ (даже в 3.0). См. выше про разные планы запросов.

folko
Сообщения: 16
Зарегистрирован: 27 янв 2012, 13:02

Re: Падение производительности ХП в 6 раз на FB 2.1.3

Сообщение folko » 18 фев 2015, 11:56

Мопед не мой. Мне не нравятся такие процедуры и на FB2.1 хочу перевести потому что хочу обойтись одиночными запросами там где это можно. Но из-за крупности проекта нет возможности сразу и все переделывать, а fb2.5 вызывает не только видимые, но и скрытые ошибки (тот же синтаксис в execute statement). В проекте сотни тысяч строк кода, и все без тестов. А я делаю unit и gui тесты только на свой код или там где что-то фиксил. В общем потихоньку меняю веточки и ветки проекта, но о кардинальных переменах вроде перехода на FB2.5 еще рано говорить.

1. Да, я вызываю только ХП с параметрами. Как я понял execute statement-ы используются из-за передачи имен таблиц в параметрах процедуры. Я сейчас переделал ее под конкретные имена таблиц и заменил execute statement на select into. Результаты не изменились почти. ХП можно представить схематично следующим образом:
--входящий параметр a
--входящий параметр b
begin
for select t1.field1, t2.field2, t3.field3
from table1 t1
inner join table2 t2 on t2.kod_t1 = t1.kod
inner join table3 t3 on t3.kod_t2 = t2.kod
where t1.field5 = a
into x1, x2, x3 do
begin
select max(field2) from table4
where field1 = a and field2 < x1

select min(field1) from table4
where field3 = b and field1 = a and field5 > x2

select sum(field10) from table4
where field1 = a and field6 > x1 and field2 < x3
suspend
end
end

2. Вот проблема именно с table4(это PR_12 из анализа производительности). Затем я провел эксперименты изменив количество строк возвращаемых главным запросом. В FB 1.5 если выполнить ХП при first 1 в главном запросе, а затем выполнить ХП при first 100 - количество чтений индексов не меняется ни на одну операцию. Из чего можно предположить, что он не производит перечитывание таблиц при каждом проходе цикла. А у FB 2.1 количество операций чтения при first1 и first 100 меняется очень существенно (десятки раз). Может быть FB 1.5 производит повторные расчеты по данным имеющимся у него в оперативной памяти, а FB 2.1 для этого чего-то не хватает?

kdv
Forum Admin
Сообщения: 6575
Зарегистрирован: 25 окт 2004, 18:07
Контактная информация:

Re: Падение производительности ХП в 6 раз на FB 2.1.3

Сообщение kdv » 18 фев 2015, 16:25

Из чего можно предположить, что он не производит перечитывание таблиц при каждом проходе цикла.
suspend в процедуре заполняет буфер, как только он заполнится, данные передаются клиенту. если клиент больше ничего не просит, процедура дальше не выполняется.
Сравнивайте планы запросов, не надо анализировать результат работы процедуры.
Может быть FB 1.5 производит повторные расчеты по данным имеющимся у него в оперативной памяти, а FB 2.1 для этого чего-то не хватает?
нет тут никаких "может быть". ФБ 2.1 не может не иметь чего-то того, что было у ФБ 1.5. Исходя из ваших данных кэш одинаков на обоих серверах.
Так что еще раз - сравнивайте планы запросов из процедуры.

Кстати, у вас там max memory разное. То есть, вы тестируете на разных машинах. Надеюсь, их производительность близка?

folko
Сообщения: 16
Зарегистрирован: 27 янв 2012, 13:02

Re: Падение производительности ХП в 6 раз на FB 2.1.3

Сообщение folko » 18 фев 2015, 17:46

Сравнивайте планы запросов, не надо анализировать результат работы процедуры.
Результат запроса для курсора я сравнивал - работают одинаково. На fb2.1 чуть быстрее, но план такой же.

Хорошо, я сравню результат запросов вида
select max(field2) from table4
where field1 = a and field2 < x1
И хотя там есть куда оптимизировать (в where есть конструкции типа or и in), но запрос обращается только к одной таблице и и чет мне не думается, что проблема этим решится...
Кстати, у вас там max memory разное. То есть, вы тестируете на разных машинах. Надеюсь, их производительность близка?
Не знаю, почему такая информация по памяти (может тут проблема тоже зарыта?). Оба сервера работают на одной машине (Win 8 X64) и запросы запускаются по localhost. Только у fb 1.5 порт 3052, а у FB 2.1 порт 3050.

folko
Сообщения: 16
Зарегистрирован: 27 янв 2012, 13:02

Re: Падение производительности ХП в 6 раз на FB 2.1.3

Сообщение folko » 19 фев 2015, 07:03

Я был не прав. Планы запросов внутри курсора действительно играли негативную роль.

Указал принудительно в ХП на 2.1 планы всех внутренних запросов курсора из FB 1.5. Производительность процедуры на сервере FB 2.1 стала не хуже чем на сервере FB 1.5.

Вот конкретный запрос:

Код: Выделить всё

SELECT SUM(COUNTERVAL) 
FROM PR_12 
WHERE CHT_D=6220 AND MATERIAL=0 AND DATE_<='2010-05-31 00:00:00.0000' and ((oau_d1=206 and spr_d1=402) or (oau_d2=206 and spr_d2=402) or (oau_d3=206 and spr_d3=402) or (oau_d4=206 and spr_d4=402)) AND DATE_>='2008-08-01 00:00:00.0000'
его план на сервере FB 2.1:
PLAN (PR_12 INDEX (PR_12_IDX26, PR_12_SLDD0))
Статистика индексов:
PR_12_IDX26 (поле DATE_) = 0,000713266781...
PR_12_SLDD0 (поля CHT_D, MATERIAL) = 0,052631579339...

его план на сервере FB 1.5:
PLAN (PR_12 INDEX (PR_12_SLDD0,PR_12_IDX26,PR_12_IDX22,PR_12_IDX23,PR_12_IDX24,PR_12_IDX25))
Статистика индексов:
PR_12_SLDD0 (поля CHT_D, MATERIAL) = 0,052631579339...
PR_12_IDX26 (поле DATE_) = 0,000713266781...
PR_12_IDX22 (поле OAU_D1) = 0,000334448151...
PR_12_IDX23 (поле OAU_D2) = 1,000000000000...
PR_12_IDX24 (поле OAU_D3) = 1,000000000000...
PR_12_IDX25 (поле OAU_D4) = 1,000000000000...

При этом, количество операций чтения индекса для этого запроса у FB 2.1 = 5591, а у FB 1.5 = 1.

Как я понимаю, статистика индексов одинаковая. Их селективность тоже одинаковая, так как базы идентичные и подняты с одного бэкапа.
Тем не менее FB2.1 при выполнении этого запроса берет в расчет не все индексы полей в критерии отбора.
Может ли быть вероятным, что оптимизатору не хватает времени для выбора лучшего плана запроса (на сколько мне известно, в некоторых СУБД оптимизатор для построения лучшего плана отводит какой-то процент времени от предполагаемого времени выполнения самого запроса)?
Есть ли какие варианты решения данной проблемы кроме указания конкретных планов для запросов в ХП?

kdv
Forum Admin
Сообщения: 6575
Зарегистрирован: 25 окт 2004, 18:07
Контактная информация:

Re: Падение производительности ХП в 6 раз на FB 2.1.3

Сообщение kdv » 19 фев 2015, 12:06

PR_12_IDX23 (поле OAU_D2) = 1,000000000000...
PR_12_IDX24 (поле OAU_D3) = 1,000000000000...
PR_12_IDX25 (поле OAU_D4) = 1,000000000000...
эти индексы вообще лишние, потому что селективность, равная 1, означает, что все значения ключа одинаковы. Т.е. в столбце ровно одно значение. И использование такого индекса оптимизатором просто жрет память.

одновременно, разница между планами в основном в отсутствии хорошего индекса
PR_12_IDX22 (поле OAU_D1) = 0,000334448151...
в плане ФБ 2.1.
И в присутствии в плане не очень хорошего индекса
PR_12_SLDD0 (поля CHT_D, MATERIAL) = 0,052631579339...

попробуйте в where поставить
where cht_d+0=6620 and material ....

как подцепить индекс PR_12_IDX22 - это уже другой вопрос. Может, оптимизатор сам его подцепит, если отвалится PR_12_SLDD0.
оптимизатору не хватает времени для выбора лучшего плана запроса
вы издеваетесь? :-) ну какого еще времени. Оптимизатор строит план при Prepare, и серверу совершенно все равно, займет это 1 миллисекунду, или 10 минут. Сколько надо, столько и будет строить план.

folko
Сообщения: 16
Зарегистрирован: 27 янв 2012, 13:02

Re: Падение производительности ХП в 6 раз на FB 2.1.3

Сообщение folko » 19 фев 2015, 14:34

эти индексы вообще лишние, потому что селективность, равная 1, означает, что все значения ключа одинаковы. Т.е. в столбце ровно одно значение. И использование такого индекса оптимизатором просто жрет память.
Да, там одно значение, NULL. У этих столбцов назначение - аналитики, и если там ничего нет, значит конкретно этому клиенту не нужно ничего на данный момент лишнего. Это не всегда так, так как некоторые клиенты более аккуратно ведут свой учет. Поэтому там есть индексы.
попробуйте в where поставить
where cht_d+0=6620 and material ....

как подцепить индекс PR_12_IDX22 - это уже другой вопрос. Может, оптимизатор сам его подцепит, если отвалится PR_12_SLDD0.
У меня была надежда, что получится без глубокого вмешательства повлиять на быстродействия. В проекте базы > 400 процедур и как всегда времени нет... Жаль, буду тогда реагировать только на громкие писки и править точечно.
вы издеваетесь? :-) ну какого еще времени. Оптимизатор строит план при Prepare, и серверу совершенно все равно, займет это 1 миллисекунду, или 10 минут. Сколько надо, столько и будет строить план.
Нет, я не издеваюсь. Вроде такая инфа проскакивала по ****** ASE версии старше 13 (или IQ, присутствовали обе у нас), когда я занимался проектами, использующими эти СУБД. И они не позволяли себе строить запросы по 10 минут. Хорошо это или плохо - не знаю, наверное это холивар. Но их продвиженцы утверждали, что это круто и у них самые эволюционные решения)

В общем, спасибо за помощь. Вопрос решен.

kdv
Forum Admin
Сообщения: 6575
Зарегистрирован: 25 окт 2004, 18:07
Контактная информация:

Re: Падение производительности ХП в 6 раз на FB 2.1.3

Сообщение kdv » 19 фев 2015, 17:43

некоторые клиенты более аккуратно ведут свой учет. Поэтому там есть индексы.
тогда ок, и это даже хорошо, потому что в ФБ 2.1 оптимизатор выкинул эти индексы, как имеющие крайне низкую селективность.
В проекте базы > 400 процедур и как всегда времени нет.
если я прав, и "принудительное отключение индекса" через +0 помогло, значит, или там не нужен композитный индекс, или нужны 2 отдельных индекса по каждому столбцу, или вообще они не нужны.
Проще проэкспериментировать с индексами, чем действительно править 400 процедур.
И они не позволяли себе строить запросы по 10 минут.
все серверы разные, и проецировать свойства одного на другой в корне неверно. Чтобы план препарировался не 10 минут, а хотя бы 1 минуту, нужны особые условия.
Например, нужно соблюдение всех перечисленных условий (по and)
- таблицы должны быть гигантские, с миллиардами записей. Например,
http://www.ibase.ru/devinfo/fb1tbtech.htm
по таблице с 372млн записей prepare в первый раз идет 20 секунд (вроде бы это ускорено в Firebird 3.0)
- у таблицы должно быть огромное количество столбцов и огромное количество индексов. Т.к. оптимизатор строит план перебором, соответственно, время построения плана зависит от количества индексов. К счастью, при разнице селективности в 100 худшие отсекаются (вроде бы еще с ФБ 1.5).

Так что в ФБ нет смысла ограничивать построение плана prepare по времени. Это вообще имеет смысл только для real-time СУБД, которые можно пересчитать по пальцам, и которые имеют весьма специфическое назначение и применение.

folko
Сообщения: 16
Зарегистрирован: 27 янв 2012, 13:02

Re: Падение производительности ХП в 6 раз на FB 2.1.3

Сообщение folko » 19 фев 2015, 18:00

если я прав, и "принудительное отключение индекса" через +0 помогло, значит, или там не нужен композитный индекс, или нужны 2 отдельных индекса по каждому столбцу, или вообще они не нужны.
Проще проэкспериментировать с индексами, чем действительно править 400 процедур.
Я завтра попробую этот вариант и напишу результат.
все серверы разные, и проецировать свойства одного на другой в корне неверно.

Я и не собирался) Просто сделал предположение на основе некоторых знаний о других СУБД. Так сказать, а вдруг) И не настаиваю на своем.

folko
Сообщения: 16
Зарегистрирован: 27 янв 2012, 13:02

Re: Падение производительности ХП в 6 раз на FB 2.1.3

Сообщение folko » 20 фев 2015, 07:14

В общем не получилось заставить цеплять нужный индекс...
Пробовал следующее:
1)удалил составной индекс PR_12_SLDD0 (поля CHT_D и MATERIAL) - не помогло (в плане два индекса по полю DATE_ и CHT_D)
2)удалил индекс по полю CHT_D - не помогло (в плане два индекса по полю DATE_ и MATERIAL)
3) удалил индекс по полю MATERIAL - не помогло (в плане только индекс по полю DATE_)
4) удалил все индексы, оставил только по полям DATE_ и OAU_D1 - не помогло (в плане только индекс по полю DATE_)
5) вернул все индексы, убрал из запроса критерии OR, получился запрос:

Код: Выделить всё

  SELECT SUM(COUNTERVAL) 
  FROM PR_12 
  WHERE CHT_D=6220 AND MATERIAL=0 AND DATE_<='2010-05-31 00:00:00.0000' 
    and ((oau_d1=206 and spr_d1=402)) AND DATE_>='2008-08-01  00:00:00.0000'
И только для такого запроса получился необходимый план:
PLAN (PR_12 INDEX (PR_12_IDX22, PR_12_IDX26))
PR_12_IDX22 - по полю OAU_D1
PR_12_IDX26 - по полю DATE_

Но убирать критерии OR для других полей из запросов - не выход...
Дальше мысли по экспериментам с индексами закончились...

Ответить

Кто сейчас на конференции

Сейчас этот форум просматривают: нет зарегистрированных пользователей и 1 гость