Przejdź do treści

MySQL – optymalizacja i wydajność

O pracy MySQL DBA – przemyślenia administratora

Archiwa

Kategoria: MySQL

Co jest szybsze – MyISAM czy InnoDB?

Sie 17
MySQL

Aktualizacja

Od roku 2010 trochę się już zmieniło w tej kwestii. Stan obecny (5.6-rc1) najlepiej opisuje poniższy post:
http://dimitrik.free.fr/blog/archives/11-01-2012_11-30-2012.html
W skrócie, InnoDB jest szybsze praktycznie w każdym elemencie. Pora najwyższa zapomnieć o MyISAM. A teraz wracamy do oryginalnego wpisu.

 

Pisząc poprzedni post dotyczący wydajności wykonałem serię testów wydajności obu silników. Poniżej zamieszczam wyniki tych testów, wykonywane były one przy pomocy sysbencha na tabeli wielkości 10 milionów rekordów. Dało to ok. 2GB danych dla MyISAM i 2,3GB danych dla InnoDB. Serwer MySQL, na którym uruchamiane były zapytania działał z następującą wersją silnika XtraDB (czyli InnoDB na sterydach, prosto od Percony):

mysql> SHOW VARIABLES LIKE 'innodb_version';
+----------------+------------+
| Variable_name  | Value      |
+----------------+------------+
| innodb_version | 1.0.8-11.2 |
+----------------+------------+
1 row in set (0.00 sec)

Pierwsza seria testów wykonana była z następującymi parametrami:

sysbench --test=oltp --mysql-user=root --oltp-table-size=10000000 --num-threads=128 --max-requests=1000000 --mysql-table-engine=myisam --mysql-db=sbtest1 --myisam-max-rows=20000000 --oltp-read-only=on --oltp-test-mode=simple run

czytaj dalej…

Komentarze wył. , , , , , Czytaj dalej

Różnice pomiędzy MyISAM i InnoDB – wydajność

Sie 11
MySQL

Aktualizacja

Od roku 2010 trochę się już zmieniło w tej kwestii. Stan obecny (5.6-rc1) najlepiej opisuje poniższy post:
http://dimitrik.free.fr/blog/archives/11-01-2012_11-30-2012.html
W skrócie, InnoDB jest szybsze praktycznie w każdym elemencie. Pora najwyższa zapomnieć o MyISAM. A teraz wracamy do oryginalnego wpisu.

 

MySQL posiada modułową budowę, dzięki czemu może udostępnić użytkownikom dużą ilość silników bazodanowych. Znakomita większość z nich powstała dla konkretnego zastosowania i służy do realizowania wyspecjalizowanych zadań. Wśród silników można jednak znaleźć dwa, które wykorzystywane są powszechnie. Chodzi oczywiście o MyISAM i InnoDB. Administrator MySQL często staje przed dylematem – który z tych dwóch silników wybrać dla własnych celów? Na przestrzeni lat przez internet przewaliły się dziesiątki zaciekłych dyskusji na temat tego, który z tych silników jest lepszym rozwiązaniem. MyISAM oferuje znacznie szybszą obsługę SELECTów, jest też łatwiejszy w backupowaniu i szybciej da się odtworzyć z backupu tabelę działającą na silniku MyISAM. InnoDB obsługuje transakcje, umożliwia stosowanie kluczy obcych, nie da się go łatwo backupować przez kopiowanie plików, ma przeciętną wydajność. Mniej więcej do takich wniosków doszedłem bazując na szybkim przeglądnięciu wyników z Google. Jak to się ma do realiów w jakich pracuje administrator MySQL z 2010 roku?
czytaj dalej…

komentarzy 5 , , , , Czytaj dalej

Lista IN() i podzapytania

Sie 5
MySQL

Z tego co widzę w logach bloga, jednym z przewijających się przez Google pytań jest pytanie o wydajność zapytania typu:

SELECT kol1, kol2 FROM tabela WHERE kol3 IN (1,2,3);

Na przykładzie bazy danych `sakila` sprawdźmy jak wygląda plan wykonania takiego SELECTa:

mysql> EXPLAIN SELECT * FROM rental WHERE customer_id IN (546, 67)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
type: range
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: NULL
rows: 47
Extra: Using where
1 row in set (0.00 sec)

Jak widać po zawartości kolumn type i key, zapytanie takie wykorzystuje indeks. Zapytanie typu ‚range’ zawsze korzysta z indeksu, natomiast wartość w kolumnie key wskazuje który indeks został użyty. Aby uzyskać wynik MySQL sprawdził 47 rekordów. Więcej informacji na temat poszczególnych typów znaleźć można w dokumentacji MySQL.

Tak więc mamy ładne, szybkie zapytanie. Sęk w tym, że często może być tak, że wygodniej byłoby napisać zapytanie w inny sposób:

SELECT * FROM rental WHERE customer_id IN (SELECT customer_id FROM customer WHERE first_name='KELLY');

Z punktu widzenia SQL jest to odpowiednik wcześniejszego zapytania, jako że wynikiem podzapytania są wartości 67 i 546:

mysql> SELECT customer_id FROM customer WHERE first_name='KELLY';
+-------------+
| customer_id |
+-------------+
|          67 |
|         546 |
+-------------+
2 rows in set (0.00 sec)

czytaj dalej…

Komentarze wył. , , , , , Czytaj dalej

SELECT DISTINCT, tabele tymczasowe i JOIN kilku tabel

Lip 30
MySQL

W poprzednim poście analizowałem zapytanie typu SELECT DISTINCT i pokazywałem jak można uniknąć tworzenia tablicy tymczasowej. Jak sytuacja wygląda, gdy wykonywany jest SELECT DISTINCT na JOINie dwóch tabel? Niestety, tu już sytuacja jest gorsza. Tak jak w przypadku zapytania na pojedynczej tabeli, tworzona jest tabela tymczasowa.

mysql> EXPLAIN SELECT SQL_NO_CACHE DISTINCT text1 FROM tab1 LEFT OUTER JOIN tab2 USING(id)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tab1
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 3
Extra: Using index; Using temporary
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: tab2
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: test.tab1.id
rows: 1
Extra:
2 rows in set (0.00 sec)

Tym razem, niestety, zastosowanie indeksów nie pomaga. Co można zrobić?

czytaj dalej…

Komentarze wył. , , , , Czytaj dalej

SELECT DISTINCT i tabele tymczasowe

Lip 24
MySQL

DISTINCT to modyfikator agregujący dane w wyniku zapytania – zwraca tylko unikalne wartości, bez powtórzeń. Najlepiej zobaczyć to na przykładzie:

mysql> SELECT text1 FROM tab2;
+-------+
| text1 |
+-------+
| w     |
| e     |
| e     |
| e     |
| f     |
+-------+
5rows in set (0.00 sec)

mysql> SELECT DISTINCT text1 FROM tab2;
+-------+
| text1 |
+-------+
| w     |
| e     |
| f     |
+-------+
3 rows in set (0.00 sec)

Jak widać, w pierwszym zestawie wyników trzykrotnie występuje wartość ‚e’. Po zastosowaniu DISTINCT otrzymaliśmy zagregowane wyniki, powtórzenia zostały usunięte. Generalnie stosowanie agregacji wiąże się z koniecznością utworzenia tabeli tymczasowej.

mysql> EXPLAIN SELECT DISTINCT text1 FROM tab2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tab2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
Extra: Using temporary
1 row in set (0.00 sec)

Administratorzy MySQL nie przepadają za tabelami tymczasowymi, bo stanowią one skazę na idealnym pięknie, jakim jest szybko i wydajnie działająca baza danych. Konkretnie mówiąc, potrafią one zmniejszyć tempo działania bazy z galopu, do czołgania się. Dzieje się tak szczególnie wtedy, gdy w zestawie wyników pojawi się jakaś kolumna typu BLOB czy TEXT i w efekcie tabela tworzona jest na dysku. Jak więc pozbyć się tabel tymczasowych z SELECT DISTINCT?
czytaj dalej…

Komentarze wył. , , , , , Czytaj dalej

Optymalna konfiguracja MySQL

Lip 19
MySQL

„Mam MySQL uruchomiony na serwerze fizycznym/VPS o następujących parametrach: <ciach parametry>.
Co zrobić, aby działał on z maksymalną wydajnością?

<ciach przeklejoną zawartość my.cnf>”

Znacie to, drodzy Czytelnicy? Tego typu pytania pojawiają się co pewien czas na różnorakich forach dotyczących webdeveloperki czy hostingu. Co pewien czas, coraz to nowe osoby szukają Graala, jakim jest „optymalna konfiguracja MySQL”. Uczynni, a lepiej orientujący się w temacie członkowie forum, sugerują poszukującym różne rozwiązania. Czy te poszukiwania uda się kiedykolwiek zakończyć?
czytaj dalej…

1 komentarz , , , Czytaj dalej

STRAIGHT_JOIN – gdy optimizer zawodzi

Lip 14
MySQL

Optimizer MySQL to kawałek kodu, który przy pomocy wbudowanych w niego algorytmów i na podstawie posiadanych przez serwer MySQL informacji o tabelach i ich zawartości, przepisuje zapytania do postaci najbardziej optymalnej dla serwera. Tyle teoria. W praktyce nie jest tak różowo. Podstawowym problemem jest to, że zawartość tabel się zmienia. Zmienia się wybiórczość danych, zmienia się ich lokalizacja – część danych została pobrana do pamięci, część jest na dysku. Serwer MySQL zakłada zawsze, że dane znajdują się na dysku. W praktyce, w przypadku baz danych mniejszych niż dostępna pamięć na serwerze fizycznym, może być tak, że cały zestaw używanych danych jest w pamięci. Jak wiadomo, czas dostępu do pamięci i do dysku trochę się różni. Administrator, wiedząc że czas dostępu będzie krótszy, może na przykład preferować zapytanie, które dokona skanu tabeli, ale za to nie będzie generowało tabeli tymczasowej. Optimizer zawsze wybierze tą opcję, która zagwarantuje konieczność odczytania mniejszej ilości danych. Statystyki tabeli także nie są uaktualniane na bieżąco (dlatego też trzeba co pewien czas wykonywać operacje ANALYZE TABLE), przez co optimizer może podejmować decyzje na podstawie błędnych danych. Na szczęście, w niektórych przypadkach można trochę poprawić efekty jego pracy.
czytaj dalej…

Komentarze wył. , , , Czytaj dalej

Sortowanie ORDER BY z wykorzystaniem indeksów

Lip 9
MySQL

Indeksy typu B-tree, które są stosowane w MySQL a o których pisałem niedawno, mają bardzo przydatną cechę – chodzi o to, że dane w indeksie przechowywane są w sposób posortowany. Z punktu widzenia administratora MySQL jest to cecha niezwykle pożyteczna, gdyż można ją wykorzystać do przyspieszenia działania zapytań, w których wyniki są sortowane. Wspominałem o tym przy okazji postu dotyczącego algorytmu filesort, tu temat trochę rozwiniemy.

czytaj dalej…

Komentarze wył. , , , Czytaj dalej

Komentarze

Lip 7
MySQL

Być może zostało to już przez niektórych czytelników zauważone, ale jeśli nie to chciałbym poinformować, że od dziś możliwe jest komentowanie wpisów na blogu. Zapraszam wszystkich do dzielenia się swoimi uwagami, sugestiami i pomysłami.

1 komentarz Czytaj dalej

Indeksy w MySQL

Lip 3
MySQL

To, że indeksy są niezbędne do poprawnego funkcjonowania bazy danych, powinni wiedzieć nie tylko administratorzy MySQL, ale także cała reszta użytkowników, którzy projektują strukturę bazy danych.

W silnikach MyISAM i InnoDB stosowane są indeksy typu B-tree. W pewnym uproszczeniu. W praktyce, pomiędzy indeksem w MyISAM a indeksem w InnoDB jest kilka różnic, nie są one jednak istotne dla dalszej części tego postu. Indeksy mogą zostać nałożone na jedną, lub kilka kolumn. Jest to stosunkowo istotna ich cecha, o której twórcy aplikacji bazodanowych (szczególnie ci początkujący) nie zawsze pamiętają. Przykładowy ALTER TABLE dodający indeks na kolumny `kategoria`, `podkategoria` i `cena` w tabeli `produkt` mógłby wyglądać następująco:

ALTER TABLE produkt ADD INDEX idx_kat_podkat_cena (kategoria, podkategoria, cena);

czytaj dalej…

Komentarze wył. , Czytaj dalej
MySQL – optymalizacja i wydajność
Copyright © 2018 MySQL – optymalizacja i wydajność Design by SRS Solutions do góry