Przejdź do treści

MySQL – optymalizacja i wydajność

O pracy MySQL DBA – przemyślenia administratora

Archiwa

Kategoria: MySQL

Tym razem trochę prywaty. Jak być może stali czytelnicy zauważyli, na stronie, obok linka do formularza kontaktowego pojawił się nowy link.

Często jest tak, że serwis po prostu działa. Ktoś go kiedyś zainstalował na serwerze, skonfigurował jako tako i od tego czasu funkcjonuje sobie w miarę rześko. Niestety, czasami pojawia się problem – a to ruch jest za duży, a to zmienia się wersja serwera bazodanowego i trzeba przeprowadzić migrację. Czasami po prostu potrzeba przygotować jakieś nowe rozwiązanie, zapewniające stabilność działania usług, bo internetowy biznes tak się  ładnie rozkręcił, że każda minuta przestoju serwisu to już wymierne straty. Dlaczego w takich sytuacjach warto zatrudnić osobę z zewnątrz?

Na pierwszy rzut oka jest to droższe niż rozwiązanie problemu własnymi siłami. W końcu, większość rozwiązań stosowanych wraz z MySQL to rozwiązania Open Source. Zazwyczaj jakąś tam dokumentację udostępniają, how-to na Google też można znaleźć. Pytanie tylko, czy czas poświęcony na naukę nowego rozwiązania nie będzie ostatecznie więcej warty niż pieniądze wydane na zatrudnienie konsultanta? Szczególnie dobrze jest to policzyć, jeśli daną operację planuje się wykonywać raz na kilka lat (jak np. zmiana wersji MySQL czy gruntowna przebudowa architektury serwisu). Dodatkowo, jeśli zdecydujemy sami się zmierzyć z problemem, jesteśmy zdani tylko na siebie i uczymy się na własnych błędach. Konsultant takich problemów rozwiązywał już w swojej dotychczasowej historii pracy znacznie więcej, ma więc znacznie większe doświadczenie i wie gdzie są ukryte jakieś haczyki.

Na dziś tyle kryptoreklamy. Jutro nowy wpis, który dotyczyć będzie algorytmu ‚index merge’.

Dzisiejszy post to odpowiedź na jedno z zapytań z Google. Czy da się skonfigurować replikację tak, aby masterem był serwer MySQL w wersji 5.1 a slave MySQL w wersji 5.0? Zgodnie z dokumentacją, replikacja z nowszej wersji do starszej nie jest wspierana, ale w niektórych przypadkach da się to zrobić. Przyznam, że osobiście tego typu potrzeby nie miałem, ciężko z resztą autorytatywnie stwierdzić, że tego typu konfiguracja jest możliwa, bądź nie. Zbyt dużo tu zależy od konkretnych zapytań, które mają się replikować. Co wiemy na pewno?
czytaj dalej…

MySQL udostępnia bezpośredni dostęp do silników InnoDB i MyISAM – realizowany jest on przy pomocy polecenia HANDLER. Jego zasada działania sprowadza się z grubsza do tego, że operuje on na indeksie i pobiera rekord dla danego wpisu w indeksie. Wygląda to mniej więcej tak:

HANDLER tabela OPEN;
HANDLER tabela READ jakis_indeks='jakas wartosc';

Efektem powyższego jest zwrócenie zawartości rekordu, w przypadku którego kolumna indeksowana przez indeks „jakis_indeks” posiada wartość „jakas wartosc”. Więcej szczegółów na ten temat można oczywiście znaleźć w dokumentacji MySQL. Zgodnie z dokumentacją, HANDLER jest szybszy niż SELECT. Wiąże się to z tym, że odpada problem z koniecznością analizy i parsowania SQL, nie jest konieczny udział optimizera (bo skoro nie ma SQL, to nie bardzo co jest optymizować), nie stosuje się lockowanie tabel, tak więc odpada kolejny element do pilnowania. Sprawdźmy z resztą dokładnie jak to wygląda w praktyce:
czytaj dalej…

Nie będę się rozpisywać na temat tego, czym są klucze obce. Jeśli ktoś nie miał okazji się z nimi zetknąć, zapraszam do Google. Chciałbym natomiast napisać o tym, jakie wymogi muszą być spełnione, aby klucz obcy mógł zostać utworzony. Nie do końca jest to jasne, a świadczy o tym choćby fakt pojawienia się na jednym z czytanych przeze mnie forów opisu problemu z tworzeniem takiego klucza.

Załóżmy, że mamy dwie tabele, które tworzymy w następujący sposób:

CREATE TABLE tab1 (tab1_id SERIAL, tab2_id INT, CONSTRAINT pk PRIMARY KEY (tab1_id)) ENGINE=InnoDB;
CREATE TABLE tab2 (tab2_id SERIAL, kol INT, CONSTRAINT pk2 PRIMARY KEY (tab2_id)) ENGINE=InnoDB;

Chcemy je połączyć kluczem obcym na kolumnach tab2_id. Próbujemy taki klucz obcy utworzyć:

mysql> ALTER TABLE tab1 ADD CONSTRAINT tab1_tab2_fk FOREIGN KEY (tab2_id) REFERENCES tab2(tab2_id);
ERROR 1005 (HY000): Nie można stworzyć tabeli 'test.#sql-6ed0_b0a9' (Kod błędu: 150)

Co jest nie tak?

czytaj dalej…

Być może do części czytelników dotarł już poniższy link:

http://www.mysql.com/products/

MySQL w wersji z InnoDB za $2000? Ciekawe.  Na Twitterze szerzy się panika, użytkownicy Postgresql zacierają ręce…

Na szczęście, nie jest tak źle, jakby to wyglądało na pierwszy rzut oka. W przypadku MySQL Community Edition, czyli wersji opartej na licencji GPL, InnoDB jest, ma się dobrze i pozostanie dostępne. Przynajmniej tak twierdzi Oracle. Jeśli tak będzie, to nic się tragicznego nie stało – w końcu MySQL używane na komercyjnej licencji, z supportem i całą resztą dobrobytu,  nie jest zbyt często spotykane – szczególnie w Polsce.

Pożyjemy, zobaczymy. W każdym razie, Oracle wpadło na bardzo specyficzny pomysł…

Jeden z czytelników w komentarzu zapytał się, czy wykonywałem testy jak włączenie dodatkowych statystyk wpływa na działanie serwera. Fakt jest taki, że takich testów nie wykonywałem. Tego typu dane są mi niezbędnie potrzebne do skutecznego administrowania serwerami MySQL w środowisku, w jakim pracuję. Zadowoliłem się stwierdzeniem, że ich wpływ jest niewielki. Skoro jednak temat się pojawił, to takie testy wykonałem. Sprowadziło się to do wykonania w pętli 60 razy polecenia:

sysbench  --test=oltp --mysql-user=root --oltp-table-size=10000000  --num-threads=128 --max-requests=10000 --mysql-table-engine=innodb  --mysql-db=sbtest --oltp-read-only=off --oltp-test-mode=complex run

W przypadku wyłączonych statystyk (set global userstat_running=0;) średnia to 917,4 transakcji na sekundę.

Po włączeniu statystyk (set global userstat_running=1;) średnia spadła do 878,7 transakcji na sekundę.

Jak widać, spadek wydajności to ok. 5% Dla mnie jest to wartość jak najbardziej do zaakceptowania, biorąc pod uwagę ilość informacji, jakie uzyskuję dzięki tym statystykom.

W poprzednim poście pisałem o statystykach udostępnianych przez patch Google i przez Percona Server. Dziś kilka przykładów na wykorzystanie tych danych w praktyce.
czytaj dalej…

Gdy widać, że serwer bazodanowy zaczyna zwalniać, obciążenie procesora jest co raz większe, miło by było aby móc zlokalizować przyczynę takiego stanu rzeczy. W przypadku, gdy stosujemy standardową dystrybucję MySQL a użytkowników w bazie jest więcej niż kilku (i analizowanie bieżących wyników mytop’a czy SHOW PROCESSLIST; już nie wystarcza), w zasadzie jedynym rozwiązaniem jest włączenie pełnego logowania zapytań do slowlogów (long_query_time=0) i a potem wykorzystać magię awk’a/sed’a/perl’a aby posumować czas wykonywania zapytań dla każdego użytkownika. Problem w tym, że po pierwsze, tego typu statystyki są nieprecyzyjne. Jeśli chwilowo serwer został przeciążony, wydłuży się czas wykonywania wszystkich zapytań – nie tylko tych, które przeciążenie spowodowały. Po drugie, logowanie wszystkiego do slowlogu wpływa negatywnie na wydajność serwera – w końcu trzeba te wszystkie logi zapisać na dysku. Po trzecie, jeśli na serwerze jest spory ruch, to i logi będą długie – czas potrzebny na ich obróbkę, a także obciążenie przez ten proces generowane, może być znaczące i w praktyce uniemożliwić wykonywanie tego typu operacji na żądanie – da się je wykonać tylko w zaplanowanym okienku, w nocy, gdy dodatkowe obciążenie serwera nie będzie takim problemem. Czy jest jakieś inne rozwiązanie?
czytaj dalej…

MySQL posiada ciekawy mechanizm, który odpowiednio wykorzystany może znacznie przyspieszyć czas wykonywania się zapytań. Mechanizm ten to cache zapytań (query cache), a sprowadza się on do tego, że MySQL przeznacza pewną ilość pamięci na przechowywanie wyników zapytań. Działa to tak, że do cache trafia informacja o jakie zapytanie chodzi i jaki wynik zwróciło. Każde zapytanie jest sprawdzane pod kontem tego, czy jego wynik nie znajduje się w cache. Jeśli tak jest, wynik jest pobierany i przesyłany do klienta bez konieczności wykonywania całego zapytania. Jeśli nie, zapytanie jest normalnie wykonywane. Co ważne, w standardowej wersji MySQL porównywana jest cała treść zapytania. W efekcie zapytanie:

SELECT /* komentarz */ * FROM tabela WHERE kolumna='optymizacja';

jest innym zapytaniem niż:

SELECT * FROM tabela WHERE kolumna='optymizacja';

pomimo, że różnią się one tylko komentarzem. Zmodyfikowane wersje MySQL (jak na przykład Percona Server) potrafią sobie z komentarzami radzić, usuwając je po prostu przed sprawdzaniem cache. Co jest oczywiste, pobranie wyniku z cache jest rozwiązaniem szybszym niż wykonywanie całego zapytania, wydawałoby się, że zwiększanie query cache jest znakomitym sposobem na poprawę wydajności serwera MySQL. Czy tak jest faktycznie?
czytaj dalej…

Wydawałoby się, że sprawa jest oczywista, ale biorąc pod uwagę ilość pytań z Google dotyczących tego tematu, wygląda na to że nie jest tak do końca. Tak więc dziś szukamy logów MySQL. Jakie są te logi i do czego służą?

czytaj dalej…