Optimalizácia MySQL servera

Optimalizácia InnoDB

Aby mal MySQL server optimálny výkon pri väčších databázach, treba v súbore my.ini vhodne nastaviť uvedené parametre.

Innodb_buffer_pool_size - cache pre dáta a indexy tabuliek. Na dedikovaných serveroch nastavujeme v rozmedzí 70-80% celkovej pamäte, nie však mnoho väčšiu, než je súčet veľkostí súborov ibdata.
Innodb_log_file_size - veľkosť logovacieho súboru. Oplatí sa zväčšiť ak sa často zapisujú dáta do databázy. Čím väčšiu hodnotu nastavíme, tým dlhšie bude trvať následná obnova, ak dôjde k pádu SQL servera!
Innodb_log_buffer_size - veľkosť buffera logu pred zápisom do súboru, rozmedzie je 1 až 8 MB. Odporúčame 7 MB.
Innodb_file_per_table - pre databázy EcoSun odporúčame nastaviť na 0, inak výrazne spomaľuje konverziu. Platí pre MySQL od verzie 5.6.
Tmp_table_size - udáva maximálnu veľkosť dočasnej tabuľky, ktorú je možné vytvoriť v operačnej pamäti. Po prekročení tohto limitu sú dočasné tabuľky vytvárané ako MyISAM tabuľky na disku. Súčasne s navýšením hodnoty je dobré zvýšiť tiež nastavenie max_heap_table_size.

Príklad optimálneho nastavenia pre 8 GB RAM a 3 GB databázu:

innodb_buffer_pool_size=4000M
innodb_log_file_size=1000M 
tmp_table_size=4000M
max_connections=100
query_cache_size=100M
innodb_log_buffer_size=7M
max_allowed_packet=32M

Zvýraznené parametre sú najdôležitejšie pre optimálny výkon. Pre iné veľkosti databázy a RAM treba upraviť parameter innodb_buffer_pool_size.

Dôležité: Od verzie MySQL 5.7.20 je defaultne vypnutá query cache, čo má za následok zníženie rýchlosti aplikácie EcoSun na viacerých miestach (napr. výber organizácie na doklade).

Zapnutie query cache v my.ini v sekcii [mysqld]:

query_cache_type=1
query_cache_size=100M

POZOR: Po zmene parametrov treba reštartovať službu MySQL. Ak ste menili parameter innodb_log_file_size, treba službu MySQL zastaviť, vymazať súbory ib_logfile0 a ib_logfile1 v adresári c:\ProgramData\MySQL\MySQL Server 5.x\data\ a službu opäť spustiť.

Zistenie veľkosti MySQL databázy v SQLyog

Ak nie je na serveri dostatok RAM aby sa mohla načítať celá databáza do pamäte, dôjde k výraznému zníženiu výkonu aplikácie pri čítaní údajov z MySQL.