Badanie wydajności insertów MariaDB

Problemem, który omówimy tym razem, jest optymalizacja szybkości zapisu (INSERT) do bazy danych MariaDB przy spełnieniu kilku warunków. Zakładamy, że nasza tabela posiada zdefiniowane klucze główne oraz klucze obce (a więc i indeksy). Wiemy, że dane, które do niej zapisujemy, są poprawne i w czasie wykonywania testów żaden inny proces nie zapisuje danych do tej samej tabeli.

Celem tego artykułu jest zbudowanie ilościowego wyczucia wydajnościowego oraz przetestowanie różnych technik masowego wstawiania danych do bazy relacyjnej.

Struktura bazy danych

Interesuje nas przerzucanie danych między dwiema identycznymi tabelami (main_1 oraz main_2), które posiadają powiązania przez więzy integralności referencyjnej z $N$ mniejszymi tabelami pomocniczymi (minor_1, minor_2, …, minor_n).

Nasza baza przypomina więc “pajęczynę”, w której dwie duże tabele odnoszą się $N$ kluczami obcymi do $N$ niepowiązanych ze sobą tabel pomocniczych.

Strukturę bazy przedstawia poniższy diagram hierarchiczny:

Dla lepszego obrazu zależności prezentujemy również diagram organiczny tej samej bazy:

Duże tabele (main_1, main_2) przy dużej liczbie kluczy obcych i indeksów napotykają istotne narzuty wydajnościowe podczas operacji masowego zapisu.

Tabele pomocnicze (minor_x) zawierają jedynie pole id będące ich kluczem głównym. Po ich utworzeniu i wypełnieniu tworzone są tabele główne, do których dodawane są klucze obce odnoszące się do tabel minor.

Instalacja i uruchomienie

Środowisko testowe zostało przygotowane w postaci gotowej aplikacji. Aby je uruchomić, sklonuj repozytorium:

git clone https://github.com/gustawdaniel/test_inserts_performance.git
cd test_inserts_performance

Przed uruchomieniem instalacji utwórz plik konfiguracyjny z szablonu:

cp config/parameters.yml.dist config/parameters.yml

Następnie otwórz plik config/parameters.yml i ustaw odpowiednie parametry połączenia z bazą danych (użytkownik, hasło, nazwa bazy). W naszym przykładzie użytkownikiem jest daniel — nazwę swojego użytkownika w systemie możesz sprawdzić, wpisując w terminalu:

echo $USER

Nadanie uprawnień w MariaDB

Jeśli potrzebujesz utworzyć użytkownika bazy danych i nadać mu uprawnienia, zaloguj się raz przez sudo mariadb:

sudo mariadb

I w konsoli MariaDB wykonaj polecenia (zastępując 'daniel' swoją nazwą użytkownika uzyskaną z echo $USER):

CREATE USER IF NOT EXISTS 'daniel'@'localhost' IDENTIFIED BY '';
GRANT ALL PRIVILEGES ON *.* TO 'daniel'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Gdy baza i uprawnienia są skonfigurowane, wykonaj skrypt instalacyjny:

bash install.sh

Po pomyślnym wykonaniu instalacji utworzony schemat bazy danych powinien wyglądać następująco:

Monitorowanie obciążenia dysku (iotop)

Podczas wykonywania intensywnych testów zapisu warto monitorować obciążenie dyskowe I/O. Narzędzie iotop pozwala na bieżąco obserwować operacje zapisu i odczytu generowane przez procesy bazy danych oraz wątki systemowe:

Architektura aplikacji testowej (Symfony + Doctrine DBAL)

Zamiast pisać powtarzalne skrypty SQL, do elastycznego generowania struktury bazy oraz uruchamiania pomiarów użyliśmy frameworka Symfony oraz komponentu Doctrine DBAL. Pozwala to na dynamiczne budowanie dowolnej liczby tabel i kluczy obcych bez kopiowania kodu.

Dynamiczna generacja schematu (SchemaGenerator)

Za budowanie schematu bazy danych w aplikacji odpowiada klasa SchemaGenerator:

<?php

namespace AppBundle\Model;

use Doctrine\DBAL\Schema\Schema;

class SchemaGenerator
{
    private $main = [];
    private $minor = [];
    private $N;

    public function __construct($n = 10)
    {
        $this->N = $n;
    }

    private function appendMinorToSchema(Schema $schema): void
    {
        for ($i = 1; $i <= $this->N; $i++) {
            $this->minor[$i] = $schema->createTable("minor_" . $i);
            $this->minor[$i]->addColumn("id", "integer");
            $this->minor[$i]->setPrimaryKey(["id"]);
        }
    }

    private function appendMainToSchema(Schema $schema): void
    {
        for ($i = 1; $i <= 2; $i++) {
            $this->main[$i] = $schema->createTable("main_" . $i);
            $this->main[$i]->addColumn("id", "integer");
            for ($j = 1; $j <= $this->N; $j++) {
                $this->main[$i]->addColumn("minor_" . $j . "_id", "integer");
                $this->main[$i]->addForeignKeyConstraint(
                    $this->minor[$j],
                    ["minor_" . $j . "_id"],
                    ["id"]
                );
            }
            $this->main[$i]->setPrimaryKey(["id"]);
        }
    }

    private function appendLogToSchema(Schema $schema): void
    {
        $log = $schema->createTable("log");
        $log->addColumn("id", "integer", ["autoincrement" => true, "unsigned" => true]);
        $log->addColumn("n", "smallint", ["unsigned" => true]);
        $log->addColumn("l", "smallint", ["unsigned" => true]);
        $log->addColumn("k0", "integer", ["unsigned" => true]);
        $log->addColumn("k", "integer", ["unsigned" => true]);
        $log->addColumn("execution_time", "float");
        $log->addColumn("operation", "string");
        $log->setPrimaryKey(["id"]);
    }

    public function generate(): Schema
    {
        $schema = new Schema();
        $this->appendMinorToSchema($schema);
        $this->appendMainToSchema($schema);
        $this->appendLogToSchema($schema);
        return $schema;
    }
}

Kontroler przygotowania struktury i danych

Kontroler PreparationController umożliwia generowanie schematu oraz wypełnianie tabel przez REST API HTTP.

Przykładowa akcja wstawiania danych do tabeli main:

/**
 * @Route("/main/{n}/{l}/{k0}/{k}/{main}/{transaction}")
 */
public function mainAction($n = 10, $l = 10, $k0 = 1, $k = 1000, $main = 1, $transaction = true)
{
    $conn = $this->getDoctrine()->getConnection();
    if ($k0 == 1) {
        $conn->delete('main_' . $main, [1 => 1]);
    }

    if ($k > 10000) {
        set_time_limit(0);
        ini_set("max_execution_time", "0");
    }

    if ($transaction) {
        $conn->beginTransaction();
    }

    try {
        for ($i = $k0; $i <= $k; $i++) {
            $content = ['id' => $i];
            for ($j = 1; $j <= $n; $j++) {
                $content['minor_' . $j . '_id'] = rand(1, $l);
            }
            $conn->insert('main_' . $main, $content);
        }
        if ($transaction) {
            $conn->commit();
        }
    } catch (\Exception $e) {
        if ($transaction) {
            $conn->rollBack();
        }
        throw $e;
    }

    return new JsonResponse(['n' => $n, 'l' => $l, 'k0' => $k0, 'k' => $k]);
}

Porównanie strategii zapisu

1. Pojedyncze instrukcje INSERT vs Transakcje

Pierwszą kluczową obserwacją jest drastyczny wpływ transakcji na czas zapisu przy pojedynczych operacjach INSERT. Testy przeprowadzone dla 100 000 wierszy pokazują różnicę:

Różnica wynika z faktu, że przy braku jawnej transakcji baza MariaDB dokonuje wymuszonego zapisu na dysk (fsync) przy każdym pojedynczym wierszu.

2. Kopiowanie czystym SQL oraz mariadb-dump

Gdy dane istnieją już w tabeli main_1, szybkim sposobem na skopiowanie ich do main_2 jest bezpośrednie zapytanie SQL:

INSERT INTO main_2 SELECT * FROM main_1;
-- Query OK, 1 000 000 rows affected (8 min 38.35 sec)

Z kolei przy użyciu narzędzia mariadb-dump można wyeksportować dane do pliku i zmienić nazwę tabeli za pomocą awk:

# Eksport zrzutu
mariadb-dump -u root training main_1 > main_1.sql

# Zmiana tabeli docelowej w locie z main_1 na main_2
awk '/^INSERT/ {sub("main_1","main_2",$0); print $0;}' main_1.sql > main_2.sql

# Import do bazy
mariadb -u root training < main_2.sql

Plan pomiarowy, hipotezy badawcze i wypracowanie intuicji

Aby dogłębnie zrozumieć, dlaczego operacja INSERT w relacyjnej bazie danych MariaDB działa wolno lub szybko w określonych warunkach, musimy poznać mechanizmy wewnętrzne silnika InnoDB. Zdefiniowaliśmy cztery kluczowe hipotezy badawcze oparte o następujące zmienne:

• $N$ – liczba tabel pomocniczych minor (od 1 do 50+), czyli liczba zdefiniowanych więzów integralności (FOREIGN KEY) w tabeli docelowej.
• $L$ – kardynalność słownika (liczba rekordów w tabelach minor_x, od 10 do 50 000), decydująca o rozproszeniu kluczy obcych.
• $K$ – rozmiar paczki wstawianych wierszy (od 1 do 25 000 wierszy w jednym zapytaniu INSERT).
• Weryfikacja kluczy obcych (FOREIGN_KEY_CHECKS) – flaga silnika określająca, czy podczas zapisu sprawdzane są indeksy w tabelach nadrzędnych (0 = wyłączona, 1 = włączona).

Dlaczego badamy poszczególne parametry? (Budowanie intuicji)

1. Wpływ liczby kluczy obcych ($N$)

• Mechanizm bazy: Każdy zapis wiersza do tabeli z $N$ kluczami obcymi wymaga od silnika InnoDB wykonania $N$ dodatkowych operacji wyszukiwania w B-drzewie indekscu głównego odpowiednich tabel minor_1 .. minor_N. Ponadto InnoDB nakłada blokady dzielone (Shared Locks) na sprawdzane rekordy nadrzędne.
• Hipoteza H1: Czas wykonywania operacji INSERT rośnie liniowo wraz z liczbą $N$. Każdy kolejny klucz obcy nakłada stały narzut weryfikacyjny.

2. Wpływ rozmiaru paczki w transakcji ($K$)

• Mechanizm bazy: Domyślnie w trybie autocommit=1 MariaDB po każdym pojedynczym zapytaniu wymusza zrzut dziennika wywołań na dysk (fsync), co przy dyskach talerzowych lub tradycyjnych SSD stanowi potężne wąskie gardło (operacja I/O). Łączenie wierszy w wielowierszowe zapytanie INSERT INTO ... VALUES (...), (...)... i otoczenie transakcją wykonuje dyskowy fsync tylko raz na całą paczkę.
• Hipoteza H2: Przepustowość zapisu (wierszy na sekundę) drastycznie rośnie wraz ze rozmiarem paczki $K$ do momentu osiągnięcia “punktu słodkiego” (sweet spot, $K \approx 1\ 000 - 10\ 000$), po czym przyrost ulega spłaszczeniu ze względu na narzut alokacji pamięci buffer pool i przetwarzania tekstu zapytania.

3. Wyłączenie sprawdzania więzów (FOREIGN_KEY_CHECKS=0)

• Mechanizm bazy: Gdy dane pochodzą z zaufanego źródła (np. migracja produkcyjna, odtwarzanie zrzutu, operacje ETL), ponowne sprawdzanie każdego klucza obcego przy kopiowaniu z tabeli do tabeli (INSERT INTO ... SELECT) generuje zbędny narzut CPU i odczytów.
• Hipoteza H3: Tymczasowe ustawienie SET FOREIGN_KEY_CHECKS=0 eliminuje sprawdzanie B-drzew w tabelach pomocniczych, dając kilkukrotne przyspieszenie zapisu przy dużym $N$.

4. Kardynalność słowników ($L$)

• Mechanizm bazy: Mała liczba unikalnych wartości klucza obcego ($L=10$) powoduje, że strony indeksów tabel słownikowych są niemal zawsze gorące i przebywają w pamięci podręcznej InnoDB Buffer Pool. W przypadku dużej kardynalności ($L=50\ 000$) odwołania do B-drzew są rozproszone, co w przypadku niedoboru RAM prowadzi do częstych odczytów z dysku (page faults).
• Hipoteza H4: Wysoka kardynalność $L$ wydłuża czas zapisu ze względu na spadek wskaźnika trafień (cache hit ratio) w pamięci buforowej InnoDB.

Polecenia do uruchomienia testów i generowania wyników

Cały zestaw pomiarowy jest w pełni zautomatyzowany. Aby samodzielnie przeprowadzić pomiary na swojej maszynie i wygenerować wykresy oraz tabelki:

1. Inicjalizacja bazy danych i zebranie metryk sprzętowych

W pierwszej kolejności uruchamiamy skrypt inicjalizujący schemat i mierzący opóźnienia oraz wydajność dysku/CPU maszyny:

# Instalacja zależności (w przypadku systemu opartego o Arch/Manjaro)
bash install.sh

# Utworzenie bazy i zebranie parametrów sprzętowych do tabeli `machine`
bash bash/initialize.sh

2. Wykonanie serii benchmarków

Przygotowany skrypt CLI bin/run_benchmarks.php wykonuje scenariusze pomiarowe dla różnych wariantów $N$, $L$ oraz $K$, zapisując czas wykonania z precyzją mikrosekundową w tabeli log:

# Szybka seria testowa (do szybkiej weryfikacji)
php bin/run_benchmarks.php --quick

# Pełna seria pomiarowa (zalecana dla pełnych danych)
php bin/run_benchmarks.php

Możesz również uruchomić tylko wybrany scenariusz:

php bin/run_benchmarks.php --scenario=fk_count
php bin/run_benchmarks.php --scenario=batch_size
php bin/run_benchmarks.php --scenario=cardinality

3. Analiza wyników w Pythonie i wygenerowanie wykresów

Po ukończeniu pomiarów uruchom skrypt w Pythonie, który pobierze dane z bazy MariaDB, obliczy przepustowość (rekordy/sek), wygeneruje zestawienia oraz zapisze estetyczne wykresy w katalogu public/img/:

python3 python/analyze_and_plot.py

Wyniki pomiarów i analiza wydajnościowa

Wykonana seria pomiarowa na lokalnym środowisku testowym przyniosła następujące rezultaty:

1. Skalowanie z liczbą kluczy obcych ($N$)

Poniższy wykres prezentuje czas wykonania operacji zapisu w zależności od liczby kluczy obcych zdefiniowanych w tabeli głównej:

Weryfikacja hipotezy H1 (Liniowość narzutu $N$):

1. Potwierdzenie liniowości $\mathcal{O}(N)$: Wyniki pomiarów empirycznych w pełni potwierdzają Hipotezę H1. Czas operacji zapisu rośnie w sposób liniowy wraz ze wzrostem liczby kluczy obcych $N$. Dla paczki $K=5\ 000$ wierszy każdy dodatkowy klucz obcy generuje stały narzut weryfikacji w B-drzewach słownika na poziomie ok. 0.005 s (dla $N=1$ czas weryfikacyjny wynosi 0.0556 s, a dla $N=50$ rośnie proporcjonalnie do 0.3113 s).
2. Stała relatywna różnica wydajności: Mimo że czas bezwzględny rośnie liniowo, krotność narzutu sprawdzania kluczy obcych w stosunku do zapisu bez weryfikacji (FOREIGN_KEY_CHECKS=0) utrzymuje się na stabilnym poziomie ~1.5x – 1.7x w całym zakresie $N=1 \dots 50$. Wynika to z proporcjonalnego wzrostu alokacji pamięci dla samych kolumn $N$ przy operacji kopiowania.

Dlaczego wzrost czasu dla małych $N$ ($1 \dots 5$) jest szybszy niż dla dużych $N$?

Analizując nachylenie wykresu (pochodną cząstkową $\frac{\Delta t}{\Delta N}$), można zaobserwować charakterystyczny efekt: dla pierwszych kilku kluczy ($N=1 \dots 5$) czas zapisu rośnie znacznie szybciej (~0.037 s na każdy dodatkowy FK), podczas gdy dla $N \ge 5$ linia ulega zauważalnemu spłaszczeniu (~0.003 s na kolejny FK).

Efekt ten wynika bezpośrednio z niskopoziomowej architektury silnika InnoDB i procesora:

1. Inicjalizacja uchwytów tabel w podsystemie InnoDB (Table Share Allocation): Dodanie pierwszych kilku kluczy obcych wymusza alokację w pamięci operacyjnej nowych obiektów metadanych (dict_table_t, table share) dla każdej tabeli słownikowej oraz zbudowanie wewnętrznego parsera więzów w obiekcie transakcji. Narzut inicjalizacji obiektów dla $N=1 \dots 5$ jest jednorazowym “kosztem wejścia”.
2. Rozgrzewanie pamięci buforowej (InnoDB Buffer Pool Warming): Przy małej liczbie kluczy pierwsze operacje sprawdzania trafiają na niezaalokowane lub zimne strony indeksowe. Dla $N \ge 5$ strony B-drzew wszystkich tabel słownikowych ($L=1000$) stają się w 100% gorące i przebywają na stałe w buforze RAM procesora, co eliminuje opóźnienia alokacji.
3. Optymalizacja instrukcji CPU (Branch Prediction & Instruction Cache): Pętla weryfikacji kluczy w kodzie źródłowym MariaDB (row_ins_check_foreign_constraints) przy większym $N$ wpada w optymalną ścieżkę predykcji skoków (Branch Prediction Hit) oraz pozostaje w pamięci podręcznej instrukcji CPU L1/L2 Cache, co drastycznie obniża jednostkowy czas weryfikacji dla każdego kolejnego klucza.

2. Wyznaczanie optymalnego rozmiaru paczki ($K$)

Wpływ wielkości pojedynczego zapytania masowego INSERT na przepustowość (liczba dodanych rekordów na sekundę):

Weryfikacja hipotezy H2, dwa obszary „Sweet Spot” i odkrycie „Doliny Alokacyjnej”:

1. Weryfikacja hipotezy bazowej H2: Pomiary empiryczne w pełnym zakresie $K = 1 \dots 300\ 000$ potwierdzają Hipotezę H2. Przejście z pojedynczych zapytań $K=1$ (1 385 rec/s) do wielowierszowego zapisu masowego generuje ponad 53-krotny wzrost przepustowości (osiągając ~74 000 rec/s), eliminując narzut transakcyjnego zrzutu na dysk (fsync) oraz nagłówków protokołu bazy danych.
2. Niespodziewany profil wydajnościowy: dwa szczyty i „Dolina Alokacyjna”: Wbrew podręcznikowemu założeniu, że krzywa przepustowości rośnie monotonicznie i gładko się spłaszcza, gęsta siatka 45 punktów pomiarowych ujawniła złożony, nieliniowy profil:
   • 🟢 Pierwszy szczyt (Initial Sweet Spot, $K = 250 \dots 1\ 000$): Przepustowość błyskawicznie szybuje do ~74k rec/s. W tym przedziale zapytanie SQL ma wielkość poniżej kilkudziesięciu kilobajtów, co pozwala na bezproblemowe mieszczenie się w podręcznych buforach pamięci RAM zarówno po stronie PHP, jak i MariaDB.
   • ⚠️ Dolina Alokacyjna ($K = 2\ 000 \dots 3\ 500$): Przy przejściu przez próg $K \approx 2\ 250$ następuje gwałtowny, powtarzalny spadek wydajności do ~44k rec/s.
     • Alokator PHP (Zend Memory Manager): Przy wielkości zapytania SQL przekraczającej 256 KB alokator PHP przełącza się z szybkich puli pamięci (small allocations) na alokację dużych bloków systemowych (large block allocation), co wymusza dodatkowy narzut zarządzania pamięcią i Garbage Collectora.
     • Bufor odbiorczy MariaDB (net_buffer_length): Pakiet SQL przekracza domyślny bufor protokołu MariaDB (16 KB), zmuszając serwer do dynamicznego rozszerzania bufora sieciowego parsera przed przetworzeniem zapytania.
   • 🟢 Globalny szczyt (Global Sweet Spot, $K = 6\ 000 \dots 40\ 000$): Po przekroczeniu $K=3\ 500$ zysk z drastycznego ograniczenia liczby operacji sieciowych zaczyna zdecydowanie dominować nad kosztem alokacji. Przepustowość powraca na poziom ~70k – 72k rec/s i utrzymuje się stabilnie aż do 40 000 wierszy w jednej paczce.
3. Co dzieje się przy potężnych paczkach ($K = 50\ 000 \dots 300\ 000$)?: Dla $K = 300\ 000$ wierszy pojedyncze zapytanie SQL osiąga wielkość 11.7 MB tekstu!
   • Przepustowość nieznacznie spada z ~72k do ~60k rec/s z powodu czasu potrzebnego PHP na alokację i sklejanie wielomegabitowych ciągów znaków.
   • Zbliżamy się wówczas do krytycznego limitu serwera max_allowed_packet (domyślnie 16 MB w MariaDB), którego przekroczenie skutkuje błędem Packet too large.
   • Praktyczny wniosek inżynieryjny: Optymalnym rozmiarem paczki w aplikacjach produkcyjnych jest przedział $K = 1\ 000 \dots 10\ 000$ wierszy – zapewnia on maksymalną przepustowość przy minimalnym ryzyku przekroczenia limitów pamięci i pakietu SQL.

3. Zysk z tymczasowego wyłączenia sprawdzania kluczy obcych (FOREIGN_KEY_CHECKS=0)

Poniższy wykres prezentuje krotność przyspieszenia (FK Enabled / FK Disabled) w zależności od liczby kluczy obcych ($N$):

Analiza trendu i wnioski:

1. Stabilny narzut wokół średniej (~1.54x): Jak wskazuje wykres oraz linia odniesienia (Mean Speedup = 1.54x), krotność przyspieszenia wynikająca z wyłączenia weryfikacji więzów integralności nie rośnie wykładniczo, lecz oscyluje w wąskim przedziale od 1.2x do 2.0x (ze średnią 1.54x).
2. Dlaczego relatywny zysk jest stabilny niezależnie od $N$?: Wykonanie zapytania masowego bez sprawdzania kluczy obcych (FOREIGN_KEY_CHECKS=0) również musi alokować pamięć i wstawiać wiersze do tabeli docelowej z $N$ kolumnami. Gdy flaga jest włączona (FOREIGN_KEY_CHECKS=1), silnik MariaDB dla każdego z $N$ kluczy wykonuje dodatkowe wyszukiwanie w B-drzewie słownika o złożoności $\mathcal{O}(\log L)$. Ponieważ zarówno czas zapisu podstawowego, jak i czas weryfikacji kluczy rosną liniowo z liczbą kolumn $N$, ich stosunek pozostaje względnie stały.
3. Praktyczna rekomendacja: Bazowanie na pojedynczym skrajnym wyniku (np. dla $N=50$) mogłoby być mylące ze względu na fluktuacje systemowe. Patrząc na pełen profil pomiarowy dla 19 punktów, stosowanie SET FOREIGN_KEY_CHECKS=0 przy zaufanym imporcie masowym przynosi stabilny, przewidywalny zysk rzędu ~35% skrócenia czasu operacji (ok. 1.5x przyspieszenia).

4. Wpływ kardynalności słowników ($L$)

Zależność czasu weryfikacji od rozproszenia wartości klucza obcego w tabelach nadrzędnych:

Weryfikacja hipotezy H4 (Wpływ rozproszenia słownika $L$):

1. Weryfikacja hipotezy H4: Pomiary empiryczne w pełnym zakresie $L = 10 \dots 100\ 000$ w pełni potwierdzają Hipotezę H4. Czas operacji wstawiania wierszy rośnie wraz ze wzrostem liczby rekordów w tabelach słownikowych (wzrost czasu o 35.7%, z 0.0817 s dla $L=10$ do 0.1108 s dla $L=100\ 000$).
2. Dlaczego krzywa jest płaska dla małych $L$ ($L \le 1\ 000$)?: Dla słowników zawierających do 1 000 rekordów wszystkie strony B-drzewa indeksu głównego (id) mieszczą się w zaledwie kilku stronach pamięci podręcznej (InnoDB Buffer Pool) i są stale „gorące”. W efekcie silnik wykonuje weryfikację bezpośrednio w pamięci RAM bez żadnych opóźnień.
3. Wpływ dużego rozproszenia ($L \ge 10\ 000 \dots 100\ 000$): Gdy kardynalność rośnie do 100 000 unikalnych wartości, strony indeksowe B-drzew zajmują znacznie więcej bloków pamięci. Losowe generowanie identyfikatorów (rand(1, L)) wymusza ciągłe przeskakiwanie po rozproszonych gałęziach drzewa i częstsze unieważnianie pamięci podręcznej procesora (CPU cache misses) oraz stron bufora InnoDB.

5. Jednowątkowe wąskie gardło CPU vs zrównoleglone połączenia SQL (Multi-threading & Concurrency)

Jednowątkowe wąskie gardło CPU (Single-Core Bottleneck)

Podczas wykonywania testów masowego zapisu dla dużej liczby kluczy obcych ($N=50$) i paczki $K=5\ 000$ wierszy można zaobserwować sytuację, w której pojedyncza instrukcja SQL wykonuje się w konsoli przez kilkadziesiąt do kilkuset sekund.

Analiza obciążenia systemu wykazuje wówczas charakterystyczne zachowanie:

• CPU (Single-Core): 100% – proces MariaDB (mariadbd) maksymalnie dociąża dokładnie jeden rdzeń procesora.
• Dysk I/O: 0% wait – dysk nie stanowi wąskiego gardła (dane mieszczą się w InnoDB Buffer Pool).
• RAM: brak ograniczeń – wysokie zapasy wolnej pamięci operacyjnej.

Mechanizm: W silniku MariaDB / InnoDB wykonanie pojedynczej instrukcji SQL (CALL do_test(...) lub INSERT INTO ... SELECT) odbywa się w ramach pojedynczego połączenia i jednego wątku serwera. Dla $N=50$ i $K=5\ 000$ silnik musi sekwencyjnie wykonać pod maską $50 \times 5\ 000 = \mathbf{250\ 000}$ weryfikacji w B-drzewach indeksów w tabeli major_1 oraz kolejne 250 000 sprawdzeń przy kopiowaniu do major_2 – łącznie pół miliona operacji wyszukiwania B-tree wykonywanych jednowątkowo!

Zrównoleglenie zapisu po stronie klienta (Client-Side Concurrency)

Silnik MariaDB nie zrównolegla automatycznie wykonania pojedynczego zapytania INSERT na wiele rdzeni CPU. Aby w pełni wykorzystać nowoczesne procesory wielordzeniowe (w naszym środowisku testowym dysponujemy maszyna z 32 rdzeniami CPU), należy zastosować zrównoleglenie połączeń klientów (Parallel Connections).

Skrypt benchmarkujący wspiera flagę --concurrency=W (lub --threads=W), która dzieli paczkę $K$ wierszy na $W$ równoległych procesów w PHP (pcntl_fork), z których każdy nawiązuje osobne połączenie do MariaDB:

# Wykonanie testów w trybie jednopołączeniowym (wątek domyślny, W=1)
php bin/run_benchmarks.php

# Uruchomienie testu w trybie zrównoleglonym na 4 wątkach połączeń:
php bin/run_benchmarks.php --concurrency=4

# Dedykowany scenariusz skalowania wielowątkowego (W = 1, 2, 4, 8, 16, 32):
php bin/run_benchmarks.php --scenario=concurrency

Gdy $W$ niezależnych połączeń przesyła zapytania jednocześnie, MariaDB przydziela im osobne wątki robocze (mariadbd worker threads z puli thread_handling=one-thread-per-connection), angażując $W$ rdzeni procesora jednocześnie.

Wyniki skalowania wielowątkowości ($W = 1 \dots 32$)

Przeprowadzono gęstą serię pomiarową dla zapisu paczki $K = 10\ 000$ wierszy z $N = 10$ kluczami obcymi, badając zachowanie systemu dla każdego poziomu zrównoleglenia od $W = 1$ do $W = 32$ wątków klienckich:

Wnioski i Prawo Amdahla w Praktyce

1. Szczyt wydajności wielowątkowej przy $W=6 \dots 10$ (blisko $2\times$ przyspieszenia): Zwiększanie liczby równoległych wątków połączeń z 1 do 8 skraca czas zapisu $10\ 000$ wierszy z 0.1681 s do 0.0882 s, zwiększając łączną przepustowość bazy do ~113 300 rekordów na sekundę!
2. Punkt nasycenia i stabilizacja ($W=6 \dots 10$): W przedziale od 6 do 10 równoległych połączeń MariaDB w pełni nasyca dedykowane wątki robocze procesora, utrzymując maksymalną stabilną przepustowość ~100k–113k rec/s.
3. Narzut synchronizacji i rywalizacji przy $W \ge 16 \dots 32$: Przy 32 współbieżnych połączeniach czas zapisu rośnie do 0.1699 s (spadek przepustowości do ~58k rec/s, powrót w okolice pojedynczego wątku). Wynika to bezpośrednio z:
   • Page Latches (Blokada stron indeksowych B-drzewa): 32 wątki próbują naraz modyfikować te same liście B-drzewa tabeli głównej major_1.
   • Log Buffer Mutex: Wszystkie wątki walczą o sekwencyjny dostęp i zapis do wspólnego dziennika transakcji ib_logfile w pamięci InnoDB.

Rekomendacja architektoniczna: W produkcyjnych systemach masowego przetwarzania (ETL / usługi importu) optymalny rozmiar puli równoległych workerów (Worker Pool / Connection Pool) w MariaDB wynosi 6 do 8 współbieżnych połączeń. Zapewnia to maksymalną przepustowość I/O przy braku zbędnego narzutu rywalizacji o blokady InnoDB.

Podsumowanie i praktyczne zalecenia (Best Practices)

1. Grupuj zapisy w transakcje: Nigdy nie wykonuj pętli z pojedynczymi zapytaniami INSERT przy domyślnym autocommit=1. Otoczenie bloku zapisu transakcją ($conn->beginTransaction() / $conn->commit()) redukuje czas zapisu z niemal 7 minut do poniżej 1 minuty, eliminując wymuszone wywołania dyskowe fsync po każdym wierszu.
2. Stosuj zapis masowy (Multi-row INSERT / Batching):
   • Zalecana wielkość paczki ($K$): Optymalny rozmiar paczki wstawianej w pojedynczym zapytaniu SQL wynosi od 500 do 1 000 rekordów dla lekkich zapytań (początkowy szczyt ~74 000 rec/s) lub od 6 000 do 25 000 rekordów w ciężkich procesach ETL (maksymalna stabilna przepustowość do ~72 000 rec/s).
   • Unikaj „Doliny Alokacyjnej” ($2\ 250 \dots 3\ 500$ wierszy): Z powodu przejścia alokatora PHP (Zend Memory Manager) na duże bloki systemowe (>256 KB) oraz realokacji bufora pakietu w MariaDB (net_buffer_length), paczki tej wielkości generują lokalny spadek wydajności do ~44 000 rec/s.
   • Unikaj paczek $>50\ 000$ wierszy: Sklejanie gigantycznych ciągów tekstowych nie przynosi zysków, obciąża pamięć RAM i zbliża zapytanie do limitu max_allowed_packet (16 MB).
3. Wyłączaj FOREIGN_KEY_CHECKS przy masowym imporcie: Jeżeli importujesz dane z zaufanego źródła (np. zrzut bazy, migracje, przetwarzanie wsadowe), wyłączenie weryfikacji na czas operacji:

   SET FOREIGN_KEY_CHECKS=0;
   -- Masowy import danych
   SET FOREIGN_KEY_CHECKS=1;

   zapewnia stabilne 1.5-krotne przyspieszenie (~35% redukcji czasu) w całym zakresie liczby kluczy obcych ($N$), a przy bardzo złożonych strukturach skraca czas nawet o połowę (~2.0x).
4. Zapewnij odpowiedni rozmiar innodb_buffer_pool_size: Dla małych słowników ($L \le 1\ 000$) strony B-drzewa są stale gorące w pamięci RAM. Przy dużej kardynalności ($L \ge 100\ 000$) rozproszenie kluczy zwiększa czas weryfikacji o ponad 35%, dlatego wielkość bufora InnoDB powinna bez problemu mieścić wszystkie indeksy relacyjne.
5. Wykorzystuj zrównoleglenie połączeń klienckich ($W = 6 \dots 10$): Pojedyncze zapytanie masowe w MariaDB wykonuje się zawsze jednowątkowo na jednym rdzeniu CPU. Podział zadania na 6 do 8 równoległych workerów pozwala wykorzystać wielordzeniowość procesora i podnieść łączną przepustowość bazy do ponad 113 000 rekordów na sekundę (blisko 2-krotne przyspieszenie bez ryzyka rywalizacji o blokady).