Alexey Kovyazin, 26-Tháng Năm-2014

Cách đây vài ngày, chúng tôi đã đăng một bài viết về các bài kiểm tra của chúng tôi, dành cho mối quan hệ giữa hiệu suất Firebird và sự tăng trưởng của cơ sở dữ liệu, trong đó chúng tôi đã kiểm tra (trong số những thứ khác) cơ sở dữ liệu Firebird SQL 1.7 Terabyte. Tại đây bạn có thể tìm thấy thêm chi tiết về một cơ sở dữ liệu lớn như vậy.

Bảng

Trong bảng 1, bạn có thể tìm thấy danh sách các bảng với các đặc điểm chính. Số liệu thống kê này được lấy bằng gstat -a -r và được diễn giải bằng công cụ IBAnalyst của chúng tôi.

Bảng	Số bản ghi	Độ dài bản ghi, byte	Trang dữ liệu	Kích thước bảng, Mb	Kích thước chỉ mục, Mb	Tổng, %
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

Bảng 1. Các bảng và tham số chính của chúng trong cơ sở dữ liệu Firebird SQL 1.7 Terabyte

Như bạn có thể thấy, bảng lớn nhất là ORDER_LINE - nó chứa hơn 6 tỷ bản ghi. Kích thước của nó khoảng 600Gb và có 50Gb chỉ mục cho bảng này. Bảng này chiếm 34% cơ sở dữ liệu.

Bảng STOCK cũng rất lớn - nó chứa khoảng 2 tỷ bản ghi. Mặc dù số lượng bản ghi nhỏ hơn so với bảng ORDER_LINE, STOCK chiếm khoảng 680Gb trong cơ sở dữ liệu (39%), vì độ dài bản ghi trong STOCK là 298.88 byte so với chỉ 60.09 byte trong ORDER_LINE. Theo đó, kích thước của các chỉ mục liên quan cho STOCK chỉ là 15Gb.

Bảng lớn thứ ba, CUSTOMER, có độ dài bản ghi thậm chí còn lớn hơn - 577.52 byte, và chiếm 378Gb (21%) với chỉ 630 triệu bản ghi.

Chỉ mục

Không có nhiều chỉ mục trong cơ sở dữ liệu này, vì nó chỉ được thiết kế cho các bài kiểm tra - phần lớn các bảng chỉ có 1 chỉ mục - khóa chính. Trong các ứng dụng thực tế, các nhà phát triển tạo nhiều chỉ mục để phục vụ các yêu cầu cụ thể của người dùng, nhưng ở đây các chỉ mục tập trung vào việc chèn và cập nhật dữ liệu nhanh nhất - kết quả là kích thước chỉ mục chỉ chiếm 5-10% kích thước bảng, trong khi thông thường là 30-50% (nếu bạn có kích thước chỉ mục lớn hơn 50% kích thước bảng, hãy cân nhắc thiết kế lại lược đồ cơ sở dữ liệu, hoặc có thể loại bỏ các chỉ mục vô dụng hoặc không hiệu quả).

Trong bảng 2, bạn có thể tìm thấy danh sách tất cả các chỉ mục trong cơ sở dữ liệu này và các đặc điểm chính của chúng, được lấy từ số liệu thống kê gstat và được phân tích bởi IBAnalyst:

Chỉ mục	Bảng	Độ sâu	Số khóa #	Độ dài khóa, byte	Số khóa duy nhất #	Kích thước, Mb
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

Bảng 2. Các chỉ mục của cơ sở dữ liệu Firebird 1.7Tb

Như bạn có thể thấy, tất cả các chỉ mục có kích thước khóa rất nhỏ - lớn nhất là 1.41, điều đó có nghĩa là chỉ mục được nén rất hiệu quả - ví dụ, khóa chính cho ORDER_LINE chứa 6 tỷ khóa trong 50 Gb không gian. Đây là một kết quả rất tốt.

Có 2 chỉ mục có độ sâu = 4. Điều đó có nghĩa là engine cần thực hiện 4 lần đọc các trang chỉ mục để tìm giá trị được yêu cầu. Nên khuyến nghị rằng độ sâu chỉ mục không nên cao hơn 3, và nếu cao hơn, kích thước trang cơ sở dữ liệu nên được tăng lên. Tuy nhiên, chúng tôi đã có kích thước trang tối đa trong cơ sở dữ liệu này (16Kb), vì vậy chúng tôi cần chấp nhận điều đó.

Làm nóng

Trước khi chúng tôi tiếp tục với các truy vấn, hãy thực hiện “làm nóng”. Khi cơ sở dữ liệu lớn, dữ liệu hệ thống liên quan đến các bảng cũng lớn, và Firebird cần một khoảng thời gian để lưu vào bộ nhớ cache các trang hệ thống cần thiết. Ví dụ, bảng ORDER_LINE có 10110 trang con trỏ. Việc làm nóng là cần thiết cho bất kỳ cơ sở dữ liệu lớn nào.

Không có việc làm nóng, lần thực thi đầu tiên của các truy vấn sẽ mất nhiều thời gian hơn đáng kể so với khi có làm nóng.

Để làm nóng bộ nhớ cache của cơ sở dữ liệu, hãy thực hiện một loạt các truy vấn đơn giản để tải dữ liệu hệ thống vào bộ nhớ cache:

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;

Điều này là đủ cho một lược đồ dữ liệu đơn giản như vậy. Đối với các cơ sở dữ liệu lớn và phức tạp với hàng trăm bảng, lược đồ làm nóng có thể phức tạp hơn nhiều và cần được phát triển cẩn thận.

Kết quả là, mức tiêu thụ bộ nhớ của tiến trình Firebird (Firebird SuperServer 2.5.2 64 bit) tăng lên:

Hình 1. Làm nóng bộ nhớ cache của cơ sở dữ liệu

Truy vấn

Sau khi làm nóng, chúng tôi có thể chạy một số truy vấn điển hình để ước tính cách người dùng sẽ làm việc với một cơ sở dữ liệu Firebird lớn như vậy: thời gian phản hồi cho các truy vấn điển hình và các thao tác đọc sẽ như thế nào.

Truy vấn	Kế hoạch và số liệu thống kê	Mô tả
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	Nối bảng WAREHOUSE (21000 bản ghi) với CUSTOMER (630 triệu bản ghi), với điều kiện cho một kho cụ thể
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	Đếm số bản ghi cho truy vấn trước đó.
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	Truy vấn đến bảng lớn nhất ORDER_LINE (~6 tỷ bản ghi) với điều kiện chọn các bản ghi order_line cho một id kho cụ thể. Vì khóa chính ORDER_LINE_PK là khóa tổng hợp và chứa ID kho (CONSTRAINT ORDER_LINE_PK: Primary key (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER), nó được sử dụng hiệu quả trong truy vấn này.
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	Đếm cho truy vấn trước đó. Truy vấn thứ hai với cùng tham số sẽ nhanh hơn nhiều, nhưng các truy vấn với tham số khác nhau cho kết quả tương tự.
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	Truy vấn với phép nối và 2 điều kiện.
select first 10 c1.C_ID, c1.C_FIRST, c1.C_LAST, o1.O_ID,<br> o1.O_OL_CNT, i1.I_NAME, i1.I_PRICE, ol1.OL_AMOUNT<br> from customer c1<br> join orders o1 on (c1.c_w_id = o1.O_w_ID<br> and c1.C_D_ID = o1.O_D_ID and c1.C_ID = o1.O_C_ID)<br> join ORDER_LINE ol1 on (ol1.ol_w_id = c1.C_W_ID<br> and ol1.OL_D_ID = c1.C_D_ID<br> and ol1.OL_O_ID = o1.O_ID)<br> join Item i1 on (ol1.OL_I_ID = i1.i_id)<br> where o1.o_d_id = 1 and o1.o_w_id = 1 and c1.c_id = 10;	PLAN JOIN (C1 INDEX (CUSTOMER_PK),<br> O1 INDEX (ORDERS_PK),<br> OL1 INDEX (ORDER_LINE_PK),<br> I1 INDEX (ITEM_PK))<br> <br> STATISTICS<br> Current memory = 166807128<br> Delta memory = 6016<br> Max memory = 166879200<br> Elapsed time= 0.20 sec<br> Buffers = 9999<br> Reads = 1<br> Writes 0<br> Fetches = 4960	Truy vấn để hiển thị chi tiết cho một khách hàng cụ thể trong một kho và khu vực cụ thể. Nó thể hiện hiệu suất của các phép nối phức tạp hơn.

Tóm tắt

Như bạn có thể thấy, cơ sở dữ liệu Firebird 1.7 Terabyte cho thấy hiệu suất khá tốt cho các truy vấn đọc thông thường. Nếu truy vấn được thiết kế tốt và sử dụng các chỉ mục hiệu quả, hiệu suất vẫn tốt ngay cả trên cơ sở dữ liệu rất lớn. Tất nhiên, có những điểm khó khăn, như thời gian tìm nạp dài cho các tập dữ liệu rất lớn và thời gian lâu cho các thao tác đếm (vì Firebird truy cập tất cả các trang để có được số lượng bản ghi chính xác cho một truy vấn cụ thể trong một giao dịch cụ thể), nhưng những điểm khó khăn này đã được các nhà phát triển Firebird có kinh nghiệm biết rõ, và có các phương pháp để thiết kế cơ sở dữ liệu theo cách khắc phục chúng.

Nhưng còn sự suy giảm hiệu suất cho việc chèn và cập nhật thì sao? Có thể bù đắp nó bằng cách tinh chỉnh thông minh cấu hình Firebird không?

Trong bài viết tiếp theo, chúng tôi sẽ xem xét các tùy chọn tinh chỉnh cho cơ sở dữ liệu Firebird 1.7 terabyte - chúng tôi sẽ tăng tốc con chim của mình và làm cho nó bay.