Sakinisha Innotop ili Kufuatilia Utendaji wa Seva ya MySQL
Innotop ni programu bora ya mstari wa amri, sawa na 'amri ya juu' ya kufuatilia seva za MySQL za ndani na za mbali zinazoendesha chini ya injini ya InnoDB. Innotop huja na vipengele vingi na aina tofauti za modi/chaguo, ambazo husaidia kufuatilia vipengele tofauti vya utendakazi wa MySQL na pia husaidia msimamizi wa hifadhidata kujua ni nini kibaya kinachoendana na seva ya MySQL.
Kwa mfano, Innotop husaidia katika kufuatilia hali ya urudufishaji wa mysql, takwimu za mtumiaji, orodha ya maswali, vihifadhi vya InnoDB, maelezo ya InnoDB I/O, meza wazi, jedwali la kufuli, n.k, husasisha data yake mara kwa mara, ili uweze kuona matokeo yaliyosasishwa.
Innotop inakuja na vipengele bora na unyumbufu na hauhitaji usanidi wowote wa ziada na inaweza kutekelezwa kwa kutekeleza tu amri ya 'innotop' kutoka kwa terminal.
Kufunga Innotop (Ufuatiliaji wa MySQL)
Kwa chaguo-msingi kifurushi cha innotop hakijajumuishwa katika usambazaji wa Linux kama vile RHEL, CentOS, Fedora na Linux ya kisayansi. Unahitaji kuisakinisha kwa kuwezesha hazina ya epel ya mtu wa tatu na kutumia yum amri kama inavyoonyeshwa hapa chini.
# yum install innotop
Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: centos.mirror.net.in * epel: epel.mirror.net.in * epel-source: epel.mirror.net.in * extras: centos.mirror.net.in * updates: centos.mirror.net.in Setting up Install Process Resolving Dependencies --> Running transaction check ---> Package innotop.noarch 0:1.9.0-3.el6 will be installed --> Finished Dependency Resolution Dependencies Resolved ========================================================================================================== Package Arch Version Repository Size ========================================================================================================== Installing: innotop noarch 1.9.0-3.el6 epel 149 k Transaction Summary ========================================================================================================== Install 1 Package(s) Total download size: 149 k Installed size: 489 k Is this ok [y/N]: y Downloading Packages: innotop-1.9.0-3.el6.noarch.rpm | 149 kB 00:00 Running rpm_check_debug Running Transaction Test Transaction Test Succeeded Running Transaction Installing : innotop-1.9.0-3.el6.noarch 1/1 Verifying : innotop-1.9.0-3.el6.noarch 1/1 Installed: innotop.noarch 0:1.9.0-3.el6 Complete!
Kuanza innotop, chapa tu innotop na ueleze chaguo -u (jina la mtumiaji) na -p (nenosiri) kwa mtiririko huo, kutoka kwa mstari wa amri na ubofye Ingiza.
# innotop -u root -p 'tecm1nt'
Mara tu unapounganisha kwenye seva ya MySQL, unapaswa kuona kitu sawa na skrini ifuatayo.
[RO] Dashboard (? for help) localhost, 61d, 254.70 QPS, 5/2/200 con/run/cac thds, 5.1.61-log Uptime MaxSQL ReplLag Cxns Lock QPS QPS Run Run Tbls Repl SQL 61d 4 0 254.70 _ _ 462 Off 1
Bonyeza ? kupata muhtasari wa chaguzi za mstari wa amri na utumiaji.
Switch to a different mode: A Dashboard I InnoDB I/O Info Q Query List B InnoDB Buffers K InnoDB Lock Waits R InnoDB Row Ops C Command Summary L Locks S Variables & Status D InnoDB Deadlocks M Replication Status T InnoDB Txns F InnoDB FK Err O Open Tables U User Statistics Actions: d Change refresh interval p Pause innotop k Kill a query's connection q Quit innotop n Switch to the next connection x Kill a query Other: TAB Switch to the next server group / Quickly filter what you see ! Show license and warranty = Toggle aggregation # Select/create server groups @ Select/create server connections $ Edit configuration settings \ Clear quick-filters Press any key to continue
Sehemu hii ina picha za skrini za matumizi ya innotop. Tumia vitufe vya herufi kubwa kubadili kati ya modi.
Hali hii inaonyesha takwimu za mtumiaji na takwimu za faharasa zilizopangwa kulingana na usomaji.
CXN When Load QPS Slow QCacheHit KCacheHit BpsIn BpsOut localhost Total 0.00 1.07k 697 0.00% 98.17% 476.83k 242.83k
Hali hii inaonyesha matokeo kutoka kwa SHOW FULL PROCESSLIST, sawa na hali ya orodha ya hoja ya mytop. Kipengele hiki hakionyeshi maelezo ya InnoDB na ni muhimu zaidi kwa matumizi ya jumla.
When Load Cxns QPS Slow Se/In/Up/De% QCacheHit KCacheHit BpsIn BpsOut Now 0.05 1 0.20 0 0/200/450/100 0.00% 100.00% 882.54 803.24 Total 0.00 151 0.00 0 31/231470/813290/188205 0.00% 99.97% 1.40k 0.22 Cmd ID State User Host DB Time Query Connect 25 Has read all relay system u 05:26:04
Hali hii inaonyesha takwimu za I/O za InnoDB, nyuzi za I/O, I/O zinazosubiri, faili I/O na jedwali la takwimu za kumbukumbu kwa chaguomsingi.
____________________ I/O Threads ____________________
Thread Purpose Thread Status
0 insert buffer thread waiting for i/o request
1 log thread waiting for i/o request
2 read thread waiting for i/o request
3 write thread waiting for i/o request
____________________________ Pending I/O _____________________________
Async Rds Async Wrt IBuf Async Rds Sync I/Os Log Flushes Log I/Os
0 0 0 0 0 0
________________________ File I/O Misc _________________________
OS Reads OS Writes OS fsyncs Reads/Sec Writes/Sec Bytes/Sec
26 3 3 0.00 0.00 0
_____________________ Log Statistics _____________________
Sequence No. Flushed To Last Checkpoint IO Done IO/Sec
0 5543709 0 5543709 0 5543709 8 0.00Sehemu hii, utaona taarifa kuhusu hifadhi ya bafa ya InnoDB, takwimu za ukurasa, weka bafa, na faharasa ya hashi inayojirekebisha. Data inachota kutoka SHOW INNODB STATUS.
__________________________ Buffer Pool __________________________
Size Free Bufs Pages Dirty Pages Hit Rate Memory Add'l Pool
512 492 20 0 -- 16.51M 841.38k
____________________ Page Statistics _____________________
Reads Writes Created Reads/Sec Writes/Sec Creates/Sec
20 0 0 0.00 0.00 0.00
______________________ Insert Buffers ______________________
Inserts Merged Recs Merges Size Free List Len Seg. Size
0 0 0 1 0 2
__________________ Adaptive Hash Index ___________________
Size Cells Used Node Heap Bufs Hash/Sec Non-Hash/Sec
33.87k 0 0.00 0.00Hapa, utaona matokeo ya utendakazi wa safu mlalo ya InnoDB, misc ya utendakazi wa safu mlalo, semaphores, na majedwali ya safu ya kusubiri kwa chaguo-msingi.
________________ InnoDB Row Operations _________________
Ins Upd Read Del Ins/Sec Upd/Sec Read/Sec Del/Sec
0 0 0 0 0.00 0.00 0.00 0.00
________________________ Row Operation Misc _________________________
Queries Queued Queries Inside Rd Views Main Thread State
0 0 1 waiting for server activity
_____________________________ InnoDB Semaphores _____________________________
Waits Spins Rounds RW Waits RW Spins Sh Waits Sh Spins Signals ResCnt
2 0 41 1 1 2 4 5 5
____________________________ InnoDB Wait Array _____________________________
Thread Time File Line Type Readers Lck Var Waiters Waiting? Ending?Njia ya muhtasari wa amri inaonyesha jedwali la cmd_summary yote, ambayo inaonekana sawa na hapa chini.
_____________________ Command Summary _____________________ Name Value Pct Last Incr Pct Com_update 11980303 65.95% 2 33.33% Com_insert 3409849 18.77% 1 16.67% Com_delete 2772489 15.26% 0 0.00% Com_select 507 0.00% 0 0.00% Com_admin_commands 411 0.00% 1 16.67% Com_show_table_status 392 0.00% 0 0.00% Com_show_status 339 0.00% 2 33.33% Com_show_engine_status 164 0.00% 0 0.00% Com_set_option 162 0.00% 0 0.00% Com_show_tables 92 0.00% 0 0.00% Com_show_variables 84 0.00% 0 0.00% Com_show_slave_status 72 0.00% 0 0.00% Com_show_master_status 47 0.00% 0 0.00% Com_show_processlist 43 0.00% 0 0.00% Com_change_db 27 0.00% 0 0.00% Com_show_databases 26 0.00% 0 0.00% Com_show_charsets 24 0.00% 0 0.00% Com_show_collations 24 0.00% 0 0.00% Com_alter_table 12 0.00% 0 0.00% Com_show_fields 12 0.00% 0 0.00% Com_show_grants 10 0.00% 0 0.00%
Sehemu hii hukokotoa takwimu, kama vile hoja kwa sekunde, na kuzionyesha katika idadi ya hali tofauti.
QPS Commit_PS Rlbck_Cmt Write_Commit R_W_Ratio Opens_PS Tbl_Cch_Usd Threads_PS Thrd_Cch_Usd CXN_Used_Ever CXN_Used_Now 0 0 0 18163174 0 0 0 0 0 1.99 1.32 0 0 0 18163180 0 0 0 0 0 1.99 1.32 0 0 0 18163188 0 0 0 0 0 1.99 1.32 0 0 0 18163192 0 0 0 0 0 1.99 1.32 0 0 0 18163217 0 0 0 0 0 1.99 1.32 0 0 0 18163265 0 0 0 0 0 1.99 1.32 0 0 0 18163300 0 0 0 0 0 1.99 1.32 0 0 0 18163309 0 0 0 0 0 1.99 1.32 0 0 0 18163321 0 0 0 0 0 1.99 1.32 0 0 0 18163331 0 0 0 0 0 1.99 1.32
Katika hali hii, utaona matokeo ya Hali ya SQL ya Mtumwa, Hali ya Mtumwa I/O na Hali ya Ubwana. Sehemu mbili za kwanza zinaonyesha hali ya mtumwa na hali ya uzi wa mtumwa wa I/O na sehemu ya mwisho inaonyesha hadhi ya Mwalimu.
_______________________ Slave SQL Status _______________________ Master On? TimeLag Catchup Temp Relay Pos Last Error 172.16.25.125 Yes 00:00 0.00 0 41295853 ____________________________________ Slave I/O Status _____________________________________ Master On? File Relay Size Pos State 172.16.25.125 Yes mysql-bin.000025 39.38M 41295708 Waiting for master to send event ____________ Master Status _____________ File Position Binlog Cache mysql-bin.000010 10887846 0.00%
Unaweza kuendesha innotop bila mwingiliano.
# innotop --count 5 -d 1 -n
uptime max_query_time time_behind_master connections locked_count qps spark_qps run spark_run open slave_running longest_sql 61d 2 0 0.000363908088893752 64 Yes 61d 2 0 4.96871146980749 _ _ 64 Yes 61d 2 0 3.9633543857494 ^_ __ 64 Yes 61d 2 0 3.96701862656428 ^__ ___ 64 Yes 61d 2 0 3.96574802684297 ^___ ____ 64 Yes
Ili kufuatilia hifadhidata ya mbali kwenye mfumo wa mbali, tumia amri ifuatayo kwa kutumia jina fulani la mtumiaji, nenosiri na jina la mwenyeji.
# innotop -u username -p password -h hostname
Kwa habari zaidi kuhusu matumizi na chaguzi za 'innotop', angalia kurasa za mtu kwa kugonga man innotop kwenye terminal.
Viungo vya Marejeleo
Ukurasa wa nyumbani wa Innotop
- Mtop (Ufuatiliaji Hifadhidata ya MySQL) katika RHEL/CentOS/Fedora