Nastavení a stavové proměnné MySQL

7. února 2002

•

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.