MySQL a MariaDB jsou nejpoužívanější systémy pro správu relačních databází (RDMS), pokud jde o webhosting a CMS systémy, jako jsou Joomla, WordPress, Drupal a Typo 3. V tomto článku vysvětlím, jak zrychlit a optimalizovat vaše MySQL a databázový server MariaDB.
Ukládejte data MySQL do samostatných oddílů
Pokud jde o optimalizaci a zajištění, je vždy nejlepším nápadem ukládat data databáze do samostatného svazku. Svazky jsou speciálně pro rychlé svazky úložiště, jako je SSD, NVMe. I když váš systém selže, budete mít databázi v bezpečí. Protože se svazek oddílu skládá z rychlých svazků úložiště, výkon bude rychlejší.
Nastavte maximální počet připojení MySQL
MySQL/MariaDB používá instrukci max_connections který určuje, kolik souběžných připojení je aktuálně povoleno na serveru. Příliš mnoho připojení má za následek vysokou spotřebu paměti a vysoké zatížení procesoru. U malých webů lze připojení specifikovat na 100-200 a větší mohou potřebovat 500-800 a více. max_connections lze dynamicky měnit pomocí SQL dotazu. V tomto příkladu jsem nastavil hodnotu na 200.
$ mysql -u root -p
mysql> set global max_connections=200;
Výstup:
Povolit protokol pomalých dotazů MySQL
Protokolovací dotazy, jejichž provedení trvá velmi dlouho, usnadňuje odstraňování problémů s databází. Protokol pomalých dotazů lze aktivovat přidáním následujících řádků do konfiguračního souboru MySQL/MariaDB.
slow-query-log=1 slow-query-log-file= /var/lib/mysql/mysql-slow-query.log long-query-time=1
Kde první proměnná umožňuje pomalý protokol dotazů
Druhá proměnná definuje adresář souboru protokolu
Třetí proměnná definuje čas dokončení dotazu MySQL
Restartujte službu mysql/mariadb a sledujte protokol
$ systemctl restart mysql
$ systemctl restart mariadb
$ tail -f /var/lib/mysql/mysql-slow-query.log
Nastavte maximální paket povolený MySQL
Data jsou v MySQL rozdělena do paketů. Max_allowed_packet definuje maximální velikost paketů, které lze odeslat. Příliš nízké nastavení max_allowed_packet může způsobit, že dotaz bude příliš pomalý. Doporučuje se nastavit hodnotu paketu na velikost největšího paketu.
Nastavení dočasné kapacity tabulky
Tmp_table_size je maximální prostor použitý pro tabulku vestavěné paměti. Pokud velikost tabulky překročí zadaný limit, bude převedena na tabulku MyISAM na disku. V MySQL/MariaDB můžete přidat následující proměnné do konfiguračního souboru a nastavit velikost dočasné tabulky. Doporučuje se nastavit tuto hodnotu na serveru 64M na GB paměti.
[mysqld] tmp_table_size=64M
Restartujte službu mysql
$ systemctl restart mysql
$ systemctl restart mariadb
Nastavte maximální kapacitu tabulky paměti.
Max_heap_table_size je proměnná používaná v MySQL ke konfiguraci maximální kapacity tabulky paměti. Velikost maximální kapacity tabulky paměti by měla být stejná jako kapacita dočasné tabulky, aby nedocházelo k zápisu na disk. Doporučuje se nastavit tuto hodnotu na serveru na 64M na GB paměti. Přidejte následující řádek do konfiguračního souboru MySQL a restartujte službu.
[mysqld] max_heap_table_size=64M
Chcete-li použít změny, restartujte databázový server.
$ systemctl restart mysql
$ systemctl restart mariadb
Zakázat zpětné vyhledávání DNS pro MySQL
Když je přijato nové připojení, MySQL/MariaDB provede vyhledání DNS k vyřešení IP adresy uživatele. To může způsobit zpoždění, když je konfigurace DNS neplatná nebo je problém se serverem DNS. Chcete-li zakázat vyhledávání DNS, přidejte do konfiguračního souboru MySQL následující řádek a restartujte službu MySQL.
[mysqld] skip-name-resolve
Restartujte službu:
$ systemctl restart mysql
$ systemctl restart mariadb
Vyhněte se používání Swappiness v MySQL
Linuxové jádro přesune část paměti do speciálního oddílu disku zvaného „swap“ prostor, když systému dojde fyzická paměť. V tomto stavu systém spíše zapisuje informace na disk, než aby uvolňoval část paměti. Vzhledem k tomu, že systémová paměť je rychlejší než diskové úložiště, doporučujeme deaktivovat swapování. Swappiness lze zakázat pomocí následujícího příkazu.
$ sysctl -w vm.swappiness=0
Výstup:
Zvětšete velikost fondu vyrovnávací paměti InnoDB
MySQL/MariaDB má InnoDB engine, který má vyrovnávací paměť pro cache a indexování dat v paměti. Buffer pool pomáhá MySQL/MariaDB dotazy spouštět poměrně rychleji. Výběr správné velikosti fondu vyrovnávací paměti InnoDB vyžaduje určité znalosti systémové paměti. Nejlepším nápadem je nastavit hodnotu velikosti vyrovnávací paměti InnoDB na 80 % RAM.
Příklad.
- Systémová paměť =4 GB
- Velikost vyrovnávací paměti =3,2 GB
Přidejte následující řádek do konfiguračního souboru MySQL a restartujte službu
[mysqld] Innodb_buffer_pool_size 3.2G
Restartujte databázi:
$ systemctl restart mysql
$ systemctl restart mariadb
Zacházení s velikostí mezipaměti dotazu
Direktiva Query cache v MySQL/MariaDB se používá k ukládání všech dotazů, které se neustále opakují se stejnými daty. U malých webů se doporučuje nastavit hodnotu na 64 MB a prodloužit čas. Zvyšování hodnoty velikosti mezipaměti dotazů na GB se nedoporučuje, protože může snížit výkon databáze. Přidejte následující řádek do souboru my.cnf.
[mysqld] query_cache_size=64M
Zkontrolujte nečinná připojení
Nečinná připojení spotřebovávají zdroje, takže je třeba je ukončit nebo obnovit, pokud je to možné. Tato připojení zůstávají ve stavu „spánku“ a mohou zůstat po dlouhou dobu. Zkontrolujte nečinná připojení pomocí následujícího příkazu.
$ mysqladmin processlist -u root -p | grep “Sleep”
Dotaz zobrazí seznam procesů, které jsou ve stavu spánku. Obecně v PHP může událost nastat při použití mysql_pconnect. Tím se otevře připojení MySQL, provede se dotazy, odstraní se autentizace a připojení zůstane otevřené. Používá se wait_timeout mohou být přerušena nečinná spojení. Výchozí hodnota pro wait_timeout je 28 800 sekund, které lze snížit na minimální časový rozsah, například 60 sekund. Přidejte následující řádek do souboru my.cnf
[mysqld] wait_timeout=60
Optimalizace a oprava databáze MySQL
Pokud se server neočekávaně vypne, existuje možnost, že tabulky v MySQL/MariaDB mohou selhat. Existují další možné důvody pro zhroucení databázové tabulky, jako je například přístup k databázi, když běží proces kopírování, náhle se zhroutí souborový systém. V této situaci máme speciální nástroj s názvem „mysqlcheck ” který kontroluje, opravuje a optimalizuje všechny tabulky v databázích.
Pomocí následujícího příkazu proveďte činnosti opravy a optimalizace.
Pro všechny databáze:
$ mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
Pro konkrétní databázi:
$ mysqlcheck -u root -p --auto-repair --check --optimize dbname
Nahraďte dbname názvem vaší databáze
- Zkontrolujte výkon MySQL/MariaDB pomocí testovacích nástrojů
Nejlepší je pravidelně kontrolovat výkon databáze MySQL/MariaDB. Díky tomu bude snadné získat zprávu o výkonu a bod zlepšení. Existuje mnoho dostupných nástrojů, z nichž mysqltuner je nejlepší.
Spusťte následující příkaz ke stažení nástroje
$ wget https://github.com/major/MySQLTuner-perl/tarball/master
Rozbalte soubor
$ tar xvzf master
Přejděte do adresáře projektu a spusťte následující skript.
$ cd major-MySQLTuner-perl-7aa57fa
$ ./mysqltuner.pl
Výstup:
Závěr
V tomto článku jsme se naučili optimalizovat MySQL/MariaDB pomocí různých technik. Děkuji za přečtení.