Jak ustalić optymalny rozmiar sort_buffer_size?

Czytam z przykładowego pliku konfiguracyjnego, który mówi:

# Sort buffer is used to perform sorts for some ORDER BY and GROUP BY
# queries. If sorted data does not fit into the sort buffer, a disk
# based merge sort is used instead - See the "Sort_merge_passes"
# status variable. Allocated per thread if sort is needed.

Mam kilka zapytań, które używają sortowania plików. Jak ustalić rozmiar bufora, którego potrzebuję, aby zapytania działały płynnie bez uderzania w dysk?

Jest tylko jedna zmienna statusu, która dba o sort_buffer_size . To właśnie masz w wiadomości z powrotem w pytaniu: Sort_merge_passes . Dokumentacja MySQL mówi:

Sort_merge_passes: liczba przebiegów scalania, które musiał wykonać algorytm sortowania. Jeśli ta wartość jest duża, należy rozważyć zwiększenie wartości zmiennej systemowej sort_buffer_size .

Pamiętaj o jednej rzeczy dotyczącej sort_buffer_size

Jeśli w wynikach POKAŻ GLOBALNY STAN widać wiele przepustek Sort_merge_pass, możesz rozważyć zwiększenie wartości sort_buffer_size, aby przyspieszyć operacje ORDER BY lub GROUP BY, których nie można poprawić za pomocą optymalizacji zapytań ani lepszego indeksowania

Podczas gdy podnoszenie sort_buffer_sizemoże pomóc w zapytaniach za pomocą GROUP BYs i ORDER BYs, lepiej jest ulepszyć zapytania, które można ulepszyć, i dodać indeksy, których można użyć w Optymalizatorze zapytań.

Pozostaje pytanie: Jak sprawdzić przepustki Sort_merge_ ?

Użyj tego kodu, aby sprawdzić, ile przepustek Sort_merge_pass miało miejsce w ciągu ostatnich 5 minut. Oblicza również przepustki Sort_merge_ na godzinę.

SET @SleepTime = 300;
SELECT variable_value INTO @SMP1
FROM information_schema.global_status WHERE variable_name = 'Sort_merge_passes';
SELECT SLEEP(@SleepTime) INTO @x;
SELECT variable_value INTO @SMP2
FROM information_schema.global_status WHERE variable_name = 'Sort_merge_passes';
SET @SMP = @SMP2 - @SMP1;
SET @SMP_RATE = @SMP * 3600 / @SleepTime;
SELECT @SMP,@SMP_RATE;

Jeśli znajdziesz Sort_merge_passes i wskaźnik jest zbyt wysoki, możesz zwiększyć sort_buffer_size . Załóżmy, że chcesz podbić do 4M. Uruchomiłbyś to:

mysql> SET GLOBAL sort_buffer_size = 1024 * 1024 * 4;

Dodałbyś to do my.cnf

[mysqld]
sort_buffer_size = 4M

Od czasu do czasu uruchamiałbyś kod, aby sprawdzić, czy nie występuje inny czas Sort_merge_passes spikes.

Nie trzeba zmieniać domyślnego rozmiaru sort_buffer_size. Nie rozumiesz jego użycia na podstawie pytania. Powinieneś zacząć od sprawdzenia SQL, aby sprawdzić, czy możesz go dostroić i spełnić warunki ORDER BY / GROUP BY za pomocą indeksu. Zasadniczo będzie to indeks złożony.

Ponadto: http://www.xaprb.com/blog/2010/05/09/how-to-tune-mysqls-sort_buffer_size/

Wskazówki zawarte w instrukcji (5.0–5.5) to

Jeśli w wynikach POKAŻ GLOBALNY STAN widać wiele przepustek Sort_merge_pass, możesz rozważyć zwiększenie wartości sort_buffer_size, aby przyspieszyć operacje ORDER BY lub GROUP BY, których nie można poprawić za pomocą optymalizacji zapytań ani lepszego indeksowania. Cały bufor jest przydzielany, nawet jeśli nie jest on potrzebny, więc ustawienie go większego niż wymagany globalnie spowolni większość sortowanych zapytań. Najlepiej jest zwiększyć to ustawienie sesji i tylko dla sesji, które wymagają większego rozmiaru. W systemie Linux istnieją progi 256 KB i 2 MB, w przypadku których większe wartości mogą znacznie spowolnić przydzielanie pamięci, dlatego należy rozważyć pozostanie poniżej jednej z tych wartości. Eksperymentuj, aby znaleźć najlepszą wartość dla swojego obciążenia.

Począwszy od 5.6, sformułowanie wskazuje, że optymalizator może wybrać wartość dla zapytania i że serwer może rozszerzyć bufor do limitu. Zmniejsza to koszt ustawienia zbyt wysokiej wartości. Brzmi więc to tak, jakbyś chciał być konserwatywny, niższy niż domyślny (jak to robią pliki cnf w wydaniu) dla wersji poniżej 5.6.4, ale może sobie pozwolić na wyższy limit, powiedzmy, domyślny 2 MB, a nawet więcej, od 5.6. 4, ponieważ pełna kwota nie jest ślepo przydzielana.

Począwszy od MySQL 5.6.4, optymalizator próbuje ustalić, ile miejsca jest potrzebne, ale może przydzielić więcej, aż do limitu.

Najlepszym sposobem na określenie optymalnego sort_buffer_sizejest przeprowadzenie testu porównawczego.

W jaki sposób? Podobnie jak @RolandoMySQLDBA powiedział, że sprawdzanie Sort_merge_passesmoże być pomocne, ale to nie jedyny czynnik wpływający na wydajność. Powinieneś być ostrożny przy zwiększaniu sort_buffer_size.

Dokument tak mówi

W systemie Linux istnieją progi 256 KB i 2 MB, w przypadku których większe wartości mogą znacznie spowolnić przydzielanie pamięci, dlatego należy rozważyć pozostanie poniżej jednej z tych wartości.

Jest post o testowaniu, który to kończy

sort_merge_passesnie są takie złe. Ustawienie sort_buffer_sizewystarczająco dużego, aby zero sort_merge_passesnie było optymalne.

Kiedy testowałem, otrzymałem również podobny wynik.

Najlepiej byłoby uniknąć sytuacji, w której trzeba zoptymalizować sort_buffer_size. W jaki sposób? Ten dokument optymalizacyjny ORDER BY może pomóc ci zrozumieć, jak działają rzeczy pod maską.

„mysql> SET GLOBAL sort_buffer_size = 1024 * 1024 * 4;” to zły sposób na umieszczenie w buforze sortującym 4m rozmiaru bufora sortującego, co powoduje, że bufor buforowy sortowania ma 4GB użycia

„mysql> SET GLOBAL sort_buffer_size = 1024 * 4;”

Jeśli byłem twój, nie próbuję zmieniać rozmiaru krótkiego bufora, to dobry sposób na zawieszenie serwera i wysłanie go do kosza. Lepiej spróbuj tworzyć lepsze zapytania.