Dlaczego MySQL tworzy tak wiele tabel tymczasowych na dysku?

Czy jakikolwiek błąd konfiguracji może doprowadzić do utworzenia zbyt wielu tabel tymczasowych przez tuner mysql..mysql

Current max_heap_table_size = 200 M
Current tmp_table_size = 200 M
Of 17158 temp tables, 30% were created on disk

table_open_cache = 125 tables
table_definition_cache = 256 tables
You have a total of 97 tables
You have 125 open tables.
Current table_cache hit rate is 3%

Wcześniej tabela temp była „z 23725 tabel temp 38% zostało utworzonych na dysku”, ale zmieniłem max_heap i tmp_table na 200m z 16m i obniżyłem do 30%.

Konfiguracja:

engine myisam 
group_concat_max_len = 32768
key_buffer_size = 3.7 GB,
thread_stack = 256k,
table_cache = 125
query_cache_limit = 1M
query_cache_size = 16M
join_buffer_size = 2.00 M
max_connections = 800

Inny system z domyślną konfiguracją pokazuje „z 23725 tabel temp., 1% utworzono na dysku” z tą samą bazą danych.

Próbowałem zmienić domyślną na komputerze z tym problemem i nadal pokazuje „Z 580 tabel temp, 16% utworzono na dysku”.

Używam Ubuntu 11.4 64 bit z ram 48 gb. Czy ktoś może zaproponować rozwiązanie?

Czy zmiana silnika db z „myisam” na „pamięć” w tabelach za pomocą „grupuj według” naprawi to? Jak wyjaśniono tutaj: http://www.mysqlperformanceblog.com/2007/08/16/how-much-overhead-is-caused-by-on-disk-temporary-tables/

mysqltuner rzadko dostarcza użytecznych informacji. Wykorzystuje głównie nieistotne statystyki dotyczące „liczby trafień” i nakłada arbitralne ograniczenia na dopuszczalną liczbę widżetów. Jeśli nie napotykasz problemów z wydajnością, nie musisz tak naprawdę rozwiązywać żadnych problemów, które ci to przedstawia. Biorąc to pod uwagę, oto kilka podstawowych informacji na temat tabel tymczasowych ...

MySQL wewnętrznie wykorzystuje silnik pamięci MEMORY do tworzenia niejawnych tabel tymczasowych. Na dyskowych tabelach tymczasowych użyj silnika pamięci MyISAM.

Tabele tymczasowe są tworzone na dysku, gdy:

• Obecne są pola TEXT lub BLOB (ponieważ MEMORY nie obsługuje tych typów)
• wielkość wynikowej niejawnej tabeli tymczasowej przekracza mniejszą tmp_table_sizelubmax_heap_table_size
• Jeśli kolumna z więcej niż 512 bajtami jest używana z GROUP BY lub UNION lub ORDER BY

Przeczytaj dokumentację MySQL na temat wewnętrznych tabel tymczasowych, aby uzyskać więcej informacji.

Co możesz z tym zrobić? Zakładając, że w rzeczywistości stanowi to problem z wydajnością (a nie tylko przeszkadza ci intelektualnie):

• Unikaj pól TEKST / BLOB i zamiast tego używaj pól VARCHAR lub CHAR o odpowiednich rozmiarach, jeśli to możliwe.
• Jeśli nie można uniknąć TEKSTU / BLOBA, należy zsekwencjonować je w osobnych tabelach z relacją klucza obcego i ŁĄCZYĆ tylko wtedy, gdy są potrzebne.
• Traktuj duże kolumny, ponad 512 bajtów, tak jak powyższe pola TEXT / BLOB.
• Upewnij się, że zapytania zwracają tylko potrzebny zestaw wyników (odpowiednio selektywne klauzule WHERE, unikaj SELECT *)
• Unikaj podzapytań i zamień je złączeniami, zwłaszcza jeśli zwracają duży zestaw wyników
• Ostateczności - zarówno podbicie tmp_table_sizei max_heap_table_size. Nie rób tego, chyba że okaże się, że twoich zapytań nie można zoptymalizować.

Jeśli martwisz się konfiguracją MySQL i nie czujesz się swobodnie z dostępnymi ustawieniami, możesz spróbować Kreatora konfiguracji Percona jako punktu wyjścia.

Czy zmiana silnika db z „myisam” na „pamięć” w tabelach za pomocą „grupuj według” naprawi to? jak wyjaśniono tutaj

Nie, nie będzie i sprawi, że Twoje tabele nigdy nie zostaną utrwalone na dysku. Nie rób tego

„używanie tymczasowe” i „używanie plików” to nie koniec świata!

WYBIERZ ... GRUPUJ WEDŁUG a, b ORDER BY c, d - Wymaga 1 lub 2 „tabel tymczasowych”.

Są po prostu czasy, w których zapytania będą korzystać z tabel tymczasowych. Tabele temperatur mogą spowolnić zapytanie o niewielki czynnik. Ale jeśli zapytanie jest nadal „wystarczająco szybkie”, nie przejmuj się nim.

Jeśli zapytanie jest zbyt wolne (z tabelami TMP lub bez), omówmy to. Podaj SHOW CREATE TABLE, SHOW TABLE STATUS i EXPLAIN.