如何监控mysql性能

发布时间：2021-12-28 16:06:17 所属栏目：MySql教程来源：互联网

导读：这篇文章主要介绍了如何监控mysql性能，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。查看mysql服务器静态参数： show variables; 查看mysql动态运行信息： show status; 一、查询用

这篇文章主要介绍了如何监控mysql性能，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。

查看mysql服务器静态参数：

show variables;

查看mysql动态运行信息：

show status;

一、查询用户的连接数

--show processlist;只列出前100条，如果想全列出请使用show full processlist;

命令： show processlist;

如果是root帐号，你能看到所有用户的当前连接。如果是其它普通帐号，只能看到自己占用的连接。用 mysql -uroot -p123456 来登录（注意：用户名和密码不用包含“”）

二、查看数据库运行状态

命令：show global status;

命令：show status;

命令：show status like '%下面变量%';

Aborted_clients 由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。

Aborted_connects 尝试已经失败的MySQL服务器的连接的次数。

Connections 试图连接MySQL服务器的次数。

Created_tmp_tables 当执行语句时，已经被创造了的隐含临时表的数量。

Delayed_insert_threads 正在使用的延迟插入处理器线程的数量。

Delayed_writes 用INSERT DELAYED写入的行数。

Delayed_errors 用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。

Flush_commands 执行FLUSH命令的次数。

Handler_delete 请求从一张表中删除行的次数。

Handler_read_first 请求读入表中第一行的次数。

Handler_read_key 请求数字基于键读行。

Handler_read_next 请求读入基于一个键的一行的次数。

Handler_read_rnd 请求读入基于一个固定位置的一行的次数。

Handler_update 请求更新表中一行的次数。

Handler_write 请求向表中插入一行的次数。

Key_blocks_used 用于关键字缓存的块的数量。

Key_read_requests 请求从缓存读入一个键值的次数。

Key_reads 从磁盘物理读入一个键值的次数。

Key_write_requests 请求将一个关键字块写入缓存次数。

Key_writes 将一个键值块物理写入磁盘的次数。

Max_used_connections 同时使用的连接的最大数目。

Not_flushed_key_blocks 在键缓存中已经改变但是还没被清空到磁盘上的键块。

Not_flushed_delayed_rows 在INSERT DELAY队列中等待写入的行的数量。

Open_tables 打开表的数量。

Open_files 打开文件的数量。

Open_streams 打开流的数量(主要用于日志记载）

Opened_tables 已经打开的表的数量。

Questions 发往服务器的查询的数量。

Slow_queries 要花超过long_query_time时间的查询数量。

Threads_connected 当前打开的连接的数量。

Threads_running 不在睡眠的线程数量。

Uptime 服务器工作了多少秒。

注释：

如果Opened_tables太大，那么你的table_open_cache变量可能太小。

如果key_reads太大，那么你的key_cache可能太小。缓存命中率可以用key_reads/key_read_requests计算。

如果Handler_read_rnd太大，那么你很可能有大量的查询需要MySQL扫描整个表或你有没正确使用键值的联结(join)。

可以根据“show status”命令返回的状态进行微调。主要注意以下变量的数值，越小越好，最好为零：

  Created_tmp_disk_tables

  Created_tmp_tables

  Created_tmp_files

  Slow_queries

三、数据库性能等查询

1. Show status命令了解各种SQL的执行频率

--查看当前会话执行的各项命令统计 com_XXX

show session status like 'Com_%' 其中session可省

show status like 'Com_%'

--显示全局统计使用

SHOW GLOBAL STATUS LIKE 'COM_%';

2. 针对InnoDB存储引擎状态的统计

SHOW GLOBAL STATUS LIKE 'Innodb_%'

3. 查看试图连接mySQL服务器的次数

show global status like 'connections';

4. 查看服务器工作时间

show global status like 'uptime';

5.查看所在的数据库名

（1）用select database()语句;

select database();

（2）用show tables语句，查询出来的结果中，第一行为Tables_in_***，这里***就是当前所在的数据库名称。

show tables;

（3）用status语句，查询出来的结果中有一行是currrent database:***。这里***就是当前所在的数据库名称。

status;

6.查看表的存储格式信息

show table status like '%test%'G;

--显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间

show table status;

--显示当前数据库中所有表的名称

show tables或show tables from database_name;

7.查看告警信息

show warningsG;

8.查询表结构信息

show create table testG;

9.查询当前架构(schema)下的基表

select * from information_schema.tables where table_type='BASE TABLE' and table_schema=database()G;

10.查询当前架构(schema)下的视图信息

select * from information_schema.views where table_schema=database()G';

11.查询数据库是否启用了分区功能

show variables like '%partition%'G;

show pluginsG;

12.显示索引信息

show index from TABLE_NAMEG

show keys from TABLE_NAME;

13.查询当前架构(schema)下的分区信息

select * from information_schema.PARTITIONS where table_schema=database() and table_name='t3'G;

14.查询当前架构(schema)下的表

select * from information_schema.tables where table_name='t' and table_schema=database()G;

15.显示mysql如何使用索引来处理select语句以及连接表

如何监控mysql性能

https://www.cnblogs.com/yycc/p/7338894.html

explain sqlG;

explain partitions sqlG;

17.根据已知表创建新表的结构

create table e2 like e;

18.将已知分区表的结构变为普通表

alter table e2 remove partitioning;

19.交换分区

alter table e exchange partition p0 with table e2;

20.查看innodb存储引擎中的latch

show engine innodb mutex;

21.查看innodb存储引擎中的当前锁引擎信息

show engine innodb statusG;

show full processlist;

select * from information_schema.innodb_trxG;

select * from information_schema.innodb_locksG;

select * from information_schema.innodb_lock_waitsG;

--锁联合查询，如下：

select r.trx_id                waiting_trx_id,

       r.trx_requested_lock_id waiting_requested_lock_id,

       r.trx_mysql_thread_id   waiting_thread,

       r.trx_query             waiting_query,

       r.trx_state             waiting_status,

       b.trx_id                blocking_trx_id,

       b.trx_mysql_thread_id   blocking_thread,

       b.trx_query             blocking_query,

       b.trx_state             blocking_status

  from information_schema.innodb_lock_waits w,

       information_schema.innodb_trx        b,

       information_schema.innodb_trx        r

where b.trx_id = w.blocking_trx_id

   and r.trx_id = w.requesting_trx_idG;

22.mysql 显示开启事物与关闭事物

    开启事物： start transaction 或者 begin

    关闭事物：commit---成功与 rollback--回滚；

23.mysql修改事务隔离级别

用户可以用SET TRANSACTION语句改变单个会话或者所有新进连接的隔离级别。它的语法如下：

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED |

REPEATABLE READ | SERIALIZABLE}

注意：默认的行为（不带session和global）是为下一个（未开始）事务设置隔离级别。如果你使用GLOBAL关键字，

语句在全局对从那点开始创建的所有新连接（除了不存在的连接）设置默认事务级别。你需要SUPER权限来做这个。

使用SESSION 关键字为将来在当前连接上执行的事务设置默认事务级别。任何客户端都能自由改变会话隔离级别（甚至

在事务的中间），或者为下一个事务设置隔离级别。

你可以用下列语句查询全局和会话事务隔离级别：

SELECT @@global.tx_isolation;

SELECT @@session.tx_isolation;

SELECT @@tx_isolation;

24.SHOW COLUMNS显示有关给定表中列的信息。它也适用于视图。 SHOW COLUMNS仅显示您具有某些权限的列的信息。

-- 显示表中列名称

show columns from table_name from database_name; 或show columns from database_name.table_name;

SHOW [FULL] {COLUMNS | FIELDS}

    {FROM | IN} tbl_name

    [{FROM | IN} db_name]

    [LIKE 'pattern' | WHERE expr]

25. QPS(每秒Query量)

QPS = Questions(or Queries) / seconds

mysql > show global status like 'Question%';

26. TPS(每秒事务量)

TPS = (Com_commit + Com_rollback) / seconds

mysql > show global status like 'Com_commit';

mysql > show global status like 'Com_rollback';

27. key Buffer 命中率

mysql>show global status like 'key%';

key_buffer_read_hits = (1-key_reads / key_read_requests) * 100%

key_buffer_write_hits = (1-key_writes / key_write_requests) * 100%

28. InnoDB Buffer命中率

mysql> show status like 'innodb_buffer_pool_read%';

innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100%

29. Query Cache命中率

mysql> show status like 'Qcache%';

Query_cache_hits = (Qcahce_hits / (Qcache_hits + Qcache_inserts )) * 100%;

30. Table Cache状态量

mysql> show global status like 'open%';

比较 open_tables 与 opend_tables 值

31. Thread Cache 命中率

mysql> show global status like 'Thread%';

mysql> show global status like 'Connections';

Thread_cache_hits = (1 - Threads_created / connections ) * 100%

32. 锁定状态

mysql> show global status like '%lock%';

Table_locks_waited/Table_locks_immediate=0.3% 如果这个比值比较大的话，说明表锁造成的阻塞比较严重

Innodb_row_lock_waits innodb行锁，太大可能是间隙锁造成的

33. 复制延时量

mysql > show slave status

查看延时时间

34. Tmp Table 状况(临时表状况)

mysql > show status like 'Create_tmp%';

Created_tmp_disk_tables/Created_tmp_tables比值最好不要超过10%，如果Created_tmp_tables值比较大，

可能是排序句子过多或者是连接句子不够优化

35. Binlog Cache 使用状况

mysql > show status like 'Binlog_cache%';

如果Binlog_cache_disk_use值不为0 ，可能需要调大 binlog_cache_size大小

36. Innodb_log_waits 量

mysql > show status like 'innodb_log_waits';

Innodb_log_waits值不等于0的话，表明 innodb log buffer 因为空间不足而等待

比如命令：

>#show global status;

虽然可以使用：

>#show global status like %...%;

来过滤，但是对应长长的list，每一项都代表什么意思，还是有必要弄清楚。

37.查看是否启动了分布式事物XA(默认是ON):

show variables like '%innodb_support_xa%';

38.MySQL XA事务基本语法

XA {START|BEGIN} xid [JOIN|RESUME] 启动xid事务 (xid 必须是一个唯一值; 不支持[JOIN|RESUME]子句)

XA END xid [SUSPEND [FOR MIGRATE]] 结束xid事务 ( 不支持[SUSPEND [FOR MIGRATE]] 子句)

XA PREPARE xid 准备、预提交xid事务

XA COMMIT xid [ONE PHASE] 提交xid事务

XA ROLLBACK xid 回滚xid事务

XA RECOVER 查看处于PREPARE 阶段的所有事务

39.判断数据库内存是否已经达到瓶颈

show global status like '%innodb%read%'G;

40.查看索引使用情况

show status like '%Handler_read%';

41.查看innodb存储引擎上行锁的争用情况：

show status like '%innodb_row_lock%';

如果发现锁争用比较严重，则Innodb_row_lock_time_avg和Innodb_row_lock_waits比较高。

另外就是设置innodbDB Monitors进一步发生锁冲突的表、数据行等，并分析锁争用的原因！

注意：innodb行锁是通过给索引上的索引项加锁实现的。这意味着：只有通过索引条件检索数据，innodb才使用行级锁，

否则，innodb将使用表锁！

42.查看mysql服务器参数的详细定义

mysqld --verbose --help|more

比如想要知道当前数据库字符集的设置，如下：

mysqld --verbose --help|grep character-set-server

43.二进制日志

查看二进制日志状态

show variables like '%log_bin%';

查看当前服务器所有的二进制日志文件

show binary logs;

show master logs;

查看当前二进制日志文件状态

show master status;

切换二进制日志

flush logs;

44.查询mysql数据库中的当前时间

select now();

45.在mysql数据库中查询操作系统当前的时间

system date;

46.查看数据库字符集查看MYSQL数据库服务器和数据库字符集

show variables like '%character%';

查看MYSQL所支持的字符集

show charset;

查看库的字符集

show create database test72G;

查看表的字符集

show table status from test72 like 'zs'G;

查看表中所有列的字符集

show full columns from 表名;

show full columns from zsG;

47.显示mysql中所有数据库的名称

show databases;

48.显示一个用户的权限，显示结果类似于grant 命令

show grants for user_name;

49.显示服务器所支持的不同权限

show privileges;

50.显示create database 语句是否能够创建指定的数据库

show create database database_name;

51.显示安装以后可用的存储引擎和默认引擎

show engines;

52.显示innoDB存储引擎的状态

show engine innodb statusG;

53.统计每个库大小

select TABLE_SCHEMA,SUM(DATA_LENGTH)/1024/1024/1024 as data_length,SUM(INDEX_LENGTH)/1024/1024/10

24 as index_length,SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024/1024 as sum_data_index from informatio

n_schema.tables where TABLE_SCHEMA!='information_schema' and TABLE_SCHEMA!='mysql' group by TABL

E_SCHEMA;

54.统计库中每个表大小，此以test库为例

select TABLE_NAME,DATA_LENGTH,INDEX_LENGTH,SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024/102

4 as TOTAL_SIZE from information_schema.tables where TABLE_SCHEMA='test' group by TABLE_SCHEMA;

55.统计所有数据库大小

select SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024/1024 as sum_data_index from information_schema.tab

les;

感谢你能够认真阅读完这篇文章，希望小编分享的“如何监控mysql性能”这篇文章对大家有帮助。

（编辑：云计算网_泰州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

MySQL之日志管制	如何使用mysql索引
Anemometer图形化显示	mysql数据库explain命