pl  |  en

Za co nie lubi Cię Twój hoster 2

Po uważnej lekturze poprzedniego odcinka wiesz już, jak nie ściągnąć na siebie gniewu hostera miniaturkami, plikami tymczasowymi czy mailingami. To wszystko jest oczywiście bardzo istotne, ale prawdopodobnie najczęstszą kością niezgody między wielkim złym hostingiem a biednymi zastraszonym klientem są bazy danych. To w nich wydajność ma szczególne znaczenie a najwięcej w tej kwestii zależy od aplikacji klienta. Dlatego warto zwrócić uwagę na kilka często popełnianych błędów i niedopatrzeń. Jednym z najczęstszych uchybień jest brak indeksów.

Brak indeksów w bazach

Indeksy w bazach danych nie różnią się zbytnio od tych na końcu książek. Pomyśl, ile czasu zajęłoby Ci bez niego znalezienie konkretnego słowa w książce w obcym języku
— baza danych nie rozumie przecież swojej zawartości. Tak samo baza danych bez odpowiednich indeksów musi przejrzeć całą tabelę, żeby znaleźć szukane dane. Różnica w w
ydajności między zapytaniami używającymi indeksów a tymi bez nich jest często ogromna — liczona w rzędach wielkości. Aby sprawdzić, czy serwerowi baz danych można pomó
c tworząc indeksy, najlepiej wspomóc się poleceniem EXPLAIN, wspieranym przez serwery MySQL i PostgreSQL. Przykładowy wynik MySQLowego EXPLAIN operującego na prostym za
pytaniu:

   mysql> EXPLAIN SELECT votes FROM wp_votes WHERE post='3222';
   +----+-------------+----------+------+---------------+------+---------+------+------+-------------+
   | id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
   +----+-------------+----------+------+---------------+------+---------+------+------+-------------+
   |  1 | SIMPLE      | wp_votes | ALL  | NULL          | NULL | NULL    | NULL | 1407 | Using where |
   +----+-------------+----------+------+---------------+------+---------+------+------+-------------+

Z kolumny ‚key’ wynika, że zapytanie nie używa żadnego indeksu, a z ‚rows’ — że serwer musi przejrzeć wszystkie 1407 wierszy z tej tabeli. Spróbujmy nałożyć indeks na kolumnę ‚post’, do której odwołujemy się w zapytaniu i wywołać EXPLAIN ponownie.

mysql> CREATE INDEX i_post_wp_votes ON wp_votes(post);
Query OK, 1407 rows affected (0,04 sec)
Records: 1407  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT votes FROM wp_votes WHERE post='3222';
+----+-------------+----------+------+-----------------+-----------------+---------+-------+------+-------+
| id | select_type | table    | type | possible_keys   | key             | key_len | ref   | rows | Extra |
+----+-------------+----------+------+-----------------+-----------------+---------+-------+------+-------+
|  1 | SIMPLE      | wp_votes | ref  | i_post_wp_votes | i_post_wp_votes | 4       | const |    1 |       |
+----+-------------+----------+------+-----------------+-----------------+---------+-------+------+-------+

Różnicę widać od razu. Serwer baz danych przeszukał indeks (o wiele szybciej niż tabelę) i na jego podstawie od razu znalazł odpowiedni wiersz w tabeli.
W przypadku bardziej skomplikowanych zapytań wynik polecenia EXPLAIN będzie miał więcej wierszy. Bardzo zgrubną miarą kosztu zapytania może być iloczyn wszystkich wartości w kolumnie ‚rows’, dlatego najlepiej zacząć od największych tabel. Żeby jednak indeks mógł być wykorzystany, musi być nałożony na kolumnę, która jest częścią warunku (np. WHERE, JOIN lub GROUP) w zapytaniu. Indeksy nie są zupełnie darmowe — zajmują miejsce na dysku i (minimalnie ale jednak) spowalniają operacje zapisu do bazy, dlatego jak wszystkiego należy ich używać z głową.

Serwer PostgreSQL udostępnia bardziej rozbudowany (i nieco bardziej skomplikowany) wariant polecenia EXPLAIN. Najprościej wywołać je tak jak w MySQLu. Tabela example zawiera 10000 krotek. Każda z nich ma klucz główny (kolumna „id”) oraz liczbę losową z przedziału od 0 do 10000 (kolumna „value”).

example=> EXPLAIN SELECT * FROM example WHERE value < 100;
## QUERY PLAN

Seq Scan on example  (cost=0.00..171.25 rows=3233 width=8)
   Filter: (value < 100)
(2 rows)

W odpowiedzi serwer zwraca plan zapytania, czyli drzewo operacji, które musi wykonać żeby ustalić wynik. Koszt jest podany w umownych jednostkach, oddających złożoność operacji. W odróżnieniu od MySQLa nie trzeba tu nic mnożyć, pierwszy element planu zawiera oszacowanie kosztu całego zapytania. Dodając parametr ANALYZE otrzymamy informację o rzeczywistym czasie potrzebnym na wykonanie poszczególnych elementów planu. Uwaga! Zapytanie rzeczywiście się wtedy wykonuje, więc zachowaj ostrożność np. przy
EXPLAIN ANALYZE DELETE.

example=> EXPLAIN ANALYZE SELECT * FROM example WHERE value < 100;
## QUERY PLAN

Seq Scan on example  (cost=0.00..171.25 rows=3233 width=8) (actual time=0.030..2.219 rows=91 loops=1)
   Filter: (value < 100)
 Total runtime: 2.286 ms
(3 rows)

Nałóżmy teraz indeks, analogicznie jak w MySQLu:

example=> CREATE INDEX i_example_value ON example(value);
CREATE INDEX
example=> EXPLAIN ANALYZE SELECT * FROM example WHERE value < 100;
## QUERY PLAN

Bitmap Heap Scan on example  (cost=58.08..149.74 rows=3333 width=8) (actual time=0.161..0.299 rows=91 loops=1)
   Recheck Cond: (value < 100)
   ->  Bitmap Index Scan on i_example_value  (cost=0.00..57.25 rows=3333 width=0) (actual time=0.148..0.148 rows=91 loops=1)
         Index Cond: (value < 100)
 Total runtime: 0.354 ms
(5 rows)

Różnicę widać od razu, nawet na tak małej tabeli. Uważny czytelnik (tak, to Ty!) spyta jednak zapewne, skąd się bierze liczba 3233 (lub 3333) wierszy, którą to Postgres oszacował jako ilość wynikowych danych. Do tego pomylił się o ponad dwa rzędy wielkości, bo rzeczywiście zwróconych wierszy (actual … rows) było poniżej setki. Otóż Postgres trzyma statystyki na temat każdej z tabel, które wykorzystuje później do planowania zapytań. Jeżeli dane w tabeli zmienią się drastycznie, te statystyki przestają być aktualne i potrafią wprowadzić serwer w błąd, który skutkuje słabymi planami zapytań. Takie statystyki możemy przebudować poleceniem ANALYZE z opcjonalnym parametrem VERBOSE i nazwą tabeli.

Kolejne sprawdzenie planu zapytania zwróci już dużo lepsze oszacowanie, jak również niższy szacowany koszt:

example=> EXPLAIN ANALYZE SELECT * FROM example WHERE value < 100;
## QUERY PLAN

Bitmap Heap Scan on example  (cost=4.89..57.57 rows=83 width=8) (actual time=0.091..0.211 rows=91 loops=1)
   Recheck Cond: (value < 100)
   ->  Bitmap Index Scan on i_example_value  (cost=0.00..4.87 rows=83 width=0) (actual time=0.076..0.076 rows=91 loops=1)
         Index Cond: (value < 100)
 Total runtime: 0.282 ms
(5 rows)

Jeżeli Twój hoster oferuje bazy PostgreSQL, powinien zatroszczyć się o wywoływanie ANALYZE (lub VACUUM ANALYZE, o którym za chwilę), jednak po dużych zmianach w bazie warto wywołać to polecenie ręcznie.

Martwe krotki w bazach PostgreSQL

Aktualizacja krotki w Postgresie polega na utworzeniu jej kopii z nowymi wartościami i oznaczeniu oryginału jako niewidocznego dla przyszłych transakcji. Jeżeli nie istnieją już żadne transakcje, które mogłyby widzieć oryginalną krotkę, jest ona uznawana za “martwą”, natomiast pozostaje ona na dysku. Weźmy pod uwagę np. bloga, w którym chcemy pamiętać liczbę wyświetleń każdego posta. Po każdym zapytaniu w stylu:

UPDATE posts SET view_count = view_count + 1

serwer tworzy nową wersję krotki. Nie trzymaj danych, które zmieniają się często (w naszym przykładzie licznika wyświetleń) z danymi uaktualnianymi rzadko lub wcale (np treścią posta) – rozdziel je na dwie tabele i połącz kluczem obcym. Częste aktualizacje mogą doprowadzić do sytuacji, gdzie większość krotek będzie martwa, a serwer musi je wszystkie przeczytać, żeby się o tym przekonać. Stan tabeli można sprawdzić poleceniem ANALYZE VERBOSE tabela, które przy okazji uaktualni statystyki. Jeżeli liczba martwych krotek w wyniku przekroczy (powiedzmy) 20%, możemy odzyskać utracone miejsce i wydajność poleceniem VACUUM ANALYZE VERBOSE tabela.

Nieodpowiedni storage engine w MySQLu

Żeby nie było, że czepiamy się tylko Postgresa (to nasz ulubiony serwer baz danych), zwrócimy uwagę na jeden aspekt tabel w bazach MySQLowych, który potrafi położyć bazę na łopatki. Możliwość wyboru storage engine’u (czyli mechanizmu rzeczywiście przechowującego dane tabeli) to dość unikalna cecha MySQLa. Powszechnie używane są silnik i MyISAM oraz InnoDB, inne raczej nie są stosowane na typowych hostingach. MyISAM jest bardzo prosty i szybki — to dzięki niemu MySQL ma opinię wydajnej bazy — jednak nie wspiera on transakcji ani żadnych innych mechanizmów zapewniających integralność danych. Dla tych, którzy nie lubią żyć na krawędzi i cenią swoje dane, MySQL udostępnia w pełni transakcyjny silnik InnoDB. W odróżnieniu od Postgresa nie wymaga on cyklicznego odśmiecania poleceniem VACUUM lub analogicznym, jednak cena tego udogodnienia jest wysoka — InnoDB cechuje się bardzo kiepską wydajnością przy modyfikacji danych. Żeby osiągnąć akceptowalną prędkość zapisu dużej ilości danych do bazy, najlepiej pogrupować je w transakcje po kilkaset do kilku tysięcy zapytań.

Mieszanie silników w obrębie jednej bazy jest jak najbardziej dozwolone, ale może wiązać się z niespodziewanymi konsekwencjami, bo wycofanie (ROLLBACK) transakcji modyfikującej tabele MyISAM i InnoDB wycofa tylko zmiany w InnoDB, a tabele MyISAM pozostaną zmodyfikowane. Twojemu hosterowi raczej nie będzie to przeszkadzać, ale Twojej aplikacji pewnie tak, więc miej to na uwadze.

Trzymanie plików w bazie

Niektóre aplikacje pozwalające na wysyłanie plików na serwer decydują się na przechowywanie ich w całości w bazie danych. Jakkolwiek by na to nie patrzeć, jest to zły pomysł. Od serwowania plików statycznych jest serwer WWW, a przeniesienie tej funkcji na bazę danych niepotrzebnie obciąża bazę (która nie do takich operacji jest optymalizowana), aplikację (która musi przepychać plik między bazą a serwerem WWW) oraz sam serwer WWW (który tak czy inaczej musi te same dane do klienta wysłać). Dużo lepiej będzie zapisać wysyłane pliki na dysku w odpowiedniej strukturze katalogów (wspominaliśmy o tym w pierwszej części artykułu), a w bazie przechowywać wyłącznie ścieżki do tych plików.

Wyciąganie zbędnych danych z bazy

Zanim zaczniesz zakładać indeksy i optymalizować zapytania, zastanów się, czy rzeczywiście potrzebujesz wszystkich danych, które zwraca zapytanie. Zbyt wiele razy widzieliśmy aplikacje, które wyciągały całą tabelę tylko po to, by policzyć ile zawiera krotek albo przefiltrować dane dopiero w aplikacji, zamiast w bazie. Ignorantia SQL neminem excusat — w zasadzie wszystkie warunki, jakie można nałożyć na dane w tabeli da się przedstawić w języku SQL. Dla szczególnie opornych operacji współczesne serwery baz danych (nawet MySQL) udostępniają procedury składowane.

Ograniczenie ilości danych przesyłanych z bazy odciąża zarówno ją, jak i aplikację, która dostaje już tylko interesujące dane. Dzięki temu Twój serwis będzie działał płynniej i szybciej i wszyscy będą zadowoleni. Nawet czarny charakter tego artykułu — Twój hoster.

Autor: Magda Zarych, Grzegorz Nosek