本文详述了如何利用MySQL自带命令、数据库GUI管理工具、第三方工具等方法，对MySQL实例状态、复制过程、存储引擎及性能进行全方位监控与优化。通过分析日志文件、使用mysar、mytop等工具，实现对MySQL资源、SQL执行情况、日志监控，以及进行性能分析和压力测试。此外，介绍了如何借助MySQLbenchmarksuite对MySQL进行压力测试，并通过EXPLAIN分析SQL执行细节，以提升数据库性能。
一 对MySQL实例所在的服务器状态进行监控
二 对MySQL实例进行监控
1.使用mysqladmin命令监控MySQL实例状态
mysqladmin是MySQL自带的一个数据库管理命令，可以用来查看当前数据库的配置和状态信息
例1.查看MySQL实例是否存活
$ mysqladmin -h127.0.0.1 -uroot -p ping
Enter password:
mysqld is alive例2.查看活动地服务器进程
$ mysqladmin -h127.0.0.1 -uroot -p -v processlist
Enter password:
+---------+-------------+-----------------+--------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------+-------------+-----------------+--------+---------+------+-------+-----------------------+
| 965138 | root | localhost | zabbix | Sleep | 181 | | |
| 965146 | root | localhost | zabbix | Sleep | 2 | | |
| 965148 | root | localhost | zabbix | Sleep | 3 | | |
| 965149 | root | localhost | zabbix | Sleep | 1 | | | 例3.查看服务器状态信息
$ mysqladmin -h127.0.0.1 -uroot -p -v status
Enter password:
Uptime: 5756530 Threads: 96 Questions: 325496998 Slow queries: 40 Opens: 3228309 Flush tables: 1 Open tables: 64 Queries per second avg: 56.5432.使用数据库自带命令
show status; 查看本地状态变量的值
show session status; 查看当前连接的状态变量的值
show global status; 查看全局状态变量的值
这些状态变量的值是可以更改的
可以使用
set [global | session ] <variable_name> = <value>;
或者
set [@@global. | @@session. | @@] <variable_name> = <value>;
show index from <table> 查看指定表的索引信息
show plugins; 查看插件和状态，存储引擎也当作插件被列出
show processlist; 查看MySQL的线程状态信息
show full processlist;
show table status; 查看数据库中表的详细信息
show global status like '%log%'; 使用like匹配查询
show engine <engine_name> status; 查看指定存储引擎的状态
show engine <engine_name> logs; 查看指定存储引擎的日志信息
show engines; 查看MySQL支持的存储引擎
show binlog events [ in '<log_file>' ] [from <pos>] [limit [<offset>,] <row count>]
显示二进制日志binary log中记录的事件。默认输出当前二进制日志文件信息。
show binlog events\G;
show binary logs; 列出服务器上的二进制文件，可以显示每个二进制文件的大小，单位为字节
show mater logs;
mysql> show binary logs;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master-bin.000001 | 446 |
| master-bin.000002 | 126 |
| master-bin.000003 | 467608268 |
+-------------------+-----------+
3 rows in set (0.00 sec)
mysql> show master logs;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master-bin.000001 | 446 |
| master-bin.000002 | 126 |
| master-bin.000003 | 467608268 |
+-------------------+-----------+
3 rows in set (0.00 sec)show relaylog events [ in '<log_file>'] [from <pos>] [limit [<offset>,] <row count>]
从MySQL5.5开始支持。只在Slave端显示relay logs。
show master status; 查看Server端的当前配置，显示当前的二进制日志文件，二进制日志文件当前的位置
mysql> show master status;
+-------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+-----------+--------------+------------------+
| master-bin.000003 | 467608493 | | |
+-------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)show slave hosts; 显示连接到Master的Slave
mysql> show slave hosts;
+-----------+------+------+-----------+
| Server_id | Host | Port | Master_id |
+-----------+------+------+-----------+
| 2 | | 3306 | 1 |
+-----------+------+------+-----------+
1 row in set (0.00 sec)show slave status\G 显示Slave的复制状态信息
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.41.7
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 467608493
Relay_Log_File: slave-relay-bin.000008
Relay_Log_Pos: 467608640
Relay_Master_Log_File: master-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 467608493
Relay_Log_Space: 467608796
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)MySQL的变量很多，默认是按照字母顺序排列，但是也有例外，如果想要查看特定含有特定关键字的变量可以用like
mysql> show global variables like '%thread%';
+-----------------------------------------+---------------------------+
| Variable_name | Value |
+-----------------------------------------+---------------------------+
| innodb_purge_threads | 0 |
| innodb_read_io_threads | 4 |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 10000 |
| innodb_write_io_threads | 4 |
| max_delayed_threads | 20 |
| max_insert_delayed_threads | 20 |
| myisam_repair_threads | 1 |
| performance_schema_max_thread_classes | 50 |
| performance_schema_max_thread_instances | 1000 |
| thread_cache_size | 64 |
| thread_concurrency | 10 |
| thread_handling | one-thread-per-connection |
| thread_stack | 262144 |
+-----------------------------------------+---------------------------+
14 rows in set (0.00 sec)3.使用MySQL GUI管理工具
使用MySQL Administrator和MySQL Query Browser
4.查看日志文件
MySQL日志相关类型
查询日志
慢查询日志
错误日志
二进制日志
备份日志
在/etc/my.cnf中配置
log
log-slow-queries
long_query_time=10
5.使用第三方工具
mysar , mytop, InnoTop,和MONyog这几个工具可以分析MySQL性能
5.1 mysar
mysar 是一个用Perl语言编写的程序，它将show global status；show global variables;和show full processlist的结果存入到数据库中以备其他工具调用。
可以从 https://launchpad.net/mysar 下载
5.2 mytop
CentOS上可以通过 yum -y install mytop安装
MySQL on localhost (5.5.21-log) up 0+01:13:21 [15:19:19]
Queries: 4.0k qps: 1 Slow: 0.0 Se/In/Up/De(%): 22/23/00/00
qps now: 3 Slow qps: 0.0 Threads: 3 ( 2/ 2) 29/00/00/00
Key Efficiency: 100.0% Bps in/out: 207.5k/207.7k Now in/out: 198.2/ 2.0k
Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
941 root localhost test 0 Query show full processlist
718 root localhost 900 Sleep
15 repl_user 10.10.41.8 4373 Binlog Master has sent all binlog to slave; waiting for binlog t5.3 innotop
innotop is a MySQL and InnoDB transaction/status monitor, like 'top' for
MySQL. It displays queries, InnoDB transactions, lock waits, deadlocks,
foreign key errors, open tables, replication status, buffer information,
row operations, logs, I/O operations, load graph, and more. You can
monitor many servers at once with innotop.
CentOS上通过yum -y install innotop安装
5.4 MONyog
MONyog 是一个可以帮助DBA分析MySQL性能的工具。可以从
https://www.webyog.com/product/monyog 下载
它具有以下特性：
可以对MySQL实例的资源进行监控
可以对SQL语句的执行情况进行监控
可以对MySQL日志进行监控
可以对特定事件进行报警
6.使用MySQL benchmark suite
MySQL benchmark suite有助于对MySQL进行压力测试
进入到MySQL安装目录下如/usr/local/mysql/sql-bench
./run-all-tests --server=mysql --cmp=mysql --user=root --socket=/tmp/mysql.sock
Benchmark DBD suite: 2.15
Date of test: 2014-12-14 12:33:29
Running tests on: Linux 2.6.32-358.el6.i686 i686
Arguments: --socket=/tmp/mysql.sock
Comments:
Limits from: mysql
Server version: MySQL 5.5.21 log
Optimization: None
Hardware:
alter-table:
Total time: 295 wallclock secs ( 0.08 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.10 CPU)
ATIS: Total time: 1499 wallclock secs ( 2.87 usr 0.15 sys + 0.00 cusr 0.00 csys = 3.02 CPU)
big-tables: Total time: 283 wallclock secs ( 1.61 usr 0.14 sys + 0.00 cusr 0.00 csys = 1.75 CPU)
connect: Total time: 60 wallclock secs (16.22 usr 4.73 sys + 0.00 cusr 0.00 csys = 20.95 CPU)
create:MySQL自带一个内置函数benchmark()
mysql> SELECT BENCHMARK(1000000,"SELECT CONCAT('te','s','t')");
+--------------------------------------------------+
| BENCHMARK(1000000,"SELECT CONCAT('te','s','t')") |
+--------------------------------------------------+
| 0 |
+--------------------------------------------------+
1 row in set (0.00 sec)7.使用EXPLAIN分析SQL语法的执行情况
三 对MySQL主从复制进行监控
四 对MySQL的存储引擎进行监控
参考文档
http://dev.mysql.com/doc/refman/5.5/en/index.html
转载于:https://blog.51cto.com/john88wang/1587676
扫一扫
167
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
