MySQL duomenų bazių valdymo sistemos konfigūravimas

IV_RomanL

MySQL - duomenų bazių valdymo sistema, kurios našumas pagrinde priklauso nuo jai išskirtų resursų - kiek atminties ar procesų galės naudoti ši tarnyba. Jei šių resursų bus išskirta per mažai, Jūsų svetainės veiks lėtai, o serveris neišnaudos turimų resursų, jei šių resursų bus skirta per daug, tai gali lemti nestabilų serverio darbą ar net jo pakibimą.
Pateiksime optimalias pagrindinių kintamųjų, atsakančių už sistemos našumą, reikšmes, kurias galėsite nustatyti savo MySQL konfigūraciniuose failuose, priklausomai nuo pasirinkto dedikuoto serverio plano.
Šiuos kintamuosius būtų galima padalinti į dvi grupes, sesijos bei globalius kintamuosius.


Sesijos kintamųjų sąrašas


Sesijos kintamųjų sąrašas padės nustatyti kokios šiuo metu nustatytos reikšmės jūsų MySQL serveryje. Prisijunkite prie MySQL tarnybos ir įvykdykite šią komandą:

shell> mysqld --verbose --help

Sesijos kintamieji

join_buffer_size - buferis naudojamas nesuindeksuotoms JOIN užklausoms, jungiančioms dvi ar daugiau lentelių;
sort_buffer_size - buferio dydis skirtas SORT BY ar ORDER BY užklausoms;
read_buffer_size - buferis naudojamas nuosekliam visos lentelės skaitymui.
read_rnd_buffer_size - buferis naudojamas skaityti eilutes po įvykdyto rikiavimo, gerina ORDER BY užklausų veikimą.
Sesijos kintamųjų reikšmės neturėtų būti itin didelės, kiekvienai iš Jų rekomenduojame skirti po 2 megabaitus operatyvinės atminties. Esant poreikiui bei resursams, reikšmes galima didinti iki 10 megabaitų.


Globalūs kintamieji


tmp_table_size - laikinųjų lentelių buferio dydis, naudojamas kartu su max_heap_table_size;
max_heap_table_size - MEMORY tipo laikinosios lentelės dydis, naudojamas kartu su tmp_table_size;
key_buffer_size - raktų buferio dydis;
query_cache_size - užklausų buferio dydis.
Optimalus resursų kiekis išskiriamas kiekvienam iš šių kintamųjų galėtų būti skaičiuojamas atsižvelgiant į šiuos kriterijus:
query_cache_size atminties podėliui rekomenduojame išskirti apie 10-15% turimos operatyvinės atminties.
key_buffer_size atminties podėliui rekomenduojame išskirti apie 10-15% turimos operatyvinės atminties.
tmp_table_size bei max_heap_table_size reikšmės turėtų būti vienodos, šiam podėliui rekomenduojame išskirti apie 5-10% operatyvinės atminties. Jei vienam šių podėlių bus išskirta mažiau atminties, MySQL tarnyba vadovausis būtent ta mažesne reikšme


Pavyzdžiui dedikuoto serverio planui "C4" reikšmės galėtų būti tokios:


query_cache_size = 12.5% nuo 4096M = 512M;
key_buffer_size = 12.5% nuo 4096M = 512M;
tmp_table_size = 6.5% nuo 4096M = 256M;
max_heap_table_size = 6.5% nuo 4096M = 256M;


Šių kintamųjų reikšmės turi būti nustatytos MySQL tarnybos konfigūraciniame faile, kurį galite redaguoti įvykdę šią komandą:

nano -w /etc/my.cnf

Pavyzdinis my.cnf failas dedikuoto serverio planui "C4"

# Žemiau pateikiamos reikšmės bus pritaikomos visiems MySQL klientams
[client]
#password       = jusu_slaptazodis
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

# Pateikiamos specifinių programų reikšmės

# MySQL serveris
[mysqld]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
skip-locking
key_buffer = 256M
key_buffer_size = 512M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 512M
tmp_table_size = 256M
max_heap_table_size = 256M

# Dauginame iš esamo server CPU branduolių kiekio*2 thread_concurrency reikšmei nustatyti
thread_concurrency = 4

# Jei nesijungiate prie MySQL serverio iš išorės, tuomet šią nuostatą galite aktyvuoti.
#skip-networking

# Pašalinkite ženklą # , jei naudojate BDB lenteles
#bdb_cache_size = 64M
#bdb_max_lock = 100000

# Pašalinkite ženklą # , jei naudojate InnoDB lenteles

#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/

# Galite nustatyti sesijos kintamojo .._buffer_pool_size dydį iki 50 - 80 %
# serverio virtualios atminties dydžio.

#innodb_buffer_pool_size = 256M
#innodb_additional_mem_pool_size = 20M

# Galite nustatyti reikšmę .._log_file_size iki 25 % sesijos kintamojo 
# .._buffer_pool_size dydžio

#innodb_log_file_size = 64M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

# Pašalinkite žemiau pateiktos reikšmės komentaro simbolį # jei nesate detaliai susipažinę su SQL
#safe-updates

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Atlikę reikiamus pakeitimus teksto redaktoriuje, failą galėsite išsaugoti CTRL+O klaviatūros mygtukų paspaudimu. Norint uždaryti teksto redaktorių spauskite CTRL+X mygtukų derinį.

Norint, kad atlikti pakeitimai įsigaliotų, MySQL tarnybą reikės perkrauti. Tai galite atlikti įvykdę šią komandą:

service mysqld restart

---

Naudingos nuorodos:

MySQL Tuner - perl programavimo kalba parašyta programa gelbėjanti nustatyti optimalias kintamųjų reikšmes.
MySQL programinės įrangos dokumentacija
MySQL atminties kalkuliatorius
my.cnf failo analizatorius

---

Patarimai:

• Intensyviai naudojamai MySQL duomenų bazei išskirkite atskirą dedikuotą serverį, tuomet serverio resursai bus skirti tik MySQL tarnybos palaikymui;
• Optimizuokite MySQL duomenų bazių lenteles;
• Nuolatos stebėkite MySQL programinės įrangos resursų suvartojimą, atitinkamai atlikite pakeitimus konfigūraciniame faile my.cnf ;
• Naudojantiems InnoDB pagrindu veikiančias duomenų bazes, rekomenduojame peržiūrėti Percona MySQL sprendimą.