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

Многие компании работают с большими базами данных Firebird и полагаются на них в поддержке важных бизнес-операций. Некоторые базы данных Firebird уже имеют размер в сотни гигабайт и продолжают расти (см. раздел «Кто большой?»), и легко предсказать момент, когда они станут в 2, 3 или 5 раз больше. Поэтому администраторы баз данных и поставщики заинтересованы в исследовании поведения Firebird с большими базами данных и в получении рекомендаций по управлению ими.

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


## Оборудование

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

Таблица 1: Оборудование


Компонент	Параметры
CPU	AMDAthlon 64 x2 5200
RAM	4GB
Материнская плата	MSI K9N Platinum
HDD1 (операционная система и временные файлы)	ST3160815AS, 160GB, SATA II
HDD2 (вспомогательный)	HDT721064SLA360, 640GB,SATAII
HDD2 (вспомогательный)	HDS728080PLA380, 80GB, SATA I
HDD3 (база данных)	ST31500341AS, 1.5TB, SATA II (Firmware CC1H)

По сути, мы установили жёсткий диск на 1,5 Тб в один из наших офисных настольных компьютеров без каких-либо других модификаций. Этот диск был отформатирован с размером кластера 16 Кб (такой же, как размер страницы базы данных, как видно ниже).

Программное обеспечение

Поскольку это настольный компьютер, операционная система — Windows XP Professional SP3, 32-бит. Для проведения теста мы использовали загрузчик из набора инструментов на основе TPC (скачайте его с http://ibdeveloper.com/tests/tpc-c/, доступны как бинарные файлы, так и исходные коды).

Мы хотели бы подчеркнуть, что загрузчик вставляет данные так, как они вставлялись бы в реальном сценарии: записи вставляются и размещаются внутри базы данных (и в физических областях диска) в нескольких таблицах master-detail-subdetail, а не таблица за таблицей.

Таблица 2: Программное обеспечение


Программное обеспечение	Версия
Операционная система	Windows XP Professional SP3, 32bit
Firebird	2.1.3 SuperServer (snapshot)
Загрузчик	Пользовательский загрузчик из теста на основе tpc

План

У нас был очень простой план для этого эксперимента:

Создать базу данных и загрузить в неё 1 Тб данных без индексов
Создать первичные ключи и соответствующие индексы (так что фактически размер базы данных превышает 1 Тб)
Собрать статистику базы данных
Выполнить несколько SQL-запросов и оценить производительность базы данных

Конфигурация базы данных и сервера Firebird

База данных имеет размер страницы 16384 байта, такой же, как кластер жёсткого диска, чтобы максимизировать пропускную способность диска (чтение/запись 1 страницы за один цикл ввода-вывода).

В конфигурации Firebird мы настроили дополнительный каталог для временного пространства и указали его на диск объёмом 640 Гб (где было свободно ~300 Гб).

Этап загрузки

Данные загружались в эту базу данных в несколько этапов. Компьютер использовался во время операций загрузки как обычный настольный (у нас были MS Office, Firefox, IBAnalyst и т. д. — одновременно работало около 8-12 программ). Если бы мы выделили оборудование только для этой задачи, вероятно, это было бы быстрее, поэтому, пожалуйста, рассматривайте эти значения только как пример низкого уровня; они определённо не являются лучшими результатами.

Таблица 3: Операции загрузки

| |


Описание	Значение
Время загрузки	~70 часов
Всего вставлено записей	6,2 миллиарда
Средняя скорость вставки	24500 записей/секунду
Средний размер записи	146 байт (мин 13 байт, макс - 600 байт)
Транзакции	646489

Мы потратили ~4 дня на загрузку, и после этого у нас была база данных Firebird размером ровно 1 Тб (т. е. 1 099 900 125 184 байт).

Ниже вы можете увидеть динамику роста базы данных и транзакций в FBDataGuard Viewer:

Индексы

Мы создавали индексы один за другим и замеряли время их создания и соответствующий размер временного файла, используемого для сортировки.

Самый большой индекс был создан для таблицы ORDER_LINE. Его первичный ключ содержит четыре поля (Smallint, Smallint, Integer и Smallint). Временный файл для этого индекса сортировки составил 182 Гб, а итоговый размер индекса в базе данных — 29,3 Гб.

Интересно отметить, что даже индекс для таблицы с 3,8 миллиарда записей имеет глубину = 3, поскольку размер страницы составлял 16384 байта, поэтому при поиске данных по первичному ключу для этой таблицы нет накладных расходов.

Статистика

После этого мы собрали статистику базы данных. Это заняло 7 часов 32 минуты 45 секунд.

Мы поместили ключевую статистическую информацию в одну таблицу и включили некоторые запросы и измерения времени:

Таблица 4: Сводная статистика для базы данных 1 Тб


Имя таблицы	Количество записей	Размер, Гб	*Время выполнения select count()**	Время создания индекса	Размер временного файла, Гб	Размер индекса, Гб

| WAREHOUSE | 1240 | 0.002 | 0s | 0 | 0 | 0.0 | | ITEM | 100000 | 0.012 | 0.7s | - | - | 0.0 | | DISTRICT | 124000 | 0.017 | 0.7s | 6 | - | 0.0 | | NEW_ORDER | 111600000 | 32 | 20m 00s | 23m 00s | 4.56 | 0.8 | | CUSTOMER | 372000000 | 224 | - | 41m 00s | - | 2.6 | | customer_last | | | | 1h 52m 32s | 12.4 | 2.3 | | fk_cust_ware | | | | 2h 10m 51s | - | 2.3 | | HISTORY | 372000000 | 32 | - | - | - | - | | ORDERS | 372000000 | 25 | 32m 00s | 45m 41s | 15.2 | 2.5 | | STOCK | 1240000000 | 404 | - | 3h 34m 44s | 41.5 | 9.2 | | ORDER_LINE | 3720051796 | 359 | - | 12h 6m 18s | 182.0 | 29.3 |

Статистику базы данных можно скачать здесь.

Вы можете использовать бесплатную программу просмотра FBDataGuard Community Edition, чтобы интерпретировать текстовые данные и видеть не только метрики производительности базы данных, но и потребление CPU и памяти.

Запросы

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

После запросов select count мы выполнили запросы из реального сценария и, честно говоря, были поражены столь хорошими результатами. Убедитесь сами:


Запрос	Статистика	Описание
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 записей, без условий WHERE. «Avg fetch time = 6.08 ms» — это для выборки первой строки.
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	Соединение тех же таблиц с условием, которое заставляет выбирать последние записи. «Avg fetch time = 6.00 ms» — это для выборки первой строки.
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	Подсчёт записей для предыдущего запроса

SELECT * FROM ORDER_LINE WHERE OL_W_ID = 500	Plan 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.8B записей). «Avg fetch time = 7.23 ms» — это для выборки первой строки.

	`<br>Plan<br>` `<br>PLAN (ORDER_LINE INDEX (ORDER_LINE_PK))<br>` `<br> <br>` `<br>------ Performance info ------<br>` `<br>Prepare time = 0ms<br>` `<br>Execute time = 3s 438ms<br>` `<br>Avg fetch time = 0.01 ms<br>` `<br>Current memory = 136 445 496<br>` `<br>Max memory = 136 592 176<br>` `<br>Memory buffers = 8 192<br>` `<br>Reads from disk to cache = 1 840<br>` `<br>Writes from cache to disk = 0<br>` `<br>Fetches from cache = 598 636<br>`
SELECT * FROM ORDER_LINE WHERE OL_W_ID = 500	Тот же запрос к самой большой таблице (3.8B записей), но на этот раз мы выбрали все записи (выбрано 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	Соединение таблиц с 1240 записями и 372M записей.
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 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	Подсчёт записей для предыдущего запроса

Итоги

В этом эксперименте Firebird показывает следующие результаты

Несомненная способность работать с большими базами данных. Мы совершенно уверены, что можно создать и использовать базу данных размером 32 Тб на подходящем оборудовании, и Firebird покажет такую же высокую производительность, как и для баз данных меньшего размера (то есть 1 Тб и ниже).
Хорошая масштабируемость и удивительно малый объём. База данных размером 1 Тб была создана на обычном настольном компьютере и, что более важно, её можно использовать для выполнения обычных запросов: если вы не выбираете миллионы записей, скорость запросов такая же, как и для баз данных умеренного размера (10-15 Гб).

Это не конец эксперимента: мы намерены выполнить ещё несколько запросов, собрать дополнительную статистику и вскоре опубликовать более подробный отчёт. Оставайтесь с нами.

Контакты

Отправляйте все свои вопросы и запросы на [email protected]