Помогите, плз, разобраться в чем причина самопроизвольного падения производительности БД? Установил на виндовый сервер версию DB2 Express-C 9.1.2 (сервер с 6 Гб Ram). Установка по-умолчанию (за исключением пула буферов - его размер задал ~ 160000 страниц). Для проверки запускаю у себя приложение которое тащит из файла данные и с предобработкой заливает их в таблицу (приложение на Java, записей около 1 000 000). Все вроде работает замечательно. Оставляю в покое БД на время (пару дней). Запускаю повторно то же приложение с теми же данными (предварительно удалив из базы старые) - тормоза. После того как убедился с пулом буферов все нормально (в смысле создается и используется) поковырял параметры DBHEAP и SORTHEAP - эффект ноль. Снес нафиг всю базу, заново ее создал из сохраненных скриптов - при запуске приложения тормоза не исчезли, а только возросли. Для проверки запускал системный монитор винды и монитор активности DB2. Согласно ему пул используется, ожиданий блокировок нет , сортировок почти нет. Центр работоспособности тоже рапортует что все Ок!. Ничего подозрительного не вижу, но тормоза - жуть! Где, что именно и чем можно посмотреть чтобы хоть рыть в нужном направлении? Просьба сильно не пинать - ниже мой конф БД (может о чем-то скажет):
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING APP_CTL_HEAP_SZ 128 ;
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING APPGROUP_MEM_SZ 30000 ;
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING APPLHEAPSZ 3072 ;
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING CATALOGCACHE_SZ 474 ;
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING CHNGPGS_THRESH 80 ;
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING DBHEAP 2296 ;
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING DFT_DEGREE 1 ;
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING DFT_EXTENT_SZ 32 ;
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING DFT_PREFETCH_SZ AUTOMATIC;
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING LOCKLIST 2812 AUTOMATIC;
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING LOGBUFSZ 148 ;
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING LOGFILSIZ 2048 ;
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING LOGSECOND 5 ;
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING MAXAPPLS 80 MAXLOCKS 100;
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING MINCOMMIT 1 ;
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING NUM_IOCLEANERS 2;
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING NUM_IOSERVERS 10;
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING PCKCACHESZ 2427 AUTOMATIC;
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING SOFTMAX 960 ;
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING LOGPRIMARY 30 ;
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING SORTHEAP 256 AUTOMATIC;
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING STMTHEAP 11192 ;
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING STAT_HEAP_SZ 4384 ;
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING UTIL_HEAP_SZ 100128 ;
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING SELF_TUNING_MEM ON ;
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING AUTO_MAINT ON ;
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING AUTO_DB_BACKUP ON ;
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING AUTO_TBL_MAINT ON ;
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING AUTO_RUNSTATS ON ;
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING AUTO_STATS_PROF OFF ;
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING AUTO_REORG ON ;
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING AUTO_PROF_UPD OFF ;
UPDATE DATABASE CONFIGURATION FOR TESTOMS USING SHEAPTHRES_SHR 16512 AUTOMATIC;
UPDATE DATABASE CONFIGURATION USING DATABASE_MEMORY 140900 IMMEDIATE AUTOMATIC;
UPDATE DATABASE MANAGER CONFIGURATION USING AGENT_STACK_SZ 42 ;
UPDATE DATABASE MANAGER CONFIGURATION USING ASLHEAPSZ 32 ;
UPDATE DATABASE MANAGER CONFIGURATION USING FCM_NUM_BUFFERS 1024 AUTOMATIC;
UPDATE DATABASE MANAGER CONFIGURATION USING INTRA_PARALLEL OFF ;
UPDATE DATABASE MANAGER CONFIGURATION USING MAX_QUERYDEGREE 3 ;
UPDATE DATABASE MANAGER CONFIGURATION USING NUM_POOLAGENTS 200 ;
UPDATE DATABASE MANAGER CONFIGURATION USING NUM_INITAGENTS 8 ;
UPDATE DATABASE MANAGER CONFIGURATION USING MAXAGENTS 220 ;
UPDATE DATABASE MANAGER CONFIGURATION USING RQRIOBLK 32768 ;
UPDATE DATABASE MANAGER CONFIGURATION USING SHEAPTHRES 0 ;
CONNECT TO TESTOMS;
ALTER BUFFERPOOL IBMDEFAULTBP SIZE 181072 ;
ALTER BUFFERPOOL TESTOMSPOOL SIZE 181072 ;
SET CURRENT QUERY OPTIMIZATION = 5;

Mark Barinstein,

Выполнял RUNSTATS и REORG после того как пересоздал БД. Никакого заметного эффекта нет. Пробовал задать автонастройку пула буферов, и явно менял его значение от 200000 до 1024 страниц. Менял количество агентов, ставил различные значения в INSTANCE_MEMORY - ничего не меняется. Такое впечатление, что БД живет своей жизнью и ей пофиг конфиги. Нет, конечно кое-что в графиках монитора меняется, но весьма не значительно и на производительности никак не сказывается. Я просто не до конца понимаю логическую взаимосвязь многих параметров отсюда и траблы. К примеру - какое влияние оказывает конфиг самого экземпляра на БД? Т.е. если я выделяю память для экземпляра, то в конфиге БД я могу использовать только ранее выделенный объем или это две независимые области? Может стоит обратить внимание не на конф БД, а на конф инстанса? Если так, то что именно должно быть проверено прежде всего?

Mark Barinstein в 9.5 это общий объём памяти, который инстанс может использовать для всех баз под ним. Т.е. база использует память внутри instance_memory.

А пул буферов конкретной БД? Должен ли объем памяти инстаса быть больше чем пул? Согласно диапазону возможных значений в конфигураторе - нет. Вот это и не вполне понятно.

Mark BarinsteinНа вставку записей все параметры, что вы перечислили, не оказывают влияния.
Для увеличения производительности вставки можно порекомендовать:
- создавать dms пространство, а не sms
- без кэширования на уровне OC (no file system caching),
- на нескольких физ. дисках отдельно от логов, если возможно
- для 4k табличного пространства попробуйте extent size побольше (например 16 или 32).
Ну и commit не очень часто, если можно.

Заново установил DB2. Создал БД как рекомендовано:
Пространства именно dms. При этом (исходя из наличия отдельных винтов) табличное, индексное пространство и логи лежат на разных винтах. Размер страницы 4К и extent size пространства - 32. Стало чуть лучше, но не существенно. Если бы я сам не видел как та же программа выполнялась за 15 минут, то решил бы что так и должно быть. Но с текущей производительностью, она выполнится часов за 8.

шубин_ду
Перед запуском приложения сделайте такой запрос:


Сохраните результаты.
Запустите свое приложение.

После завершения еще раз стартуйте тот же запрос.


Ругнулся на
stats_fabricate_time и sync_runstats_time
убрал их из запроса.
после первого запроса - нули
после второго изменения в elapsed_exec_time_s (3564б,28) и as log_write_time_s(102,34)

шубин_ду2 askfinder,

Непонятно на что уходит elapsed time. Сделайте запрос для своего приложения в snapappl. Там есть колонка для CPU.

Результат: elapsed_exec_time_s - 1 128,509 total_cpu_s - 565,86 остальное - 0.

шубин_ду И посмотрите на отдельные запросы после завершения теста:



Львиная доля (1 434,28) затрат идет на хранимую процедуру. Логично (ИМХО) т.к. клиент только передает ей данные, а всю работу выполняет SP. Но только та же SP делала эту же работу на порядок быстрее. Почему?

шубин_ду

Почему такие большие затраты CPU?

565/1128 = 50% времени тратится на CPU.

В процедуре много длинных вычислений?

Как мониторить содержимое хранимой процедуры - не знаю. Может быть Марк ответит? Есть в DB2 профайлер для хранимых процедур, чтобы можно было посмотреть на каком шаге процедуры тратятся ресурсы?

После моих экспериментов с конфигами похоже совсем не используется пул буферов (сужу по виндовому монитору производительности - счетчики Pool Buffer Read/Write показывают 0, примерно тоже самое показывает и монитор в DB2). При этом при соединении с БД никаких сообщений нет (обычно манагер орет что фиг те а не пул - буду использовать Deafault Pool). У меня в БД установлено UPDATE DATABASE CONFIGURATION FOR TESTOMS USING SELF_TUNING_MEM ON. Эта самонастройка не может ничего саботировать? Каким образом можно вкл. и настроить использование пула?

Mark BarinsteinДля этого надо включить мониторинг буферов:
Включил, смотрю монитор - Pool Logical Read присутствует, Pool Data Write и Asynchronous Read - нет. Это нормально? Внешне - производительность не изменилась.

Mark Barinsteinвы тестовый export / import сделали?
Да, правда на фрагменте данных - около 25 000 записей. Обычный Export/Import выполняется почти мгновенно. Тормоза где-то при выполнении SP. Сама SP для каждой загружаемой записи выполняет пару Select-ов и далее либо обновляет, либо вставляет запись (при этом триггер проверяет символы на допустимые, т.е. нет дополнительных обращений к таблицам). Причем тормоза именно при записи, поиск выполняется быстро. Наверняка, можно оптимизировать SP, но вопрос не в этом. Вопрос в том, почему тот же алгоритм с теми же данными ранее выполнялся в разы быстрее на том же железе и с теми же начальными конфигами БД (автонастройка памяти работает?). И я не могу как не пытаюсь воспроизвести ту ситуацию, и не могу понять причину. Есть одно подозрение (пожалуй, притянутое но...) - изначально на сервере была не до конца сконфигурировано ОЗУ (вместо 6 Гб работали только 4Гб), далее прошла установка DB2 Express-C v.9.1.2 , создание новой БД из бэкапа и только потом выяснилось на счет памяти. Память на серваке подключили. При проверке загрузки данных (о которых все время идет речь) все замечательно работало, но через пару дней та же загрузка стала выполняться на порядок медленее. Не могут ли (сомневаюсь, но все же) быть проблемы при использовании указанной версии DB2 c 6Гб ОЗУ, вроде как она рассчитана только на 4Гб (но ведь работала)? И еще. Процедура загрузки вначале тестировалась на локальной DB2 и работала также быстро. После того как на локальной DB2 отбросили и заново (из ранее сохраненных скриптов) создали БД, процедура загрузки так же как и на серваке стала тормозить.

шубин_ду
Если у Вас данные пишутся на диск и проблема в дисковой системе, то в этой колонке должно быть большое число. У вам точно используется буферная запись? Может быть Вы CLOB пишете? Тогда смотрите DIRECT_WRITE_TIME.

Так и осталось непонятным почему такое большое потребление ЦПУ.
Пишутся обычные VARCHAR(<=150), всего-то полей менее 10. Когда выполняется загрузка мониторы показывают массовое чтение (из буфера), но вот записи ни в буфер, ни на диск не вижу. Такое впечатление, что CPU молотит массивы данных но никуда их не сбрасывает.

Что-то у меня монитор показывает астрономические цифры чтения строк целевой таблицы (миллиарды). Сама SP принимает и анализирует параметры (без обращения к таблицам), делает SELECT INTO... WHERE ID=xxx FETCH FIRST 1 ONLY, и если NOT FOUND то INSERT, иначе - UPDATE. Для каждой строки исходной таблицы вызывается эта ХП. Исходных записей около 600 тыс. при этом после заливки 16 000 строк монитор таблицы показывает ROWS_READ почти 1,5 миллиарда, а ROWS_WRITTEN - 0. Как это можно объяснить?

Mark Barinstein
Посмотрие в файл, там планы всех ваших статических запросов в этой процедуры.
Что там с планами запросов?
Да, Вы правы. Идет сканирование таблицы. Хуже того - с многократными объединениями. Как быть? По плану - данная таблица предназначена для хранения адресов (из КЛАДР) и внутри имеется поле с ID родителя (отсюда, полагаю, и все объединения). Имеется первичный индекс, индекс по полю PARRENT_ID (внешний ключ) и по коду КЛАДР. При добавлении/обновлении как раз и происходит поиск ID по коду КЛАДР, если не найдено, то вставляю запись, в противном случае обновляю наименования. Похоже в базе имелся какой-то ключ из-за которого все работало иначе.

епрст! Нашел!
Ситуация проста как два пальца. Когда повторно создавал из скриптов БД вначале залил структуры таблиц с первичными и внешними ключами, потом триггеры и процедуры и только затем остальные ключи. Т.к. план для SQL-процедуры создается при ее определении, то ессно в план не вошел дополнительный индекс (а REBIND я не сделал, каюсь). Отсюда и все траблы и ессно конфиги не при делах. Поправил и все залетало. Спасибо большое всем.

шубин_дуА что сейчас с elapsed time и cpu time?

elapsed time - 146,45777100000
cpu time - 82,84375000000