170

Mam zajętą bazę danych z tabelami InnoDB o wielkości około 5 GB. Baza danych działa na serwerze Debiana przy użyciu dysków SSD i ustawiłem maksymalne połączenia = 800, które czasami nasycają i mielą serwer, aby się zatrzymać. Średnie zapytanie na sekundę wynosi około 2,5 KB. Muszę więc zoptymalizować wykorzystanie pamięci, aby zrobić miejsce dla maksymalnej możliwej liczby połączeń.

Widziałem sugestie, że innodb_buffer_pool_size powinien wynosić do% 80 całkowitej pamięci. Z drugiej strony otrzymuję to ostrzeżenie ze skryptu tuning-primer:

Max Memory Ever Allocated : 91.97 G
Configured Max Per-thread Buffers : 72.02 G
Configured Max Global Buffers : 19.86 G
Configured Max Memory Limit : 91.88 G
Physical Memory : 94.58 G

Oto moje obecne zmienne innodb:

| innodb_adaptive_flushing                          | ON                                                                                                                     |
| innodb_adaptive_hash_index                        | ON                                                                                                                     |
| innodb_additional_mem_pool_size                   | 20971520                                                                                                               |
| innodb_autoextend_increment                       | 8                                                                                                                      |
| innodb_autoinc_lock_mode                          | 1                                                                                                                      |
| innodb_buffer_pool_instances                      | 1                                                                                                                      |
| innodb_buffer_pool_size                           | 20971520000                                                                                                            |
| innodb_change_buffering                           | all                                                                                                                    |
| innodb_checksums                                  | ON                                                                                                                     |
| innodb_commit_concurrency                         | 0                                                                                                                      |
| innodb_concurrency_tickets                        | 500                                                                                                                    |
| innodb_data_file_path                             | ibdata1:10M:autoextend                                                                                                 |
| innodb_data_home_dir                              |                                                                                                                        |
| innodb_doublewrite                                | ON                                                                                                                     |
| innodb_fast_shutdown                              | 1                                                                                                                      |
| innodb_file_format                                | Antelope                                                                                                               |
| innodb_file_format_check                          | ON                                                                                                                     |
| innodb_file_format_max                            | Antelope                                                                                                               |
| innodb_file_per_table                             | ON                                                                                                                     |
| innodb_flush_log_at_trx_commit                    | 2                                                                                                                      |
| innodb_flush_method                               | O_DIRECT                                                                                                               |
| innodb_force_load_corrupted                       | OFF                                                                                                                    |
| innodb_force_recovery                             | 0                                                                                                                      |
| innodb_io_capacity                                | 200                                                                                                                    |
| innodb_large_prefix                               | OFF                                                                                                                    |
| innodb_lock_wait_timeout                          | 50                                                                                                                     |
| innodb_locks_unsafe_for_binlog                    | OFF                                                                                                                    |
| innodb_log_buffer_size                            | 4194304                                                                                                                |
| innodb_log_file_size                              | 524288000                                                                                                              |
| innodb_log_files_in_group                         | 2                                                                                                                      |
| innodb_log_group_home_dir                         | ./                                                                                                                     |
| innodb_max_dirty_pages_pct                        | 75                                                                                                                     |
| innodb_max_purge_lag                              | 0                                                                                                                      |
| innodb_mirrored_log_groups                        | 1                                                                                                                      |
| innodb_old_blocks_pct                             | 37                                                                                                                     |
| innodb_old_blocks_time                            | 0                                                                                                                      |
| innodb_open_files                                 | 300                                                                                                                    |
| innodb_purge_batch_size                           | 20                                                                                                                     |
| innodb_purge_threads                              | 0                                                                                                                      |
| innodb_random_read_ahead                          | OFF                                                                                                                    |
| innodb_read_ahead_threshold                       | 56                                                                                                                     |
| innodb_read_io_threads                            | 4                                                                                                                      |
| innodb_replication_delay                          | 0                                                                                                                      |
| innodb_rollback_on_timeout                        | OFF                                                                                                                    |
| innodb_rollback_segments                          | 128                                                                                                                    |
| innodb_spin_wait_delay                            | 6                                                                                                                      |
| innodb_stats_method                               | nulls_equal                                                                                                            |
| innodb_stats_on_metadata                          | ON                                                                                                                     |
| innodb_stats_sample_pages                         | 8                                                                                                                      |
| innodb_strict_mode                                | OFF                                                                                                                    |
| innodb_support_xa                                 | ON                                                                                                                     |
| innodb_sync_spin_loops                            | 30                                                                                                                     |
| innodb_table_locks                                | ON                                                                                                                     |
| innodb_thread_concurrency                         | 4                                                                                                                      |
| innodb_thread_sleep_delay                         | 10000                                                                                                                  |
| innodb_use_native_aio                             | ON                                                                                                                     |
| innodb_use_sys_malloc                             | ON                                                                                                                     |
| innodb_version                                    | 1.1.8                                                                                                                  |
| innodb_write_io_threads                           | 4                                                                                                                      |

Uwaga dodatkowa, która może być istotna: widzę, że kiedy próbuję wstawić duży post (powiedzmy ponad 10 KB) z Drupala (który znajduje się na osobnym serwerze internetowym) do bazy danych, trwa on wiecznie, a strona nie wraca poprawnie.

W związku z tym zastanawiam się, jaki powinien być mój innodb_buffer_pool_size dla optymalnej wydajności. Doceniam twoje sugestie, aby ustawić ten i inne parametry optymalnie dla tego scenariusza.

mysql innodb

— alfish
źródło

252

Twój rozmiar innodb_buffer_pool_size jest ogromny. Masz ustawiony na 20971520000. To 19,5135 GB. Jeśli masz tylko 5 GB danych i indeksów InnoDB, powinieneś mieć tylko około 8 GB. Nawet to może być zbyt wysokie.

Oto, co powinieneś zrobić. Najpierw uruchom to zapytanie

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

Otrzymasz RIBPS, zalecany rozmiar puli buforów InnoDB w oparciu o wszystkie dane i indeksy InnoDB z dodatkowymi 60%.

Na przykład

mysql>     SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
    ->     (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
    ->     FROM information_schema.tables WHERE engine='InnoDB') A;
+-------+
| RIBPS |
+-------+
|     8 |
+-------+
1 row in set (4.31 sec)

mysql>

Przy takim wyjściu ustawiłbyś następujące w /etc/my.cnf

[mysqld]
innodb_buffer_pool_size=8G

Kolejny, service mysql restart

Po ponownym uruchomieniu uruchom mysql na tydzień lub dwa. Następnie uruchom to zapytanie:

SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM
(SELECT variable_value PagesData
FROM information_schema.global_status
WHERE variable_name='Innodb_buffer_pool_pages_data') A,
(SELECT variable_value PageSize
FROM information_schema.global_status
WHERE variable_name='Innodb_page_size') B;

To da ci ile rzeczywistych GB pamięci jest aktualnie używanych przez dane InnoDB w puli buforów InnoDB.

Pisałem o tym wcześniej: Co ustawić innodb_buffer_pool i dlaczego ..?

Możesz po prostu uruchomić DataGBteraz tę kwerendę, zamiast rekonfigurować, restartować i czekać tydzień.

Ta wartość DataGBbardziej przypomina, jak duża powinna być pula buforów InnoDB + (procent określony w innodb_change_buffer_max_size). Jestem pewien, że będzie to znacznie mniej niż 20000M, które właśnie zarezerwowałeś. Oszczędności pamięci RAM można wykorzystać do strojenia innych rzeczy

CAVEAT # 1

Jest to bardzo ważne, aby pamiętać: czasami InnoDB może wymagać dodatkowych 10% ponad wartość parametru innodb_buffer_pool_size . Oto, co mówi na ten temat dokumentacja MySQL :

Im większa jest ta wartość, tym mniej dyskowych operacji we / wy jest potrzebnych do uzyskania dostępu do danych w tabelach. Na dedykowanym serwerze bazy danych można ustawić do 80% wielkości fizycznej pamięci maszyny. Przygotuj się na przeskalowanie tej wartości, jeśli wystąpią inne problemy:

Rywalizacja o pamięć fizyczną może powodować stronicowanie w systemie operacyjnym.

InnoDB rezerwuje dodatkową pamięć na bufory i struktury kontrolne, dzięki czemu całkowita przydzielona przestrzeń jest o około 10% większa niż określony rozmiar.

Przestrzeń adresowa musi być ciągła, co może stanowić problem w systemach Windows z bibliotekami DLL ładowanymi pod określonymi adresami.

Czas inicjalizacji puli buforów jest mniej więcej proporcjonalny do jej wielkości. W dużych instalacjach ten czas inicjalizacji może być znaczący. Na przykład na nowoczesnym serwerze Linux x86_64 inicjalizacja puli buforów 10 GB zajmuje około 6 sekund. Patrz rozdział 8.9.1, „Pula buforów InnoDB” .

CAVEAT # 2

I Widzę następujące wartości w twoim my.cnf

| innodb_io_capacity                                | 200 |
| innodb_read_io_threads                            | 4   |
| innodb_thread_concurrency                         | 4   |
| innodb_write_io_threads                           | 4   |

Liczba ta utrudni InnoDB dostęp do wielu rdzeni

Ustaw następujące ustawienia:

[mysqld]
innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64

Pisałem o tym wcześniej w DBA StackExchange

May 26, 2011: O wydajności baz danych jednowątkowych i wielowątkowych
Sep 12, 2011: Czy można zmusić MySQL do korzystania z więcej niż jednego rdzenia?
Sep 20, 2011: Wiele rdzeni i wydajność MySQL

Właśnie odpowiedziałem na takie pytanie w ServerFault, używając bardziej zwięzłej formuły :

SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
Recommended_InnoDB_Buffer_Pool_Size FROM
(
    SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
    FROM
    (
        SELECT SUM(data_length+index_length)*1.1*growth RIBPS
        FROM information_schema.tables AAA,
        (SELECT 1.25 growth) BBB
        WHERE ENGINE='InnoDB'
    ) AA
) A;

— RolandoMySQLDBA
źródło

1

Dzięki za ten świetny post! Twoja formuła rozpoczynająca się od SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM...generuje następujący błąd w MySQL 5.7: „ Funkcja„ INFORMACJE_SCHEMA.GLOBAL_STATUS ”jest wyłączona; zobacz dokumentację dla„ show_compatibility_56 ” ”. Czy masz przypadkiem zaktualizowaną wersję?

— Benjamin

Dostaję 307 RIBPS i 264G. To znaczy, że potrzebuję 307 GB pamięci RAM?

— E_Blue

Więcej jak 264G. Ale powinieneś mieć wystarczająco dużo pamięci RAM, w przeciwnym razie oddaj wspomniane 80% pamięci RAM mysql, w zależności od tego, co jeszcze działa w systemie.

— sjas

2

Najlepszy post, jaki kiedykolwiek przeczytałem! Mam ~ dużą bazę danych około 3 GB. Po przeczytaniu twojej odpowiedzi / artykułu i szybkości linków

— wzrosło

4

@Benjamin: Od MySQL 5.7.6 schemat_informacji jest scalany w scenie_wydajności. Więc po prostu zmień „kwerendę_informacyjną” na „kwerendę wydajności” w zapytaniu, aby działała. Źródło: dev.mysql.com/doc/refman/5.7/en/status-table.html

— Ralph Bolton

11

Coś takiego? Korzystanie SHOW VARIABLESi SHOW GLOBAL STATUS:

Wyrażenie: innodb_buffer_pool_size / _ram
Znaczenie: % pamięci RAM używanej dla puli buforów InnoDB
Zalecany zakres: 60 ~ 80%

Wyrażenie: Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests
Znaczenie: Żądania odczytu, które musiały trafić w dysk
Zalecany zakres: 0-2%
Co zrobić, gdy poza zasięgiem: Zwiększ innodb_buffer_pool_size, jeśli masz dość BARAN.

Wyrażenie: Innodb_pages_read / Innodb_buffer_pool_read_requests
Znaczenie: Żądania odczytu, które musiały trafić na dysk
Zalecany zakres: 0-2%
Co zrobić, gdy poza zasięgiem: Zwiększ innodb_buffer_pool_size, jeśli masz wystarczającą ilość pamięci RAM.

Wyrażenie: Innodb_pages_written / Innodb_buffer_pool_write_requests
Znaczenie: Napisz prośby, które musiały trafić na dysk
Zalecany zakres: 0-15%
Co zrobić, gdy poza zasięgiem: Sprawdź innodb_buffer_pool_size

Wyrażenie: Innodb_buffer_pool_reads / Uptime
Znaczenie: Odczyty
Zalecany zakres: 0-100 / s.
Co zrobić, gdy poza zasięgiem: Zwiększ innodb_buffer_pool_size?

Wyrażenie: (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) / Uptime
Znaczenie: InnoDB I / O
Zalecany zakres: 0-100 / s.
Co zrobić, gdy poza zasięgiem: Zwiększ innodb_buffer_pool_size?

Wyrażenie: Innodb_buffer_pool_pages_flushed / Uptime
Znaczenie: Zapisuje (spłukuje)
Zalecany zakres: 0-100 / s.
Co zrobić, gdy poza zasięgiem: Zwiększ innodb_buffer_pool_size?

Wyrażenie: Innodb_buffer_pool_wait_free / Uptime
Znaczenie: Licznik, gdy w puli buforowej nie ma wolnych stron. Oznacza to, że wszystkie strony są brudne.
Zalecany zakres: 0-1 / sek.
Co zrobić, gdy jest poza zakresem: Najpierw upewnij się, że innodb_buffer_pool_size jest ustawiony rozsądnie; jeśli nadal występują problemy, zmniejsz innodb_max_dirty_pages_pct

— Rick James
źródło

Dzięki @Rick za miły komentarz. Jaka innodb_buffer_pool_sizewartość określa? Rzeczywisty rozmiar lub skonfigurowany?

— joker

1

@joker - innodb_buffer_pool_sizewskazuje maksymalny rozmiar. Na typowym serwerze „pula buforów” zaczyna się od małych, ale szybko rośnie do tego maksymalnego rozmiaru i pozostaje tam. Uwaga: jeśli jest to więcej niż pamięć RAM (lub nawet blisko), prowadzi to do zamiany, co jest straszne z punktu widzenia wydajności.

— Rick James

7

W twoim tytule pytasz o innodb_buffer_pool_size, ale podejrzewam, że to nie jest prawdziwy problem. (Rolando skomentował, dlaczego ustawiłeś go wystarczająco duży, a nawet zbyt duży).

Ustawiłem maksymalną liczbę połączeń = 800, które czasami nasycają i grindują serwer, aby zatrzymać.

To jest niejasne. 800 użytkowników w trybie „uśpienia” ma praktycznie zerowy wpływ na system. 800 aktywnych wątków byłoby katastrofą. Ile wątków jest „uruchomionych”?

Czy wątki się blokują? Zobacz POKAŻ STATUS INNODB SILNIKA, aby uzyskać wskazówki dotyczące zakleszczeń itp.

Czy w slowlogu pojawiają się jakieś zapytania? Zoptymalizujmy je.

Jakiej wersji używasz? XtraDB (drop-in zamiennik dla InnoDB) lepiej radzi sobie z używaniem wielu rdzeni. 5.6.7 robi jeszcze lepszą robotę.

innodb_buffer_pool_instances - zmień to na 8 (zakładając, że basen buforowy 20G); zmniejszy się nieznacznie w związku ze sporem o Mutex.

Czy jesteś związany we / wy, czy jesteś związany z procesorem? Rozwiązania są diametralnie różne, w zależności od twojej odpowiedzi.

SSD - Może być lepiej, jeśli wszystkie pliki dziennika znajdują się na dyskach innych niż SSD.

— Rick James
źródło

6

Więcej pamięci jest zawsze lepsze, ale z mojego doświadczenia wynika, że większość czasu puli buforów nie powinna pasować do rozmiaru danych. Wiele tabel jest nieaktywnych przez większość czasu, np. Leżące wokół tabele kopii zapasowych, więc rozmiar puli buforów innodb powinien raczej pasować do rozmiaru danych aktywnych.

Określone ramy czasowe dla aktywnych stron wpływają na wydajność, ale istnieje optymalny punkt, w którym nie uzyska się takiej wydajności przy większym rozmiarze bufora. Możesz to oszacować / obliczyć / zmierzyć wedługshow engine innodb status

— użytkownik77376
źródło

Jak duży powinien być mysql innodb_buffer_pool_size?

CAVEAT # 1

CAVEAT # 2