Запрс на 7.5 в 16 раз медленне чем на 7.0

[georgekl@rambler.ru]

Проблема:
запрос на IB 7.5 выполняется раз в 16 медленне, чем на IB 7.0.
Выполнялись на одном сервере W2003, в один день. Настройки сервера не менялись. Параметры конфиг. файла практически одинаковы, за исключением новых для 7.5.

БД многофайловая, сразу после b/r:
DS_P1OF8.GDB 1610616832
DS_P2OF8.GDB 1610616832
DS_P3OF8.GDB 1610616832
DS_P4OF8.GDB 278261760
DS_P5OF8.GDB 8192
DS_P6OF8.GDB 8192
DS_P7OF8.GDB 8192
DS_P8OF8.GDB 8192

Запрос:
select
min(tt.descr) as descr_tt,
min(la_t.descr) as descr_la_t,
min(c.code) as code_c,
min(cf0.descr) as descr_cf
min(c.descr) as descr_c,
c.ADDRESS,
a.ts_doc,
min(a.docdef) as docdef,
min(a.num_doc) as num_doc,
min(cast(f_left(p.descr,1) as varchar(1))) as descr_pa,
min(a.days_cre) as days_cre,
min(UDF_SIIF3(a.discount,0,a.discount)) as discount,
max(UDF_SIIF3(a.izd,0,a.izd)) as izd,
min(a.price_level) as price_level,
min(UDF_SIIF3(n_payed,0,n_payed)+UDF_SIIF3(b_payed,0,b_payed)) as payed,
a.id_i,
i.code,
i.descr as descr_i,
min(ifl.descr) as descr_if,
a.id_d,
-sum(a.kol) as kol,
-sum(a.sum_ss) as sum_ss,
-sum(a.summ) as summ
from sa_type1 a
left join sc_clients c on c.id_c=a.id_c
left join sc_clients cf0 on cf0.id_c=c.pid
left join sc_clients cf1 on cf1.id_c=cf0.pid
left join sc_clients cf2 on cf2.id_c=cf1.pid
left join sc_items i on i.id_i=a.id_i
left join sc_items ifl on ifl.id_i=i.pid
left join sc_tradetypes tt on tt.id_tt=a.id_tt
left join sc_laibors la_t on la_t.id_la=a.id_la_t
left join sc_paytypes p on p.id_pa=a.id_pa
where
/* Uni limit begin */
a.ID_PR=124
/* Uni limit end */
and sign=1 and a.date_doc>='2006-06-01' and a.date_doc<'2006-07-01'
group by a.id_tt, a.id_la, a.id_c, C.ADDRESS, a.ts_doc, i.pid, a.id_i, i.code, i.descr, id_d
order by tt.descr, la_t.descr, c.descr, ifl.descr, i.descr

Результаты, выданные IBExpert:

>> IB 7.0 on server #2 --------------------------------------------------------------------------------------------------
Plan
SORT (SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (A INDEX (X_SA_TYPE1_DATE_DOC,SA_TYPE1_ID_PR),C INDEX (RDB$PRIMARY7)),CF0 INDEX (RDB$PRIMARY7)),CF1 INDEX (RDB$PRIMARY7)),CF2 INDEX (RDB$PRIMARY7)),I INDEX (RDB$PRIMARY12)),IFL INDEX (RDB$PRIMARY12)),TT INDEX (RDB$PRIMARY19)),LA_T INDEX (RDB$PRIMARY14)),P INDEX (RDB$PRIMARY15))))

Adapted Plan
SORT (SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (A INDEX (X_SA_TYPE1_DATE_DOC,SA_TYPE1_ID_PR),C INDEX (RDB$PRIMARY7)),CF0 INDEX (RDB$PRIMARY7)),CF1 INDEX (RDB$PRIMARY7)),CF2 INDEX (RDB$PRIMARY7)),I INDEX (RDB$PRIMARY12)),IFL INDEX (RDB$PRIMARY12)),TT INDEX (RDB$PRIMARY19)),LA_T INDEX (RDB$PRIMARY14)),P INDEX (RDB$PRIMARY15))))

------ Performance info ------
Prepare time = 0ms
Execute time = 33s 938ms
Avg fetch time = 2 121.13 ms
Current memory = 301 017 314
Max memory = 301 018 338
Memory buffers = 65 535
Reads from disk to cache = 17 572
Writes from cache to disk = 0
Fetches from cache = 4 190 984

>> IB 7.5 on server #2 --------------------------------------------------------------------------------------------------
Plan
PLAN SORT (SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (A INDEX (X_SA_TYPE1_DATE_DOC,SA_TYPE1_ID_PR),C INDEX (RDB$PRIMARY7)),CF0 INDEX (RDB$PRIMARY7)),CF1 INDEX (RDB$PRIMARY7)),CF2 INDEX (RDB$PRIMARY7)),I INDEX (RDB$PRIMARY12)),IFL INDEX (RDB$PRIMARY12)),TT INDEX (RDB$PRIMARY19)),LA_T INDEX (RDB$PRIMARY14)),P INDEX (RDB$PRIMARY15))))

Adapted Plan
PLAN SORT (SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (A INDEX (X_SA_TYPE1_DATE_DOC,SA_TYPE1_ID_PR),C INDEX (RDB$PRIMARY7)),CF0 INDEX (RDB$PRIMARY7)),CF1 INDEX (RDB$PRIMARY7)),CF2 INDEX (RDB$PRIMARY7)),I INDEX (RDB$PRIMARY12)),IFL INDEX (RDB$PRIMARY12)),TT INDEX (RDB$PRIMARY19)),LA_T INDEX (RDB$PRIMARY14)),P INDEX (RDB$PRIMARY15))))

------ Performance info ------
Prepare time = 16ms
Execute time = 9m 11s 547ms
Avg fetch time = 34 471.69 ms
Current memory = 585 275 116
Max memory = 585 279 944
Memory buffers = 131 070
Reads from disk to cache = 17 884
Writes from cache to disk = 0
Fetches from cache = 4 191 043


Не подскажете как избежать этого?
И можно ли добиться того, чтобы этот запрос выполнялся на 7.5 не медленнее, чем на 7.0?

[kdv]

БД многофайловая, сразу после b/r:

этот изврат уже давно не нужен. не морочьте голову, ресторьте в один файл.

планы, насколько я вижу, одинаковые в 7.0 и 7.5. Поэтому, надо выяснять, ГДЕ именно тормоза.
Попробуйте для начала выполнить запрос без сортировок (group by и order by) на обоих серверах. Сравните время выполнения.
Затем добавьте group by. Сравните опять. Потом order by.

Первое впечатление, что для 7.5 просто не настроен TEMP.
Кроме того, слегка подозрительно наличие одних left join.

Оба сервера показывают одинаковые планы, после restore БД? Для IB 7.5 запрос выполняется от базы 7.0, или после b/r?
Также рекомендую проверить используемые в плане индексы IBAnalyst-ом.

[kdv]

да, и еще. page reads и fetches у обоих серверов примерно одинаково. Поэтому такая разница во времени выполнения запроса странновата.
Вижу, что для IB 7.5 database_cache_pages выставлено до упора. Может быть, уравнять с 7.0?

и еще - какой размер страницы БД?

[georgekl@rambler.ru]

Благодарю за содержательный ответ!
Возможно, я что-либо пропустил, а что изменилось с максимальным размером файла? Он теперь больше 4Г?

Параметры:
Первоначально параметры IB 7.5 были заданы такие (остальные дефолтные):
ANY_LOCK_MEM_SIZE 1048576
DATABASE_CACHE_PAGES 524288
DATABASE_CACHE_PAGES 524288
SORTMEM_BUFFER_SIZE 268435456
SWEEP_YIELD_TIME 10000
TMP_DIRECTORY Z:\IB_sort
V4_LOCK_MEM_SIZE 1048576

>>DATABASE_CACHE_PAGES: и для 7.0, и для 7.5 стояло одинаковое значение - 524288.
Затем все было возвращено полность к дефолтным значениям.
Если TEMP имеется в виду TMP_DIRECTORY, то он был.
В обоих случаях результат почто точно совпал - он и был приведен для 7.5.

Страница БД - 4К. Кластер - 4К.

Что касается left join - у нас таких запросов много, и никогда проблем таких (по времени выполнения) на 7.0 не было.

Индексы: IBAnalyst показал ОДИН фрагментированный, его исправили (inactive/active), не повлияло. Есть несколько с большим количеством дублей, но в плане их нет. Да и, кроме того, индекс должен быть плох и для 7.0, видимо, в той же степени, что и для 7.5?

Сейчас пробуем без группировок.

[CyberMax]

Благодарю за содержательный ответ!
Возможно, я что-либо пропустил, а что изменилось с максимальным размером файла? Он теперь больше 4Г?

В вашем случае (IB 7.x), максимальный размер файла зависит только от файловой системы. В случае NTFS (а у вас именно так и должно быть) он больше 4 Гб.

[georgekl@rambler.ru]

Запросы были выполнены бе группировок:
================ Результат выполнения запроса без групп для 7.5 ================
Plan
PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (A INDEX (X_SA_TYPE1_DATE_DOC,SA_TYPE1_ID_PR),C INDEX (RDB$PRIMARY7)),CF0 INDEX (RDB$PRIMARY7)),CF1 INDEX (RDB$PRIMARY7)),CF2 INDEX (RDB$PRIMARY7)),I INDEX (RDB$PRIMARY12)),IFL INDEX (RDB$PRIMARY12)),TT INDEX (RDB$PRIMARY19)),LA_T INDEX (RDB$PRIMARY14)),P INDEX (RDB$PRIMARY15))

Adapted Plan
PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (A INDEX (X_SA_TYPE1_DATE_DOC,SA_TYPE1_ID_PR),C INDEX (RDB$PRIMARY7)),CF0 INDEX (RDB$PRIMARY7)),CF1 INDEX (RDB$PRIMARY7)),CF2 INDEX (RDB$PRIMARY7)),I INDEX (RDB$PRIMARY12)),IFL INDEX (RDB$PRIMARY12)),TT INDEX (RDB$PRIMARY19)),LA_T INDEX (RDB$PRIMARY14)),P INDEX (RDB$PRIMARY15))

------ Performance info ------
Prepare time = 0ms
Execute time = 6m 32s 515ms
Avg fetch time = 24 532.19 ms
Current memory = 37 756 576
Max memory = 37 757 600
Memory buffers = 2 048
Reads from disk to cache = 4 810
Writes from cache to disk = 0
Fetches from cache = 14 804

================ Результат выполнения запроса без групп для 7.0 ================
Plan
JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (A INDEX (X_SA_TYPE1_DATE_DOC,SA_TYPE1_ID_PR),C INDEX (RDB$PRIMARY7)),CF0 INDEX (RDB$PRIMARY7)),CF1 INDEX (RDB$PRIMARY7)),CF2 INDEX (RDB$PRIMARY7)),I INDEX (RDB$PRIMARY12)),IFL INDEX (RDB$PRIMARY12)),TT INDEX (RDB$PRIMARY19)),LA_T INDEX (RDB$PRIMARY14)),P INDEX (RDB$PRIMARY15))

Adapted Plan
JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (A INDEX (X_SA_TYPE1_DATE_DOC,SA_TYPE1_ID_PR),C INDEX (RDB$PRIMARY7)),CF0 INDEX (RDB$PRIMARY7)),CF1 INDEX (RDB$PRIMARY7)),CF2 INDEX (RDB$PRIMARY7)),I INDEX (RDB$PRIMARY12)),IFL INDEX (RDB$PRIMARY12)),TT INDEX (RDB$PRIMARY19)),LA_T INDEX (RDB$PRIMARY14)),P INDEX (RDB$PRIMARY15))

------ Performance info ------
Prepare time = 16ms
Execute time = 687ms
Avg fetch time = 42.94 ms
Current memory = 300 708 860
Max memory = 300 709 884
Memory buffers = 65 535
Reads from disk to cache = 3 834
Writes from cache to disk = 0
Fetches from cache = 13 826

То есть различие во времени возросло еще больше!

[kdv]

Возможно, я что-либо пропустил, а что изменилось с максимальным размером файла? Он теперь больше 4Г?

предлагаю пройти тест общих знаний по IB/FB на http://course.ibase.ru

То есть различие во времени возросло еще больше!

Товарищ! разуй глаза - ты у 7.5 испортил конфиг, и теперь там database_cache_pages стоит по умолчанию, 2048. Чего ты хочешь, урезав серверу кэш в 64 раза (и в 32 раза относительно 7.0)???

DATABASE_CACHE_PAGES: и для 7.0, и для 7.5 стояло одинаковое значение - 524288.

то есть, параметры тыкаем "от балды", не глядя в документацию? Авось чего прокатит?

Что касается left join - у нас таких запросов много,

вот я и спрашиваю - вы понимаете, что пишете, или копируете текст запросов из "автоматизированного построителя SQL"?

SORTMEM_BUFFER_SIZE 268435456

мда. А что тогда будет если 10 человек одновременно выполнят запрос с сортировкой?

SWEEP_YIELD_TIME 10000

без комментариев...

[georgekl@rambler.ru]

Сами вы от "от балды". И без комментариев!
Предлагаю перечесть первое сообщение, где и описана проблема.
Остальное - результат применения ваших советов.
От вашего остроумия проблема не исчезнет.
Кто-либо еще может подсказать что-нибудб дельное?
А вам, kdv, по поводу стиля вашего, хотел бы заметить, что виртуальный мир, конечно, расслабляет, но речь-то лучше контролировать.

[dimitr]

Уважаемый, тебе предлагали уравнять конфиг. А ты что сделал?

[georgekl@rambler.ru]

Давайте я процитирую первое сообщение:
Параметры конфиг. файла практически одинаковы, за исключением новых для 7.5.

[dimitr]

когда ты делал тесты без группировки, конфиг сменился. У 7.1 стоит 64К страниц кеша, а у 7.5 - дефолтные 2К страниц. И что после этого ты хочешь показать нам своими цифрами?

[georgekl@rambler.ru]

Сменился, в результате разных экспериментов. И сменялся не раз. Однако это ни на что не влияло (в вплане скорости), можете поверить.
Если посмотрите на первое сообщение, то там он (кеш) у 7.5 больше, чем у 7.0.
Так что идея с группировками (точнее, без них) мертвая. В самом деле, как это должно звучать: "Уберите из запроса группы, и он будет выполняться нормально"? Но он нужен именно с группами.

Так что предлагаю забыть все о группах и вернуться к сути:
в одной исправной БД разными серверами, на одной машине, один и тот же запрос выполняется с драматическими различиями в скорости. Как можно этого избежать, кто-лнибудь может подсказать?

Препираться же просто некогда, простите.

[CyberMax]

Я бы посоветовал идти от обратного. Мы имеем запрос, который выполняется медленно. Попробуй сначала поубирать по одному условия. Если это никак не повлияло, начни убирать постепенно все джойны, начиная с конца (условия оставь). В итоге можно будет определить, в результате чего сервер задумывается.

[kdv]

Препираться же просто некогда, простите.

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

[kdv]

кроме того. без группировки и сортировки можно было бы понять, есть разница в таком выполнении, или нет. То есть, последовательно найти причину отличий в скорости выполнения запроса на разных серверах.
Далее, сравнить скорость сортировки, предварительно оценив объем сортируемых записей.
Короче, если Вы хотите, чтобы Вам помогли - следуйте советам.

[Merlin]

Про лошадь сюда надо было постить ))

[kdv]

Вполне возможно, что ответа не дождемся. Как бы то ни было, поясню, как вообще решаются проблемы подобного рода.
1. в спокойной обстановке разбираемся, и советуем, что делать.
Только не как в этом случае, когда то один конфиг, то другой, то еще что.
2. если надо "быстро", то тогда - за деньги. www.ibase.ru/techsupp.htm
3. qc.borland.com, формулируем проблему, отправляем, ждем.

лично мне было бы интересно понять, в чем все-таки проблема. И по возможности сымитировать это на IB 8 FT2. Заодно и на FB проверить. Но - с корректным конфигом.

Например, странно видеть по одному и тому же запросу следующую информацию:
7.5
Memory buffers = 2 048
Reads from disk to cache = 4 810
Fetches from cache = 14 804

7.0
Memory buffers = 65 535
Reads from disk to cache = 3 834
Fetches from cache = 13 826

что фетчей почти одинаково - это ладно. но почему reads-то такой странный. Типа, 7.5 прочитал с диска 6858 тысяч страниц, а 7.0 - 69300? Или, в этот момент на сервере работали? Или запрос выполнен после первого коннекта к БД? Сколько памяти на сервере? Сколько свободно? Может, 7.5 при установленном размере буфера сортировки для ОДНОГО запроса в 286 мегабайт, просто выпадает в виртуалку?

[georgekl@rambler.ru]

Счас делаю эксперимент начисто. Собираю данные в одну сводную таблицу, потому задерживаюсь. Чтоб не было вопросов по разным конфигам.
предварительный результат такой:
- убрав все группировки и джойны, получили ~3 мин вместо 7-9.
- убрав затем фильтр по реквизиту a.id_pr, получили ~ 3 сек.
Памяти на машине 3.25 Г, 2xXeon 2.8, SCSI RAID5.

[kdv]

ждем результатов, спасибо.