Nastavení a stavové proměnné MySQL
Jak nakonfigurovat MySQL, aby zpracovávalo korektně češtinu? Jak zapnout logování všech dotazů? Jak zjistit, proč je váš server tak pomalý? Odpovím vám i na spousty dalších otázek o pokročilé konfiguraci a o runtime proměnných.
Stavové proměnné
Sledováním hodnot těchto proměnných se dozvíte o aktuálním stavu MySQL serveru. Zjistíte, kolik připojení právě obsluhuje, kolik souborů tabulek má otevřeno atd. Runtime proměnné nejsou konfigurační, takže je nelze měnit, jen sledovat. Jak se k nim dostat, jak je zobrazit, jsem popsal v článku Řádkový klient a administrátor MySQL a Zjednodušení práce s MySQL. Následující seznam vysvětluje, co znamenají.
Aborted_clients | Počet násilně ukončených spojení. Klient zmizel bez náležitého ukončení spojení. |
Aborted_connects | Počet pokusů o připojení k serveru, které skončily nezdarem. |
Bytes_received | Celkový počet bytů přijatý od všech klientů během všech spojení. |
Bytes_sent | Celkový počet bytů odeslaných všem klientům během všech spojení. |
Connections | Celkový počet pokusů o připojení k serveru. |
Created_tmp_disk_tables | Aktuální počet skrytých dočasných tabulek na disku serveru, které jsou vytvářeny během obsluhy požadavků. |
Created_tmp_tables | Aktuální počet skrytých dočasných tabulek v operační paměti serveru, které jsou vytvářeny během obsluhy požadavků. Když je hodnota příliš velká, měli byste zvětšit sort_buffer. |
Created_tmp_files | Kolik dočasných souborů server vytvořil. |
Delayed_insert_threads | Počet vláken běžících s úkolem zpožděného zápisu do tabulek. Odpovídá SQL příkazu INSERT DELAYED. |
Delayed_writes | Počet řádků zapsaných pomocí SQL příkazu INSERT DELAYED. |
Delayed_errors | Počet pokusů o zapsání řádku pomocí SQL příkazu INSERT DELAYED, u kterých nastala chyba. |
Flush_commands | Počet vykonaných příkazů FLUSH. |
Handler_delete | Celkový počet všech smazaných řádků ve všech tabulkách a databázích, co server obsluhuje. Odpovídá SQL příkazu DELETE. |
Handler_read_first | Celkový počet případů, kdy první údaj byl načítán z indexu tabulky. Opět se vztahuje na všechny tabulky a databáze. Když je hodnota vysoká, naznačuje to, že server provádí často prohledávání celých indexů. |
Handler_read_key | Celkový počet požadavků čtení řádků, jejichž nějaký sloupec je v dané tabulce klíčem. Když je hodnota vysoká, naznačuje to, že dotazy i tabulky jsou správně indexovány. |
Handler_read_next | Celkový počet požadavků čtení řádků, jejichž nějaký sloupec je v dané tabulce v pořadí dalším (ne prvním) sloupcem s klíčem. |
Handler_read_rnd | Celkový počet požadavků čtení řádků, kde se vyžaduje setřízení výstupní tabulky. Vysoké čislo prozrazuje vysoký počet dotazů, které vyžadují třídění výsledků, nebo průchod celé tabulky, nebo operace spojení tabulek nepoužívá klíče. |
Handler_read_rnd_next | Celkový počet požadavků prohledávání dvojic řádků za sebou v pořadí tabulky. Vysoké číslo prozrazuje, že spouštíte hodně dotazů, které musí prohledávat všechny řádky tabulky. Tabulky nejsou pravděpodobně správně naindexovány a nebo dotazy nejsou sestaveny tak, aby využívaly indexy. |
Handler_update | Celkový počet všech kdy změněných řádků ve všech tabulkách a databázích, co server obsluhuje. Odpovídá SQL příkazu UPDATE. |
Handler_write | Celkový počet všech kdy zapsaných řádků ve všech tabulkách a databázích, co server obsluhuje. Odpovídá SQL příkazu INSERT. |
Key_blocks_used | Počet aktuálně využitých bloků v cache klíčů. |
Key_read_requests | Počet požadavků čtení bloku z cache klíčů. |
Key_reads | Počet fyzických čtení bloků klíčů z disku serveru. Jestliže je key_reads velké, pak je key_cache pravděpodobně příliš malá. |
Key_write_requests | Počet požadavků načtení bloku klíčů do cache klíčů. |
Key_writes | Počet fyzických zápisů bloků klíčů na disk serveru. |
Max_used_connections | Maximální počet současně používaných spojení. |
Not_flushed_key_blocks | Počet bloků klíčů v cache klíčů, které byly změněny, ale nebyly dosud zapsány na disk. |
Not_flushed_delayed_rows | Počet řádků čekajících na zapsání pomocí SQL příkazů INSERT DELAYED. |
Open_tables | Celkový počet aktuálně otevřených tabulek. |
Open_files | Celkový počet aktuálně otevřených souborů. |
Open_streams | Celkový počet aktuálně otevřených proudů. |
Opened_tables | Celkový počet kdy otevřených tabulek. Když je číslo příliš vysoké, pravděpodobně je proměnná table_cache nastavena na nízkou hodnotu. |
Select_full_join | Celkový počet operací spojení tabulek bez pomocí klíčů. Hodnota by měla být určitě 0, jinak takové spojování neúměrně zatěžuje server. |
Select_full_range_join | Celkový počet operací spojení tabulek, kde server použil prohledávání v celém rozsahu připojované tabulky. |
Select_range | Celkový počet operací spojení tabulek, kde server použil prohledávání v celém rozsahu první tabulky. I když je číslo vysoké, neznamená to špatnou optimalizaci SQL dotazu JOIN. |
Select_scan | Celkový počet operací spojení tabulek, kde server procházel první tabulku. |
Select_range_check | Celkový počet operací spojení tabulek bez použití klíčů, kde server kontroluje použití klíče v každém řádku tabulky. Hodnota by měla být nulová. |
Questions | Počet dotazů poslaných serveru. |
Slave_running | Hodnota ON nebo OFF určuje, zda se vytváří podřízené vlákno (slave thread). Viz také následující. |
Slave_open_temp_tables | Počet aktuálně otevřených dočasných tabulek v podřízeném vlákně (slave thread). |
Slow_launch_threads | Počet vláken, kterým spojení trvalo déle než slow_launch_time sekund. |
Slow_queries | Počet dotazů, jejichž zpracování trvalo více než long_query_time sekund. |
Sort_merge_passes | Počet záměn, které se musely vykonat při setřizování. Souvisí s ORDER BY. Když je hodnota příliš velká, měli byste zvětšit sort_buffer. |
Sort_range | Počet třízení, která byla uskutečněna v daném rozsahu. |
Sort_rows | Počet všech tříděných řádků. |
Sort_scan | Počet třízení, která byla uskutečněna pomocí procházení celé tabulky. |
Table_locks_immediate | Aktuální počet zamknutých tabulek. |
Table_locks_waited | Počet tabulek čekajících na uzamčení, např. aby na nich pak mohl být vykonán SQL dotaz. |
Threads_cached | Počet vláken uložených v cache vláken. |
Threads_created | Celkový počet kdy vytvořených vláken. |
Threads_connected | Počet aktuálně otevřených spojení. |
Threads_running | Počet právě aktivních vláken, tj. těch, která nejsou „sleep“. |
Uptime | Kolik sekund již MySQL server běží. |
Serverové proměnné, nastavení serveru
Jak na editaci konfiguračních souborů v souvislosti s nutností základního nastaveni při instalaci serveru jsem psal již v Instalujeme MySQL na Windows a Instalujeme MySQL na Linux. Nyní uvádím seznam proměnných s vysvětlením jejich významu pro nastavování.
ansi_mode | Hodnota může být ON nebo OFF. Jedná se o potlačení některých syntaktických a sémantických konvencí běžných jen u MySQL tak, aby vyhovovaly normě ANSI. |
back_log | Největší počet nedokončených požadavků na spojení, které může server mít. |
basedir | Výchozí adresář instalace serveru. |
binlog_cache_size | Velikost cache, která uchovává SQL dotazy pro binární log v průběhu zpracování transakce. Když často používáte rozsáhlé transakce o více SQL dotazech, zvyšte tuto hodnotu. |
character_set | Aktuální znaková stránka. Pro češtinu se používá především „czech“, „latin2“ nebo „win1250“. |
character_sets | Seznam dostupných znakových stránek, které server „umí“. |
concurrent_inserts | Jestliže je ON, je současně na tabulách typu MyISAM povoleno provádění příkazu INSERT a SELECT. |
connect_timeout | Kolik sekund čeká server na connect packet. Jinak ukončí handshaking. |
datadir | Adresář, kam MySQL ukládá soubory tabulek. |
delay_key_write | Jestliže je zapnuto ON, tak buffery klíčů tabulek nebudou flushované pokaždé, když se změní index, ale až v okamžiku uzavření tabulky. |
delayed_insert_limit | Jak dlouho se bude čekat s obsluhou SQL příkazu INSERT DELAYED, než se vyřídí všechny SELECTy. |
delayed_insert_timeout | Jak dlouho by mělo vlákno obsluhující INSERT DELAYED čekat na SQL příkaz INSERT před tím, než se ukončí jeho zpracování. |
delayed_queue_size | Jaká velikost fronty (počítán počet řádků tabulky) by se měla alokovat pro obsluhu SQL příkazu INSERT DELAYED. Když se fronta naplní, kterýkoli z připojených klientů požadujících rovněž INSERT DELAYED bude čekat na uvolnění. |
flush | Jestliže je ON, MySQL byla spuštěna s parametrem –flush |
flush_time | Nenulová hodnota znamená, že každých flush_time sekund budou uzavřeny všechny tabulky. Tím se uvolní systémové prostředky a vše se uloží na disk. Vhodné jen na systémech s nedostatkem systémových prostředků. |
have_bdb | Hodnota YES (opakem je NO) znamená, že server podporuje databázové tabulky typu Berkeley. |
have_gemini | Podporuje databázové tabulky typu Gemini. |
have_innobase | Podporuje databázové tabulky typu Innobase. |
have_isam | Podporuje databázové tabulky typu Isam. |
have_raid | Podporuje datový sklad typu RAID. |
have_ssl | Podporuje kryptování SSL pro svůj klient – server protokol. |
init_file | Jméno souboru obsahujícího SQL příkazy, který se má provést ihned po spuštění serveru. Pokud je prázdné = žádný soubor. |
interactive_timeout | Doba (v sekundách), jak dlouho čeká server na aktivitu interaktivního spojení, než jej ukončí. |
join_buffer_size | Velikost bufferu, který se použije při obsluze SQL dotazu FULL JOIN. Taková spojení tabulek nepoužívají indexy. Pro zrychlení operace zvětšete buffer, pokud nemůžete spojovat tabulky s využitím indexů. |
key_buffer_size | Udává velikost bufferu pro bloky indexů. Tyto sdílené bloky používají všechna vlákna všech klientů. Pro lepší práci s indexy zvyšte velikost. Souvisí se stavovými proměnnými Key_read_requests, Key_reads, Key_write_requests, a Key_writes. |
language | Jazyk, ve kterém se vypisují chybová hlášení, resp. cesta k souboru, kde jsou hlášení uložena. |
large_file_support | Hodnota YES (opakem je NO) znamená, že server podporuje velké soubory. |
log | … se logují všechny SQL dotazy. |
log_update | … je zapnutý update log. |
log_bin | … je zapnutý binary log. |
log_slave_updates | … do update log se zapisuje i ze „slave thread“. Viz stavová proměnná slave_running. |
long_query_time | Jestliže SQL dotaz trvá déle, než long_query_time sekund, zvětší se počítadlo slow_queries a jestliže je zapnuté logovaní pomalých dotazů, provede se rovněž zápis do logu. |
lower_case_table_names | Hodnota je 0 nebo 1. Jména tabulek jsou ukládána na disk malými písmeny. |
max_allowed_packet | Maximální délka paketu. Když používáte obrovské BLOB záznamy, budete ji muset zvětšit. Maximálně však na 16 mega. |
max_binlog_cache_size | Jestliže transakce s více SQL dotazy vyžaduje více, než max_binlog_cache_size, nastane chyba. |
max_connections | Maximální povolený počet současně připojených klientů. |
max_connect_errors | Když z jednoho počítače (klienta) nastane více než max_connect_errors přerušených spojení, je zablokován pro další spojení. Všechny pak odblokujete pomocí FLUSH HOSTS. |
max_delayed_threads | Maximum vláken obsluhujících SQL dotaz INSERT DELAYED. Při překročení budou další INSERT DELAYED obsluhovány jako běžné INSERT bez DELAYED. |
max_heap_table_size | Maximální velikost heap tabulek (hromada) v bytech. |
max_join_size | Operace spojení tabulek, které budou potřebovat načíst více, než max_join_size záznamů, vrátí chybu. |
max_sort_length | Kolik bytů se má použít při třídění položek typu BLOB nebo TEXT. Zbývající byty jsou ignorovány. |
max_tmp_tables | Maximální počet dočasných tabulek, které může mít klient současně otevřené. |
max_write_lock_count | Až proběhne max_write_lock_count uzamčení tabulek pro zápis, obslouží se čekající uzamčení tabulek pro čtení. |
myisam_recover_options | Hodnota (OFF/ON) atributu –myisam-recover option při spouštení serveru. |
myisam_sort_buffer_size | Velikost bufferu, který je alokován pro třízení indexu, nebo pro operaci opravy tabulky REPAIR, nebo pro vytváření indexu pomocí CREATE INDEX nebo ALTER TABLE. |
net_buffer_length | Komunikační buffer je nastaven na tuto velikost mezi SQL dotazy. |
net_read_timeout | Kolik sekund se čeká na další data před tím, než se čtení přeruší. |
net_retry_count | Jestliže je čtení na komunikačním potru přerušeno, zkouší jej obnovit net_retry_count krát, než to vzdá. |
net_write_timeout | Kolik sekund se čeká na zapsání bloku předtím, než se zápis přeruší. |
open_files_limit | Jestliže je hodnota nenulová, udává limit počtu otevřených souborů. Zvětšete, pokud se objeví chyba příliš mnoho otevřených souborů. |
pid_file | Cesta a název pid souboru. |
port | Port, na kterém server běží. |
protocol_version | Verze protokolu, kterou MySQL server používá. |
record_buffer | Každé vlákno, které prohledává sekvenčně (full scan), si vytvoří buffer této velikosti pro každou tabulku, kterou prohledává. |
query_buffer_size | Velikost počáteční alokace bufferu pro SQL dotaz. Zvětšete pro případ, že často spouštíte velké dotazy, např. vkládání BLOB položek. |
safe_show_databases | Hodnota ON říká, že uživateli se neukážou v seznamu databází takové z nich, na které nemá žádná práva. Při OFF se zobrazí všechny. |
server_id | Hodnota atributu –server-id při spuštění serveru. |
skip_locking | Je OFF, jestliže server používá externí zamykání tabulek. |
skip_networking | Když je ON, jsou dovolena jen lokální spojení. |
skip_show_databases | Hodnota ON zakáže uživateli, který nemá právo PROCESS_PRIV, provedení příkazu SHOW DATABASES. |
slow_launch_time | Jestliže vytvoření vlákna bude trvat déle než slow_launch_time, bude zvětšeno počítadlo slow_launch_threads. |
socket | Unixový soket, který sever používá. |
sort_buffer | Každé vlákno, které potřebuje třídit, si alokuje buffer této velikosti. Zvětšete pro zrychlení operací ORDER BY a GROUP BY. |
table_cache | Maximální počet otevřených tabulek pro všechna vlákna. |
table_type | Výchozí typ databázových tabulek. |
thread_cache_size | Kolik vláken se má držet v paměti pro opětovné použití. Když se klient odpojí, příslušná vlákna se vloží do cache, pokud už tam není thread_cache_size vláken předtím. Všechna nová vlákna jsou nejprve brána z cache a až je prázdná, pak jsou teprve vytvářena nová podle potřeby. |
thread_stack | Velikost zásobníku pro každé vlákno. |
timezone | Časová zóna serveru. |
tmp_table_size | Jestliže dočasná tabulka, která se udržuje v paměti, přesáhne tuto velikost, bude automaticky převedena na MyISAM typ a uložena na disk. |
tmpdir | Adresář určený pro ukládání dočasných souborů a tabulek. |
version | Číslo verze serveru. |
wait_timeout | Doba v sekundách, po kterou server čeká na aktivitu daného spojení, než jej uzavře. |
Spolupráce MySQL s interpretrem PHP
Aby se mohlo PHP připojovat jako klient k serveru MySQL, nemusí se na straně serveru nastavovat nic. Je však potřeba změnit konfiguraci PHP.
Starší komentáře ke článku
Pokud máte zájem o starší komentáře k tomuto článku, naleznete je zde.
Mohlo by vás také zajímat
-
10 nejpopulárnějších programovacích jazyků a jejich využití
9. listopadu 2023 -
Vaše pošta může být špatně nastavena – svěřte ji profesionálům
13. července 2023
Nejnovější
-
Výkonný a kompaktní: ASOME Max Studio s výjimečným poměrem cena/výkon
11. listopadu 2024 -
Šokující data od Microsoftu: Kyberútoky rostou o stovky procent!
8. listopadu 2024 -
Chcete jedinečnou doménu? Objevte koncovky FOOD, MEME a MUSIC!
7. listopadu 2024 -
OpenAI představilo novou funkci ChatGPT Search
6. listopadu 2024