Wydajność baz danych

Share Niniejszy artykuł zawiera różne wskazówki i rady jak zwiększyć wydajność baz danych (zazwyczaj tekst odnosi się do MySQL lub PostgreSQL).

MySQL

Ciekawy artykuł z www.netcoffee.pl: Optymalizacja bazy MySQL - część II
W skrócie: używaj pól o jak najmniejszym rozmiarze, deklaruj kolumny jako NOT NULL, dla tabel MyISAM używaj rekordu o stałej długości, podstawowy indeks tabeli powinien być tak mały, jak to możliwe, twórz indeksy których naprawdę potrzebujesz, pierwsza kolumna złożonego klucza powinna być kolumną najczęściej używaną, nie używaj znaków wieloznacznych na początku porównania LIKE, używaj pól typu ENUM jeżeli to możliwe.

Tabele InnoDB

• Długi PRIMARY KEY zajmuje sporo miejsca, zaleca się ustawianiem kolumny AUTO_INCREMENT jako PRIMARY KEY
• Maksymalne rozmiary logów powinny być dość duże. Operowanie na wielu małych plikach to zbędne operacje I/O
• Używaj VARCHAR a nie CHAR jeżeli kolumna zawiera łańcuchy o zmiennej długości lub dużą ilość pól o wartości NULL
• Jeżeli dodajesz dużo danych po kolei wyłącz AUTO COMMIT (SET AUTOCOMMIT=0;) jako że każdy INSERT powodowałby "czyszczenie" logów
• Duże ROLLBACKi są znacznie wolniejsze niż INSERTy
• Wystrzegaj się operacji wymagających dużej ilości operacji na dysku. Używaj DROP TABLE a nie DELETE FROM
• Używaj składnię wielowierszowego INSERTa do dodania wielu wierszy (INSERT INTO tabela VALUES (1,2), (5,5), ...;)
• Jeżeli tabele mają UNIQUE lub FOREIGN KEY na kolumna z SECONDARY KEY to przy imporcie danych warto wyłączyć sprawdzanie obu tych "warunków" (SET UNIQUE_CHECKS=0; ..import.. SET UNIQUE_CHECKS=1;) (SET FOREIGN_KEY_CHECKS=0;... import ...SET FOREIGN_KEY_CHECKS=1;)
• Na niektórych unixach/Linuksach operacje wykonywane z pomocą wywołania fsync() mogą być bardzo wolne. Ustawienie innodb_flush_method na O_DSYNC może przynieść niespodziewaną zmianę wydajności.

PostgreSQL

Wydajność postgresa zależy od wielu czynników, jednym z podstawowych jest konfiguracja. Kilka opcji ustawień możne znacząco wpłynąć na osiągi tej bazy danych:

• max_connections = (num) - określa maksymalną ilość jednoczesnych połączeń. Zbyt wysoka wartość może doprowadzić do zajęcia całego RAMu i wejścia bazy w SWAP co spowolni cały system.
• shared_buffers = (num) - Domyślna wartość to 1000 i uważana jest na za niską jak na współczesny sprzęt. Podana wartość powinna odpowiadać 10-15% posiadanego RAMu (zazwyczaj około 10 000 - 20 000)
• work_mem = (num) - Opcja kontrolująca ilość zasobów pamięci używanych dla sortowań i haszowych tabel. Jeżeli wykonujesz dużo takich operacji możesz zwiększyć limit pamięci ale zwróć uwagę że limit ten dotyczy każdego zapytania (a kilka zapytań wykonywanych przez naszą aplikację + możliwość wykonywania takich zapytań równocześnie może szybko wyczerpać zapasy RAMu)
• max_fsm_pages = (num) - Ta opcja pomaga kontrolować mapę wolnego miejsca (free space map). Gdy coś jest kasowane z tabeli to nie jest od razu usuwane z dysku. Po prostu jest oznaczane jako "wolne" w mapie wolnego miejsca. To miejsce może być użyte przez nowe INSERTy dotyczące danej tabeli. Jeżeli wykonujesz dużo operacji DELETE i INSERT z tabeli/tabel będziesz musiał zwiększyć tą wartość w celu uniknięcia śmieci w tabeli.
• effective_cache_size = (num) - wartość pomaga podjąć decyzję bazie danych czy użyć indeksu czy nie. Im wyższa wartość tym większe szanse na użycie indeksu. Parametr ten określa wydajność keszu dysku używanego systemu operacyjnego.

• Procesor - im lepszy procesor tym lepiej, ale jeżeli nie wykonujesz w bazie złożonych funkcji to lepiej zainwestować w lepszy dysk. Nie zaleca się procesorów Intel Xeon jako że są z nimi problemy, które obniżają wydajność. Chwalone są za to Opterony
• RAM - im więcej RAMu tym więcej keszu dysku. Operacje I/O w RAMie są tysiąc razy szybsze niż takowe na dysku
• Dysk - polecane są dyski SCSI Ultra-320, lecz wysokiej klasy dyski SATA też są bardzo dobre.
• Konfiguracja Dysku - optymalna konfiguracja to RAID 1+0 z maksymalnie dużą ilością dysków i z logiem transakcji(pg_xlog) na oddzielnym dysku. RAID 5 nie jest zbyt dobrym rozwiązaniem, chyba że masz więcej niż 6 dysków w woluminie. W nowszych wersjach postgresa można rozmieszczać tabele (i nie tylko) na różnych dyskach/partycjach. Często używane tabele/dane powinny znajdować się na najlepszym dysku.
• Oprócz tego warto trzymać bazę na oddzielnej maszynie nie zajętej przy okazji np. serwerem www.

Zobacz także PostgreSQL 8.0 Performance Checklist