MySQL DBA 刚入职时如何快速拥抱新的环境

浏览数：22 / 时间：2015年06月12日

方法何其多、这里介绍的是懒人做法、也就是、借助工具
但是、生产环境是不能随便安装程序的、肿么办？
没关系、我们伟大的percona 已经为我们考虑周详鸟

这里、我们要借助三个工具：

① pt-summary：绿色纯天然、免安装、下载即用、收集MySQL Server所在OS的基本信息
② pt-mysql-summary：本地安装、然后远程收集MySQL Server的基本信息
③ innotop：将MySQL Server的信息分析的粒度做细、让你深入观察和监控MySQL Server、同样可以远程监控

关于 pt-summary、这个工具几乎把这个服务器的配置从里到外扒了遍、
而且、所显示的参数也非常明了、有兴趣的朋友到官网下个、跑一下就出来

下面重点介绍的是 pt-mysql-summary和innotop

先瞧瞧 pt-mysql-summary

[plain] view plain copy print ?

[mysql@odd ~]$ pt-mysql-summary --user=david --password=123 --host=62.131.164.222
㈠第一部分
# Percona Toolkit MySQL Summary Report #######################
System time | 2013-06-12 08:58:40 UTC (local TZ: CST +0800)
# Instances ##################################################
Port Data Directory Nice OOM Socket
===== ========================== ==== === ======
3306 /home/mysql/mysql/data 0 0 /tmp/mysql.sock
--前面这两部分展示了MySQL 实例的基本概况
--实际上、这里可以列出多个实例、不过、我这里仅有一个
㈡第二部分
# MySQL Executable ###########################################
Path to executable | /home/mysql/mysql/bin/mysqld
Has symbols | Yes
# Report On Port 3306 ########################################
User | david@%
Time | 2013-06-12 16:57:58 (CST)
Hostname | cdio
Version | 5.5.16-log Source distribution
Built On | Linux x86_64
Started | 2013-06-11 15:26 (up 1+01:31:13)
Databases | 6
Datadir | /home/mysql/mysql/data/
Processes | 20 connected, 2 running
Replication | Is not a slave, has 0 slaves connected
Pidfile | /home/mysql/mysql/data/cdio.pid (does not exist)
--这部分是对 MySQL 实例(PS:这里特指3306、因为还可存在其他实例)的基本情况再次做报表、当然、比前面部分来得详细些
--有个参数需要特别注意、Time 指的是MySQL 服务器的当前时间、而不是MySQL 服务器所在的系统的时间
㈢第三部分
# Processlist ################################################
Command COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
Query 1 1 0 0
Sleep 20 0 35000 4000
User COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
david 1 1 0 0
root 9 0 0 0
shaoyun 10 0 0 0
Host COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
110.80.147.20 1 1 0 0
110.84.245.124 6 0 0 0
27.151.68.106 4 0 0 0
localhost 9 0 0 0
db COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
cdio 15 0 0 0
NULL 5 1 0 0
State COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
20 0 0 0
NULL 1 1 0 0
--这部分的内容来自 show processlist 、我们先看看未经过统计的 show processlist的内容、如下：
--
mysql> show processlist;
+------+---------+---------------------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+---------+---------------------+------+---------+------+-------+------------------+
| 1508 | shaoyun | 130.84.225.124:4373 | cdio | Sleep | 3523 | | NULL |
| 1510 | shaoyun | 130.84.225.124:4385 | cdio | Sleep | 2322 | | NULL |
| 1513 | shaoyun | 27.161.68.106:2585 | NULL | Sleep | 7866 | | NULL |
| 1514 | shaoyun | 27.161.68.106:2586 | NULL | Sleep | 7866 | | NULL |
| 1522 | shaoyun | 27.161.68.106:3107 | NULL | Sleep | 6861 | | NULL |
| 1529 | root | localhost | cdio | Sleep | 4426 | | NULL |
| 1557 | root | localhost:59459 | cdio | Sleep | 7 | | NULL |
| 1579 | shaoyun | 165.77.122.42:56966 | cdio | Sleep | 433 | | NULL |
| 1580 | root | localhost | cdio | Sleep | 239 | | NULL |
| 1582 | shaoyun | 27.161.68.126:5640 | cdio | Sleep | 164 | | NULL |
| 1583 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+------+---------+---------------------+------+---------+------+-------+------------------+
--不难看出、这是对每一列都做了统计
㈣第四部分
# Status Counters (Wait 10 Seconds) ##########################
Variable Per day Per second 295 secs
Bytes_received 90000000 1000 4000
Bytes_sent 600000000 7000 22500
Com_admin_commands 1
Handler_rollback 80
Handler_update 6000
Handler_write 20000 5
Innodb_buffer_pool_pages_flushed 2250 1
Innodb_buffer_pool_read_requests 2250000 25 125
Innodb_buffer_pool_reads 800
Innodb_buffer_pool_write_requests 10000 7
Innodb_data_fsyncs 1250
Innodb_data_read 15000000 175 100
Innodb_data_reads 900
Innodb_data_writes 2000 1
Innodb_data_written 40000000 450 25000
Innodb_dblwr_pages_written 1250
Innodb_dblwr_writes 150
Innodb_log_write_requests 1750 1
Innodb_log_writes 600
Innodb_os_log_fsyncs 700
Innodb_os_log_written 1000000 10 600
--这部分内容取自 show global status 、仅收集哪些变化的量
--比较有价值的应该是第三列、取自两张快照、除以变化量、算出每秒的变化
㈤第五部分
# Table cache ################################################
Size | 64
Usage | 100%
--显示表缓存的大小、Usage表示在用百分比、这是个近视值
㈥第六部分
# Plugins ####################################################
InnoDB compression | ACTIVE
--显示指定的插件程序以及它们的状态
㈦第七部分
# Query cache ################################################
query_cache_type | ON
Size | 0.0
Usage | 0%
HitToInsertRatio | 0%
--这部分是关于查询缓存基本情况
㈧第八部分
# Schema #####################################################
Would you like to mysqldump -d the schema and analyze it? y/n y
There are 6 databases. Would you like to dump all, or just one?
Type the name of the database, or press Enter to dump all of them.
Database Tables Views SPs Trigs Funcs FKs Partn
cdio 61 65
jiradb 130 6
mysql 22
test
Database InnoDB MyISAM
cdio 61
jiradb 130
mysql 22
test
Database BTREE
cdio 193
jiradb 274
mysql 31
c t
h i
a m
r e
s
t
a
m
p
Database === ===
cdio 114 155
jiradb 1 346
mysql 8 3
test
--这部分便是 mysqldump --no-data 跑出来的数据
--第一个小部分指明每个库有多少张表、视图、存储过程、触发器、函数、外键约束等
--第二个小部分指明每个库有多少张表使用了不同的存储引擎
--第三个小部分指明每个库有多少索引类型
--第四个小部分指明每个库数据类型的分布情况、可能比较难看、因为列是竖着放、比如第一列是char
㈨第九部分
# Noteworthy Technologies ####################################
Full Text Indexing | No
Geospatial Types | No
Foreign Keys | Yes
Partitioning | No
InnoDB Compression | No
SSL | No
Explicit LOCK TABLES | No
Delayed Insert | No
XA Transactions | No
NDB Cluster | No
Prepared Statements | No
Prepared statement count | 0
--这部分显示了一些关键技术是否被用上、如：全文索引、XA等
(十)第十部分
# InnoDB #####################################################
Version | 1.1.8
Buffer Pool Size | 128.0M
Buffer Pool Fill | 10%
Buffer Pool Dirty | 0%
File Per Table | ON
Page Size | 16k
Log File Size | 2 * 5.0M = 10.0M
Log Buffer Size | 8M
Flush Method |
Flush Log At Commit | 1
XA Support | ON
Checksums | ON
Doublewrite | ON
R/W I/O Threads | 4 4
I/O Capacity | 200
Thread Concurrency | 0
Concurrency Tickets | 500
Commit Concurrency | 0
Txn Isolation Level | REPEATABLE-READ
Adaptive Flushing | ON
Adaptive Checkpoint |
Checkpoint Age | 0
InnoDB Queue | 0 queries inside InnoDB, 0 queries in queue
Oldest Transaction | 0 Seconds
History List Len | 1899
Read Views | 1
Undo Log Entries | 0 transactions, 0 total undo, 0 max undo
Pending I/O Reads | 0 buf pool reads, 0 normal AIO, 0 ibuf AIO, 0 preads
Pending I/O Writes | 0 buf pool (0 LRU, 0 flush list, 0 page); 0 AIO, 0 sync, 0 log IO (0 log, 0 chkp); 0 pwrites
Pending I/O Flushes | 0 buf pool, 0 log
Transaction States | 16xnot started
--这部分显示了InnoDB 的基本配置
--Buffer Pool Fill和Buffer Pool Dirty是近视值
(11)第11部分
# MyISAM #####################################################
Key Cache | 16.0M
Pct Used | 20%
Unflushed | 0%
--这部分是MyISAM的部分
(12)第12部分
# Security ###################################################
Users | 9 users, 1 anon, 4 w/o pw, 9 old pw
Old Passwords | ON
--这部分从 grants table 的mysql表提取出来
--显示了显示了有多少用户以及各种可能的安全配置
(13)第13部分
# Binary Logging #############################################
Binlogs | 12
Zero-Sized | 0
Total Size | 62.5M
binlog_format | MIXED
expire_logs_days | 0
sync_binlog | 0
server_id | 1
binlog_do_db |
binlog_ignore_db |
--这部分显示了二进制日志的配置
(14)第14部分
# Noteworthy Variables #######################################
Auto-Inc Incr/Offset | 1/1
default_storage_engine | InnoDB
flush_time | 0
init_connect |
init_file |
sql_mode |
join_buffer_size | 128k
sort_buffer_size | 512k
read_buffer_size | 256k
read_rnd_buffer_size | 512k
bulk_insert_buffer | 0.00
max_heap_table_size | 16M
tmp_table_size | 16M
max_allowed_packet | 1M
thread_stack | 256k
log | OFF
log_error | /home/mysql/mysql/log/alert.log
log_warnings | 1
log_slow_queries | ON
log_queries_not_using_indexes | OFF
log_slave_updates | OFF
--这部分展示了服务器的几个关键配置
(15)第15部分
# Configuration File #########################################
Config File | /etc/my.cnf
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
basedir = /home/mysql/mysql
datadir = /home/mysql/mysql/data
log-error = /home/mysql/mysql/log/alert.log
log_slow_queries = /home/mysql/mysql/log/slow.log
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
innodb_file_per_table = 1
log-bin = mysql-bin
binlog_format = mixed
server-id = 1
sync_binlog = 1
innodb_support_xa = 1
innodb_flush_log_at_trx_commit = 2
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
# The End ####################################################
--如您所见、这部分完完全全来自 my.cnf、这里不赘述了

其实分析完 pt-mysql-summary 之后、你基本就已经对你即将管理的Server知根知底了
接下来的 Innotop 能给您想知道的部分锦上添花的作用

更多了解请查询：code.google.com/p/innotop

㈠安装

# perl -MCPAN -eshell
CPAN> install Time::HiRes
CPAN> install Term::ReadKey
CPAN> install DBI
CPAN> install DBD::mysql

# wget https://innotop.googlecode.com/files/innotop-1.9.0.tar.gz
# tar -zxv -f innotop-1.9.0.tar.gz
# cd innotop-1.9.0
# perl Makefile.PL
# make install

㈡进入

# innotop -uroot -p123 -h127.0.0.1

㈢使用

进入第一件事情就是按 ? 号、然后innotop就会自个教你如何使用

举个例子、如果你想查询MySQL当前执行的SQL、您可以这么做：
按 Q 进入 Query list模式
然后、按 e并输入thread ID显示执行计划