Jak dbać o naszą bazę danych ? Automatyczna optymalizacja

Usuwanie, przenoszenie i dodawanie nowych plików na dysku twardym powoduje jego fragmentacje co spowalnia działanie naszego komputera.
W związku z tym użytkownik powinien co jakiś czas uruchamiać narzędzie defragmentacji, by komputer uporządkował dane na dysku w odpowiedni dla niego sposób.

Tak samo jest też w przypadku baz danych w SQL. Dodając i usuwając rekordy z tabel powodujemy, iż powstaje tzw. "nadmiar" co w przypadku dużych projektów może spowalniać działanie naszej strony.



Ostatnio umieszczałem w bazie 3 mln rekordów, a następnie kasowałem te w których pewne pola były puste. Usuwając około 700 tys. rekordów spowodowałem nadmiar wielkości 3 MB przez co problemem było nawet przeglądanie bazy przez phpmyadmin.
Moim ratunkiem okazało się wejście do bazy przez terminal w linuxie i optymalizacja tabeli.

By uniknąć takich niespodzianek przedstawiam dziś skrypt, który uruchamiany regularnie pozwoli wam optymalizować bazę danych automatycznie.

1) Założenia do projektu

• Należy posiadać dane do bazy MySQL takie jak host, login i hasło użytkownika oraz nazwę bazy danych w której chcemy zoptymalizować tabele.
• Na serwerze powinna istnieć możliwość uruchomienia zadań CRON (zadania cykliczne), by skrypt optymalizujący tabele mógł sam regularnie się uruchamiać.

2) Podstawowe operacje na bazie danych

Jak dotąd wykonaliśmy podstawowe operacje na bazie danych, które każdemu programiście PHP powinny być znane.
Połączyliśmy się z serwerem MySQL i wybraliśmy bazę danych.

Kolejnym krokiem jest poznanie nowych funkcji:

• mysql_list_tables();
• mysql_free_result();

Przy pomocy pierwszej funkcji pobierzemy informacje o nazwach wszystkich tabel w bazie danych.

Oczywiście nadmiar nie będzie występował we wszystkich tabelach, ponieważ zależnie od naszej aplikacji w niektórych dane są stałe i się nie zmieniają.

Drugim sposobem jest po prostu wypisanie tabel, które mają zostać zoptymalizowane (najlepiej w tablicy).

3) Optymalizacja tabel

W podanym skrypcie:

• Pobieramy listę tablic w naszej bazie danych, a właściwie wskaźniki do nich.
• W pętli rozpatrujemy każdy wynik osobno i zapytaniem SQL "OPTIMIZE TABLE nazwa_tabeli", optymalizujemy naszą tablicę.
• Gdy zoptymalizujemy wszystko skrypt wyjdzie z pętli.
• Funkcja mysql_free_result() zwolni miejsce zajęte przez wskaźnik. W przypadku małej ilości tabel funkcja ta mogłaby być zbędna.
• Kończymy połączenie z bazą danych.

4) Zadania cykliczne - CRON Jobs

Nie musimy pamiętać o tym by regularnie uruchamiać nasz skrypt. Może to zrobić za nas serwer.
Zależnie od naszego usługodawcy mamy różne panele do zarządzania serwerem. Są one zazwyczaj oparte o skrypty DirectAdmin, PLESK czy też ccPanel.

Musimy znaleźć opcję ustawiania zadań cyklicznych (CRON Jobs), a następnie skonfigurować regularność uruchamiania skryptu wg. naszych potrzeb.

Na swoim przykładzie mogę powiedzieć, że skrypt w jednym serwisie uruchamiam co tydzień w innym raz w miesiącu i to w zupełności wystarcza.

Co wpisać w pole command/komenda w CRON Jobs ?
Ścieżka do pliku musi być określona dokładnie i poprzedzona jeszcze ścieżką do PHP.

Pomiędzy "/usr/local/bin/php",a ścieżką do pliku jest umieszczona spacja.

/usr/local/bin/php /home/dyktus/domains/zzz.hekko.pl/public_html/skrypty/baza_konserw.php

Skrypt będzie uruchamiał się regularnie, a my możemy być pewni, że problemów z szybkością bazy danych mieć nie będziemy.