Przejdź do treści

MySQL – optymalizacja i wydajność

O pracy MySQL DBA – przemyślenia administratora

Archiwa

Archiwa z daty Sierpień, 2010

MySQL 5.6

Sie 31

Wczoraj, 30 sierpnia wydana została nowa wersja MySQL – 5.6. Z nowości wprowadzono możliwość wymuszenia opóźnienia replikacji, coś co do tej pory trzeba było realizować przy pomocy zewnętrznych aplikacji (mk-slave-delay). Wprowadzono zmiany w Performance Schema, dodatkowe możliwości zarządzania partycjami i subpartycjami, a także możliwość wykorzystania mysqlbinlog do backupowania binary logów z zdalnego serwera. Do tej pory można było co najwyżej zrzucić je do postaci SQL, w MySQL 5.6 można będzie zapisać je także w postaci binarnej. Więcej informacji w dokumentacji MySQL.

Oczywiście pamiętamy, że obecnie, od 8 grudnia 2008 roku, stabilną wersją MySQL jest MySQL 5.1

W poprzednim poście porównywałem odporność obu silników na awarie. W skrócie chodziło o to, co się stanie, jeśli wyciągniemy wtyczkę z kontaktu? Jakie będą skutki? Jak szybko serwer zacznie działać i serwować dane? W tym poście pokrótce opiszę, co się stanie, jeśli wtyczka została wyciągnięta i po ponownym starcie serwera okazuje się, że trzeba dane przywrócić z backupu.
czytaj dalej…

W poprzednich postach (Różnice pomiędzy MyISAM i InnoDB – wydajność i Co jest szybsze – MyISAM czy InnoDB? ) porównywałem wydajność obu silników. Dziś przyglądniemy się innemu, także ważnemu aspektowi jakim jest bezpieczeństwo danych w trakcie awarii. Administrator MySQL, wybierając silnik bazodanowy, musi uwzględnić to, że awarie się zdarzają. Nawet w najlepiej przetestowanym środowisku produkcyjnym prędzej czy później dojdzie do padu jakiegoś elementu. Systemy operacyjne segfaultują, dyski padają, zasilaniu zdarza się zaniknąć, a UPSy się wyczerpują. Który z silników będzie lepszym wyborem, jeśli chodzi o odporność na tego typu sytuacje?
czytaj dalej…

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…

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…

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…