FB 1.5 и большая таблица

Andrew Sagulin

Исходные условия:
FB 1.5.1.4481-Win32
W2k Server, 512 Mb, iP4 2.4 ГГц

Есть большая (27 млн записей, 2.5 гигабайта) таблица с биллинговыми данными за год. Запрос типа select max(datetime) from ... выполняется порядка 5 минут. Это хоть и много (для плана NATURAL и скорости чтения с винта 30 мегабайт в секунду должно быть не более 2 минут), но ожидаемо.
Однако при этом винда съедает под дисковый кеш всю оперативную память и свопится. Делать что-то ещё в это время практически невозможно - всё тормозит, при этом загрузка процессора не превышает 35%.
Можно ли как-нибудь от этого избавиться, не разбивая таблицу на таблицы меньшего размера, например, по месяцам? Не хотелось бы усложнять логику работы приложения и вводить в него операции CREATE TABLE, UNION и т.д...
Вообще, есть какие-нибудь удобные стратегии для работы с данными в таких условиях?

Ivan_Pisarevsky

скорости чтения с винта 30 мегабайт в секунду

Покажите мне винт способный выдать наружу 30 метров в секунду на запросах к БД, честное пионерское построюсь в очередь на покупку... или все же речь о сказевом рэйде?

alex_k · Сообщение **alex_k** » 14 мар 2005, 12:12

может индекс по этому полю нужен?

Сообщение **kdv** » 14 мар 2005, 12:18

Вообще, есть какие-нибудь удобные стратегии для работы с данными в таких условиях?

есть. например хранимые агрегаты.

Andrew Sagulin

Ivan_Pisarevsky писал(а): Покажите мне винт способный выдать наружу 30 метров в секунду на запросах к БД, честное пионерское построюсь в очередь на покупку... или все же речь о сказевом рэйде?

Обычный SATA винт Seagate. При копировании файла базы данных в nul даёт 38 мегабайт в секунду. Почему при последовательном сканировании базы данных он должен дать в разы меньше, при условии, что БД тоже заполнялась последовательно? Но вопрос не в этом, а в том, что съедается вся оперативка, т.е. 450 из 512 мегабайт ОЗУ "уходят" под дисковый кеш и всё это сопровождается интенсивным свопом.

Andrew Sagulin

alex_k писал(а):может индекс по этому полю нужен?

Да, несомненно, причём, как оказалось, обязательно descending, так как ascending при поиске max() не используется, а используется только при поиске min():

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

SQL> show index ama_datetime;
AMA_DATETIME INDEX ON AMA(DATETIME)
SQL> set planonly;
SQL> select max(datetime) from ama;

PLAN (AMA NATURAL)
SQL> select min(datetime) from ama;

PLAN (AMA ORDER AMA_DATETIME)

Дело в другом: всегда есть вероятность сделать какой-нибудь редкий запрос, который в повседневной работе не нужен (какая-нибудь статистика), поэтому создавать для него индекс, увеличивая размер БД и заметно уменьшая скорость добавления данных, смысла нет. Ничего страшного, если такой запрос будет выполняться хоть 20 минут, но при этом желательно не съедать всю память, тормозя сервер!

Пока склоняюсь к не очень симпатичному для меня варианту разбивки на таблицы по месяцам. Выглядеть будет не очень (особенно при выборке данных из 2-3 разных месяцев), но зато работать будет всяко быстрей и на insert-ах и на select-ах.

Merlin · Сообщение **Merlin** » 14 мар 2005, 15:21

Andrew Sagulin писал(а): Да, несомненно, причём, как оказалось, обязательно descending, так как ascending при поиске max() не используется, а используется только при поиске min():

Итак, открытие Америки, наконец, состоялось

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

Про жуткое последствие в виде увеличения размера базы я уже даже шутковать устал. Насчёт _заметного_ уменьшения скорости добавления данных - это тебе кто-то рассказал, или сам померил?

Если индекс нужен, значит он нужен, если нет - значит нет.

Andrew Sagulin писал(а): Ничего страшного, если такой запрос будет выполняться хоть 20 минут, но при этом желательно не съедать всю память, тормозя сервер!

Настраивай параметры сортировки в конфиге сервера и файлового кеша оси и будет тебе щастье.

Andrew Sagulin

Merlin писал(а):
Andrew Sagulin писал(а): Да, несомненно, причём, как оказалось, обязательно descending, так как ascending при поиске max() не используется, а используется только при поиске min():
Итак, открытие Америки, наконец, состоялось

А почему так? Ведь при любом направлении сортировки индекса в нём есть вся необходимая для вычисления max() и min() информация. Чем пробежка по дереву индекса в поисках max() отличается от выборки по условию при select?

dimitr · Сообщение **dimitr** » 14 мар 2005, 18:54

При обычном индексном поиске мы получаем битовую карту записей и потом проверяем их на соответствие нашей транзакции. Range scan выполняется от меньшего заданного ключа к большему.

Для поиска min мы идем в левый угол дерева и смотрим минимальный ключ. Если соотв. запись нам не видна, то берем следующий (больший) ключ и т.п. Для поиска max нам придется в случае облома брать предыдущую, но индексная структура содержит только прямые ссылки, а обратных нет. Это сделано для устранения дедлоков при параллельном сканировании индекса в разных направлениях и расщеплении/слиянии дерева.

Andrew Sagulin

Merlin писал(а): Настраивай параметры сортировки в конфиге сервера и файлового кеша оси и будет тебе щастье.

Последний совет про кэш оси дан с такой лёгкостью, что я подумал, что я отстал от жизни, и в Windows это стало возможно. Однако, порывшись в яндексе и гугле, ничего, кроме LargeSystemCache, который у меня и так равен нулю, я не нашёл. Поставил даже SP4, который (по отзывам в интернете) фиксит баги SP3 при работе с памятью. Однако память на большой таблице как съедалась, так и съедается.
Насколько я знаю, такая штука происходит при работе с mapped files. Может мне к разработчикам FB пойти - они что-нибудь подскажут?..

Ivan_Pisarevsky

Обычный SATA винт Seagate. При копировании файла базы данных в nul даёт 38 мегабайт в секунду. Почему при последовательном сканировании базы данных он должен дать в разы меньше, при условии, что БД тоже заполнялась последовательно? Но вопрос не в этом, а в том, что съедается вся оперативка, т.е. 450 из 512 мегабайт ОЗУ "уходят" под дисковый кеш и всё это сопровождается интенсивным свопом.

Эээ.. коллега, спускайся с небес на землю. Нет никакого последовательного чтения при работе с БД, есть море случайных запросов, то что БД заполняется последовательно это ни о чем не говорит, как файрберд распихает их по страничкам известно только ему, и один САТАшный винт просто конструктивно не способен выплюнуть столько иопсов, не дураки же ставят в сервера сказевые массивы, БД в пару гиг уже достаточно серьезная нагрузка на дисковую подсистему. Для подтверждения запусти перфмон и внимательно посмотри на счетчики по очереди к диску, трансфер с диска и количество операций с диском в секунду.
Безусловно надо начать с оптимизации индексов, потом добить памяти хотя бы пару гиг, ну и серьзному серваку, серьезную дисковую ПС, а не один САТАшный накопитель.

Пока склоняюсь к не очень симпатичному для меня варианту разбивки на таблицы по месяцам. Выглядеть будет не очень (особенно при выборке данных из 2-3 разных месяцев), но зато работать будет всяко быстрей и на insert-ах и на select-ах.

ИМХО, гораздо бОльший гемор, чем нарастить мускулы железу.

dimitr · Сообщение **dimitr** » 15 мар 2005, 11:28

Andrew Sagulin писал(а):Однако память на большой таблице как съедалась, так и съедается.

У тебя супер или классик? И что это за "делать что-то еще" на серваке с такой базой?

Я бы предложил собрать себе FB с отключенным файловым кешем...

Сообщение **kdv** » 15 мар 2005, 13:59

я бы предложил не выполнять select max по таким здоровым таблицам. или уж если выполнять, то осознанно понимать что это может вызвать сборку мусора и вообще будет медленно. мой намек на хранимые агрегаты, по-моему, остался незамеченным.

Andrew Sagulin

dimitr писал(а):
У тебя супер или классик? И что это за "делать что-то еще" на серваке с такой базой?

супер.

Этот так называемый "сервак" - на самом деле обычная персоналка, на которой кроме базы делается "всё"

: компилируются программы, шарятся в инете, пишутся в ворде бумажки, в отдельной терминальной сессии крутится фидошная нода. В другой терминальной сессии мой шеф считает статистику. Тихонько работает апач, чтобы по интранету отдавать мелкую статистику в ещё одно подразделение. Есть ещё одна малюсенькая БД в 150 мегабайт, которая хранит информацию дочернего предприятия. Тут же хранится 15 гигабайт биллинговых фалов. До недавнего времени на этой персоналке было всего 256 мегабайт ОЗУ, и только путём хитрой комбинации у начальства удалось выбить ещё 256.
У нас даже на "сервере" повремёнки с базой в 12 гигабайт стоит такой же компьютер. Но там вышли из положения, разбив данные по отдельным дням, иначе работать было просто невозможно.

dimitr писал(а): Я бы предложил собрать себе FB с отключенным файловым кешем...

Чтобы как в Оракле - использовать только собственный кэш, не задействуя кэш операционки? Это идея...

kdv писал(а): мой намек на хранимые агрегаты, по-моему, остался незамеченным.

Да нет, я его заметил.

max() привёден как пример простого запроса с планом NATURAL, на котором операционка жутко тормозит.

Хоть это и непосредственно и не относится к теме БД, но я хотел бы объяснить, зачем, собственно, мне нужна такая база. Есть биллинговые файлы электронной станции. За год (именно столько необходимо хранить данные) это порядка 15 гигабайт и есть заметная тенденция к росту. Результаты обработки этих файлов уходят в несколько подразделений. Я уже задолбался писать однотипные программы, которые только и делают, что сканируют эти файлы в поисках нужной информации и формируют отчёт в текстовом или другом виде. Это не только нудно, но и медленно. Я уже давно лелеял создать промежуточный, независимый от формата биллинговых файлов, слой, в котором хранились бы уже предобработанные данные. Первым этапом было создание промежуточного csv-файла. Это уже намного лучше, чем прямая работа с биллинговыми файлами, но требует аккуратности при работе, и удаление устаревших данных в начале csv-файла занимает значительное время, поэтому хранить в нём данные больше чем за месяц (а это 200 мегабайт) уже не рационально. Но часто информации за месяц недостаточно, и приходится опять шарится напрямую по биллинговым файлам.

Поэтому я решил загнать всё в БД. База нужна лишь как хранилище - нагрузка на неё просто никакая: однократное обновление раз в сутки и не больше одного, изредка двух только читающих запросов одновременно. Выбивать под такую задачу мощное железо нецелесообразно, да и никто мне его не даст. Таблица в 2.5 гигабайта - это только междугородные вызовы. Есть планы хранить в БД и городские вызовы, а это в сумме в 2-2.5 раза большее количество данных, т.е. 5-7 гигабайт. Понятно, что ждать на таких объёмах и таком железе рекордного быстродействия не приходится, но хотелось бы иметь какие-то реальные цифры, скажем, пару минут на добавления данных (200 тыс. записей объёмом в 400 мегабайт), минут 10-15 на поиске при плане NATURAL. Но, мне совершенно не нравится то, что при таком поиске у меня совсем не остаётся оперативки. Это не только не приятно, но и сильно в конечном итоге тормозит поиск, так как операционке приходится выгружать в своп массу страниц памяти, а если, не дай бог, в это время работает какая-нибудь программа, активно использующая память (та же статистика для шефа), то это вообще финиш... У меня винт до старости не доживёт.

Ivan_Pisarevsky

Если насилия избежать не удается, расслабьтесь и попробуйте получить удовольствие (с) не помню чей

Пишешь служебку, так мол и так, без приобретения сервака за 10 килобаксов дело будет дрянь, пусть начальство нанесет свою визу, типа душит жаба денех не дадим, потом служебку в рамочку и пусть все ее видят. Одной оптимизацией софта тебе здесь не вырулить. Через месяцок когда твой САТА винт перейдет в стабильное состояние, то биш уйдет в лучший мир, и все встанет, деньги найдутся сразу, пусть не 10 килобаков, но пяток изыщут (только не надо говорить, что нет денег, почему-то при форс мажоре они всегда находятся), а их вполне хватит на приличный сервер. Но бумажку напиши обязательно, ибо без нее из тебя сделают крайнего.

Истренне сочувствую

KDV сорри за болтовню.

Щукин В.В.

dimitr писал(а): ... Это сделано для устранения дедлоков при параллельном сканировании индекса в разных направлениях и расщеплении/слиянии дерева.

А дидлуки могут возникать при сканировании индексов ?

Сообщение **kdv** » 16 мар 2005, 10:40

не "дидлуки" а дэдлоки. deadlocks. могут, если не делать так.
например, модифицируем запись. Сервер блокирует ее, затем блокирует страницу ключей (целиком), где расположено значение ключа для этой записи. При этом неявно блокируются все остальные записи, ключи (значения столбцов по этому индексу) которых расположены на этой странице. В Informix такое, ну и в других серверах встречается.

Щукин В.В.

kdv писал(а):не "дидлуки" а дэдлоки. deadlocks. могут, если не делать так.
например, модифицируем запись. Сервер блокирует ее, затем блокирует страницу ключей (целиком), где расположено значение ключа для этой записи. При этом неявно блокируются все остальные записи, ключи (значения столбцов по этому индексу) которых расположены на этой странице. В Informix такое, ну и в других серверах встречается.

ну это при UPDATE. Речь шла о SELECT. При SELECT-е могут появлятся deadlocks по причине сканирования страниц индексов ?

Merlin · Сообщение **Merlin** » 16 мар 2005, 12:32

Andrew Sagulin писал(а):
kdv писал(а): мой намек на хранимые агрегаты, по-моему, остался незамеченным.
Да нет, я его заметил. max() привёден как пример простого запроса с планом NATURAL, на котором операционка жутко тормозит.

По части общей ситуации - сочувствую. По технической стороне вопроса:

1. Намёк ты не совсем понял

Кроме больших таблиц в самой базе заведи дополнительные, в которые складывай часто искомые агрегаты - суммы за период, количество записей, последние значения чего-то и т.п. И при формировании отчётов их используй. Если такие хранимые агрегаты нужны в real-time, их обычно ведут на триггерах, если для аналитики, для которой как правило запаздывание на день - пофик, то ночными просчётами или по требованию, однократно перед использованием при формировании нескольких однотипных отчётов. Я задачами такого типа, как биллинг не занимаюсь, но кажется в твоём случае это можно делать сразу после заливки данных за период.

2. При заливке у тебя тормозит всё-таки скорее всего диск. Если он на машине один и на нём и ось и tmp и swap, то так и будет. Но стоит посмотреть и
а) Модель данных на предмет трагически неуникальных индексов, обычно это FK. Например, на тип телефона, статический справочник из пяти записей с таблицы-миллионника. Если нарушение ссылочной целостности из-за обновлений справочника параллельно с работой в ссылающейся на него таблице исключено, то контроль лучше перенести в триггера и ликвидировать такой FK.
б) Методику массированной заливки на предмет поэкспериментировать с размером блока записей для коммита. Как поштучный коммит, так и всего миллиона разом - путь тормозов.
в) Методику массированной заливки в смысле самой вставки. Оптимален Insert Into Table Select From External Table. Если данные пропускаются через приложение с препроцессингом, то инсёрт должен быть параметризован и подготовлен однократно, а не собираться динамически для каждой операции. Ни в коем случае через Append-Post. Некоторые компоненты (например TIBQuery) тормозят на ParamByName и приемлемо работают через Params.

3. План NATURAL, конечно, тормоз, но память жрёт и загоняет ось в своп не натурал, а группировка сортировкой во временном файле при вычислении агрегатов, в том числе и твой макс. Поэкспериментируй с индексами для таких агрегатов, не жмись на диск, затраты времени при вставке в случае индексов с хорошей селективностью невелики, это сильно неуникальные индексы тормозят вставку. Ну и потом, насколько я понимаю, в твоём конкретном случае заливка идёт в монопольном доступе и индексы в это время вполне можно деактивировать и потом активировать взад, будет гораздо быстрее.

Вкратце вроде всё. Кстати, почти всё, что я сейчас сказал - резюме статей на этом сайте.

Andrew Sagulin

kdv писал(а):я бы посоветовал не морочить голову, и перепрочитать таки указанный тобой топик, и избавиться от select max.

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

В общем, при любой операции, которая затрагивает большое количество записей (статистика за месяц, sweep, gbak), начинается своп, великий и тормозной.
Я лучше явно выделю под кэш БД 200-250 мегабайт, но без системного кеширования. Сдаётся мне, тормоза будут меньше.

Кстати, в исходниках CreateFileMapping упоминается несколько раз. Никто не подскажет, который из них нужно править?
В конце концов, если не собирается из исходников, я бинарник подправлю. Чего не сделаешь ради эксперимента.