View source for MySQL andmebaasi jõudluse parandamine

=Sissejuhatus=
Selles peatükis keskendutakse MySQL-i andmbeaasi jõudluse parandamisega. Peamiselt kirjeldatakse konfiguratsiooni failis olevad<br /> muutujad ja nende suurused. MySQL andmebaasi konfiguratsiooni fail asub kaustas:<br />
<pre>/etc/mysql/my.cnf</pre>
Selles failis võib muuta mälu, tabelite ja internti ühenduste piiranguid ja teisi võimalike muutusi lisada.<br />

Tehnilised näitajad: <b>MySQL 5.5</b> versioon

==Autor==
Sergei Istomin<br /> 
A22<br />
[mailto:sistomin@itcollege.ee sistomin@itcollege.ee]

=InnoDB MySQL-is=

InnoDB on talletamise mootor MySQL-i andmebaasi jaoks. InnoDB on oma funksionaalsuse poolest kõige eelistatum mootor, kuna InnoDB<br /> mootor võimaldab teha andmete taastamist ja süsteemi kokkujooksmised salvestatakse logi failidesse.<br />
InnoDB salvestab andmete read füüsiliselt primaarsete võtmete järjekorras.<br />
Rohkem lugemist leiab järgmiselt lingilt:[http://en.wikipedia.org/wiki/InnoDB InnoDB vs MyISAM]

Kaks peamist ketta baasil manageeritud ressursi talletamist on InnoDB-s tema tabelite andme- ja logi failid.<br />
Kui kasutaja ei spetsifitseeri InnoDB konfiguratsiooni sätteid, siis MySQL loob isiklikult <b>auto-extending</b> 10MB andmete faili<br /> 
nimega <b>idbata1</b> ja kaks 5MB log faili nimega <b>ib_logfile0</b> ja <b>ib_logfile1</b> MySQL-i andmete kategoorias.<br />
Et parandada jõudlust tuleks lisada InnoDB parameetritele kindlad väärtused, mis sobivad kasutaja raudvaraga kokku.<br />

=MySQL-i Optimeerimine=

Kaks kõige tähtsamat muutujat my.cnf failis on: <b>Table_cache</b> and <b>Key_buffer_size</b>

*Kui <b>Open_tables</b> on väga suure mahuga, siis kindlasti on <b>Table_cache</b> muutuja väikse mahuga.
Ehk:<br />
<b>Table_cache:</b> 64<br />
<b>Open_tables:</b> 64<br />
<b>Opened_tables:</b> 544468<br />

Parameeter nimega <b>table_cache</b> on arv avatud tabeleid. MySQL on multi-threaditud ja võib joosta paljudel
päringutel samaaegselt ja iga üks nendest avab tabeli. <br />
Seega kui kasutajal on paar tabelit, läheb temal vaja rohkem tabeleid <b>opened_tables</b>.<br />

<b>Opened_table</b> väärtus on kõrge ja näitab vahemälus olevaid puudusi. <br />
Muutes <b>table_cache</b> parameetri suurust parandab oluliselt jõudlust. 

* Kui <b>Key_reads</b> on suure mahuga, siis <b>key_buffer_size</b> muutuja on ilmselt liiga väike.<br />

Vahemälu jõudmise keskmist saab arvutada järmiselt:<br />
<b>Key_reads/Key_read_requests.</b><br />
<b>Key_buffer_size:</b> 16M<br />
<b>Key_read_requests:</b> 2973620399<br />
<b>Key_reads:</b> 8490571<br />
(cache hit rate = 0.0028)<br />

Parameeter <b>Key_buffer_size</b> mõjutab indeksite puhvri suurust ja indexi käsitlemise aega.
Tavaliselt peaks <b>Key_reads/Key_read_request</b> koefitsent olema < 0.01. <br />

Kindlasti tuleks kontrollida <b>Key_writes_requests</b> ja <b>Key_writes</b><br />
<b>Key_writes/Key_writes_request</b> peaks olema tavaliselt < 1 (0.5 väärtus sobib).


==Teised tähtsad parameetrid==

Järmisena tähtsad sätted on: <b>Wait_timeout</b>, <b>max_connection</b> ja <b>thread_cache</b><br />

Natukene seletust:
Tavaliselt on andmebaasis palju MySQL-i protsesse, mis niiöelda magavad sellepärast, et <b>wait_timeout</b> ei ole määratud suuruse <br />poolest väikseks. Kasutaja peaks kindlaks tegema, et <b>wait_timeout</b> on määratud väga väikesele väärtusele: 15 sekundit. <br />
See tähendab, et MySQL katkestab kõik ühendused, mis ei olnud aktiivsed 15 sekundi jooksul.<br />

Probleem seisneb selles, et järgmisena peaks tõstma <b>max_connection</b> parameetrit(igal kasutajal on oma väärtus), kuna paljud<br /> ebaaktiivsed kasutajad ei hoiaks ühendused kinni ja ei blokeeriks välja uusi kliente ühendades andmebaasile.<br />

Kasutaja peaks kasutama <b>Thread_cache</b>:
"Kui palju threade peaks vahemällu jätma uuesti kasutamiseks. Kui klient katkestab ühenduse, siis kliendi threadid pannakse<br /> vahemällu kui seal pole rohkem <b>Thread_cache_size</b> threade ennem. Kõik uued threadid on võetud vahemälust ja ainult siis kui vahemälu on tühi, siis luuakse uus thread. <br />
Muutuja väärtust saab tõsta, et parandada jõudlust kui kasutajal on palju uusi ühendusi.<br />

*Kui <b>Threads_created</b> on suure väärtusega, siis peab kasutaja suurendama <b>Thread_cache_size</b> muutuja väärtusi.<br />
Vahemälu koefitsenti saab arvutada järmiselt:<br />
<b>Threads_created/Connections.</b><br />
<b>Thread_cache_size: 0</b><br />
<b>Threads_created: 150022</b><br />
<b>Connections: 150023</b><br />

Vahemälu suurus väärtusega 0 on vaikimiselt määratud my.cnf failis aga soovitatud suurus on 8.<br />

Seda saab kalkuleerida järgmise valemiga: <b>table_cache = opened table / max_used_connection</b><br />

*Viimseks tuleks vaadata: <b>tmp_table_size</b> ja <b>Handler_read_rnd / Handler_read_rnd_next</b><br />
*Kui <b>Created_tmp_disk_tables</b> on liiga suure väärtusega, siis tuleks suurendada <b>tmp_table_size</b> muutujad.<br />
<b>Tmp_table_size:</b> 32M<br />
<b>Created_tmp_disk_tables:</b> 3227<br />
<b>Created_tmp_tables:</b> 159832<br />
<b>Created_tmp_files:</b> 4444<br />

<b>Created_tmp_disk_tables</b> on arv varjatud ajutisi tabeleid, mis on loodud kettale samalajal käivitades lauseid ja<br /> <b>Create_tmp_tables</b> on mälu põhjal. Ilmselt on see halb kui peab minema ketta kallale mälu asemel. <br />Umbes 2% <b>temp tables </b>lähvad kettale, mis peaks olemas olema aga tõstes <b>tmp_table_size</b> väärtust oleks oluline jõudlusele.<br /> 
*Kui <b>Handler_read_rnd</b> on väärtuse poolest liiga suur, siis on ilmselt andmebaasis palju päringuid, mis vajavad MySQL-il
skaneerida kõiki tabeleid või on andmebaasis JOIN lauseid, mis ei kasuta oma võtmeid korralikult.<br /> 
<b>Handler_read_rnd:</b> 27712353<br />
<b>Handler_read_rnd_next:</b> 283536234<br />

Need väärtused on liiga suured, seega peab ilmselt parandama indexeid ja päringuid.<br />
*Täielik mälu kasutus<br />

Kasutatud MySQL-i mälu = <b>key_buffer</b> + <b>max_connections</b> * (<b>join_buffer</b> + <b>record_buffer</b> + <b>sort_buffer</b> + <b>thread_stack</b> + <b>tmp_table_size</b>)<br />
*Kui InnoDB mootor peaks olema peal:<br /> 

<b>max_memory_utilization</b> = <b>key_buffer_size</b> + <b>innoDB_buffer_pool_size</b> + <b>max_connections</b> * (<b>read_buffer_size</b> + <b>sort_buffer_size</b> + <b>binlog_cache_size</b>) + <b>max_connections</b> * <b>2M</b><br />

Siin tuleks tähelepanna <b>max_connexion</b> ja korrutamise teguri.<br />
<b>Ühenduse kasvamine</b> = <b>Mälu kasutamise suurendamist</b><br />

<b>Table_cache:</b><br />
Suurendades <b>Table_cache</b> parameetrit aitab suurendada jõudlust.<br />
Siin peab ettevaatlik olema, et parameetri väärtus ei läheks väga suureks. Kõigil operatsioonisüsteemidel on olemas arvu limiit "Open file pointer" mida omab iga üksik protsess.<br />
Kui MySQL proovib avada palju faile, siis OS võib keelduda ja MySQL genereerib veateate vealogi sisse.

=Kasutatud kirjandus=

[http://dev.mysql.com/doc/refman/5.5/en/innodb-configuration.html InnoDB Configuration]
[http://cherry.world.edoors.com/Cw9QcpkgQwVQ OPTIMISING MYSQL]
[http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html The Slow Query Log]
[http://www.mysqlperformanceblog.com/?s=key_buffer_size MySQL Performance Blog]



[[Category:Operatsioonisüsteemide administreerimine ja sidumine]]