Moje pytanie składa się z 2 części.
- Czy istnieje sposób określenia początkowego rozmiaru bazy danych w PostgreSQL?
- Jeśli nie, to jak radzić sobie z fragmentacją, gdy baza danych rośnie z czasem?
Niedawno przeprowadziłem migrację z MSSQL do Postgres, a jedną z rzeczy, które zrobiliśmy w świecie MSSQL podczas tworzenia bazy danych, było określenie początkowego rozmiaru bazy danych i dziennika transakcji. Zmniejszyło to fragmentację i zwiększyło wydajność, zwłaszcza jeśli wcześniej znany był „normalny” rozmiar bazy danych.
Wydajność mojej bazy danych spada wraz ze wzrostem rozmiaru. Na przykład obciążenie, na które się narażam, zwykle zajmuje 10 minut. Wraz ze wzrostem bazy danych czas ten rośnie. Wydaje się, że wykonywanie VACUUM, VACUUM FULL i VACUUM FULL ANALYZE nie rozwiązuje problemu. Rozwiązaniem problemu z wydajnością jest zatrzymanie bazy danych, rozpakowanie dysku, a następnie wykonanie VACUUM FULL ANALYZE przywraca wydajność mojego testu z powrotem do pierwotnych 10 minut. To prowadzi mnie do podejrzeń, że fragmentacja powoduje ból.
Nie udało mi się znaleźć żadnego odniesienia do rezerwowania przestrzeni tabel / bazy danych w Postgres. Albo używam niewłaściwej terminologii i tym samym nie znajduję niczego, albo istnieje inny sposób ograniczania fragmentacji systemu plików w Postgres.
Jakieś wskazówki?
Rozwiązanie
Dostarczone odpowiedzi pomogły potwierdzić to, co zacząłem podejrzewać. PostgreSQL przechowuje bazę danych w wielu plikach, co pozwala jej rozwijać się bez obaw o fragmentację. Domyślne zachowanie polega na spakowaniu tych plików po brzegi danymi tabeli, co jest dobre w przypadku tabel, które rzadko się zmieniają, ale jest złe w przypadku tabel, które są często aktualizowane.
PostgreSQL wykorzystuje MVCC do zapewnienia równoczesnego dostępu do danych tabeli. W ramach tego schematu każda aktualizacja tworzy nową wersję wiersza, który został zaktualizowany (może to być sygnatura czasowa lub numer wersji, kto wie?). Stare dane nie są natychmiast usuwane, ale są oznaczane do usunięcia. Faktyczne usunięcie ma miejsce, gdy wykonywana jest operacja VACUUM.
Jak to się ma do współczynnika wypełnienia? Domyślny współczynnik wypełnienia tabeli wynoszący 100 w pełni pakuje strony tabeli, co z kolei oznacza, że na stronie tabeli nie ma miejsca na zaktualizowane wiersze, tj. Zaktualizowane wiersze zostaną umieszczone na innej stronie tabeli niż oryginalny wiersz. Jest to niekorzystne dla wydajności, jak pokazuje moje doświadczenie. Ponieważ moje tabele podsumowań są bardzo często aktualizowane (do 1500 wierszy / s), zdecydowałem się ustawić współczynnik wypełnienia na 20, tj. 20% tabeli będzie dotyczyło wstawionych danych wierszy, a 80% danych aktualizacji. Choć może się to wydawać nadmierne, duża ilość miejsca zarezerwowanego dla zaktualizowanych wierszy oznacza, że zaktualizowane wiersze pozostają na tej samej stronie, co oryginał, a strona tabeli nie jest pełna do czasu uruchomienia demona autovacuum w celu usunięcia przestarzałych wierszy.
Aby „naprawić” moją bazę danych, wykonałem następujące czynności.
- Ustaw współczynnik wypełnienia moich tabel podsumowań na 20. Możesz to zrobić w czasie tworzenia, przekazując parametr do CREATE TABLE lub po fakcie za pomocą ALTER TABLE. Wydałem następującą komendę plpgsql:
ALTER TABLE "my_summary_table" SET (fillfactor = 20); - Wydano VACUUM FULL, ponieważ zapisuje on całkowicie nową wersję pliku tabeli, a zatem domniemany zapisuje nowy plik tabeli z nowym współczynnikiem wypełnienia .
Po ponownym uruchomieniu testów nie widzę obniżenia wydajności, nawet jeśli baza danych jest tak duża, jak potrzebuję, i zawiera wiele milionów wierszy.
TL; DR - fragmentacja plików nie była przyczyną, była to fragmentacja obszaru tabel. Można to złagodzić, modyfikując współczynnik wypełnienia tabeli, aby dopasować go do konkretnego przypadku użycia.