Cyfroteka.pl

klikaj i czytaj online

Cyfro
Czytomierz
00517 005022 14474048 na godz. na dobę w sumie
Wysoko wydajny PostgreSQL 9.0 - książka
Wysoko wydajny PostgreSQL 9.0 - książka
Autor: Liczba stron: 464
Wydawca: Helion Język publikacji: polski
ISBN: 978-83-246-3062-2 Data wydania:
Lektor:
Kategoria: ebooki >> komputery i informatyka >> bazy danych >> postgresql - programowanie
Porównaj ceny (książka, ebook, audiobook).

Poznaj najlepsze techniki zwiększania wydajności PostgreSQL i sprawdzone rozwiązania najczęściej spotykanych problemów!

Mający za sobą już ponad piętnaście lat rozwoju PostgreSQL jest dziś potężnym systemem baz danych typu open source, o sprawdzonej architekturze i reputacji narzędzia niezawodnego oraz nieprzeciętnie wydajnego. Współdziała on ze wszystkimi popularnymi systemami operacyjnymi i jest w pełni zgodny z warunkami ACID. Te zalety sprawiają, że można go używać jako magazynu danych dla aplikacji oraz jako bazy danych dla aplikacji sieciowych. Jednak osiągnięcie maksymalnej wydajności PostgreSQL nie jest wcale zadaniem łatwym, a w trakcie korzystania z jego serwerów można napotkać powtarzające się trudności, zwłaszcza gdy wzrasta obciążenie serwera, a wymagania stają się coraz większe. Jeśli zatem nie chcesz tygodniami dochodzić do właściwych rozwiązań swoich problemów - oto książka, w której znajdziesz całą potrzebną Ci wiedzę.

Masz w rękach kompletny podręcznik, przeznaczony dla średnio i bardzo zaawansowanych administratorów baz danych, którzy już używają PostgreSQL lub dopiero zamierzają to zrobić. Najpierw zapoznasz się z najnowszymi wersjami tej platformy oraz dowiesz się, jak dobierać komponenty serwera, aby optymalnie wykorzystać możliwości systemu. Zobaczysz, jak testować wydajność sprzętu dla bazy danych oraz konfigurować dyski i system plików, aby zwiększać ich efektywność. Poznasz także parametry, których zmiana może powodować problemy, a ponadto najważniejsze ustawienia, ich znaczenie i zasady prawidłowego stosowania. Przeczytasz o tym, jak uzyskać użyteczne wyniki testów wydajności, a także o skutecznym indeksowaniu bazy danych, optymalizacji zapytań i partycjonowaniu danych na podzbiory. Na koniec dowiesz się, jak unikać najczęściej spotykanych problemów i rozwiązywać je, gdy już się pojawią.

Dzięki tej książce:

Zoptymalizuj swój serwer PostgreSQL i unikaj problemów, które mogą zmniejszyć jego wydajność!

Znajdź podobne książki Ostatnio czytane w tej kategorii

Darmowy fragment publikacji:

Idź do • Spis treści • Przykładowy rozdział • Skorowidz Katalog książek • Katalog online • Zamów drukowany katalog Twój koszyk • Dodaj do koszyka Cennik i informacje • Zamów informacje o nowościach • Zamów cennik Czytelnia • Fragmenty książek online Kontakt Helion SA ul. Kościuszki 1c 44-100 Gliwice tel. 32 230 98 63 e-mail: helion@helion.pl © Helion 1991–2011 Wysoko wydajny PostgreSQL 9.0 Autor: Gregory Smith Tłumaczenie: Robert Górczyński ISBN: 978-83-246-3062-2 Tytuł oryginału: PostgreSQL 9.0 High Performance Format: 170×230, stron: 464 Poznaj najlepsze techniki zwiększania wydajności PostgreSQL i sprawdzone rozwiązania najczęściej spotykanych problemów! • Jak dobierać komponenty serwera, aby maksymalnie wykorzystać jego możliwości? • Jak przeprowadzać testy wydajności całego systemu, od sprzętu po aplikację? • Jak skutecznie indeksować bazę danych i optymalizować zapytania? Mający za sobą już ponad piętnaście lat rozwoju PostgreSQL jest dziś potężnym systemem baz danych typu open source, o sprawdzonej architekturze i reputacji narzędzia niezawodnego oraz nieprzeciętnie wydajnego. Współdziała on ze wszystkimi popularnymi systemami operacyjnymi i jest w pełni zgodny z warunkami ACID. Te zalety sprawiają, że można go używać jako magazynu danych dla aplikacji oraz jako bazy danych dla aplikacji sieciowych. Jednak osiągnięcie maksymalnej wydajności PostgreSQL nie jest wcale zadaniem łatwym, a w trakcie korzystania z jego serwerów można napotkać powtarzające się trudności, zwłaszcza gdy wzrasta obciążenie serwera, a wymagania stają się coraz większe. Jeśli zatem nie chcesz tygodniami dochodzić do właściwych rozwiązań swoich problemów – oto książka, w której znajdziesz całą potrzebną Ci wiedzę. Masz w rękach kompletny podręcznik, przeznaczony dla średnio i bardzo zaawansowanych administratorów baz danych, którzy już używają PostgreSQL lub dopiero zamierzają to zrobić. Najpierw zapoznasz się z najnowszymi wersjami tej platformy oraz dowiesz się, jak dobierać komponenty serwera, aby optymalnie wykorzystać możliwości systemu. Dzięki tej książce: • Poznasz najlepsze praktyki pozwalające na obsłużenie wymagających aplikacji • Odkryjesz, dlaczego sprzęt komputerowy nadaje się (lub nie) dla wysoko wydajnych aplikacji bazodanowych • Zrozumiesz, na czym polegają kompromisy związane z szybkością i niezawodnością działania • Zoptymalizujesz system operacyjny, aby osiągnąć najlepszą wydajność bazy danych • Przeprowadzisz testy wydajności całego systemu, od sprzętu komputerowego po aplikację • Przeanalizujesz rzeczywiste przykłady, co pozwoli Ci poznać wpływ różnych ustawień parametrów serwera na wydajność • Będziesz skutecznie monitorować zdarzenia zachodzące na serwerze, zarówno w bazie danych, jak i poza nią • Znajdziesz najlepsze dodatki, rozszerzające podstawowe możliwości bazy danych PostgreSQL • Dowiesz się, jak przygotować replikację systemów za pomocą najnowszych funkcji wprowadzonych w PostgreSQL 9.0 Zoptymalizuj swój serwer PostgreSQL i unikaj problemów, które mogą zmniejszyć jego wydajność! Spis treĂci O autorze O recenzentach Wprowadzenie Rozdziaï 1. Wersje PostgreSQL WydajnoĂÊ we wczeĂniejszych wydaniach PostgreSQL Wybór odpowiedniej wersji Uaktualnienie do nowszej gïównej wersji PostgreSQL czy inna baza danych? NarzÚdzia PostgreSQL Moduïy contrib w PostgreSQL pgFoundry Dodatkowe oprogramowanie zwiÈzane z PostgreSQL Cykl ĝyciowy aplikacji PostgreSQL Optymalizacja wydajnoĂci w praktyce Podsumowanie Rozdziaï 2. SprzÚt dla bazy danych Zrównowaĝenie wydatków na zakup sprzÚtu Procesor PamiÚÊ Dyski twarde Kontrolery dysków Niezawodne kontrolery i konfiguracja dysków Bufor zapisu Wpïyw bufora bez wstrzymywania zapisu na wydajnoĂÊ Podsumowanie 13 15 17 21 22 23 23 26 27 27 30 30 31 32 34 35 35 36 37 37 43 48 49 52 53 Wysoko wydajny PostgreSQL 9.0 Rozdziaï 3. Testy wydajnoĂci sprzÚtu dla bazy danych Testy wydajnoĂci procesora i pamiÚci memtest86+ Testowanie pamiÚci za pomocÈ narzÚdzia STREAM Testy wydajnoĂci procesora Powody wolnego dziaïania procesora i pamiÚci Fizyczna wydajnoĂÊ dysku Swobodny dostÚp i liczba operacji wejĂcia-wyjĂcia na sekundÚ DostÚp sekwencyjny i ZCAV Liczba wykonywanych operacji zatwierdzania NarzÚdzia do testowania wydajnoĂci dysku hdtune dd bonnie++ sysbench Skomplikowane testy wydajnoĂci dysku twardego Przykïadowe wyniki testu wydajnoĂci dysku Oczekiwana wydajnoĂÊ dysku Podsumowanie Rozdziaï 4. Konfiguracja dysków Maksymalna wielkoĂÊ systemu plików Odzyskiwanie danych po awarii systemu plików Systemy plików z ksiÚgowaniem Systemy plików w Linuksie ext2 ext3 ext4 XFS Inne systemy plików w Linuksie Bariery zapisu Ogólne dostrajanie systemów plików w Linuksie Systemy plików Solaris i FreeBSD Solaris UFS FreeBSD UFS2 ZFS Systemy plików w Windows FAT32 NTFS Konfiguracja dysku dla PostgreSQL DowiÈzania symboliczne Tablespace Drzewo katalogów bazy danych Macierze dyskowe, RAID i konfiguracja dysków Podsumowanie 4 55 55 56 56 59 60 61 61 63 64 65 65 69 70 75 77 78 80 83 85 85 86 87 88 88 89 91 91 93 94 96 102 102 104 105 107 107 107 108 108 109 109 112 115 Rozdziaï 5. PamiÚÊ dla bufora bazy danych Jednostki pamiÚci w pliku konfiguracyjnym postgresql.conf ZwiÚkszenie parametrów pamiÚci wspóïdzielonej w systemie Unix w celu zdefiniowania wiÚkszego bufora Semafory jÈdra Oszacowanie wielkoĂci pamiÚci wspóïdzielonej PrzeglÈd bufora bazy danych Instalacja pg_buffercache w bazie danych Konfiguracja ukïadu dysków Utworzenie nowego bloku w bazie danych Zapis zmodyfikowanych bloków na dysku Naprawa bazy danych po awarii a wielkoĂÊ bufora Podstawy przetwarzania punktów kontrolnych Dziennik zapisu z wyprzedzeniem a proces naprawy po awarii Tworzenie punktów kontrolnych Cykl ĝycia bloku bazy danych Bufor bazy danych kontra bufor systemu operacyjnego Podwójnie buforowane dane PrzeciÈĝenie punktu kontrolnego PoczÈtkowe wskazówki dotyczÈce wielkoĂci Analiza zawartoĂci bufora Zapytania pozwalajÈce na przeglÈd zawartoĂci bufora PrzeglÈd wielkoĂci bufora i jej dostosowanie Podsumowanie Rozdziaï 6. Optymalizacja konfiguracji serwera Interakcja z uĝywanÈ konfiguracjÈ Ustawienia domyĂlne i sposoby ich zerowania Dozwolony kontekst do przeprowadzania zmian Ponowne wczytywanie pliku konfiguracyjnego Ustawienia na poziomie serwera PoïÈczenia z bazÈ danych PamiÚÊ wspóïdzielona Rejestrowanie zdarzeñ Polecenie VACUUM i dane statystyczne Punkty kontrolne Ustawienia mechanizmu WAL Replikacja WAL i PITR Ustawienia na poziomie klienta Optymalizacje niezalecane Optymalizacja ustawieñ nowego serwera Wskazówki dotyczÈce serwerów dedykowanych Wskazówki dotyczÈce serwerów wspóïdzielonych pgtune Podsumowanie Spis treĞci 117 118 119 120 121 122 123 124 126 127 128 128 128 129 131 132 133 134 135 136 137 141 141 143 144 144 144 146 147 147 149 150 152 155 156 159 159 162 164 164 165 166 166 5 Wysoko wydajny PostgreSQL 9.0 Rozdziaï 7. Rutynowa konserwacja WidocznoĂÊ transakcji wraz z kontrolÈ wspóïbieĝnoĂci WewnÚtrzne mechanizmy okreĂlajÈce widocznoĂÊ Uaktualnienia Konflikty podczas blokowania rekordów UsuniÚcie Zalety mechanizmu MVCC Wady mechanizmu MVCC Wyzerowanie identyfikatora transakcji Vacuum Implementacja procesu vacuum Operacja czyszczenia na podstawie kosztów Demon autovacuum Powszechnie spotykane problemy z vacuum i autovacuum Automatyczna analiza Nadmuchane indeksy Pomiar nadmuchania indeksu Szczegóïowe monitorowanie stron indeksu i danych Monitorowanie dzienników zdarzeñ zapytañ Podstawowa konfiguracja rejestracji zdarzeñ w PostgreSQL Rejestrowanie trudnych zapytañ Analiza pliku dziennika zdarzeñ Podsumowanie Rozdziaï 8. Sprawdzanie wydajnoĂci bazy danych DomyĂlne testy pgbench Definicja tabeli Wykrywanie skali wielkoĂci bazy danych Definicja skryptu zapytania Konfiguracja serwera bazy danych pod kÈtem pgbench RÚczne uruchamianie pgbench Wyniki graficzne generowane za pomocÈ pgbench-tools Konfiguracja pgbench-tools Przykïadowe wyniki testów pgbench Test przeprowadzajÈcy jedynie zapytania SELECT Test transakcji TPC-B-like Analiza opóěnienia Powody otrzymywania bïÚdnych wyników i róĝnic Programistyczne wersje PostgreSQL WÈtki worker i ograniczenia programu pgbench Wïasne testy pgbench Test szybkoĂci wstawiania danych Testy wydajnoĂci Transaction Processing Performance Council Podsumowanie 6 167 167 168 169 171 173 174 174 174 176 177 179 181 185 190 191 191 193 194 194 199 200 207 209 209 210 210 211 213 214 216 216 217 217 218 219 222 223 224 225 225 226 228 Spis treĞci Rozdziaï 9. Indeksowanie bazy danych PrzeglÈd sposobów indeksowania Dane statystyczne sïuĝÈce do pomiaru wielkoĂci zapytania na dysku i bloku indeksu Uruchomienie przykïadu Konfiguracja przykïadowych danych Proste wyszukiwania za pomocÈ indeksów Peïne skanowanie tabeli Tworzenie indeksu Wyszukiwanie za pomocÈ nieefektywnego indeksu ’Èczenie indeksów PrzejĂcie ze skanowania indeksowanego na sekwencyjne Klastry kontra indeksy Polecenie Explain oraz liczniki bufora Tworzenie indeksu i jego obsïuga Zapewnienie unikalnoĂci indeksów Wspóïbieĝne tworzenie indeksu Klastrowanie indeksu Ponowne indeksowanie Rodzaje indeksów B-tree Hash GIN GiST Zaawansowane sposoby korzystania z indeksów Indeksy wielokolumnowe Indeksy dla operacji sortowania Indeksy czÚĂciowe Indeksy bazujÈce na wyraĝeniu Indeksowanie na potrzeby wyszukiwania peïnego tekstu Podsumowanie Rozdziaï 10. Optymalizacja zapytañ Przykïadowe zbiory danych Pagila Dell Store 2 Podstawy polecenia EXPLAIN ObciÈĝenie zwiÈzane z pomiarem Zachowanie przy zimnym i rozgrzanym buforze Struktura wÚzïów planu zapytania Podstawy obliczania kosztu NarzÚdzia analizy danych polecenia EXPLAIN Graficzne przedstawienie danych EXPLAIN Rozbudowane dane wyjĂciowe Dane wyjĂciowe EXPLAIN w postaci czytelnej dla komputera NarzÚdzia sïuĝÈce do analizy planu 229 230 230 231 232 233 234 235 235 237 238 239 241 241 242 243 243 244 245 245 246 246 247 247 248 248 249 249 250 250 253 253 254 254 256 256 257 259 260 262 262 263 263 264 7 Wysoko wydajny PostgreSQL 9.0 ’Èczenie zbiorów rekordów Identyfikator krotki Skanowanie sekwencyjne Skanowanie indeksu Mapa bitowa i skanowanie indeksu Przetwarzanie wÚzïów WÚzeï Sort WÚzeï Limit WÚzeï Aggregate WÚzeï HashAggregate WÚzeï Unique WÚzeï Result WÚzeï Append WÚzeï Group WÚzïy Subquery Scan i Subplan Operacje ustawiania Materializacja Skanowanie CTE ZïÈczenia PÚtle zagnieĝdĝone ZïÈczenie Merge Join ZïÈczenia Hash Join Dane statystyczne PrzeglÈdanie i szacowanie za pomocÈ danych statystycznych Cele danych statystycznych Obszary trudne do oszacowania Inne parametry planowania zapytania effective_cache_size work_mem constraint_exclusion cursor_tuple_fraction Wykonywanie innych typów zapytañ Poprawianie zapytañ Optymalizacja dla w peïni buforowanych zbiorów danych Poszukiwanie odpowiednika zapytania WyïÈczanie funkcji optymalizatora RozwiÈzywanie bïÚdów optymalizatora Unikanie planu restrukturyzacji za pomocÈ OFFSET ZewnÚtrzne ěródïa problemów Ograniczenia SQL Numerowanie rekordów w SQL Uĝywanie funkcji Window do numerowania Uĝywanie funkcji Window do kumulowania wyniku Podsumowanie 8 265 265 266 266 267 268 268 270 271 272 273 274 275 276 277 278 279 280 281 281 283 285 290 290 293 295 295 295 297 298 298 298 299 300 300 301 305 306 309 309 309 311 311 313 Rozdziaï 11. Dane statystyczne i dziaïanie bazy danych Widoki danych statystycznych Widoki kumulacyjne i ĝywe Dane statystyczne tabel Operacje wejĂcia-wyjĂcia tabel Dane statystyczne indeksu Operacje wejĂcia-wyjĂcia indeksu Dane statystyczne dotyczÈce caïej bazy danych PoïÈczenia i aktywnoĂÊ Blokady Transakcje wirtualne Dekodowanie informacji o blokadzie Oczekiwanie na blokadÚ transakcji Oczekiwanie na blokadÚ tabeli Rejestrowanie informacji o blokadach Wykorzystanie dysku Bufor, zapis w tle oraz aktywnoĂÊ tworzenia punktu kontrolnego Zapis migawek pg_stat_bgwriter Optymalizacja z uĝyciem danych statystycznych dotyczÈcych zapisu w tle Podsumowanie Rozdziaï 12. Monitorowanie i trendy NarzÚdzia monitorujÈce w systemie Unix Przykïadowa konfiguracja vmstat iostat top sysstat i sar NarzÚdzia monitorujÈce dla Windows Menedĝer zadañ Monitor systemu Windows Oprogramowanie trendów Rodzaje monitorowania i oprogramowanie trendów Nagios Cacti Munin Inne pakiety trendów Podsumowanie Rozdziaï 13. Pula poïÈczeñ i buforowanie Pula poïÈczeñ Liczniki puli poïÈczeñ pgpool-II pgBouncer Spis treĞci 315 315 317 318 320 322 323 324 324 325 326 327 330 331 332 333 335 337 339 341 343 343 344 344 347 355 357 360 360 360 362 363 364 366 366 367 369 371 371 372 373 374 9 Wysoko wydajny PostgreSQL 9.0 Buforowanie bazy danych memcached pgmemcache Podsumowanie Rozdziaï 14. Skalowanie za pomocÈ replikacji Hot Standby Terminologia Konfiguracja przekazywania danych mechanizmu WAL Protokóï Streaming Replication Optymalizacja funkcji Hot Standby Menedĝery kolejki replikacji Slony Londiste Skalowanie odczytu za pomocÈ oprogramowania replikacji bazujÈcego na kolejce Wymagania aplikacji specjalnych Bucardo pgpool-II Inne interesujÈce projekty replikacji Podsumowanie Rozdziaï 15. Partycjonowanie danych Partycjonowanie o zasiÚgu tabeli OkreĂlenie pola klucza uĝywanego do partycjonowania WielkoĂci partycji Tworzenie partycji Przekierowywanie poleceñ INSERT do partycji Plany zapytañ dla pustej partycji Zmiana daty i uaktualnianie wyzwalacza Migracja partycjonowanej uĝywanej tabeli Zapytania partycjonowane Tworzenie nowych partycji Zalety partycjonowania BïÚdy czÚsto popeïniane podczas partycjonowania Partycjonowanie poziome za pomocÈ PL/Proxy Generowanie wartoĂci hash Skalowanie za pomocÈ PL/Proxy Skalowanie za pomocÈ GridSQL Podsumowanie Rozdziaï 16. Unikanie najczÚĂciej spotykanych problemów Operacja bulk-loading Metody wczytywania danych Optymalizacja operacji bulk-loading PominiÚcie optymalizacji mechanizmu WAL 10 376 376 377 378 381 381 382 383 384 384 386 387 387 388 388 388 389 389 391 393 393 394 395 396 397 399 400 401 403 405 406 407 408 408 410 411 412 415 415 416 417 418 Ponowne utworzenie indeksów i dodanie ograniczeñ Przywracanie równolegïe Czyszczenie po operacji wczytania danych NajczÚstsze problemy zwiÈzane z wydajnoĂciÈ Zliczanie rekordów NiewyjaĂnione operacje zapisu Wolne wykonywanie funkcji i poleceñ skïadowanych Testy wydajnoĂci PL/pgSQL Ogromne przeciÈĝenie klucza zewnÚtrznego Uĝycie pamiÚci przez wyzwalacz Ogromne przeciÈĝenie mechanizmu zbierajÈcego dane statystyczne Zmaterializowane widoki Profilowanie bazy danych gprof OProfile Visual Studio DTrace Problemy wydajnoĂci w poszczególnych wersjach Agresywne uaktualnienia PostgreSQL 8.1 8.2 8.3 8.4 9.0 Podsumowanie Skorowidz Spis treĞci 419 419 420 421 421 422 423 424 424 425 426 427 427 427 428 428 428 429 430 431 432 432 434 436 441 443 11 6 Optymalizacja konfiguracji serwera Podstawowe ustawienia optymalizacyjne PostgreSQL znajdujÈ siÚ w zwykïym pliku teksto- wym o nazwie postgresql.conf umieszczonym w strukturze katalogów bazy danych. Struktura katalogów w systemach z rodziny Unix czÚsto okreĂlana jest przy uĝyciu zmiennej Ărodowi- skowej $PGDATA, a wiÚc Ăcieĝka dostÚpu do pliku konfiguracyjnego na tych platformach ma postaÊ $PGDATA/postgresql.conf. W rozdziale przedstawiono omówienie parametrów konfiguracyjnych; praktycznie jest to powielony ogólny format stosowany w dokumentacji dostÚpnej na stronie http://www.postgresql. org/docs/current/static/runtime-config.html. Jednak tutaj, zamiast opisywania znaczenia kaĝ- dego parametru, nacisk zostaï poïoĝony na udzielenie wskazówek dotyczÈcych ustawiania naj- waĝniejszych wartoĂci z perspektywy uĝytkownika zainteresowanego optymalizacjÈ wydajnoĂci. Rozdziaï naleĝy wiÚc potraktowaÊ jako uzupeïnienie materiaïu umieszczonego w oficjalnej dokumentacji, a nie jej zamiennik. Inne ěródïo informacji na omawiany tutaj temat znajduje siÚ w artykule „Tuning Your PostgreSQL Server” dostÚpnym na stronie http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server, w którym pewne informacje sÈ identyczne z przedstawionymi w rozdziale. Struktura artykuïu wiki powoduje, ĝe jest on aktualizowany na bieĝÈco i moĝe zawieraÊ pewne informacje doty- czÈce przyszïych wersji bazy danych PostgreSQL, który byïy niedostÚpne w trakcie przygoto- wywania tego rozdziaïu. Wysoko wydajny PostgreSQL 9.0 Interakcja z uĝywanÈ konfiguracjÈ Istnieje wiele sposobów modyfikacji parametrów bazy danych, nie sÈ to jedynie edycja pliku konfiguracyjnego i ponowne uruchomienie serwera. Poznanie i zrozumienie tych sposobów ma znaczenie krytyczne zarówno dla skrócenia czasu niedostÚpnoĂci serwera jedynie z powodu rutynowych zmian konfiguracyjnych, jak i zagwarantowania, ĝe czytelnik przeprowadza mody- fikacjÚ wïaĂciwych parametrów, gdy zmiana ma zostaÊ wprowadzona natychmiast. Ustawienia domyĂlne i sposoby ich zerowania Baza danych ma dwa rodzaje ustawieñ, które moĝna okreĂliÊ jako „ustawienia domyĂlne” w zaleĝnoĂci od kontekstu. Jednym z nich sÈ ustawienia domyĂlne powodujÈce ustalenie przez serwer pewnych wartoĂci, jeĝeli uĝytkownik nie zmieni ĝadnych opcji — sÈ to ustawienia, z któ- rymi serwer jest uruchamiany jeszcze przed odczytaniem pliku konfiguracyjnego postgresql.conf. Od wydania PostgreSQL 8.4 wartoĂci te moĝna sprawdziÊ w kolumnie boot_val w widoku pg_settings. WiÚcej informacji na ten temat znajduje siÚ na stronie http://www.postgresql.org/ docs/current/static/view-pg-settings.html. Po uruchomieniu serwera i wprowadzeniu zmian w parametrach, które równieĝ posiadajÈ swoje wartoĂci domyĂlne, powrót do wspomnianych wartoĂci domyĂlnych nastÚpuje w wyniku wydania polecenia RESET. Dziaïanie tego polecenia zostaïo omówione na stronie http://www. postgresql.org/docs/current/static/sql-reset.html. Te wartoĂci domyĂlne moĝna przejrzeÊ w kolumnie reset_val znajdujÈcej siÚ w widoku pg_settings. Dozwolony kontekst do przeprowadzania zmian Kaĝde ustawienie konfiguracyjne ma powiÈzany z nim kontekst, w ramach którego moĝe zostaÊ zmienione. Najlepszym sposobem okreĂlenia dozwolonego kontekstu do przeprowadzania zmian jest bezpoĂrednie zapytanie bazy danych. W przedstawionym poniĝej przykïadzie pokazano po jednym wpisie dla kaĝdego rodzaju kontekstu (w rzeczywistoĂci po wydaniu poniĝszego polece- nia dane wyjĂciowe bÚdÈ zawieraïy wszystkie parametry serwera): postgres=# select name,context from pg_settings; name | context ----------------------------+------------ archive_command | sighup archive_mode | postmaster block_size | internal log_connections | backend log_min_duration_statement | superuser search_path | user 144 Rozdziaá 6. • Optymalizacja konfiguracji serwera W oficjalnym podrÚczniku uĝytkownika pole kontekstu (context) nie jest zbyt dobrze udoku- mentowane. Poniĝej przedstawiono wiÚc znaczenie róĝnych stosowanych ustawieñ w kolejnoĂci od najtrudniejszego do najïatwiejszego do zmiany. Q internal. Ustawienia te w duĝej mierze sÈ wewnÚtrznymi ustawieniami bazy danych okreĂlonymi w trakcie jej kompilacji. WyĂwietlane sÈ w celu dostarczenia uĝytkownikowi informacji, ale nie mogÈ zostaÊ zmienione bez ponownej kompilacji serwera. Q postmaster. Ustawienia sÈ uaktualniane jedynie podczas peïnego uruchamiania serwera. Do tej kategorii zaliczajÈ siÚ wszystkie opcje dotyczÈce pamiÚci wspóïdzielonej. Q sighup. Wysïanie sygnaïu HUP do serwera spowoduje ponowne odczytanie pliku konfiguracyjnego postgresql.conf, a wszelkie zmiany wprowadzone do tego parametru bÚdÈ natychmiast zastosowane. WiÚcej informacji na ten temat znajduje siÚ w kolejnym punkcie zatytuïowanym „Ponowne wczytywanie pliku konfiguracyjnego”. Q backend. Ustawienia oznaczone za pomocÈ tej wïaĂciwoĂci sÈ podobne do ustawieñ sighup, z wyjÈtkiem faktu, ĝe wprowadzone zmiany nie majÈ wpïywu na juĝ istniejÈce sesje bazy danych. Tylko sesje uruchomione po wprowadzeniu zmian bÚdÈ stosowaïy nowe ustawienia. Istnieje niewiele parametrów oznaczonych omawianÈ wïaĂciwoĂciÈ, wiÚkszoĂÊ z nich wpïywa jedynie na dziaïania podejmowane w trakcie uruchamiania i zamykania sesji. Ostatnia opcja tej grupy (log_connections) nie moĝe dziaïaÊ wstecz, to znaczy nie ma moĝliwoĂci rozpoczÚcia rejestrowania zdarzeñ juĝ nawiÈzanego poïÈczenia. Zdarzenia bÚdÈ rejestrowane tylko dla nowych poïÈczeñ ustanowionych po wïÈczeniu opcji log_connections. Q superuser. Ustawienia tej grupy mogÈ byÊ modyfikowane w dowolnym momencie przez kaĝdego superuĝytkownika bazy danych (z reguïy uĝytkownika, który utworzyï bazÚ danych, czyli bardzo czÚsto „postgres”). Aktywowanie zmiany nie wymaga peïnego, ponownego wczytania pliku konfiguracyjnego. WiÚkszoĂÊ ustawieñ tej grupy jest powiÈzana z rejestrowaniem w plikach dzienników zdarzeñ róĝnych aspektów poleceñ wykonywanych przez serwer. Q user. Te parametry poszczególni uĝytkownicy mogÈ dostosowaÊ w dowolnym momencie swojej sesji. Wprowadzone zmiany dotyczÈ jedynie danej sesji. WiÚkszoĂÊ parametrów zmienia sposób wykonywania zapytañ, co pozwala na przeprowadzenie optymalizacji w ramach sesji. Na podstawie powyĝszej sesji moĝna siÚ przekonaÊ, ĝe udzielenie odpowiedzi na — wyda- waïoby siÚ — proste pytanie, jaka jest bieĝÈca wartoĂÊ work_mem, moĝe byÊ bardzo trudne, w zaleĝnoĂci od wybranego kontekstu. PoczÈtkowo wartoĂÊ ta moĝe byÊ wartoĂciÈ okreĂlonÈ w pliku konfiguracyjnym postgresql.conf, nastÚpnie moĝe byÊ zmieniona w wyniku ponownego wczytania konfiguracji, a na koñcu znów zmieniona przez uĝytkownika przed wykonaniem zapytania. 145 Wysoko wydajny PostgreSQL 9.0 Ponowne wczytywanie pliku konfiguracyjnego IstniejÈ trzy sposoby pozwalajÈce bazie danych na ponowne wczytanie konfiguracji w celu uaktualnienia wartoĂci zaliczajÈcych siÚ do grupy sighup. Jeĝeli czytelnik jest poïÈczony z bazÈ danych jako superuĝytkownik, moĝna uĝyÊ funkcji pg_reload_conf() w nastÚpujÈcy sposób: postgres=# SELECT pg_reload_conf(); pg_reload_conf ---------------- t Sygnaï SIGHUP moĝna wysïaÊ równieĝ rÚcznie za pomocÈ polecenia kill systemu Unix: $ ps -eaf | grep postgres -D postgres 11185 1 0 22:21 pts/0 00:00:00 /home/postgres/inst/bin/ ´postgres -D /home/postgres/data/ $ kill -HUP 11185 Wreszcie, sygnaï SIGHUP moĝna wysyïaÊ do serwera przy uĝyciu narzÚdzia pg_ctl: $ pg_ctl reload server signaled Niezaleĝnie od uĝytego sposobu, po wysïaniu sygnaïu SIGHUP w dziennikach zdarzeñ bazy danych znajdzie siÚ nastÚpujÈcy komunikat: LOG: received SIGHUP, reloading configuration files NastÚpnie, za pomocÈ poleceñ, takich jak SHOW, lub po przejrzeniu widoku pg_settings moĝna potwierdziÊ, ĝe zmiany zostaïy wprowadzone zgodnie z oczekiwaniami. Umieszczanie ustawieñ w komentarzach Co siÚ stanie w sytuacji, gdy pewien parametr zostaï rÚcznie ustawiony, ale musi byÊ wyïÈ- czony w dziaïajÈcym serwerze? Konkretna odpowiedě zaleĝy od wersji uĝywanego serwera. Przyjmijmy zaïoĝenie, ĝe plik postgresql.conf zostaï uruchomiony wraz z ustawionym nastÚ- pujÈcym parametrem: checkpoint_segments = 30 W celu wyïÈczenia tego parametru trzeba wyedytowaÊ plik konfiguracyjny, a parametr umieĂciÊ w komentarzu: #checkpoint_segments = 30 NastÚpnie konieczne jest nakazanie serwerowi, aby ponownie wczytaï plik konfiguracyjny: $ pg_ctl reload 146 Rozdziaá 6. • Optymalizacja konfiguracji serwera Parametr checkpoint_segments jest ustawieniem kontekstu sighup. Od PostgreSQL 8.3 powyĝ- sza zmiana spowoduje powrót do wartoĂci domyĂlnej serwera (boot_val). W wersji 9.0 w dzien- niku zdarzeñ zostanie ponadto umieszczony nastÚpujÈcy komunikat: LOG: received SIGHUP, reloading configuration files LOG: parameter checkpoint_segments removed from configuration file, reset ´to default Uĝycie wartoĂci domyĂlnej moĝna potwierdziÊ za pomocÈ polecenia SHOW: $ psql -x -c show checkpoint_segments -[ RECORD 1 ]-------+-- checkpoint_segments | 3 Jeĝeli uĝywany serwer to PostgreSQL w wersji 8.2 lub wczeĂniejszej, wprowadzona zmiana nie spowoduje ĝadnego efektu, to znaczy parametr checkpoint_segments nadal bÚdzie miaï war- toĂÊ 30. Dopiero po peïnym, ponownym uruchomieniu serwera nastÈpi przywrócenie wartoĂci domyĂlnej, czyli 3. Poniewaĝ takie zachowanie jest skomplikowane i uzaleĝnione od uĝywanej wersji serwera, doĂwiadczeni administratorzy PostgreSQL zwykle dwukrotnie sprawdzajÈ parametry, które majÈ zamiar zmodyfikowaÊ. Do sprawdzenia uĝywajÈ polecenia SHOW lub widoku pg_settings i upewniajÈ siÚ w ten sposób, ĝe parametr ma oczekiwanÈ wartoĂÊ. W tym miejscu innÈ, skomplikowanÈ kwestiÈ jest moĝliwoĂÊ doïÈczenia dodatkowych plików konfiguracyjnych z poziomu gïównego pliku postgresql.conf. W przypadku takiego rozwiÈzania skutek jest taki sam jak wstawienie zawartoĂci okreĂlonego pliku we wskazanym miejscu. W widoku pg_settings moĝna poznaÊ nazwÚ pliku, z którego pochodzi dany parametr, wraz z numerem wiersza zawierajÈcego aktywnÈ wersjÚ. Warto teĝ pamiÚtaÊ, ĝe w przypadku wielo- krotnego ustawiania tego samego parametru pod uwagÚ zawsze brane jest tylko ostatnie jego wystÈpienie. Ustawienia na poziomie serwera Wprawdzie w pewnych przypadkach parametry mogÈ byÊ modyfikowane w innych kontekstach, przedstawione w tym podrozdziale sÈ tymi, które moĝna zmodyfikowaÊ w pliku postgresql.conf odczytywanym przed uruchomieniem serwera. PoïÈczenia z bazÈ danych Istnieje wiele parametrów konfiguracyjnych okreĂlajÈcych sposoby zdalnego i lokalnego nawiÈ- zywania poïÈczenia z bazÈ danych. Peïna lista wspomnianych parametrów zostaïa przedstawiona na stronie http://www.postgresql.org/docs/current/static/runtime-config-connection.html. 147 Wysoko wydajny PostgreSQL 9.0 listen_addresses W kaĝdej instalacji wymagajÈcej obsïugi poïÈczeñ pochodzÈcych z systemów zdalnych konieczna jest modyfikacja parametru listen_addresses pozwalajÈcego na obsïugÚ takich poïÈczeñ. DomyĂl- nie dozwolone sÈ jedynie poïÈczenia lokalne pochodzÈce od uĝytkownika zalogowanego do tego samego systemu, w którym znajduje siÚ baza danych. Powszechnie stosowanym podejĂciem jest akceptacja poïÈczeñ przychodzÈcych z kaĝdego miejsca. W tym celu w gïównym pliku konfiguracyjnym trzeba umieĂciÊ poniĝszy wiersz: listen_addresses = * Aby kontrolowaÊ, kto moĝe nawiÈzaÊ poïÈczenie, trzeba przeprowadziÊ konfiguracjÚ pliku pg_hba.conf, co zostaïo omówione na stronie http://www.postgresql.org/docs/current/static/ auth-pg-hba-conf.html. Stosowanie takiego podejĂcia wiÈĝe siÚ z pewnym problemem doty- czÈcym wydajnoĂci. Filtrowanie poïÈczeñ za pomocÈ dokïadniej skonfigurowanego parametru listen_addresses moĝe byÊ znacznie bardziej efektywne niĝ pozwolenie na nawiÈzanie poïÈ- czenia wszystkim klientom. Zezwolenie klientowi na poïÈczenie, a nastÚpnie jego odrzucenie w wyniku wpisu znajdujÈcego siÚ w pliku konfiguracyjnym pg_hba.conf powoduje niepotrzebne zuĝycie pewnych zasobów serwera i naraĝa system na niebezpieczeñstwo przeprowadzenia w ten sposób ataku typu odmowa usïugi — DoS (ang. Denial Of Service) — przez zïoĂliwego uĝytkownika. W praktyce tylko niewielka liczba serwerów PostgreSQL pozwala na bezpoĂrednie przyjmo- wanie zapytañ pochodzÈcych z internetu. Normalnie sÈ one filtrowane przez port domyĂlny PostgreSQL (5432) na poziomie zapory sieciowej, co stanowi najefektywniejsze podejĂcie i jest czÚsto stosowanÈ implementacjÈ mechanizmu wspóïdzielonej ochrony takĝe innych aplikacji. W systemie podïÈczonym do internetu, takim jak na przykïad klastry zawierajÈce bazy danych dla usïug „w chmurach”, naleĝy upewniÊ siÚ o stosowaniu wszystkich trzech warstw ochrony. Na poziomie zapory sieciowej warto okreĂliÊ, kto moĝe nawiÈzaÊ poïÈczenie z serwerem, dodatkowo zmniejszyÊ listÚ nasïuchiwanych adresów za pomocÈ opcji listen_addresses, a takĝe ograniczyÊ uĝytkownikom dostÚp do bazy danych za pomocÈ pliku konfiguracyjnego pg_hba.conf. max_connections Jednym z parametrów, dla którego czytelnik najczÚĂciej bÚdzie ustawiaï wartoĂÊ (z reguïy 100) w pliku konfiguracyjnym postgresql.conf wygenerowanym przez initdb, jest max_connections. Poniewaĝ, jak wspomniano w poprzednim rozdziale, kaĝde poïÈczenie wykorzystuje niewielkÈ iloĂÊ pamiÚci wspóïdzielonej, w systemach domyĂlnie uĝywajÈcych niewielkiej iloĂci pamiÚci wspóïdzielonej nawiÈzanie wiÚkszej iloĂci poïÈczeñ nie zostanie nawet dozwolone. W zwiÈzku z powyĝszym, podobnie jak w przypadku parametru shared_buffers, po utworzeniu klastra bazy danych i okreĂleniu w domyĂlnym pliku konfiguracyjnym najwiÚkszej dopuszczalnej war- toĂci (do 100) przeprowadzane sÈ pewne badania. W praktyce iloĂÊ pamiÚci niewspóïdzielonej uĝywanej przez kaĝdego klienta podczas przeprowadzania operacji, takich jak sortowanie, jest wprawdzie znacznie wiÚksza, ale iloĂci wykorzystywanej pamiÚci wspóïdzielonej nie moĝna pominÈÊ. 148 Rozdziaá 6. • Optymalizacja konfiguracji serwera Waĝne jest, aby w tym parametrze nie ustawiaÊ wartoĂci wyĝszej niĝ konieczna. Istnieje wiele wad wynikajÈcych z ustawienia wyĝszej wartoĂci parametru max_connections. Jedna z nich to marnowanie pamiÚci wspóïdzielonej, ale to problem, którym naleĝy siÚ najmniej przejmowaÊ, poniewaĝ iloĂÊ pamiÚci wspóïdzielonej wykorzystywanej przez kaĝde poïÈczenie pozostaje niewielka. Jednak sÈ inne zasoby wykorzystywane przez klienta, na przykïad alokacja pamiÚci dla operacji sortowania (kontrolowana za pomocÈ parametru work_mem omówionego dalej, w tym rozdziale), która z reguïy obejmuje ogromny blok pamiÚci. Jeĝeli dozwolona bÚdzie obsïuga wiÚkszej liczby poïÈczeñ, to by bezpiecznie oszczÚdzaÊ pamiÚÊ, trzeba równieĝ zmniejszyÊ wartoĂci wspomnia- nych ustawieñ, tak aby zminimalizowaÊ niebezpieczeñstwo alokacji wiÚkszej iloĂci pamiÚci niĝ dostÚpna. Ze wzglÚdu na problemy zwiÈzane z alokacjÈ zasobów, serwery PostgreSQL w Windows mogÈ mieÊ bardzo ograniczonÈ liczbÚ obsïugiwanych poïÈczeñ. Bardzo czÚsto zdarza siÚ, ĝe zanim wyczerpana zosta- nie pamiÚÊ w obszarze Desktop Heap, obsïuĝonych bÚdzie jedynie okoïo 125 poïÈczeñ. WiÚcej informa- cji na temat tego problemu oraz potencjalne sposoby jego rozwiÈzania moĝna znaleěÊ na stronie http://wiki.postgresql.org/wiki/Running_ _Installing_PostgreSQL_On_Native_Windows. Wreszcie, nawiÈzywanie poïÈczeñ w bazie danych PostgreSQL naleĝy uwaĝaÊ za operacjÚ intensywnie wykorzystujÈcÈ zasoby. Celem bazy danych nie jest dziaïalnoĂÊ w charakterze komponentu nawiÈzujÈcego poïÈczenie z bazÈ danych, przeprowadzajÈcego uwierzytelnianie i dotarcie do punktu, w którym zapytanie bÚdzie wykonane jako stosunkowo niewielka operacja. Ogólnie rzecz ujmujÈc, obciÈĝenie zwiÈzane z nawiÈzaniem kilkuset poïÈczeñ staje siÚ wÈskim gardïem podczas dziaïania serwera. Dokïadna liczba poïÈczeñ, po przekroczeniu której stajÈ siÚ one obciÈĝeniem, zaleĝy od uĝywanego sprzÚtu i konfiguracji serwera. Jeĝeli czytelnik ma zamiar obsïugiwaÊ jednoczeĂnie tysiÈce zapytañ, nie moĝe zastosowaÊ podejĂcia, w którym kaĝdy klient bezpoĂrednio nawiÈzuje poïÈczenie z bazÈ danych. W takim przypadku najczÚĂciej stosowanym rozwiÈzaniem problemu skalowalnoĂci jest uĝycie oprogramowania obsïugujÈcego pulÚ poïÈczeñ miÚdzy aplikacjÈ i bazÈ danych. Temat ten zostaï omówiony w rozdziale 13. PamiÚÊ wspóïdzielona WïaĂciwe ustawienie wartoĂci parametrów zwiÈzanych z pamiÚciÈ wspóïdzielonÈ jest waĝne, poniewaĝ ich zmiana zawsze wymaga peïnego, ponownego uruchomienia serwera bazy danych — serwer nie ma moĝliwoĂci dynamicznej ponownej alokacji pamiÚci wspóïdzielonej. shared_buffers Parametr shared_buffers byï szczegóïowo omawiany w poprzednim rozdziale. 149 Wysoko wydajny PostgreSQL 9.0 Ustawienia Free Space Map (FSM) Przestrzeñ pozostaïa w wyniku operacji usuwania bÈdě modyfikacji danych jest umieszczana przez polecenie VACUUM w przestrzeni FSM (ang. Free Space Map). Nowe operacje alokacji wykorzystujÈ przestrzeñ pochodzÈcÈ z FSM, zamiast alokowaÊ nowÈ przestrzeñ na dysku. Od wydania PostgreSQL 8.4 przestrzeñ FSM jest przechowywana na dysku, a tym samym automatycznie skaluje swojÈ wielkoĂÊ. W PostgreSQL do wersji 8.3 przestrzeñ FSM byïa przechowywana w pamiÚci wspóïdzielonej, co wymagaïo dokïadnego monitorowania iloĂci tej pamiÚci i potencjalnie prowadziïo do zwiÚkszenia jej zuĝycia. Upewnienie siÚ, ĝe wartoĂci para- metrów max_fsm_pages i max_fsm_relations w pliku konfiguracyjnym sÈ wystarczajÈce, powinno byÊ czÚĂciÈ regularnych operacji konserwacyjnych w tych wersjach serwera PostgreSQL. Ope- racjÚ tÚ moĝna przeprowadziÊ rÚcznie bÈdě wydaÊ polecenie VACUUM VERBOSE przeprowadzajÈce pomiar bieĝÈcego uĝycia pamiÚci wspóïdzielonej w bardziej zautomatyzowany sposób. WiÚcej informacji na ten temat przedstawiono w rozdziaïach 5. i 7. Rejestrowanie zdarzeñ Ogólne ustawienia dotyczÈce rejestracji zdarzeñ sÈ waĝne, ale w pewnym sensie pozostajÈ poza zakresem tematycznym tej ksiÈĝki. Czytelnik moĝe byÊ zmuszony do ustawienia parametrów, takich jak log_destination, log_directory i log_filename, w sposób zgodny z systemem oraz wymaganiami administratora uĝywanego Ărodowiska. Parametry te majÈ domyĂlnie ustawione rozsÈdne wartoĂci pozwalajÈce na rozpoczÚcie pracy w wiÚkszoĂci systemów. W rozdziale 7. zostanie poruszony problem dostosowania omawianych parametrów w celu przeprowadzenia rejestracji zdarzeñ w plikach CSV, co moĝe byÊ uĝyteczne podczas pomiaru czasu wykonywania zapytañ. W wiÚkszoĂci systemów Unix bardzo czÚsto zdarza siÚ, ĝe opcje dotyczÈce rejestracji zdarzeñ bazy danych sÈ ustawiane w skryptach uruchamiajÈcych i zatrzymujÈcych serwer, a nie bezpo- Ărednio w pliku konfiguracyjnym postgresql.conf. Jeĝeli w celu rÚcznego uruchomienia ser- wera stosowane jest polecenie pg_ctl, czytelnik moĝe odkryÊ, ĝe rejestracja zdarzeñ nastÚpuje poprzez ich bezpoĂrednie wyĂwietlenie na ekranie. W takim przypadku, aby dowiedzieÊ siÚ, o co chodzi, trzeba spojrzeÊ do skryptu uruchamiajÈcego serwer w zwykïy sposób (najczÚĂciej /etc/init.d/postgresql). W wiÚkszoĂci przypadków do polecenia pg_ctl wystarczy dodaÊ opcjÚ -l nazwa_pliku, która powoduje przekierowanie danych wyjĂciowych do standardowego poïoĝenia. log_line_prefix WartoĂÊ domyĂlna opcji log_line_prefix jest pusta, a to zdecydowanie niepoĝÈdane. WartoĂÊ dobra na poczÈtek bÚdzie miaïa nastÚpujÈcÈ postaÊ: log_line_prefix= t: r: u@ d:[ p]: 150 Rozdziaá 6. • Optymalizacja konfiguracji serwera Powyĝszy parametr powoduje umieszczenie w kaĝdym wierszu dziennika zdarzeñ takich infor- macji jak: Q t: znacznik czasu; Q u: nazwa uĝytkownika bazy danych; Q r: nazwa zdalnego komputera, z którego nawiÈzano poïÈczenie; Q d: nazwa bazy danych, z którÈ nawiÈzano poïÈczenie; Q p: identyfikator procesu poïÈczenia. Na poczÈtku moĝe nie byÊ takie oczywiste, po co zostaïy zastosowane powyĝsze wartoĂci domyĂlne, a zwïaszcza identyfikator procesu. Jednak po próbie rozwiÈzania kilku problemów zwiÈzanych z wydajnoĂciÈ koniecznoĂÊ zapisania w pliku dziennika zdarzeñ wymienionych informacji stanie siÚ bardziej oczywista i czytelnik bÚdzie zadowolony z faktu posiadania tych informacji. Inne podejĂcie warte rozwaĝenia to ustawienie wartoĂci parametru log_line_prefix w taki sposób, aby dzienniki zdarzeñ byïy zgodne z programem pgFouine, omówionym w rozdziale 7. Jest to rozsÈdny, przeznaczony do ogólnego rejestrowania zdarzeñ prefiks, a wiele witryn i tak ostatecznie stosuje pewne mechanizmy analizy zapytañ. log_statement Oto opcje do zastosowania w tym parametrze: Q none. Nie sÈ rejestrowane ĝadne informacje na poziomie poleceñ. Q ddl. Rejestrowane sÈ jedynie polecenia DDL (ang. Data Definition Language), na przykïad CREATE lub DROP. Takie ustawienie moĝna zastosowaÊ nawet w systemie produkcyjnym i jest przydatne podczas wychwytywania najwaĝniejszych zmian przypadkowo lub celowo wprowadzonych przez administratorów. Q mod. Rejestrowane sÈ wszystkie polecenia modyfikujÈce wartoĂÊ, czyli praktycznie wszystkie, poza prostymi poleceniami SELECT. Jeĝeli obciÈĝenie w serwerze to przede wszystkim polecenia SELECT przeprowadzajÈce niewielkÈ iloĂÊ zmian w danych, praktycznym rozwiÈzaniem moĝe byÊ pozostawienie tej opcji wïÈczonej przez caïy czas. Q all. Rejestrowane sÈ wszystkie polecenia. Ogólnie rzecz ujmujÈc, pozostawienie tej opcji wïÈczonej w serwerze produkcyjnym jest niepraktyczne, ze wzglÚdu na obciÈĝenie powodowane przez operacjÚ rejestrowania zdarzeñ. Gdy jednak serwer jest na tyle potÚĝny, aby poradziÊ sobie z takim obciÈĝeniem, pozostawienie tej opcji wïÈczonej przez caïy czas moĝe okazaÊ siÚ pomocne. Rejestrowanie poleceñ to uĝyteczna technika pozwalajÈca na wyszukiwanie problemów zwiÈ- zanych z wydajnoĂciÈ. Analiza informacji zebranych po ustawieniu parametru log_statement i powiÈzanych ěródeï informacji szczegóïowych na poziomie poleceñ moĝe ukazaÊ prawdziwÈ przyczynÚ wielu rodzajów problemów wydajnoĂci. Zebrane w ten sposób informacje moĝna poïÈczyÊ z odpowiednimi narzÚdziami analizy, które omówiono w rozdziale 7. 151 Wysoko wydajny PostgreSQL 9.0 log_min_duration_statement Gdy znamy juĝ iloĂÊ czasu wymaganÈ do wykonania typowego zapytania, uĝycie parametru log_min_duration_statement pozwoli na rejestrowanie tylko tych poleceñ, których wykonanie przekroczy ustalony czas. WartoĂÊ jest podawana w milisekundach, tak wiÚc parametr moĝna ustawiÊ w nastÚpujÈcy sposób: log_min_duration_statement=1000 Powyĝsze polecenie powoduje rejestracjÚ w dzienniku zdarzeñ tylko poleceñ wykonywanych dïuĝej niĝ sekundÚ. Parametr moĝe byÊ uĝyteczny do wyszukiwania ěródeï „odstajÈcych” pole- ceñ, których wykonanie trwa znacznie dïuĝej niĝ pozostaïych. Jeĝeli uĝywany jest PostgreSQL w wersji 8.4 lub nowszej, zamiast tej funkcji preferowanym rozwiÈzaniem moĝe byÊ moduï auto_explain. WiÚcej informacji na temat moduïu moĝna zna- leěÊ na stronie http://www.postgresql.org/docs/8.4/static/auto-explain.html. Moduï umoĝliwia sprawdzenie operacji wykonywanych przez powolne zapytania po przejrzeniu powiÈzanych z nimi planów EXPLAIN. Polecenie VACUUM i dane statystyczne Baza danych PostgreSQL wymaga dwóch podstawowych form regularnej obsïugi podczas doda- wania, uaktualniania i usuwania danych. Polecenie VACUUM powoduje usuniÚcie Ămieci po starych transakcjach, ïÈcznie z usuniÚciem informacji, które nie sÈ dïuĝej widoczne, oraz zwrócenie zwolnionego miejsca, tak aby mogïo byÊ ponownie wykorzystane. Im wiÚcej w bazie danych wykonywanych poleceñ UPDATE i DELETE, tym czÚĂciej trzeba przeprowadzaÊ operacjÚ usuwania Ămieci. Nawet w statycznych tabelach, których zawartoĂÊ nigdy nie ulega zmianie, od czasu do czasu trzeba wykonaÊ operacjÚ usuwa- nia Ămieci. Polecenie ANALYZE przeglÈda tabele w bazie danych i zbiera na ich temat dane statystyczne — informacje, takie jak liczba posiadanych rekordów bÈdě liczba odmiennych wartoĂci w tabelach. Wiele aspektów planowania zapytania zaleĝy od prawidïowego zebrania tych danych. WiÚcej informacji na temat polecenia VACUUM znajduje siÚ w rozdziale 7., natomiast temat stoso- wania danych statystycznych poruszono w rozdziale 10. Demon autovacuum Poniewaĝ zadanie usuwania Ămieci ma w dïuĝszej perspektywie znaczenie krytyczne dla bazy danych, od wydania PostgreSQL 8.1 w serwerze umieszczono demon autovacuum, który dziaïa w tle i zajmuje siÚ wspomnianym zadaniem. Aktywacja demona nastÚpuje po wykonaniu w bazie danych liczby zmian przekraczajÈcej wartoĂÊ obliczonÈ na podstawie wielkoĂci tabeli. 152 Rozdziaá 6. • Optymalizacja konfiguracji serwera Parametr dla demona autovacuum jest wïÈczony domyĂlnie w PostgreSQL 8.3, a wartoĂÊ domyĂlna jest ustalona w sposób wystarczajÈcy do dziaïania mniejszej bazy danych i wymaga jedynie niewielkiej rÚcznej modyfikacji. Ogólnie rzecz biorÈc, trzeba uwaĝaÊ, aby iloĂÊ danych w obsza- rze FSM nie przekraczaïa wartoĂci ustalonej przez parametr max_fsm_pages. Od PostgreSQL 8.4 opisane wymaganie nie stanowi juĝ powodu do zmartwieñ dla uĝytkownika. WïÈczanie demona autovacuum w starszych wersjach Jeĝeli demon autovacuum jest dostÚpny, ale nie jest wïÈczony domyĂlnie, jak ma to miejsce w bazach danych PostgreSQL 8.1 i 8.2, istnieje kilka powiÈzanych z nim parametrów, które muszÈ byÊ wïÈczone w celu zapewnienia jego prawidïowej pracy. WiÚcej informacji na ten temat przedstawiono na stronach http://www.postgresql.org/docs/8.1/interactive/maintenance.html i http://www.postgresql.org/docs/8.2/interactive/routine-vacuuming.html. W wymienionych wersjach w pliku konfiguracyjnym postgresql.conf trzeba ustawiÊ trzy parametry: stats_start_collector=true stats_row_level=true autovacuum=on Warto zwróciÊ uwagÚ, ĝe — zgodnie z ostrzeĝeniem przedstawionym w dokumentacji — zale- cane jest równieĝ uĝycie parametru superuser_reserved_connections zezwalajÈcego na dziaïa- nie demonów autovacuum w wymienionych wersjach bazy danych. Demon autovacuum dostÚpny w wersjach 8.1 i 8.2 nie jest aĝ tak wydajny jak dostarczany w wersjach 8.3 i nowszych. Uzyskanie odpowiedniego balansu zapewniajÈcego prawidïowÈ obsïugÚ bazy danych bez zbyt duĝego obciÈĝenia serwera bÚdzie wymagaïo pewnych ekspery- mentów i dopasowywania wartoĂci. Poniewaĝ jest to tylko jeden proces, pozostawienie go, by dziaïaï w tle, gdy serwer jest zajÚty, bÚdzie mniejszym obciÈĝeniem dla serwera. Zagadnienie to nie bÚdzie tutaj obszernie omawiane. Ogólnie rzecz biorÈc, znacznie lepszym rozwiÈzaniem jest poĂwiÚcenie czasu na uaktualnienie bazy danych PostgreSQL do wydania zawierajÈcego nowszÈ wersjÚ demona autovacuum niĝ próba dostosowania starszej wersji. Szczególnie dotyczy to sytuacji, kiedy równoczeĂnie wystÚpujÈ inne problemy z wydajnoĂciÈ, których nie moĝna tak ïatwo rozwiÈzaÊ w starszej wersji serwera. maintenance_work_mem Kilka operacji w serwerze bazy danych wymaga pamiÚci roboczej dla operacji wiÚkszych niĝ zwykïe sortowanie. Polecenia VACUUM, CREATE INDEX oraz ALTER TABLE ADD FOREIGN KEY mogÈ alokowaÊ maksymalnÈ iloĂÊ pamiÚci okreĂlonÈ przez parametr maintenance_work_mem. Jest maïo prawdopodobne, aby wiele sesji przeprowadzaïo jednoczeĂnie tego rodzaju operacje. Dla tego parametru moĝna okreĂliÊ wartoĂÊ znacznie wyĝszÈ niĝ w standardowej opcji work_mem ustawia- nej dla kaĝdego klienta. Warto pamiÚtaÊ, ĝe przynajmniej parametr autovacuum_max_workers 153 Wysoko wydajny PostgreSQL 9.0 (dla którego od wersji 8.3 wartoĂÊ domyĂlna wynosi 3) zaalokuje tÚ iloĂÊ pamiÚci, dlatego teĝ podczas okreĂlania tej wartoĂci naleĝy uwaĝaÊ na takie sesje (prawdopodobnie z jednÈ lub dwiema sesjami wykonujÈcymi operacjÚ CREATE INDEX). Przy zaïoĝeniu, ĝe liczba demonów autovacuum nie zostaïa zwiÚkszona, typowe wysokie ustawienie omawianej wartoĂci w nowoczesnym serwerze bÚdzie miaïo wartoĂÊ 5 caïkowitej iloĂci pamiÚci RAM. Tak wiÚc nawet piÚÊ procesów nie wykorzysta wiÚcej niĝ jednÈ czwartÈ dostÚpnej pamiÚci RAM. Oznacza to, ĝe maintenance_work_mem wykorzystuje okoïo 50 MB pamiÚci na kaĝdy dostÚpny gigabajt pamiÚci RAM w serwerze. default_statistics_target PostgreSQL podejmuje decyzje dotyczÈce sposobu wykonywania zapytañ na podstawie danych statystycznych zebranych dla kaĝdej tabeli i przechowywanych w bazie danych. Tego rodzaju informacje sÈ zbierane podczas analizy tabel za pomocÈ polecenia ANALYZE lub demona autovacuum. W kaĝdym przypadku iloĂÊ informacji zbierana w trakcie analizy jest okreĂlana przez parametr default_statistics_target. ZwiÚkszenie wartoĂci tego parametru wydïuĝa analizÚ. Analiza regularnie przeprowadzana przez demon autovacuum w tle stanowi coraz wiÚksze obciÈ- ĝenie podczas konserwacji bazy danych. Jednak brak odpowiednich danych statystycznych dotyczÈcych tabeli skutkuje ryzykiem uĝycia niewïaĂciwego planu wykonywania zapytañ wzglÚdem tej tabeli. WartoĂÊ domyĂlna wymienionej opcji z reguïy jest bardzo niska (to znaczy 10), ale w Postgre- SQL 8.4 zostaïa zwiÚkszona do 100. Stosowanie wiÚkszej wartoĂci byïo popularne takĝe w star- szych wersjach bazy danych w celu ogólnego poprawienia zachowania zapytañ. Indeksy korzy- stajÈce z operatora LIKE dziaïajÈ znacznie lepiej, gdy opisywany parametr ma wartoĂÊ wyĝszÈ niĝ 100, a nie niĝszÈ, co wiÈĝe siÚ ze zdefiniowanÈ na staïe zmianÈ sposobu ich dziaïania po uĝyciu wartoĂci wiÚkszej niĝ 100 dla tego parametru. Warto zwróciÊ uwagÚ, ĝe zwiÚkszenie wartoĂci parametru default_statistics_target skutkuje ogólnym spowolnieniem systemu, jeĝeli w ogóle nie sÈ wykonywane zapytania, w których dodat- kowe dane statystyczne powodujÈ wybór lepszego planu wykonywania. Z tego powodu pewne proste testy wydajnoĂci pokazujÈ, ĝe baza danych PostgreSQL w wersji 8.4 jest nieco wolniejsza od 8.3, jeĝeli obie stosujÈ wartoĂci domyĂlne parametrów. W niektórych przypadkach po insta- lacji wersji 8.4 moĝna zmniejszyÊ wartoĂÊ wymienionego parametru. Ustawianie wyjÈtkowo duĝej wartoĂci parametru default_statistics_target jest odradzane, ze wzglÚdu na generowane wówczas ogromne obciÈĝenie dla bazy danych. Jeĝeli w tabeli znajduje siÚ kolumna, o której wiadomo, ĝe potrzebuje lepszych danych staty- stycznych, moĝna wzglÚdem tej kolumny wykonaÊ polecenie ALTER TABLE SET STATISTICS w celu dostosowania dla niej wartoĂci parametru. Takie rozwiÈzanie jest lepsze niĝ zwiÚkszanie wartoĂci domyĂlnej parametru dla caïego systemu, gdyĝ wtedy kaĝda tabela „pïaci” za wymaganie jednej kolumny w bazie danych. Zazwyczaj kolumny wymagajÈce do prawidïowej pracy naprawdÚ duĝej iloĂci danych statystycznych muszÈ mieÊ wartoĂÊ parametru default_statistics_target równÈ 1000 (w nowszych wersjach PostgreSQL zwiÚkszono jÈ do 10000), aby zmiana byïa 154 Rozdziaá 6. • Optymalizacja konfiguracji serwera widoczna. Jest to wartoĂÊ duĝo wiÚksza niĝ iloĂÊ danych statystycznych konieczna do zebrania w kaĝdej tabeli bazy danych. Punkty kontrolne Sposób dziaïania mechanizmu punktów kontrolnych i dotyczÈce go parametry przedstawiono w poprzednim rozdziale. W tym miejscu nacisk poïoĝono na powszechnie stosowane praktyki ustawiania wartoĂci poczÈtkowych dla tych parametrów. checkpoint_segments Kaĝdy segment mechanizmu WAL zabiera do 16 MB. Jak przedstawiono na stronie http://www. postgresql.org/docs/current/interactive/wal-configuration.html, maksymalnÈ liczbÚ segmentów, która moĝe byÊ w uĝyciu w danym momencie, moĝna obliczyÊ ze wzoru: (2 + checkpoint_completion_target) * checkpoint_segments + 1 Warto zwróciÊ uwagÚ, ĝe baza danych PostgreSQL w wersjach wczeĂniejszych niĝ 8.3 nie obsïu- guje rozproszonych punktów kontrolnych, ale nadal moĝna uĝyÊ powyĝszego wzoru i po prostu dla brakujÈcej wartoĂci wykorzystaÊ poniĝszy fragment kodu: checkpoint_completion_target=0 Wynik to caïkowita wielkoĂÊ wszystkich segmentów mechanizmu WAL, które mogÈ znaleěÊ siÚ na dysku. Pozwala to zarówno na ustalenie iloĂci zajmowanego miejsca na dysku twardym, jak i oszacowanie iloĂci czasu potrzebnego na przeprowadzenie naprawy po awarii bazy danych. Oczekiwany wzrost wielkoĂci dziennika zdarzeñ pg_xlog zostaï przedstawiony w poniĝszej tabeli. checkpoint_segments checkpoint_completion_target=0 target=0.5 target=0.9 3 10 32 64 128 256 112 MB 336 MB 1040 MB 2064 MB 4112 MB 8208 MB 144 MB 416 MB 1296 MB 2576 MB 5136 MB 10256 MB 160 MB 480 MB 1504 MB 2992 MB 5968 MB 11904 MB Ogólny wniosek, który moĝna wyciÈgnÈÊ na podstawie przedstawionych tutaj danych, jest nastÚ- pujÈcy: na kaĝde 32 segmenty punktów kontrolnych trzeba siÚ liczyÊ z akumulacjÈ okoïo 1 GB danych mechanizmu WAL. Poniewaĝ w przypadku takiej iloĂci danych naprawa bazy danych po awarii moĝe zajÈÊ sporo czasu, wartoĂÊ 32 to najwiÚksza, jakÈ warto zastosowaÊ w powaĝnym, produkcyjnym serwerze bazy danych. WartoĂÊ domyĂlna 3 jest jednak dla wiÚkszoĂci systemów stanowczo za maïa, nawet w niewielkich instalacjach naleĝy rozwaĝyÊ jej zwiÚkszenie do 10. 155 Wysoko wydajny PostgreSQL 9.0 WartoĂÊ wiÚkszÈ niĝ 32 stosuje siÚ zazwyczaj tylko w mniejszych serwerach przeprowadzajÈcych operacje typu bulk-loading, poniewaĝ moĝe to znacznie zwiÚkszyÊ wydajnoĂÊ, a czas naprawy bazy danych po ewentualnej awarii jest nieistotny. Bazy danych, które regularnie przeprowadzajÈ operacje bulk-loading, mogÈ wymagaÊ wiÚkszej wartoĂci parametru checkpoint_segments. checkpoint_timeout WartoĂÊ domyĂlna tego parametru wynoszÈca 5 minut jest wystarczajÈca dla wiÚkszoĂci instalacji. Jeĝeli system nie nadÈĝa z operacjami zapisu i zwiÚkszono wartoĂÊ parametru checkpoint_ ´segments w taki sposób, ĝe parametr checkpoint_timeout staï siÚ podstawowym sposobem inicjowania operacji tworzenia punktu kontrolnego, rozsÈdne bÚdzie rozwaĝenie zwiÚkszenia wartoĂci takĝe dla parametru checkpoint_timeout. Ustalenie przerwy w dïugoĂci dziesiÚciu minut lub nawet wiÚcej miÚdzy tworzeniem punktów kontrolnych nie jest niebezpieczne, po prostu wydïuĝa czas naprawy bazy danych po ewentualnej awarii. Poniewaĝ jest to jeden z parametrów, które majÈ wpïyw na dïugoĂÊ czasu niedostÚpnoĂci bazy danych po awarii, jego wartoĂÊ naleĝy ustawiaÊ bardzo ostroĝnie. checkpoint_completion_target Po zwiÚkszeniu wartoĂci parametru checkpoint_segments do przynajmniej 10 rozsÈdne wydaje siÚ równieĝ zwiÚkszenie wartoĂci parametru checkpoint_competion_target do jego praktycznego maksimum wynoszÈcego 0,9. W ten sposób uzyskiwane jest maksymalne rozproszenie punktów kontrolnych, co przynajmniej teoretycznie oznacza mniejsze obciÈĝenie zwiÈzane z operacjami wejĂcia-wyjĂcia. Jednak w niektórych sytuacjach utrzymywanie wartoĂci domyĂlnej 0,5 nadal bÚdzie lepszym rozwiÈzaniem, poniewaĝ zmniejsza prawdopodobieñstwo, ĝe operacje zapisu jednego punktu kontrolnego zazÚbiÈ siÚ z operacjami drugiego. Wydaje siÚ nieprawdopodobne, aby wartoĂÊ poniĝej 0,5 byïa w ogóle efektywna dla rozpra- szania punktów kontrolnych. Co wiÚcej, o ile liczba segmentów nie jest naprawdÚ ogromna, maïe zmiany wartoĂci parametru majÈ niewielkie znaczenie. Jedyne sensowne podejĂcie polega na wypróbowaniu obu wartoĂci (0,5 i 0,9) w aplikacji i sprawdzeniu na poziomie monitorowania systemu operacyjnego, która powoduje mniejsze obciÈĝenie zwiÈzane z operacjami wejĂcia- -wyjĂcia. Ustawienia mechanizmu WAL Mechanizm WAL (ang. Write-Ahead Log) uĝywany w PostgreSQL zostaï omówiony w rozdziale 5. wal_buffers Dokumentacja parametru wal_buffers sugeruje, ĝe wartoĂÊ domyĂlna wynoszÈca 64 kB jest wystarczajÈca tak dïugo, dopóki pojedyncza transakcja nie przekracza wymienionej wartoĂci. 156 Rozdziaá 6. • Optymalizacja konfiguracji serwera Jednak w praktyce testy wydajnoĂci intensywnie wykorzystujÈce operacje zapisu pokazujÈ, ĝe optymalnÈ wydajnoĂÊ moĝna osiÈgnÈÊ po ustawieniu znacznie wiÚkszej wartoĂci, co najmniej 1 MB lub wiÚcej. JedynÈ wadÈ jest zwiÚkszony poziom wykorzystania pamiÚci wspóïdzielonej. BiorÈc po uwagÚ, ĝe nie ma potrzeby, aby wiÚcej niĝ pojedynczy segment WAL musiaï byÊ bufo- rowany, a takĝe iloĂÊ pamiÚci instalowanÈ w nowoczesnych serwerach, parametrowi wal_buffers moĝna obecnie przydzieliÊ znacznie wiÚkszÈ wartoĂÊ: wal_buffers=16MB Po ustawieniu powyĝszej wartoĂci moĝna zapomnieÊ o parametrze wal_buffers jako poten- cjalnym wÈskim gardle lub komponencie, który póěniej trzeba optymalizowaÊ. W przypadku niewielkiej iloĂci pamiÚci w serwerze moĝna rozwaĝyÊ uĝycie mniejszej wartoĂci. wal_sync_method W rozdziale 2. wyjaĂniono, jak waĝne jest skonfigurowanie serwera w taki sposób, aby unikaï wykorzystywania nietrwaïych buforów zapisu. Jednym z celów parametru wal_sync_method jest optymalizacja zachowania podczas korzystania z nietrwaïych buforów zapisu. Zachowanie domyĂlne jest w pewien sposób odmienne od wiÚkszoĂci opcji. Podczas kompilacji kodu ěródïowego serwera rozwaĝane sÈ róĝne moĝliwe sposoby zapisu. Jeden, uznany za naj- bardziej efektywny, staje siÚ póěniej stosowany domyĂlnie w trakcie kompilacji. Jednak wartoĂÊ ta nie jest zapisana w pliku konfiguracyjnym postgresql.conf wygenerowanym przez initdb, co odróĝnia jÈ od innych automatycznie wykrywanych i charakterystycznych dla platform wartoĂci, takich jak shared_buffers. Przed zmianÈ czegokolwiek naleĝy za pomocÈ polecenia SHOW sprawdziÊ, co w uĝywanej przez czytelnika platformie zostaïo wykryte jako najszybsza, bezpieczna metoda. W systemie Linux wynik jest nastÚpujÈcy: postgres=# show wal_sync_method; wal_sync_method ----------------- fdatasync Z kolei na platformach Windows i Mac OS X istnieje specjalny parametr gwarantujÈcy, ĝe system operacyjny wyczyĂci wszystkie bufory zapisu. Na wspomnianych platformach wartoĂciÈ bezpiecznÈ, która wïÈcza takie zachowanie, jest: wal_sync_method=fsync_writethrough Jeĝeli ustawienie takie jest dostÚpne, naprawdÚ naleĝy je wykorzystaÊ! Pracuje ono prawi- dïowo i gwarantuje wykonywanie bezpiecznych operacji zapisu w bazie danych bez spowal- niania innych aplikacji, co ma miejsce w przypadku caïkowitego wyïÈczenia bufora zapisu w dysku twardym. 157 Wysoko wydajny PostgreSQL 9.0 Jednak powyĝsze ustawienie nie bÚdzie funkcjonowaïo na wszystkich platformach. Warto pamiÚtaÊ, ĝe po zmianie wartoĂci domyĂlnej na przedstawionÈ powyĝej nastÈpi pewien spadek wydajnoĂci. Dzieje siÚ tak zawsze podczas przejĂcia z buforowania niebezpiecznego do bez- piecznego. Na innych platformach optymalizacja parametru wal_sync_method moĝe byÊ znacznie bardziej skomplikowana. Teoretycznie istnieje moĝliwoĂÊ poprawienia wydajnoĂci operacji zapisu w sys- temach z rodziny Unix poprzez zmianÚ dowolnej metody zapisu uĝywajÈcej par write/fsync lub write/fdatasync na stosujÈcÈ zapis prawdziwie synchroniczny. Moĝna siÚ przekonaÊ, po wyda- niu polecenia SHOW, ĝe na platformach obsïugujÈcych bezpieczne zachowanie zapisu DSYNC jest to opcja stosowana domyĂlnie: wal_sync_method=open_datasync Jednak takĝe w tym przypadku opcja ta nie jest jawnie podana w pliku konfiguracyjnym. Jeĝeli tak jest na uĝywanej przez czytelnika platformie, moĝna przeprowadziÊ jedynie niewielkÈ optymalizacjÚ. WartoĂÊ open_datasync to ogólnie optymalne podejĂcie, a kiedy wymieniona opcja jest dostÚpna, pozwala nawet na bezpoĂrednie uĝywanie urzÈdzeñ wejĂcia-wyjĂcia, a takĝe pomijanie bufora systemu operacyjnego. W systemie Linux sytuacja jest prawdopodobnie najbardziej skomplikowana. Jak przedstawiono we wczeĂniejszym fragmencie kodu, platforma Linux domyĂlnie uĝywa metody fdatasync. Za pomocÈ poniĝszego polecenia moĝna wïÈczyÊ tryb synchronicznych operacji zapisu: wal_sync_method=open_sync Ponadto w wielu przypadkach moĝna odkryÊ, ĝe metoda ta dziaïa szybciej — czasami nawet znacznie szybciej — niĝ okreĂlona domyĂlnie. Jednak bezpieczeñstwo jej stosowania zaleĝy od uĝywanego systemu plików. W wiÚkszoĂci systemów Linux domyĂlnym systemem plików jest ext3, który w wielu przypadkach nie obsïuguje bezpiecznych operacji zapisu O_SYNC, co moĝe doprowadziÊ do uszkodzenia danych. Przykïady niebezpieczeñstw takiego ustawienia na platformie Linux przedstawiono w wÈtku „PANIC caused by open_sync on Linux” znajdujÈcym siÚ na stronie http://archives.postgresql.org/pgsql-hackers/2007-10/msg01310.php. IstniejÈ dowody, ĝe w ostatnich wersjach jÈdra (2.6.32) ten problem nie istnieje, jeĂli uĝywany jest system pli- ków ext4, ale takie rozwiÈzanie nie zostaïo jeszcze intensywnie przetestowane na poziomie bazy danych. W kaĝdym przypadku podczas przeprowadzania wïasnych testów wartoĂci parametru wal_sync_method naleĝy wykonaÊ równieĝ test polegajÈcy na „wyciÈgniÚciu z gniazda sieciowego wtyczki przewodu zasilajÈcego serwer”. Spowoduje to nagïe odciÚcie zasilania serwera i pozwoli na sprawdzenie, czy nastÈpiïa utrata jakichkolwiek danych w wyniku stosowania testowanej metody. Zalecane jest równieĝ przeprowadzenie dïugotrwaïych testów przy duĝym obciÈĝeniu, aby znaleěÊ sporadyczne bïÚdy, które mogÈ doprowadziÊ do awarii. 158 Rozdziaá 6. • Optymalizacja konfiguracji serwera Replikacja WAL i PITR Parametry archive_mode, archive_command oraz archive_timeout zostaïy omówione w rozdziale 14. Ustawienia na poziomie klienta Wprawdzie wszystkie parametry omawiane w tym podrozdziale mogÈ byÊ dostosowane na poziomie klienta, poĝÈdane jest ustawienie dla nich odpowiednich wartoĂci poczÈtkowych w gïównym pliku konfiguracyjnym. Gdy trzeba zmieniÊ wartoĂÊ parametru w poszczególnych klientach, zawsze moĝna jÈ wprowadziÊ na czas trwania sesji, wykorzystujÈc do tego polece- nie SET. effective_cache_size Jak wspomniano w poprzednim rozdziale, baza danych PostgreSQL uĝywa zarówno wïasnej dedykowanej jej pamiÚci (shared_buffers), jak i korzysta z bufora systemu plików. W pew- nych sytuacjach, podczas podejmowania decyzji dotyczÈcych efektywnoĂci uĝycia indeksu bÈdě nie, baza danych porównuje obliczone przez siebie wielkoĂci wzglÚdem efektywnej sumy wszystkich wymienionych buforów. WartoĂÊ tÚ spodziewa siÚ znaleěÊ w parametrze effective_ ´cache_size. Ta sama reguïa, wedle której wartoĂÊ parametru shared_buffers powinna wynosiÊ okoïo 25 iloĂci pamiÚci systemowej, mówi takĝe, ĝe wartoĂÊ parametru effective_cache_size powinna wynosiÊ od 50 do 75 iloĂci pamiÚci RAM. Aby znacznie dokïadniej oszacowaÊ wartoĂci, trzeba na poczÈtek przyjrzeÊ siÚ buforowi systemu plików: Q w systemach z rodziny Unix oszacowanie wielkoĂci bufora systemu plików wymaga dodania wartoĂci free i cached wyĂwietlanych przez polecenia free i top; Q w systemach Windows naleĝy wyĂwietliÊ okno Menedĝera zadañ Windows, przejĂÊ na zakïadkÚ WydajnoĂÊ i odczytaÊ wartoĂÊ w linii Bufor systemu. Po przyjÚciu, ĝe baza danych zostaïa juĝ uruchomiona, do obliczonych wartoĂci trzeba dodaÊ jeszcze wartoĂÊ shared_buffers, w ten sposób otrzyma siÚ effective_cache_size. Jeĝeli baza danych nie zostaïa jeszcze uruchomiona, bufor systemu operacyjnego bÚdzie zwykle wystarcza- jÈco dokïadny do oszacowania wartoĂci parametru. Po uruchomieniu bazy danych wiÚkszoĂÊ pamiÚci przeznaczonej dla bazy danych zwykle i tak bÚdzie zaalokowana na potrzeby jej bufora. Parametr effective_cache_size nie powoduje alokacji jakiejkolwiek pamiÚci. Jest uĝywany wyïÈcznie po to, by okreĂliÊ sposób wykonywania zapytañ, i jego ogólne oszacowanie jest wystar- czajÈce do wiÚkszoĂci celów. Kiedy jednak wartoĂÊ ta bÚdzie zbyt wysoka, wykonywanie zapy- tañ moĝe skutkowaÊ zakïóceniami w buforze zarówno bazy danych, jak i systemu operacyjnego 159 Wysoko wydajny PostgreSQL 9.0 wynikajÈcymi z koniecznoĂci odczytania ogromnej liczby bloków wymaganych do wykonania zapytania, które bardzo ïatwo zmieĂci siÚ w pamiÚci RAM. Bardzo rzadko zdarza siÚ, aby parametr ten byï optymalizowany na poziomie klienta, nawet jeĂli jest to moĝliwe. synchronous_commit W rozdziale 2. obciÈĝenie zwiÈzane z oczekiwaniem, aĝ fizyczny dysk zakoñczy operacjÚ zatwierdzania, wskazano jako potencjalne wÈskie gardïo podczas zatwierdzania transakcji. Jeĝeli czytelnik nie posiada podtrzymywanego bateryjnie bufora zapisu, który mógïby przyĂpieszyÊ takie operacje, ale koniecznie chce przyĂpieszyÊ operacje zatwierdzania transakcji, rodzi siÚ pytanie, w jaki sposób moĝna to zrobiÊ. Standardowym podejĂciem jest wyïÈczenie opcji synchronous_commit, która czasami nazywana jest opcjÈ wïÈczajÈcÈ asynchroniczne zatwierdzenia. Powoduje ona zgromadzenie w wiÚkszÈ grupÚ operacji zatwierdzenia w czÚstotliwoĂci okreĂlo- nej przez powiÈzany z niÈ parametr wal_writer_delay. Ustawienia domyĂlne gwarantujÈ realne zatwierdzenie transakcji na dysku po upïywie co najwyĝej 600 milisekund od chwili zatwier- dzenia transakcji przez klienta. W trakcie tego okresu czasu, który moĝna skróciÊ, liczÈc siÚ jednoczeĂnie ze spadkiem szybkoĂci, niezatwierdzone dane nie bÚdÈ odzyskane po ewentualnej awarii serwera. Warto zwróciÊ uwagÚ, ĝe opisywany parametr moĝna wyïÈczyÊ dla pojedynczego klienta na czas trwania jego sesji, zamiast wyïÈczaÊ go dla caïego serwera. WyïÈczenie dla klienta odbywa siÚ za pomocÈ polecenia SET: SET LOCAL synchronous_commit TO OFF; W ten sposób czytelnik dysponuje opcjÈ stosowania róĝnych fizycznych gwarancji zatwierdza- nia dla odmiennych typów danych wstawianych do bazy danych. Aktywnie monitorowana tabela — do której dane sÈ najczÚĂciej wstawiane, a uïamek sekundy strat akceptowany — jest dobrym kandydatem dla asynchronicznych zatwierdzeñ. W przypadku rzadziej zapisywanej tabeli przechowujÈcej rzeczywiste transakcje finansowe preferowanym rozwiÈzaniem jest sto- sowanie standardowych, synchronicznych zatwierdzeñ. work_mem Kiedy wykonywane jest zapytanie wymagajÈce sortowania danych, baza danych szacuje iloĂÊ danych koniecznych do uĝycia i nastÚpnie porównuje obliczonÈ wartoĂÊ do okreĂlonej w para- metrze work_mem. Jeĝeli jest wiÚksza (a wartoĂÊ domyĂlna parametru work_mem wynosi 1 MB), zamiast sortowaÊ dane w pamiÚci, zapisze je na dysku i przeprowadzi operacjÚ sortowania dyskowego. Taka operacja jest znacznie wolniejsza niĝ sortowanie przeprowadzane w pamiÚci. Dlatego teĝ, w przypadku regularnego sortowania danych i posiadania wolnej pamiÚci, zwiÚk- szenie wartoĂci parametru work_mem moĝe byÊ jednym z najefektywniejszych sposobów przy- Ăpieszenia dziaïania serwera. Magazyny danych generujÈce ogromne raporty majÈ w swoich serwerach gigabajty pamiÚci przypisane parametrowi work_mem. 160 Rozdziaá 6. • Optymalizacja konfiguracji serwera Problem polega na tym, ĝe niekoniecznie moĝna ïatwo przewidzieÊ liczbÚ operacji sortowania przeprowadzanych przez klienta, a parametr work_mem jest okreĂlany dla kaĝdej operacji sor- towania, a nie dla kaĝdego klienta. Oznacza to, ĝe iloĂÊ pamiÚci uĝywanej przez work_mem moĝe byÊ teoretycznie nieograniczona, jeĝeli liczba klientów jednoczeĂnie przeprowadzajÈcych sor- towanie bÚdzie wystarczajÈco duĝa. W praktyce w typowym zapytaniu nie ma zbyt wielu operacji sortowania, najczÚĂciej tylko jedna bÈdě dwie. Ponadto nie wszyscy aktywni klienci bÚdÈ w tym samym czasie przeprowa- dzali operacje sortowania. W trakcie okreĂlania iloĂci pamiÚci dla parametru work_mem zwykle bierze siÚ pod uwagÚ iloĂÊ wolnej pamiÚci RAM po alokacji shared_buffers (ta sama wielkoĂÊ bufora systemu operacyjnego obliczana na potrzeby parametru effective_cache_size), dzieli jÈ przez wartoĂÊ max_connections, a nastÚpnie bierze tylko czÚĂÊ obliczonej wartoĂci. Poïowa obli- czonej liczby bÚdzie wartoĂciÈ doĂÊ duĝÈ dla parametru work_mem. W takim przypadku w ser- werze moĝe zabraknÈÊ pamiÚci, jeĝeli kaĝdy klient bÚdzie przez caïy czas przeprowadzaï po dwie operacje sortowania, ale prawdopodobieñstwo wystÈpienia takiej sytuacji jest znikome. Obliczenia wartoĂci work_mem sÈ coraz czÚĂciej stosowane w najnowszych wersjach PostgreSQL w celu oszacowania, czy struktura hash moĝe zostaÊ zbudowana w pamiÚci. PamiÚÊ jest uĝywana takĝe przez klienta, a nie jedynie przeznaczona dla operacji sortowania. Przedstawiono po pro- stu najïatwiejszy sposób rozwiÈzywania problemów dotyczÈcych rodzajów alokacji pamiÚci. Podobnie jak synchronous_commit, parametr work_mem moĝe byÊ równieĝ ustawiany na poziomie klienta. W ten sposób wartoĂÊ domyĂlnÈ moĝna zdefiniowaÊ na Ărednim poziomie i zwiÚkszaÊ pamiÚÊ do sortowania dla uĝytkowników, którzy wykonujÈ zapytania generujÈce ogromne raporty. random_page_cost Para
Pobierz darmowy fragment (pdf)

Gdzie kupić całą publikację:

Wysoko wydajny PostgreSQL 9.0
Autor:

Opinie na temat publikacji:


Inne popularne pozycje z tej kategorii:


Czytaj również:


Prowadzisz stronę lub blog? Wstaw link do fragmentu tej książki i współpracuj z Cyfroteką: