Alexey Kovyazin, 26-Mayıs-2014

Birkaç gün önce, Firebird performansı ile veritabanı büyümesi arasındaki ilişkiye adanmış testlerimiz hakkında bir makale yayınladık ve burada (diğerlerinin yanı sıra) 1,7 Terabaytlık Firebird SQL veritabanını test ettik. Bu kadar büyük bir veritabanı hakkında daha fazla ayrıntıyı burada bulabilirsiniz.

Tablolar

Tablo 1’de temel özellikleriyle birlikte tabloların listesini bulabilirsiniz. Bu istatistikler gstat -a -r ile alınmış ve IBAnalyst aracımızla yorumlanmıştır.

Tablo	Kayıtlar	Kayıt Uzunluğu, bayt	Veri Sayfaları	Tablo Boyutu, Mb	İndeks Boyutu, Mb	Toplam, %
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

Tablo 1. 1,7 Terabaytlık Firebird SQL veritabanındaki tablolar ve ana parametreleri

Gördüğünüz gibi, en büyük tablo ORDER_LINE - 6 milyardan fazla kayıt içeriyor. Boyutu yaklaşık 600Gb ve bu tablo için 50Gb indeks var. Bu tablo veritabanının %34’ünü kaplıyor.

STOCK tablosu da çok büyük - yaklaşık 2 milyar kayıt içeriyor. Kayıt sayısı ORDER_LINE tablosundan daha az olsa da, STOCK veritabanında ~680Gb yer kaplıyor (%39), çünkü STOCK’taki kayıt uzunluğu 298.88 bayt iken ORDER_LINE’da sadece 60.09 bayttır. Buna bağlı olarak, STOCK için ilişkili indekslerin boyutu yalnızca 15Gb’dir.

Üçüncü en büyük tablo olan CUSTOMER, daha da büyük bir kayıt uzunluğuna sahip - 577.52 bayt - ve yalnızca 630 milyon kayıtla 378Gb (%21) yer kaplıyor.

İndeksler

Bu veritabanında çok fazla indeks yok, çünkü yalnızca testler için tasarlanmıştır - tabloların çoğunluğu yalnızca 1 indekse sahiptir - birincil anahtar. Gerçek dünya uygulamalarında geliştiriciler belirli kullanıcı isteklerini karşılamak için birçok indeks oluşturur, ancak burada indeksler en hızlı ekleme ve güncelleme işlemlerine odaklanmıştır - sonuç olarak, indeks boyutu tablo boyutunun yalnızca %5-10’udur, oysa genellikle %30-50’dir (indeks boyutunuz tablo boyutunun %50’sinden fazlaysa, veritabanı şemasını yeniden tasarlamayı veya belki de gereksiz ya da etkisiz indeksleri kaldırmayı düşünün).

Tablo 2’de bu veritabanındaki tüm indekslerin listesini ve gstat istatistiklerinden alınan ve IBAnalyst tarafından analiz edilen temel özelliklerini bulabilirsiniz:

İndeks	Tablo	Derinlik	Anahtar #	Anahtar uzunluğu, bayt	Benzersiz anahtar #	Boyut, 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

Tablo 2. Firebird 1.7Tb veritabanının indeksleri

Gördüğünüz gibi, tüm indeksler çok küçük anahtar boyutuna sahiptir - en büyüğü 1.41’dir, bu da indeksin çok etkili bir şekilde sıkıştırıldığı anlamına gelir - örneğin, ORDER_LINE için birincil anahtar 50 Gb alanda 6 milyar anahtar içerir. Bu çok iyi bir sonuçtur.

Derinliği 4 olan 2 indeks vardır. Bu, motorun istenen değeri bulmak için 4 indeks sayfası okuması yapması gerektiği anlamına gelir. İndeks derinliğinin 3’ten yüksek olmaması önerilir ve daha yüksekse veritabanı sayfa boyutu artırılmalıdır. Ancak, bu veritabanında zaten maksimum sayfa boyutuna sahibiz (16Kb), bu yüzden bununla yaşamak zorundayız.

Isıtma

Sorgularla devam etmeden önce “ısıtma” yapalım. Veritabanı büyük olduğunda, tablolarla ilişkili sistem verileri de büyüktür ve Firebird’ün gerekli sistem sayfalarını önbelleğe alması biraz zaman alır. Örneğin, ORDER_LINE tablosunun 10110 işaretçi sayfası vardır. Isıtma her büyük veritabanı için gereklidir.

Isıtma olmadan sorguların ilk çalıştırılması, ısıtma ile olduğundan önemli ölçüde daha uzun sürer.

Veritabanı önbelleğini ısıtmak için, sistem verilerini önbelleğe yüklemek üzere bir dizi basit sorgu çalıştıralım:

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;

Bu kadar basit bir veri şeması için bu yeterlidir. Yüzlerce tablosu olan büyük ve karmaşık veritabanları için ısıtma şeması çok daha karmaşık olabilir ve dikkatlice geliştirilmelidir.

Sonuç olarak, Firebird sürecinin bellek tüketimi (Firebird SuperServer 2.5.2 64 bit) artar:

Şekil 1. Veritabanı önbelleğinin ısıtılması

Sorgular

Isıtmadan sonra, kullanıcıların bu kadar büyük bir Firebird veritabanıyla nasıl çalışacağını tahmin etmek için birkaç tipik sorgu çalıştırabiliriz: tipik sorgular ve okuma işlemleri için yanıt süreleri ne olacak?

Sorgu	Plan ve istatistikler	Açıklama
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 tablosunu (21000 kayıt) CUSTOMER ile (630 milyon kayıt) belirli bir depo koşuluyla birleştirir
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	Önceki sorgu için kayıt sayısını hesaplar.
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	En büyük tablo ORDER_LINE (~6 milyar kayıt) üzerinde belirli bir depo kimliği için order_line kayıtlarını seçme koşuluyla sorgu. Birincil anahtar ORDER_LINE_PK bileşik olduğundan ve depo kimliğini içerdiğinden (CONSTRAINT ORDER_LINE_PK: Primary key (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER), bu sorguda etkili bir şekilde kullanılır.
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	Önceki sorgu için sayım. Aynı parametrelerle ikinci sorgu çok daha hızlı olacaktır, ancak farklı parametrelerle yapılan sorgular benzer sonuç gösterir.
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	Birleştirme ve 2 koşullu sorgu.
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	Belirli bir depo ve bölgedeki belirli bir müşteri için ayrıntıları gösterme sorgusu. Daha karmaşık birleştirmelerin performansını gösterir.

Özet

Gördüğünüz gibi, 1,7 Terabaytlık Firebird veritabanı yaygın okuma sorguları için oldukça iyi bir performans gösteriyor. Sorgu iyi tasarlanmışsa ve iyi indeksler kullanıyorsa, çok büyük veritabanlarında bile performans iyidir. Elbette, çok büyük veri kümeleri için uzun getirme süresi ve sayım işlemleri için uzun süre (Firebird belirli bir işlemde belirli bir sorgu için tam kayıt sayısını almak üzere tüm sayfaları ziyaret ettiğinden) gibi sorunlu noktalar vardır, ancak bu sorunlu noktalar deneyimli Firebird geliştiricileri tarafından iyi bilinir ve bunların üstesinden gelmek için veritabanını tasarlamanın yöntemleri vardır.

Peki ya ekleme ve güncelleme işlemleri için performans düşüşü? Firebird yapılandırmasının akıllıca ayarlanmasıyla bunu telafi etmek mümkün mü?

Bir sonraki makalede Firebird 1,7 terabaytlık veritabanı için ayar seçeneklerini ele alacağız - kuşumuzu güçlendirip uçuracağız.