Fi Innotop sii lati ṣetọju Išẹ Server MySQL


Innotop jẹ eto laini aṣẹ ti o dara julọ, iru si 'aṣẹ oke' lati ṣe atẹle agbegbe ati latọna jijin awọn olupin MySQL ti n ṣiṣẹ labẹ ẹrọ InnoDB. Innotop wa pẹlu ọpọlọpọ awọn ẹya ati awọn oriṣi awọn ipo/awọn aṣayan oriṣiriṣi, eyiti o ṣe iranlọwọ lati ṣe atẹle awọn oriṣiriṣi awọn ẹya ti iṣẹ MySQL ati tun ṣe iranlọwọ fun olutọju ibi ipamọ data lati wa kini aṣiṣe ti n lọ pẹlu olupin MySQL.

Fun apẹẹrẹ, Innotop ṣe iranlọwọ ni mimojuto ipo idapọ mysql, awọn iṣiro olumulo, atokọ ibeere, Awọn ifipamọ InnoDB, InnoDB I/O alaye, awọn tabili ṣiṣi, awọn tabili titiipa, ati bẹbẹ lọ, o sọ awọn data rẹ di igbagbogbo, nitorinaa o le rii awọn abajade imudojuiwọn.

Innotop wa pẹlu awọn ẹya nla ati irọrun ati pe ko nilo iṣeto ni afikun ati pe o le ṣee ṣe nipasẹ ṣiṣe ‘innotop’ pipaṣẹ nikan lati ọdọ ebute naa.

Fifi Innotop sii (Abojuto MySQL)

Nipa aiyipada innotop package ko si ninu awọn pinpin kaakiri Linux bi RHEL, CentOS, Fedora ati Linux Linux. O nilo lati fi sii nipasẹ muuṣiṣẹ ibi-itaja epel ẹnikẹta ṣiṣẹ ati lilo pipaṣẹ yum bi a ṣe han ni isalẹ.

# 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!

Lati bẹrẹ innotop, tẹ ni kia kia “innotop” ki o ṣafihan awọn aṣayan -u (orukọ olumulo) ati -p (ọrọ igbaniwọle) lẹsẹsẹ, lati laini aṣẹ ki o tẹ Tẹ.

# innotop -u root -p 'tecm1nt'

Lọgan ti o ti sopọ si olupin MySQL, o yẹ ki o wo nkan ti o jọra si iboju atẹle.

[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

Tẹ “?” lati gba akopọ ti awọn aṣayan laini aṣẹ ati lilo.

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

Apakan yii ni awọn iyaworan iboju ti lilo innotop. Lo Awọn bọtini nla-nla lati yipada laarin awọn ipo.

Ipo yii ṣafihan awọn iṣiro olumulo ati awọn iṣiro atọka lẹsẹsẹ nipasẹ awọn kika.

CXN        When   Load  QPS    Slow  QCacheHit  KCacheHit  BpsIn    BpsOut 
localhost  Total  0.00  1.07k   697      0.00%     98.17%  476.83k  242.83k

Ipo yii n ṣe afihan iṣẹjade lati SHOW FULL PROCESSLIST, iru si ipo atokọ ibeere ibeere mytop. Ẹya yii ko ṣe afihan alaye InnoDB ati pe o wulo julọ fun lilo gbogbogbo.

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

Ipo yii ṣe afihan awọn iṣiro I/O InnoDB, ni isunmọtosi I/O, awọn okun I/O, faili I/O ati awọn tabili awọn iṣiro iṣiro nipasẹ aiyipada.

____________________ 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.00

Apakan yii, iwọ yoo wo alaye nipa adagun ifiṣura InnoDB, awọn iṣiro oju-iwe, fi sii ifipamọ, ati itọka elile ifasita. Awọn data mu lati ṢANGAN 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.00

Nibi, iwọ yoo wo iṣiṣẹ ti awọn iṣẹ ila InnoDB, misc iṣẹ kana, semaphores, ati duro de awọn tabili titọ ni aiyipada.

________________ 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?

Ipo akopọ aṣẹ ṣe afihan gbogbo tabili cmd_summary, eyiti o dabi iru si isalẹ.

_____________________ 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%

Apakan yii ṣe iṣiro awọn iṣiro, bii awọn ibeere fun iṣẹju-aaya, ati ṣafihan wọn ni nọmba awọn ipo oriṣiriṣi.

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

Ni ipo yii, iwọ yoo wo abajade ti Ipo SQL Ẹrú, Ipo I/O Ẹrú ati Ipo Titunto. Apakan akọkọ akọkọ fihan ipo ẹrú ati ẹrú ipo I/O ati apakan ti o kẹhin fihan ipo Titunto.

_______________________ 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%

O le ṣiṣe “innotop” ni aiṣe ibaraẹnisọrọ.

# 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

Lati ṣetọju ibi ipamọ data latọna jijin lori eto latọna jijin, lo aṣẹ atẹle nipa lilo orukọ olumulo kan, ọrọ igbaniwọle ati orukọ olupin.

# innotop -u username -p password -h hostname

Fun alaye diẹ sii nipa 'innotop' ilo ati awọn aṣayan, wo awọn oju-iwe eniyan naa nipa titẹ “eniyan innotop” lori ebute kan.

Itọkasi Awọn ọna asopọ

Innotop Aaye akọọkan

  1. Mtop (Abojuto Abojuto data data MySQL) ni RHEL/CentOS/Fedora