Alexey Kovyazin, 26-May-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.7 Тб

Як ви можете бачити, усі індекси мають дуже малий розмір ключа - найбільший становить 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 біт) зростає:

прогрівання кешу 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 терабайта - ми підсилимо нашого птаха і змусимо його летіти.