Smeće u bazi, po ko zna koji put…
Standardna početnička greška (ne setovanje enkodinga konekcije) vrlo često rezultuje velikim problemom zvanim smeće u bazi. Primer problema je standardna PHP greška
1. aplikacija misli da radi sa utf8 (forsiran je utf8 enkoding u hederu html-a, web enkodira karatktere kao utf8 i očekuje ih kao utf8)
2. baza čuva podatke kao utf8
3. NIKO NIJE SETOVAO ENKODING KONEKCIJE što u php slučaju znači da je enkoding defaultni – LATIN1.
Šta se desi kada u ovom slučaju pošaljemo utf8 karakter Č u tabelu. Kako je php glup on to pošalje kako ga ima u memoriji, dakle on mysql drajveru pošalje ta dva bajta od kojih je karakter Č sastavljen. Pošto je enkoding konekcije LATIN1 koji ima jednobajtne karaktere, mysql ta dva bajta vidi kao DVA latin1 karaktera, i svaki od njih upiše kao jedan trobajtni utf8 karakter. Sada u polju na mysql-u imam 6 bajtova tj dva karaktera umesto jedan. Kada sledeci put pročitate taj podatak, mysql će prekodirati ta dva utf8 karaktera u 2 jednobajtna latin1 karaktera i vratiti ih php-u koji će ta dva karaktera poslati web browseru koji će pošto očekuje utf8 to pogrešno interpretirati kao utf8 karakter i prikazati slovo Č.
Dakle poslali smo Č, pročitali smo Č – gde je problem? Problem je u tome što u bazi ne piše Č, što sortiranje tog stringa neće biti kao da se na tom mestu nalazi Č, što poredjenje neće raditi, što računanje dužine neće raditi, što zauzima duplo više mesta u bazi…. i najzad, što ako probate da pročitate taj podatak VALIDNO – videćete da je u bazi SMEĆE.
Kako rešiti ovaj problem kada ga zateknemo?
Dakle ako imamo tabelu:
create table `smece` ( `id` int auto_increment primary key not null, `djubre` varchar(100) character set utf8 ) engine=myisam;
i imamo aplikaciju sa početničkom greškom:
< !DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<body>
<form action="djubre.php" method="post">
<input name="q" type="text" />
<input type="submit" />
</form>
< ?php
mysql_connect(':/tmp/mysql.sock', 'root', '') or die(mysql_error());
mysql_select_db('test') or die (mysql_error());
$res = mysql_query('select djubre from smece');
while ($row=mysql_fetch_row($res)) echo $row[0].'<br/>';
@mysql_free_result($res);
if ( isset( $_POST['q'] ) ) {
$djubre = $_POST['q'];
mysql_query("insert into smece (djubre) values ('$djubre')");
mysql_close();
}
?>
</body>
</html>
na web-u se vidi:
dšdšdšd dasdad dšdšdšd dššdšdšdš ccccccccccc šššššššššš ccccccccccccc
ali ako pogledamo u bazi vidimo smeće:
mysql> select * from smece; +----+-------------------------------------------------------------------+ | id | djubre | +----+-------------------------------------------------------------------+ | 1 | ÄÅ¡ÄÅ¡ÄÅ¡Ä | | 2 | dasdad | | 3 | ÄÅ¡ÄÅ¡ÄÅ¡Ä | | 4 | ÄÅ¡Å¡ÄÅ¡ÄÅ¡ÄÅ¡ | | 5 | ÄÄÄÄÄÄÄÄÄÄÄ | | 6 | Å¡Å¡Å¡Å¡Å¡Å¡Å¡Å¡Å¡Å¡ | | 7 | ÄÄÄÄÄÄÄÄÄÄÄÄÄ | +----+-------------------------------------------------------------------+ 7 rows in set (0.00 sec) mysql> select hex(djubre) from smece; +------------------------------------------------------------------------------------------------------------------------------------+ | hex(djubre) | +------------------------------------------------------------------------------------------------------------------------------------+ | C384E28098C385C2A1C384E28098C385C2A1C384E28098C385C2A1C384E28098 | | 646173646164 | | C384E28098C385C2A1C384E28098C385C2A1C384E28098C385C2A1C384E28098 | | C384E28098C385C2A1C385C2A1C384E28098C385C2A1C384E28098C385C2A1C384E28098C385C2A1 | | C384C28DC384C28DC384C28DC384C28DC384C28DC384C28DC384C28DC384C28DC384C28DC384C28DC384C28D | | C385C2A1C385C2A1C385C2A1C385C2A1C385C2A1C385C2A1C385C2A1C385C2A1C385C2A1C385C2A1 | | C384E280A1C384E280A1C384E280A1C384E280A1C384E280A1C384E280A1C384E280A1C384E280A1C384E280A1C384E280A1C384E280A1C384E280A1C384E280A1 | +------------------------------------------------------------------------------------------------------------------------------------+ 7 rows in set (0.00 sec)
Ovo smeće se na webu i dalje vidi kako treba !!!
sada da bi ovo smeće prebacili u ispravan zapis:
mysql> alter table smece change djubre djubre varchar(100) character set latin1; Query OK, 7 rows affected (0.00 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> alter table smece change djubre djubre blob; Query OK, 7 rows affected (0.00 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> alter table smece change djubre djubre varchar(100) character set utf8; Query OK, 7 rows affected (0.01 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> select * from smece; +----+----------------------------+ | id | djubre | +----+----------------------------+ | 1 | dšdšdšd | | 2 | dasdad | | 3 | dšdšdšd | | 4 | dššdšdšdš | | 5 | ccccccccccc | | 6 | šššššššššš | | 7 | ccccccccccccc | +----+----------------------------+ 7 rows in set (0.00 sec) mysql> select hex(djubre) from smece; +------------------------------------------------------+ | hex(djubre) | +------------------------------------------------------+ | C491C5A1C491C5A1C491C5A1C491 | | 646173646164 | | C491C5A1C491C5A1C491C5A1C491 | | C491C5A1C5A1C491C5A1C491C5A1C491C5A1 | | C48DC48DC48DC48DC48DC48DC48DC48DC48DC48DC48D | | C5A1C5A1C5A1C5A1C5A1C5A1C5A1C5A1C5A1C5A1 | | C487C487C487C487C487C487C487C487C487C487C487C487C487 | +------------------------------------------------------+ 7 rows in set (0.00 sec)
i voila, u bazi vise nije smeće nego validan sadržaj. Ako se sad vratimo na aplikaciju, na webu se sad vide neke nove kuke i kvake:
?�?�?�? dasdad ?�?�?�? ?��?�?�?� ??????????? ���������� ?????????????
sve što treba da uradimo je da setujemo pravilno enkoding u aplikaciji
< !DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<body>
<form action="djubre.php" method="post">
<input name="q" type="text" />
<input type="submit" />
</form>
< ?php
mysql_connect(':/tmp/mysql.sock', 'root', '') or die(mysql_error());
mysql_select_db('test') or die (mysql_error());
$res = mysql_query(“set names ‘utf8′”);
$res = mysql_query('select djubre from smece');
while ($row=mysql_fetch_row($res)) echo $row[0].'<br/>';
@mysql_free_result($res);
if ( isset( $_POST['q'] ) ) {
$djubre = $_POST['q'];
mysql_query("insert into smece (djubre) values ('$djubre')");
mysql_close();
}
?>
</body>
</html>
i problem rešen.
Particionisanje podataka
Pre nekog sitnog vremena jedna firma me zamolila da im malo protrkeljam sistem da vidim sta bi moglo da se ubrza posto imaju veliku bazu, zahtev za velikim brojem upita (uglavnom read) ali im sve to vrlo traljavo radi. Po iskustvu pretpostavio sam neke osnovne greske u postavci sistema (los server, lose iskonfigurisan mysql, ugasen ili lose podesen query cache etc) ali kada sam stigao na teren – sve moje pretpostavke su bile pogresne. MySQL je bio vrlo optimalno konfigurisan, server je zmija (8 jezgara, 15000RPM diskovi u raidu, mnogo rama…)… sumnja je naravno odma pala na lose upite. Pogledavsi upite, ni tu nije bilo sta mnogo da se “ubrza” … neke sitnice smo ubrzali par stotina puta no to nije mnogo uticalo na ukupnu sporost celog sistema, te sam se usresredio na samu strukturu i kvalitet podataka.
CPU LOAD
Baza ima stotinjak tabela od kojih su vecma sve poprilicno male po horizontali – sto generalno govori da je baza prilicno dobro normalizovana. Atributske tabelice su reda velicine par stotina do par hiljada slogova sto nije puno, ali “glavna” velika tabela, iako dobro normalizovana, ima oko 250 miliona slogova. Upiti koji se izvrsavaju nad tom tabelom i mnoze sa atributskim tabelama zahtevaju prolazak kroz veliki broj slogova i ma koliko indexi bili korisni ako imate agregatnu funkciju nad 10000 slogova, to prosto znaci da morate da prodjete kroz 10000 slogova, procitate svaki, uzmete vrednost, ubacite je u agregatnu funkciju … tu indexi na zalost ne pomazu mnogo … Posle malo dublje analize zakljucio sam da bi bilo realno da se ta glavna tabela particionise po jednom delu primarnog kljuca. Ako pogledamo kako radi partitioning na mysql-u zakljucicemo da je svaka operacija nad vise particija “mnogo” sporija nego u slucaju da nema particija ali sam ja ovaj put resio da batalim “opste brojke” i izmerim sam u realnom sistemu kakva je razlika. Operacije koje idu nad CELOM tabelom (znaci SVIM particijama) su radile oko 2.5% sporije nego na neparticionisanim podacima – dakle prilicno zadovoljavajuci rezultat ako se pokaze da se upiti nad jednom particijom izvrsavaju brze (posebno sto tih upita ima znacajno vise). Podaci nad jednom particijom su radili oko 40 PUTA brze :). I ne samo da je brzina izvrsavanja svakog upita veca, tokom izvrsavanja upita stres nad samim serverom je mnogostruko manji (i do 200 puta manji IO, 30tak puta manji user cpu usage …)
SERVER LOAD
Grafikoni koje vidite pokazuju CPU USAGE i AVERAGE LOAD na serveru u nekom vremenskom periodu. Pretpostavite trenutak kada smo ukljucili particionisanje podataka :)
MySQL podrzava partitioning od verzije 5.1. U osnovi, MySQL partitioning radi tako da vasu tabelu podeli u male tabele (svaka particija fizicki na disku postane zasebna tabela) i kada optimizer prepozna da moze upit da izvrsi nad samo jednom particijom, operaciju vrsi nad tom jednom “tabelicom”, ako upit mora da izvrsava nad vise tabela, onda se odredjuje da li moze da sekvencijalno koristi jednu pa drugu pa … ili mora da pravi “join” izmedju particija. U slucaju da se pravi join tada je pristup particionisanim podaci nesto sporiji nego kada oni nisu particionisani. U slucaju upisa i slicno podatak se upisuje u “odgovarajucu tabelu”. Ono sto je kod particionisanja vrlo zgodno je to sto mozete
- Obrisati podatke iz particije (recimo da particionisete podatke po godini, brisanje svih podataka iz jedne godine se u ovom slucaju svodi na brisanje jednog fajla sa diska)
- Popraviti jednu particiju (u slucaju korupcije, repair se vrsi nad samo jednim segmentom tabele, ne nad celom tabelom)
- U slucaju da su vam particije tipa myisam, svaka particija moze da bude na svom disku i to posebno data posebno index sto vam daje mogucnost da rasprostrete tabelu na mnogo diskova povecavajuci time max IO koji masina moze da da
Ako resite da probate mysql paritioning, obavezno procitajte i deo o limitima istog.
6.0 codebase i zvanično otišao u /dev/null
Spominjao sam već nekoliko puta ali evo sada i zvanično, 6.0 codebase je otišao u smeće. 90 procenata novina koje nisu vezane za falcon ili maria engine su backportovane nazad u 5.x drvo (većinom u 5.6) tako da će bolja podrška za windoze, bolji optimizer, ubrzanja u innodb-u, online backup i još mnogo drugih poboljšanja biti dostupni u 5.x codebase-u i polako, kako koji feature dobije GA status tako će biti prebacivan u AZALEA code. Falcon i Maria su ostavljeni da umru.
Monti razvija Maria storage engine nezavisno od Orakla mada i dalje, posle skoro 5 godina, Maria engine nije ni blizu produkcione upotrebe :(. Izbacili su podršku za transakcije (pošto nisu mogli godinama da naprave da to radi) i krenuli putem da naprave da Maria bude samo drop in zamena za MyISAM ali da bude crash safe ali ni to na žalost još uvek ne radi… MyISAM je odličan storage engine za mnoge prilike ali bi mnogo bilo iskusno kada bi postojala zamena koja je isto toliko brza a koja je crash safe, na moju veliku žalost (i na radovanje nekih zlobnika) Monti to još uvek nije uspeo da napravi. Ja se iskreno nadam da nisu daleko od cilja.
Što se Falcon-a tiče, današnji kompajleri prosto nisu dovoljno dobri za ono kako je Falcon storage engine zamišljen tako da ogromna zavisnost od pravilne implementacije exception-a od strane c++-a je učinilo da Falcon danas i dalje ne radi iako bi bio ozbiljan takmac InnoDB-u. Za sada je ostavljen u stranu za neka bolja vremena, pa možda, ako nekad neki c++ kompajler bude imao thread safe exception-e možda se i Falcon vrati iz pepela. Druga mogućnost je da će neke dobre ideje/tehnologija iz Falcon-a da se implementiraju u InnoDB i tako poboljša već odličan InnoDB storage engine.
Novosti sa UC-a 2010
Kao i za vreme ranijih MySQL User Conference-a i sada je tokom istog gomila novih verzija sa značajnim poboljšanjima izašla u javnost:
- MySQL 5.5.4-m4, trenutno najbolja verzija innodb (i klonova) dostupna na netu je u njemu
- MySQL Cluster 7.1 je postao GA sa verzijom 7.1.3
- MySQL WorkBench 5.2.18 beta8
Od prezentacija, meni je najznačajnija za mene je Bernhardova prezentacija novosti u MySQL Cluster-u 7.x sa posebnim osvrtom na 7.1.
Neki od bitnih detalja za 7.0:
- Online Add Node (dodavanje nodova bez prekida rada sistema – dakle online)
- Multi-threaded data nodes – ozbiljno poboljšanje u radu data nodova na multicore sistemima
- snapshot backup – mogucnost biranja “trenutka” konzistentnosti bekapa
- podrška za windoze (da, od 7.0 MySQL Cluster radi i na windozi)
I neki od bitnih detalja za 7.1:
- NDBINFO baza na SQL nodu za real time proveru statusa klastera
- novi klaster manager
- 40+ puta brzi restart
- Cluster/J i openJPA
Toliko za sada
MySQL AUDIT data
Jedan od velikih nedostataka MySQL-a za ozbiljno enterprise okruzenje je auditing. Nekakav minimalan auditing koji enterprise uglavnom zahteva je log
– ko se ulogovao
– kada se ulogovao
– odakle se ulogovao
– da li je logovanje bilo uspesno ili ne
– kada se odlogovao sa servera
Za web je ovo nepotrebno (bez potrebe bi u logu imali informaciju kada se web server zakacio/otkacio) ali za enterprise moze da bude vrlo korisno a vrlo cesto i neophodno, pogotovo kada enterprise aplikacije projektuju polupismeni developeri kojima baza radi autentifikaciju i user permissioning a ne aplikacija “posto im je tako lakse”.
Kao sto rekoh, MySQL ima jako slabu / nikakvu podrsku za auditing. Sve sto MySQL moze je da upali “general query log” i da u log fajl pise SVE (ko se okacio, kad, i stra je sve radio), ali to ima 2 mane
1. general query log trosi resurse (i do 20% usporenja ne high load aplikacijama)
2. general query log ne pamti neuspele konekcije
Kako je mysql-proxy postao vrlo popularan i koristi se vrlo cesto (sto za load balancing sto u neke druge svrhe) a i sastavni je deo MySQL Enterprise Monitor-a gde sve konekcije idu kroz MEM agent kako bi MEM mogao da pravi analizu upita, koristenje proxy-a za auditing uopste nije tako lose resenje.
Kako bi auditing radio, potrebno je instalirati mysql-proxy (ili agent), namestiti mysql da prima konekcije samo sa proxy-a (staviti ga na neki zaseban port i setovati firewall) a proxy podici na standardnom 3306 portu te ce vase aplikacije bez znanja sav traffic usmeriti kroz proxy.
Skript koji radi neki basic auditing bi izgledao:
function read_auth_result( auth )
local state = auth.packet:byte()
if state == proxy.MYSQLD_PACKET_OK then
print( os.date('%Y-%m-%d %H:%M:%S') .. ":AUTH OK:"
.. proxy.connection.server.thread_id .. ":"
.. proxy.connection.client.username .. ":"
.. proxy.connection.client.src.name .. "->"
.. proxy.connection.server.dst.name )
elseif state == proxy.MYSQLD_PACKET_ERR then
print( os.date('%Y-%m-%d %H:%M:%S') .. ":AUTH FAILED: "
.. proxy.connection.server.thread_id .. ":"
.. proxy.connection.client.username .. ":"
.. proxy.connection.client.src.name .. "->"
.. proxy.connection.server.dst.name )
else
print( os.date('%Y-%m-%d %H:%M:%S') .. ":AUTH UNKNOWN "
.. string.format("%q", auth.packet) .. ":"
.. proxy.connection.client.username .. ":"
.. proxy.connection.client.src.name .. "->"
.. proxy.connection.server.dst.name )
end
end
function disconnect_client()
if proxy.connection.server.thread_id then
print( os.date('%Y-%m-%d %H:%M:%S') .. ":DISCONNECT:"
.. proxy.connection.server.thread_id .. ":"
.. proxy.connection.client.username .. ":"
.. proxy.connection.client.src.name .. "->"
.. proxy.connection.server.dst.name )
end
end
Ako pogledamo, skript nam loguje vreme, username, thread na mysql-u i sors/destination adrese konekcije. Vidimo kada je neko pokusao da se uloguje na mysql, da li je uspeo ili ne i kada se otkacio.
Log izgleda otprilike ovako:
2010-02-08 17:21:22:AUTH OK:31:arhimed:127.0.0.1:44440->127.0.0.1:3306
2010-02-08 17:21:33:DISCONNECT:31:arhimed:127.0.0.1:44440->127.0.0.1:3306
2010-02-08 17:21:38:AUTH FAILED: 32:arhime:127.0.0.1:44587->127.0.0.1:3306
2010-02-08 17:21:38:DISCONNECT:32:arhime:127.0.0.1:44587->127.0.0.1:3306
2010-02-08 17:21:43:AUTH FAILED: 33:arhie:127.0.0.1:44622->127.0.0.1:3306
2010-02-08 17:21:43:DISCONNECT:33:arhie:127.0.0.1:44622->127.0.0.1:3306
Ako hocemo da logujemo i osnovne podatke o upitima koje korisnik izvrsava onda mozemo prosiriti lua skript:
function read_auth_result( auth )
local state = auth.packet:byte()
if state == proxy.MYSQLD_PACKET_OK then
print( os.date('%Y-%m-%d %H:%M:%S') .. ":AUTH OK:"
.. proxy.connection.server.thread_id .. ":"
.. proxy.connection.client.username .. ":"
.. proxy.connection.client.src.name .. "->"
.. proxy.connection.server.dst.name )
elseif state == proxy.MYSQLD_PACKET_ERR then
print( os.date('%Y-%m-%d %H:%M:%S') .. ":AUTH FAILED: "
.. proxy.connection.server.thread_id .. ":"
.. proxy.connection.client.username .. ":"
.. proxy.connection.client.src.name .. "->"
.. proxy.connection.server.dst.name )
else
print( os.date('%Y-%m-%d %H:%M:%S') .. ":AUTH UNKNOWN "
.. string.format("%q", auth.packet) .. ":"
.. proxy.connection.client.username .. ":"
.. proxy.connection.client.src.name .. "->"
.. proxy.connection.server.dst.name )
end
end
function disconnect_client()
if proxy.connection.server.thread_id then
print( os.date('%Y-%m-%d %H:%M:%S') .. ":DISCONNECT:"
.. proxy.connection.server.thread_id .. ":"
.. proxy.connection.client.username .. ":"
.. proxy.connection.client.src.name .. "->"
.. proxy.connection.server.dst.name )
end
end
function read_query( packet )
if string.byte(packet) == proxy.COM_QUERY then
proxy.queries:append(1, packet, {resultset_is_needed = true} )
return proxy.PROXY_SEND_QUERY
end
end
function read_query_result( inj )
local res = assert(inj.resultset)
local raw_len = assert(res.raw):len()
local packet = assert(inj.query)
local flags = res.flags
if res.affected_rows then
ar = res.affected_rows
else
ar = "0"
end
print( os.date('%Y-%m-%d %H:%M:%S')
.. ":QUERY:" .. proxy.connection.server.thread_id .. ":"
.. inj.query .. ":"
.. (inj.query_time / 1000) .. "ms" .. ":"
.. (inj.response_time / 1000) .. "ms" .. ":"
.. raw_len .. ":"
.. tostring(res.flags.no_good_index_used) .. ":"
.. tostring(res.flags.no_index_used) .. ":"
.. res.warning_count .. ":"
.. res.query_status .. ":"
.. ar
)
end
pa nam sada log izgleda malo drugacije:
2010-02-08 17:21:22:AUTH OK:31:arhimed:127.0.0.1:44440->127.0.0.1:3306
2010-02-08 17:21:22:QUERY:31:select @@version_comment limit 1:0.092ms:0.095ms:1:false:false:0:0:0
2010-02-08 17:21:23:QUERY:31:select * from mysql.user:0.281ms:0.305ms:1:false:true:0:0:0
2010-02-08 17:21:24:QUERY:31:select * from mysql.user:0.278ms:0.299ms:1:false:true:0:0:0
2010-02-08 17:21:25:QUERY:31:select * from mysql.user:0.272ms:0.293ms:1:false:true:0:0:0
2010-02-08 17:21:28:QUERY:31:select * from mysql.db:0.229ms:0.243ms:1:false:true:0:0:0
2010-02-08 17:21:33:DISCONNECT:31:arhimed:127.0.0.1:44440->127.0.0.1:3306
2010-02-08 17:21:38:AUTH FAILED: 32:arhime:127.0.0.1:44587->127.0.0.1:3306
2010-02-08 17:21:38:DISCONNECT:32:arhime:127.0.0.1:44587->127.0.0.1:3306
2010-02-08 17:21:43:AUTH FAILED: 33:arhie:127.0.0.1:44622->127.0.0.1:3306
2010-02-08 17:21:43:DISCONNECT:33:arhie:127.0.0.1:44622->127.0.0.1:3306
