Alexey Kovyazin, 26 мая 2014

Несколько дней назад мы опубликовали статью о наших тестах, посвященную взаимосвязи между производительностью Firebird и ростом баз данных, где мы тестировали (в том числе) базу данных Firebird SQL объемом 1,7 терабайта. Здесь вы можете найти более подробную информацию о такой большой базе данных.

Таблицы

В таблице 1 вы можете найти список таблиц с ключевыми характеристиками. Эта статистика была получена с помощью gstat -a -r и интерпретирована с помощью нашего инструмента IBAnalyst.

Таблица	Записей	Длина записи, байт	Страниц данных	Размер таблицы, Мб	Размер индексов, Мб	Итого, %
ORDER_LINE	6300024797	60.09	38880344	607505.3	50582.3	34
STOCK	2100000000	298.88	43783806	684121.9	15809.61	39
ORDERS	630004141	29.00	2692324	42067.56	4250.38	2
HISTORY	630000434	48.77	3446455	53850.86	0.00	3
CUSTOMER	630000000	577.52	24226054	378532.0	8675.78	21
NEW_ORDER	188999981	13.00	623764	9746.31	1260.84	1
DISTRICT	210000	103.92	1860	29.06	1.27	~0
ITEM	100000	82.73	756	11.81	0.52	~0
WAREHOUSE	21000	97.90	179	2.80	0.11	~0

Таблица 1. Таблицы и их основные параметры в базе данных Firebird SQL объемом 1,7 Терабайта

Как вы можете видеть, самая большая таблица - ORDER_LINE: она содержит более 6 миллиардов записей. Ее размер составляет около 600 Гб, и для этой таблицы есть 50 Гб индексов. Эта таблица занимает 34% базы данных.

Таблица STOCK также очень большая - она содержит около 2 миллиардов записей. Хотя количество записей меньше, чем в таблице ORDER_LINE, STOCK занимает ~680 Гб в базе данных (39%), потому что длина записи в STOCK составляет 298.88 байт против всего 60.09 байт в ORDER_LINE. Соответственно, размер связанных индексов для STOCK составляет всего 15 Гб.

Третья по величине таблица, CUSTOMER, имеет еще большую длину записи - 577.52 байт и занимает 378 Гб (21%) при всего 630 миллионах записей.

Индексы

В этой базе данных не так много индексов, поскольку она предназначена только для тестов - большинство таблиц имеют только 1 индекс - первичный ключ. В реальных приложениях разработчики создают множество индексов для обслуживания конкретных запросов пользователей, но здесь индексы сосредоточены на максимально быстрых вставках и обновлениях данных - в результате размер индексов составляет всего 5-10% от размера таблицы, хотя обычно это 30-50% (если размер индексов превышает 50% размера таблицы, подумайте о редизайне схемы базы данных или, возможно, об удалении бесполезных или неэффективных индексов).

В таблице 2 вы можете найти список всех индексов в этой базе данных и их ключевые характеристики, взятые из статистики gstat и проанализированные с помощью IBAnalyst:

Индекс	Таблица	Глубина	Ключей #	Длина ключа, байт	# уникальных ключей	Размер, Мб
ORDER_LINE_PK	ORDER_LINE	4	6300024797	1.41	6300024797	50582.34
STOCK_PK	STOCK	4	2100000000	1.00	2100000000	15809.61
ORDERS_PK	ORDERS	3	630004141	1.01	630004141	4250.38
CUSTOMER_LAST	CUSTOMER	3	630000000	0.00	1000	4029.64
CUSTOMER_PK	CUSTOMER	3	630000000	1.01	188999981	1260.84
NEW_ORDER_PK	NEW_ORDER	3	188999981	1.01	188999981	1260.84
DISTRICT_PK	DISTRICT	2	210000	1.37	210000	1.27
ITEM_PK	ITEM	2	100000	1.00	100000	0.52
WAREHOUSE_PK	WAREHOUSE	2	21000	1.00	21000	0.11

Таблица 2. Индексы базы данных Firebird 1.7Tb

Как вы можете видеть, все индексы имеют очень маленький размер ключа - самый большой равен 1.41, это означает, что индекс очень эффективно сжат - например, первичный ключ для ORDER_LINE содержит 6 миллиардов ключей в 50 Гб пространства. Это очень хороший результат.

Есть 2 индекса с глубиной = 4. Это означает, что движку необходимо выполнить 4 чтения страниц индекса, чтобы найти запрошенное значение. Рекомендуется, чтобы глубина индекса была не выше 3, и если она выше, размер страницы базы данных следует увеличить. Однако в этой базе данных уже максимальный размер страницы (16Кб), поэтому нам придется с этим жить.

Прогрев

Прежде чем мы продолжим с запросами, давайте выполним «прогрев». Когда база данных большая, системные данные, связанные с таблицами, также велики, и Firebird требуется некоторое время для кэширования необходимых системных страниц. Например, таблица ORDER_LINE имеет 10110 страниц указателей. Прогрев необходим для любой большой базы данных.

Без прогрева первое выполнение запросов займет значительно больше времени, чем с прогревом.

Чтобы прогреть кэш базы данных, давайте выполним серию простых запросов для загрузки системных данных в кэш:

select first 1 * from ORDER_LINE; select first 1 * from STOCK; select first 1 * from customer; select first 1 * from ORDERS; select first 1 * from ITEM;

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

В результате потребление памяти процессом Firebird (Firebird SuperServer 2.5.2 64 bit) растет:

прогрев кэша firebird

Рисунок 1. Прогрев кэша базы данных

Запросы

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

Запрос	План и статистика	Описание
`select first 10 w_id, w_name, c_id, c_last<br> from WAREHOUSE, customer<br> where c_w_id = w_id and c_w_id = 10000`	`PLAN JOIN (WAREHOUSE NATURAL,<br> CUSTOMER INDEX (CUSTOMER_PK))<br> STATISTICS<br> Current memory = 166919744<br> Delta memory = 5616<br> Max memory = 166977488<br> Elapsed time= 0.08 sec<br> Buffers = 10000<br> Reads = 1<br> Writes 0<br> Fetches = 51`	Соединение таблицы WAREHOUSE (21000 записей) с CUSTOMER (630 миллионов записей) с условием для конкретного склада
`select count(*)<br> from WAREHOUSE, customer<br> where c_w_id = w_id and c_w_id = 10000`	`PLAN JOIN (WAREHOUSE INDEX (WAREHOUSE_PK),<br> CUSTOMER INDEX (CUSTOMER_PK))<br> COUNT<br> ============<br> 30000<br> <br> Current memory = 166918560<br> Delta memory = -1184<br> Max memory = 166977488<br> Elapsed time= 0.16 sec<br> Buffers = 10000<br> Reads = 1175<br> Writes 0<br> Fetches = 60040`	Подсчет записей для предыдущего запроса.
`SELECT first 10 *<br> FROM ORDER_LINE<br> WHERE OL_W_ID = 10050`	`PLAN (ORDER_LINE INDEX (ORDER_LINE_PK))<br> STATISTICS<br> Current memory = 167013640<br> Delta memory = 95080<br> Max memory = 167055968<br> Elapsed time= 0.80 sec<br> Buffers = 10000<br> Reads = 10285<br> Writes 0<br> Fetches = 10424`	Запрос к самой большой таблице ORDER_LINE (~6 миллиардов записей) с условием выбора записей order_line для указанного идентификатора склада. Поскольку первичный ключ ORDER_LINE_PK является составным и содержит идентификатор склада (CONSTRAINT ORDER_LINE_PK: Primary key (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER), он эффективно используется в этом запросе.
`SELECT count(*)<br> FROM ORDER_LINE<br> WHERE OL_W_ID = 10050;`	`PLAN (ORDER_LINE INDEX (ORDER_LINE_PK))<br> <br> COUNT<br> ============<br> 299509<br> Current memory = 167125464<br> Delta memory = -7160<br> Max memory = 167267976<br> Elapsed time= 3.41 sec<br> Buffers = 10000<br> Reads = 1994<br> Writes 0<br> Fetches = 599170`	Подсчет для предыдущего запроса. Второй запрос с теми же параметрами будет намного быстрее, но запросы с другими параметрами показывают аналогичный результат.
`Select first 10 w_id, w_name, c_id, c_last<br> rom WAREHOUSE, CUSTOMER<br> where c_w_id = w_id and (c_w_id > 8000)<br> and (c_w_id < 10000)`	`PLAN JOIN (WAREHOUSE INDEX (WAREHOUSE_PK),<br> CUSTOMER INDEX (CUSTOMER_PK))<br> <br> STATISTICS<br> Current memory = 167687664<br> Delta memory = -651888<br> Max memory = 168931400<br> Elapsed time= 0.19 sec<br> Buffers = 10000<br> Reads = 31<br> Writes 0<br> Fetches = 63`	Запрос с соединением и 2 условиями.

| select first 10 c1.C_ID, c1.C_FIRST, c1.C_LAST, o1.O_ID, o1.O_OL_CNT, i1.I_NAME, i1.I_PRICE, ol1.OL_AMOUNT from customer c1 join orders o1 on (c1.c_w_id = o1.O_w_ID and c1.C_D_ID = o1.O_D_ID and c1.C_ID = o1.O_C_ID) join ORDER_LINE ol1 on (ol1.ol_w_id = c1.C_W_ID and ol1.OL_D_ID = c1.C_D_ID and ol1.OL_O_ID = o1.O_ID) join Item i1 on (ol1.OL_I_ID = i1.i_id) where o1.o_d_id = 1 and o1.o_w_id = 1 and c1.c_id = 10; | PLAN JOIN (C1 INDEX (CUSTOMER_PK), O1 INDEX (ORDERS_PK), OL1 INDEX (ORDER_LINE_PK), I1 INDEX (ITEM_PK)) STATISTICS Current memory = 166807128 Delta memory = 6016 Max memory = 166879200 Elapsed time= 0.20 sec Buffers = 9999 Reads = 1 Writes 0 Fetches = 4960 | Запрос для отображения деталей конкретного клиента в конкретном складе и районе. Он демонстрирует производительность более сложных соединений. |

Резюме

Как вы можете видеть, база данных Firebird объемом 1,7 терабайта показывает довольно хорошую производительность для типичных запросов на чтение. Если запрос хорошо спроектирован и использует хорошие индексы, производительность остается хорошей даже на очень большой базе данных. Конечно, есть проблемные места, такие как длительное время выборки для очень больших наборов данных и длительное время для операций подсчета (поскольку Firebird просматривает все страницы, чтобы получить точное количество записей для конкретного запроса в конкретной транзакции), но эти проблемные места хорошо известны опытным разработчикам Firebird, и существуют методы проектирования базы данных для их обхода.

Но что насчет снижения производительности при вставках и обновлениях? Можно ли компенсировать это умной настройкой конфигурации Firebird?

В следующей статье мы рассмотрим варианты настройки для базы данных Firebird объемом 1,7 терабайта - мы ускорим нашу птицу и заставим ее летать.