Czy można zmusić MySQL do korzystania z więcej niż jednego rdzenia?

Przedstawiono mi kilka dedykowanych serwerów MySQL, które nigdy nie używają więcej niż jednego rdzenia. Jestem bardziej programistą niż DBA dla MySQL, więc potrzebuję pomocy

Ustawiać

Serwery są dość mocne z obciążeniem typu OLAP / DataWarehouse (DW):

• Podstawowa: 96 GB RAM, 8 rdzeni + pojedyncza macierz RAID 10
• Test: 32 GB pamięci RAM z 4 rdzeniami
• Największy DB to 540 GB, w sumie około 1,1 TB i głównie tabele InnoDB
• Solaris 10 Intel-64
• MySQL 5.5.x

Uwaga: Największa baza danych to replikowana z serwera OLTP DR, z której ładowana jest DW. Nie jest to pełny DW: trwa tylko od 6 miesięcy do 6 tygodni, więc jest mniejszy niż OLTP DB.

Obserwacje na serwerze testowym

• 3 oddzielne połączenia
• każdy ma współbieżny (i inny) ALTER TABLE...DROP KEY...ADD INDEX
• 3 tabele mają 2,5, 3,8 i 4,5 miliona wierszy
• Zużycie procesora wzrasta do 25% (jeden rdzeń jest maksymalnie obciążony) i nie więcej
• 3 ALTERY zajmują 12–25 minut (jeden na najmniejszym zajmuje 4.5)

pytania

1. Jakie ustawienie lub łatka jest wymagane, aby można było użyć więcej niż jednego rdzenia?
   To znaczy, dlaczego MySQL nie używa wszystkich dostępnych rdzeni? (podobnie jak inne RDBMS)
2. Czy jest to konsekwencja replikacji?

Inne notatki

• Rozumiem różnicę między „wątkiem” RDBMS a „wątkiem” systemu operacyjnego
• Nie pytam o żadną formę paralelizmu
• Niektóre zmienne systemowe InnoDB i wątków nie są optymalne
  (szukają szybkiej wygranej)
• Krótko mówiąc, nie jestem w stanie zmienić układu dysku
• W razie potrzeby można dostosować system operacyjny
• Pojedyncza ALTER TABELA na najmniejszym stole zajmuje 4,5 minuty (szokujące IMO)

Edytuj 1

• innodb_thread_concurrency ma wartość 8 na obu. Tak, to źle, ale nie zmusza MySQL do używania wielu rdzeni
• Rozmiar innodb_buffer_pool_size wynosi 80 GB w wersji podstawowej, 10 GB w teście (inna instancja jest zamykana). Na razie jest OK.
• innodb_file_per_table = ON

Edytuj 2

• innodb_flush_log_at_trx_commit = 2
• innodb_use_sys_malloc = ON
• innodb_flush_method powinien mieć wartość O_DIRECT (ale SHOW VARIABLES tego nie pokazuje)
• innodb_doublewrite = OFF
• System plików = ZFS (i mój sysadmin znalazł to: http://blogs.oracle.com/realneel/entry/mysql_innodb_zfs_best_practices )

Testować

• Metoda innodb_flush_method nie jest wyświetlana jako O_DIRECT, kiedy powinna być
• będzie śledzić ustawienia RolandoMySQLDBA

Daj mi znać, jeśli coś przeoczyłem

Twoje zdrowie

Aktualizacja

Zmieniono metodę innodb_flush_method + 3 x wątek w odpowiedzi RolandoMySQLDBA
Wynik:> 1 rdzeń użyty do testów = wynik pozytywny

Rozmawiałem o innodb_thread_concurrency z ekspertem MySQL na konferencji Percona Live NYC w maju 2011 roku .

Nauczyłem się czegoś zaskakującego: pomimo dokumentacji najlepiej pozostawić innodb_thread_concurrency0 (nieskończona współbieżność). W ten sposób InnoDB decyduje o najlepszej liczbie innodb_concurrency_ticketsdo otwarcia dla danej konfiguracji instancji MySQL.

Po ustawieniu innodb_thread_concurrencyna 0 możesz ustawić innodb_read_io_threadsi innodb_write_io_threads(oba od MySQL 5.1.38) na maksymalną wartość 64. Powinno to zaangażować więcej rdzeni.

MySQL automatycznie użyje wielu rdzeni, więc albo obciążenie 25% to przypadek ¹ lub potencjalna błędna konfiguracja w systemie Solaris. Nie będę udawać, że wiem, jak dostrajać solaris, ale oto artykuł, który omawia niektóre informacje na temat strojenia solaris .

Strony tuningu InnoDB zostały poddane przeglądowi w MySQL 5.5, więc jest tam również kilka dobrych informacji. Z porad IO dysku InnoDB :

Jeśli najwyższe narzędzie systemu Unix lub Menedżer zadań systemu Windows wykaże, że procent wykorzystania procesora z obciążeniem jest mniejszy niż 70%, prawdopodobnie obciążenie jest związane z dyskiem. Być może dokonujesz zbyt wielu zatwierdzeń transakcji lub pula buforów jest zbyt mała. Zwiększenie puli buforów może pomóc, ale nie ustawiaj jej na więcej niż 80% pamięci fizycznej.

Kilka innych rzeczy do sprawdzenia:

• Przełączanie innodb_flush_method do O_DIRECT warto testowania. Jeśli to pomoże, może być konieczne zamontowanie systemu plików z forcedirectioopcją

• Zmień innodb_flush_log_at_trx_commit z 1 na 0 (jeśli nie masz nic przeciwko utracie ostatniej sekundy po awarii mysql) lub 2 (jeśli nie masz nic przeciwko utracie ostatniej sekundy po awarii systemu operacyjnego).

• Sprawdź wartość innodb_use_sys_malloc . Ten artykuł zawiera więcej informacji o zmiennej.

  W tym czasie nie było bibliotek alokatora pamięci dostosowanych do procesorów wielordzeniowych. Dlatego InnoDB zaimplementował własny alokator pamięci w podsystemie pamięci. Tego alokatora strzeże pojedynczy muteks, który może stać się wąskim gardłem.

  Ale na końcu tego rozdziału są pewne zastrzeżenia dotyczące tego, co to znaczy włączyć zmienną (domyślnie jest włączona w 5.5).

  Zauważ, że gdy alokator pamięci InnoDB jest wyłączony, InnoDB zignoruje wartość parametru innodb_additional_mem_pool_size.

• Możliwe, że replikacja powoduje część problemu. Zdaję sobie sprawę, że nie jesteś zainteresowany równoległością, ale z opisu tego dziennika pracy :

  Obecnie replikacja nie skaluje się dobrze na komputerach wielordzeniowych. Pojedynczy wątek podrzędny wykonuje zdarzenia replikacji jeden po drugim i może nie poradzić sobie z obciążeniem wytwarzanym przez jednoczesne połączenia wielu klientów obsługiwane przez procesor osobnego serwera głównego.

Ostatecznie InnoDB może nie być najlepszym silnikiem do przechowywania danych z powodu operacji dyskowych. Możesz rozważyć zmianę tabel magazynu danych na skompresowane MyISAM .

¹ _{Przez przypadek mam na myśli wąskie gardło, które zapobiega wzrostowi obciążenia powyżej 25%, ale niekoniecznie jest to wymuszony problem z jednym rdzeniem.}

Pojedyncze połączenie będzie wykorzystywało tylko jeden rdzeń. (OK, InnoDB używa innych wątków, a więc rdzeni, do niektórych operacji we / wy, ale to nie jest znaczące).

Miałeś 3 ZMIANY, więc nie używałeś więcej niż 3 wartości rdzenia.

Niestety, nawet PARTITION nie używa wielu rdzeni.

Do niedawna wiele połączeń kończyło się maksymalnie po 4-8 rdzeniach. Xtradb Percona (dołączony do MariaDB) lepiej wykorzystuje wiele rdzeni, ale wciąż tylko jeden na wątek. Maksymalnie osiągają około 32 rdzeni.

IMHO oraz w opisanym przypadku użycia nigdy nie użyjesz więcej niż jednego rdzenia. Powodem jest to, że twoje obciążenie jest związane z IO, a nie z procesorem. Ponieważ Twoje 3 połączenia tworzą nowy Indeks, każde z nich musi odczytać całą tabelę z dysku: to zajmuje czas, a nie obliczanie Indeksów.

Weź pod uwagę, że wąskim gardłem może być wydajność IO twojego systemu plików.

Oprócz ustawień sugerowanych przez @RolandoMySQLDBA , ustawiłem także ustawienia noatimemontowania /etc/fstabdla partycji przechowującej mój katalog danych mysql ( /data01/mysqlw moim przypadku z /dev/sdb1zamontowanym na /data01).

Domyślnie Linux rejestruje czas dostępu KAŻDEGO dysku do odczytu lub zapisu, co negatywnie wpływa na wydajność IO, szczególnie w aplikacjach o wysokim IO, takich jak bazy danych. Oznacza to, że nawet odczyt danych z pliku powoduje zapis na dysk ... WAT!

Aby to wyłączyć, dodaj noatime opcję montowania /etc/fstabdla żądanego punktu montowania w następujący sposób (przykład w moim przypadku):

/dev/sdb1  /data01  ext4  defaults,noatime  0  2

Następnie zamontuj partycję:

mount -o,remount /data01

Powinno to zwiększyć wydajność odczytu / zapisu aplikacji korzystających z tej partycji. ALE ... nic nie przebije trzymania wszystkich danych w pamięci.