{"id":33345,"date":"2025-07-24T12:59:07","date_gmt":"2025-07-24T10:59:07","guid":{"rendered":"https:\/\/www.websupport.sk\/podpora\/?post_type=ht_kb&#038;p=33345"},"modified":"2025-07-24T13:46:49","modified_gmt":"2025-07-24T11:46:49","slug":"ladenie-vykonu-mysql-mariadb-a-percona-servera-pre-systemovych-administratorov","status":"publish","type":"ht_kb","link":"https:\/\/www.websupport.sk\/podpora\/kb\/ladenie-vykonu-mysql-mariadb-a-percona-servera-pre-systemovych-administratorov\/","title":{"rendered":"Ladenie v\u00fdkonu MySQL, MariaDB a Percona servera pre syst\u00e9mov\u00fdch administr\u00e1torov"},"content":{"rendered":"\n<p>Spr\u00e1vne <strong>ladenie v\u00fdkonu datab\u00e1zov\u00e9ho servera<\/strong> je k\u013e\u00fa\u010dov\u00e9 pre stabilitu a r\u00fdchlos\u0165 (nielen webov\u00fdch) aplik\u00e1ci\u00ed. Datab\u00e1zy MySQL (a jej klony MariaDB a Percona Server) v predvolenom nastaven\u00ed neb\u00fdvaj\u00fa vyladen\u00e9 pre optim\u00e1lny resp. maxim\u00e1lny v\u00fdkon \u2013 sk\u00f4r s\u00fa nastaven\u00e9 konzervat\u00edvne, aby fungovali na priemernom hardv\u00e9ri. Bez optimaliz\u00e1cie m\u00f4\u017ee doch\u00e1dza\u0165 k pomal\u00fdm odozv\u00e1m, <strong>pomal\u00fdm dopytom<\/strong> a neefekt\u00edvnemu vyu\u017eitiu CPU, pam\u00e4te \u010di diskov\u00e9ho \u00falo\u017eiska. Zmenou konfigur\u00e1cie a laden\u00edm v\u00fdkonu dok\u00e1\u017eeme zr\u00fdchli\u0165 spracovanie dopytov, zvl\u00e1dnu\u0165 viac s\u00fabe\u017en\u00fdch pou\u017e\u00edvate\u013eov a pred\u00eds\u0165 zahlteniu syst\u00e9mu.<br><br>Be\u017enou chybou administr\u00e1tora je spolieha\u0165 sa na <strong>predvolen\u00e9 nastavenia<\/strong> alebo naopak nekontrolovane prebera\u0165 nastavenia z internetu bez pochopenia. Ka\u017ed\u00fd server je in\u00fd \u2013 to, \u010do funguje inde, nemus\u00ed u v\u00e1s. Preto je d\u00f4le\u017eit\u00e9 postupova\u0165 systematicky: najprv <strong>monitorova\u0165<\/strong> aktu\u00e1lny stav, identifikova\u0165 \u00fazke miesta a potom postupne aplikova\u0165 zmeny. V\u017edy sledujte efekt ka\u017edej zmeny na v\u00fdkon. Neexistuje jedno magick\u00e9 nastavenie; ide o kombin\u00e1ciu viacer\u00fdch optimaliz\u00e1ci\u00ed prisp\u00f4soben\u00fdch konkr\u00e9tnemu prostrediu.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>V\u0161eobecn\u00e9 odpor\u00fa\u010dania a z\u00e1lohovanie<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-ht-blocks-messages wp-block-hb-message wp-block-hb-message--withicon is-style-alert\"><strong>Upozornenie:<\/strong> Pred ak\u00fdmko\u013evek z\u00e1sahom do konfigur\u00e1cie datab\u00e1zy <strong>v\u017edy zaistite aktu\u00e1lnu z\u00e1lohu<\/strong> datab\u00e1z aj konfigura\u010dn\u00fdch s\u00faborov a overte, \u017ee viete vykona\u0165 obnovu. Optimaliza\u010dn\u00e9 z\u00e1sahy m\u00f4\u017eu vies\u0165 k neo\u010dak\u00e1van\u00fdm probl\u00e9mom \u2013 napr\u00edklad zmena ve\u013ekosti logov InnoDB vy\u017eaduje \u0161peci\u00e1lny postup pri re\u0161tarte servera. Majte preto pripraven\u00fd pl\u00e1n n\u00e1vratu k p\u00f4vodn\u00e9mu stavu.<\/p>\n\n\n\n<p>Pam\u00e4tajte, \u017ee ni\u017e\u0161ie uveden\u00e9 nastavenia a hodnoty s\u00fa <strong>informat\u00edvne odpor\u00fa\u010dania<\/strong>. Ka\u017ed\u00fd server a aplik\u00e1cia m\u00e1 in\u00e9 po\u017eiadavky, preto ladeniu pristupujte met\u00f3dou <em>pokus-omyl<\/em> s priebe\u017en\u00fdm testovan\u00edm. Odpor\u00fa\u010dan\u00e9 hodnoty treba doladi\u0165 pod\u013ea re\u00e1lnej z\u00e1\u0165a\u017ee a v\u00fdsledkov meran\u00ed.<\/p>\n\n\n\n<p>Z\u00e1kladn\u00e9 pravidlo v\u00fdkonov\u00e9ho ladenia je meni\u0165 v\u017edy <strong>iba jednu vec naraz<\/strong> a pozorova\u0165 vplyv. Ak by ste upravili viac parametrov s\u00fa\u010dasne a zlep\u0161il by sa (alebo zhor\u0161il) v\u00fdkon, \u0165a\u017eko zist\u00edte, ktor\u00e1 zmena mala ak\u00fd efekt. Preto postupujte iterat\u00edvne a trpezlivo.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Rozdiely medzi MySQL, MariaDB a Percona<\/strong><\/h2>\n\n\n\n<p>MySQL, MariaDB a Percona Server s\u00fa <strong>pr\u00edbuzn\u00e9 datab\u00e1zov\u00e9 syst\u00e9my<\/strong> (MariaDB a Percona s\u00fa forky MySQL) a z poh\u013eadu v\u00fdkonov\u00e9ho ladenia s\u00fa vo ve\u013ekej miere podobn\u00e9. V\u00e4\u010d\u0161ina konfigura\u010dn\u00fdch parametrov (najm\u00e4 pre \u00falo\u017eisko InnoDB) m\u00e1 rovnak\u00e9 n\u00e1zvy a funkciu. Napriek tomu existuj\u00fa niektor\u00e9 rozdiely, na ktor\u00e9 je dobr\u00e9 bra\u0165 oh\u013ead:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Predvolen\u00e9 nastavenia:<\/strong> MariaDB aj Percona m\u00f4\u017eu ma\u0165 odli\u0161n\u00e9 predvolen\u00e9 hodnoty niektor\u00fdch parametrov oproti Oracle MySQL. Napr\u00edklad MariaDB vyp\u00edna niektor\u00e9 bezpe\u010dnostn\u00e9 z\u00e1pisy pre r\u00fdchlos\u0165 \u2013 predvolen\u00e1 hodnota sync_binlog je 0 (MySQL pou\u017e\u00edva 1) a re\u017eim z\u00e1pisu bin\u00e1rneho logu je MIXED namiesto plne transak\u010dn\u00e9ho ROW . To znamen\u00e1, \u017ee MariaDB v z\u00e1klade dosahuje vy\u0161\u0161\u00ed v\u00fdkon z\u00e1pisu na \u00fakor vy\u0161\u0161ieho rizika straty posledn\u00fdch d\u00e1t pri p\u00e1de (MySQL vol\u00ed konzervat\u00edvnej\u0161\u00ed pr\u00edstup). Pri laden\u00ed teda skontrolujte tieto nastavenia a zv\u00e1\u017ete, \u010di nepotrebujete v MariaDB zapn\u00fa\u0165 bezpe\u010dnej\u0161ie spr\u00e1vanie alebo naopak v MySQL povoli\u0165 r\u00fdchlej\u0161ie (no menej odoln\u00e9) nastavenie pod\u013ea potrieb.<br><\/li>\n\n\n\n<li><strong>Performance Schema:<\/strong> MySQL od verzie 5.6 \u0161tandardne pou\u017e\u00edva <strong>Performance Schema<\/strong> (intern\u00fd monitorovac\u00ed n\u00e1stroj), ktor\u00fd je v Oracle MySQL zapnut\u00fd predvolene. MariaDB m\u00e1 Performance Schema tie\u017e implementovan\u00fa, ale <strong>\u0161tandardne ju m\u00e1 vypnut\u00fa<\/strong>, aby sa zn\u00ed\u017eila re\u017eijn\u00e1 z\u00e1\u0165a\u017e . Ak teda pou\u017e\u00edvate MariaDB a chcete vyu\u017e\u00edva\u0165 detailn\u00e9 v\u00fdkonov\u00e9 metriky cez Performance Schema, mus\u00edte ju explicitne zapn\u00fa\u0165 (performance_schema=ON v konfigura\u010dnom s\u00fabore a re\u0161tart). Percona Server m\u00e1 Performance Schema rovnako ako MySQL zapnut\u00fa a \u010dasto prid\u00e1va aj vlastn\u00e9 roz\u0161\u00edrenia monitorovania.<br><\/li>\n\n\n\n<li><strong>InnoDB vs XtraDB:<\/strong> Star\u0161ie verzie MariaDB (do 10.1) pou\u017e\u00edvali namiesto Oracle InnoDB vlastn\u00fa fork verziu XtraDB (prevzat\u00fa od Percony). V nov\u0161\u00edch MariaDB u\u017e op\u00e4\u0165 figuruje InnoDB, av\u0161ak s r\u00f4znymi \u00fapravami. Z praktick\u00e9ho h\u013eadiska s\u00fa v\u0161ak pre n\u00e1s parametre rovnak\u00e9. Percona Server historicky pou\u017e\u00edval vylep\u0161en\u00e9 InnoDB (XtraDB), dnes u\u017e zos\u00fala\u010fuje n\u00e1zvy s Oracle MySQL. Rozdiely s\u00fa minim\u00e1lne \u2013 napr. <strong>MariaDB m\u00f4\u017ee ma\u0165 in\u00e9 implikovan\u00e9 spr\u00e1vanie niektor\u00fdch premenn\u00fdch<\/strong> alebo <strong>podporu \u010fal\u0161\u00edch storage engine<\/strong> (Aria, MyRocks at\u010f.), \u010do v\u0161ak pre be\u017en\u00e9 ladenie InnoDB nie je podstatn\u00e9.<br><\/li>\n\n\n\n<li><strong>Funkcie navy\u0161e:<\/strong> MariaDB aj Percona prid\u00e1vaj\u00fa niektor\u00e9 extra funkcie. Napr\u00edklad <strong>thread pool &#8211; <\/strong>v MySQL Community ed\u00edcii nie je dostupn\u00fd (iba v Enterprise verzii), zatia\u013e \u010do MariaDB ho m\u00e1 zabudovan\u00fd a Percona ho podporuje ako vo\u013ebu (thread_handling=pool-of-threads). Thread pool umo\u017e\u0148uje pri ve\u013ekom po\u010dte s\u00fabe\u017en\u00fdch spojen\u00ed efekt\u00edvnej\u0161ie riadi\u0165 vl\u00e1kna, aby nebol syst\u00e9m preplnen\u00fd stovkami vl\u00e1kien naraz. V praxi to znamen\u00e1, \u017ee MariaDB\/Percona zvl\u00e1dnu <strong>ve\u013ek\u00fd po\u010det pripojen\u00ed<\/strong> lep\u0161ie v\u010faka zabudovan\u00e9mu thread poolu (napr. MariaDB dok\u00e1\u017ee obsluhova\u0165 stovky tis\u00edc pripojen\u00ed pomocou poolu ), k\u00fdm MySQL pou\u017e\u00edva pre ka\u017ed\u00e9 pripojenie samostatn\u00e9 vl\u00e1kno. Pri be\u017enej z\u00e1\u0165a\u017ei to nemus\u00ed by\u0165 rozdiel, ale pri tis\u00edcoch spojen\u00ed u\u017e \u00e1no. Ak administrujete MariaDB\/Percona, zv\u00e1\u017ete vyu\u017eitie thread poolu (je \u010dasto <strong>predvolene zapnut\u00fd<\/strong> v MariaDB, v Percone treba povoli\u0165) pre vysok\u00fa konkuren\u010dn\u00fa z\u00e1\u0165a\u017e.<\/li>\n<\/ul>\n\n\n\n<p>Celkovo v\u0161ak plat\u00ed, \u017ee <strong>princ\u00edpy ladenia v\u00fdkonu s\u00fa takmer toto\u017en\u00e9<\/strong> pre MySQL, MariaDB aj Percona. \u010ealej v \u010dl\u00e1nku budeme hovori\u0165 v\u0161eobecne o MySQL (resp. o <strong>MySQL\/MariaDB<\/strong>), pri\u010dom spomenieme len tam, kde je konkr\u00e9tny rozdiel. V\u017edy si v\u0161ak overte detaily v dokument\u00e1cii pre konkr\u00e9tnu verziu, ktor\u00fa pou\u017e\u00edvate. Tie\u017e odpor\u00fa\u010dame pou\u017e\u00edva\u0165 <strong>posledn\u00e9 stabiln\u00e9 verzie<\/strong> (napr. MySQL 8.0.x, MariaDB 10.6\/10.11, Percona Server 8.0), ke\u010f\u017ee nov\u0161ie verzie \u010dasto prin\u00e1\u0161aj\u00fa v\u00fdkonov\u00e9 zlep\u0161enia a lep\u0161ie predvolen\u00e9 nastavenia.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Monitorovanie v\u00fdkonu<\/strong><\/h2>\n\n\n\n<p>Prv\u00fdm krokom ladenia je <strong>monitoring<\/strong> \u2013 potrebujete vedie\u0165, <em>\u010do<\/em> va\u0161u datab\u00e1zu spoma\u013euje. M\u00f4\u017ee to by\u0165 nedostatok CPU, m\u00e1lo pam\u00e4te, pomal\u00e9 diskov\u00e9 I\/O, nevhodn\u00e9 dopyty bez indexov, alebo kombin\u00e1cia faktorov. Na monitoring pou\u017eijeme jednak <strong>syst\u00e9mov\u00e9 n\u00e1stroje<\/strong>, jednak <strong>n\u00e1stroje na \u00farovni datab\u00e1zy<\/strong>.<\/p>\n\n\n\n<p><strong>Syst\u00e9mov\u00e9 n\u00e1stroje (CPU, RAM, disk, sie\u0165)<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>htop:<\/strong> Pokro\u010dil\u00e1 verzia klasick\u00e9ho top. Umo\u017e\u0148uje interakt\u00edvne sledova\u0165 vyu\u017eitie <strong>CPU a pam\u00e4te<\/strong> v re\u00e1lnom \u010dase, s rozpisom po vl\u00e1knach\/procesoch. Po spusten\u00ed htop na serveri uvid\u00edte, ko\u013eko CPU pou\u017e\u00edva proces MySQL\/MariaDB (mysqld), ko\u013eko pam\u00e4te zaber\u00e1 a \u010di n\u00e1hodou syst\u00e9m neswapuje (indik\u00e1cia nedostatku RAM). htop vie farebne zv\u00fdrazni\u0165 <strong>I\/O wait<\/strong> (\u010dakanie na disk) \u2013 ak vid\u00edte, \u017ee CPU jadro je v\u00e4\u010d\u0161inou v stave \u201eIOwait\u201c, znamen\u00e1 to, \u017ee proces \u010dak\u00e1 na disk (\u00falo\u017eisko je pravdepodobne bottleneck). htop v\u00e1m r\u00fdchlo napovie, \u010di je datab\u00e1zov\u00fd server CPU-bound (vysok\u00e9 za\u0165a\u017eenie CPU), RAM-bound (doch\u00e1dza pam\u00e4\u0165, pou\u017e\u00edva sa swap) alebo IO-bound (\u010dast\u00e9 \u010dakanie na disk). <\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-ht-blocks-messages wp-block-hb-message wp-block-hb-message--withicon is-style-info\"> Tip: <a href=\"https:\/\/www.websupport.sk\/podpora\/kb\/htop-manual\/\">Pre htop m\u00e1me pokro\u010dil\u00fd n\u00e1vod<\/a>.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>iotop:<\/strong> N\u00e1stroj podobn\u00fd top, zameran\u00fd na <strong>diskov\u00e9 vstupy\/v\u00fdstupy<\/strong>. Pr\u00edkaz iotop zobraz\u00ed procesy a mno\u017estvo d\u00e1t, ktor\u00e9 pr\u00e1ve \u010d\u00edtaj\u00fa alebo zapisuj\u00fa na disk. Ak je datab\u00e1za pomal\u00e1 a iotop uk\u00e1\u017ee, \u017ee mysqld trvalo intenz\u00edvne zapisuje alebo \u010d\u00edta ve\u013ek\u00e9 objemy, viete, \u017ee \u00fazke hrdlo je disk. M\u00f4\u017eete tie\u017e vidie\u0165 % \u010dasu str\u00e1ven\u00e9ho v I\/O oper\u00e1ci\u00e1ch. Napr\u00edklad pri vysokom IO vy\u0165a\u017een\u00ed uvid\u00edte v iotope proces mysqld s vysok\u00fdmi hodnotami \u010d\u00edtania\/z\u00e1pisu. V kombin\u00e1cii s htopom zist\u00edte, \u010di CPU \u010dak\u00e1 na disk (IOwait). Iotop pom\u00e1ha odhali\u0165, \u010di n\u00e1hodou <strong>nezapisuje MySQL pr\u00edli\u0161 \u010dasto na disk<\/strong> (napr. kv\u00f4li nevhodn\u00e9mu nastaveniu flushovania, o \u010dom viac ni\u017e\u0161ie).<br><\/li>\n\n\n\n<li><strong>atop:<\/strong> \u010eal\u0161\u00ed pokro\u010dil\u00fd monitorovac\u00ed n\u00e1stroj, ktor\u00fd zobrazuje historick\u00e9 \u0161tatistiky o vyu\u017eit\u00ed CPU, pam\u00e4te, diskov, siet\u00ed at\u010f. M\u00f4\u017ee by\u0165 u\u017eito\u010dn\u00fd, ak potrebujete pozrie\u0165, \u010do sa dialo v ur\u010ditom \u010dase (ak atop be\u017e\u00ed ako daemon a loguje d\u00e1ta). Pre r\u00fdchle interakt\u00edvne sledovanie ale v\u00e4\u010d\u0161inou posta\u010d\u00ed htop + iotop.<br><\/li>\n\n\n\n<li><strong>netdata:<\/strong> Netdata je modern\u00fd monitorovac\u00ed d\u00e9mon s webov\u00fdm rozhran\u00edm. D\u00e1 sa jednoducho nasadi\u0165 (jedn\u00fdm skriptom) a poskytuje <strong>prieh\u013eadn\u00e9 grafy<\/strong> CPU, pam\u00e4te, diskov\u00fdch oper\u00e1ci\u00ed, siet\u00ed a dokonca m\u00e1 <a href=\"https:\/\/gist.github.com\/dubcl\/c609061d64a6efaa691f54d6b6a6e5b4\">integr\u00e1ciu aj pre MySQL<\/a>. Cez netdata v prehliada\u010di vid\u00edte v re\u00e1lnom \u010dase grafy &#8211; napr\u00edklad vy\u0165a\u017eenie CPU jadier, mno\u017estvo I\/O oper\u00e1ci\u00ed na disk, po\u010det pripojen\u00ed k MySQL, po\u010det dopytov za sekundu, d\u013a\u017eku transak\u010dn\u00fdch logov a mno\u017estvo \u010fal\u0161\u00edch metr\u00edk. V\u00fdhodou je, \u017ee r\u00fdchlo spozorujete <strong>v\u00fdkyvy<\/strong> (spiky) \u2013 napr\u00edklad ak ka\u017ed\u00fa hodinu be\u017e\u00ed z\u00e1lohovac\u00ed skript a sp\u00f4sobuje prudk\u00fd n\u00e1rast diskovej z\u00e1\u0165a\u017ee, v grafe to uvid\u00edte. Netdata je teda skvel\u00fd pomocn\u00edk na <strong>vizualiz\u00e1ciu trendov<\/strong> v\u00fdkonu a odhalenie anom\u00e1li\u00ed.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-ht-blocks-messages wp-block-hb-message wp-block-hb-message--withicon is-style-success\"><strong>Pro tip:<\/strong> Ak vyu\u017e\u00edvate slu\u017ebu <a href=\"https:\/\/www.websupport.sk\/servery\/monitoring\/\">Websupport Monitoring<\/a>, tak syst\u00e9mov\u00e9 inform\u00e1cie o va\u0161ej datab\u00e1ze v\u00e1m vieme prida\u0165 do va\u0161ich dashboardov.<\/p>\n\n\n\n<p>Stru\u010dne povedan\u00e9, syst\u00e9mov\u00e9 n\u00e1stroje v\u00e1m pom\u00f4\u017eu <strong>ur\u010di\u0165, ktor\u00e1 \u010das\u0165 zdrojov je limituj\u00faca<\/strong>. Zist\u00edte, \u010di datab\u00e1ze doch\u00e1dza RAM (a za\u010dne swapova\u0165), \u010di nest\u00edha CPU (pln\u00e9 vyu\u017eitie jadier), alebo \u010di disky nest\u00edhaj\u00fa (vysok\u00e9 I\/O \u010dakacie doby). T\u00fdm nasmerujete va\u0161e \u010fal\u0161ie kroky spr\u00e1vnym smerom.<\/p>\n\n\n\n<p><strong>Datab\u00e1zov\u00e9 n\u00e1stroje (dopyty a intern\u00e9 metriky)<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Slow Query Log:<\/strong> MySQL\/MariaDB m\u00e1 vstavan\u00fa funkcionalitu logovania dopytov, ktor\u00fdch vykonanie presiahne ur\u010dit\u00fd \u010das. Tento <em>slow query log<\/em> je \u0161tandardne vypnut\u00fd \u2013 ve\u013emi odpor\u00fa\u010dame ho <strong>zapn\u00fa\u0165<\/strong> na ladenej datab\u00e1ze. V konfigur\u00e1cii (my.cnf) nastavte napr\u00edklad:<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-preformatted\">slow_query_log = ON<br><br>slow_query_log_file = \/var\/log\/mysql-slow.log<br><br>long_query_time = 2<br><br>log_queries_not_using_indexes = ON<\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>T\u00fdm zabezpe\u010d\u00edte, \u017ee v\u0161etky dopyty trvaj\u00face dlh\u0161ie ne\u017e 2 sekundy (hodnotu upravte pod\u013ea potreby) sa zap\u00ed\u0161u do uveden\u00e9ho log s\u00faboru. Vo\u013eba log_queries_not_using_indexes sp\u00f4sob\u00ed, \u017ee sa zaloguj\u00fa aj dopyty, ktor\u00e9 s\u00edce mo\u017eno netrvaj\u00fa 2s, ale nevyu\u017eili index (\u010do m\u00f4\u017ee indikova\u0165 neoptim\u00e1lny dopyt). Anal\u00fdzou slow logu potom zist\u00edte, <strong>ktor\u00e9 SQL dopyty s\u00fa najpomal\u0161ie alebo naj\u010dastej\u0161ie spoma\u013euj\u00fa syst\u00e9m<\/strong>. Log m\u00f4\u017eete prech\u00e1dza\u0165 ru\u010dne, alebo lep\u0161ie pou\u017ei\u0165 n\u00e1stroj ako <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/9.3\/en\/mysqldumpslow.html\">mysqldumpslow<\/a> (ktor\u00fd zhrnie podobn\u00e9 dopyty) \u010di pokro\u010dil\u00fd <a href=\"https:\/\/docs.percona.com\/percona-toolkit\/pt-query-digest.html\">pt-query-digest<\/a> z Percona Toolkitu (o \u0148om viac nesk\u00f4r). V\u00fdstupom bude zoznam dopytov zoraden\u00fd napr\u00edklad pod\u013ea celkov\u00e9ho \u010dasu, ko\u013ekokr\u00e1t sa vykonali, priemern\u00e9ho \u010dasu at\u010f. To s\u00fa cenn\u00e9 inform\u00e1cie \u2013 \u010dasto zist\u00edte, \u017ee napr\u00edklad jeden zabudnut\u00fd dopyt bez indexu sp\u00f4sobuje zna\u010dn\u00e9 za\u0165a\u017eenie.<br><\/li>\n\n\n\n<li><strong>SHOW PROCESSLIST \/ PERFORMANCE SCHEMA:<\/strong> Pri be\u017eiacej datab\u00e1ze m\u00f4\u017eete v MySQL pr\u00edkazom <code>SHOW FULL PROCESSLIST;<\/code> z\u00edska\u0165 preh\u013ead moment\u00e1lne be\u017eiacich dopytov a stavov. Uvid\u00edte napr\u00edklad \u010di nejak\u00e9 dopyty \u201evisia\u201c na lockoch, alebo trvaj\u00fa dlho (vo v\u00fdstupe je \u010das). To je u\u017eito\u010dn\u00e9 pre <em>aktu\u00e1lne<\/em> probl\u00e9my. Pre dlhodobej\u0161ie sledovanie v\u0161ak posl\u00fa\u017ei pr\u00e1ve slow log. Modern\u00e9 MySQL (8.0) m\u00e1 tie\u017e <strong>Performance Schema<\/strong>, ktor\u00e9 zbiera detailn\u00e9 \u0161tatistiky o v\u00fdkone (\u010dasy \u010dakania na lock, I\/O, CPU \u010das dopytov at\u010f.). Je to v\u0161ak pomerne komplexn\u00fd n\u00e1stroj \u2013 d\u00e1ta s\u00fa dostupn\u00e9 v \u0161peci\u00e1lnych tabu\u013ek\u00e1ch (schema performance_schema a poh\u013eady v sys sch\u00e9me). Pre systematick\u00fa anal\u00fdzu m\u00f4\u017eete pou\u017ei\u0165 napr. <strong>MySQL Workbench Performance Reports<\/strong> alebo pr\u00edkazy v sys sch\u00e9me, ktor\u00e9 \u0165a\u017eia z performance schema. Pre za\u010diatok si vysta\u010d\u00edte so zapnut\u00edm slow logu a z\u00e1kladn\u00fdmi statusmi.<br><\/li>\n\n\n\n<li><strong>SHOW GLOBAL STATUS a SHOW VARIABLES:<\/strong> Tieto pr\u00edkazy zobrazia mno\u017estvo intern\u00fdch <strong>\u010d\u00edseln\u00fdch metr\u00edk<\/strong> (po\u010det vykonan\u00fdch dopytov, po\u010det otvoren\u00ed tabuliek, hit-rate cache a pod.) a hodnoty aktu\u00e1lnych nastaven\u00ed. Ide\u00e1lne je nasadi\u0165 si n\u00e1stroj ako <strong>MySQL\/MariaDB monitor<\/strong> (napr. Netdata, Percona PMM, Zabbix templaty a pod.), ktor\u00fd bude tieto metriky periodicky \u010d\u00edta\u0165 a grafova\u0165. Ak tak\u00fd n\u00e1stroj nem\u00e1te, m\u00f4\u017eete ru\u010dne z \u010dasu na \u010das pozrie\u0165 vybran\u00e9 hodnoty. Napr\u00edklad hodnota Threads_connected povie po\u010det aktu\u00e1lnych pripojen\u00ed, Innodb_buffer_pool_pages_free a podobn\u00e9 zase vyu\u017eitie InnoDB buffer poolu, Created_tmp_disk_tables po\u010det do\u010dasn\u00fdch tabuliek na disku (vysok\u00e9 \u010d\u00edslo nazna\u010d\u00ed, \u017ee DB \u010dasto vytv\u00e1ra do\u010dasn\u00e9 tabu\u013eky na disku, mo\u017eno treba zv\u00fd\u0161i\u0165 tmp_table_size), at\u010f. Manu\u00e1l MySQL popisuje stovky tak\u00fdch metr\u00edk. Je nad r\u00e1mec tohto \u010dl\u00e1nku prech\u00e1dza\u0165 v\u0161etky \u2013 sk\u00f4r odpor\u00fa\u010dame pou\u017ei\u0165 na to n\u00e1stroje (mysqltuner, pt-mysql-summary, netdata), ktor\u00e9 tieto \u010d\u00edsla <strong>zinterpretuj\u00fa<\/strong> za v\u00e1s.<\/li>\n<\/ul>\n\n\n\n<p>Na z\u00e1ver k monitoringu \u2013 <strong>monitorujte DB priebe\u017ene a aj po aplikovan\u00ed zmien<\/strong>. Optimaliz\u00e1cia je neust\u00e1ly proces. Uistite sa, \u017ee m\u00e1te nastaven\u00e9 nejak\u00e9 alerty (\u010di u\u017e vlastn\u00e9 skripty, Zabbix, Netdata alarmy, alebo aspo\u0148 pravideln\u00e9 kontroly), ktor\u00e9 v\u00e1s upozornia na <em>nezvy\u010dajn\u00e9 stavy<\/em> (napr. v\u00fdpadok MySQL, extr\u00e9mne vysok\u00e9 load, doch\u00e1dzaj\u00face miesto na disku s datab\u00e1zami a pod.). Tieto veci s\u00edce priamo nezlep\u0161ia v\u00fdkon, ale pom\u00f4\u017eu v\u00e1m udr\u017ea\u0165 datab\u00e1zu v kond\u00edcii a r\u00fdchlo reagova\u0165, ke\u010f sa nie\u010do deje.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Anal\u00fdza konfigur\u00e1cie<\/strong><\/h2>\n\n\n\n<p>Ke\u010f u\u017e viete, kde s\u00fa slabiny (z monitoringu), prejdite k <strong>anal\u00fdze s\u00fa\u010dasnej konfigur\u00e1cie<\/strong> MySQL\/MariaDB. Konfigur\u00e1cia sa typicky nach\u00e1dza v s\u00fabore my.cnf alebo v adres\u00e1ri \/etc\/mysql\/mysql.conf.d\/ (z\u00e1le\u017e\u00ed od distrib\u00facie). U\u017e vieme, \u017ee predvolen\u00e9 nastavenia servera \u010dasto <strong>nie s\u00fa ide\u00e1lne pre v\u00fdkon<\/strong> \u2013 napr\u00edklad MySQL 8.0 u\u017e m\u00e1 lep\u0161ie defaulty ne\u017e star\u0161ie verzie, ale st\u00e1le m\u00f4\u017ee by\u0165 potrebn\u00e9 ich doladi\u0165 pre v\u00e1\u0161 server.<br><\/p>\n\n\n\n<p>Postupujte takto:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Zistite aktu\u00e1lne nastavenia:<\/strong> Pomocou pr\u00edkazu SHOW VARIABLES; v MySQL klientovi (alebo mysqladmin variables) z\u00edskate zoznam v\u0161etk\u00fdch syst\u00e9mov\u00fdch premenn\u00fdch a ich hodn\u00f4t. Tieto zah\u0155\u0148aj\u00fa aj implicitn\u00e9 defaulty aj hodnoty na\u010d\u00edtan\u00e9 z config s\u00faborov. U\u017eito\u010dn\u00e9 je filtrova\u0165 pod\u013ea k\u013e\u00fa\u010dov\u00e9ho slova, napr\u00edklad:<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-preformatted\">SHOW VARIABLES LIKE 'innodb%';<br><br>SHOW VARIABLES LIKE 'max_connections';<br><br>SHOW VARIABLES LIKE 'cache%';<\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>T\u00fdmto sp\u00f4sobom viete r\u00fdchlo n\u00e1js\u0165, ak\u00e9 hodnoty m\u00e1 konkr\u00e9tny parameter, ktor\u00fd idete ladi\u0165, pr\u00edpadne \u010di nie je vypnut\u00fd (OFF) at\u010f.<br><\/li>\n\n\n\n<li><strong>Identifikujte hlavn\u00e9 parametre na zmenu:<\/strong> Ni\u017e\u0161ie sa venujeme konkr\u00e9tnym d\u00f4le\u017eit\u00fdm nastaveniam. Pri anal\u00fdze si v\u0161imnite napr\u00edklad hodnotu innodb_buffer_pool_size (\u010di je primeran\u00e1 ve\u013ekosti RAM), hodnoty t\u00fdkaj\u00face sa <strong>cache<\/strong> (napr. table_open_cache, query_cache_size \u2013 ak existuje), nastavenie flushovania (innodb_flush_log_at_trx_commit, sync_binlog), po\u010det povolen\u00fdch pripojen\u00ed (max_connections), at\u010f. Porovnajte ich s odpor\u00fa\u010daniami.<br><\/li>\n\n\n\n<li><strong>Kontrola konfigura\u010dn\u00e9ho s\u00faboru:<\/strong> Otvorte my.cnf a pozrite, \u010do je v \u0148om u\u017e nastaven\u00e9. Neraz sa st\u00e1va, \u017ee distrib\u00facia linuxu alebo hosting tam m\u00e1 nejak\u00e9 default hodnoty (\u010dasto zakomentovan\u00e9), alebo \u017ee sa uplat\u0148uj\u00fa <strong>viacer\u00e9 s\u00fabory<\/strong> (napr. \/etc\/mysql\/conf.d\/ m\u00f4\u017ee obsahova\u0165 \u010fal\u0161ie .cnf fragmenty). Uistite sa, \u017ee upravujete spr\u00e1vny s\u00fabor a \u017ee po re\u0161tarte sa hodnoty naozaj menia (over\u00edte op\u00e4\u0165 cez SHOW VARIABLES). Odpor\u00fa\u010da sa vies\u0165 si <strong>verzionovanie<\/strong> konfigur\u00e1cie \u2013 t.j. z\u00e1lohova\u0165 si p\u00f4vodn\u00fd stav (napr. my.cnf.orig) a ka\u017ed\u00fa zmenu komentova\u0165, aby ste vedeli, \u010do ste menili.<br><\/li>\n\n\n\n<li><strong>Pochopte aktu\u00e1lne vyu\u017eitie:<\/strong> Okrem statickej konfigur\u00e1cie zistite aj, ako je syst\u00e9m vyu\u017e\u00edvan\u00fd. Napr\u00edklad ak Threads_connected (po\u010det pripojen\u00ed) be\u017ene dosahuje 100 a max_connections m\u00e1te 150, ste v bezpe\u010dnej z\u00f3ne; ak by v\u0161ak Threads_connected \u010dasto bl\u00ed\u017eil k 150, znamen\u00e1 to, \u017ee aplik\u00e1cia otv\u00e1ra ve\u013ea spojen\u00ed a mo\u017eno treba zv\u00fd\u0161i\u0165 limit (alebo optimalizova\u0165 aplik\u00e1ciu). Podobne Innodb_buffer_pool_pages_free v\u00e1m povie, \u010di buffer pool (cache InnoDB) m\u00e1 vo\u013en\u00e9 miesto, alebo je pln\u00fd (a teda by v\u00e4\u010d\u0161\u00ed pool mo\u017eno pomohol). Tieto \u0161tatistiky z\u00edskate cez SHOW GLOBAL STATUS a z Performance Schema. Na interpret\u00e1ciu je dobr\u00e9 pou\u017ei\u0165 n\u00e1stroj <strong>MySQLTuner<\/strong> alebo <strong>Percona pt-variable-advisor<\/strong>, ktor\u00e9 upozornia na o\u010dividn\u00e9 nedostatky (napr. \u017ee pr\u00edli\u0161 ve\u013ea tabuliek sa otv\u00e1ralo \u2013 mo\u017eno mal\u00fd table_open_cache at\u010f.).<\/li>\n<\/ul>\n\n\n\n<p>Na z\u00e1klade anal\u00fdzy konfigur\u00e1cie a spr\u00e1vania sa DB si spravte pl\u00e1n, ktor\u00e9 parametre idete meni\u0165. \u010ealej si prejdeme najd\u00f4le\u017eitej\u0161ie okruhy nastaven\u00ed a odpor\u00fa\u010dania.<br><\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>D\u00f4le\u017eit\u00e9 parametre InnoDB<\/strong><\/h2>\n\n\n\n<p>V\u00e4\u010d\u0161ina modern\u00fdch in\u0161tal\u00e1ci\u00ed MySQL\/MariaDB pou\u017e\u00edva \u00falo\u017en\u00fd engine <strong>InnoDB<\/strong> (defaultn\u00fd od MySQL 5.5). InnoDB m\u00e1 mno\u017estvo konfigur\u00e1ci\u00ed, no z h\u013eadiska v\u00fdkonu vynik\u00e1 <strong>nieko\u013eko k\u013e\u00fa\u010dov\u00fdch parametrov<\/strong>, ktor\u00e9 maj\u00fa najv\u00e4\u010d\u0161\u00ed dopad. Ni\u017e\u0161ie uv\u00e1dzame tie najd\u00f4le\u017eitej\u0161ie, ktor\u00e9 by mal admin pozna\u0165 a nastavi\u0165:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>innodb_buffer_pool_size:<\/strong> Jedno z najd\u00f4le\u017eitej\u0161\u00edch nastaven\u00ed v\u00f4bec. Ur\u010duje ve\u013ekos\u0165 <strong>buffer poolu<\/strong> \u2013 \u010do je pam\u00e4\u0165ov\u00e1 cache, ktor\u00fa InnoDB pou\u017e\u00edva na ukladanie d\u00e1tov\u00fdch a indexov\u00fdch str\u00e1nok. V podstate plat\u00ed, \u017ee \u010d\u00edm v\u00e4\u010d\u0161\u00ed buffer pool, t\u00fdm viac datab\u00e1zov\u00fdch d\u00e1t sa zmest\u00ed do RAM a t\u00fdm menej mus\u00ed server \u010d\u00edta\u0165 z disku pri dopytoch. Odpor\u00fa\u010dania hovoria alokova\u0165 pribli\u017ene <strong>50\u201375% dostupnej RAM<\/strong> pre InnoDB buffer pool (ak datab\u00e1zov\u00fd server be\u017e\u00ed s\u00e1m na danom stroji a nie s\u00fa tam in\u00e9 pam\u00e4\u0165ovo n\u00e1ro\u010dn\u00e9 slu\u017eby). Na 16 GB RAM serveri teda napr\u00edklad 10\u201312 GB. MySQL 8.0 vie dynamicky meni\u0165 buffer pool (premenn\u00e1 je dynamick\u00e1), ale i tak ju nastavte v konfigur\u00e1cii napevno. MariaDB a Percona to maj\u00fa rovnako. <\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-ht-blocks-messages wp-block-hb-message wp-block-hb-message--withicon is-style-danger\"><strong>Pozor:<\/strong> ak hostujete viac slu\u017eieb na serveri (napr. webov\u00fd server), nechajte im tie\u017e pam\u00e4\u0165 \u2013 nedajte cel\u00fdch 75% ak by inak OS swapoval. Buffer pool rad\u0161ej mierne nadhodno\u0165te ne\u017e podhodno\u0165te \u2013 ak je pr\u00edli\u0161 mal\u00fd, syst\u00e9m bude neust\u00e1le na\u010d\u00edtava\u0165 str\u00e1nky z disku do RAM (cache miss), \u010do je pomal\u00e9. Naopak pr\u00edli\u0161 ve\u013ek\u00fd buffer pool (bl\u00ed\u017eiaci sa 90%+ RAM) m\u00f4\u017ee vytla\u010di\u0165 OS cache a in\u00e9 procesy do swapu. Treba n\u00e1js\u0165 rozumn\u00fd balans. Ak va\u0161a <strong>datab\u00e1za je men\u0161ia ne\u017e RAM<\/strong>, m\u00f4\u017eete buffer_pool_size nastavi\u0165 pribli\u017ene na ve\u013ekos\u0165 DB (pr\u00edpadne o nie\u010do viac, ak prib\u00fadaj\u00fa d\u00e1ta), nem\u00e1 zmysel d\u00e1va\u0165 n\u00e1sobne viac ne\u017e ve\u013ekos\u0165 d\u00e1t \u2013 nevyu\u017eilo by sa to.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>innodb_log_file_size (resp. v MySQL 8.0.30+ parameter innodb_redo_log_capacity):<\/strong> Toto nastavenie ur\u010duje <strong>ve\u013ekos\u0165 redo log s\u00faborov<\/strong> InnoDB (ka\u017ed\u00fd InnoDB m\u00e1 tzv. tranzak\u010dn\u00fd log, kam zapisuje zmeny pred ich aplik\u00e1ciou do d\u00e1tov\u00fdch s\u00faborov). Ve\u013ekos\u0165 logu ovplyv\u0148uje <strong>v\u00fdkon z\u00e1pisu<\/strong> a tie\u017e <strong>\u010das obnovy po p\u00e1de<\/strong>. <strong>V\u00e4\u010d\u0161\u00ed log<\/strong> umo\u017e\u0148uje InnoDB zapisova\u0165 zmeny sekven\u010dne do logu a d\u00e1ta flushova\u0165 na disk menej \u010dasto, \u010do zlep\u0161uje priepustnos\u0165 z\u00e1pisov \u2013 pr\u00edli\u0161 mal\u00fd log m\u00f4\u017ee brzdi\u0165 v\u00fdkon pri intenz\u00edvnych z\u00e1pisoch, preto\u017ee sa log \u010dasto zapln\u00ed a InnoDB mus\u00ed \u010daka\u0165 na flush. Na druhej strane <strong>pr\u00edli\u0161 ve\u013ek\u00fd log<\/strong> znamen\u00e1, \u017ee ak datab\u00e1za spadne, bude trva\u0165 dlh\u0161ie prejs\u0165 cel\u00fd log a aplikova\u0165 zmeny (recovery). Dobr\u00e1 prax je nastavi\u0165 relat\u00edvne ve\u013ek\u00fd log, ktor\u00fd pokryje napr. aspo\u0148 <strong>1 hodinu z\u00e1pisov<\/strong> (z\u00e1vis\u00ed od va\u0161ej z\u00e1\u0165a\u017ee). Konkr\u00e9tne \u010d\u00edsla: \u010dasto sa odpor\u00fa\u010da okolo <strong>512 MB a\u017e 2 GB<\/strong> na jeden log s\u00fabor (a zvy\u010dajne s\u00fa 2 s\u00fabory \u2013 v MySQL 8+ ich m\u00f4\u017ee by\u0165 viac). V\u0161imnite si, \u017ee od MySQL 8.0.30 je parameter innodb_log_file_size ozna\u010den\u00fd ako zastaran\u00fd a nahraden\u00fd innodb_redo_log_capacity , ktor\u00fd ur\u010duje celkov\u00fa kapacitu redo logov. Ka\u017edop\u00e1dne, odpor\u00fa\u010danie je: <strong>zv\u00e4\u010d\u0161i\u0165 v\u00fdchoz\u00ed mal\u00fd log<\/strong> (ktor\u00fd b\u00fdval napr. 50 MB) na stovky MB. Percona uv\u00e1dza, \u017ee <strong>dostato\u010dne ve\u013ek\u00e9 logy s\u00fa k\u013e\u00fa\u010dov\u00e9 pre stabiln\u00fd v\u00fdkon z\u00e1pisu<\/strong>, av\u0161ak so zvy\u0161uj\u00facou ve\u013ekos\u0165ou rastie \u010das recovery . Ak m\u00e1te 16GB RAM server, log v stovk\u00e1ch MB nebude probl\u00e9m. <\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-ht-blocks-messages wp-block-hb-message wp-block-hb-message--withicon is-style-danger\"><strong>Pozor: Zmena tohto parametra vy\u017eaduje re\u0161tart MySQL a \u0161peci\u00e1lny postup<\/strong> \u2013 mus\u00edte vypn\u00fa\u0165 server, z\u00e1lohova\u0165\/odstr\u00e1ni\u0165 star\u00e9 log s\u00fabory (ib_logfile* v datadir), nastavi\u0165 nov\u00fa hodnotu v my.cnf, potom zapn\u00fa\u0165 server, aby vytvoril nov\u00e9 logy. Bez tohto postupu by server nemusel na\u0161tartova\u0165 (ak zist\u00ed nes\u00falad ve\u013ekost\u00ed logu). Preto t\u00fato zmenu robte ve\u013emi opatrne a ide\u00e1lne v \u00fadr\u017ebovom okne.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>innodb_flush_log_at_trx_commit<\/strong><strong>:<\/strong> T\u00e1to vo\u013eba riadi, ako \u010dasto sa obsah tranzak\u010dn\u00e9ho logu flushuje (z\u00e1pis na disk). Predvolen\u00e1 hodnota 1 znamen\u00e1, \u017ee <strong>pri ka\u017edom commite transakcie<\/strong> InnoDB flushne (zap\u00ed\u0161e) log na disk \u2013 to zaru\u010duje maxim\u00e1lnu trvanlivos\u0165 (durability), ale generuje ve\u013ea sync oper\u00e1ci\u00ed na disk. Nastavenie 0 alebo 2 zlep\u0161uje v\u00fdkon z\u00e1pisov t\u00fdm, \u017ee neflushuje pri ka\u017edom commite (pri 2 zapisuje do logu na konci ka\u017edej sekundy, ale nevol\u00e1 fsync na disk pri ka\u017edom commite; pri 0 dokonca nep\u00ed\u0161e na disk pri commitoch v\u00f4bec, loguje len do pam\u00e4te a raz za sekundu flushne). Rozdiel medzi 0 a 2 je v \u00farovni risku pri p\u00e1de: 2 zaru\u010d\u00ed, \u017ee transakcie sa zap\u00ed\u0161u do OS cache na disk (ale nevyvol\u00e1 fsync), tak\u017ee ak spadne OS, pr\u00eddu sa o 1s transakci\u00ed; pri 0 sa m\u00f4\u017ee strati\u0165 aj cel\u00e1 1s d\u00e1vka. Mnoho nasaden\u00ed, kde nevad\u00ed potenci\u00e1lna strata posledn\u00fdch ~1 sekundy transakci\u00ed pri hav\u00e1rii, vol\u00ed innodb_flush_log_at_trx_commit=2 pre v\u00fdrazne vy\u0161\u0161\u00ed v\u00fdkon z\u00e1pisu (najm\u00e4 na pomal\u0161\u00edch diskoch alebo pri ve\u013ekom mno\u017estve mal\u00fdch transakci\u00ed). <strong>Oracle MySQL default je 1<\/strong> (bezpe\u010dnos\u0165), MariaDB default je tie\u017e 1. Zv\u00e1\u017ete teda zmenu na 2, ak potrebujete v\u00fdkon a ak aplik\u00e1cia vydr\u017e\u00ed teoretick\u00fa stratu drobn\u00fdch d\u00e1t pri p\u00e1de. Hodnotu 0 neodpor\u00fa\u010dame v produkcii, t\u00e1 je sk\u00f4r na testy (znamen\u00e1 \u00fapln\u00e9 vypnutie flush pri commitoch).<br><\/li>\n\n\n\n<li><strong>innodb_flush_method<\/strong><strong>:<\/strong> Met\u00f3da flushovania na disk. Pre klasick\u00e9 disky b\u00fdvalo default fsync (alebo fdatasync). Pre SSD sa \u010dasto odpor\u00fa\u010da pou\u017ei\u0165 O_DIRECT, \u010d\u00edm sa vynech\u00e1 cache opera\u010dn\u00e9ho syst\u00e9mu pri I\/O (InnoDB bude zapisova\u0165 priamo na disk, nebud\u00fa sa duplikova\u0165 str\u00e1nky v OS cache). V\u00fdhoda O_DIRECT je, \u017ee nech\u00e1 cache mana\u017ement na InnoDB (buffer pool) a nezahlcuje OS cache ve\u013ek\u00fdmi sekven\u010dn\u00fdmi I\/O z flushovania. Modern\u00e9 MySQL u\u017e m\u00e1 default \u010dasto O_DIRECT pre InnoDB na Linuxe, ale rad\u0161ej si to na va\u0161om OS preverte. Na SSD ur\u010dite pou\u017e\u00edvajte O_DIRECT (pr\u00edpadne v kombin\u00e1cii s O_DSYNC na logy). Parameter sa nastavuje v my.cnf, napr: innodb_flush_method = O_DIRECT. V\u00fdsledkom je predov\u0161etk\u00fdm zn\u00ed\u017eenie za\u0165a\u017eenia OS cache a potenci\u00e1lne o \u010dosi lep\u0161ia konzistencia v\u00fdkonu pri ve\u013ek\u00fdch flush oper\u00e1ci\u00e1ch.<br><\/li>\n\n\n\n<li><strong>innodb_buffer_pool_instances<\/strong><strong>:<\/strong> Ak m\u00e1 InnoDB buffer pool dostato\u010dne ve\u013ek\u00fa ve\u013ekos\u0165 (napr. nad 1GB), odpor\u00fa\u010da sa ho rozdeli\u0165 na viacer\u00e9 \u201einstances\u201c \u2013 aby sa zmiernila konkurencia (locks) pri pr\u00edstupe z viacer\u00fdch vl\u00e1kien. Default je 1 (cel\u00fd pool ako jeden celok). MySQL aj MariaDB umo\u017e\u0148uj\u00fa nastavi\u0165 napr. 8 instanci\u00ed. Prakticky ak m\u00e1te buffer pool napr. 8GB, dajte innodb_buffer_pool_instances = 8 (\u010d\u00edm vznikne 8 blokov po 1GB). T\u00fdm sa zlep\u0161ia paraleln\u00e9 pr\u00edstupy. Pri men\u0161\u00edch ve\u013ekostiach (pod ~1GB) nem\u00e1 zmysel deli\u0165. Tento parameter sta\u010d\u00ed nastavi\u0165 a re\u0161tartn\u00fa\u0165 server (je to statick\u00e1 vo\u013eba).<br><\/li>\n\n\n\n<li><strong>innodb_io_capacity<\/strong><strong> a <\/strong><strong>innodb_io_capacity_max<\/strong><strong>:<\/strong> Tieto parametre ovplyv\u0148uj\u00fa, akou r\u00fdchlos\u0165ou vykon\u00e1va InnoDB <strong>pozadia \u010dinnosti<\/strong> ako flush dirty pages (z\u00e1pis upraven\u00fdch str\u00e1nok z buffer poolu na disk) a zl\u00fa\u010denie change bufferu. innodb_io_capacity definuje pribli\u017en\u00fd po\u010det I\/O oper\u00e1ci\u00ed za sekundu, ktor\u00e9 m\u00e1 InnoDB v pozad\u00ed vyu\u017e\u00edva\u0165 . P\u00f4vodne bol default len 200 (\u010do zodpoved\u00e1 cca be\u017en\u00e9mu HDD). <strong>Pre SSD disky<\/strong> by mal by\u0165 t\u00e1to hodnota v\u00fdrazne vy\u0161\u0161ia, preto\u017ee SSD zvl\u00e1dnu stovky a\u017e tis\u00edce IOPS. Odpor\u00fa\u010da sa nastavi\u0165 pribli\u017ene na \u00farove\u0148, ak\u00fa disk zvl\u00e1dne \u2013 napr. pre be\u017en\u00e9 SSD m\u00f4\u017ee by\u0165 nieko\u013eko tis\u00edc. Typicky <strong>pre SSD vo\u013ete stovky a\u017e n\u00edzke tis\u00edce<\/strong> (napr. 1000). Av\u0161ak pozor: ak d\u00e1te zbyto\u010dne privysok\u00e9 \u010d\u00edslo, InnoDB m\u00f4\u017ee a\u017e prehnane vypr\u00e1zd\u0148ova\u0165 cache (flushne str\u00e1nky sk\u00f4r, ne\u017e to je nutn\u00e9) a t\u00fdm zn\u00ed\u017ei\u0165 efektivitu cache. Odpor\u00fa\u010da sa hodnotu dr\u017ea\u0165 <strong>\u010do najni\u017e\u0161iu, ale nie tak n\u00edzku, aby doch\u00e1dzalo k periodic\u00fdm v\u00fdkyvom v\u00fdkonu<\/strong> ke\u010f flush nest\u00edha a naraz dobieha zame\u0161kan\u00e9 . Praktick\u00fd postup: ak vid\u00edte, \u017ee v SHOW ENGINE INNODB STATUS narast\u00e1 unflushed modified pages a ob\u010das to sp\u00f4sob\u00ed z\u00e1\u0165a\u017e (checkpointy), m\u00f4\u017eete zv\u00fd\u0161i\u0165 io_capacity. innodb_io_capacity_max je horn\u00fd strop pri ur\u010dit\u00fdch situ\u00e1ci\u00e1ch (default ~2000 ak capacity 200). M\u00f4\u017eete ho nastavi\u0165 napr. 2x capacity. Napr\u00edklad: pre n\u00e1\u0161 4vCPU\/SSD server napr. innodb_io_capacity=1000 a innodb_io_capacity_max=2000 m\u00f4\u017ee by\u0165 dobr\u00fd \u0161tart.&nbsp;<\/li>\n\n\n\n<li><strong>\u010eal\u0161ie InnoDB parametre:<\/strong> Existuj\u00fa aj \u010fal\u0161ie, ako napr. innodb_purge_threads (po\u010det vl\u00e1kien pre purge oper\u00e1cie, default 4 v nov\u0161\u00edch verzi\u00e1ch \u2013 zvy\u010dajne netreba meni\u0165), innodb_max_dirty_pages_pct (ko\u013eko % buffer poolu m\u00f4\u017ee by\u0165 \u201e\u0161pinav\u00fdch\u201c \u2013 default okolo 90, ak chcete agres\u00edvnej\u0161ie flushova\u0165, m\u00f4\u017eete zn\u00ed\u017ei\u0165 na napr. 75, ale v\u00e4\u010d\u0161inou netreba), innodb_flush_neighbors (\u010di flushova\u0165 susediace str\u00e1nky na disku \u2013 pri SSD <strong>vypnut\u00e9<\/strong> by default, ke\u010f\u017ee nem\u00e1 zmysel flushova\u0165 sekven\u010dne, na HDD to bolo zapnut\u00e9 \u2013 skontrolujte, malo by by\u0165 OFF). Tie\u017e innodb_adaptive_hash_index (default ON \u2013 adapt\u00edvny hash index m\u00f4\u017ee zr\u00fdchli\u0165 niektor\u00e9 lookupy, vo vz\u00e1cnych pr\u00edpadoch v\u0161ak m\u00f4\u017ee by\u0165 bottleneck pri extr\u00e9mnej konkurecii \u2013 ak by ste videli v profile CPU, \u017ee ve\u013ea \u010dasu tr\u00e1vi v futex kv\u00f4li adaptive hash latch, m\u00f4\u017eete zv\u00e1\u017ei\u0165 vypnutie). Tieto v\u0161ak presahuj\u00fa \u00farove\u0148 be\u017en\u00e9ho ladenia a spom\u00edname ich len pre \u00faplnos\u0165. V\u00e4\u010d\u0161inou netreba zasahova\u0165.<\/li>\n<\/ul>\n\n\n\n<p><strong>InnoDB<\/strong> by ste mali nastavi\u0165 tak, aby vyu\u017eila dostupn\u00fa pam\u00e4\u0165 (buffer pool), mala primerane ve\u013ek\u00e9 logy pre plynul\u00fd z\u00e1pis a flushovacie parametre prisp\u00f4soben\u00e9 typu \u00falo\u017eiska (SSD vs HDD) a po\u017eiadavk\u00e1m na trvanlivos\u0165 d\u00e1t. Tieto nastavenia spravidla prines\u00fa najv\u00fdraznej\u0161ie zlep\u0161enie v\u00fdkonu datab\u00e1zy.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Optimaliz\u00e1cia I\/O a SSD<\/strong><\/h2>\n\n\n\n<p>Pre v\u00fdkon datab\u00e1zy je <strong>diskov\u00e9 \u00falo\u017eisko kritick\u00e9<\/strong>, hlavne ak d\u00e1ta nevojd\u00fa cel\u00e9 do pam\u00e4te. V dne\u0161nej dobe sa u\u017e takmer v\u017edy pou\u017e\u00edva SSD (SATA alebo r\u00fdchlej\u0161ie NVMe) na datab\u00e1zov\u00e9 servery, ke\u010f\u017ee v\u00fdrazne prekon\u00e1vaj\u00fa klasick\u00e9 HDD v I\/O oper\u00e1ci\u00e1ch za sekundu. Av\u0161ak aj pri SSD existuj\u00fa \u0161pecifik\u00e1:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>I\/O scheduler a s\u00faborov\u00fd syst\u00e9m:<\/strong> Na \u00farovni OS Linux sa uistite, \u017ee m\u00e1te vhodn\u00fd I\/O scheduler. Pre SSD sa typicky odpor\u00fa\u010da noop alebo deadline namiesto star\u0161ieho cfq. Nov\u00e9 jadr\u00e1 s BFQ\/CFQ si aj tak poradia, ale ak viete nastavi\u0165 scheduler na noop (\u017eiadne zbyto\u010dn\u00e9 radenie I\/O \u2013 SSD to nepotrebuje), spravte tak. S\u00faborov\u00fd syst\u00e9m \u2013 ext4 alebo XFS s\u00fa oba v pohode. Ext4 je vo v\u0161eobecnosti dobr\u00e1 vo\u013eba, XFS funguje tie\u017e dobre, najm\u00e4 pri v\u00e4\u010d\u0161\u00edch s\u00faboroch (InnoDB data). ZFS sa na Linuxe d\u00e1 pou\u017ei\u0165 ak potrebujete snapshoty, ale m\u00e1 vlastn\u00fa re\u017eiu \u2013 to je na zv\u00e1\u017eenie a v pr\u00edpade VPS asi nie ve\u013emi dostupn\u00e9 rie\u0161enie.<br><\/li>\n\n\n\n<li><strong>innodb_flush_method = O_DIRECT<\/strong><strong>:<\/strong> Ako spom\u00edname vy\u0161\u0161ie, toto nastavte pre SSD, aby ste zabr\u00e1nili dvojn\u00e1sobn\u00e9mu ke\u0161ovaniu a pr\u00edpadn\u00fdm \u201ewrite burstom\u201c, ke\u010f OS cache zrazu flushne ve\u013ea d\u00e1t naraz. S O_DIRECT InnoDB priebe\u017ene flushuje priamo na disk. To m\u00f4\u017ee mierne zv\u00fd\u0161i\u0165 latenciu jednotliv\u00fdch z\u00e1pisov, ale zn\u00ed\u017ei sa kol\u00edsanie v\u00fdkonu. U SSD to celkovo vedie k predv\u00eddate\u013enej\u0161iemu spr\u00e1vaniu.<br><\/li>\n\n\n\n<li><strong>innodb_flush_neighbors = 0:<\/strong> Overte, \u017ee je vypnut\u00e9 (MySQL 8 m\u00e1 default 0). Toto zabezpe\u010d\u00ed, \u017ee InnoDB pri flushnut\u00ed str\u00e1nky nebude e\u0161te prech\u00e1dza\u0165 susedn\u00e9 str\u00e1nky na disku \u2013 pri SSD to nem\u00e1 v\u00fdznam (\u017eiadne hlavi\u010dky diskov tu neh\u013eadaj\u00fa susedn\u00e9 sektory), tak\u017ee u\u0161etr\u00ed sa zbyto\u010dn\u00e1 I\/O z\u00e1\u0165a\u017e.<br><\/li>\n\n\n\n<li><strong>Ve\u013ekos\u0165 str\u00e1nky a alignement:<\/strong> InnoDB m\u00e1 default 16KB str\u00e1nku. V\u00e4\u010d\u0161ina SSD operuje s 4KB sektormi (intern\u00e9 page). To je v poriadku. Mo\u017eno ste po\u010duli o mo\u017enosti nastavi\u0165 men\u0161iu InnoDB page size 4KB pre ur\u010dit\u00e9 workloady. To je u\u017e ve\u013emi pokro\u010dil\u00e9 \u2013 be\u017ene sa to nerob\u00ed, 16KB je osved\u010den\u00fd default. Podobne align partition na SSD na 1MB boundary \u2013 to v\u0161etko dnes OS in\u0161tal\u00e1tory robia spr\u00e1vne, nemus\u00edte to extra rie\u0161i\u0165.<br><\/li>\n\n\n\n<li><strong>RAID a kontrol\u00e9ry:<\/strong> Ak pou\u017e\u00edvate RAID (napr. RAID10), overte, \u017ee radi\u010d nie je \u00fazke hrdlo. Napr\u00edklad enterprise RAID radi\u010d by mal ma\u0165 vyrovn\u00e1vaciu pam\u00e4\u0165 (BBU \u2013 battery backed cache) a nemal by obmedzova\u0165 priepustnos\u0165 SSD. V prostred\u00ed VPS toto ovplyvni\u0165 neviete, to je na poskytovate\u013eovi.<br><\/li>\n\n\n\n<li><strong>Sledovanie I\/O wait:<\/strong> Aj ke\u010f SSD s\u00fa r\u00fdchle, nekone\u010dn\u00e9 nie s\u00fa. M\u00f4\u017eu zvl\u00e1dnu\u0165 napr. 100k IOPS, no ak ich vy\u0165a\u017eujete prudk\u00fdmi z\u00e1pismi (napr. flush 1GB d\u00e1t), na moment saturuj\u00fa. To sa prejav\u00ed v htope ako I\/O wait, pr\u00edpadne v metrik\u00e1ch ako narastaj\u00faci Innodb_buffer_pool_wait_free (ak by buffer pool musel \u010daka\u0165 na flush aby uvo\u013enil miesto). Sledujte tieto indik\u00e1tory. Rie\u0161en\u00edm m\u00f4\u017ee by\u0165 zv\u00fd\u0161enie innodb_io_capacity (nech flushuje viac priebe\u017ene), alebo nav\u00fd\u0161enie innodb_log_file_size (nech sa flushuje menej \u010dasto), alebo proste zlep\u0161enie dopytov, aby to\u013eko nezapisovali.<br><\/li>\n\n\n\n<li><strong>Trim (TRIM\/Discard):<\/strong> Uistite sa, \u017ee SSD dost\u00e1va TRIM pr\u00edkazy, aby mohol interne spravova\u0165 opotrebovanie. Ak m\u00e1te filesystem s discard mount option, TRIMuje priebe\u017ene. Ak nie, sp\u00fa\u0161\u0165ajte fstrim (napr. raz t\u00fd\u017edenne cez cron) na diskov\u00e9 oddiely s DB. Toto nesp\u00f4sob\u00ed priamo r\u00fdchlos\u0165 dopytov, ale dlhodobo udr\u017euje SSD v\u00fdkon (inak ke\u010f sa SSD zapln\u00ed, bez TRIMu m\u00f4\u017ee spomali\u0165 z\u00e1pisy).<br><\/li>\n\n\n\n<li><strong>Nebojte sa vy\u0165a\u017ei\u0165 SSD:<\/strong> SSD s\u00fa stavan\u00e9 na pomerne vysok\u00e9 opotrebenie (hlavne tie kvalitnej\u0161ie). Napr. parametre ako TBW (terabytes written) b\u00fdvaj\u00fa vysok\u00e9. Modern\u00e9 NVMe disky zvl\u00e1dnu nonstop load. \u010ci\u017ee ak datab\u00e1za potrebuje priebe\u017ene zapisova\u0165 stovky GB denne a disk je ur\u010den\u00fd na stovky TBW, ste v pohode. Samozrejme netreba plytva\u0165 &#8211; ale netreba sa ani pr\u00edli\u0161 b\u00e1\u0165 vyu\u017ei\u0165 disk. Napr\u00edklad nastavenie innodb_io_capacity_max spom\u00ednan\u00e9 vy\u0161\u0161ie &#8211; Percona upozornila, \u017ee extr\u00e9mne prehnan\u00e9 hodnoty m\u00f4\u017eu vies\u0165 k zbyto\u010dn\u00e9mu <strong>opotrebovaniu SSD<\/strong> bez pr\u00ednosu (logick\u00e9 \u2013 ak nepotrebujeme flushn\u00fa\u0165, rad\u0161ej to nechajme v RAM a zap\u00ed\u0161me nesk\u00f4r). \u010ci\u017ee primeranos\u0165 je na mieste.<\/li>\n<\/ul>\n\n\n\n<p>Optimaliz\u00e1cia I\/O spo\u010d\u00edva hlavne v prisp\u00f4soben\u00ed nastaven\u00ed charakteru v\u00e1\u0161ho \u00falo\u017eiska. Pre SSD vypn\u00fa\u0165 nepotrebn\u00e9 veci (neighbors), vyu\u017ei\u0165 O_DIRECT, adekv\u00e1tne nastavi\u0165 flush politiky a dba\u0165 na TRIM. V\u00fdsledkom by mala by\u0165 datab\u00e1za, ktor\u00e1 <strong>konzistentne pod\u00e1va v\u00fdkon<\/strong> a nekol\u00ed\u0161e pri I\/O bursts, s minimalizovanou latenciou diskov\u00fdch oper\u00e1ci\u00ed.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Vyrovn\u00e1vacie pam\u00e4te (cache) a buffery<\/strong><\/h2>\n\n\n\n<p>Okrem ve\u013ekej InnoDB cache (buffer pool), ktor\u00fa sme u\u017e rozobrali, m\u00e1 MySQL nieko\u013eko \u010fal\u0161\u00edch <strong>cache a vyrovn\u00e1vac\u00edch pam\u00e4t\u00ed<\/strong>, ktor\u00fdch nastavenie ovplyv\u0148uje v\u00fdkon. Tu s\u00fa tie podstatn\u00e9:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Query cache (cache dopytov):<\/strong> Query cache je mechanizmus, ktor\u00fd si pam\u00e4tal v\u00fdsledky SELECT dopytov a pri rovnakom dopyte z cache vr\u00e1til v\u00fdsledok namiesto znovuv\u00fdpo\u010dtu. <strong>MySQL 8.0 query cache \u00faplne odstr\u00e1nil<\/strong> kv\u00f4li probl\u00e9mom so \u0161k\u00e1lovate\u013enos\u0165ou. V star\u0161\u00edch MySQL (5.6\/5.7) bola query cache dostupn\u00e1, ale predvolene vypnut\u00e1 (size=0), lebo na viacjadrov\u00fdch syst\u00e9moch sp\u00f4sobovala lock contention . MariaDB query cache st\u00e1le obsahuje &#8211; v MariaDB 10.x je default query_cache_size=1M ale query_cache_type=OFF (tzn. vypnut\u00e1, aj ke\u010f alokovan\u00e1 1MB) . <strong>Odpor\u00fa\u010danie:<\/strong> vo v\u00e4\u010d\u0161ine pr\u00edpadov query cache <em>nepou\u017e\u00edvajte<\/em>. Pokia\u013e nem\u00e1te \u0161pecifick\u00fd scen\u00e1r (ve\u013emi statick\u00e9 d\u00e1ta, m\u00e1lo z\u00e1pisov, ve\u013ea identick\u00fdch selectov), query cache sp\u00f4sob\u00ed viac probl\u00e9mov (synchroniz\u00e1cia pri ka\u017edom z\u00e1pise, invalid\u00e1cia). Modern\u00e9 pr\u00edstupy ke\u0161ovania sa rie\u0161ia sk\u00f4r na aplika\u010dnej vrstve (napr. v\u00fdsledky dopytov do Redis\/memcached, alebo aspo\u0148 proxy cache). Ak v\u0161ak viete, \u010do rob\u00edte a MariaDB query cache chcete, dajte jej rozumn\u00fa ve\u013ekos\u0165 (desiatky MB, nie viac) a sledujte metriku Qcache_hits vs Qcache_inserts at\u010f., aby ste vedeli \u010di m\u00e1 efekt. Pre nov\u00fdch resp. za\u010d\u00ednajucich adminov v\u0161ak sk\u00f4r: <em>nechajte query cache vypnut\u00fa<\/em>, s\u00fastredi\u0165 sa treba na in\u00e9 oblasti.<br><\/li>\n\n\n\n<li><strong>table_open_cache:<\/strong> MySQL (resp. MariaDB) intern\u00e9 cache otvoren\u00fdch tabuliek. Datab\u00e1za mus\u00ed \u010dasto otv\u00e1ra\u0165 s\u00fabory tabuliek (frm defin\u00edcie, alebo samotn\u00e9 d\u00e1tov\u00e9 s\u00fabory MyISAM\/Aria), a t\u00e1to cache dr\u017e\u00ed handle u\u017e otvoren\u00fdch tabuliek pre r\u00fdchlej\u0161\u00ed pr\u00edstup. Ak m\u00e1te ve\u013ea tabuliek alebo \u010dasto sp\u00fa\u0161\u0165ate dopyty na r\u00f4zne tabu\u013eky, v\u00e4\u010d\u0161ia cache pom\u00f4\u017ee, aby syst\u00e9m nemusel st\u00e1le otv\u00e1ra\u0165\/zatv\u00e1ra\u0165 s\u00fabory. Stavov\u00e1 hodnota Opened_tables indikuje, ko\u013ekokr\u00e1t musel server otvori\u0165 tabu\u013eku \u2013 ak r\u00fdchlo rastie, cache je mal\u00e1. Odpor\u00fa\u010dame nastavi\u0165 aspo\u0148 nieko\u013eko tis\u00edc (predvolen\u00e9 b\u00fdvalo okolo 200. Na serveri s 16GB RAM k\u013eudne table_open_cache = 4000 alebo viac, ak m\u00e1te tis\u00edce tabuliek. Pam\u00e4te to ve\u013ea nezje a m\u00f4\u017ee to zn\u00ed\u017ei\u0165 latencie pri pr\u00edstupe k nov\u00fdm tabu\u013ek\u00e1m. <\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-ht-blocks-messages wp-block-hb-message wp-block-hb-message--withicon is-style-danger\"> <strong>Pozor:<\/strong> ak zv\u00fd\u0161ite table_open_cache, z\u00e1rove\u0148 zv\u00fd\u0161te limit po\u010dtu s\u00faborov v OS (ulimit -n alebo v \/etc\/security\/limits.conf), preto\u017ee MySQL bude dr\u017ea\u0165 viac file descriptorov otvoren\u00fdch.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>table_definition_cache:<\/strong> Podobn\u00e1 cache ako open_cache, ale cache defin\u00edci\u00ed tabuliek (najm\u00e4 u\u017eito\u010dn\u00e9 v InnoDB, kde sa defin\u00edcie inak ukladaj\u00fa do jeho data dictionary a mohlo by doch\u00e1dza\u0165 k reloadom). Zvy\u010dajne ju MySQL nastav\u00ed automaticky pod\u013ea po\u010dtu tabuliek. Ak m\u00e1te desiatky tis\u00edc tabuliek (napr. multitenant aplik\u00e1cia s ve\u013ea sch\u00e9mami), zva\u017eujte nav\u00fd\u0161enie. Inak default b\u00fdva OK. Toto nastavenie neovplyv\u0148uje priamo v\u00fdkon dopytov, sk\u00f4r zabra\u0148uje prie\u0165ahom pri otv\u00e1ran\u00ed nov\u00fdch tabuliek.<br><\/li>\n\n\n\n<li><strong>max_connections a thread_cache_size:<\/strong> S\u00edce to nie je cache v\u00fdsledkov alebo d\u00e1t, ale ide o <strong>cache vl\u00e1kien<\/strong> (threads). MySQL obsluhuje ka\u017ed\u00e9 pripojenie v samostatnom vl\u00e1kne. Ke\u010f sa klient odpoj\u00ed, vl\u00e1kno sa bu\u010f ukon\u010d\u00ed alebo sa <strong>ulo\u017e\u00ed do thread cache<\/strong> pre neskor\u0161ie pou\u017eitie. Parameter thread_cache_size hovor\u00ed, ko\u013eko nepou\u017e\u00edvan\u00fdch vl\u00e1kien dr\u017ea\u0165 \u201ev rezerve\u201c. Ak je 0, v\u017edy sa vl\u00e1kno zru\u0161\u00ed a nov\u00e9 pripojenie si vy\u017eiada fork nov\u00e9ho vl\u00e1kna (\u010do m\u00e1 mal\u00fa re\u017eiu, ale pri ve\u013emi \u010dastom prip\u00e1jan\u00ed sa to m\u00f4\u017ee nas\u010d\u00edta\u0165). Preto je vhodn\u00e9 ma\u0165 thread_cache_size aspo\u0148 nieko\u013eko (desiatok). Napr\u00edklad 16 alebo 50 pod\u013ea toho, ko\u013eko typicky m\u0155tvych vl\u00e1kien vznik\u00e1. Stavov\u00e1 hodnota Threads_created v\u00e1m povie, ko\u013eko vl\u00e1kien bolo vytvoren\u00fdch \u2013 ak vid\u00edte, \u017ee je ve\u013emi vysok\u00e1 a Connections tie\u017e, znamen\u00e1 to, \u017ee cache mohla by\u0165 v\u00e4\u010d\u0161ia. Modern\u00e9 MySQL \u010dasto thread cache autokonfiguruje. V MariaDB (ak nepou\u017e\u00edvate thread pool) tie\u017e funguje podobne. Nastavenie max_connections ur\u010duje, ko\u013eko s\u00fa\u010dasn\u00fdch pripojen\u00ed maxim\u00e1lne DB akceptuje. Na v\u00fdkon priamo nem\u00e1 vplyv (okrem toho, \u017ee extr\u00e9mne vysok\u00e9 \u010d\u00edslo m\u00f4\u017ee skonzumova\u017e ve\u013ea pam\u00e4te ak by sa v\u0161etci naraz pripojili). V be\u017en\u00fdch pr\u00edpadoch hodnota okolo 150\u2013300 sta\u010d\u00ed. Ak potrebujete viac (napr. stovky), uistite sa, \u017ee na to m\u00e1te pam\u00e4\u0165 a pr\u00edpadne zv\u00e1\u017ete architekt\u00faru (napr. pou\u017eitie connection poolu v aplik\u00e1cii, ProxySQL, alebo thread pool ak MariaDB). Ka\u017ed\u00e9 spojenie (vl\u00e1kno) toti\u017e nesie re\u017eijn\u00fa pam\u00e4\u0165 (stack ~256KB, plus pr\u00edpadne alokovan\u00e9 buffre pre dopyt at\u010f.). Tis\u00edc ne\u010dinn\u00fdch pripojen\u00ed tak zaberie ~256MB pam\u00e4te a ni\u010d nerobia.<br><\/li>\n\n\n\n<li><strong>query_cache_size<\/strong><strong> (MariaDB):<\/strong> Ako spom\u00ednan\u00e9, rad\u0161ej vypn\u00fa\u0165 alebo ponecha\u0165 mal\u00fa. Ak by ste ju predsa vyu\u017eili, sledujte Qcache_free_blocks a fragment\u00e1ciu cache.<br><\/li>\n\n\n\n<li><strong>tmp_table_size a max_heap_table_size:<\/strong> Tieto nastavenia ovplyv\u0148uj\u00fa, do akej ve\u013ekosti m\u00f4\u017ee MySQL vytvori\u0165 <strong>do\u010dasn\u00fa tabu\u013eku v pam\u00e4ti<\/strong>. Ak do\u010dasn\u00e1 tabu\u013eka (napr. pre GROUP BY, DISTINCT, \u010di na triedenie) presiahne t\u00fato ve\u013ekos\u0165, automaticky sa zap\u00ed\u0161e na disk (\u010do je pochopite\u013ene pomal\u0161ie). Predvolene b\u00fdva okolo 16\u201332MB. Na 16GB serveri si m\u00f4\u017eete dovoli\u0165 zv\u00fd\u0161i\u0165 povedzme na 64MB alebo 128MB, ak m\u00e1te dopyty, ktor\u00e9 triedia ve\u013ek\u00e9 mno\u017estvo d\u00e1t. Zv\u00fd\u0161i to \u0161ancu, \u017ee sa to cel\u00e9 zmest\u00ed do RAM a nezasiahne disk. Pozor v\u0161ak, \u017ee toto je na <strong>ka\u017ed\u00fa do\u010dasn\u00fa tabu\u013eku<\/strong> \u2013 ak by naraz stovka dopytov robila obrovsk\u00e9 do\u010dasn\u00e9 tabu\u013eky, m\u00f4\u017ee to dokopy zjes\u0165 zna\u010dn\u00fa RAM. \u010ci\u017ee nastavova\u0165 s uv\u00e1\u017een\u00edm pod\u013ea povahy dopytov.<br><\/li>\n\n\n\n<li><strong>join_buffer_size, sort_buffer_size, read_buffer_size, read_rnd_buffer_size:<\/strong> To s\u00fa <strong>buffre pre jednotliv\u00e9 dopyty (resp. threado)<\/strong>. Napr\u00edklad join_buffer sa pou\u017e\u00edva pri spojeniach bez indexu (bad), sort_buffer pri trieden\u00ed ktor\u00e9 sa nevmest\u00ed do optimalizovan\u00fdch \u0161trukt\u00far at\u010f. MySQLTuner \u010dasto navrhuje tieto hodnoty zvy\u0161ova\u0165 ak vid\u00ed ve\u013ea vyu\u017eit\u00ed. Bu\u010fte opatrn\u00fd \u2013 tieto buffre sa alokuj\u00fa <strong>per thread per operation<\/strong>. Ak ich d\u00e1te napr. 64MB ka\u017ed\u00fd a spust\u00ed sa komplexn\u00fd dopyt, m\u00f4\u017ee jedno vl\u00e1kno zabra\u0165 aj stovky MB len v do\u010dasn\u00fdch buffroch. A ak tak\u00fdch dopytov paralelne be\u017e\u00ed viac, pam\u00e4\u0165 sa m\u00ed\u0148a r\u00fdchlo. V\u0161eobecne defaulty (napr. join_buffer 256KB\u20131MB, sort_buffer p\u00e1r MB) s\u00fa v poriadku pre v\u00e4\u010d\u0161inu pr\u00edpadov. Zvy\u0161ujte ich len ak viete, \u017ee to potrebujete (napr. \u0161pecifick\u00fd dopyt na ve\u013ea d\u00e1t) a rad\u0161ej do\u010dasne pomocou SET SESSION pre dan\u00e9 spojenie. Glob\u00e1lne zv\u00e4\u010d\u0161ovanie t\u00fdchto bufferov je zradn\u00e9. \u010casto je lep\u0161ie optimalizova\u0165 dopyt (prida\u0165 index, upravi\u0165 pl\u00e1n) ne\u017e lia\u0165 viac pam\u00e4te do bufferov.<\/li>\n<\/ul>\n\n\n\n<p><strong>Najv\u00e4\u010d\u0161\u00ed v\u00fdznam m\u00e1 InnoDB buffer pool<\/strong>, ostatn\u00e9 cache (table cache, thread cache) doladi\u0165 pod\u013ea potreby. Query cache rad\u0161ej off. Ostatn\u00e9 buffre a pam\u00e4te nechajte pribli\u017ene default, pokia\u013e nem\u00e1te d\u00f4vod meni\u0165 \u2013 a ak men\u00edte, bu\u010fte si vedom\u00ed efektu na pam\u00e4\u0165 pri mnoh\u00fdch vl\u00e1knach. V\u017edy sledujte <strong>hit-ratio<\/strong> ak je k dispoz\u00edcii \u2013 napr. Key_reads a Key_read_requests pre MyISAM key cache (dnes menej podstatn\u00e9, ale ak by ste pou\u017e\u00edvali MyISAM\/Aria, ve\u013ekos\u0165 key_buffer_size nastavte pod\u013ea ve\u013ekosti indexov t\u00fdchto tabuliek). Pre InnoDB buffer pool je hit ratio v\u00e4\u010d\u0161inou 99%+ ak je spr\u00e1vne nastaven\u00fd, inak by ste videli ve\u013ea Innodb_buffer_pool_reads z disku.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Paraleliz\u00e1cia a po\u010det pripojen\u00ed<\/strong><\/h2>\n\n\n\n<p>V\u00fdkon modern\u00e9ho datab\u00e1zov\u00e9ho servera z\u00e1vis\u00ed aj od toho, ako dok\u00e1\u017ee vyu\u017ei\u0165 viacjadrov\u00fd procesor a obsl\u00fa\u017ei\u0165 mnoho <strong>s\u00fabe\u017en\u00fdch dopytov<\/strong>. Tu je nieko\u013eko aspektov:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Maxim\u00e1lny po\u010det spojen\u00ed (<\/strong><strong>max_connections<\/strong><strong>):<\/strong> Ako u\u017e bolo spomenut\u00e9, toto \u010d\u00edslo nerob\u00ed datab\u00e1zu r\u00fdchlej\u0161ou samo o sebe, ale nastavuje limit, ko\u013eko klientov m\u00f4\u017ee naraz by\u0165 pripojen\u00fdch. Ak ho nastav\u00edte pr\u00edli\u0161 n\u00edzko, riskujete chybu <em>\u201cToo many connections<\/em>\u201d ak pr\u00edde \u0161pi\u010dka. Ak pr\u00edli\u0161 vysoko, riskujete, \u017ee pri nejakej zbl\u00e1znenej situ\u00e1cii za\u010dne tis\u00edc vl\u00e1ken z\u00e1pasi\u0165 o zdroje a server sa <strong>zahlt\u00ed kontextov\u00fdmi prep\u00edna\u010dmi<\/strong> alebo vy\u010derp\u00e1 pam\u00e4\u0165. Pre VPS 4 vCPU \/ 16 GB RAM typicky sta\u010d\u00ed okolo 200\u2013300. Viac d\u00e1va zmysel len ak be\u017ene m\u00e1te stovky akt\u00edvnych spojen\u00ed (napr. ve\u013ek\u00fd web s mnoh\u00fdmi worker procesmi). Mnoh\u00e9 pripojenia v\u0161ak m\u00f4\u017eu by\u0165 len ne\u010dinn\u00e9 (idle) \u2013 tie moc nevadia, len zaber\u00fa thread (to rie\u0161i thread pool, vi\u010f ni\u017e\u0161ie). <strong>Sledujte<\/strong> Threads_connected a Threads_running \u2013 prv\u00e9 s\u00fa v\u0161etky pripojenia, druh\u00e9 len tie aktu\u00e1lne vykon\u00e1vaj\u00face pr\u00e1cu. Aj pri 200 pripojeniach m\u00f4\u017ee by\u0165 len 5 akt\u00edvnych (ostatn\u00e9 \u010dakaj\u00fa na klienta). V takom pr\u00edpade v\u00fdkon sa riadi t\u00fdmi akt\u00edvnymi, ostatn\u00e9 len visia v pam\u00e4ti.<br><\/li>\n\n\n\n<li><strong>Thread Pool vs per-thread:<\/strong> Ako spom\u00ednan\u00e9 v sekcii rozdielov, Oracle MySQL Community pou\u017e\u00edva model <strong>jedno vl\u00e1kno na jedno pripojenie<\/strong>. To znamen\u00e1, \u017ee ak aj m\u00e1te 500 pripojen\u00ed, je tam 500 vl\u00e1kien, hoci v\u00e4\u010d\u0161ina ni\u010d nerob\u00ed. OS\/Linux si s t\u00fdm do istej miery porad\u00ed (vl\u00e1kna ktor\u00e9 s\u00fa idle nespotreb\u00favaj\u00fa CPU, len pam\u00e4\u0165). No ak za\u010dne naraz 500 dopytov, 500 vl\u00e1kien sa rozbehne a to m\u00f4\u017ee sp\u00f4sobi\u0165 siln\u00e9 pre\u0165a\u017eovanie CPU (prep\u00ednanie medzi 500 vl\u00e1knami na 4 jadr\u00e1ch = ka\u017ed\u00fd dostane mal\u00fd k\u00fasok \u010dasu, overhead je ve\u013ek\u00fd). Rie\u0161en\u00edm je <strong>thread pool<\/strong>, ktor\u00fd po\u010det skuto\u010dne be\u017eiacich threadov obmedzuje a rad\u00ed dopyty do front, ak je u\u017e jedno vl\u00e1kno na jadro zanepr\u00e1zdnen\u00e9. Thread pool je dostupn\u00fd v MariaDB out-of-the-box (premenn\u00e1 thread_handling=pool-of-threads je default). V Percona Server sa d\u00e1 zapn\u00fa\u0165 nastaven\u00edm rovnakej premennej.. V MySQL Community bohu\u017eia\u013e nie je \u2013 tam by ste museli prejs\u0165 na MySQL Enterprise alebo pou\u017ei\u0165 <em>connection pool<\/em> pred MySQL (napr. ProxySQL m\u00f4\u017ee multiplexova\u0165 mnoho logick\u00fdch spojen\u00ed do p\u00e1r fyzick\u00fdch). <strong>Odpor\u00fa\u010danie:<\/strong> Ak pou\u017e\u00edvate MariaDB alebo Perconu a m\u00e1te 100ky+ pripojen\u00ed, ur\u010dite vyu\u017eite thread pool. Spravidla funguje bez nutnosti \u010fal\u0161ieho ladenia, default rozdel\u00ed pripojenia do thread group pod\u013ea jadier . V\u00fdsledkom je, \u017ee akt\u00edvnych vl\u00e1kien be\u017e\u00ed pribli\u017ene to\u013eko ako CPU jadier, \u010do zabr\u00e1ni zbyto\u010dn\u00e9mu kontextov\u00e9mu prep\u00ednaniu a \u010dasto to zlep\u0161\u00ed latenciu dopytov pri vysokej z\u00e1\u0165a\u017ei (za cenu mierneho oneskorenia pre niektor\u00e9 dopyty kv\u00f4li \u010dakaniu vo fronte, ale to je st\u00e1le lep\u0161ie ako znefunk\u010dni\u0165 cel\u00fd server pre\u0165a\u017een\u00edm). Ak ste na Oracle MySQL, zv\u00e1\u017ete ProxySQL alebo aspo\u0148 dostato\u010dne vysok\u00fd thread_cache_size, nech aspo\u0148 recykluje vl\u00e1kna.<br><\/li>\n\n\n\n<li><strong>Paraleln\u00e9 spracovanie dopytu:<\/strong> Tradi\u010dne MySQL dopyt (napr. jeden SELECT) be\u017e\u00ed v jednom vl\u00e1kne, teda na jednom jadre. To znamenalo, \u017ee ak m\u00e1te jeden ve\u013emi \u0165a\u017ek\u00fd dopyt, nevyu\u017eije viac jadier (na rozdiel od niektor\u00fdch in\u00fdch DB). MySQL 8.0 v\u0161ak priniesol <strong>parallel read<\/strong> pre InnoDB (\u010diasto\u010dne) a <strong>parallel index build<\/strong> at\u010f. Napr\u00edklad ak rob\u00edte ALTER TABLE &#8230; ADD INDEX, m\u00f4\u017ee pou\u017ei\u0165 viac jadier. Tie\u017e v replik\u00e1cii existuje paraleln\u00e9 aplikovanie transakci\u00ed. Ale be\u017en\u00fd SELECT st\u00e1le ide v jednom vl\u00e1kne. Preto pri tuningu myslite tak, \u017ee v\u00fdkon sa dosahuje <strong>paralelnos\u0165ou mnoh\u00fdch dopytov<\/strong>, nie paraleliz\u00e1ciou jedn\u00e9ho dopytu (ako by to vedel napr. Oracle DB \u010di Postgres s ur\u010dit\u00fdmi nastaveniami). Z poh\u013eadu admina to znamen\u00e1: <strong>rozlo\u017eenie z\u00e1\u0165a\u017ee<\/strong> \u2013 ak potrebujete spracova\u0165 ve\u013ek\u00fd report, rad\u0161ej to rozbi\u0165 na viac men\u0161\u00edch dopytov paralelne ak to d\u00e1 zmysel, inak ten jeden dopyt bude obmedzen\u00fd v\u00fdkonom jedn\u00e9ho jadra. V praxi s t\u00fdm ve\u013ea nenarob\u00edme, len je dobr\u00e9 to vedie\u0165.<br><\/li>\n\n\n\n<li><strong>innodb_read_io_threads<\/strong><strong> a <\/strong><strong>innodb_write_io_threads<\/strong><strong>:<\/strong> Ide o po\u010det vl\u00e1kien, ktor\u00e9 InnoDB pou\u017e\u00edva na <strong>pozadie I\/O oper\u00e1cie<\/strong> (\u010d\u00edtanie pre read ahead, z\u00e1pisy na pozad\u00ed). Default b\u00fdval 4 a 4. Na serveri s 4 jadrami to spravidla sta\u010d\u00ed. Ak by ste mali viac diskov alebo fakt extr\u00e9mnu I\/O z\u00e1\u0165a\u017e, dalo by sa zv\u00fd\u0161i\u0165 na 8. MySQL umo\u017e\u0148uje a\u017e 64 nastavi\u0165, ale re\u00e1lne pr\u00ednos nad 4\u20138 b\u00fdva minim\u00e1lny, ke\u010f\u017ee aj tak z\u00e1le\u017e\u00ed na IOPS disku. Neodpor\u00fa\u010dame meni\u0165, pokia\u013e nem\u00e1te meranie, \u017ee I\/O queue nie je dostato\u010dne vy\u0165a\u017een\u00fd s 4 vl\u00e1knami (to je m\u00e1lo pravdepodobn\u00e9 na be\u017enom SSD). MariaDB tie\u017e default 4. \u010ci\u017ee sk\u00f4r to nerie\u0161te \u2013 spom\u00edname pre kompletnos\u0165 inform\u00e1ci\u00ed.<br><\/li>\n\n\n\n<li><strong>innodb_thread_concurrency<\/strong><strong>:<\/strong> Historick\u00fd parameter, ktor\u00fdm sa obmedzoval po\u010det s\u00fabe\u017en\u00fdch InnoDB threads. V nov\u00fdch verziach ak je 0 (default), tak je vlastne vypnut\u00fd a InnoDB si to riadi sama. Kedysi sa ladil v MySQL 5.1\/5.5, dnes u\u017e nie. Nechajte default (0 alebo necha\u0165 tak).<br><\/li>\n\n\n\n<li><strong>Asynchr\u00f3nne I\/O a prefetching:<\/strong> E\u0161te spomenieme, \u017ee InnoDB vyu\u017e\u00edva asynchr\u00f3nne I\/O, tak\u017ee ke\u010f jedno vl\u00e1kno potrebuje \u010d\u00edta\u0165 z disku, neblokuje t\u00fdm nutne CPU \u2013 po\u017eiadavka sa spracuje asynchr\u00f3nne pomocou spom\u00ednan\u00fdch read_io_threads. To je len detail, ktor\u00fd vysvet\u013euje, pre\u010do aj jedno vl\u00e1kno dok\u00e1\u017ee zapoji\u0165 viac vl\u00e1kien v pozad\u00ed. Ale maximum CPU pre v\u00fdpo\u010det nad d\u00e1tami m\u00e1 st\u00e1le jedno.<br><\/li>\n\n\n\n<li><strong>Scale-up vs scale-out:<\/strong> Ak naraz\u00edte na limit, \u017ee 4 jadr\u00e1 CPU proste nesta\u010dia (m\u00e1te trvalo 100% CPU a dopyty nest\u00edhaj\u00fa), m\u00e1te v z\u00e1sade dve cesty \u2013 scale-up (v\u00fdkonnej\u0161\u00ed stroj, viac jadier, viac GHz) alebo scale-out (rozlo\u017ei\u0165 z\u00e1\u0165a\u017e na viac serverov). Scale-up v pr\u00edpade MySQL ide do istej miery \u2013 MySQL 8 zvl\u00e1dne vyu\u017ei\u0165 aj 32+ jadier pre mnoho s\u00fabe\u017en\u00fdch spojen\u00ed, tak\u017ee ak m\u00e1te mo\u017enos\u0165, m\u00f4\u017eete zv\u00fd\u0161i\u0165 po\u010det vCPU z 4 na 8 \u010di 16 a uvid\u00edte zlep\u0161enie pri paralelnej z\u00e1\u0165a\u017ei. Samozrejme, u VPS to z\u00e1vis\u00ed od v\u00e1\u0161ho pl\u00e1nu. Scale-out znamen\u00e1 napr. rozdeli\u0165 datab\u00e1zu na viac serverov pod\u013ea funkcie (napr. jeden server na \u010d\u00edtanie \u2013 replik\u00e1cia, druh\u00fd na z\u00e1pisy) alebo pod\u013ea d\u00e1t (sharding). To je v\u0161ak u\u017e architektonick\u00e1 ot\u00e1zka nad r\u00e1mec tohto \u010dl\u00e1nku. Pre v\u00e4\u010d\u0161inu pr\u00edpadov optimaliz\u00e1cia konfigur\u00e1cie a dopytov na jednom servery posta\u010duje.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Indexy a n\u00e1vrh sch\u00e9my<\/strong><\/h2>\n\n\n\n<p>Aj ten najvyladenej\u0161\u00ed datab\u00e1zov\u00fd server nebude pod\u00e1va\u0165 dobr\u00fd v\u00fdkon, ak <strong>dopyty a datab\u00e1zov\u00e1 sch\u00e9ma nie s\u00fa optim\u00e1lne navrhnut\u00e9<\/strong>. Z poh\u013eadu administr\u00e1tora nem\u00e1me v\u017edy dosah na \u00fapravu k\u00f3du aplik\u00e1cie, ale m\u00f4\u017eeme <strong>analyzova\u0165 dopyty <\/strong>(cez slow log, EXPLAIN) a identifikova\u0165 potenci\u00e1lne probl\u00e9my, ktor\u00e9 v\u00fdvoj\u00e1rom navrhneme na opravu. Nieko\u013eko tipov:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Indexy, indexy, indexy:<\/strong> Naj\u010dastej\u0161ou pr\u00ed\u010dinou pomal\u00fdch dopytov je ch\u00fdbaj\u00faci alebo nevhodn\u00fd <strong>index<\/strong>. Full table scan je r\u00e1dovo pomal\u0161ie ne\u017e pou\u017eitie indexu na v\u00fdber p\u00e1r riadkov. Ako povedal jeden znalec: <em>\u201cAk dopyt mus\u00ed prejs\u0165 cel\u00fa tabu\u013eku, nepom\u00f4\u017eu ti ani 10\u00d7 r\u00fdchlej\u0161ie CPU, ale ak prid\u00e1\u0161 index a dopyt spracuje len 0,01% tabu\u013eky, z\u00edska\u0161 obrovsk\u00e9 zr\u00fdchlenie aj bez r\u00fdchlej\u0161ieho hardv\u00e9ru.\u201d<\/em> . Toto presne vystihuje v\u00fdznam indexov. Admin by mal preto <strong>prejs\u0165 slow log<\/strong> a pre podozrivo pomal\u00e9 dopyty si vysk\u00fa\u0161a\u0165 EXPLAIN dopyt; \u2013 uvid\u00ed sa, \u010di pou\u017e\u00edvaj\u00fa indexy (typicky v st\u013apci key alebo possible_keys). Ak nie, treba navrhn\u00fa\u0165 prida\u0165 index na st\u013apec, pod\u013ea ktor\u00e9ho sa vo WHERE filtuje alebo JOINuje. Napr\u00edklad dopyt WHERE email = &#8218;nieco&#8216; na tabu\u013eku s mili\u00f3nmi z\u00e1znamov bude pomal\u00fd bez indexu na st\u013apci email. Jednoduch\u00fdm pridan\u00edm indexu m\u00f4\u017eete skr\u00e1ti\u0165 beh dopytu z sek\u00fand na milisekundy, \u010do je <strong>najlep\u0161ia optimaliz\u00e1cia<\/strong> ak\u00e1 existuje (v porovnan\u00ed s t\u00fdm je ladenie configu druhorad\u00e9).<br><\/li>\n\n\n\n<li><strong>Kontrola pou\u017e\u00edvania indexov:<\/strong> Niektor\u00e9 dopyty mo\u017eno index maj\u00fa, ale nepou\u017eij\u00fa ho \u2013 m\u00f4\u017ee by\u0165 napr. nevhodn\u00fd. Toto je sk\u00f4r na developerov, aby dopyt prepisali alebo vytvorili lep\u0161\u00ed index (napr. kompozitn\u00fd index ak dopyt filtruje pod\u013ea viacer\u00fdch st\u013apcov s\u00fa\u010dasne). Ako admin m\u00f4\u017eete aspo\u0148 identifikova\u0165, \u017ee <em>\u201ctento dopyt nerob\u00ed index scan ale full scan\u201d<\/em>. MySQL 8 umo\u017e\u0148uje aj pr\u00edkaz EXPLAIN ANALYZE ktor\u00fd skuto\u010dne vykon\u00e1 dopyt a povie ko\u013eko riadkov pre\u0161lo \u2013 to je u\u017eito\u010dn\u00e9 na potvrdenie.<br><\/li>\n\n\n\n<li><strong>N\u00e1vrh sch\u00e9my:<\/strong> Okrem indexov sem patr\u00ed aj typy st\u013apcov a normaliz\u00e1cia. Ve\u013emi \u0161irok\u00e9 tabu\u013eky (mnoho st\u013apcov, zvl\u00e1\u0161\u0165 ak s\u00fa TEXT\/BLOB nepotrebne) spoma\u013euj\u00fa pr\u00e1cu. Obrovsk\u00e9 VARCHAR(1000) ak nepotrebujete to\u013eko znakov, at\u010f. Tieto veci ke\u010f objav\u00edte, m\u00f4\u017eete odporu\u010di\u0165 n\u00e1pravu. \u010ealej, <strong>ch\u00fdbaj\u00face prim\u00e1rne k\u013e\u00fa\u010de<\/strong> \u2013 v InnoDB by ka\u017ed\u00e1 tabu\u013eka mala ma\u0165 PRIMARY KEY (alebo aspo\u0148 unik\u00e1tny not null index), inak InnoDB mus\u00ed vytv\u00e1ra\u0165 skryt\u00fd rowid, \u010do s\u0165a\u017euje replik\u00e1cie a m\u00f4\u017ee ma\u0165 v\u00fdkonnostn\u00e9 dopady pri niektor\u00fdch oper\u00e1ci\u00e1ch. Tak\u017ee ak objav\u00edte tabu\u013eku bez prim\u00e1rneho k\u013e\u00fa\u010da, navrhnite ho doplni\u0165.<br><\/li>\n\n\n\n<li><strong>\u00dadr\u017eba \u0161tatist\u00edk a fragment\u00e1cie:<\/strong> InnoDB si automaticky udr\u017eiava \u0161tatistiky pre optimaliz\u00e1tor (pokia\u013e nem\u00e1te star\u00fa verziu s innodb_stats_on_metadata zapnut\u00fdm, v nov\u00fdch to u\u017e nerob\u00ed pri ka\u017edom selecte). Niekedy pom\u00f4\u017ee spusti\u0165 ANALYZE TABLE mytable; aby sa \u0161tatistiky obnovili, ak optimaliz\u00e1tor vol\u00ed zl\u00fd pl\u00e1n. Tie\u017e ak sa v\u00fdrazne zmenila ve\u013ekos\u0165 tabu\u013eky (ve\u013ea zmazan\u00fdch z\u00e1znamov), m\u00f4\u017eete uva\u017eova\u0165 nad OPTIMIZE TABLE (\u010do je vlastne recreate+analyze pre InnoDB) \u2013 to zmen\u0161\u00ed .ibd s\u00fabor a defragmentuje indexy. Av\u0161ak s modern\u00fdmi verzami to robte len ak naozaj treba, InnoDB inak efekt\u00edvne pracuje aj s fragmentation, a OPTIMIZE je zd\u013ahav\u00e1 oper\u00e1cia.<br><\/li>\n\n\n\n<li>Percona Toolkit m\u00e1 n\u00e1stroj pt-index-usage a pt-duplicate-key-checker, ktor\u00e9 vedia pom\u00f4c\u0165 n\u00e1js\u0165 <strong>nepou\u017e\u00edvan\u00e9 indexy alebo duplicity<\/strong>. Nadbyto\u010dn\u00e9 indexy zbyto\u010dne spoma\u013euj\u00fa z\u00e1pisy (ka\u017ed\u00fd INSERT\/UPDATE ich mus\u00ed udr\u017eiava\u0165) a zaberaj\u00fa pam\u00e4\u0165 aj disk. Tak\u017ee ob\u010das je fajn aj odhali\u0165 indexy, ktor\u00e9 sa nikdy nepou\u017e\u00edvaj\u00fa a odstr\u00e1ni\u0165 ich. To ale robte len ak naozaj viete, \u017ee sa nepou\u017eij\u00fa.<\/li>\n<\/ul>\n\n\n\n<p>Z poh\u013eadu admina teda <strong>monitorujte dopyty a spolupracujte s v\u00fdvoj\u00e1rmi<\/strong>. M\u00f4\u017eete im poskytn\u00fa\u0165 zoznam najhor\u0161\u00edch dopytov z logu, odporu\u010di\u0165 kde prida\u0165 index, alebo ktor\u00e9 indexy by mohli zlep\u0161i\u0165 v\u00fdkon. Toto \u00fasilie \u010dasto prinesie <em>radovo vy\u0161\u0161ie zr\u00fdchlenie<\/em> ne\u017e ak\u00e9ko\u013evek tunenie parametrov.<br><\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Automatick\u00e9 re\u0161tarty a logovanie<\/strong><\/h2>\n\n\n\n<p>Star\u00e1 administr\u00e1torsk\u00e1 m\u00fadros\u0165 hovor\u00ed: <em>\u201cHope for the best, plan for the worst.\u201d<\/em> Aj dobre vyladen\u00e1 datab\u00e1za m\u00f4\u017ee zlyha\u0165 alebo spadn\u00fa\u0165. D\u00f4le\u017eit\u00e9 je, aby sa v takom pr\u00edpade <strong>r\u00fdchlo zotavila<\/strong> a aby ste mali k dispoz\u00edcii <strong>logy na diagnostiku<\/strong>.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Automatick\u00e9 re\u0161tartovanie:<\/strong> Uistite sa, \u017ee datab\u00e1zu sp\u00fa\u0161\u0165ate pomocou mechanizmu, ktor\u00fd ju v pr\u00edpade p\u00e1du znova nahod\u00ed. Ak pou\u017e\u00edvate syst\u00e9m so systemd, skontrolujte mysqld.service &#8211; mala by ma\u0165 nastaven\u00e9 Restart=on-failure (v\u00e4\u010d\u0161ina distier to tak m\u00e1). V minulosti sa pou\u017e\u00edval wrapper mysqld_safe, ktor\u00fd sp\u00fa\u0161\u0165a mysqld a ak skon\u010d\u00ed p\u00e1dom, znovu ho spust\u00ed. Na modern\u00fdch syst\u00e9moch to prevzal systemd. Ka\u017edop\u00e1dne, nechcete osta\u0165 s vypnutou DB len preto, \u017ee ju nikto nere\u0161tartol. <strong>Automatick\u00fd re\u0161tart nie je n\u00e1hrada rie\u0161enia probl\u00e9mov, ale k\u00fapi v\u00e1m \u010das<\/strong> \u2013 v\u00fdpadok je mo\u017eno min\u00fata namiesto hod\u00edn, ak by ste to nezistili.<br><\/li>\n\n\n\n<li><strong>Watchdog a vysok\u00e1 dostupnos\u0165:<\/strong> Pre kritick\u00e9 slu\u017eby sa oplat\u00ed nasadi\u0165 e\u0161te watchdog proces, ktor\u00fd ak vid\u00ed, \u017ee DB nereaguje (zasekne sa), tak ju zabije alebo re\u0161tartne. To u\u017e je pokro\u010dil\u00e9 \u2013 existuj\u00fa na to n\u00e1stroje ako <strong>MHA (Master High Availability)<\/strong> pre MySQL, alebo skripty s mysqladmin ping v crone. Na single serveri v\u0161ak v\u00e4\u010d\u0161inou posta\u010d\u00ed spom\u00ednan\u00fd systemd restart.<br><\/li>\n\n\n\n<li><strong>Logovanie ch\u00fdb a v\u00fdpadkov:<\/strong> MySQL\/MariaDB m\u00e1 <strong>error log<\/strong> \u2013 tam zapisuje r\u00f4zne varovania, chyby, stack trace pri p\u00e1de, inform\u00e1cie o obnove po v\u00fdpadku at\u010f. Skontrolujte, kam sa loguje (parametre log_error a log_error_verbosity). Zvy\u010dajne to bude \/var\/log\/mysql\/error.log. Pri ladiacom re\u017eime m\u00f4\u017eete zv\u00fd\u0161i\u0165 verbositu (ale nepotrebujete ak v\u0161etko be\u017e\u00ed OK). D\u00f4le\u017eit\u00e9 je <strong>priebe\u017ene nazera\u0165 do error logu<\/strong>, \u010di tam nie s\u00fa varovania typu <em>\u201eInnoDB: ### \u2026\u201c alebo \u201e[Warning] Aborted connection\u2026\u201c<\/em>. Napr\u00edklad opakovan\u00e9 <em>\u201eAborted connection (Got an error reading communication packets)\u201c<\/em> m\u00f4\u017eu indikova\u0165 probl\u00e9my s aplik\u00e1ciou alebo sie\u0165ou (klienti sa odp\u00e1jaj\u00fa nekorektne). Varovania o v\u00fdkonov\u00fdch veciach tam moc nie s\u00fa, sk\u00f4r o anom\u00e1li\u00e1ch (deadlocky v InnoDB sa loguj\u00fa, to je u\u017eito\u010dn\u00e9 vedie\u0165, ak by aplik\u00e1cia napr. \u010dasto vytv\u00e1rala deadlock situ\u00e1cie, log to uk\u00e1\u017ee).<br><\/li>\n\n\n\n<li><strong>Slow query log pre trval\u00fd monitoring:<\/strong> M\u00f4\u017eete necha\u0165 slow log zapnut\u00fd dlhodobo, len zvo\u013ete vhodn\u00fd long_query_time aby log ner\u00e1stol pr\u00edli\u0161 r\u00fdchlo. Napr. ak be\u017ene dopyty trvaj\u00fa pod 0.1s a to pova\u017eujete za OK, dajte long_query_time napr. 0.5 \u010di 1. Nech loguje len naozaj pomal\u00e9. Log potom rotujte (pridajte do logrotate.d), aby sa s\u00fabor nezahltil. Pravideln\u00fdm prezeran\u00edm slow logu (alebo generovan\u00edm preh\u013eadov cez pt-query-digest) z\u00edskate st\u00e1le aktu\u00e1lny preh\u013ead o tom, kde sa v aplik\u00e1cii objavuj\u00fa pomal\u00e9 dopyty \u2013 a tie viete n\u00e1sledne rie\u0161i\u0165.<br><\/li>\n\n\n\n<li><strong>Ostatn\u00e9 logy:<\/strong> MySQL m\u00e1 aj <strong>general query log<\/strong>, ktor\u00fd loguje v\u0161etky dopyty. Ten ur\u010dite nechajte vypnut\u00fd v produkcii (I\/O overkill). M\u00f4\u017eete ho pou\u017ei\u0165 do\u010dasne pri debuggu. Tie\u017e bin\u00e1rne logy (ak pou\u017e\u00edvate replik\u00e1ciu alebo aspo\u0148 zapnut\u00e9 pre point-in-time recovery) si zasl\u00fa\u017eia pozornos\u0165 \u2013 sledujte, aby nezahltili disk. Nastavte expire_logs_days (MySQL 8 m\u00e1 binlog_expire_logs_seconds) napr\u00edklad na 7 alebo 14 dn\u00ed, nech sa star\u00e9 binlogy ma\u017e\u00fa automaticky. Inak ak zabudnete, o pol roka m\u00f4\u017ee disk zaplni\u0165 prie\u010dinok s binlogmi.<br><\/li>\n\n\n\n<li><strong>Test crash recovery:<\/strong> Toto sa moc v praxi nerob\u00ed, ale ak viete (napr. v testovacom prostred\u00ed), sk\u00faste simulova\u0165 p\u00e1d (kill -9 mysqld napr\u00edklad) a pusti\u0165 ho. Sledujte v error logu, ako dlho trv\u00e1 obnovenie (InnoDB by mala prejs\u0165 logy a nahodi\u0165 DB). Malo by to by\u0165 relat\u00edvne r\u00fdchle (p\u00e1r sek\u00fand \u010di min\u00fat). Ak by ste mali extr\u00e9mne dlh\u00fd recovery, to by indikovalo, \u017ee napr. innodb_log_file_size bol zbyto\u010dne obrovsk\u00fd a mohol by by\u0165 men\u0161\u00ed. Tie\u017e to otestuje, \u010di m\u00e1te nastaven\u00fd auto-restart.<br><\/li>\n\n\n\n<li><strong>Op\u00e4\u0165 z\u00e1lohy a obnova:<\/strong> Hoci to u\u017e presahuje t\u00e9mu v\u00fdkonu, spomenieme, \u017ee ma\u0165 <strong>aktu\u00e1lnu z\u00e1lohu<\/strong> je aj v\u00fdkonov\u00e9 opatrenie \u2013 v zmysle ak sa nie\u010do pokaz\u00ed (napr. zl\u00fd ALTER Table, alebo nevydaren\u00e9 ladenie configu po\u0161kodil data file \u2013 hoci to by nemalo, ale pre istotu), aby ste vedeli <strong>r\u00fdchlo obnovi\u0165<\/strong> prev\u00e1dzku. V r\u00e1mci v\u00fdkonu sem patr\u00ed aj test obnovy \u2013 nielen \u017ee m\u00e1te dump, ale viete ho nane\u010disto obnovi\u0165 a zmera\u0165, ko\u013eko by to trvalo. Lebo ak obnova potrv\u00e1 5 hod\u00edn, viete, \u017ee v\u00fdpadok by bol 5 hod\u00edn. Mo\u017eno potom zainvestujete do repliky alebo inej HA met\u00f3dy, aby ste to skr\u00e1tili.<\/li>\n<\/ul>\n\n\n\n<p>Starajte sa o <strong>logy<\/strong> (error log, slow log) a majte nastaven\u00e9 mechanizmy, aby DB <strong>be\u017eala neust\u00e1le<\/strong> (automatick\u00fd \u0161tart po boote, re\u0161tart po p\u00e1de). Monitoring v\u00fdpadkov by mal by\u0165 samozrejmos\u0165 &#8211; cie\u013eom je minimalizova\u0165 downtime a z\u00edska\u0165 u\u017eito\u010dn\u00e9 inform\u00e1cie z logov pre \u010fal\u0161ie ladenie.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>N\u00e1stroje na ladenie a optimaliz\u00e1ciu<\/strong><\/h2>\n\n\n\n<p>Prejdime si <strong>u\u017eito\u010dn\u00e9 n\u00e1stroje<\/strong>, ktor\u00e9 vedia administr\u00e1torovi v\u00fdrazne pom\u00f4c\u0165 pri identifik\u00e1cii probl\u00e9mov a n\u00e1vrhu optimaliz\u00e1ci\u00ed. Spomenuli sme ich u\u017e priebe\u017ene, tu ich zhrnieme a porovn\u00e1me:<\/p>\n\n\n\n<p><strong>MySQLTuner<\/strong><\/p>\n\n\n\n<p>Skript v Perli, ktor\u00fd analyzuje aktu\u00e1lny stav MySQL (premenn\u00e9 a \u0161tatistiky) a vygeneruje odpor\u00fa\u010dania.<\/p>\n\n\n\n<p>R\u00fdchle zhodnotenie, upozorn\u00ed na zjavn\u00e9 nedostatky (napr. mal\u00fd buffer_pool, fragmentovan\u00e1 query cache at\u010f.). <strong>Nevykon\u00e1va zmeny automaticky<\/strong>, iba rad\u00ed. Je potrebn\u00e9 bra\u0165 odpor\u00fa\u010dania s rezervou \u2013 ka\u017ed\u00fd bod pos\u00fadi\u0165, \u010di d\u00e1va zmysel. Neodpor\u00fa\u010da sa slepo aplikova\u0165 v\u0161etko, cie\u013eom nem\u00e1 by\u0165 \u201c<em>zbavi\u0165 sa v\u0161etk\u00fdch warningov<\/em>\u201d za cenu prestrelen\u00fdch hodn\u00f4t . MySQLTuner je dobr\u00fd sluha, ale zl\u00fd p\u00e1n &#8211; vyu\u017eite ho na checklist, ale prem\u00fd\u0161\u013eajte nad ka\u017edou radou.<\/p>\n\n\n\n<p><strong>Tuning Primer<\/strong><\/p>\n\n\n\n<p>Jednoduch\u00fd shell skript (tuning-primer.sh) na podobn\u00fd \u00fa\u010del ako MySQLTuner.<\/p>\n\n\n\n<p>Je to star\u0161\u00ed n\u00e1stroj, vhodn\u00fd najm\u00e4 pre MySQL 5.x. V nov\u0161\u00edch verzi\u00e1ch u\u017e nemus\u00ed rozpozna\u0165 niektor\u00e9 parametre. Poskytuje z\u00e1kladn\u00e9 odpor\u00fa\u010dania (napr. \u201cquery cache disabled \u2013 ok\u201d, \u201cjoins without index \u2013 X\u201d). Ak pou\u017e\u00edvate MySQLTuner, tuning-primer netreba, ke\u010f\u017ee robia podobn\u00fa pr\u00e1cu.<\/p>\n\n\n\n<p><strong>Percona Toolkit<\/strong><\/p>\n\n\n\n<p>Bal\u00edk pokro\u010dil\u00fdch n\u00e1strojov pre MySQL a syst\u00e9m. Obsahuje mno\u017estvo util\u00edt: pt-query-digest, pt-kill, pt-stalk, pt-online-schema-change, pt-table-checksum, pt-variable-advisor a \u010fal\u0161ie.<\/p>\n\n\n\n<p>Ide o <strong>profesion\u00e1lnu sadu<\/strong> pre hlb\u0161iu anal\u00fdzu a spr\u00e1vu. Pre v\u00fdkonov\u00e9 ladenie s\u00fa najd\u00f4le\u017eitej\u0161ie: <strong>pt-query-digest<\/strong> (analyzuje slow log alebo aj pr\u00fad dopytov a vytvor\u00ed podrobn\u00fd report najpomal\u0161\u00edch dopytov, ich percenta z\u00e1\u0165a\u017ee at\u010f.), <strong>pt-variable-advisor<\/strong> (prejde SHOW VARIABLES a upozorn\u00ed na potenci\u00e1lne zl\u00e9 nastavenia \u2013 v podstate tak\u00fd static code analyzer pre my.cnf, s pripraven\u00fdmi pravidlami ), <strong>pt-mysql-summary<\/strong> a <strong>pt-summary<\/strong> (zhroma\u017edia mno\u017estvo info o konfigur\u00e1cii syst\u00e9mu a DB do preh\u013eadnej spr\u00e1vy), <strong>pt-stalk<\/strong> (str\u00e1\u017ei v\u00fdskyt nejakej udalosti, napr. vysok\u00e9 za\u0165a\u017eenie, a vtedy za\u010dne zbiera\u0165 diagnostick\u00e9 d\u00e1ta), <strong>pt-kill<\/strong> (automaticky ukon\u010duje dopyty\/spojenia pod\u013ea krit\u00e9ri\u00ed \u2013 napr. ak nejak\u00fd dopyt be\u017e\u00ed viac ne\u017e X sek\u00fand, m\u00f4\u017ee ho zabi\u0165; opatrne s t\u00fdm v produkcii). \u010ealej obsahuje n\u00e1stroje na \u00fadr\u017ebu: <strong>pt-online-schema-change<\/strong> (umo\u017en\u00ed meni\u0165 sch\u00e9mu tabuliek bez v\u00fdpadku \u2013 vytvoren\u00edm do\u010dasnej tabu\u013eky a triggers, at\u010f.), <strong>pt-table-checksum<\/strong> a <strong>pt-table-sync<\/strong> (na kontrolu konzistencie repl\u00edk a n\u00e1pravu rozdielov), <strong>pt-index-usage<\/strong> (sk\u00fama logy a povie, ktor\u00e9 indexy sa nepou\u017eili ), at\u010f. Percona Toolkit je nesmierne u\u017eito\u010dn\u00fd, ale je to <strong>ako skalpel v ruk\u00e1ch chirurga<\/strong> \u2013 treba vedie\u0165 \u010do rob\u00edte. Odpor\u00fa\u010dame ho sk\u00f4r sk\u00fasenej\u0161\u00edm adminom. Pre za\u010diatok sa zozn\u00e1mte s pt-query-digest (na anal\u00fdzu slow logu) a pt-variable-advisor (r\u00fdchla kontrola configu). V\u017edy pou\u017e\u00edvajte najnov\u0161iu verziu PT kompatibiln\u00fa s va\u0161ou DB verziou.<\/p>\n\n\n\n<p>Ako vidno, n\u00e1stroje MySQLTuner a Tuning Primer s\u00fa sk\u00f4r <strong>jednorazov\u00e9 skripty<\/strong> pre r\u00fdchlu radu, zatia\u013e \u010do Percona Toolkit je <strong>komplexn\u00e1 sada<\/strong> pre kontinu\u00e1lnu pr\u00e1cu a hlb\u0161\u00ed z\u00e1sah. V praxi je dobr\u00e9 pusti\u0165 MySQLTuner po p\u00e1r d\u0148och behu servera (upozorn\u00ed v\u00e1s napr\u00edklad na to, ko\u013eko % table cache je vyu\u017eit\u00fdch, ko\u013eko dopytov i\u0161lo do slow logu, at\u010f.) a n\u00e1sledne pravidelne pou\u017e\u00edva\u0165 napr. pt-query-digest na analyzovanie pomal\u00fdch dopytov ka\u017ed\u00fd t\u00fd\u017ede\u0148\/mesiac.<\/p>\n\n\n\n<p>E\u0161te spomenieme n\u00e1stroj <strong>Performance Schema + sys<\/strong> \u2013 to s\u00fa vlastne vstavan\u00e9 \u201cn\u00e1stroje\u201d. Sys schema (u MySQL) pon\u00faka mno\u017estvo u\u017eito\u010dn\u00fdch poh\u013eadov ako sys.statements_with_temp_tables alebo sys.indexes_with_seeks at\u010f., ktor\u00e9 vedia poskytn\u00fa\u0165 preh\u013ead bez extern\u00fdch n\u00e1strojov. MariaDB sys nem\u00e1, ale m\u00e1 INFORMATION_SCHEMA a Performance Schema. Ich vyu\u017eitie je v\u0161ak na samostatn\u00fd \u010dl\u00e1nok.<\/p>\n\n\n\n<p>Pri pou\u017e\u00edvan\u00ed t\u00fdchto n\u00e1strojov v\u017edy <strong>dbajte na bezpe\u010dnos\u0165<\/strong> (napr. pt-online-schema-change pou\u017e\u00edva TRIGGERy \u2013 nezabudnite ich po akcii dropnu\u0165 ak by skript neukon\u010dil, a podobne). Tie\u017e si v\u0161\u00edmajte odpor\u00fa\u010dania z viacer\u00fdch zdrojov \u2013 ak napr. MySQLTuner nie\u010do navrhne, overte to s ofici\u00e1lnou dokument\u00e1ciou \u010di Percona blogom.A hlavne \u2013 <strong>nezab\u00fadajte na zdrav\u00fd rozum<\/strong>. Niekedy n\u00e1stroj povie \u201czv\u00fd\u0161te X na Y\u201d, no vy viete, \u017ee va\u0161a pracovn\u00e1 z\u00e1\u0165a\u017e je \u0161pecifick\u00e1 a \u017ee to by mohlo sp\u00f4sobi\u0165 in\u00fd probl\u00e9m. Berte to ako tip, nie pr\u00edkaz.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Z\u00e1ver a \u010fal\u0161ie kroky<\/strong><\/h2>\n\n\n\n<p>Ladenie v\u00fdkonu datab\u00e1z MySQL\/MariaDB\/Percona je <strong>iterat\u00edvny proces<\/strong>. Za\u010d\u00edna sa pochopen\u00edm probl\u00e9mov cez monitoring, pokra\u010duje \u00fapravou konfigur\u00e1cie a optimaliz\u00e1ciou dopytov, a nekon\u010d\u00ed nikdy \u2013 s meniacou sa z\u00e1\u0165a\u017eou a rast\u00facimi d\u00e1tami mus\u00edme st\u00e1le dohliada\u0165 na to, \u010di je potrebn\u00e9 nie\u010do doladi\u0165.<\/p>\n\n\n\n<p>Hlavn\u00e9 body, ktor\u00e9 si odtia\u013eto odnies\u0165:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Monitorujte<\/strong>: Bez d\u00e1t naslepo netraf\u00edte. Sledujte CPU, pam\u00e4\u0165, I\/O, vyu\u017e\u00edvajte slow query log. Z\u00e1rove\u0148 monitorujte dostupnos\u0165 a z\u00e1lohy.<br><\/li>\n\n\n\n<li><strong>Optimalizujte konfigur\u00e1ciu<\/strong>: Najm\u00e4 InnoDB a cache parametre. Upravte buffer pool, logy, flush nastavenia pod\u013ea va\u0161ej RAM a typu disku. Ka\u017ed\u00fa zmenu otestujte.<br><\/li>\n\n\n\n<li><strong>Optimalizujte dopyty a sch\u00e9mu<\/strong>: Toto je \u010dasto najv\u00e4\u010d\u0161ia v\u00fdhra. Indexy dok\u00e1\u017eu z\u00e1zraky. Spolupracujte s v\u00fdvoj\u00e1rmi, poskytujte im d\u00e1ta o pomal\u00fdch dopytoch.<br><\/li>\n\n\n\n<li><strong>Overujte n\u00e1stroje<\/strong>: Pou\u017eite skripty a toolkity na identifik\u00e1ciu slab\u00fdch miest, ale z\u00e1very implementujte s rozvahou. V\u017edy majte <strong>z\u00e1lohu konfigur\u00e1cie a d\u00e1t<\/strong> pred v\u00e4\u010d\u0161\u00edmi zmenami.<br><\/li>\n\n\n\n<li><strong>Postupn\u00e9 ladenie<\/strong>: Ne\u010dakajte, \u017ee jedn\u00fdm z\u00e1sahom vyrie\u0161ite v\u0161etko. Mo\u017eno zv\u00fd\u0161enie buffer poolu odhal\u00ed, \u017ee teraz je bottleneck inde (napr. CPU kv\u00f4li neoptimal dopytu).<br><\/li>\n\n\n\n<li><strong>Upgrade softv\u00e9ru<\/strong>: Dr\u017ete sa podporovan\u00fdch verzi\u00ed &#8211; nov\u00e9 verzie prin\u00e1\u0161aj\u00fa fixy. Nov\u0161ie verzie m\u00f4\u017eu ma\u0165 lep\u0161\u00ed v\u00fdkon bez inej zmeny.<br><\/li>\n\n\n\n<li><strong>Hardware vs optimaliz\u00e1cia<\/strong>: Ak po vyladen\u00ed st\u00e1le v\u00fdkon neposta\u010duje, zv\u00e1\u017ete upgrade HW (viac RAM je v\u017edy dobr\u00e1 invest\u00edcia, r\u00fdchlej\u0161ie CPU pom\u00f4\u017ee line\u00e1rne pri single-thread dopytoch, viac jadier pom\u00f4\u017ee paralelnej z\u00e1\u0165a\u017ei) alebo rozdelenie z\u00e1\u0165a\u017ee. Ale najprv si bu\u010fte ist\u00ed, \u017ee ste softv\u00e9rovo optimalizovali maximum \u2013 je zbyto\u010dn\u00e9 kupova\u0165 2\u00d7 drah\u0161\u00ed server, ak by sta\u010dilo prida\u0165 jeden index \ud83d\ude09.<\/li>\n<\/ul>\n\n\n\n<p>Datab\u00e1za sa v\u00e1m odv\u010fa\u010d\u00ed plynul\u00fdm chodom a va\u0161i pou\u017e\u00edvatelia r\u00fdchlymi odozvami aplik\u00e1cie. Prajeme ve\u013ea \u00faspechov pri laden\u00ed.<\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Spr\u00e1vne ladenie v\u00fdkonu datab\u00e1zov\u00e9ho servera je k\u013e\u00fa\u010dov\u00e9 pre stabilitu a r\u00fdchlos\u0165 (nielen webov\u00fdch) aplik\u00e1ci\u00ed. Datab\u00e1zy MySQL (a jej klony MariaDB a Percona Server) v predvolenom nastaven\u00ed neb\u00fdvaj\u00fa vyladen\u00e9 pre optim\u00e1lny resp. maxim\u00e1lny v\u00fdkon \u2013 sk\u00f4r s\u00fa nastaven\u00e9 konzervat\u00edvne, aby fungovali na priemernom hardv\u00e9ri. Bez optimaliz\u00e1cie m\u00f4\u017ee doch\u00e1dza\u0165 k pomal\u00fdm odozv\u00e1m,&#8230;<\/p>\n","protected":false},"author":57,"template":"","format":"standard","meta":{"footnotes":""},"ht-kb-category":[33],"ht-kb-tag":[330,564,360,381],"class_list":["post-33345","ht_kb","type-ht_kb","status-publish","format-standard","hentry","ht_kb_category-servery","ht_kb_tag-linux","ht_kb_tag-mariadb","ht_kb_tag-mysql","ht_kb_tag-server"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Ladenie v\u00fdkonu MySQL, MariaDB a Percona servera pre syst\u00e9mov\u00fdch administr\u00e1torov - Websupport centrum podpory<\/title>\n<meta name=\"description\" content=\"Spr\u00e1vne ladenie v\u00fdkonu datab\u00e1zov\u00e9ho servera je k\u013e\u00fa\u010dov\u00e9 pre stabilitu a r\u00fdchlos\u0165 (nielen webov\u00fdch) aplik\u00e1ci\u00ed.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.websupport.sk\/podpora\/kb\/ladenie-vykonu-mysql-mariadb-a-percona-servera-pre-systemovych-administratorov\/\" \/>\n<meta property=\"og:locale\" content=\"sk_SK\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Ladenie v\u00fdkonu MySQL, MariaDB a Percona servera pre syst\u00e9mov\u00fdch administr\u00e1torov - Websupport centrum podpory\" \/>\n<meta property=\"og:description\" content=\"Spr\u00e1vne ladenie v\u00fdkonu datab\u00e1zov\u00e9ho servera je k\u013e\u00fa\u010dov\u00e9 pre stabilitu a r\u00fdchlos\u0165 (nielen webov\u00fdch) aplik\u00e1ci\u00ed.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.websupport.sk\/podpora\/kb\/ladenie-vykonu-mysql-mariadb-a-percona-servera-pre-systemovych-administratorov\/\" \/>\n<meta property=\"og:site_name\" content=\"Websupport centrum podpory\" \/>\n<meta property=\"article:modified_time\" content=\"2025-07-24T11:46:49+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.websupport.sk\/podpora\/app\/uploads\/sites\/2\/2025\/07\/VYLADTE-SVOJ-VYKON_1200x628.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"628\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Predpokladan\u00fd \u010das \u010d\u00edtania\" \/>\n\t<meta name=\"twitter:data1\" content=\"44 min\u00fat\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.websupport.sk\\\/podpora\\\/kb\\\/ladenie-vykonu-mysql-mariadb-a-percona-servera-pre-systemovych-administratorov\\\/\",\"url\":\"https:\\\/\\\/www.websupport.sk\\\/podpora\\\/kb\\\/ladenie-vykonu-mysql-mariadb-a-percona-servera-pre-systemovych-administratorov\\\/\",\"name\":\"Ladenie v\u00fdkonu MySQL, MariaDB a Percona servera pre syst\u00e9mov\u00fdch administr\u00e1torov - Websupport centrum podpory\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.websupport.sk\\\/podpora\\\/#website\"},\"datePublished\":\"2025-07-24T10:59:07+00:00\",\"dateModified\":\"2025-07-24T11:46:49+00:00\",\"description\":\"Spr\u00e1vne ladenie v\u00fdkonu datab\u00e1zov\u00e9ho servera je k\u013e\u00fa\u010dov\u00e9 pre stabilitu a r\u00fdchlos\u0165 (nielen webov\u00fdch) aplik\u00e1ci\u00ed.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.websupport.sk\\\/podpora\\\/kb\\\/ladenie-vykonu-mysql-mariadb-a-percona-servera-pre-systemovych-administratorov\\\/#breadcrumb\"},\"inLanguage\":\"sk-SK\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.websupport.sk\\\/podpora\\\/kb\\\/ladenie-vykonu-mysql-mariadb-a-percona-servera-pre-systemovych-administratorov\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.websupport.sk\\\/podpora\\\/kb\\\/ladenie-vykonu-mysql-mariadb-a-percona-servera-pre-systemovych-administratorov\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.websupport.sk\\\/podpora\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Ladenie v\u00fdkonu MySQL, MariaDB a Percona servera pre syst\u00e9mov\u00fdch administr\u00e1torov\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.websupport.sk\\\/podpora\\\/#website\",\"url\":\"https:\\\/\\\/www.websupport.sk\\\/podpora\\\/\",\"name\":\"Websupport centrum podpory\",\"description\":\"Radi v\u00e1m pom\u00f4\u017eeme s va\u0161im probl\u00e9mom\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.websupport.sk\\\/podpora\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"sk-SK\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Ladenie v\u00fdkonu MySQL, MariaDB a Percona servera pre syst\u00e9mov\u00fdch administr\u00e1torov - Websupport centrum podpory","description":"Spr\u00e1vne ladenie v\u00fdkonu datab\u00e1zov\u00e9ho servera je k\u013e\u00fa\u010dov\u00e9 pre stabilitu a r\u00fdchlos\u0165 (nielen webov\u00fdch) aplik\u00e1ci\u00ed.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.websupport.sk\/podpora\/kb\/ladenie-vykonu-mysql-mariadb-a-percona-servera-pre-systemovych-administratorov\/","og_locale":"sk_SK","og_type":"article","og_title":"Ladenie v\u00fdkonu MySQL, MariaDB a Percona servera pre syst\u00e9mov\u00fdch administr\u00e1torov - Websupport centrum podpory","og_description":"Spr\u00e1vne ladenie v\u00fdkonu datab\u00e1zov\u00e9ho servera je k\u013e\u00fa\u010dov\u00e9 pre stabilitu a r\u00fdchlos\u0165 (nielen webov\u00fdch) aplik\u00e1ci\u00ed.","og_url":"https:\/\/www.websupport.sk\/podpora\/kb\/ladenie-vykonu-mysql-mariadb-a-percona-servera-pre-systemovych-administratorov\/","og_site_name":"Websupport centrum podpory","article_modified_time":"2025-07-24T11:46:49+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/www.websupport.sk\/podpora\/app\/uploads\/sites\/2\/2025\/07\/VYLADTE-SVOJ-VYKON_1200x628.png","type":"image\/png"}],"twitter_card":"summary_large_image","twitter_misc":{"Predpokladan\u00fd \u010das \u010d\u00edtania":"44 min\u00fat"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.websupport.sk\/podpora\/kb\/ladenie-vykonu-mysql-mariadb-a-percona-servera-pre-systemovych-administratorov\/","url":"https:\/\/www.websupport.sk\/podpora\/kb\/ladenie-vykonu-mysql-mariadb-a-percona-servera-pre-systemovych-administratorov\/","name":"Ladenie v\u00fdkonu MySQL, MariaDB a Percona servera pre syst\u00e9mov\u00fdch administr\u00e1torov - Websupport centrum podpory","isPartOf":{"@id":"https:\/\/www.websupport.sk\/podpora\/#website"},"datePublished":"2025-07-24T10:59:07+00:00","dateModified":"2025-07-24T11:46:49+00:00","description":"Spr\u00e1vne ladenie v\u00fdkonu datab\u00e1zov\u00e9ho servera je k\u013e\u00fa\u010dov\u00e9 pre stabilitu a r\u00fdchlos\u0165 (nielen webov\u00fdch) aplik\u00e1ci\u00ed.","breadcrumb":{"@id":"https:\/\/www.websupport.sk\/podpora\/kb\/ladenie-vykonu-mysql-mariadb-a-percona-servera-pre-systemovych-administratorov\/#breadcrumb"},"inLanguage":"sk-SK","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.websupport.sk\/podpora\/kb\/ladenie-vykonu-mysql-mariadb-a-percona-servera-pre-systemovych-administratorov\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.websupport.sk\/podpora\/kb\/ladenie-vykonu-mysql-mariadb-a-percona-servera-pre-systemovych-administratorov\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.websupport.sk\/podpora\/"},{"@type":"ListItem","position":2,"name":"Ladenie v\u00fdkonu MySQL, MariaDB a Percona servera pre syst\u00e9mov\u00fdch administr\u00e1torov"}]},{"@type":"WebSite","@id":"https:\/\/www.websupport.sk\/podpora\/#website","url":"https:\/\/www.websupport.sk\/podpora\/","name":"Websupport centrum podpory","description":"Radi v\u00e1m pom\u00f4\u017eeme s va\u0161im probl\u00e9mom","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.websupport.sk\/podpora\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"sk-SK"}]}},"_links":{"self":[{"href":"https:\/\/www.websupport.sk\/podpora\/wp-json\/wp\/v2\/ht-kb\/33345","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.websupport.sk\/podpora\/wp-json\/wp\/v2\/ht-kb"}],"about":[{"href":"https:\/\/www.websupport.sk\/podpora\/wp-json\/wp\/v2\/types\/ht_kb"}],"author":[{"embeddable":true,"href":"https:\/\/www.websupport.sk\/podpora\/wp-json\/wp\/v2\/users\/57"}],"version-history":[{"count":4,"href":"https:\/\/www.websupport.sk\/podpora\/wp-json\/wp\/v2\/ht-kb\/33345\/revisions"}],"predecessor-version":[{"id":33366,"href":"https:\/\/www.websupport.sk\/podpora\/wp-json\/wp\/v2\/ht-kb\/33345\/revisions\/33366"}],"wp:attachment":[{"href":"https:\/\/www.websupport.sk\/podpora\/wp-json\/wp\/v2\/media?parent=33345"}],"wp:term":[{"taxonomy":"ht_kb_category","embeddable":true,"href":"https:\/\/www.websupport.sk\/podpora\/wp-json\/wp\/v2\/ht-kb-category?post=33345"},{"taxonomy":"ht_kb_tag","embeddable":true,"href":"https:\/\/www.websupport.sk\/podpora\/wp-json\/wp\/v2\/ht-kb-tag?post=33345"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}