15 korisnih savjeta za podešavanje i optimizaciju performansi MySQL/MariaDB

MySQL moćan je Sustav upravljanja relacijskom bazom podataka otvorenog koda ili skraćeno RDBMS. Izdata je davne 1995. godine (stara 20 godina). Koristi Structured Query Language koji je vjerojatno najpopularniji izbor za upravljanje sadržajem unutar baze podataka. Najnovija verzija MySQL-a je 5.6.25 i objavljena je 29. svibnja 2015..

Zanimljiva činjenica o MySQL-u je činjenica da ime dolazi od kćeri Michaela Wideniusa (tvorca MySQL-a) My. Čakiako postoji mnogo zanimljivih činjenica o MySQL-u, ovaj vam članak treba pokazati neke korisne prakse koje će vam pomoći u upravljanju vašim MySQL poslužiteljem.

U travnju 2009 projekt MySQL kupio je Oracle. Kao rezultat toga stvorena je MySQL zajednica pod nazivom MariaDB. Glavni razlog za stvaranje forka bio je da projekt ostane slobodan pod Općom javnom licencom.

Danas su MySQL i MariaDB jedan od najčešće (ako ne i najčešće) korištenih RDBMS-a koji se koriste za web aplikacije kao što su WordPress, Joomla, Magento i drugi.

Ovaj članak će vam pokazati neke osnovne, ali korisne savjete kako optimizirati fino podešavanje performansi MySQL/MariaDB. Imajte na umu da ovaj članak pretpostavlja da već imate instaliran MySQL ili MariaDB. Ako se još uvijek pitate kako ih instalirati na svoj sustav, možete slijediti naše opsežne vodiče ovdje:

  1. Instaliranje LAMP-a na RHEL/CentOS 7
  2. Instaliranje LAMP-a na Fedora 22
  3. Postavljanje LAMP-a na Ubuntu 15.04
  4. Instaliranje MariaDB na Debian 8
  5. Instalirajte MariaDB na Gentoo Linux
  6. Instalirajte MariaDB na Arch Linux

Važno: Prije nego što počnemo – nemojte slijepo prihvaćati ove prijedloge. Svaka postavka MySQL-a je jedinstvena i zahtijeva dodatno razmišljanje prije bilo kakvih promjena.

Stvari koje trebate znati:

  1. Konfiguracijska datoteka MySQL/MariaDB nalazi se u /etc/my.cnf. Svaki put kada izmijenite ovu datoteku morat ćete ponovno pokrenuti MySQL uslugu kako bi nove promjene mogle stupiti na snagu.
  2. Za pisanje ovog članka MySQL verzija 5.6 korištena je kao predložak.

1. Omogućite InnoDB datoteku po tablici

Prvo je važno objasniti da je InnoDB mehanizam za pohranu podataka. MySQL i MariaDB koriste InnoDB kao zadani mehanizam za pohranu. U prošlosti se MySQL koristio za čuvanje tablica baze podataka i indeksa u tabličnom prostoru sustava. Ovaj pristup je bio namijenjen poslužiteljima čija je jedina svrha obrada baze podataka i njihov disk za pohranu se ne koristi u bilo koje druge svrhe.

InnoDB pruža fleksibilniji pristup i svaka informacija baze podataka čuva se u .ibd podatkovnoj datoteci. Svaka .ibd datoteka predstavlja vlastiti tablični prostor. Na taj način se operacije baze podataka kao što je “TRUNCATE” mogu dovršiti brže, a također možete povratiti neiskorišteni prostor prilikom ispuštanja ili skraćivanja tablice baze podataka.

Još jedna prednost ove konfiguracije je činjenica da neke od tablica baze podataka možete držati u zasebnom uređaju za pohranu. Ovo može znatno poboljšati I/O opterećenje vaših diskova.

innodb_file_per_table omogućen je prema zadanim postavkama u MySQL 5.6 i novijim verzijama. To možete vidjeti u datoteci /etc/my.cnf. Direktiva izgleda ovako:

innodb_file_per_table=1

2. Pohranite MySQL podatke baze podataka na zasebnu particiju

Napomena: Ova postavka radi samo s MySQL, ali ne i s MariaDB.

Ponekad čitanje/pisanje OS-a može usporiti performanse vašeg MySQL poslužitelja, posebno ako se nalazi na istom tvrdom disku. Umjesto toga, preporučio bih korištenje zasebnog tvrdog diska (po mogućnosti SSD) za MySQL uslugu.

Da biste ovo dovršili, morat ćete priključiti novi pogon na svoje računalo/poslužitelj. Za potrebe ovog članka, pretpostavit ću da će pogon biti pod /dev/sdb.

Sljedeći korak je priprema novog pogona:

fdisk /dev/sdb

Sada pritisnite “n” za stvaranje nove particije. Zatim pritisnite “p” kako biste novu particiju učinili primarnom. Nakon toga postavite broj particije od 1-4. Nakon toga ćete odabrati veličinu particije. Pritisnite enter ovdje. U sljedećem koraku morat ćete konfigurirati veličinu particije.

Ako želite koristiti cijeli disk pritisnite enter još jednom. Inače možete ručno postaviti veličinu nove particije. Kada budete spremni, pritisnite “w” za pisanje promjena. Sada ćemo morati stvoriti datotečni sustav za našu novu particiju. To se lako može učiniti pomoću:

mkfs.ext4 /dev/sdb1

Sada ćemo montirati našu novu particiju u mapu. Nazvao sam mapu “ssd” i stvorio u korijenskom direktoriju:

mkdir /ssd/

Spremni smo montirati novu particiju koju smo upravo napravili u novu mapu:

mount /dev/sdb1  /ssd/

Možete izvršiti montiranje pri pokretanju dodavanjem sljedećeg retka u datoteku /etc/fstab.

/dev/sdb1 /ssd ext3 defaults 0 0

Sada ste spremni premjestiti MySQL na novi disk. Prvo zaustavite MySQL uslugu s:

service mysqld stop

Preporučio bih da zaustavite i Apache/nginx kako biste spriječili sve pokušaje pisanja u bazama podataka:

service httpd stop
service nginx stop

Sada kopirajte cijeli MySQL direktorij u novi pogon:

cp /var/lib/mysql /ssd/ -Rp

Ovo može potrajati, ovisno o mjestu vaših MySQL baza podataka. Kada se ovaj proces završi, preimenujte MySQL direktorij:

mv /var/lib/mysql /var/lib/mysql-backup

Zatim ćemo stvoriti simboličku vezu.

ln -s /ssd/mysql /var/lib/mysql

Sada ste spremni pokrenuti svoj MySQL i web uslugu:

service mysqld start
service httpd start
service nginx start

U ovom trenutku će se vašim MySQL bazama podataka pristupati s novog pogona.