Mysql性能评估

问道飞鱼

已于 2024-08-27 23:58:04 修改

阅读量911

点赞数 19

文章标签： mysql 数据库

于 2024-08-13 23:51:58 首次发布

本文链接：https://blog.csdn.net/wendao76/article/details/141144429

版权

MySQL数据库的性能和健康状况可以通过多种不同的指标来评估。这些指标可以帮助您了解数据库的运行状态、性能瓶颈以及潜在的问题。以下是一些常用的MySQL数据库评价指标：

连接数（Connections）:

当前活跃连接数，可以用来判断系统是否接近最大连接数限制。

-- 查看当前连接到 MySQL 服务器的线程总数
SHOW STATUS LIKE 'Threads_connected';
-- 哪些连接正在执行查询
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME > 0;
-- 如果只对正在执行 SQL 语句的连接感兴趣，可以进一步筛选出 STATE 为 null 的记录
SELECT COUNT(*) AS Active_Sessions FROM performance_schema.threads WHERE PROCESSLIST_ID != 'NULL';

查询执行时间（Query Execution Time）:

查询响应时间是衡量数据库性能的关键指标之一。长时间运行的查询可能表明索引或查询优化问题。

-- 查看执行计划
EXPLAIN SELECT * FROM your_table;

测量查询时间

-- 启用查询分析器
SET profiling = 1;
-- 执行您想测量的查询
SELECT * FROM your_table;
-- 查看查询的执行概况
SHOW PROFILES;

慢查询数量（Slow Queries）:

MySQL允许记录执行时间超过指定阈值的查询。慢查询的数量和具体内容有助于识别需要优化的部分。
查看配置情况

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

修改配置文件（my.conf 或 my.ini）

[mysqld]
slow_query_log = ON
slow_query_log_file = /path/to/slow-query.log
long_query_time = 2
log_queries_not_using_indexes = ON

临时修改配置（数据库重启后失效）

SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/path/to/slow-query.log';
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = ON;

表锁等待时间（Table Lock Waits）:

表锁等待时间过长可能意味着应用程序中的锁竞争问题。

-- 显示当前的锁信息
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
-- 显示等待中的锁请求
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

InnoDB缓冲池命中率（InnoDB Buffer Pool Hit Ratio）:
- InnoDB缓冲池用于缓存数据和索引。高的命中率表示缓存效率高；低的命中率可能表明内存配置不足。
  查询 InnoDB 缓冲池的状态变量
```
SHOW STATUS LIKE 'Innodb_buffer_pool_pages%';
-- 表示从缓冲池中请求读取的页数。
SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';
-- 表示从磁盘读取的页数
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';
```
计算命中率
InnoDB 缓冲池命中率 = (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100%

磁盘I/O活动（Disk I/O Activity）:

频繁的磁盘读写操作可能会成为性能瓶颈。通过监控I/O活动，可以确定是否需要调整存储配置或增加硬件资源。

-- 确认 Performance Schema 已经启用
SHOW VARIABLES LIKE 'performance_schema';
-- 查看 I/O 事件的摘要信息
SELECT *
FROM performance_schema.file_events_waits_summary_by_file_name
WHERE file_name LIKE '%.ibd%' OR file_name LIKE '%ibdata1' OR file_name LIKE '%ib_logfile%';
-- 查看当前正在发生的 I/O 等待事件
SELECT *
FROM performance_schema.file_events_waits_current
WHERE file_name LIKE '%.ibd%' OR file_name LIKE '%ibdata1' OR file_name LIKE '%ib_logfile%';
-- 查看历史上的 I/O 等待事件
SELECT *
FROM performance_schema.file_events_waits_history
WHERE file_name LIKE '%.ibd%' OR file_name LIKE '%ibdata1' OR file_name LIKE '%ib_logfile%';

使用操作系统工具

除了 MySQL 内部的监控之外，您还可以使用操作系统的工具来监控磁盘 I/O 活动，例如 iostat（在 Linux 上）或 perfmon（在 Windows 上）。

临时表使用情况（Temporary Tables）:
- 过多使用临时表可能表明查询设计问题或资源限制问题。
```
-- 查看临时表的创建情况
SHOW STATUS LIKE 'Created_tmp_disk_tables';
SHOW STATUS LIKE 'Created_tmp_files';
SHOW STATUS LIKE 'Created_tmp_tables';

-- 查看 Performance Schema 中的临时表信息
SELECT table_name, create_time, engine
FROM performance_schema.tables
WHERE table_name LIKE 'mysql_tmp%';

-- 查看临时表的 I/O 操作
SELECT *
FROM performance_schema.table_io_waits_summary_by_table
WHERE table_name LIKE 'mysql_tmp%';
```
建议优化方案

增加 tmp_table_size 和 max_heap_table_size: 这些变量控制着内存中临时表的最大大小。增加这些值可以允许更多的临时表在内存中创建，从而减少磁盘 I/O。
优化查询: 尽量减少子查询和大表连接，使用索引优化查询。
使用 EXPLAIN 分析: 使用 EXPLAIN 命令来检查查询计划，并优化性能不佳的查询
二进制日志写入速度（Binary Log Write Rate）:
- 用于复制和恢复的数据写入速率，对于高可用性和灾难恢复策略非常重要。
主从复制延迟（Replication Lag）:
- 如果使用MySQL主从复制，则需要监控从服务器与主服务器之间的同步延迟。
- 在从服务器上执行 SHOW SLAVE STATUS\G 命令，可以看到有关复制状态的信息，其中 Seconds_Behind_Master 表示从服务器落后于主服务器的时间（以秒为单位）
- 如果你使用的是 MySQL Workbench 或其他类似的图形界面工具，通常可以直接查看仪表板中的复制延迟。
- MySQL 5.6 及更高版本提供了 Performance Schema，它可以帮助你深入了解复制过程中的性能问题。例如，performance_schema.replication_applier_status_by_worker 表提供了关于复制线程的详细信息。
- 有一些专门用于监控 MySQL 复制的工具，比如 Percona Toolkit 中的 pt-heartbeat 和 pt-slave-delay