Projekt bazy danych do obsługi 1 miliarda wierszy i liczenia

Otrzymujemy dane GPS w czasie rzeczywistym z prędkością około 5000 pr. minuta (z 4 serwerów TCP). Każdy serwer używa pojedynczego połączenia do wstawienia danych i buforuje dane pomiędzy wstawkami. Co około 15 minut usługa pobiera te dane i przetwarza je na wyłączenia. Po wygenerowaniu podróży rzeczywiste dane GPS zwykle nie są tak ważne, tylko jeśli użytkownik chce zobaczyć trasę na mapie.

Problem polega na tym, że wydaje się, że baza danych stara się nadążyć za szybkością wprowadzania danych. Czasami, gdy obciążenie się zwiększa, czas wstawiania nagle gwałtownie wzrasta (> 30 sekund), co z kolei pozwala buforować więcej danych, co z kolei skutkuje większymi wstawkami i dłuższym czasem wstawiania.

Mam nadzieję, że otrzymam kilka uwag na temat obecnego projektu i niektórych pomysłów, które musimy poprawić, oraz odpowiedzi na niektóre z naszych pytań - i wszelkie inne wskazówki, które ludzie mogą mieć!

Obecny projekt

Dane są obecnie podzielone na tabele reprezentujące jeden tydzień, a dane starsze niż rok są archiwizowane w dodatkowej bazie danych. Całość jest połączona razem w edytowalnym widoku, który jest używany zarówno dla wstawek, jak i odczytów.

Projekt stołu

• Id (PK, unikalny identyfikator)
• DeviceId (FK, int)
• PersonId (FK, int)
• VehicleId (FK, int)
• TokenId (FK, int)
• UtcTime (PK, datetime2 (3))
• Szerokość geograficzna (liczba zmiennoprzecinkowa)
• Długość geograficzna (liczba zmiennoprzecinkowa)
• Prędkość (mała)
• Nagłówek (smallint)
• Satelity (tinyint)
• IOData (varbinary (100))
• IgnitionState (tinyint)
• UserInput (tinyint)
• CreateTimeUtc (datetime2 (3))

Wskaźniki

• DeviceId_CreateTimeUtc_Desc
• DeviceId_UtcTime_Desc (klastrowane)
• PersonId_UtcTime_Desc
• TokenId_UtcTime_Desc
• VehicleId_UtcTime_Desc

Co tydzień obecnie zajmuje około 10 GB, w tym indeksy, a obecnie w głównej bazie danych znajduje się około 300 GB danych.

Tabele danych w głównej bazie danych mają własną grupę plików z 1 plikiem, ale znajduje się na tym samym dysku, co wszystkie inne tabele w głównej bazie danych. Pomocnicza baza danych znajduje się na innym dysku, ale na tym samym komputerze.

Myślę, że co tydzień uruchamiamy także zadanie przebudowy indeksu, gdy używana jest nowa partycja tabeli (tydzień). Nie wykonuje się obkurczania.

Maszyna jest 8-rdzeniowym HP z 12 GB pamięci, a na dysku z główną bazą danych działa RAID 10.

Pomysły

• Ogranicz ilość danych przechowywanych w podstawowej bazie danych do np. Maksymalnie 1 miesiąca. Przynajmniej sprawiłoby to, że baza danych byłaby łatwiejsza w zarządzaniu w zakresie tworzenia kopii zapasowych / przywracania, ale czy możemy oczekiwać poprawy wydajności?
• Utwórz 2 pliki w grupie plików dla bieżących danych i rozpowszechnij je na 2 różnych partycjach fizycznych
• Twórz bazy danych master-slave przechowujące aktualne dane, aby wstawianie i odczytywanie odbywało się w różnych bazach danych
• Umieść pliki z bieżącymi danymi na dyskach SSD (czy tworzenie kopii lustrzanych wpłynęłoby na jakąkolwiek różnicę w wydajności dysków SSD?)

Daj mi znać, jeśli potrzebujesz więcej informacji. Jest bardzo wiele czynników wpływających na wydajność i prawdopodobnie równie wiele sposobów jej poprawiania.

5000 wkładek na minutę to około 83 wkładek na sekundę. Z 5 indeksami, czyli 400 fizycznych wierszy wstawianych na sekundę. Gdyby obciążenie było w pamięci, nie stanowiłoby to problemu nawet dla najmniejszych serwerów. Nawet jeśli była to wstawka wiersz po rzędzie, przy użyciu najbardziej nieefektywnego sposobu, jaki mogę wymyślić. 83 trywialne zapytania na sekundę nie są interesujące z punktu widzenia procesora.

Prawdopodobnie jesteś związany z dyskiem. Możesz to sprawdzić, sprawdzając statystyki oczekiwania lub STATISTICS IO.

Twoje zapytania prawdopodobnie dotykają wielu różnych stron, więc w puli buforów nie ma miejsca na wszystkie. Powoduje to częste odczytywanie stron i prawdopodobnie również losowe zapisy na dysku.

Wyobraź sobie tabelę, w której wkładasz fizycznie tylko na końcu z powodu stale rosnącego klucza. Zestaw roboczy będzie jedną stroną: ostatnią. Spowodowałoby to wygenerowanie sekwencyjnego We / Wy, jak również leniwy pisarz lub proces punktu kontrolnego zapisuje „koniec” tabeli na dysk.

Wyobraź sobie tabelę z losowo rozmieszczonymi wstawkami (klasyczny przykład: klucz prowadzący). Tutaj wszystkie strony są zestawem roboczym, ponieważ losowa strona zostanie dotknięta dla każdej wstawki. We / wy są losowe. To najgorszy przypadek, jeśli chodzi o zestaw roboczy.

Jesteś w środku. Twoje indeksy mają strukturę (SomeValue, SequentialDateTime). Pierwszy składnik częściowo randomizuje sekwencję zapewnianą przez drugi. Sądzę, że istnieje całkiem sporo możliwych wartości dla „ SomeValue”, dzięki czemu masz wiele losowo umieszczonych punktów wstawiania w swoich indeksach.

Mówisz, że dane są dzielone na 10 GB tabel tygodniowo. To dobry punkt wyjścia, ponieważ zestaw roboczy jest teraz ograniczony przez 10 GB (pomijając wszelkie odczyty, które możesz zrobić). Przy 12 GB pamięci serwera mało prawdopodobne jest, aby wszystkie odpowiednie strony mogły pozostać w pamięci.

Jeśli możesz zmniejszyć rozmiar tygodniowych „partycji” lub zwiększyć pamięć serwera o trochę, prawdopodobnie nic ci nie jest.

Spodziewałbym się, że wstawki na początku tygodnia są szybsze niż na końcu. Możesz przetestować tę teorię na serwerze deweloperskim, uruchamiając test porównawczy o określonym rozmiarze danych i stopniowo zmniejszając pamięć serwera, aż zobaczysz zbiornik wydajności.

Teraz, nawet jeśli wszystkie odczyty i zapisy mieszczą się w pamięci, nadal możesz mieć przypadkowe zabrudzenie IO z opróżnianiem stron. Jedynym sposobem, aby się tego pozbyć, jest zapisanie w pozycjach kolokowanych w indeksach. Jeśli w ogóle możesz przekonwertować swoje indeksy na (kolejne) klucze sekwencyjne, które bardzo by pomogły.

Jako szybkie rozwiązanie dodałbym warstwę buforującą między klientami a głównym stołem. Może nagromadzić 15 minut zapisów w tabeli pomostowej i okresowo ją spłukiwać. To usuwa obciążenia szczytowe i wykorzystuje bardziej wydajny plan do pisania na dużym stole.