MySQL andmebaasi jõudluse parandamine

Sissejuhatus

Selles peatükis keskendutakse MySQL-i andmbeaasi jõudluse parandamisega. Peamiselt kirjeldatakse konfiguratsiooni failis olevad
muutujad ja nende suurused. MySQL andmebaasi konfiguratsiooni fail asub kaustas:

/etc/mysql/my.cnf

Selles failis võib muuta mälu, tabelite ja internti ühenduste piiranguid ja teisi võimalike muutusi lisada.

Tehnilised näitajad: MySQL 5.5 versioon

Autor

Sergei Istomin
A22
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
mootor võimaldab teha teha andmete taastamist ja süsteemi kokkujooksmised salvestatakse log failidesse.
InnoDB salvestab andmete read füüsiliselt primaarsete võtmete järjekorras jne.
Rohkem lugemist leiab järgmiselt lingilt:InnoDB vs MyISAM

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

MySQL-i Optimeerimine

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

• Kui Open_tables on väga suure mahuga, siis kindlasti on table_cache muutuja väikse mahuga.

Ehk: table_cache: 64 Open_tables: 64 Opened_tables: 544468

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

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

• Kui Key_reads on suure mahuga, siis key_buffer_size muutuja on ilmselt liiga väike.
  

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

Parameeter Key_buffer_size mõjutab indeksite puhvri suurust ja indexi käsitlemise aega. Tavaliselt peaks Key_reads/Key_read_request koefitsent olema < 0.01.
See on äärmuslikult tähtis, mida õigeks saada.

Kindlasti tuleks kontrollida Key_writes_requests ja Key_writes
Key_writes/Key_writes_request peaks olema tavaliselt < 1 (0.5 väärtuse juures sobib).

MySQL-i Optimeerimine

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

• Kui Open_tables on väga suure mahuga, siis kindlasti on table_cache muutuja väikse mahuga.

Ehk: table_cache: 64 Open_tables: 64 Opened_tables: 544468

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

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

• Kui Key_reads on suure mahuga, siis key_buffer_size muutuja on ilmselt liiga väike.
  

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

Parameeter Key_buffer_size mõjutab indeksite puhvri suurust ja indexi käsitlemise aega. Tavaliselt peaks Key_reads/Key_read_request koefitsent olema < 0.01.
See on äärmuslikult tähtis, mida õigeks saada.

Kindlasti tuleks kontrollida Key_writes_requests ja Key_writes
Key_writes/Key_writes_request peaks olema tavaliselt < 1 (0.5 väärtuse juures sobib).

Teised tähtsad parameetrid

Järmisena tähtsad sätted on: Wait_timeout, max_connection, thread_cache

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

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

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

• Kui Threads_created on suure väärtusega, siis peab kasutaja suurendama thread_cache_size muutuja väärtusi.
  

Vahemälu koefitsenti saab arvutada järmiselt:
Threads_created/Connections.
Thread_cache_size: 0
Threads_created: 150022
Connections: 150023

See peaks probleem peaks järgmisena olema parandatud. Vahemälu suurus väärtusega 0 on vaikimiselt määratud my.cnf failis aga soovitatud suurus on 8.

Seda saab kalkuleerida järgmise valemiga: table_cache = opened table / max_used_connection

• Viimseks tuleks vaadata: tmp_table_size ja Handler_read_rnd / Handler_read_rnd_next
  

• Kui Created_tmp_disk_tables on liiga suure väärtusega, siis tuleks suurendada tmp_table_size muutujad.
  

Tmp_table_size: 32M
Created_tmp_disk_tables: 3227
Created_tmp_tables: 159832
Created_tmp_files: 4444

Created_tmp_disk_tables on arv varjatud ajutisi tabeleid, mis on loodud kettale samalajal käivitades lauseid ja
Create_tmp_tables on mälu põhjal. Ilmselt on see halb kui peab minema ketta kallale mälu asemel. Umbes 2% temp tables lähvad kettale, mis ei tundu väga halb olema aga tõstes tmp_table_size väärtust oleks oluline.

• Kui Handler_read_rnd on väärtuse poolest liiga suur, siis on ilmselt andmebaasis palju päringuid, mis vajavad MySQL-il, et

skaneerida kõiki tabeleid või on andmebaasis JOIN muutujad, mis ei kasuta võtmeid korralikult.
Handler_read_rnd: 27712353
Handler_read_rnd_next: 283536234

Need väärtused on liiga suured, seega peab ilmselt parandama indexeid ja päringuid.

• Täielik mälu kasutus
  

Kasutatud MySQL-i mälu = key_buffer + max_connections * (join_buffer + record_buffer + sort_buffer + thread_stack + tmp_table_size)

• Kui InnoDB mootor peaks olema peal:
  

max_memory_utilization = key_buffer_size + innoDB_buffer_pool_size + max_connections * (read_buffer_size + sort_buffer_size + binlog_cache_size) + max_connections * 2M

Siin tuleks tähelepanna max_connexion ja korrutamise teguri.
Ühenduse kasvamine = Mälu kasutamise suurendamist

Table_cache:
Suurendades Table_cache parameetrit aitab suurendada jõudlust.
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. Kui MySQL proovib avada palju faile, siis OS võib keelduda ja MySQL genereerib veateate vealogi sisse.