Tuning baz danych MariaDB

Program szkolenia

Wprowadzenie do optymalizacji MariaDB

• Cele optymalizacji MariaDB.
• Znaczenie konfiguracji systemu operacyjnego dla wydajności bazy danych.

Konfiguracja systemu operacyjnego Linux

• CPU:
  • Ustawienia CPU Governor.
  • Zarządzanie NUMA (Non-Uniform Memory Access).
  • cpulimit, taskset, cgroups
• RAM:
  • Zarządzanie rozmiarem i dostępnością pamięci RAM.
  • Konfiguracja Huge Pages.
• Sieć:
  • Konfiguracja parametrów sieciowych dla optymalizacji połączeń z bazą danych.
  • Ustawienia protokołów i zarządzanie opóźnieniami.
• Storage i SWAP:
  • Zarządzanie przestrzenią SWAP i ustawienia dla bazy danych.
  • Konfiguracja storage"u, w tym ustawienia dla dysków SSD i HDD.
• Limity OS:
  • Ustawienia limitów systemowych dla procesów bazy danych.
  • Konfiguracja limitów otwartych plików i pamięci.

Narzędzia monitorowania zasobów systemowych

• top, nmon, pidstat: Monitorowanie zużycia CPU i pamięci.
• iotop,iostat : Monitorowanie operacji I/O.
• runqlat: Monitorowanie czasów oczekiwania w kolejce CPU.
• ps, strace, perf : Monitorowanie procesów
• PMM (Percona Monitoring and Management):
  • Instalacja i konfiguracja PMM.
  • Monitoring zasobów systemowych oraz bazodanowych.
  • Analiza metryk wydajnościowych w PMM.

MariaDB Memory Allocator

• Porównanie różnych alokatorów pamięci: porównanie domyślnego alokatora z alternatywami.
• Wybór optymalnego alokatora: kryteria wyboru alokatora w zależności od typu obciążeń i wymagań wydajnościowych.

Zarządzanie operacjami I/O na dysku

• flush, capacity, concurrency, threads
• Doublewrite: Konfiguracja zależna od systemu plików.
• Zapisy sąsiadujących stron
• innodb purge lag
• random read ahead: Strategie dla odczytu losowego i wyprzedzającego.

Profilowanie i zarządzanie użyciem pamięci

• Globalnie: Analiza zużycia pamięci przez całą bazę danych.
• Dla schematów: Monitorowanie zużycia pamięci przez wybrane schematy.

Zarządzanie wątkami

• spin wait: Zarządzanie czasem oczekiwania na zasoby.
• thread concurrency: Optymalizacja współbieżności wątków.

Zarządzanie buforami globalnymi

• AHI (Adaptive Hash Index): Dynamiczne indeksy haszujące, konfiguracja i optymalizacja użycia.
• Pamięć Tymczasowa: Parametry rozmiaru, użycie przy sortowaniu i JOIN, unikanie przepełnienia.
• Query Cache: Przechowywanie wyników zapytań, konfiguracja rozmiaru i strategia czyszczenia.
• Old Blocks: Zarządzanie starszymi blokami w buforze.
• Rozmiar Strony: Optymalizacja rozmiaru stron w buforze dla lepszej wydajności.
• Sort Bufor: Ustawienia sort_buffer_size, optymalizacja sortowania.
• Ładowanie Buffer Pool: Zarządzanie buforem przy uruchamianiu serwera.

Konfiguracja buforów i limitów sesji

• Limity: Ustawienia limitów zasobów dla sesji.
• Timeouty: Zarządzanie czasami oczekiwania.
• Bufory sesji: Konfiguracja buforów dla efektywnego przetwarzania zapytań.

Monitorowanie zapytań i analiza wydajności

• Performance Schema: Konfiguracja i monitorowanie wydajności bazy danych.
• Slow Query Log:
  • Konfiguracja Slow Query Log.
  • Analiza wolnych zapytań przy użyciu pt-query-digest.

Optymalizacja zapytań i indeksów

• Analiza zapytań: EXPLAIN, ANALYZE
• Statystyki Zapytań
  • Konsumujące Sumarycznie Najwięcej Czasu
  • Full Scany
  • Użycie Tabel Tymczasowych
  • Sortowania
• Profilowanie zapytań : czas wykonania zapytania z podziałem na etapy procesu
• Optimizer Trace: analiza działania optymalizatora zapytań.
• Indeksy:
  • Indeks niejawny
  • Unique PK: Wybór i zarządzanie kluczami głównymi.
  • Zduplikowane indeksy: Usuwanie niepotrzebnych, zduplikowanych indeksów.
  • Kolumny generowane: Wykorzystanie kolumn generowanych dla indeksów.
  • Nieużywane indeksy: Identyfikacja i usuwanie nieużywanych indeksów.
• Histogramy:
  • Typy i rozmiary histogramów.
  • Dekodowanie histogramów dla lepszej analizy danych.
• Statystyki:
  • ANALYZE i ANALYZE PERSISTENT FOR: Aktualizacja statystyk tabel.

Optymalizacja schematu

• Partycjonowanie: Techniki partycjonowania tabel dla zwiększenia wydajności oraz wybór strategii partycjonowania (np. RANGE, LIST, HASH).
• Partycjonowanie rozproszone (Engine Connect): Konfiguracja i zalety partycjonowania rozproszonego, optymalizacja zapytań oraz zarządzanie dużymi zbiorami danych.
• Engine Memory: Konfiguracja silnika pamięci dla szybkiego dostępu do danych oraz optymalizacja zapytań w pamięci.

Zaawansowane techniki optymalizacji zapytań

• STRAIGHT_JOIN: Wymuszanie kolejności łączenia tabel.
• Windows Functions, JSON, CTE: Zaawansowane techniki zapytań.
• Poziomy izolacji: Wybór odpowiedniego poziomu izolacji dla przypadku.
• optimizer_switch, optimizer cost, optimizer condition selectivity:
  • Konfiguracja opcji optymalizatora dla specyficznych scenariuszy.

Zarządzanie blokadami i transakcjami

• Statystyki InnoDB: Monitorowanie blokad i transakcji.
• Analiza blokad:
  • DATA_LOCKS i INNODB_LOCK_WAITS.
  • Monitorowanie deadlocków za pomocą pt-deadlock-logger.
• Priorytety operacji zapisu.

Optymalizacja procesu importu i modyfikacji danych

• Optymalizacja importu danych: Przyspieszenie importu danych poprzez zarządzanie pamięcią i brudnymi stronami.

Redo log i Bin log

• Dostosowanie rozmiaru redo logów i konfiguracja logów binarnych.

Optymalizacja zmian schematu

• Online DDL
• Monitoring Lock.
• Śledzenie postępu

Narzędzia Mysqltuner i Sysbench

• Analiza wyników Mysqltuner: Interpretacja wyników i wdrażanie rekomendacji.
• Testy wydajności Sysbench