Motoare de stocare
Motoarele de stocare (Storage Engines) administreaza maniera in care datele sunt organizate, stocate si accesate. Sistemele de tip MySQL utilizeaza o arhitectura de motoare de stocare conectabile. Exemple de astfel de motoare de stocare sunt MyISAM, InnoDB, NDB (MySQL Cluster), Archive, CSV sau Memory. Motoarele de stocare sunt responsabile cu executia instructiunilor SQL, dar si cu preluarea datelor din fisiere de date.
Pentru a determina care sunt motoarele de stocare disponibile pe un sistem MySQL poate fi utilizata comanda SHOW ENGINES. Valoarea din cadrul optiunii Support indica daca un motor de stocare este disponibil sau nu.
mysql> SHOW ENGINES\G *************************** 1. row *************************** Engine: FEDERATED Support: NO Comment: Federated MySQL storage engine Transactions: NULL XA: NULL Savepoints: NULL *************************** 2. row *************************** Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tables Transactions: NO XA: NO Savepoints: NO *************************** 3. row *************************** Engine: InnoDB Support: DEFAULT Comment: Supports transactions, row-level locking, and foreign keys Transactions: YES XA: YES Savepoints: YES *************************** 4. row *************************** Engine: PERFORMANCE_SCHEMA Support: YES Comment: Performance Schema Transactions: NO XA: NO Savepoints: NO *************************** 5. row *************************** Engine: MyISAM Support: YES Comment: MyISAM storage engine Transactions: NO XA: NO Savepoints: NO *************************** 6. row *************************** Engine: MRG_MYISAM Support: YES Comment: Collection of identical MyISAM tables Transactions: NO XA: NO Savepoints: NO *************************** 7. row *************************** Engine: BLACKHOLE Support: YES Comment: /dev/null storage engine (anything you write to it disappears) Transactions: NO XA: NO Savepoints: NO *************************** 8. row *************************** Engine: CSV Support: YES Comment: CSV storage engine Transactions: NO XA: NO Savepoints: NO *************************** 9. row *************************** Engine: ARCHIVE Support: YES Comment: Archive storage engine Transactions: NO XA: NO Savepoints: NO
Motoarele de stocare determina performantele unei baze de date; daca este utilizat un motor de stocare adecvat, performatele unei baze de date se pot imbunatati. Trebuie avute in vedere o serie de caracteristici ale motoarelor de stocare:
• suport pentru tranzactii (transactional support): o tranzactie este un set de instructiuni SQL care sunt executate ca o singura unitate; pentru ca o tranzactie sa fie finalizata si pentru a salva modificarile aduse, toate instructiunile din tranzactie trebuie sa fie executate cu succes; daca nu este necesara operarea prin intermediul tranzactiilor, stabilirea unui motor de stocare netranzactional poate duce la utilizarea mult mai buna a resurselor;
• blocare (locking): in functie de aplicatie putem avea blocare la nivel de inregistrare sau la nivel de tabel;
• indexare: motoarele de stocare utilizeaza diferite strategii de indexare a datelor;
• chei externe: sunt disponibile doar pentru anumite motoare de stocare (ex. InnoDB);
• buffering: si acesata caracteristica este disponibila pentru anumite motoare de stocare; MyISAM implementeaza aceasta caracteristica doar pentru indecsi, nu si pentru date;
• stocare: unele motoare de stocare pastreaza datele si indecsii in fisiere specifice, in timp ce altele utilizeaza fisiere centralizate (ex. metadate).
Pentru un sistem de tip MySQL motorul de stocare implicit poate fi modificat prin intermediul fisierelor de configurare. Incepand cu versiunea 5.5, motorul de stocare implicit este InnoDB; inainte de aceasta versiune, motorul de stocare implicit era MyISAM.
[mysqld] default_storage_engine = InnoDB
sudo service mysql restart
Motor stocare | MyISAM | InnoDB | Archive | Memory | NDB |
---|---|---|---|---|---|
Tranzactii | nu | da | nu | nu | da |
Blocare | tabel | inregistrare | inregistrare | tabel | inregistrare |
Cache date | nu | da | nu | N/A | da |
Cache indecsi | da | da | nu | N/A | da |
Criptare date | da | da | da | da | da |
Replicare | da | da | da | da | da |
Chei externe | nu | da | nu | nu | da |
Cache interogare | da | da | da | da | da |
La nivel de tabel, motorul de stocare poate fi precizat in momentul crearii acestuia (tabelului) sau in momentul modificarii lui.
CREATE TABLE test(id int auto_increment primary key, col varchar(20)) ENGINE = INNODB;
ALTER TABLE test ENGINE = MyISAM;
In plus, motorul de stocare implicit poate fi stabilit si la nivel de sesiune prin intermediul variabilei default_storage_engine.
SET default_storage_engine = InnoDB;
InnoDB este un motor de stocare tranzactional compatibil cu poprietatile modelului ACID (atomicitate, consistenta, izolare, durabilitate). Ofera suport pentru blocare la nivel de linie de date (inregistrare) si reprezinta un mediu ideal pentru sisteme concurente cu scrieri frecvente. Operatiile rulate pe tabele InnoDB pot fi executate prin intermediul tranzactiilor; acestea permit executarea unui numar de instructiuni SQL ca o singura entitate. De asemenea, motorul de stocare InnoDB recunoste automat blocarile fara iesire (deadlocks) (doua procese se blocheaza mutual) si opreste unul din cele doua procese intr-o maniera automata.
Spatiul tabel (tablespace) este un grup logic de unul sau mai multe fisiere ale unei baze de date. Pentru motorul de stocare InnoDB, spatiul table poate fi configurat in doua moduri: un spatiu impartit de toate tabelele (shared tablespace); un spatiu tabel disponibil la nivelul fiecarui tabel (per-table tabelspace). Optiunea innodb_file_per_table precizeaza tipul spatiului tabel InnoDB.
Daca optiunea innodb_file_per_table prezinta valoarea 0, sistemul stocheaza metadate, date si indecsi in fisiere ibdata (shared). Cum aceste fisiere pot ajunge la dimensiuni destul de mari, o practica buna consta in impartirea lor in mai multe fisiere. Implicit fisierele ibdata sunt disponibile in locatia specifica datelor (datadir).
In cazul in care optiunea innodb_file_per_table are valoarea 1, sistemul stocheaza datele si indecsi corespunzatori fiecarui tabel intr-un fisier .ibd separat. Spre deosebire de fisierele ibdata in care sunt distribuite date din mai multe tabele, fisierele ibd ale unui tabel se pot micsora, putand recupera spatiul atunci cand tabelele sunt defragmentate sau trunchiate. La utilizarea spatiului tabel de la nivelul fiecarui tabel este utilizat un fisier ibdata comun, pentru a stoca metadatele.
[mysqld] innodb_file_per_table = 1 innodb_data_file_path = ibdata1:10M:autoextend
sudo service mysql restart
MyISAM este un motor de stocare netranzactional ce a fost utilizat ca si motor implicit pentru sistemele de tip MySQL inca din versiunea 3.2 (pana la versiunea 5.4). Motorul de stocare MyISAM ofera viteza mare pentru operatii de citire. Pentru tabelele care utilizeaza motorul de stocare MyISAM sunt definite trei tipuri de fisiere: .sdi structura tabel, .myi index tabel, .myd date tabel. Datorita faptului ca MyISAM implementeaza un mecanism de blocare la nivel de tabel, in cazul rularii unui numar mare de operatii concurente de scriere intr-un tabel, poate aparea o blocare la citire.
O caracteristica importanta a motorului de stocare MyISAM o reprezinta posibilitatea de dimensionare a memoriei (key_buffer_size) ce urmeaza a fi utilizata atat in stocarea indecsilor, cat si in procese de regasire si sortare a acestora. Valoarea corespunzatoare pentru aceasta variabila de sistem este implicit 8MB; nu se recomanda ca aceasta valoare sa depaseasca 30% din memoria disponibila.
[mysqld] key_buffer_size = 1GB delay_key_write = 1
sudo service mysql restart
O alta optiune specifica motorului de stocare MyISAM, cu implicatii in performanta tabelelor, o reprezinta delay_key_write. Daca este activata, acesta optiune determina actualizarea mult mai rapida a indecsilor (indecsii nu sunt actualizati decat in momentul in care tabelul este inchis). Optiunea poate fi activata si la nivel de tabel, in special pentru cazul in care sunt rulate intensiv operatii care determina actualizarea datelor.
ALTER TABLE test DELAY_KEY_WRITE = 1;
Optiunea (delay_key_write) determina o imbunatatire a performantelor, dar trebuie avut in vedere si faptul ca poate cauza coruperea/deteriorarea datelor in cazul in care sistemul se blocheaza. Daca procesul corespunzator serverului (mysqld) este oprit in timpul unei operatii de scriere, la nivelul tabelelor pot aparea date corupte. Din acest punct de vedere prezinta importanta verificarea starii unui tabel sau repararea acestuia.
CHECK TABLE test; REPAIR TABLE test;
Modelul ACID
Modelul ACID este un set de principii de proiectare a sistemelor de gestiune a bazelor de date care asigura calitatea datelor in situatii neprevazute. Motorul de stocare InnoDB adera la acest model. Prin urmare, o tranzactie trebuie sa fie conforma ACID, adica sa respecte caracteristicile de atomicitate, consistenta, izolare si durabilitate.
Avand aceste caracteristici, o serie de instructiuni fie se executa toate cu succes si modificarile sunt salvate in baza de date, fie, daca executia este intrerupta, modificarile sunt eliminate si baza de date nu este schimbata. Respectand principiile ACID, sistemul de gestiune a bazelor de date trebuie sa asigure consistenta datelor si mecanisme de recuperare in caz de erori tranzitorii sau permanente.
Atomicitate
Se refera la situatia in care fie toate instructiunile dintr-o tranzactie sunt finalizate, fie nici una din ele nu este executata. Fiecare tranzactie este atomica, chiar daca are mai multe instructiuni. Daca o instructiune dintr-o tranzactie esueaza, esueaza si tranzactia.
Consistenta
Caracteristica de consistenta se refera la faptul ca baza de date trece de la o stare consistenta la alta. In absenta tranzactiilor, daca apare o eroare de sistem in timpul unui transfer, baza de date ramane intr-o stare neconsistenta. Singura metoda de rezolvare a acestei stari este stergerea modificarilor deja facute.
Intr-un sistem tranzactional conform ACID, daca o instructiune dintr-o tranzactie esueaza se declanseaza procedura de ROLL BACK pentru a aduce baza de date la ultima stare consistenta. Daca tranzactia se executa cu succes, baza de date trece la noua stare consistenta.
Este in responsabilitatea SGBD-ului si a dezvoltatorilor sa asigure consistenta. Sistemul de gestiune poate verifica respectarea constrangerilor impuse in schema bazei de date, dar nu poate asigura consistenta in cazul unor erori de proiectare.
Izolare
Caracteristica de izolare specifica faptul ca datele modificate de o tranzactie nu pot fi vizualizate sau modificate de o alta tranzactie decat dupa finalizarea primei tranzactii. Aceasta caracteristica este importanta pentru a asigura executia concurenta a interogarilor, facilitate critica in sistemele de baze de date moderne. Folosind izolarea, se pot rula tranzactii concurente fara a afecta consistenta datelor.
Durabilitatea
Prin durabilitate se descrie principiul prin care daca o tranzactie este finalizata cu succes, rezultatele sunt salvate in baza de date permanent. Dupa acest punct tranzactia este finalizata si modificarile trebuie sa supravietuiasca si unei erori de sistem. Daca apare o eroare de sistem intre finalizarea cu succes a tranzactiei si scrierea datelor pe disc, baza de date marcheaza tranzactia ca finalizata cu succes, chiar daca datele nu sunt modificate in concordanta.
Caracteristica de durabilitate este implementata prin scrierea tranzactiilor finalizate cu succes intr-un fisier de log (redo log), care poate fi utilizat pentru recrearea starii curente a bazei de date, precursoare unei erori de sistem.