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

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

Также важной причиной, которую мы имели в виду при создании базы данных Firebird объёмом 1 ТБ, была окончательная ликвидация распространённого восприятия 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 (прошивка CC1H)

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

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

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

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

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


Программное обеспечение	Версия
Операционная система	Windows XP Professional SP3, 32bit
Firebird	2.1.3 SuperServer (снапшот)
Загрузчик	Пользовательский загрузчик из теста на основе 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:

Индексы

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

Самый большой индекс был создан для таблицы 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 Viewer для интерпретации текстовых данных и просмотра не только показателей производительности базы данных, но и потребления 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)) ------ Информация о производительности —— Время подготовки = 15ms Время выполнения = 79ms Среднее время выборки = 6.08 ms Текущая память = 272 264 476 Максимальная память = 272 514 048 Буферы памяти = 16 384 Чтений с диска в кэш = 82 Записей из кэша на диск = 0 Выборок из кэша = 3 648	Простое соединение таблиц с 12400 и 372000000 записей, без условий WHERE. «Среднее время выборки = 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)) ------ Информация о производительности —— Время подготовки = 16ms Время выполнения = 78ms Среднее время выборки = 6.00 ms Текущая память = 272 266 148 Максимальная память = 272 514 048 Буферы памяти = 16 384 Чтений с диска в кэш = 88 Записей из кэша на диск = 0 Выборок из кэша = 3 656	Соединение тех же таблиц с условием, которое заставляет выбирать последние записи. «Среднее время выборки = 6.00 ms» - это время получения первой строки.
select count(*) from WAREHOUSE, customer where c_w_id = w_id and c_w_id = 10000 Результат = 30000	PLAN JOIN (WAREHOUSE INDEX (WAREHOUSE_PK), CUSTOMER INDEX (FK_CUST_WARE)) ------ Информация о производительности —— Время подготовки = 0ms Время выполнения = 453ms Среднее время выборки = 453.00 ms Текущая память = 272 263 844 Максимальная память = 272 514 048 Буферы памяти = 16 384 Чтений с диска в кэш = 1 048 Записей из кэша на диск = 0 Выборок из кэша = 60 024	Подсчет записей для предыдущего запроса

SELECT * FROM ORDER_LINE WHERE OL_W_ID = 500	Plan PLAN (ORDER_LINE INDEX (ORDER_LINE_PK)) ------ Информация о производительности —— Время подготовки = 0ms Время выполнения = 94ms Среднее время выборки = 7.23 ms Текущая память = 136 445 536 Максимальная память = 136 592 176 Буферы памяти = 8 192 Чтений с диска в кэш = 150 Записей из кэша на диск = 0 Выборок из кэша = 2 402	Запрос к самой большой таблице (3.8B записей). «Среднее время выборки = 7.23 ms» - это время получения первой строки.

	`<br>Plan<br>` `<br>PLAN (ORDER_LINE INDEX (ORDER_LINE_PK))<br>` `<br> <br>` `<br>------ Информация о производительности ------<br>` `<br>Время подготовки = 0ms<br>` `<br>Время выполнения = 3s 438ms<br>` `<br>Среднее время выборки = 0.01 ms<br>` `<br>Текущая память = 136 445 496<br>` `<br>Максимальная память = 136 592 176<br>` `<br>Буферы памяти = 8 192<br>` `<br>Чтений с диска в кэш = 1 840<br>` `<br>Записей из кэша на диск = 0<br>` `<br>Выборок из кэша = 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)) ------ Информация о производительности —— Время подготовки = 0ms Время выполнения = 125ms Среднее время выборки = 9.62 ms Текущая память = 272 270 824 Максимальная память = 272 514 048 Буферы памяти = 16 384 Чтений с диска в кэш = 91 Записей из кэша на диск = 0 Выборок из кэша = 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) Результат = 59 970 000	Plan PLAN JOIN (WAREHOUSE INDEX (WAREHOUSE_PK), CUSTOMER INDEX (FK_CUST_WARE)) ------ Информация о производительности —— Время подготовки = 0ms Время выполнения = 13m 4s 718ms Среднее время выборки = 784 718.00 ms Текущая память = 272 268 532 Максимальная память = 272 514 048 Буферы памяти = 16 384 Чтений с диска в кэш = 2 332 583 Записей из кэша на диск = 0 Выборок из кэша = 119 977 902	Подсчет записей для предыдущего запроса

Резюме

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

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

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

Контакты

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