MySQL operation and Maintenance tool artifact Summary

Source: Internet
Author: User
Tags mysql client stack trace svn svn client switches time interval high cpu usage oracle database

These tools may have been used, especially the system administrator or Linux Server maintenance students may know these gadgets, there will be a little more, in addition to the system monitoring gadgets, including some MySQL tools, and even in-depth tools will be mentioned.








1, probably will tell the content





Vmstat/iostat/mpstat/ifstat/dstat:stat class, view system status,





Pstack: This command displays the stack trace for each process. The Pstack command must be run by the owner or root of the corresponding process. You can use Pstack to determine where the process hangs. For the kernel and source research people, this is relatively easy to understand.





When testing, use the following to simulate product conditions, stress testing:





Sysbench--test=fileio--file-total-size=10g--file-test-mode=rndrw--max-time=3600--max-requests=0 Run





Reference: http://www.cnblogs.com/ggjucheng/archive/2012/01/05/2312625.html





Tcprstat:tcprstat is a free open source TCP analysis tool that analyzes request response times by monitoring network transmissions





Reference: http://blog.csdn.net/wyzxg/article/details/8493658





Tcpdump: Grab Bag tool





Reference: http://www.cnblogs.com/ggjucheng/archive/2012/01/14/2322659.html





Perf: Starting from the 2.6.31 kernel, the Linux kernel has its own performance analysis tool perf, which can be used for the function-level and instruction-level hotspot lookup for software performance analysis.





Pt-stalk: Collect MySQL data for diagnostics when there is a problem





Orzdba:perl script for real-time monitoring of Linux host and MySQL related metrics.




















2,vmstat Tools








by Yum the Sysstat (yum-y install sysstat), you can use Vmstat, Vmstat: Collect information such as memory, process, paging, and so on.








Mainly look at the virtual memory aspect, want to look at the virtual memory in detail, with Vmstat is a good choice.








The Vmstat command is the most common Linux/unix monitoring tool that shows the state value of a server at a given time interval, including CPU utilization, memory usage, virtual memory exchange, and IO reading and writing. This command is my favorite command to check Linux/unix, one is Linux/unix, and the second is that I can see the CPU, memory, IO usage of the entire machine, compared to top, instead of just seeing CPU usage and memory usage of each process (using different scenarios).








The use of general vmstat Tools is done with two numeric parameters, the first parameter is the number of time intervals sampled, in seconds, the second parameter is the number of samples, as shown below, interval 2 seconds, sampling 3 times:





[Root@data01 ~]# Vmstat 2 3





procs-----------Memory-------------Swap-------io------System-------CPU-----





R b swpd free buff cache si so bi bo in CS us sy ID WA St





0 1 434092 83364 133292 207336 0 2 9 25 33 47 0 0 99 1-0





1 0 434092 82604 133300 208076 0 0 200 2064 1833 3695 1 17 72 9-0





2 0 434092 80876 133300 209512 0 0 0 302 193 64 2 12 82 4-0





[Root@data01 ~]#








Parameter introduction:





R indicates that the running queue (that is, how many processes are actually allocated to the CPU), I tested the server currently CPU is more idle, there is no program running, when this value exceeds the number of CPUs, there will be CPU bottlenecks. This also and top of the load is related to the general load more than 3 higher, more than 5 on the high, more than 10 is not normal, the state of the server is very dangerous. The top load is similar to the run queue per second. If the running queue is too large, which means your CPU is busy, it will generally cause high CPU usage.





b is blocking the process, this is not much to say, process blocking, we understand.





SWPD virtual memory has been used in size, if greater than 0, indicates that your machine physical memory is not enough, if not the reason for the program memory leak, then you should upgrade the memory or the memory of the task to migrate to other machines.





Free of the physical memory size, my machine memory total 8G, remaining 3415M.





Buff Linux/unix system is used to store, directory contains what content, permissions, etc. cache, I this machine probably occupy more than 300 m





Cache cache is directly used to memorize the files we open, to buffer the file, I am about 300 m on this machine (here is the wisdom of Linux/unix, the spare part of the physical memory to do file and directory caching, is to improve the performance of program execution, when the program uses memory, Buffer/cached will soon be used. )





Si per second the size of the virtual memory from disk, if this value is greater than 0, indicates that the physical memory is not enough or memory leaks, to find the memory process to solve the loss. My machine has plenty of memory and everything is fine.





So per second virtual memory is written to disk size, if this value is greater than 0, ditto.





The number of blocks received by the BI block device per second, the block device here refers to all the disks and other block devices on the system, the default block size is 1024byte, I have no IO on this machine, so it's always 0, but I've seen it on a machine that handles copies of large amounts of data (2-3t) and can reach 140000/s, Disk write speed is almost 140M per second





The number of blocks sent by the Bo block device per second, for example, when we read the file, Bo must be greater than 0. Bi and Bo are generally close to 0, or IO is too frequent and need to be adjusted.





The number of CPU interrupts per second, including time outages





CS the number of context switches per second, for example, we call the system function, we need to do context switching, thread switching, also process context switch, this value to the smaller the better, too big, to consider the number of threads or processes, such as in Apache and nginx such a Web server, We generally do performance testing will be thousands of concurrent or even tens of thousands of concurrent testing, the process of selecting a Web server can be a process or thread peak has been reduced, pressure, until the CS to a relatively small value, the process and the number of threads is more appropriate value. System call is also, each call system functions, our code will enter the kernel space, resulting in context switching, this is very resource-intensive, but also try to avoid frequent call system functions. Too many context switches means that most of your CPU waste in context switching, resulting in less CPU time to do serious things, the CPU is not fully utilized, is not desirable.





US user CPU time, I used to do encryption decryption very frequently on the server, you can see us close to the 100,r run queue reached 80 (the machine is doing stress testing, poor performance).





SY system CPU time, if too high, indicates a long system call time, such as IO operations frequently.





ID free CPU time, in general, ID + US + sy = 100, generally I think ID is idle CPU usage, US is the user CPU utilization, SY is the system CPU utilization rate.





WT waits for IO CPU time.








PS: See more parameters in turn, the most basic (SWPD, free, buff, cache), memory read-write (SI, so), CPU aspects (in, CS); CS needs to be constantly concerned, it is context switching, especially when we have a very high number of stand-alone threads, There will be a lot of context switching, and the CPU going through the context switch itself is very consuming resources, such as your execution task is frequently switched to switch to not actually be executed.




















3,iostat Tools








As with Vmstat, you can use it by installing the Sysstat (yum-y install sysstat) Yum way. We are mainly looking at IO status.











3.1 Iostat parameter description








-C displays only CPU statistics. The-D option is mutually exclusive.


-D displays only disk statistics. Mutually exclusive with the-C option.


-K Displays the number of disk requests per second, the default unit block, in K.


-P Device | All


Mutexes with the-X option to display statistics for block devices and system partitions. You can also specify a device name after-p, such as:


# iostat-p HDA


or Show All devices


# iostat-p All


-T prints the time to collect data when outputting data.


-V Print version number and help information.


-X Output extension information.











3.2 Iostat Output Project description








Blk_read: The total number of reads into the block. BLK_WRTN: Total number of write blocks kb_read/s: The amount of data read from the drive per second, in units K.


KB_WRTN/S: The amount of data written to the drive per second, in units K.


Kb_read: The total amount of data read, the unit is K.


KB_WRTN: The total amount of data written, in units K.


RRQM/S: The number of read requests sent to the device per second after merging the read requests.


WRQM/S: The number of write requests sent to the device per second after merging write requests.


R/S: The number of read requests sent to the device per second.


W/S: Number of write requests sent to the device per second.


RSEC/S: The number of sectors read from the device per second.


WSEC/S: The number of sectors written to the device per second.


RKB/S: The amount of data read from the device per second, in units K.


WKB/S: The amount of data written to the device per second, in units K.


Avgrq-sz: The average size of the request sent to the device, in units of sector.


Avgqu-sz: The average queue length of the request sent to the device.


Average execution time of the AWAIT:I/O request. Includes the time to send requests and executions. The unit is milliseconds.


SVCTM: Average execution time of I/O requests sent to the device. The unit is milliseconds.


%util: The percentage of CPU time that is consumed during an I/O request being sent to the device. Used to display the bandwidth utilization of the device. When this value is close to 100%, the device bandwidth is already full.











3.3 Iostat Example








# Iostat: Displays a statistical record, including all CPUs and devices.


# iostat-d 2: Every 2 seconds, the device statistics are displayed.


# iostat-d 2 6: Every 2 seconds, the device statistics are displayed. Total output 6 times.


# iostat-x SDA1 sda2 2 6: Every 2 seconds the extended statistics of HDA,HDB two devices are displayed, with a total output of 6 times.


# Iostat-p SDA1 2 6: Every 2 seconds to display a SDA and all the partitions above the statistics, total output 6 times.








PS: Main View Avgqu-sz, await, SVCTM




















4,mpstat Tools








Another useful command for obtaining CPU-related statistics is mpstat.





Sysstat (yum-y install sysstat) is installed by Yum method, and Mpstat is available











4.1 Examples








[Root@data01 ~]# mpstat-p all 2 2





Linux 2.6.32-220.el6.x86_64 (DATA01) April 03, 2015 _x86_64_ (2 CPU)











23:11 43 sec CPU%usr%nice%sys%iowait%irq%soft%steal%guest%idle





23:11 45 seconds All 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00





23:11 45 Second 0 0.00 0.00 0.50 0.00 0.00 0.00 0.00 0.00 99.50





23:11 45 Second 1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00











23:11 45 sec CPU%usr%nice%sys%iowait%irq%soft%steal%guest%idle





23:11 47 Seconds All 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00





23:11 47 Second 0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00





23:11 47 Second 1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00











Average time: CPU%usr%nice%sys%iowait%irq%soft%steal%guest%idle





Average Time: All 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00





Average Time: 0 0.00 0.00 0.25 0.00 0.00 0.00 0.00 0.00 99.75





Average Time: 1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00





[Root@data01 ~]#








It shows the various statistics about the CPU in the system. The P all option instructs the command to display statistics for all CPUs, not just specific CPU statistics. Parameter 2 2 indicates that the command runs every 2 seconds and runs 2 times.








The above output first shows the aggregate metric for all CPUs, and then shows the respective metrics for each CPU. Finally, the average of all CPUs is displayed at the end.











4.2 Significance of each parameter








%user represents the percentage of CPU used to process user processes. User processes are non-kernel processes that are used for applications, such as Oracle databases.





%nice represents the percentage of the CPU when a process is demoted using the nice command. The nice command has been introduced in the previous section. In simple terms, the nice command changes the priority of the process.





%system represents the percentage of CPU used by the kernel process





%iowait represents the percentage of CPU time that is used to wait for I/O





%IRQ represents the percentage of CPU used to handle system interrupts





%soft represents the percentage of CPU used for software interrupts





%idle shows the CPU idle time





%INTR/S shows total number of interrupts per second CPU received











When you have the Vmstat described earlier, you may want to know the role of the Mpstat command. The difference is large: Mpstat can display statistics for each processor, while Vmstat displays statistics for all processors. As a result, writing a bad application (without using a multithreaded architecture) might run on a multiprocessor machine without using all of the processors. This causes one CPU to overload, while the other CPUs are idle. These types of problems can be easily diagnosed through mpstat.








PS: Usage for Oracle users








Similar to Vmstat, the Mpstat command also produces CPU-related statistics, so all discussions related to CPU issues apply to Mpstat. When you see a lower%idle number, you know that there is a problem with a CPU shortage. When you see a higher%iowait number, you know that there are some problems with the I/O subsystem under the current load. This information is convenient for resolving Oracle database performance issues.




















5,ifstat Tools








Small tools for viewing network traffic











5.1 Download installation








Download Ifstat installation package:, Wegt http://gael.roualland.free.fr/ifstat/ifstat-1.1.tar.gz





Yum Install gcc*-y #预先安装好gcc组件





TAR-XVF ifstat-1.1.tar.gz





CD ifstat-1.1





./configure





Make





Make install











5.2 Test View detection results








[Root@data01 ifstat-1.1]# ifstat-t-I. eth1-t





Time Eth1 Total





HH:MM:SS kb/s in kb/s out kb/s into kb/s out





23:37:38 0.12 0.30 0.12 0.30





23:37:39 0.06 0.15 0.06 0.15





23:37:40 0.06 0.15 0.06 0.15





23:37:41 0.06 0.15 0.06 0.15




















6,dstat Tools








Dstat is a tool for replacing Vmstat,iostat Netstat,nfsstat and Ifstat commands, and is an all-purpose system Information statistic tool. Compared with Sysstat, Dstat has a color interface, which is easy to observe when manually observing performance conditions; and Dstat support instant refresh, such as input Dstat 3, which is collected every three seconds, but the latest data will be refreshed every second. As with Sysstat, Dstat can also collect specified performance resources, such as DSTAT-C, which shows CPU usage.








can go to contos and redhat download link: http://pkgs.repoforge.org/dstat/download rpm installation package installation; You can also use Yum installation: Yum install-y dstat








Run Dstat, as shown below





[Root@data01 ifstat-1.1]# Dstat 3





----total-cpu-usage-----dsk/total--net/total----paging-----system--





USR sys IDL Wai hiq siq| Read writ| Recv send| In Out | int CSW





0 0 99 0 0 0| 24k 9603b| 0 0 | 254B 4410b| 38 48





0 0 100 0 0 0| 0 5461b| 80B 525b| 0 0 | 50 53





0 0 100 0 0 0| 0 0 | 70B 359b| 0 0 | 33 44





0 0 100 0 0 0| 0 21k| 146B 341b| 0 0 | 61 63





0 0 100 0 0 0| 0 0 | 60B 346b| 0 0 | ^c





[Root@data01 ifstat-1.1]#








Some of the information shown here is described below:





1. Cpu:hiq,siq is the number of hard interrupts and soft interrupts respectively.





2. SYSTEM:INT,CSW for system interrupts (interrupt) and Context switches (contextual switch)





All the others are well understood.





Common parameter options can be seen through Dstat--help








PS: This software is similar to Vmstat, but it seems to be less than the Vmstat IO part of the display, Dstat can only show the disk throughput rather than display load, dstat and almost all monitoring software, You can only monitor the entire system and not analyze a process or a program in depth. The arguments I use often are dstat-cdlmnpsy, alias links aliases dstat= ' Dstat-cdlmnpsy '








Reference: http://blog.csdn.net/yuandianlws/article/details/7004364




















7,tcprstat Tools








Check the MySQL contingency and performance exception tools to capture the details of the situation at that time.








7.1 bzr mode installation download








BZR Branch Lp:tcprstat





Tar czvf tcprstat.tar.gz./tcprstat





CD tcprstat/





chmod +x Bootstrap








Install Automake and autoconf





Yum List|grep Automake





Yum List|grep autoconf





Yum-y Install Automake








After the installation succeeds, re-execute the bootstrap





./bootstrap





and then compile





./configure





Make











7.2 Mode Download








# Download the address, wget download failed, you can download the Web page under Windows, and then secure upload to the MySQL server above





Http://github.com/downloads/Lowercases/tcprstat/tcprstat-static.v0.3.1.x86_64


# CP tcprstat-static.v0.3.1.x86_64 Tcprstat


# chmod A+x Tcprstat











7.3 Examples








./tcprstat-p 3306-t 1-n 5





-P-Monitored port number,-t interval, n-run number





[root@wgq_idc_dbm_3_61 ~]# chmod a+x tcprstat





[root@wgq_idc_dbm_3_61 ~]#./tcprstat-p 3306-t 1-n 5





Timestamp count Max min avg med stddev 95_max 95_avg 95_std 99_max 99_avg





1428142214 41 548646 28 79647 145 173376 418497 44645 125122 521857 67922-158663





1428142215 25 422953 22 54943 120 118359 201027 25393 65142 366568 39609-93352





1428142216 5 409520 193 155644 223 190817 368069 92175 159287 368069 92175-159287





1428142217 24 881981 45 52977 130 188839 1968 377 556 381189 16934-77661





1428142218 15 356049 49 24126 133 88711 1989 417 549 1989 417-549





[Root@wgq_idc_dbm_3_61 ~]#











7.4, the actual use of the case








If the user thinks our MySQL database response time is very slow, the query also suddenly slows down, what we do, the method certainly has many kinds, here may choose to use the Tcprstat, we may send the./tcprstat-l 192.168.52.129-p 3306-t 1-n 0 to the user And let him do it once on his application server. Tcprstat check to see how the response time is, we can know what the specific reason is.











Reference: http://my.oschina.net/moooofly/blog/157063




















8,tcpdump








Grab kits, which can intercept all requests to the MySQL database server for client operations.








8.1 Introduction








The system's own toolkit, if not, use Yum installation (yum install Y tcpdump) to define tcpdump in simple words: Dump the traffic on a network, A packet analysis tool that intercepts packets on a network based on the user's definition. Tcpdump can intercept the "head" of packets transmitted in the network and provide analysis. It supports filtering for network layers, protocols, hosts, networks, or ports, and provides logical statements such as and, or, not to help you get rid of useless information.








To view parameter information through Tcpdump:





[Root@data02 ~]# tcpdump--help





Tcpdump version 4.1-pre-cvs_2010_08_20





Libpcap version 1.0.0





Usage:tcpdump [-AADDEFIKLLNNOPQRSTUUVXX] [-B size] [-c Count]





[-C File_size] [-E Algo:secret] [-F file] [G seconds]





[I. Interface] [M secret] [-R File]





[-S Snaplen] [-T type] [-W file] [-W FileCount]





[Y Datalinktype] [Z Command] [-Z User]





[Expression]





[Root@data02 ~]#





Tcpdump-s 0-l-w-dst xxx.xxx.xxx.xxx and Port 3306 |strings











8.2 Use








Monitor what the 192.168.52.129 server MySQL is doing now by following the command





Tcpdump-i eth1-s 0-l-w-dst 192.168.52.129 and Port 3306, as follows:





[Root@data01 ~]# tcpdump-i eth1-s 0-l-w-dst 192.168.52.129 and Port 3306 |strings





Tcpdump:listening on eth1, Link-type EN10MB (Ethernet), capture size 65535 bytes





v68@





SELECT * FROM user_db. Uc_user





469@





5.6.12-log





9?o3mil}





Umh^~ "(apnw-





Mysql_native_password





#28000Access denied for user ' repl ' @ ' 192.168.52.129 ' (using Password:yes)





j6:@





SELECT DATABASE ()





46;@





=6<@





Test





46=@





UfN





d6>@





Show tables





46?@





s6@@





SELECT * FROM Table_myisam "





Ups





46a@











It can be seen that tcpdump can expose the MySQL client connection to the MySQL database of all the packages information. Tcpdump can do the audit, the downstream client to the MySQL database of all the operations are recorded statistics compiled to help us analyze the problem, tcpdump produced more information, we can do the expansion of their own to filter out the information they need to record.




















9,perf Tools








Direct Yum installation can be: Yum install perf? y





Perf is a toolset with 22 seed tools, and the following are the 5 most commonly used:





Perf list: Displaying performance events





Perf Top: Real-time display of performance statistics mostly at the kernel level





Perf Stat: Analyzing overall performance





Perf Record: Analyzing performance over a period of time





Perf report: According to record records, statistical reports








Perf Reference: http://blog.chinaunix.net/uid-1829236-id-3212867.html





Perf Reference: http://blog.csdn.net/zhangskd/article/details/37902159




















10,pt-stalk








Tools Download:





wget http://www.percona.com/downloads/percona-toolkit/2.2.13/deb/percona-toolkit_2.2.13.tar.gz








Installation:





TAR-XVF percona-toolkit_2.2.13.tar.gz





CD percona-toolkit-2.2.13





Perl makefile.pl





Make





Make Test





Make install








Use





Pt-stalk--collect-tcpdump--function status--variable threads_connected--threshold 2500--daemonize-- host=127.0.0.1--port=3306








Mainly used to capture the scene, such as: MySQL server performance fluctuations occur very low frequency, for example, a few days, MySQL server performance fluctuations occur quickly, for example, a few seconds flashed, I would like to output some information, trigger these thresholds, when the print, Then we have to catch this scene, we can know exactly what is done to cause these performance fluctuations of the knife appears.











--daemonize: Run in the background.





Pt-stalk--collect-tcpdump--function status--variable handler_connected--threshold 3--user=root--host=127.0.0.1-- port=3306





[Root@data01 percona-toolkit-2.2.13]# pt-stalk--collect-tcpdump--function status--variable handler_connected-- Threshold 3--user=root--host=127.0.0.1--port=3306





2015_04_04_19_01_21 starting/usr/local/bin/pt-stalk--function=status--variable=handler_connected--threshold=3-- Match=--cycles=5--interval=1--iterations=--run-time=30--sleep=300- notify-by-email=--log=/var/log/pt-stalk.log--pid=/var/run/pt-stalk.pid--plugin=





2015_04_04_19_01_21 detected value is empty; Something failed? Trigger Exit status:0





2015_04_04_19_01_22 detected value is empty; Something failed? Trigger Exit status:0





2015_04_04_19_01_23 detected value is empty; Something failed? Trigger Exit status:0





2015_04_04_19_01_24 detected value is empty; Something failed? Trigger Exit status:0





2015_04_04_19_01_25 detected value is empty; Something failed? Trigger Exit status:0





2015_04_04_19_01_26 detected value is empty; Something failed? Trigger Exit status:0





2015_04_04_19_01_27 detected value is empty; Something failed? Trigger Exit status:0





2015_04_04_19_01_28 detected value is empty; Something failed? Trigger Exit status:0





2015_04_04_19_01_29 detected value is empty; Something failed? Trigger Exit status:0








When you find that the MySQL server performance fluctuations, encounter performance problems encountered, but you can not find out what MySQL did in this time, you could use Pt-stalk to crawl all the behavior of these scenes, to help you analyze the query.




















11,orzdba








Installation please refer to: HTTP://WENKU.BAIDU.COM/VIEW/79CA1EB065CE0508763213BF





First you need to install the SVN client and Tcprstat,yum installation: Yum install-y svn;yum install-y tcprstat;





Then download the ORZDBA package:





SVN Co http://code.taobao.org/svn/orzdba/trunk





CD trunk/





chmod a+x ORZDBA








Probably including Sys, MySQL, InnoDB, lazy 4 aspects, is a synthesizer, is Taobao an engineer wrote, it put the system, we need to remember that lazy enough to use, with lazy can basically meet most of our application needs. The lazy command is as follows:





./orzdba-lazy-rt-s/usr/local/mysql/mysql.sock








-------------LOAD-AVG-------cpu-usage------swap----qps--tps--hit%---------tcprstat (US)--------





Time | 1m 5m 15m |USR sys IDL iow| Si so| Ins upd del sel iud| Lor hit| Count avg 95-avg 99-avg|





21:00:17| 0.32 0.28 0.37| 0 0 99 0| 0 0| 0 0 0 0 0| 0 100.00| 0 0 0 0|





21:00:18| 0.32 0.28 0.37| 2 24 43 30| 0 128| 903 0 0 1 903| 4774 100.00| 0 0 0 0|





21:00:19| 0.32 0.28 0.37| 2 21 49 27| 0 0| 742 0 0 1 742| 3922 100.00| 0 0 0 0|





21:00:20| 0.32 0.28 0.37| 3 28 53 17| 0 0| 1108 0 0 1 1108| 5861 100.00| 0 0 0 0|





21:00:21| 0.32 0.28 0.37| 3 29 52 17| 0 0| 1037 0 0 1 1037| 5492 100.00| 0 0 0 0|





21:00:22| 0.29 0.28 0.37| 3 29 52 16| 0 0| 1003 0 0 1 1003| 5276 100.00| 0 0 0 0|





21:00:23| 0.29 0.28 0.37| 3 28 54 15| 8 0| 744 0 0 2 744| 5097 100.00| 0 0 0 0|





21:00:24| 0.29 0.28 0.37| 3 28 51 19| 0 18| 777 0 0 1 777| 4222 100.00| 0 0 0 0|





21:00:25| 0.29 0.28 0.37| 3 30 51 16| 0 0| 997 0 0 1 997| 5291 100.00| 0 0 0 0|





21:00:27| 0.27 0.27 0.36| 3 29 52 17| 0 0| 1160 0 0 1 1160| 6121 100.00| 0 0 0 0|





^c





Exit now ...











[Root@data01 trunk]#








This time, if you still want to see more detailed information, you can not lazy, you can use InnoDB parameters, you can see the buffer pool inside the data, free, dirty and so on, very full information, we can as a very important reference, the order is as follows:





./orzdba-innodb-rt-s/usr/local/mysql/mysql.sock





-----------innodb bp pages status-------innodb data status------InnoDB log--his--log (byte)--Read---query---------- -tcprstat (US)--------





Time | Data free Dirty flush| Reads writes read Written|fsyncs written| List Uflush uckpt view inside que| Count avg 95-avg 99-avg|





21:01:13| 0 0 0 0| 0 0 0 0| 0 0| 0 0 0 0 0 0| 0 0 0 0|





21:01:14| 4245 12137 815 0| 0 0 55k| 0 55k| 593 0 11.6m 0 0 0| 0 0 0 0|





21:01:15| 4248 12134 818 0| 0 0 131k| 0 131k| 593 0 11.7m 0 0 0| 0 0 0 0|





21:01:16| 4251 12131 821 0| 0 129 0 153k| 0 153k| 593 11.8m 0 1 0| 0 0 0 0|





21:01:17| 4256 12126 826 0| 0 145 0 182k| 0 182k| 593 0 11.9m 0 0 0| 0 0 0 0|





21:01:19| 4258 12124 828 0| 0 0 110k| 0 110k| 593 0 11.9m 0 0 0| 0 0 0 0|





21:01:20| 4261 12121 831 0| 0 0 91k| 0 89k| 593 576 12.0m 0 0 0| 0 0 0 0|





21:01:21| 4265 12117 841 0| 0 161 0 203k| 0 203k| 597 12.1m 0 0 0| 0 0 0 0|





^c





Exit now ...








Other monitoring items:





Network:./orzdba-n eth1-rt-s/usr/local/mysql/mysql.sock





Mysql:./orzdba-mysql-rt-s/usr/local/mysql/mysql.sock








So orzdba basically monitor all the MySQL state and the system state, you can consider using ORZDBA online to monitor the MySQL library on the line





Reference: http://www.freebuf.com/tools/5262.html





Reference: HTTP://WENKU.BAIDU.COM/VIEW/79CA1EB065CE0508763213BF




















12,orztop








View the current processlist and full processlist in real time;





A tool to view the SQL status of the MySQL database running in real time, the comrades who previously struggled to crawl SQL through show processlist/show full processlist now just stare at the screen, it's very convenient,








Download: wget http://hidba.org/wp-content/uploads/2013/06/orztop.rar





Installation: First enter the Cpan window, install DBI, DBD, and then exit the decompression Orztop.rar package can be used











The order is as follows:





Orztop-h=192.168.52.129-p=3006-u=orztop-p=orztop-t=1

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.