Зачем вообще создавать терабайтную базу данных на Firebird?

С каждым годом все больше и больше компаний используют СУБД Firebird как основную СУБД для хранения и обработки данных. На конференциях "Корпоративные базы данных" мы несколько лет подряд (2003-2007) приводили данные по средним объемам таких баз данных, и показывали ежегодный рост максимального объема хранимых данных. В настоящий момент это сотни гигабайт, объемы продолжают расти (см. врезку "Кто больше?"), и легко предсказать момент, когда эти базы данных станут в 2, 3 или 5 раз больше. Соответственно, администраторы и разработчики заинтересованы в изучении вопроса, как Firebird ведет себя на больших объемах данных, и как такими объемами управлять.

Также важной причиной, по которой мы провели этот тест, является расхожее мнение, что Firebird годится только для "небольших баз данных". Этот миф уже давно мертв, но некоторые аналитики и журналисты все еще упоминают его. Мы считаем, что наш тест поставит окончательный крест на этом мифе.

"Железо"

Firebird известен своей масштабируемостью, и тест подтвердил это целиком и полностью. Поскольку целью теста было только создать базу данных размером в терабайт, мы использовали обычный десктопный компьютер:

Процессор AMD Athlon 64 x2 5200
Память 4GB, (2 Samsung, 2 Huyndai)
Материнская плата MSI K9N Platinum
HDD1, операционная система и temp Seagate ST3160815AS, 160Gb, SATA II
HDD2, temp Firebird Hitachi HDT721964SLA360, 640Gb, SATA II
HDD2 Hitachi HDS728080PLA380, 80Gb, SATA I
HDD3, база данных Seagate ST31500341AS, 1.5Tb, SATA II, (FW CC1H)

То есть, мы просто подключили диск размером 1.5Tb на офисный десктоп, без каких-либо модификаций. Этот диск был отформатирован с размером кластера 16 килобайт, аналогичным размеру страницы БД).

Программы

Для теста мы использовали загрузчик из теста TPC-C (можно скачать отсюда http://ibdeveloper.com/tests/tpc-c/). Необходимо отметить, что этот загрузчик добавляет данные небольшими порциями во все таблицы по очереди, примерно так, как обычно происходит формирование заказов в нормальных системах.

Операционная система Windows XP Professional SP3, 32bit
Firebird 2.1.3 SuperServer (snapshot)
Загрузчик Написанный еще в начале 2000 годов загрузчик tpc-c.

План

План теста состоял в следующем:

  1. Создать базу данных и наполнить ее 1 терабайтом данных, без индексов
  2. Создать первичные и вторичные ключи с соответствующими индексами – таким образом база данных будет больше 1 терабайта
  3. Собрать статистику по базе данных
  4. Выполнить ряд запросов для определения производительности

Конфигурация БД и Firebird

Размер страницы БД 16384 байт, такая же как и кластер диска, для максимальной производительности ввода-вывода (1 операция чтения-записи на 1 страницу БД). В firebird.conf мы изменили параметр TempDirectories, чтобы он указывал на отдельный диск HDD2, размером 640 гигабайт, на котором было свободно ~300 гигабайт. Параметр DefaultDbCachePages = 8192, при этом SuperServer будет занимать ~140 мегабайт памяти.

Загрузка данных

Данные загружались порциями, т.к. компьютер использовался для работы, и нужно было предусмотреть небольшие перерывы в загрузке данных. Во время загрузки данных в течение дня производилась обычная работа сразу с 8-12 программами, запущенными одновременно – Word, Excel, Firefox, Chrome, TheBat, Delphi 2007, и т.д. Если бы мы выделили компьютер специально для теста, то загрузка была бы немного быстрее, поэтому скорость загрузки данных не является максимальной.

Общее время загрузки данных ~70 часов
Всего записей вставлено 6.2 миллиарда
Средняя скорость вставки 24500 записей в секунду, если считать вставку непрерывной.
Средний размер записи 146 байт (минимально 13 байт, максимально 600 байт)
Транзакций 646489

То есть, на вставку было потрачено четверо суток, после чего у нас получилась база данных с размером 1 терабайт (чуть больше - 1 099 900 125 184 байт). Ниже можно наблюдать рост размера базы данных и динамику транзакций в FBDataGuard Viewer:

fb1tbloadsmall

Индексы

Мы создавали индексы по очереди, поштучно, и замеряли время создания и размер промежуточного файла сортировки в temp. Самый большой индекс получился по самой большой таблице ORDER_LINE, разумеется. Это первичный ключ, состоящий из четырех столбцов (smallint, smallint, integer и smallint). Временный файл для сортировки индекса занял 182 гигабайта, и в базе данных индекс занял 29.3 гигабайт.
Интересно, что индекс по таблице с 3.8 миллиардами записей имел глубину 3 (т.к. размер страницы был 16к), так что объем ввода-вывода при поиске по этому индексу был в норме.

Статистика
После этого мы собрали статистику по базе данных. Это заняло 7 часов 32 минуты 45 секунд. В следующей таблице приведена общая статистика с дополнительными данными:


Таблица
Записей Размер, гигабайт Время select count(*) Время создания индекса Размер temp-файла, Гб Размер индекса в БД, Гб
WAREHOUSE
1240
0.002
0s
0
0
0.0
ITEM
100000
0.012
0.7s
-
-
0.0
DISTRICT
124000
0.017
0.7s
6s
-
0.0
NEW_ORDER
111 600 000
32
20m 00s
23m 00s
4.56
0.8
CUSTOMER
372 000 000
224
 
41m 00s
-
2.6
customer_last      
1h 52m 32s
12.4
2.3
fk_cust_ware      
2h 10m 51s
-
2.3
HISTORY
372 000 000
32
       
ORDERS
372 000 000
25
32m 00s
45m 41s
15.2
2.5
STOCK
1 240 000 000
404
 
3h 34m 44s
41.5
9.2
ORDER_LINE
3 720 051 796
359
 
12h 6m 18s
182.0
29.3

Примечание: символ "-" в ячейках означает что данные не были замеряны.

В более полном отчете будет приведена ссылка на статистику по базе данных для IBAnalyst и FBDataGuard.

Запросы

Первоначально, мы выполнили запросы select count(*) на некоторых таблицах (см. 4-ый столбец в таблице выше). Как вы знаете, из-за многоверсионности при select count(*) Firebird вынужден читать всю таблицу целиком, и это является достаточно "тяжелой" операцией для сервера. Обычно разработчики не выполняют select count, тем более на таблицах такого размера, но мы это сделали специально, чтобы оценить производительность Firebird и дисковой подсистемы, а также чтобы вы могли использовать эти данные для сравнения.
Также мы выполнили ряд запросов, и если честно, были удивлены хорошей производительностью:

Запрос

План и статистика

Описание

select w_id, w_name, c_id, c_last
from WAREHOUSE,  customer
where c_w_id = w_id

PLAN JOIN (WAREHOUSE NATURAL, CUSTOMER INDEX (FK_CUST_WARE))
 ------ Performance info ------
Prepare time = 15ms
Execute time = 79ms
Avg fetch time = 6.08 ms
Current memory = 272 264 476
Max memory = 272 514 048
Memory buffers = 16 384
Reads from disk to cache = 82
Writes from cache to disk = 0
Fetches from cache = 3 648

Простое объединение таблиц с 12400 и 372000000 записями. Avg fetch time = 6.08мс – это время выборки первой записи

select w_id, w_name, c_id, c_last
from WAREHOUSE,  customer
where c_w_id = w_id and c_w_id = 10000

PLAN JOIN (WAREHOUSE INDEX (WAREHOUSE_PK), CUSTOMER INDEX (FK_CUST_WARE))

------ Performance info ------
Prepare time = 16ms
Execute time = 78ms
Avg fetch time = 6.00 ms
Current memory = 272 266 148
Max memory = 272 514 048
Memory buffers = 16 384
Reads from disk to cache = 88
Writes from cache to disk = 0
Fetches from cache = 3 656

Объединение этих же таблиц с дополнительным условием по отбору warehouse N 10000

select count(*)
from WAREHOUSE,  customer
where c_w_id = w_id and c_w_id = 10000

Result = 30000

PLAN JOIN (WAREHOUSE INDEX (WAREHOUSE_PK), CUSTOMER INDEX (FK_CUST_WARE))

------ Performance info ------
Prepare time = 0ms
Execute time = 453ms
Avg fetch time = 453.00 ms
Current memory = 272 263 844
Max memory = 272 514 048
Memory buffers = 16 384
Reads from disk to cache = 1 048
Writes from cache to disk = 0
Fetches from cache = 60 024

Подсчет количества записей, выдаваемых предыдущим запросом (30 тысяч)

SELECT * FROM ORDER_LINE
WHERE OL_W_ID = 500

PLAN (ORDER_LINE INDEX (ORDER_LINE_PK))

------ Performance info ------
Prepare time = 0ms
Execute time = 94ms
Avg fetch time = 7.23 ms
Current memory = 136 445 536
Max memory = 136 592 176
Memory buffers = 8 192
Reads from disk to cache = 150
Writes from cache to disk = 0
Fetches from cache = 2 402

Запрос к самой большой таблице (3.7 миллиарда записей), фетч (выборка) только первых записей.

SELECT * FROM ORDER_LINE
WHERE OL_W_ID = 500

PLAN (ORDER_LINE INDEX (ORDER_LINE_PK))

------ Performance info ------
Prepare time = 0ms
Execute time = 3s 438ms
Avg fetch time = 0.01 ms
Current memory = 136 445 496
Max memory = 136 592 176
Memory buffers = 8 192
Reads from disk to cache = 1 840
Writes from cache to disk = 0
Fetches from cache = 598 636

Повторный запрос с выборкой всего результата на клиента (299245 записей)

select w_id, w_name, c_id, c_last
from WAREHOUSE,  CUSTOMER
where c_w_id = w_id and (c_w_id > 8000) and (c_w_id < 10000)

Plan
PLAN JOIN (WAREHOUSE INDEX (WAREHOUSE_PK), CUSTOMER INDEX (FK_CUST_WARE))

------ Performance info ------
Prepare time = 0ms
Execute time = 125ms
Avg fetch time = 9.62 ms
Current memory = 272 270 824
Max memory = 272 514 048
Memory buffers = 16 384
Reads from disk to cache = 91
Writes from cache to disk = 0
Fetches from cache = 3 659

Еще одно объединение, с указанием диапазона выбираемых warehouse

select count(*)
from WAREHOUSE,  customer
where c_w_id = w_id and (c_w_id > 8000) and (c_w_id < 10000)

Result = 59 970 000

PLAN JOIN (WAREHOUSE INDEX (WAREHOUSE_PK), CUSTOMER INDEX (FK_CUST_WARE))

------ Performance info ------
Prepare time = 0ms
Execute time = 13m 4s 718ms
Avg fetch time = 784 718.00 ms
Current memory = 272 268 532
Max memory = 272 514 048
Memory buffers = 16 384
Reads from disk to cache = 2 332 583
Writes from cache to disk = 0
Fetches from cache = 119 977 902

Подсчет количества записей, возвращаемого предыдущим запросом (~60 миллионов записей)

Итог

По нашему мнению, результаты этого эксперимента следующие

  1. Без сомнения, Firebird может работать с большими базами данных. На соответствующем оборудовании вполне можно было бы создать базу данных размером 32 терабайта, и получить такую же производительность.
  2. Отличное масштабирование при минимальных затратах. Терабайтная база данных создана на обычном настольном компьютере, и с ней вполне можно работать, если, разумеется, не выбирать на клиента миллионы записей или не делать select count(*) по таблицам размером в сотни гигабайт. При этом скорость выполнения поиска и объединений такая же, как на базах данных среднего размера (10-15 гигабайт).

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

 

Источник: http://www.ibase.ru/devinfo/fb1tb.htm

Автор: iBase.ru, IBSurgeon