Chcę obsłużyć duży zestaw danych (> 1 miliard wierszy) w SQL Server 2008 R2. Słyszałem, że jeśli skonfigurujesz „dzielenie” lub „partycjonowanie poziome”, przyspieszy to obsługę dużych zestawów danych, ponieważ dzieli tabele na wiele plików.
Czy korzystałeś już z shardingu lub partycjonowania poziomego? Jeśli tak, czy możesz zasugerować jakieś wzorce, które według twojego doświadczenia osiągają lepsze wyniki w porównaniu z pojedynczym dużym stołem?
Odpowiedzi:
Po pierwsze, zauważ, że 1 miliard wierszy można skutecznie obsłużyć za pomocą architektury partycjonowanej na zwykłym sprzęcie serwerowym. Dla tego woluminu danych egzotyczne współdzielone architektury nic nie będą potrzebne, jednak prawdopodobnie uzyskasz znaczne korzyści z partycjonowania tabel.
Sharding różni się od partycjonowania poziomego i implikuje architekturę „nic wspólnego”, która nie jest obsługiwana przez większość wersji SQL Server 1
SQL Server może obsługiwać partycjonowanie poziome, a architektura współdzielonego dysku będzie wystarczająca dla ~ 1 miliarda wierszy.
W SQL Server, tworzenie funkcji partycji wybiera partycję na podstawie wartości lub zakresów wartości w kolumnie w tabeli, np
create partition function F_AccPrdPart (int)
as range left for values (
180001 -- Dummy value
,199012 ,199112 ,199212 ,199312, 199412 ,199512
,199612 ,199712 ,199812 ,199912 ,200012 ,200112
,200212 ,200312 ,200412 ,200512 ,200612 ,200712
,200812
,200901, 200902, 200903, 200904, 200905, 200906
,200907, 200908, 200909, 200910, 200911, 200912
,201001, 201002, 201003, 201004, 201005, 201006
,201007, 201008, 201009, 201010, 201011, 201012
,201101, 201102, 201103, 201104, 201105, 201106
,201107, 201108, 201109, 201110, 201111, 201112
,202012
,939999 -- Dummy value
)
goNastępnie utwórz jedną lub więcej aplikacjami do przydzielenia partycji. W przypadku dużego zestawu danych te grupy plików można skonfigurować na różnych woluminach fizycznych. Należy pamiętać, że pamięć podłączana bezpośrednio będzie znacznie szybsza niż SAN w prawie wszystkich przypadkach. W poniższym przykładzie utworzylibyśmy 6 aplikacjami o nazwie PartVol1-PartVol6.
Można utworzyć jeden lub więcej schematów partycji, aby przydzielić partycje tabeli do aplikacjami na podstawie wartości funkcji partycji, np.
create partition S_AccPrdPart as partition F_AccPrdPart
TO ([PRIMARY]
,[PartVol1], [PartVol2], [PartVol3], [PartVol4], [PartVol5], [PartVol6]
,[PartVol1], [PartVol2], [PartVol3], [PartVol4], [PartVol5], [PartVol6]
,[PartVol1], [PartVol2], [PartVol3], [PartVol4], [PartVol5], [PartVol6]
,[PartVol1]
,[PartVol2], [PartVol3], [PartVol4], [PartVol5], [PartVol6], [PartVol1]
,[PartVol2], [PartVol3], [PartVol4], [PartVol5], [PartVol6], [PartVol1]
,[PartVol2], [PartVol3], [PartVol4], [PartVol5], [PartVol6], [PartVol1]
,[PartVol2], [PartVol3], [PartVol4], [PartVol5], [PartVol6], [PartVol1]
,[PartVol2], [PartVol3], [PartVol4], [PartVol5], [PartVol6], [PartVol1]
,[PartVol2], [PartVol3], [PartVol4], [PartVol5], [PartVol6], [PartVol1]
,[PartVol2]
,[PRIMARY]
,[PRIMARY])
goTen schemat jest przeznaczony do podziału na okres obrachunkowy. Często wykorzystywane są do tego również daty, chociaż można użyć dowolnego klucza.
Możesz utworzyć tabelę na schemacie partycji tak, jakby była to grupa plików, np
Create table FooTrans (
FooTransID int identity (1,1) not null
,AccPeriod int not null
,[...]
) on S_AccPrdPart (AccPeriod)
goZauważ, że tabela jest tworzona na schemacie partycji zamiast określonej grupy plików, a klauzula określa kolumnę, która ma być używana jako klucz partycji. Na podstawie klucza partycji wiersze w tabeli zostaną przydzielone do jednej z aplikacjami w schemacie partycji.
Uwaga: Przy projektowaniu schematu partycjonowania obowiązuje zasada, że każda partycja powinna mieć liczbę wierszy w najniższych 10 milionach, powiedzmy między 10 a 50 milionów w zależności od szerokości rzędów. Wolumin dysku, na którym znajduje się partycja, powinien być wystarczająco szybki, aby wykonać skanowanie co najmniej jednej partycji w ciągu kilku sekund.
Systemy partycjonowania, dzielenia i udostępniania
Trochę terminologii wydaje się tutaj, aby ujednoznacznić część dyskusji na ten temat.
System „współużytkujący nic” to system równoległy, w którym węzły nie mają wspólnej pamięci SAN, ale używają pamięci lokalnej dla węzła. Klasycznym przykładem tego typu architektury jest Teradata. Współdzielone systemy nic nie skalują się dobrze do bardzo dużych zestawów danych, ponieważ nie mają centralnych wąskich gardeł we / wy. Przepustowość we / wy skaluje się z liczbą węzłów w systemie.
System „dysku współdzielonego” to taki, w którym jeden lub więcej serwerów baz danych współdzieli jeden podsystem pamięci dyskowej. Baza danych może być pojedynczym serwerem z pamięcią lokalną lub podłączoną do sieci SAN lub klastrem serwerów podłączonych do wspólnej sieci SAN. Systemy tego typu są ograniczone przepustowością dostępną z podsystemu pamięci masowej.
„Sharding” jest terminem używanym do opisania podziału bazy danych na wiele serwerów fizycznych we wspólnej architekturze „nic”. Różne platformy będą miały większą lub mniejszą obsługę dzielonych baz danych. W kręgach Teradata termin ten nie jest używany, ponieważ Teradata przedstawia klientom przezroczysty obraz jednego systemu, mimo że architektura fizyczna jest wspólnym typem niczego.
Starsze wersje programu SQL Server mają ograniczoną obsługę dzielenia na fragmenty za pomocą rozproszonych widoków podzielonych na partycje. Microsoft tworzy teraz wersję SQL Server 2008 R2, która obsługuje architekturę „nic wspólnego” z jednym obrazem systemu, ale ta wersja jest dostępna tylko dla producentów OEM i można ją kupić tylko w pakiecie sprzętowym.
Na 1 miliard wierszy
Dla 1 miliarda wierszy (chyba że poszczególne rzędy są bardzo szerokie) wspólna architektura nic lub dzielona jest wygodnie w królestwie przesady. Ten typ woluminu może być obsługiwany na jednym serwerze o rozsądnej specyfikacji, jeśli ma odpowiednio szybki podsystem dyskowy.
Dysk z bezpośrednim dołączeniem lokalnym jest zdecydowanie najbardziej opłacalny pod względem ceny za wydajność. Pojedynczy kontroler SAS RAID może przyjmować kilka macierzy, a na serwerze można zainstalować wiele kontrolerów. W zależności od konfiguracji, nowoczesna macierz SAS 24-25 szczelin może przesyłać tysiące IOPS lub 1 GB + / s pod względem wydajności przesyłania strumieniowego; serwer z wieloma magistralami PCI-e i wieloma kontrolerami może teoretycznie obsłużyć więcej.
Wydajność niezbędną do pracy z bazą danych o wartości 1 miliarda wierszy można osiągnąć dość łatwo i tanio dzięki sprzętowemu serwerowi towarowemu i bezpośredniemu magazynowaniu tego typu. Można również użyć sieci SAN, ale może być potrzebnych wiele kontrolerów SAN, aby uzyskać równoważną wydajność, a sprzęt będzie prawdopodobnie droższy o rząd wielkości.
Jako ogólną rekomendację, używaj pamięci masowej z bezpośrednim dołączaniem dla aplikacji wymagających dużych wymagań We / Wy, chyba że potrzebujesz naprawdę długiego czasu sprawności. Błędy konfiguracji i kontroli zmian są znacznie większym źródłem nieplanowanych przestojów niż awaria sprzętu w nowoczesnych centrach danych.
Sieci SAN mogą zapewnić łatwiejszą do zarządzania platformę pamięci masowej, jeśli masz duży portfel aplikacji, ponieważ zapewniają one szereg scentralizowanych funkcji zarządzania pamięcią masową. Ma to jednak wysoką cenę, a uzyskanie wysokiej wydajności infrastruktury opartej na sieci SAN jest trudne i kosztowne.
1 Microsoft robi równoległą wersję SQL Server, ale jest ona dostępna tylko za pośrednictwem kanałów OEM dołączonych do sprzętu. Wersje dostępne z półki nie obsługują tej możliwości.