Jak dbać o bazę SQL?

[Jurij]

Witajcie,

Każdy mniej lub bardziej doświadczony administrator forum IPB ma na głowie mnóstwo zadań: dbanie o rozwój forum, sprawowanie opieki nad redakcją, zarządzanie forum z poziomu ACP, pozycjonowanie forum, pozyskiwanie sponsorów i zajmowanie się wszelkimi sprawami finansowymi, aktualizowaniem skryptu i aplikacji i tak dalej. Administrowaniem forum zajmuję się od dwóch lat i przez ten okres zdołałem się wielu spraw nauczyć (głównie poprzez regularne zaglądanie na invisionize) i zauważyłem że rzadko kiedy mówi się o optymalizacji bazy SQL. Zazwyczaj zwraca się na to uwagę dopiero wówczas, gdy wystąpi jakiś fatal error i trzeba naprawić uszkodzone tabele lub kiedy firma zajmująca się hostingiem przyśle upomnienie, że forum wykracza poza parametry serwera i trzeba coś z tym zrobić

Pierwszym krokiem po instalacji forum IPB powinno być zainstalowanie na serwerze PMA czyli phpMyAdmin - narzędzia umożliwiającego zarządzanie bazą mySQL. Dzięki temu w łatwy sposób będziemy mogli zrobić kopię zapasową bazy danych, naprawić uszkodzoną tabelę, zoptymalizować ją czy wykonać jakąś modyfikację / naprawę na forum.

Przeglądając IZE znalazłem dwa ciekawe tematy w trochę zapomnianych już zakątkach forum:

1. Poradnik jak przenieść forum na inny serwer: z którego możemy się dowiedzieć jak wykonać kopię bazy danych oraz jak ją oczyścić, aby zajmowała mniej miejsca na serwerze i stanowiła mniejsze obciążenie dla hostingu.

2. Drugi ciekawy temat, to zbiór przydatnych zapytań SQL - .

I tutaj nasuwa się cała seria pytań. Jak często wykonywać kopię zapasową bazy danych? Czy oprócz tych trzech tabel:

truncate ibf_mail_error_logs;
truncate ibf_spider_logs;
truncate ibf_task_logs;

są jeszcze inne, które warto regularnie czyścić? Na co zwracać uwagę podczas przeglądania tabel, żeby forum stanowiło jak najmniejsze obciążenie dla serwera? Jak często należy optymalizować tabele i które w szczególności? A może po prostu nie ruszać bazy SQL w ogóle, dopóki działa?

Zapraszam wszystkich do dzielenia się swoimi doświadczeniami związanymi z optymalizacją i zarządzaniem bazą SQL

[Portek]

Jak często wykonywać kopię zapasową bazy danych?

Najlepiej jak najczęściej, a gdy masz możliwość to super sprawą jest d*plikacja baz - ale umówmy się, nie każdego stać na takie rozwiązanie, nie każdy shared udostępnia taką opcję no i nie każdy potrzebuje aż takiej spójności danych.

W sieci jest takie genialne narzędzie Percona toolkit - tyle że to znów ukłon w stronę tych administratorów którzy mają pełen dostęp do swoich serwerów (VPS, dedyk), nie będę dokładnie opisywał co i jak z tym narzędziem, bo raz że ma dobrą dokumentacje, dwa jest sporo opisów w sieci, generalnie narzędzie znacznie pomaga w utrzymaniu MySQL'a w czystości i stanie względnej używalności.

U mnie panuje takie coś że do puki MySQL działa, strony ładują się szybko to lepiej go nie ruszać. Podstawowa optymalizacja serwera (albo i nie), sprawdzenie czy wszystko działa, cache po stronie php'a i generalnie wszystko działa tak jak działać powinno. Backup baz raz dziennie ląduje na zewnętrznym serwerze (w tym wypadku dropbox), raz na miesiąc cały folder z forum ląduje również na zewnętrznym serwerze (również dropbox) i tyle. Czuję się względnie bezpiecznie jeśli chodzi o dane, nie mam żadnych super ekstra ważnych danych, więc uważam że jest okej.

Edytowane 3 Lipca 2012 przez Portek

[elroy]

O jaki świetny temat !

Ja stawiam głównie na backup (nie wiem czy to dobrze robię, chętnie wysłucham więc rad). Treść mam dość nietypową i utrata jej byłaby raczej gwoździem do trumny.

Mam więc w cronie ustawiony backup bazy o 4:00 w nocy za pomocą automysqlbackup (polecam!) o a 5:00 rsynciem kopiuję zawartość forum i bazy na drugiego vpsa (znalazłem jakąś tanioszkę w UK za grosze - do tego idealna).

Jak na razie się to sprawdza, bo mam przyrostowy backup. (albo raczej kopię)

Co do optymalizacji, na razie forum działa bardzo sprawnie (bo i postów raczej mało:153 089), więc bazę zostawiam w spokoju (zwłaszcza, że nie lubię się z tym bawić).

Z dropboxem świetny pomysł, nie wpadłem na to

Edytowane 3 Lipca 2012 przez elroy

[Macsch15]

znalazłem jakąś tanioszkę w UK za grosze - do tego idealna

Podaj link

[elroy]

http://www.minivps.co.uk/ukvps.php (vps2)

Mam na tym odpalone oprócz w/w, dodatkowo nomachine z środowiskiem graficznym. Wydala, nie narzekam. Uptime 39 dni.

Co do ceny, to często na http://www.lowendbox.com/ i http://www.webhostingtalk.com/ pojawią się kody promo. Żeby nie skłamać, za kwartał zapłaciłem chyba coś koło 6.89 funta.

Ostatnio też pojawiły się fajne vpsy dedykowane do backupu. Duży dysk, ale z małą ilością ramu i blokadą na ilość procesów (regulamin zabrania też odpalnia mysqla i obsługi www). Mam na kompie w pracy linki, to podeślę jutro.

[Portek]

Jeśli chodzi o VPS'y pod backup z gałęzi budżetowej to jest tego sporo - problem jest niestety z supportem gdy coś się dzieje, lub ogólnie z działaniem - za 2-5$/mc cudów wymagać nie można :-)

Sam swego czasu testowałem http://volumedrive.com/vdrive/?a=vps - fajna opcja, przez miesiąc czasu ani jednego padu. Fajnie i tanio jest też podobnież w https://bluevm.com/ no a jeśli mówić już o kompletnej budżetówce to zdecydowanie http://www.hostsign.co.uk/site/onedollarvps.html

@elroy Skoro masz dwa VPS'y i ważne dane w bazie to może replikacja? 100% spójność, to co masz w bazie nr. 1 ląduje automatycznie w bazie nr. 2 - chyba lepsza opcja bo jesteś zdecydowanie na bieżąco z danymi.

Co do dropbox'a to ma napisany genialny uploader w bash'u, kwestia dopisania kilku linijek w celu spakowania danych backupu i przesłania - http://www.andreafabrizi.it/?dropbox_uploader

[elroy]

No proszę, o replikacji nie pomyślałem. Dobry pomysł - zabiorę się za to.

[Jurij]

Ja poczyniłem ostatnio pewne obserwacje i co się okazało - optymalizacja bazy i jej czyszczenie to bardzo fajna sprawa bo można sporo miejsca zyskać i zapewne odciążyć hosting Przy nieco ponad 18 kilo postów tabela z postami ważyła 45 megabajtów, a po optymalizacji waga spadła do 35 megabajtów... Dla porównania kilka dni temu wyczyściłem tabelę z logami botów, teraz google natworzyło blisko 100 000 logów i tabela z samymi logami waży ponad 17 MB czyli połowę tego co najcenniejsze - tabeli z postami

A tak z ciekawości zapytam (jeśli to nie tajemnica) ile u Ciebie elroy waży tabela z postami? Przy 150 000 postów to pewnie coś koło 300 MB...

[Gość ratosluaf]

zapewne odciążyć hosting

Bardzo. Po naprawie + optymalizacji bazy u mnie IPB pokazywało obciążenie 10x mniejsze (43 z kawałkiem, potem 3,7)

[elroy]

A tak z ciekawości zapytam (jeśli to nie tajemnica) ile u Ciebie elroy waży tabela z postami? Przy 150 000 postów to pewnie coś koło 300 MB...

Oczywiście nie tajemnica - phpmyadmin podaje 171,5MB