Mam tabelę z około 1 miliardem wierszy i intensywnością odczytu w 98%.

Próbowałem dostrajać bazę danych za pomocą różnych silników pamięci masowej (MyISAM i InnoDB)

Następnie przeprowadziłem kilka testów, aby zobaczyć wydajność

W klauzuli where miałem identyfikator klucza podstawowego i wydawało się, że skoro MyISAM Key Cache przechowuje ładuje cały indeks do swojego bufora, używanie MyISAM wydawało się dość szybkie, około 2 razy szybsze niż InnoDB

Ale dla InnoDB wydawało się to wolniejsze !! Czy InnoDB nie używa żadnego bufora do wstępnego ładowania indeksów?

Zanim zdecydujesz się na MyISAM lub InnoDB, będziesz musiał przejrzeć oba silniki pamięci masowej pod kątem tego, co każda pamięć podręczna

MyISAM

Podczas odczytu indeksy tabeli MyISAM można odczytać jeden raz z pliku .MYI i załadować do pamięci podręcznej kluczy MyISAM (o rozmiarze według parametru rozmiar_buforu_kluczy ). Jak możesz szybciej czytać tabelę MyISAM .MYD? Z tym:

ALTER TABLE mytable ROW_FORMAT=Fixed;

Pisałem o tym w moich poprzednich postach

• Sep 20, 2011: /dba/5974/best-of-myisam-and-innodb/6008#6008 (Przeczytaj najpierw ten)
• May 10, 2011: /dba/2640/what-is-the-performance-impact-of-using-char-vs-varchar-on-a-fixed-size-field/2643#2643 (TRADEOFF # 2)
• Aug 12, 2011: /dba/4576/which-dbms-is-good-for-super-fast-reads-and-a-simple-data-structure/4589#4589 (ust. 3)
• Jan 03, 2012: /dba/10069/optimized-my-cnf-for-high-end-and-busy-server/10080#10080 (pod nagłówkiem Replikacja )

InnoDB

OK, a co z InnoDB? Czy InnoDB wykonuje jakieś dyskowe operacje wejścia / wyjścia dla zapytań? Zaskakujące, że tak !! Prawdopodobnie myślisz, że jestem szalony, że to mówię, ale to absolutna prawda, nawet w przypadku zapytań SELECT . W tym momencie zapewne zastanawiasz się: „Jak, na świecie, InnoDB robi dyskowe operacje we / wy dla zapytań?”

Wszystko to wraca do tego, że InnoDB jest silnikiem transakcyjnym do przechowywania danych na podstawie ACID . Aby InnoDB, aby być transakcyjnych, musi wspierać Iin ACID, który jest izolacja. Technika utrzymywania izolacji transakcji odbywa się za pośrednictwem MVCC, Multiversion Concurrency Control . Mówiąc najprościej, InnoDB rejestruje wygląd danych przed próbą ich zmiany przez transakcje. Gdzie to się rejestruje? W systemowym pliku obszaru tabel, lepiej znanym jako ibdata1. To wymaga dysku I / O .

PORÓWNANIE

Ponieważ zarówno InnoDB, jak i MyISAM robią dyskowe operacje we / wy, jakie losowe czynniki decydują o tym, kto jest szybszy?

• Rozmiar kolumn
• Format kolumny
• Zestawy znaków
• Zakres wartości liczbowych (wymagające wystarczająco dużych liczb całkowitych)
• Rzędy dzielone na bloki (łączenie rzędów)
• Fragmentacja danych spowodowana przez DELETEsiUPDATEs
• Rozmiar klucza podstawowego (InnoDB ma indeks klastrowany, wymagający dwóch odnośników)
• Rozmiar wpisów indeksu
• i tak dalej...

EPILOG

W związku z tym w środowisku intensywnego odczytu istnieje możliwość, że tabela MyISAM ze stałym formatem wierszy przewyższa odczyt InnoDB z puli buforów InnoDB, jeśli w dziennikach cofania zawartych w ibdata1 zapisywana jest wystarczająca ilość danych do obsługi zachowania transakcyjnego nałożone na dane InnoDB. Zaplanuj dokładnie typy danych, zapytania i silnik pamięci masowej. Gdy dane rosną, przenoszenie danych może być bardzo trudne.

Nawiasem mówiąc, napisałem coś takiego 5 dni temu: Jak przypisać limit pamięci dla mySQL?

MyISAM zawsze będzie działał dużo szybciej niż innodb, gdy nie będzie rywalizacji o dane. Zacznij dodawać wiele sesji, próbując zaktualizować tę samą tabelę, a innodb bardzo szybko uzyska przewagę wydajności.

Sposób dostrojenia systemu dla 2 silników jest bardzo różny.

Istnieją różne silniki, ponieważ istnieją różne obciążenia / wzorce dostępu.

musisz „rozgrzać” innodb. np. przez ponowne odtworzenie dzienników dostępu lub uruchomienie inteligentnych zapytań, które będą dotykać każdej wartości z indeksu.

spójrz tutaj lub tutaj .

mam nadzieję, że nie używasz domyślnych ustawień mysql dla innodb - były one odpowiednie dla sprzętu od ~ 2000.

Sprawdź tę stronę, ma bardzo przydatne informacje:

http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/

http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/

Możesz także dostroić system plików. Mam dobre wyniki wydajności na XFS z optymalnymi wartościami sunit i swidth (oczywiście jeśli używasz RAID)

Po dalszym dostrajaniu InnoDB na MariaDB zwiększyłem innodb_buffer_pool_sizerozmiar bazy danych InnoDB, ponieważ robiąc to, InnoDB zaczął szybciej pobierać wiersze

Podejrzewam, że strojenie InnoDB jest dość ważne w zależności od potrzeb twojej bazy danych