Замедление INSERT при добавлении индекса

[Andrew Sagulin]

Есть таблица:

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

show table conn;
AMAFILE_ID                      (AMAFILE_ID) INTEGER Not Null
DATETIME                        (DATETIME) TIMESTAMP Not Null
ANUM                            (CONN_NUM) VARCHAR(34) Nullable
BNUM                            (CONN_NUM) VARCHAR(34) Nullable
DUR                             (DUR) INTEGER Not Null
ITGRP                           (TGRP) CHAR(6) Nullable
ILNO                            (LNO) INTEGER Nullable
OTGRP                           (TGRP) CHAR(6) Nullable
OLNO                            (LNO) INTEGER Nullable
CAUSE                           (CAUSE) INTEGER Not Null default 16

И два индекса к ней:

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

create index conn_datetime on conn (datetime);
create desc index conn_id on conn (amafile_id);

Статистика по таблице и индексам:

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

CONN (130)
    Primary pointer page: 138, Index root page: 139
    Average record length: 16.06, total records: 208433728
    Average version length: 0.00, total versions: 0, max versions: 0
    Data pages: 1950303, data page slots: 1952160, average fill: 16%
    Fill distribution:
	 0 - 19% = 267
	20 - 39% = 0
	40 - 59% = 0
	60 - 79% = 1
	80 - 99% = 1950035

     Index CONN_DATETIME (0)
	Depth: 3, leaf buckets: 212178, nodes: 208465569
	Average data length: 0.00, total dup: 171964393, max dup: 67
	Fill distribution:
	     0 - 19% = 105
	    20 - 39% = 3440
	    40 - 59% = 95451
	    60 - 79% = 12013
	    80 - 99% = 101169

    Index CONN_ID (1)
	Depth: 3, leaf buckets: 211301, nodes: 208450744
	Average data length: 0.00, total dup: 208450181, max dup: 801165
	Fill distribution:
	     0 - 19% = 4
	    20 - 39% = 17
	    40 - 59% = 116337
	    60 - 79% = 3
	    80 - 99% = 94940

Каждый день в эту таблицу добавляется где-то 400 тыс.записей. Добавление занимает примерно 5-6 минут.
Возникла необходимость сделать индексы по anum и bnum. Для начала сделал индекс по anum:

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

create index conn_anum on conn (anum);

Создание этого индекса заняло более 12 часов. Неприятно, но несмертельно. Хуже другое: после создания индекса conn_anum время добавления записей увеличилось в 6 раз. Естественно, до создания индекса по bnum дело не дошло, и индекс по anum тоже пришлось удалить.

Вот статистика по индексу anum (сразу после создания):

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

   Index CONN_ANUM (2)
	Depth: 3, leaf buckets: 189539, nodes: 208433728
	Average data length: 0.00, total dup: 207691694, max dup: 24246976
	Fill distribution:
	     0 - 19% = 0
	    20 - 39% = 0
	    40 - 59% = 71712
	    60 - 79% = 110
	    80 - 99% = 117717

Всего в столбце anum примерно 800000 уникальных значений.

Объясните мне, пожалуйста, почему при создании индекса по anum так сильно увеличилось время добавления записей (из-за сильной упаковки индекса conn_anum?), и что в этой ситуации можно сделать?

[kdv]

я тебя заранее предупреждаю, что кажется, эта таблица близка к лимиту в 36 гиг.

12 часов - подозрительно. я в 1997 году на IB 5.1 индекс по таблице в 20 гиг создавал 1 час.
Насчет замедления - подели 20 миллионов на 800 тысяч, получишь число дубликатов ключа в этом индексе. Так что, на таких объемах ....

можешь мне выслать статистику по базе - из ibanalyst или от gstat -a -r ?

[Andrew Sagulin]

я тебя заранее предупреждаю, что кажется, эта таблица близка к лимиту в 36 гиг.

Только 15 гигабайт занято. Будем максимум 20-25, но это только где-то через год набежит.

Может быть это потому, что комп слабенький (1 гиг ОЗУ, Pentium 2.4, хоть и отдельный, но IDE винт)?

[kdv]

по статистике вижу, что 15 гиг.

хоть IBA и не верещит по поводу индекса CONN_ANUM, в нем цепочки одинаковых ключей от 280 штук до 24249967 (24 миллиона дубликатов для одного из ключей).
Такой индекс у тебя должен чудовищно тормозить restore.

Другой индекс, CONN_ID в этом плане не лучше - дубликатов от 300к до 800к.

Собственно, тормоза у тебя могут быть связаны с файловым кэшем. Тут по следам теста tpc/r выяснилось, что FB при определенных объемах БД плохо реагирует на нехватку памяти у операционки для кэша. Например, запрос, перековыривающий половину БД в 1.5 гиг работает в 2 раза медленнее при 512Mb RAM, чем при 1GB Ram.

А машинка - да, слабовата, если бы temp был на отдельный винт, индекс бы быстрее создался, правда, не более чем в 1.5 раз.

[Merlin]

по статистике вижу, что 15 гиг.

хоть IBA и не верещит по поводу индекса CONN_ANUM, в нем цепочки одинаковых ключей от 280 штук до 24249967 (24 миллиона дубликатов для одного из ключей).
Такой индекс у тебя должен чудовищно тормозить restore.

Если этот индекс действительно нужен, в чём я лично не так чтоб глубоко, но таки сомневаюсь, ибо польза от него будет только при поиске тех записей, которых 280 штук или что-то в этом роде, а которых миллионы - страшный вред и тормоз, то это как раз тот случай, когда спасает уникальный композит с привинченным сзаду ID. Но тогда приходится держать ухо востро с планами запросов, чтоб оптимизатор его (индекс этот) не хватал при выборках записей с большим количеством дубликатов по первому сегменту.

[Andrew Sagulin]

хоть IBA и не верещит по поводу индекса CONN_ANUM, в нем цепочки одинаковых ключей от 280 штук до 24249967 (24 миллиона дубликатов для одного из ключей).
Такой индекс у тебя должен чудовищно тормозить restore.

Ага, сейчас делаю restore. По предварительным оценкам часов 12 будет молотить.
Я бы индекс anum не создавал, если бы не ... (см. ниже).

Собственно, тормоза у тебя могут быть связаны с файловым кэшем. Тут по следам теста tpc/r выяснилось, что FB при определенных объемах БД плохо реагирует на нехватку памяти у операционки для кэша. Например, запрос, перековыривающий половину БД в 1.5 гиг работает в 2 раза медленнее при 512Mb RAM, чем при 1GB Ram.

Мой случай, однозначно. Я индекс по anum создавал из-за того, что скан по таблице за месяц и более (> 1,5 гигабайт) сильно тормозит из-за свопа.
А можно поподробней про неправильную оценку кэша, и можно ли это как-нибудь поправить? Для меня это очень важно! Я из-за этого даже подумываю на другую СУБД уйти, так как любой скан по значительной части таблицы, gbak -b -g, и т.п. выполняется так долго и мучительно, что просто руки опускаются. На аналогичной базе в MySQL полный скан таблицы занимает всего 9 минут, здесь же это растягивается на полчаса и более.
Я даже как-то поднимал здесь этот вопрос, но был не совсем правильно понят.
http://forum.ibase.ru/phpBB2/viewtopic. ... sc&start=0

[kdv]

я бы попробовал еще гиг памяти воткнуть.
насчет файлового кэша, скана и т.п. - да, увы, ситуация печальная. Исходя из структуры БД может быть тебе вполне будет счастье и на MySQL...

[Andrew Sagulin]

Исходя из структуры БД может быть тебе вполне будет счастье и на MySQL...

Может и будет, но не счастье. На MyISAM медленно работает индексный скан по диапазону, так как каждый фетч строки сопровождается обращением к функциям ввода/вывода операционки. На юниксах это может быть и не очень заметно (там эта операция, насколько я знаю, относительно дешевая), но на Windows это вызывает большие накладные расходы. В общем, разница в скорости при индексном скане составляет 3-4 раза в пользу FB.
На InnoDB по тестовым оценкам размер таблицы будет раза в два больше, чем на FB, что тоже производительности не добавит. Кроме того, у MySQL есть один очень серёзный (с моей точки зрения) недостаток: DDL типа alter table или create index выполняется путём создания новой таблицы, копирования туда содержимого старой, и всё это сопровождается созданием всех индексов с нуля.
В общем, куда ни кинь - всюду клин.

P.S. Я понимаю, что со стороны мои попытки работать с такими объёмами данных на таком железе напоминают "стоя в гамаке на лыжах", но к сожалению, повлиять на эту ситуацию я никак не могу, вот и приходится извращаться.
P.P.S. А в FB 2.0 непорядок с кэшем поправят?

[Ivan_Pisarevsky]

Я даже как-то поднимал здесь этот вопрос, но был не совсем правильно понят.

Я тебя как раз так и понял:

P.S. Я понимаю, что со стороны мои попытки работать с такими объёмами данных на таком железе напоминают "стоя в гамаке на лыжах", но к сожалению, повлиять на эту ситуацию я никак не могу, вот и приходится извращаться.

Попробуй линух накатить... Например СуСе 9.2+фб1.5.2 у меня никаких затруднений не вызвали. Ставишь ОС, отключаешь загрузку xdm (один фиг серверу графика только лишний расход памяти), потом
rpm --install firebird-bla-bla
и все собстно, дальше разве парметры конфига ФБ подрихтовать.

P.P.S. А в FB 2.0 непорядок с кэшем поправят?

Если есть своп, то как ни крути...

[Oleg Loa]

Накатывать линух бесполезно, уже лучшепусть человек посмотрит в сторону Oracle/MSSQL на таких объёмах данных.

[Ivan_Pisarevsky]

полгига памяти+ 1 идеешный шпиндель+15 гиг база... тут хоть оракул, хоть мссиквел... будет разве что ползать.
Как КДВ предлагал: ночами считать промежуточные агрегаты, днем быстренько отдавать юзерам

[Oleg Loa]

Oracle как раз может помочь, и результаты TPCR тестов - тому пример!!!

P.S. Хотя проверить затыки в коде IB при указанных проблемах тоже никто не мешает