mysql监测工具

标签: mysql优化 mysql监测工具 tuningprimer | 发表时间:2012-12-24 11:09 | 作者:Mike.Xu
出处:http://www.dbasky.net/
mysql监测工具tuning-primer.sh

mysql的运算情况,我们可以一步一步的去查看,分析。这样需要一定的时间,今天推荐给大家一个不错的脚本tuning-primer.sh,可以帮助我们去查看一下msyql的运行情况,产生报告和给出一些建议,我们可以根据这些建议,结合mysql服务器的实际情况,对mysql进行优化。
一,下载以及使用
1,wget http://www.day32.com/MySQL/tuning-primer.sh
2,将tuning-primer.sh拷贝到my.cnf的同级目录
3,sh tuning-primer.sh 或者 chmod +x tuning-primer.sh 然后在 ./tuning-primer.sh 二者没什么区别
[[email protected] etc]# sh tuning-primer.sh
Using login values from ~/.my.cnf
- INITIAL LOGIN ATTEMPT FAILED -
Testing for stored webmin passwords:
None Found
Could not auto detect login info!
Found Sockets: /tmp/mysql.sock                 //找到一个mysql.sock
Using: /tmp/mysql.sock                                 //使用这个.sock文件
Would you like to provide a different socket?: [y/N] n       //是否使用其他的socket
Do you have your login handy ? [y/N] : y        //是否手动输入用户名和密码,当然你可以在my.cnf里面设置
User: root
Password:
Would you like me to create a ~/.my.cnf file for you? [y/N] : n     //是不是要帮你在创建一个my.cnf
你也可以把用户名和密码放到my.cnf里面,如下
[client]
user = 用户名
password = 密码
socket = /tmp/mysql.sock
二,运行结果,以及部分分析和建议
我是以本机的mysql为例来进行测试的。
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -
MySQL Version 5.1.26-rc-log i686
Uptime = 0 days 0 hrs 0 min 28 sec
Avg. qps = 0
Total Questions = 15
Threads Connected = 1

Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations
告诉我服务运行还不超过48个小时,这样不安全
To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service
SLOW QUERIES   (慢查询)
The slow query log is enabled.
Current long_query_time = 2.000000 sec.
You have 0 out of 36 that take longer than 2.000000 sec. to complete
Your long_query_time seems to be fine
BINARY UPDATE LOG     (增加备份时的log文件)
The binary update log is enabled
The expire_logs_days is not set.
The mysqld will retain the entire binary log until RESET MASTER or PURGE MASTER LOGS commands are run manually
Setting expire_logs_days will allow you to remove old binary logs automatically
See http://dev.mysql.com/doc/refman/5.1/en/purge-master-logs.html
Binlog sync is not enabled, you could loose binlog records during a server crash
告诉我binary log没有设置生存时间,会一直保存下来,只到主服务器重起,并且binlog的同步并没有激活,如果服务器崩溃binlog数据将丢失
WORKER THREADS  (工作线程)
Current thread_cache_size = 0
Current threads_cached = 0
Current threads_per_sec = 1
Historic threads_per_sec = 0
Your thread_cache_size is fine
MAX CONNECTIONS  (最大连接数)
Current max_connections = 500
Current threads_connected = 1
Historic max_used_connections = 1
The number of used connections is 0% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating
告诉我,目前已连接的最大连接和设置的最大连接的比率差不多是0%,建议我把最大连接设置小一点,可以节省一点内存
No InnoDB Support Enabled!
MEMORY USAGE    (内存使用)
Max Memory Ever Allocated : 17 M
Configured Max Per-thread Buffers : 796 M
Configured Max Global Buffers : 16 M
Configured Max Memory Limit : 812 M
Physical Memory : 1003 M
Max memory limit seem to be within acceptable norms
KEY BUFFER  (KEY缓存)
No key reads?!
Seriously look into using some indexes
Current MyISAM index space = 16 M
Current key_buffer_size = 16 M
Key cache miss rate is 1 : 0
Key buffer free ratio = 88 %
Your key_buffer_size seems to be fine
QUERY CACHE   (查询缓存)
Query cache is supported but not enabled       查询缓存是支持的,但是没有激活
Perhaps you should set the query_cache_size   建议我设置一下query_cache_size的大小
SORT OPERATIONS   (排序操作)
Current sort_buffer_size = 512 K
Current read_rnd_buffer_size = 512 K
No sort operations have been performed
Sort buffer seems to be fine
JOINS   (连接)
Current join_buffer_size = 132.00 K
You have had 0 queries where a join could not use an index properly
Your joins seem to be using indexes properly
OPEN FILES LIMIT  (最大打开文件数)
Current open_files_limit = 2500 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
告诉我,如果大量使用myisam的话,把open_files_limit的大小设置成table_cache大小的2倍-3倍
Your open_files_limit value seems to be fine
TABLE CACHE  (表缓存)
Current table_open_cache = 64 tables
Current table_definition_cache = 256 tables
You have a total of 506 tables
You have 64 open tables.
Current table_cache hit rate is 12%
, while 100% of your table cache is in use
You should probably increase your table_cache
You should probably increase your table_definition_cache value.
告诉我打开了多少文件,table_cache缓存的比率是多少,并建议我增加table_cache和table_definition_cache的值
TEMP TABLES  (临时表)
Current max_heap_table_size = 16 M
Current tmp_table_size = 16 M
Of 141 temp tables, 10% were created on disk
Created disk tmp tables ratio seems fine
TABLE SCANS  (表扫描)
Current read_buffer_size = 256 K
Current table scan ratio = 12 : 1
read_buffer_size seems to be fine
TABLE LOCKING (表锁定)
Current Lock Wait ratio = 0 : 225
Your table locking seems to be fine

相关 [mysql 工具] 推荐:

mysql监测工具

- - DBA Blog
mysql监测工具tuning-primer.sh. mysql的运算情况,我们可以一步一步的去查看,分析. 这样需要一定的时间,今天推荐给大家一个不错的脚本tuning-primer.sh,可以帮助我们去查看一下msyql的运行情况,产生报告和给出一些建议,我们可以根据这些建议,结合mysql服务器的实际情况,对mysql进行优化.

mysql监控工具:zabbix+MPM(Performance Monitor for MySQL)

- - CSDN博客数据库推荐文章
MPM主要用于监控mysql的各种参数性能指标,下面简单说一下他与zabbix的配置:. 下面是它的配置文件关系图. 1、zabbix 模板: Template_FromDual.MySQL.*.xml. 2、MPM agent perl 模块: FromDualMySQL*.pm. 下面是在linux 6.4下安装,先安装如下包:.

協助 MySQL 效能調校的工具

- Michael - SSORC.tw
mysqltuner.pl -- http://rackerhacker.com/mysqltuner/ 或 https://github.com/rackerhacker/MySQLTuner-perl . tuning-primer.sh  -- 不知道是不是正確來源的http://www.day32.com/MySQL/ .

MySQL TPCH测试工具简要手册

- - MySQL 中文网 -
tpch是TPC(Transaction Processing Performance Council)组织提供的工具包. 用于进行OLAP测试,以评估商业分析中决策支持系统(DSS)的性能. 它包含了一整套面向商业的ad-hoc查询和并发数据修改,强调测试的是数据库、平台和I/O性能,关注查询能力.

(转)mysql监控工具箱 common_schema

- - jackyrong
common_schema是一个MySQL schema工具集(5.1以上版本才适用), 它通过在MySQL数据库里建立一个名为common_schema的数据库, 该数据库里包含许多有用的视图和函数, 用来做数据库分析,监控,管理和SQL代码生成, 其中大部分视图信息其实来自INFORMATION_SCHEMA数据库, 只是做了一些连接和汇总.

MySQL自带轻量压测工具mysqlslap

- - 数据库 - ITeye博客
一款MySQL自带的专用轻量压测工具:mysqlslap. --query:测试要执行的sql语句. --number-of-queries:指定测试要执行sql语句执行的请求数. -c,concurrency:指定测试执行请求的并行度. -i,--iterations:指定测试运行的次数. --create-schema:指定此次测试在哪个schema下执行.

MySQL复制的概述、安装、故障、技巧、工具

- jinbiaozhao - 火丁笔记
同MongoDB,Redis这样的NoSQL数据库的复制相比,MySQL复制显得相当复杂. 首先主服务器把数据变化记录到主日志,然后从服务器通过I/O线程读取主服务器上的主日志,并且把它写入到从服务器的中继日志中,接着SQL线程读取中继日志,并且在从服务器上重放,从而实现MySQL复制. 整个过程反映到从服务器上,对应三套日志信息,可在从服务器上用如下命令查看:.

支持MySql的数据库自动分表工具DBShardTools发布

- Xin - 博客园-首页原创精华区
前段时间参与了公司的一个项目,这个项目的特点是数据量、访问量都比较大,考虑使用数据库水平分表策略,Google了大半天,竟然没有找到分表工具. 于是自己写了个数据库水平分表工具,支持MS Sql Server和 MySQL,对MYSQL支持比较好. 下面介绍下分表工具的使用方法. 输入连接名称(也可以点击右边的选择框,从之前保存的连接中选择),选择数据库类型(目前支持Mysql和Sql Server),输入ip地址、数据库名称、用户名、密码后点击测试连接,如果提示连接成功,说明设置正确.

(总结)MySQL自带的性能压力测试工具mysqlslap详解

- - 服务器运维与网站架构|Linux运维|X研究
PS:今天一同事问我有木有比较靠谱的mysql压力测试工具可用. 其实mysql自带就有一个叫mysqlslap的压力测试工具,还是模拟的不错的. mysqlslap是从5.1.4版开始的一个MySQL官方提供的压力测试工具. 通过模拟多个并发客户端访问MySQL来执行压力测试,同时详细的提供了“高负荷攻击MySQL”的数据性能报告.

监听mysql的binlog日志工具分析:canal、Maxwell、Databus、DTS - 程序员大本营

- -
阿里云的数据传输服务DTS. 定位:基于数据库增量日志解析,提供增量数据订阅&消费,目前主要支持了mysql. canal模拟mysql slave的交互协议,伪装自己为mysql slave,向mysql master发送dump协议. mysql master收到dump请求,开始推送binary log给slave(也就是canal).