Tekst ten skierowany jest do grona początkujących, oraz średnio zaawansowanych programistów. Dotyczy on kwestii związanych z optymalnym budowaniem oraz strojeniem aplikacji internetowych, pod kątem dużego obciążenia podczas działania a sytuacje tu opisane będą odnosiły się do optymalnej budowy skryptów w języku php jak i strojenia wydajności bazy danych MySQL. Czytający powinien mieć podstawowe pojęcie o programowaniu w języku php oraz bazie danych MySQL. Przydatna może okazać się również umiejętność korzystania z wyszukiwarki internetowej ponieważ część informacji jest wskazówką do szerszych działań.
Dobrym rozwiązaniem jest podział aplikacji na warstwy. Nie mam zamiaru przekonywać nikogo do takiego czy innego frameworka. Zdecydowanie dobrym zwyczajem jest rozdzielenie aplikacji w taki sposób, aby wszystkie zapytania danego modułu znajdowały się w jednym miejscu (klasie). Jest to oczywiście wielkie uogólnienie - nie z samych zapytań do bazy żyje aplikacja. Myślę tutaj również o spójnej segregacji kodu obliczeniowego (czasem musimy coś ostro policzyć). Takie przygotowanie kodu bardzo ułatwia życie w czasie profilowania aplikacji.
Warto wyposażyć nasze aplikacje w 2 najbardziej podstawowe funkcje, przydatne później w procesie profilowania. Pierwsza z nich to log zapytań SQL'owych. Warto więc wyposażyć klasę odpowiedzialną za obsługę zapytań (tzw. Database Layer) w funkcje zapisujące ilość, treść oraz czas realizacji zapytań (z tego wynika również sumaryczna ilość zapytań na danej podstronie). Funkcjonalność ta jest bardzo prosta do napisania. Gorzej wygląda temat łatwego profilowania kodu php podczas wykonywania. Ja na co dzień staram się wyposażać aplikację jedynie w sumaryczny czas generowania strony, oraz log zapytań. Jeśli sumaryczny czas zapytań wynosi np. 0.10 sekundy, a generowanie całej strony 5 sekund, wniosek jest jeden - mamy źle zoptymalizowane skrypty php. Jeśli czas generowania strony zbliżony jest do czasu samych zapytań, oznacza to że z php nie mamy problemów. Do profilowania skryptów php używam innych narzędzi, o czym później.
Najbardziej popularnym problemem w nie optymalnie napisanych skryptach są zapytania zagnieżdżone w pętli.
<?php
$SQL = ?SELECT * FROM newsy?;
foreach($db->fetchAll($SQL) as $row){
$SQL = ?SELECT nick FROM autorzy WHERE id = ?.$row['id_autora'].??;
$db->fetchOne($SQL);
// wyswietlamy newsa
}
?>
Powyższy pseudokod ilustruje sytuację. Problem polega na wzroście ilości zapytań, wraz z ilością newsów dodanych do bazy. Gdy wyświetlamy jednego newsa, ilość zapytań wyniesie 2. W przypadku gdy newsów będzie 20, zapytań będzie 21.
Rozwiązaniem tego problemu jest użycie SQL'owego JOIN:
<?php
$SQL = "SELECT n.*, a.nick FROM newsy n JOIN autorzy a ON n.id_autora = a.id";
foreach($db->fetchAll($SQL) as $row){
$db->fetchOne($SQL);
// wyswietlamy newsa
}
?>
Przy takim rozwiązaniu ilość zapytań do bazy podczas wyświetlania zawsze wyniesie 1. Generalizując - im mniej zapytań do bazy tym lepiej.
W pętlach nie powinniśmy jednocześnie umieszczać czaso/proceso chłonnych funkcji. Wystrzegajmy się wstawiania tam funkcji z rodziny preg oraz ereg (być może jest możliwość zastąpienia ich zwykłym str_replace?). Bardzo często ten kod wykonać możemy przed pętlą - unikniemy wielokrotnego wykonywania tego samego kodu podczas generowania strony.
Aby przejść do tematu indeksów w MySQL (na tym będę opierał dalsze rozważania) należało by wiedzieć o 2 sprawach wspomnianych w nagłówku.
EXPLAIN to słowo kluczowe które możemy dodać przed każde zapytanie wybierające dane z bazy (SELECT) w celu dokonania jego analizy. Wyniki zwracane przez MySQL opisują ilość rekordów jaka musiała zostać przeszukana w celu zwrócenia wyniku, sposób i algorytm sortowania, użyte indeksy.
Slow Query Log to dyrektywa MySQL'owa, która po włączeniu powoduje zapisywanie wszystkich zapytań, których wykonywanie trwa dłużej niż slow-query-time do pliku. Oznacza to iż po paru godzinach od uruchomienia aplikacji w pliku z logami znajdziemy całkiem pokaźny kawałek zapytań do analizy - będziemy wiedzieć w czym tkwi problem. Zapytania z tego pliku najlepiej przeanalizować używając EXPLAIN.
Indeksy są mechanizmem pozwalającym na przyspieszenie wyszukiwania danych. Uwaga - tylko wyszukiwania. Pisząc wyszukiwania mam na myśli zapytania typu SELECT oraz złączenia w tych zapytaniach (wszystkie JOIN'y). Indeksy powodują wolniejsze działanie zapytań dodających dane (w praktyce jest to mało istotne), oraz co ważne spowalniają sortowanie wyników. Zakładając iż na stronie internetowej, prezentującej nasze produkty udostępniamy wyszukiwarkę. Wyszukiwarka ta posiadać może wiele opcji, takich jak kategoria, cena, waga, rozmiar, kolor, etc. Wyszukiwarka pozwala również na wybranie sposobu sortowania wyników wyszukiwania. Wyobraźmy sobie więc tabelę w bazie. Tabela posiada 100 000 produktów.
Na pierwszy rzut oka, logika mogłaby podpowiedzieć nam iż indeksy powinniśmy założyć na wszystkich polach, po których można zawęzić wyszukiwanie. Logika ta jest jednak błędna. Każdy założony indeks, zajmuje miejsce w pamięci. Zakładając indeksy na wszystkie (pomijając opis oraz nazwę) w/w pola, rozmiar pojedynczego wiersza w tabeli zwiększył by się znacząco. Oznacza to iż mechanizm wyszukiwania MySQL podczas wyszukania oraz sortowania wyników potrzebował będzie dużo więcej pamięci aby te dane przechować, oraz odpowiednio zwrócić. Rozwaga powinna podpowiedzieć nam tutaj w jaki sposób utworzyć indeksy aby wykorzystać je dla najczęściej wybieranych opcji w wyszukiwarce. Dodatkowym problemem będzie sortowanie wyników, które również będzie zdecydowanie bardziej czasochłonne w przypadku dużej ilości założonych indeksów. Dla rozwiązania tego problemu sugeruję wykonanie zapytań z dodanym EXPLAIN, aby sprawdzić czas oraz sposób ich wykonywania.
W przypadku bardzo dużej ilości danych (np. jak w w/w przykładzie) typ danych który dobierzemy będzie miał istotne znaczenie na rozmiar tabel z danymi, co odbije się na dalszym szybszym operowaniu na danych. Kilka trywialnych acz podstawowych błędów:
Temat bardzo krótki - jeśli wyświetlamy pewne dane bardzo często, a nie wymagają one natychmiastowej aktualizacji przy zmianie rekordu w bazie (np. licznik odwiedzin na stronie może być odświeżany z godzinnym opóźnieniem) użyjmy plikowego mechanizmu cache w celu zmniejszenia obciążenia bazy. Największe efekty przynosi wprowadzenie tego typu mechanizmów na najczęściej otwieranych podstronach naszego serwisu - np. stronie głównej.
Na większości portalowych stron zdarza się losowac elementy. 5 losowych newsów, zdjęć etc.
Zapytanie:
SELECT * FROM newsy ORDER BY RAND() LIMIT 1;
Jest złym rozwiązaniem przy tabelach o większej ilości rekordów. Trzeba tutaj ponownie nawiązać do mechanizmów działających podczas wybierania danych. W tym zapytaniu, MySQL dla każdego wiersza wylosuje liczbę, następnie utworzy tabelę tymczasową do której wrzuci wynik tego działania. Tabela ta zostanie posortowana wg wartości RAND() a następnie zwrócony zostanie 1 wiersz. Przy większej ilości rekordów oznacza to dość konkretny kawałek pracy dla procesora oraz chwilowe zajęcie dużej ilości pamięci w celu utworzenia tabeli tymczasowej.
Rozwiązań jest co najmniej kilka, w zależności od zastosowania. Jednym z nich jest utworzenie tabeli newsy_rand do której co 10 minut w tle (uruchamiając prosty skrypt poprzez crona) losować będziemy 30 newsów. Następnie na stronie losowanie będziemy wykonywać z tabeli news_rand co będzie bardzo szybkie ponieważ jest tam mało rekordów. Kolejnym rozwiązaniem jest rozbicie tego zapytania na 2 inne:
SELECT COUNT(*) AS ilosc FROM newsy
następnie w php wylosowanie liczby z zakresu od 1 do ilosc oraz wykonanie zapytania:
SELECT * FROM newsy LIMIT $wylosowany_w_php_numer, 1
Minusem tego rozwiązania jest to iż zapytania są dwa zamiast jednego. Jednak te dwa zapytania są zdecydowanie szybsze niż wersja bazowa.
Redundancja - powielanie informacji w bazie. Wrócę do przykładu analizowanego wcześniej:
SELECT n.*, a.nick FROM newsy n JOIN autorzy a ON n.id_autora = a.id
Zapytanie to pobiera newsy, oraz nick autora. Wyobraźmy sobie jednak iż zapytanie to rozszerzyć musimy dodatkowo o ilość komentarzy do danego newsa. Wyglądało by więc ono docelowo:
SELECT n.*, a.nick, count(k.*) FROM newsy n JOIN autorzy a ON n.id_autora = a.id JOIN komentarze k ON n.id = k.id_newsa GROUP BY n.id
Zapytanie to stało się skomplikowane i przy dużej ilości rekordów może powodować pewne problemy. Rozwiązaniem może być dodanie do tabeli newsy dodatkowych pól o nazwach nick_autora oraz ilosc_komentarzy. Pamiętać jednak musimy aby dane te przy każdym dodaniu komentarza oraz zmianie nicka autora zaktualizować. Dane te powielają informacje zawarte w innych tabelach, jednak w pewnych sytuacjach utrata miejsca na dysku nie jest wielkim problemem, a jednocześnie rozwiązanie to pozwala na zaoszczędzenie mocy obliczeniowej serwera.
Błędem jest przesyłanie masy niepotrzebnych informacji pomiędzy serwerem baz danych a skryptami php. Po pierwsze MySQL musi dane te przygotować. Po drugie muszą one zostać przesłane co jeśli serwer MySQL nie znajduje się na tej samej maszynie co serwer www będzie generowało dodatkowy ruch sieciowy. Jeśli więc generujesz listę newsów, pobierz ich nazwę oraz datę publikacji. Nie używaj składni SELECT * w takim przypadku, ponieważ nie potrzebujesz wszystkich informacji z pobieranych wierszy a jedynie te wybrane. Dla leniwych nie chcących pamiętać o pobieraniu tylko tego co potrzeba odsyłam do wzorca aktywnego rekordu, który bardzo łatwo rozszerzyć o mechanizm nazywany Lazy Init. Do aplikacji wysyłane są tylko te dane, które są aktualnie potrzebne. Po więcej na ten temat odsyłam do PhpSolutions numer 4/2006.
http://www.xdebug.org/docs/profiler.Po zainstalowaniu aplikacji jako rozszerzenia php zbierane będą informacje na temat kolejności wykonywania funkcji w wykonywanych skryptach. Następnie do dyspozycji dostajemy aplikację ułatwiającą analizę zebranych logów - http://sourceforge.net/projects/wincachegrind. W ten sposób odnajdziemy najczęściej wykonywane funkcje, oraz te które wykonywane są najdłużej.
Liczę iż zebrane tutaj informacje pozwolą na budowę bardziej optymalnych serwisów oraz zaoszczędzi pieniądze wydawane na coraz mocniejsze serwery dedykowane. Sugeruję również stosować się do zasady ? pierw uruchom aplikację ? później profiluj to, z czym są problemy. Zaoszczędzi to wiele zbędnej pracy, którą wykonałbyś optymalizując aplikację w ciemno.
|
|
eZ publish Content Management System