Сыпанулся индекс на фрагментированной таблице (oncheck –cI <имя индекса>.)
Таблица большая 550 Гб, почти 600 млн. строк. Обращения к этой таблице (insert, update, delete) происходят в режиме 24 Х 7. Заблокировать доступ к таблице не представляется возможным.
Разворачивание БД из бэкапа займет около 8 часов. Проверка всех данных таблицы и индексов еще часов 12.
Посоветуйте пожалуйста как исправить эту ситуацию с наименьшими потерями по времени?

Забыл написать. Версия 11.70.FC6

Спасибо за советы.
База без реплики.
Индекс по полю c типом DATE. Индекс чуть более 10 Гб
Пока пришел к решению, что надо сделать на бэкапе (проверить сколько по времени, один ли индекс поврежден и т.д)
drop index
create index ... online.


1. Можно по-подробнее, что может быть плохого?
2. PDQ выставить в 0 ? Учитывая что в этой таблице в течении всего дня (без перерывов делаются вставки и удаления строк)
Т.е. отдать приоритет всем DML операциям, а затем созданию индекса.

Спасибо за совет.
Павел, а как отключить логирование при создании индекса?

Еще раз спасибо.
БД без RSS,HDR,SDS.
Часто используемый запрос стал выполняться очень долго. Судить, что запрос работает, можно только по onstat –u –r. (поле nreads). Может привести скрин или строку???
Поля reg_paym_dt , reg_date, recv_code , p.pm_state, p.paym_ord проиндексированы.
Update statistic high по всей таблицы сделан.

На всякий случай, таблица фрагментирована по выражению by round robin in. Почти все чанки зеркалируются
Вот запрос



Если запустить запрос без min ( ), то в выборке окажутся св среднем 300000 – 700000 записей и время выполнения около 30 сек
Если время выборки установить в пределах 2-3 часов, запрос тоже отрабатывает быстро (2-3 сек), не зависимо от выбранной даты и месяца.
Следующая конструкция выполняется менее 2 секунд. Нет оператора min ( )
1.

QUERY: (OPTIMIZATION TIMESTAMP: 06-26-2015 14:31:41)
------
SELECT p.reg_date
FROM plat p
WHERE p.reg_paym_dt >= '2015-06-13 00:00:00'
AND p.reg_paym_dt <= '2015-06-13 23:59:59'
AND p.recv_code in (110,101,510,810,403,72101,11101)
AND p.pm_state = 3
AND p.paym_ord > 0

Estimated Cost: 449280
Estimated # of Rows Returned: 71534

1) informix.p: INDEX PATH

Filters: ((informix.p.recv_code IN (110 , 101 , 510 , 810 , 403 , 72101 , 11101 )AND informix.p.pm_state = 3 ) AND informix.p.paym_ord > 0 )

(1) Index Name: informix.paym__ix_reg_paym_dt
Index Keys: reg_paym_dt (Serial, fragments: ALL)
Lower Index Filter: informix.p.reg_paym_dt >= datetime(2015-06-13 00:00:00) year to second
Upper Index Filter: informix.p.reg_paym_dt <= datetime(2015-06-13 23:59:59) year to second


Query statistics:
-----------------

Table map :
----------------------------
Internal name Table name
----------------------------
t1 p

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 81901 71534 464778 00:01.38 449281

2. Создаю по этому запросу темповую таблицу и по ней ищу min (date). Время выполнения менее 2 сек
QUERY: (OPTIMIZATION TIMESTAMP: 06-26-2015 14:36:46)
------



Estimated Cost: 449280
Estimated # of Rows Returned: 71534

1) informix.p: INDEX PATH

Filters: ((informix.p.recv_code IN (110 , 101 , 510 , 810 , 403 , 72101 , 11101 )AND informix.p.pm_state = 3 ) AND informix.p.paym_ord > 0 )

(1) Index Name: informix.paym__ix_reg_paym_dt
Index Keys: reg_paym_dt (Serial, fragments: ALL)
Lower Index Filter: informix.p.reg_paym_dt >= datetime(2015-06-13 00:00:00) year to second
Upper Index Filter: informix.p.reg_paym_dt <= datetime(2015-06-13 23:59:59) year to second


Query statistics:
-----------------

Table map :
----------------------------
Internal name Table name
----------------------------
t1 p
t2 tmp_r_d

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 81901 71534 464778 00:00.80 449281

type table rows_ins time
-----------------------------------
insert t2 81901 00:00.99


QUERY: (OPTIMIZATION TIMESTAMP: 06-26-2015 14:36:47)
------
select min (reg_date) from tmp_r_d;

Estimated Cost: 2946
Estimated # of Rows Returned: 1

1) informix.tmp_r_d: SEQUENTIAL SCAN


Query statistics:
-----------------

Table map :
----------------------------
Internal name Table name
----------------------------
t1 tmp_r_d

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 81901 81901 81901 00:00.04 2946

type rows_prod est_rows rows_cons time
-------------------------------------------------
group 1 1 81901 00:00.04

3. Делаю запрос с оператором min, но интервал выборки 2 часа

QUERY: (OPTIMIZATION TIMESTAMP: 06-26-2015 14:40:37)
------



Estimated Cost: 9689
Estimated # of Rows Returned: 1

1) informix.p: INDEX PATH

Filters: ((informix.p.recv_code IN (110 , 101 , 510 , 810 , 403 , 72101 , 11101 )AND informix.p.pm_state = 3 ) AND informix.p.paym_ord > 0 )

(1) Index Name: informix.paym__ix_reg_paym_dt
Index Keys: reg_paym_dt (Serial, fragments: ALL)
Lower Index Filter: informix.p.reg_paym_dt >= datetime(2015-06-13 00:00:00) year to second
Upper Index Filter: informix.p.reg_paym_dt <= datetime(2015-06-13 01:59:59) year to second


Query statistics:
-----------------

Table map :
----------------------------
Internal name Table name
----------------------------
t1 p

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 2108 5961 8619 00:00.02 9689

type rows_prod est_rows rows_cons time
-------------------------------------------------
group 1 1 2108 00:00.02


5. делаю запрос с min(p.reg_date) за интервал 1 день.



О том, что он не висит видно из команды (колонка nreads)
onstat -r -u | grep 1599044 > /tmp/tmp.log
nreads
182f12df58 ---PR-- 1599044 informix - 0 0 2 6905200 17408
182f12df58 ---PR-- 1599044 informix - 0 0 2 6905920 17408
182f12df58 ---P--- 1599044 informix - 0 0 2 6906576 17408
…………………………………………………………………………………………
182f12df58 B--PR-- 1599044 informix - 19ed5cda0 0 2 7232992 17408
182f12df58 B--PR-- 1599044 informix - 19f61d160 0 2 7233696 17408


Query statistics:
-----------------

Table map :
----------------------------
Internal name Table name
----------------------------
t1 p

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 1 71534 552110204 55:12.79 1059

type rows_prod est_rows rows_cons time
-------------------------------------------------
group 1 1 1 55:12.79


за каким х.. запрос сканирует всю таблицу rows_scan = 552110204
Как отвадить информикс от этого?















Не пойму, почему стало так долго выполняться. Как
Вот план его выполнения
-- execution plan at /home/informix/sqexplain.out at
QUERY: (OPTIMIZATION TIMESTAMP: 06-25-2015 15:04:51)
------
SELECT min (reg_date)
FROM plat p
WHERE p.reg_paym_dt >= '2015-06-13 00:00:00'
AND p.reg_paym_dt <= '2015-06-13 23:59:59'

Estimated Cost: 133
Estimated # of Rows Returned: 1

1) informix.p: INDEX PATH

Filters: (informix.p.reg_paym_dt >= datetime(2015-06-13 00:00:00) year to second AND informix.p.reg_paym_dt <= datetime(2015-06-13 23:59:59) year to second )

(1) Index Name: informix.paym__ix_reg_date
Index Keys: reg_date (Aggregate) (Serial, fragments: ALL)

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

Бой
Отключил индекс по полю. Время выполнения запроса менее секунды



Бэкап (виртуалка)


На бэкапе (на ругань про ISAM error: no free disk space for sort пока не обращаю внимания) Смотрю индексы paym__ix_reg_date, paym__ix_reg_paym_dt.
Они не «битые»

Запустил на выходные
oncheck -cI ok_arch:plat






QUERY: (OPTIMIZATION TIMESTAMP: 06-29-2015 13:33:18)
------



Estimated Cost: 4
Estimated # of Rows Returned: 1

1) informix.p: INDEX PATH

Filters: ((informix.p.recv_code IN (110 , 101 , 510 , 810 , 403 , 72101 , 11101 )AND informix.p.pm_state = 3 ) AND informix.p.paym_ord > 0 )

(1) Index Name: informix.paym__ix_reg_paym_dt
Index Keys: reg_paym_dt (Serial, fragments: ALL)
Lower Index Filter: informix.p.reg_paym_dt >= datetime(2015-06-13 00:00:00) year to second
Upper Index Filter: informix.p.reg_paym_dt <= datetime(2015-06-13 23:59:59) year to second


Query statistics:
-----------------

Table map :
----------------------------
Internal name Table name
----------------------------
t1 p

type table rows_prod est_rows rows_scan ti me est_cost
-------------------------------------------------------------------
scan t1 81901 1 464778 00:09.44 5

type rows_prod est_rows rows_cons time
-------------------------------------------------
group 1 1 81901 00:09.53

Создал индекс (не убивая старого, время создания 8 часов)
create index paym__ix_reg_paym_dt_n on .plat (reg_paym_dt desc) online;

Код: sql

1.
2.
3.
4.
5.
6.
7.

SELECT min(p.reg_date)
FROM plat p
WHERE p.reg_paym_dt >= '2015-06-13 00:00:00'
AND p.reg_paym_dt <= '2015-06-13 23:59:59'
AND p.recv_code in (110,101,510,810,403,72101,11101)
AND p.pm_state = 3
AND p.paym_ord > 0

QUERY: (OPTIMIZATION TIMESTAMP: 06-29-2015 08:32:11)
------
SELECT min(p.reg_date)
FROM plat p
WHERE p.reg_paym_dt >= '2015-06-13 00:00:00'
AND p.reg_paym_dt <= '2015-06-13 23:59:59'
AND p.recv_code in (110,101,510,810,403,72101,11101)
AND p.pm_state = 3
AND p.paym_ord > 0

Estimated Cost: 3
Estimated # of Rows Returned: 1

1) informix.p: INDEX PATH

Filters: ((informix.p.recv_code IN (110 , 101 , 510 , 810 , 403 , 72101 , 11101 )AND informix.p.pm_state = 3 ) AND informix.p.paym_ord > 0 )

(1) Index Name: informix.paym__ix_reg_paym_dt_n
Index Keys: reg_paym_dt (desc) (Serial, fragments: ALL)
Lower Index Filter: informix.p.reg_paym_dt <= datetime(2015-06-13 23:59:59) year to second
Upper Index Filter: informix.p.reg_paym_dt >= datetime(2015-06-13 00:00:00) year to second


Query statistics:
-----------------

Table map :
----------------------------
Internal name Table name
----------------------------
t1 p

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 81901 1 464778 00:23.71 4

type rows_prod est_rows rows_cons time
-------------------------------------------------
group 1 1 81901 00:23.85


Запрос отработал за 23.85 секунды, по старому индексу работает 09.53.
Пока никаких мыслей, что делать с таблицей на бою. Напоминаю, как временный вариант, сейчас запрос работает через временную таблицу

Еще раз спасибо за ответы.
АнатоЛой, это запрос работал раньше очень быстро. Мне показали логи, выполнявшие этот запрос и ранее. Просто в какой-то момент времени (пару недель назад, начал «дурить» запрос. На эмоциях я и создал тему, что полетел индекс.
После восстановления бэкапа на тестовой среде (виртуалка) оказалось, что
А) индексы не битые (сейчас проверяю индексы varchar (255,16) когда выдавалось сообщениеno free disk space for sort)
Б) в трейсе на обоих серверах выборка идет по одному и тому же индексу (только на бою работает около часа, а на тестовом сервере пару минут)
В) update statistics high - не помог
Из-за чего это произошло – пока не понятно. И как "вылечить" это тоже ясности нет. Дня через 3 попробую содать индекс как советовал Павел (запрос не массовый).

Спасибо АнатоЛой.
Будем "копать" в направлении, указанных Вами.
Но в голове пока не укладывается, да и опыта не хватает как такое может быть ("тормоз" на запросе).
Отобрать энное кол-во строк, а из них выбрать одну min (date). Время должно уйти только на сканирование строк в диапазоне.

select min (date) from (select ... into temp) менее 1 сек
SELECT {+AVOID_INDEX (plat paym__ix_reg_date}) min (date) менее 1 сек
SELECT {+INDEX (plat paym__ix_reg_paym_dt)} min (date) менее 1 сек
select min (date) 55 минут
SELECT {+INDEX (plat paym__ix_reg_date)} min (date) 55 минут

GVF112GVF.
oncheck -pl выдает следующие уровни
Level 0
Level 1
Level 2
Level 3

Размер страницы 16К

Индексы по полям reg_date, reg_paym_dt разнесены в разные dbspace, данные в эти dbspace не входят

Спасибо АнатоЛой за ответ.
Решение проблемы по времени не критично, т.к. обходные варианты найдены (хинты или через темповую таблицу)

автор1. Вопрос: "Как так могло случиться?"
Ответ: Туча вариантов. Вы действительно готовы все их сейчас обсуждать? Или ожидаете, что кто-то готов вам их просто так рассказать?!


2. Вопрос: "Почему запрос, где сервер выбрал индекс по reg_date (а не по reg_paym_dt), работает на 4 порядка дольше?...
"Отобрать энное кол-во строк, а из них выбрать одну min (date). Время должно уйти только на сканирование строк в диапазоне."

Ответ:
Потому что в условиях фильтрации нет ограничений по полю reg_date.
min у вас по reg_date, а фильтр у вас по reg_paym_dt.
И при выбранном индексе по reg_date сервер для получения ответа на вопрос вычитывает ВСЮ таблицу.
Кстати: Может у вас reg_date - это reg_paym_dt без времени - так сервер про это не знает :).

1. Не готов и не ожидаю.
2. У меня последний вопрос. Зачем серверу вычитывать всю таблицу, когда я просил его выбрать минимальную дату (пусть и не полю, указанному в условии) из некоторого диапазона выбранных строк.
Раньше он так и работал.
Также. При задании ограничений в 2-3 часа запрос не сканирует всю таблицу.
Прошу «ногами не бить». Если есть предположения, ответьте.

ЗЫ. Тип полей reg_date и reg_paym_dt DATETIME YEAR TO SECOND.

Спасибо ОГРОМНОЕ. Ваш первый ответ (за 01/07/15) открыл мне много того, о чем я представления не имел.

авторПредположения:

Первое:
раньше вы тоже пробовали запускать запрос с фильтром за целый день и больше? Если да, использовали ли вы раньше фильтр за последние дни?

Второе:
не поменялся ли принцип сбора статистики: который был раньше, и который вы имеете сейчас.

Третье:
понимаете ли вы, что просто "UPDATE STATISTICS HIGH" :
а) это не идеальная статистика, особенно для вашего случая;
б) имеет ещё параметры;

Четвёртое:
вы можете заставить сервер работать лучше, если не будете ему сами указывать индексы,
а сможете ему "рассказать", как "связаны" reg_date и reg_paym_dt.
Ведь они как-то связаны, правда?
Ну, например,:
а) reg_date >=reg_paym_dt
б) reg_paym_dt >=reg_date
в) ABS(reg_paym_dt - reg_date) < 1 месяца
г) ...
Пятое: не поменялся ли заметно объём БД с того момента, как "раньше оно так и работало".

Шестое: не поменялись ли настройки сервера за этот период, которые могли повлиять на выбор оптимизатора.

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

Первое.
запрос запускается только за день, даты в основном (95%) предыдущий месяц. Запросов может быть 1-31
Второе.
Работает крон 1 раз в неделю (update statistics medium from table ..)
HIGHT запустил в ручную 1 только раз скриптом, что выдал Server Studio (сначала LOW, Hight,Medium)
Третье.
Понимаю. Буду досконально разбираться.

Четвертое.
p.reg_paym_dt >= p.reg_date. Запустил запрос с этим условием. Лучше не стало.





Пятое.
Сама БД растет (с начала года на 4 Гб), но таблица по которой делается выборка, практически не меняется. С одним но. Данные вставляются сотнями тысяч и сотнями тысяч удаляются. Кол-во строк на любой день может как прибавиться, так и убавиться.

Шестое.
Нет. Ежедневно копируется onconfig и добавляется к архиву (сравнил, параметры за последние 2 месяца не менялись).

Седьмое.
- как оно так случилось: раньше хорошо, теперь плохо;


А можно еще вопрос, не относящийся к этому топику?

Есть таблица, rowsize 5498. Она находится в dbspce, у которой pagesize равна 4096. Как будут 2 строки заполнять страницу. Одна полная и остаток + одна полная и остаток
или
две полных и остатки заполнят одну страницу?
Извините за назойливость.

авторСергей Б

Первое:
раньше вы тоже пробовали запускать запрос с фильтром за целый день и больше? Если да, использовали ли вы раньше фильтр за последние дни?


запрос запускается только за день, даты в основном (95%) предыдущий месяц. Запросов может быть 1-31

А остальные 5%? Можете расшифровать смысл реквизитов запроса и результата запроса/запросов? [/quote]

Смысл таков.
Если между организациями не идут остатки, то по запросу создается отчет, в который входят все документы с датой исполнения с .. по .. за каждый день. В шапку отчета включается дата min(reg_date). По поводу дат, reg_date - дата регистрации, reg_date_dt дата исполнения. Почти всегда эти даты равны, но могут иметь расхождения. Например: пришла транзакция, по которой необходимо проверить корректность реквизитов. Для этого делается запрос на сервер, который гарантирует корректность (еще одна организация). А этот сервер «лежит/выстроилась большая очередь».


автор Уточните,
- HIGH вручную вы запустили ДО первых проблем с запросом или после?
- зачем запускали?

Update statistics запускал после появления проблемы.
Т.к. Update statistics по этой таблице запускается раз в неделю в режиме medium, решил пересобрать статистику (а вдруг из-за этого)


автор2. При выполнении запроса поначалу можно его запускать с хинтом
{+EXPLAIN, AVOID_EXECUTE} (см. тут). План запроса будет выбран, но сам запрос не будет запущен. Очень экономит время при проверке гипотез "а как поведёт себя вот такой запрос в реальности" :).

Спасибо. Про это не знал.


авторСтранный у вас бизнес: "поиск виновных" вместо "повышаем компетентность и достигаем результатов". Вы выбираете гораздо более длинный путь поиска ответа, и получите гораздо меньший опыт, чем при выборе третьего, или хотя бы второго варианта :).

Я выясняю причину случившегося ТОЛЬКО ДЛЯ СЕБЯ (кстати, от Ваших ответов я много нового для себя открыл). А приложение давно подправлено и работает быстро.




авторПятое: не поменялся ли заметно объём БД с того момента, как "раньше оно так и работало".

Сама БД растет (с начала года на 4 Гб), но таблица по которой делается выборка, практически не меняется. С одним но. Данные вставляются сотнями тысяч и сотнями тысяч удаляются. Кол-во строк на любой день может как прибавиться, так и убавиться.

Это существенное уточнение. Я так понимаю, такое удаление и вставка происходит разы в день.
А статистика ПОСЛЕ таких вставок и удалений обновляется сразу?
И какие характеристики пакетов на удаление и вставку? А-ля, "перезалей все записи у которых reg_paym_dt = сегодня или вчера?

Данные вставляются в эту таблицу постоянно с периодичностью 5 минут. Ночью запускается процедура, которая просматривает записи на сервере А по такой же таблице за прошедший день и если такая запись есть на сервере «Б» (это наш случай), то делает апдейт всей строки. Если строки нет, делает вставку.
Удаление. Выбираются данные за один день - «текущий день» минус энное количество дней. Данные копируются на сервер «В», а скопированные строки удаляются. Статистика по этой таблице выполняется один раз в неделю в режиме МЕДИУМ.



авторИ теперь всё-таки по поводу

- как сделать, чтобы работало хорошо.
Итак, у вас есть 95% запросов, для которых из имеющихся индексов индекс по (reg_paym_dt) - идеал.
Используйте сознательно хинт для этих 95% - и не парьтесь. :)

Но если:
- есть возможность добавлять индексы;
- есть осознание, что новый индекс сам по себе доп.нагрузка, но минусов от добавления конкретного индекса меньше чем плюсов;
- есть желание узнать новое и доскональнее попрактиковаться в вопросах оптимизации,
то предлагаю следующие варианты;
+ Варианты А. Используем знания, про которые мы знаем, что вы знаете :)



+ Варианты Б. Используем знания, про которые мы НЕ знаем, знаете ли вы :)




Варианты B против вариантов А улучшит реальное выполнение приблизительно в (8+8)/(4+8)=1,3 раза за счёт размера ключа индекса.

Кроме того, если поля recv_code и/или pm_state добавляют селективности запросам (заметно уменьшают количество строк в массиве X), можно было бы и их добавить в индексы...

Про вариант Б. Его я не знал, спасибо.
Вариант А1 не помог.
Вариант А2 и Б - пробую
АнатоЛой, а есть предположения, почему все так стало?