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.

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

Nejnovější

Napsat komentář

Vaše e-mailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *