Jedna od posebnosti arhitekture relacijskog sustava za upravljanje bazom podataka MySQL (slika 1.) je u tome što najniži sloj (MySQL Storage Engine – u nastavku teksta MSE) omogućava korištenje različitih modula za pristup podacima zapisanim na disku.
Slika 1. Arhitektura MySQL sustava
Neki od dostupnih, i ujedno najpopularnijih, modula za MSE nabrojeni su na prethodnoj slici (MyISAM, InnoDB, Memory, NDB), ali je broj dostupnih MSE modula u praksi bitno veći. Ovisno o ugrađenim karakteristikama od strane njihovih proizvođača, korištenje pojedinih MSE modula u određenim situacijama nudi brojne prednosti, ali često pokazuje i nedostatke za druge uvjete koji se mogu pojaviti tijekom korištenja baze podataka. Zato je bitno razumjeti karakteristike pojedinih MSE modula kako bi se u konkretnoj situaciji izabralo optimalno rješenje.
U istoj bazi podataka moguće je istovremeno koristiti različite MSE module za definiranje tablica (primjer na slici 2.). Ako za takvo rješenje nema posebnih razloga, bolje je cijelu bazu podataka izgraditi na istom MSE modulu.
Tijekom korištenja MySQL baze podataka različiti MSE moduli međusobno ne komuniciraju izravno, nego isključivo preko sloja više razine koji sadrži parser, optimizer, i druge module za analizu i izvođenje SQL upita. Komunikacija između MSE modula i sloja više razine izvodi se preko sustava Storage API.
Slika 2. Primjer korištenja različitih MSE modula u istoj bazi podataka
U nastavku teksta opisane su karakteristike nekoliko najpoznatijih MSE modula i situacije kad određena tehnologija predstavlja optimalan izbor.
Usporedni prikaz najvažnijih karakteristika nekoliko najpoznatijih MSE modula
MyISAM
Podrazumijevana verzija MSE modula do MySQL verzije 5.1. podržava brojna napredna svojstva u radu s bazom podataka, kao što su: full-text indeksi, kompresija podataka ili podrška za prostorno orijentirane tipove podataka.
Najvažniji nedostaci MyISAM tehnologije odnose se na izostanak podrške za korištenje transakcija, najmanji opseg zaključavanja podataka kod izvođenja odgovarajućih SQL naredbi ograničen je na cijelu tablicu te postoji slabija otpornost na potencijalne havarije sustava. Na primjer, nakon havarije sustava neke tablice mogu ostati u „oštećenom“ stanju pa njihovo korištenje nije moguće bez obnavljanja tablice iz rezervne kopije – uz potencijalni rizik gubitaka podataka nastalih nakon zadnje izrade rezervne kopije podataka.
Zbog navedenih nedostataka MyISAM tehnologija nije preporučljiva za korištenje u rješenjima gdje postoji potreba za velikim brojem ažuriranja postojećih podataka (zaključavanje na razini tablice) ili garantiranim zapisom podataka o nekom objektu/događaju u veći broj tablica (ne postoji podrška za transakcije). Zato je u novim rješenjima treba izbjegavati uvijek kad je to moguće, osim možda u slučaju „read-only“ orijentiranih rješenja, gdje se većina operacija odnosi na čitanje ili pretraživanje postojećih podataka.
InnoDB
InnoDB tehnologija predstavlja podrazumijevanu tehnologiju za MSE module za najnovije verzije MySQL-a. Prelaskom MySQL-a u vlasništvo poduzeća Oracle kontinuirano se radi na nadograđivanju i poboljšanju mogućnosti InnoDB tehnologije. U InnoDB MSE module ugrađena je većina naprednih mogućnosti koje se očekuju od relacijskih baza podataka, kao što su podrška za korištenje transakcije ili zaključavanje resursa na razini slogova. Ne treba zaboraviti i na bitno poboljšanu otpornost na eventualne havarije sustava.
Zbog svega navedenog InnoDB tehnologija predstavlja optimalan odabir za izgradnju većine novih sustava, izuzev onih za vrlo velike baze podataka zasnovane na distribuiranoj arhitekturi sustava. Također, ako je to potrebno i izvedivo, preporučljivo je postojeće MyISAM baze podataka prevesti u innoDB tehnologiju.
Memory
Kod korištenja ove tehnologije svi podaci kojima se trenutačno pristupa nalaze se u RAM memoriji računala. To garantira najveću brzinu rada u odnosu na druge tehnologije za MSE module, jer se izbjegava korištenje sporih ulazno/izlaznih operacija prema diskovima. MySQL koristi Memory tehnologiju za izvođenje dijela internih operacija upravo zato da bi se povećala brzina njihovog izvođenja.
Zbog orijentiranosti na korištenje RAM memorije računala, Memory storage engine donosi nekoliko bitnih ograničenja koje treba uzeti u obzir tijekom korištenja:
- Podaci se gube nakon restarta servera pa se navedena tehnologija ne koristi za trajno spremanje podataka.
- Zbog ograničenosti kapaciteta RAM memorije u odnosu na raspoloživi prostor na vanjskim diskovima, rješenje nije primjenjivo ni kao jedino rješenje kod vrlo velikih baza podataka.
- Nije podržan dio standardnih tipova podataka, što znači da se tablice s takvim podacima ne mogu implementirati izravno korištenjem Memory MSE modula. Tijekom korištenja izvodi se njihovo automatsko pretvaranje u podržane tipove podataka.
Jedan od najboljih načina korištenja Memory tehnologije u vlastitim rješenjima (uz standardne operacije kojima se interno koristi sustav MySQL) implementacija je vrlo često korištenih podataka namijenjenih isključivo za čitanje (ako takvi postoje), jer se time mogu podići ukupne performanse sustava izbjegavanjem mnogobrojnih sporih ulazno/izlaznih operacija s diskova.
NDB
NDB tehnologija namijenjena je prije svega za korištenje u distribuiranim okruženjima s većim brojem servera. Kao takva ne predstavlja podrazumijevanu tehnologiju za MySQL, nego je treba posebno preuzeti s adrese http://dev.mysql.com/downloads/cluster/.
Kao i InnoDB podržava brojne napredne osobine podrazumijevane kod relacijskih baza podataka (transakcije, otpornost na havarije i slično). Najveća moguća veličina baze podataka je 384 EB (1 exobyte = 1 milijun TB).
Archive
MSE moduli zasnovani na Archive tehnologiji namijenjeni su za spremanje i naknadni pristup vrlo velikoj količini neindeksiranih podataka (takozvane arhive podataka). Kako bi se smanjilo zauzeće prostora na diskovima, prilikom spremanja podataka izvodi se njihova automatska kompresija korištenjem zlib biblioteke.
Podaci spremljeni u arhivu korištenjem Archive MSE modula ne mogu se naknadno izravno ažurirati ni brisati (nisu podržane SQL naredbe UPDATE ni DELETE). Kod pretraživanja podataka nije dostupno korištenje indeksa, nego se izvodi isključivo potpuno pretraživanje tablica sa spremljenim podacima (takozvani full scan). Ako je takav način pretraživanja prespor u konkretnom slučaju, onda moguće rješenje za uklanjanje problema predstavlja zamjena Archive tehnologije s MyISAM.
Blackhole
Blackhole predstavlja vrlo zanimljivu implementaciju MSE modula jer omogućava pripremu tablica u koju vlastito rješenje može zapisivati podatke, ali tako da ti podaci stvarno ne budu nigdje trajno zapisani. Zbog takve mogućnosti Blackhole može biti primjenjiv kod različitih (automatskih) oblika testiranja nekog projekta, gdje zapravo nema smisla trajno čuvati privremene testne podatke.
CSV
Kod CSV MSE modula, kao što sam naziv govori, podaci se čuvaju u obliku tekstualnih tablica u CSV (Comma-Separated Values) formatu. Riječ je o tekstualno orijentiranim podacima, međusobno odvojenim posebnim znakom (najčešće zarezom), kakvi se u praksi susreću tijekom ETL (Extract, Transform and Load) projekata. Kao međuformat u takvim se procesima vrlo često koriste upravo CSV datoteke, pa odabir ovakvih CSV MSE modula može predstavljati vrlo dobro rješenje kod sudjelovanja MySQL baze u ETL projektima.
Pretvaranje tablica između različitih MSE modula
Zbog međusobno različitih karakteristika MSE modula, u praksi se može pojaviti potreba za pretvaranjem tablica u drugačiji format zapisa. Na primjer, MyISAM u InnoDB, kako bi se dobila mogućnost korištenja transakcija ili preciznije upravljanje zaključavanjem resursa (razina sloga umjesto tablice). Ili pretvaranje nekog drugog oblika zapisa u Archive tip, jer će se neka od baza podataka u budućnosti koristiti isključivo kao arhivska baza podataka s povremenim čitanjem.
U slučaju vrlo velike količine podataka u tablici, postupak pretvaranja tablica između različitih MSE modula može zahtijevati prilično vremena. Postoji nekoliko načina za izvođenje ovog postupka, različitog stupnja složenosti tijekom korištenja, ali istovremeno i različite brzine, to jest djelotvornosti pretvaranja podataka.
1. Korištenje SQL naredbe ALTER TABLE
S gledišta korištenja predstavlja najjednostavniji način pretvaranja tablica između različitih MSE modula. Dovoljno je navesti naziv početne tablice te odredišni oblik zapisa podataka.
Na primjer: ALTER TABLE users ENGINE = InnoDB;
Postupak ima nekoliko nedostataka. Kod vrlo velikih tablica izvođenje postupka može trajati satima ili duže, a za to vrijeme korištenje tablice nije moguće. Istovremeno, ulazno/izlazne operacije na serveru su pod velikim opterećenjem, što može utjecati na cjelokupan rad servera.
2. Izvoz i ponovni uvoz podataka
Kod ovog postupka koriste se standardni MySQL alati za izvoz podataka mysqldump i mysqlimport. Izvezena tablica može se prije ponovnog uvoza urediti u nekom editoru tako da se postojeća tehnologija zapisa podataka izmijeni u novu (na primjer, MyISAM u InnoDB).
Problemi se ponovo mogu javiti kod vrlo velikih tablica, jer je izvezene podatke ponekad vrlo teško (ili čak nemoguće) izmijeniti u nekom od standardnih editora, a u određenim trenucima na računalu postoje bar dvije kopije istih podataka (početni i izvezeni ili izvezeni i odredišni).
3. Punjenje nove tablice pomoću SQL naredbi
U ovom slučaju prvo se pripremi odredišna tablica u drugačijem načinu zapisa (npr. InnoDB) pomoću SQL naredbe CREATE TABLE. Nakon toga se sadržaj tablice iz starog formata seli u novu tablicu pomoću SQL naredbe INSERT INTO … SELECT
Najveća je prednost ovog postupka mogućnost prepisivanja podataka između tablica u dijelovima i to u trenucima najmanje opterećenosti servera. Dio podataka za kopiranje u pojedinom koraku može se ograničiti filtriranjem određenih raspona vrijednosti primarnog ključa. Na kraju postupka prepisuju se samo novi podaci nastali nakon početka prepisivanja podataka u staroj tablici. Budući da je samo u tom trenutku dobro zaključati staru tablicu, kako se u međuvremenu ne bi u njoj ponovo pojavili novi podaci, prednost ovog postupka je također relativno kratko vrijeme nemogućnosti korištenja tablice (stare ili nove).
4. Korištenjem alata pt-online-schema-change (Percona Toolkit)
Pretvaranje tablica u različite formate zapisa može se napraviti i pomoću dodatnog alata pt-online-schema-change proizvođača Percona Toolkit.
Primjer korištenja alata:
pt-online-schema-change --alter "ENGINE=InnoDB" D=sakila,t=actor
Navedeni alat ne predstavlja dio standardne distribucije MySQL-a pa ga treba posebno preuzeti s web-stranica proizvođača. Jedna od najvećih prednosti njegovog korištenja ogleda se u činjenici da tijekom pretvaranja tablice sama tablica nije zaključana za korištenje.