Innotop monitoring MySQL

Innotop

is a system activity report, similar to the Linux Performance tool, which resembles the Linux top command and is designed with reference to the Mytop tool.

It specializes in post-monitoring InnoDB performance and MySQL server. Mainly used for monitoring transactions, deadlocks, foreign keys, query activities, replication activities, system variables, the main statistical information
and other details of the host. Innotop is used extensively and is used as a common performance monitoring tool.

Since Innotop is written in the Perl language, it is necessary to install the Perl environment and the associated toolkit.

To install Perl:

Yum Install perl*-y

You must install the Time::hires,term::readkey,dbi,dbd::mysql four packages

[Email protected] data]# Perl-mcpan-eshell

Cpan> Install Time::hires
Cd
Cpan> Install Term::readkey

Cpan> Install DBI

Cpan> Install Dbd::mysql

One. Download and install innotop-1.8

[Email protected] data]# wget http://innotop.googlecode.com/files/innotop-1.8.0.tar.gz

[Email protected] data]# tar xvzf innotop-1.8.0.tar.gz

[Email protected] data]# CD innotop-1.8.0

[Email protected] data]# Perl makefile.pl

[[email protected] data]# make

[[email protected] data]# make install

Two. Use

Innotop-u <username>-P <password>-H
Example: Innotop-usystem-p ' hd_root2010! '-p 3306-s/data/mysqldata/3306/mysql.sock

The following is the page status entered

When Load QPS Slow se/in/up/de% qcachehit kcachehit bpsin bpsout
Now 0.05 0.19 0 0/0/550/50 0.00% 92.31% 32.67k 1.18k
Total 0.00 0.36 1.24k 20/3838/1473/3297 0.00% 93.88% 36.83k 284.40k

CMD ID State User Host DB time Query

Where load is the load
QPS (query per Second) indicates the rate of queries per second
Slow represents the number of slow queries
se/in/up/de%: Increase, delete, change, check the ratio
Qcachehit: Query buffer hit ratio
Kcachehit: Hit ratio of the index
Bpsin (bytes per Second in) number of bytes entered per second
Bpsout (bytes per Second out) The number of bytes output per second


Input? You can access the Help screen as follows:

Switch to a different mode:
B InnoDB buffers L Locks R InnoDB Row Ops
C Command Summary M Replication status S Variables & Status
D InnoDB deadlocks O Open Tables T InnoDB txns
F InnoDB FK Err Q Query List U User Statistics
I InnoDB I/O Info

Actions:
A Toggle the innotop process K Kill a query ' s connection
c Choose Visible Columns n Switch to the next connection
D Change Refresh interval p Pause innotop
e Explain A thread ' s query Q Quit innotop
F Show A thread ' s full query R Reverse sort order
H Toggle the header on and off S-Change the display S sort column
I Toggle idle processes x Kill a query

Other:
TAB Switch to the next server group/quickly filter
! 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

Various modes of switching are: shift+ letters

B Mode: InnoDB buffers

____________________________ Buffer Pool ____________________________
Size free bufs pages Dirty pages hits rate Memory ADD ' l Pool
512.00k 17863 495843 0--8.19G 0

_____________________ Page Statistics ______________________
Reads writes Created reads/sec writes/sec creates/sec
443950 5953289 51893 0.00 0.00 0.00

______________________ Insert Buffers ______________________
Inserts merged Recs Merges Size free List Len Seg. Size


_________________ Adaptive Hash Index __________________
Size Cells used Node Heap bufs hash/sec non-hash/sec
0.00 0.00

which
Buffer Pool:
Size: The sizes of the buffer pool used by a SQL
The value of free Bufs:innodb_buffer_pool_pages_free, empty pages.
The value of the pages:innodb_buffer_pool_pages_data that contains the number of pages (dirty or clean) of the data.
Dirty the value of Pages:innodb_buffer_pool_pages_dirty, the current number of dirty pages
Hit Rate: Hits
The value of the Memory:innodb_buffer_pool_size
The value of the Add ' L pool:innodb_additional_mem_pool_size

Page Statisics (statistics)
Reads:innodb_pages_read value, number of pages read
Writes:innodb_pages_written value, number of pages written
The value of created:innodb_pages_created, the number of pages created


C Mode: Command Summary (show Global status)
The displayed values are sorted in descending order, and the variable must be a number, press S and then enter the prefix name of the relevant parameter:

Press S to build: Enter InnoDB:

D Mode: InnoDB deadlocks (generated deadlock, and birth and death lock statement)

____________________________________________________________________________________ Deadlock Transactions ___ ______________________________________________________________________________
ID timestring User Host victim time Undo lstrcts Query Text
182513696 2012-11-15 15:39:30 apps_oper 192 No 00:00 215 9 insert into Category_doc_info (category_id, Doc_title,category_show,category_coordinate) VALUES (23692, ' 1941 ', +)
182519005 2012-11-15 15:39:30 apps_oper 192 Yes 00:00 0 4 delete from Category_doc_info where (Catego ry_id = 23692)

______________________________________ Deadlock Locks ______________________________________
ID waiting Mode DB Table Index Special Ins Intent
182513696 0 X Apps category_doc_info Index_search Rec but not gap 0
182513696 1 S Apps category_doc_info Index_search 0
182519005 1 X Apps category_doc_info Index_search Rec but not gap 0


which
ID: Connection Thread ID number
TimeString: The time when the deadlock occurred
User: Username
Host: Hosts


M mode: Replication status (View replication status information)

I mode: InnoDB I/o info (view information for InnoDB corresponding I/O)

O Mode: View open tables

Q mode (one that works well):

Then e enters the thread ID to display the execution plan or press F to display the full SQL statement.

Innotop monitoring MySQL