开发者社区> 玄学酱> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

19.12. Monitoring

简介:
+关注继续查看

http://netkiller.sourceforge.net/monitoring/index.html

19.12.1. Analysis and Optimization

19.12.1.1. mytop - top like query monitor for MySQL

sudo apt-get install mytop
		
mytop --host=172.16.0.7 --user=monitor --password=your_passwd
		

19.12.1.2. mtop - MySQL terminal based query monitor

http://mtop.sourceforge.net/

sudo apt-get install mtop
mtop --host=172.16.0.6 --dbuser=monitor --password=your_passwd
		

mkill

mkill -sl 180 -fi 'select.*from bad_table' > /var/log/mkill.out 2> /var/log/mkill.kill
		

19.12.1.3. innotop

19.12.1.4. mysqlreport - A friendly report of important MySQL status values

# yum install mysqlreport -y
		
wget hackmysql.com/scripts/mysqlreport
		
		
[root@database ~]# mysqlreport --user root --password chen
Use of uninitialized value in multiplication (*) at /usr/bin/mysqlreport line 829.
Use of uninitialized value in formline at /usr/bin/mysqlreport line 1227.
MySQL 5.0.77-log         uptime 28 23:42:33     Sat Apr 10 18:15:44 2010

__ Key _________________________________________________________________
Buffer used     6.54M of   8.00M  %Used:  81.75
  Current       1.49M            %Usage:  18.58
Write hit      97.65%
Read hit       99.81%

__ Questions ___________________________________________________________
Total           2.22M     0.9/s
  DMS           1.91M     0.8/s  %Total:  86.16
  Com_        249.93k     0.1/s           11.25
  COM_QUIT     63.68k     0.0/s            2.87
  -Unknown      6.26k     0.0/s            0.28
Slow 10 s          52     0.0/s            0.00  %DMS:   0.00  Log: OFF
DMS             1.91M     0.8/s           86.16
  SELECT        1.17M     0.5/s           52.81         61.29
  INSERT      276.13k     0.1/s           12.43         14.43
  DELETE      264.78k     0.1/s           11.92         13.84
  UPDATE      158.14k     0.1/s            7.12          8.26
  REPLACE      41.74k     0.0/s            1.88          2.18
Com_          249.93k     0.1/s           11.25
  set_option   89.09k     0.0/s            4.01
  change_db    59.71k     0.0/s            2.69
  show_create  28.57k     0.0/s            1.29

__ SELECT and Sort _____________________________________________________
Scan          161.33k     0.1/s %SELECT:  13.76
Range           6.47k     0.0/s            0.55
Full join       1.56k     0.0/s            0.13
Range check         0       0/s            0.00
Full rng join       0       0/s            0.00
Sort scan      34.03k     0.0/s
Sort range     21.98k     0.0/s
Sort mrg pass     733     0.0/s

__ Table Locks _________________________________________________________
Waited             56     0.0/s  %Total:   0.00
Immediate       2.15M     0.9/s

__ Tables ______________________________________________________________
Open               64 of   64    %Cache: 100.00
Opened        159.20k     0.1/s

__ Connections _________________________________________________________
Max used           36 of  200      %Max:  18.00
Total          63.75k     0.0/s

__ Created Temp ________________________________________________________
Disk table     32.80k     0.0/s
Table          63.69k     0.0/s    Size:  32.0M
File              319     0.0/s

__ Threads _____________________________________________________________
Running             1 of    1
Cached              0 of    0      %Hit:      0
Created        63.75k     0.0/s
Slow                0       0/s

__ Aborted _____________________________________________________________
Clients           128     0.0/s
Connects          130     0.0/s

__ Bytes _______________________________________________________________
Sent           23.89G    9.5k/s
Received        6.36G    2.5k/s

__ InnoDB Buffer Pool __________________________________________________
Usage           8.00M of   8.00M  %Used: 100.00
Read hit       99.99%
Pages
  Free              0            %Total:   0.00
  Data            511                     99.80 %Drty:   0.00
  Misc              1                      0.20
  Latched                                  0.00
Reads           1.54M     0.6/s
  From file       135     0.0/s            0.01
  Ahead Rnd         4     0.0/s
  Ahead Sql         6     0.0/s
Writes        868.00k     0.3/s
Flushes         1.56k     0.0/s
Wait Free           0       0/s

__ InnoDB Lock _________________________________________________________
Waits               0       0/s
Current             0
Time acquiring
  Total             0 ms
  Average           0 ms
  Max               0 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
  Reads           194     0.0/s
  Writes          628     0.0/s
  fsync           323     0.0/s
  Pending
    Reads           0
    Writes          0
    fsync           0

Pages
  Created         534     0.0/s
  Read            201     0.0/s
  Written       1.56k     0.0/s

Rows
  Deleted           0       0/s
  Inserted    423.82k     0.2/s
  Read          1.27M     0.5/s
  Updated           0       0/s
		
		

19.12.1.5. mysqltuner - MySQL configuration assistant

		
# mysqltuner

 >>  MySQLTuner 1.1.1 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[!!] Successfully authenticated with no password - SECURITY RISK!

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.69
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[!!] InnoDB is enabled but isn't being used
[OK] Total fragmented tables: 0

-------- Security Recommendations  -------------------------------------------
[!!] User 'root' has no password set.
[!!] User 'root' has no password set.
[!!] User 'root' has no password set.
[!!] User '' has no password set.
[!!] User '' has no password set.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 18m 55s (42 q [0.037 qps], 7 conn, TX: 27K, RX: 1K)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 34.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 449.2M (45% of installed RAM)
[OK] Slow queries: 0% (0/42)
[OK] Highest usage of available connections: 0% (1/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/89.0K
[!!] Query cache is disabled
[OK] Temporary tables created on disk: 0% (0 on disk / 4 total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 76% (23 open / 30 opened)
[OK] Open file limit used: 4% (46/1K)
[OK] Table locks acquired immediately: 100% (19 immediate / 19 locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Set thread_cache_size to 4 as a starting value
Variables to adjust:
    query_cache_size (>= 8M)
    thread_cache_size (start at 4)
		
		

19.12.2. Munin

19.12.3. Cacti

19.12.4. Monitoring MySQL with SNMP

mysql-snmp - monitoring MySQL with SNMP

		





原文出处:Netkiller 系列 手札
本文作者:陈景峯
转载请与作者联系,同时请务必标明文章原始出处和作者信息及本声明。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Elasticsearch Mapping类型映射概述与元字段详解
Elasticsearch Mapping类型映射概述与元字段详解
36 0
MyBatis + Spring 基于SqlSessionDaoSupport的泛型基类
引官网一段话: SqlSessionDaoSupport 是 一 个 抽象 的支 持 类, 用来 为你 提供 SqlSession 。
869 0
数据迁移中的数据库检查和建议
关于数据迁移,在之前也讨论过一些需要注意的地方,可能林林总总列了不少,都是在数据迁移迁移前和迁移时需要注意的。http://blog.itpub.net/23718752/viewspace-1195364/http://blog.itpub.net/23718752/viewspace-1254945/ 我在这些帖子的基础上进行更多的总结和补充。
651 0
动态库和静态库
 1.静态库不需要main函数,要想生成lib,要修改属性à常规à配置类型à改成dll 2.静态库中的头文件只是说明,是给程序员看的,其实lib里面已经有了接口。 3.链接lib的时候程序的体积就变大了。 4.静态库更新的时候,需要重新生成,然后调用 5.动态库调用就不用重新生成。 6.动态库的调用代码:(使用的是HMODULELoadLibrary
831 0
+关注
玄学酱
这个时候,玄酱是不是应该说点什么...
20683
文章
438
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载