Przejdź do treści

MySQL – optymalizacja i wydajność

O pracy MySQL DBA – przemyślenia administratora

Archiwa

Archiwa z daty Kwiecień, 2010

Tabele tymczasowe

Kwi 29
MySQL

Tabele tymczasowe są nie raz bardzo przydatnym narzędziem dla programisty – udostępniają dodatkowe możliwości przetwarzania danych jeszcze po stronie serwera bazy danych. W tabeli tymczasowej można sobie założyć indeks, można wykonać serię rożnych zapytań na tym samym zestawie danych, już bez konieczności pisania skomplikowanych JOIN’ów itp. Problemem jest to, że tabele tymczasowe tworzone są także automatycznie, jeśli tylko MySQL uzna, że jest to konieczne do zrealizowania danego zapytania. Dlaczego jest to problem? Dlatego, że często programista lub świeżo upieczony administrator MySQL, piszący zapytania, nie jest świadomy tego, kiedy i dlaczego są one tworzone. Jeśli nie jest świadomy, nie jest też w stanie kontrolować zachowania serwera baz danych – prowadzi to często do sytuacji, w której baza danych, z nieznanych przyczyn, zaczyna poważnie zwalniać. Co gorsza, często zdarza się to po pewnym czasie od wdrożenia danego serwisu.

Co to są te automatycznie tworzone tabele tymczasowe? Są to tabele tworzone w oparciu o silnik MEMORY lub MyISAM, które zakładane i zapełniane danymi są przez serwer MySQL. Tworzone są na potrzeby danego, konkretnego zapytania i usuwane są w momencie, gdy przestają być potrzebne. Jak widać, mamy dwa rodzaje tabel – tworzone w pamięci (silnik MEMORY) i na dysku (silnik MyISAM). Z oczywistych względów, szybsze i mniej obciążające serwer są te pierwsze i z tego też względu, jeśli to tylko jest możliwe, to tabela zakładana jest właśnie w pamięci.

Tabela tymczasowa tworzona jest na dysku jeśli:
– jej wielkość przekracza wartości zmiennej max_heap_table_size lub tmp_table_size
– jej zawartość uniemożliwia utworzenie jej przy pomocy silnika MEMORY – w szczególności jeśli zawiera kolumny typu TEXT lub BLOB
czytaj dalej…

Komentarze wył. , , , Czytaj dalej

Analiza i testowanie zapytań – co sprawdzać?

Kwi 27
MySQL

Podczas tworzenia nowych, bądź modyfikowania starych zapytań kluczową kwestią jest sprawdzenie, jak dane zapytanie zachowuje się w różnych warunkach. Dlaczego jest to tak ważne? Weźmy pod uwagę następujące zapytanie:

SELECT first_name, last_name, title FROM film LEFT  OUTER JOIN film_actor USING (film_id) LEFT OUTER JOIN actor USING  (actor_id) WHERE first_name='PENELOPE' AND last_name='GUINESS' ORDER BY  title DESC;

Podczas przeprowadzania jego analizy (http://blog.ksiazek.info/2010/04/09/benchmark-i-profiling/) okazało się, że przydatny może się okazać indeks nałożony na kolumny first_name i last_name w tabeli `actor`.
Po sprawdzeniu wyników okazuje się, że dodanie tego indeksu nieznacznie zmniejszyło czas wykonywania zapytania (tabela `actor1` zawiera dodatkowy indeks):

|       14 |  0.00054000 | SELECT SQL_NO_CACHE first_name, last_name, title FROM film  LEFT OUTER JOIN film_actor USING (film_id) LEFT OUTER JOIN actor1 USING  (actor_id) WHERE first_name='PENELOPE' AND last_name='GUINESS' ORDER BY  title DESC     |
|       15 | 0.00062100 | SELECT SQL_NO_CACHE  first_name, last_name, title FROM film LEFT OUTER JOIN film_actor USING  (film_id) LEFT OUTER JOIN actor1 USING (actor_id) WHERE  first_name='PENELOPE' AND last_name='GUINESS' ORDER BY title DESC     |
|        16 | 0.00051900 | SELECT SQL_NO_CACHE first_name, last_name, title FROM  film LEFT OUTER JOIN film_actor USING (film_id) LEFT OUTER JOIN actor1  USING (actor_id) WHERE first_name='PENELOPE' AND last_name='GUINESS'  ORDER BY title DESC     |
|       17 | 0.00047700 | SELECT  SQL_NO_CACHE first_name, last_name, title FROM film LEFT OUTER JOIN  film_actor USING (film_id) LEFT OUTER JOIN actor USING (actor_id) WHERE  first_name='PENELOPE' AND last_name='GUINESS' ORDER BY title DESC      |
|        18 | 0.00045100 | SELECT SQL_NO_CACHE first_name, last_name, title FROM  film LEFT OUTER JOIN film_actor USING (film_id) LEFT OUTER JOIN actor  USING (actor_id) WHERE first_name='PENELOPE' AND last_name='GUINESS'  ORDER BY title DESC      |
|       19 | 0.00046100 | SELECT  SQL_NO_CACHE first_name, last_name, title FROM film LEFT OUTER JOIN  film_actor USING (film_id) LEFT OUTER JOIN actor USING (actor_id) WHERE  first_name='PENELOPE' AND last_name='GUINESS' ORDER BY title DESC      |

To samo okazuje się, gdy wykonamy prosty benchmark przy pomocy skryptów php:
– indeks – 3834.18 zapytań na sekundę
– bez indeksu – 3923.48 zapytań na sekundę
czytaj dalej…

Komentarze wył. , , , Czytaj dalej

JOIN z CONCAT()

Kwi 23
MySQL

Jeśli stosujemy funkcję CONCAT() jako jeden z parametrów JOIN’u, należy pamiętać o pewnych jej cechach. Jeśli wszystkie jej parametry są niebinarnymi ciągami znaków, wynik jej działania także jest niebinarnym ciągiem znaków. Jeśli jakiś paramert jest w postaci binarnej, lub jest to jakaś wartość liczbowa, która z automatu jest konwertowana na postać binarną – wynikiem działania funkcji jest binarny ciąg znaków.

Jakie to ma znaczenie? Załóżmy następującą strukturę bazy danych:

mysql> SHOW CREATE TABLE tab1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                              |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| tab1  | CREATE TABLE `tab1` (
`id` int(11) NOT NULL DEFAULT '0',
`data` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

mysql> SHOW CREATE TABLE tab2;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                            |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tab2  | CREATE TABLE `tab2` (
`id` int(11) NOT NULL DEFAULT '0',
`id_1` varchar(40) DEFAULT NULL,
`data` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_id_1` (`id_1`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

czytaj dalej…

Komentarze wył. , , , , Czytaj dalej

Profiling – ciąg dalszy

Kwi 21
MySQL

Kolejnym mechanizmem udostępnianym przez MySQL, a który można wykorzystać do przeanalizowania zapytania pod kątem wydajności i zrozumienia co się w czasie jego wykonywania faktycznie dzieje, jest mechanizm statystyk, które dostępne są dzięki zapytaniu:

SHOW STATUS;

Wynikiem tego zapytania jest długa lista zmiennych obrazujących stan serwera MySQL w ramach danej sesji – ilość wykonanych zapytań różnych typów, ilość utworzonych tablic tymczasowych, jakiego rodzaju JOIN’y były wykonywane, ile danych zostało przesłanych i tak dalej. Pełna lista tych parametrów wraz z opisem znajduje się w dokumentacji MySQL – http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html

czytaj dalej…

Komentarze wył. , , Czytaj dalej

Profiling

Kwi 19
MySQL

Wykorzystanie zapytania EXPLAIN i przeanalizowanie planu naszego SELECT’a to pierwszy krok, który należy wykonać aby sprawdzić wydajność zapytania. Nie jest to jednak wszystko. MySQL udostępnia dodatkowe mechanizmy, które pokazują, co dokładnie dzieje się z zapytaniem – jak jest wykonywane, jakie operacje są przeprowadzane na tabelach itp.
Pierwszy z tych mechanizmów to profiler. Włączamy go wykonując zapytanie

SET PROFILING=1;

czytaj dalej…

Komentarze wył. , , Czytaj dalej

Explain it to me

Kwi 9
MySQL

Podstawową zasadą pisania zapytań jest to, że każde z nich gruntownie testujemy pod względem wydajności. Do koniecznego minimum należy sprawdzenie planu, jaki MySQL stworzył dla danego zapytania. Służy do tego polecenie EXPLAIN.

Przykładowy wynik działania takiego zapytania może wyglądać na przykład tak.

mysql> EXPLAIN SELECT first_name, last_name, title FROM film LEFT OUTER JOIN film_actor USING (film_id) LEFT OUTER JOIN actor USING (actor_id) WHERE first_name='PENELOPE' AND last_name='GUINESS' ORDER BY title DESC;
+----+-------------+------------+--------+-----------------------------+---------------------+---------+---------------------------+------+----------------------------------------------+
| id | select_type | table      | type   | possible_keys               | key                 | key_len | ref                       | rows | Extra                                        |
+----+-------------+------------+--------+-----------------------------+---------------------+---------+---------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | actor      | ref    | PRIMARY,idx_actor_last_name | idx_actor_last_name | 137     | const                     |    3 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | film_actor | ref    | PRIMARY,idx_fk_film_id      | PRIMARY             | 2       | sakila.actor.actor_id     |    1 | Using where; Using index                     |
|  1 | SIMPLE      | film       | eq_ref | PRIMARY                     | PRIMARY             | 2       | sakila.film_actor.film_id |    1 |                                              |
+----+-------------+------------+--------+-----------------------------+---------------------+---------+---------------------------+------+----------------------------------------------+
3 rows in set (0.00 sec)

czytaj dalej…

Komentarze wył. , , Czytaj dalej

Od czegoś trzeba zacząć

Kwi 6
MySQL

Od pewnego czasu zastanawiałem się, czy rozpocząć prowadzenie bloga. Pod wpływem pewnych ostatnich wydarzeń uznałem, że może to nie będzie taki głupi pomysł. Zawsze  istnieje szansa, że choć jedna osoba uzna moje posty za przydatne i uwzględni moje sugestie podczas projektowania serwisu i jego bazy danych. Jeśli tak się stanie, warto było zaczynać.

W swojej pracy praktycznie codziennie stykam się z problemami, których przyczyną jest beztroska osób projektujących bazę danych dla aplikacji. Beztroska w tworzeniu zapytań, w samej strukturze bazy. Dobrze rozumiem, że obecnie ważne jest aby serwis internetowy powstał szybko – tak aby szybko mógł zacząć na siebie zarabiać. Brak jest czasu na dokładne przetestowanie funkcjonalności aplikacji, a co dopiero mówić o testach wydajnościowych. Skutkiem ubocznym tego typu zjawiska są serwisy, które są małymi koszmarkami jeśli chodzi o prędkość działania. To, że strona działa poprawnie i szybko, gdy działa w środowisku testowym, nie znaczy, że będzie funkcjonować poprawnie gdy jednoczesna ilość połączeń będzie liczona w setkach.

Tworząc aplikację korzystającą z bazy danych, pamiętajmy o indeksach. Bez nich nawet mała baza potrafi zabić ośmiordzeniowy serwer.

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