Prędkość wstawiania SQLite zmniejsza się wraz ze wzrostem liczby rekordów z powodu indeksu

Oryginalne pytanietło

Dobrze wiadomo, że SQLitetrzeba dostroić aby osiągnąć szybkość wkładania rzędu 50k wkładek / s. Istnieje wiele pytań dotyczących powolnych szybkości wstawiania oraz bogactwa porad i testów porównawczych.

Istnieje równieżtwierdzi, że SQLite może obsługiwać duże ilości danych, z raportami 50+ GB nie powoduje żadnych problemów z odpowiednimi ustawieniami.

Postępowałem zgodnie z radami tutaj i gdziekolwiek indziej, aby osiągnąć te prędkości i jestem zadowolony z wkładek 35k-45k. Mam problem z tym, że wszystkie testy porównawcze pokazują tylko szybkie szybkości wstawiania z rekordami <1m. To, co widzę, jest takieprędkość wstawiania wydaje się być odwrotnie proporcjonalna do wielkości stołu.

Kwestia

Mój przypadek użycia wymaga przechowywania krotek od 500 m do 1 b ([x_id, y_id, z_id]) przez kilka lat (1 m rzędów / dzień) w tabeli linków. Wszystkie wartości to liczby całkowite od 1 do 2 000 000. Jest pojedynczy indeksz_id.

Wydajność jest świetna dla pierwszych 10m rzędów, ~ 35k wkładek / s, ale zanim stół ma ~ 20m rzędów, wydajność zaczyna cierpieć. Widzę teraz około 100 wstawek.

Rozmiar stołu nie jest szczególnie duży. Przy 20-metrowych rzędach rozmiar dysku wynosi około 500 MB.

Projekt jest napisany w Perlu.

Pytanie

Czy jest to rzeczywistość dużych tabel w SQLite, czy są jakieś tajemniceutrzymanie wysokie współczynniki wstawiania dla stołów o rzędach> 10m?

Znane obejścia, których chciałbym uniknąć, jeśli to możliweUpuść indeks, dodaj rekordy i ponownie indeksuj: Jest to dobre rozwiązanie, ale nie działa, gdy DB nadal musi być użyteczny podczas aktualizacji. Nie będzie działać, aby baza danych była całkowicie niedostępnax minut / dzieńPodziel tabelę na mniejsze podkatalogi / pliki: To zadziała w krótkim okresie i już z nim eksperymentowałem. Problem polega na tym, że podczas wysyłania zapytań muszę być w stanie pobrać dane z całej historii, co oznacza, że ​​w końcu trafię na limit 62 tabeli. Dołączanie, zbieranie wyników w tabeli tymczasowej i odłączanie setek razy na żądanie wydaje się być dużym nakładem pracy i kosztów, ale spróbuję, jeśli nie ma innych alternatyw.Zestaw SQLITE_FCNTL_CHUNK_SIZE: Nie znam C (?!), Więc wolę się tego nie nauczyć, żeby to zrobić. Nie widzę jednak żadnego sposobu na ustawienie tego parametru za pomocą Perla.AKTUALIZACJA

NastępującySugestia Tima że indeks powodował coraz wolniejsze czasy wstawiania pomimo twierdzeń SQLite, że jest w stanie obsłużyć duże zestawy danych, przeprowadziłem porównanie testów z następującymi ustawieniami:

wstawione wiersze:14 milionówzatwierdzić wielkość partii:50 000 rekordówcache_size pragma:10 000page_size pragma:4096temp_store pragma:pamięćjournal_mode pragma:kasowaćsynchronous pragma:poza

W moim projekcie, podobnie jak w wynikach testu porównawczego poniżej, tworzona jest tabela tymczasowa oparta na plikach i używana jest wbudowana obsługa SQLite do importowania danych CSV. Tabela tymczasowa jest następnie dołączana do bazy danych odbiorczych, a zestawy 50 000 wierszy są wstawiane za pomocąinsert-select komunikat. Dlatego czasy wstawiania nie odzwierciedlająplik do bazy danych wstaw czas, ale raczejstół do stołu wstaw prędkość. Uwzględnienie czasu importu CSV zmniejszyłoby prędkość o 25-50% (bardzo przybliżone oszacowanie, zaimportowanie danych CSV nie trwa długo).

Oczywiste jest, że posiadanie indeksu powoduje spowolnienie szybkości wstawiania w miarę wzrostu rozmiaru stołu.

Z powyższych danych jasno wynika, że ​​można przypisać prawidłową odpowiedźOdpowiedź Tima zamiast twierdzeń, że SQLite po prostu nie może sobie z tym poradzić. Oczywiście tomogą obsługiwać duże zestawy danychJeśli indeksowanie tego zestawu danych nie jest częścią przypadku użycia. Używam SQLite tylko do tego, jako backendu dla systemu rejestrowania, na chwilę obecnąnie należy zindeksować, więc byłem zaskoczony spowolnieniem, którego doświadczyłem.

Wniosek

Jeśli ktokolwiek chce znaleźć dużą ilość danych przy użyciu SQLitei zindeksuj,używając odłamków może być odpowiedź. Ostatecznie zdecydowałem się na użycie pierwszych trzech znaków skrótu MD5 w unikalnej kolumniez określić przydział do jednej z 4096 baz danych. Ponieważ mój przypadek użycia ma głównie charakter archiwalny, schemat nie ulegnie zmianie i zapytania nigdy nie będą wymagały chodzenia po kawałkach. Rozmiar bazy danych jest ograniczony, ponieważ bardzo stare dane zostaną zredukowane i ostatecznie odrzucone, więc ta kombinacja shardingu, ustawień pragma, a nawet niektórychdenormalizacja zapewnia miłą równowagę, która w oparciu o powyższe testy porównawcze utrzymuje prędkość wstawiania co najmniej 10k wkładek / sekundę.

questionAnswers(5)

yourAnswerToTheQuestion