印刻数据库

外观

MySQL 存储引擎指标 ​

InnoDB 存储引擎指标 ​

缓冲池指标 ​

缓冲池命中率 ​

sql
-- 计算缓冲池命中率
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

关键指标：

  • Innodb_buffer_pool_read_requests：缓冲池读取请求数
  • Innodb_buffer_pool_reads：从磁盘读取的次数
  • Innodb_buffer_pool_read_ahead：预读次数
  • Innodb_buffer_pool_read_ahead_evicted：被驱逐的预读页数

缓冲池使用情况 ​

sql
-- 查看缓冲池使用情况
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages%';

关键指标：

  • Innodb_buffer_pool_pages_total：缓冲池总页数
  • Innodb_buffer_pool_pages_free：空闲页数
  • Innodb_buffer_pool_pages_data：包含数据的页数
  • Innodb_buffer_pool_pages_dirty：脏页数
  • Innodb_buffer_pool_pages_misc：其他用途的页数

事务指标 ​

sql
-- 查看事务相关指标
SHOW GLOBAL STATUS LIKE 'Innodb_transaction%';
SHOW GLOBAL STATUS LIKE 'Innodb_row%';

关键指标：

  • Innodb_transactions_active：当前活跃事务数
  • Innodb_transactions_committed：已提交事务数
  • Innodb_transactions_rolled_back：已回滚事务数
  • Innodb_row_reads：读取的行数
  • Innodb_row_inserts：插入的行数
  • Innodb_row_updates：更新的行数
  • Innodb_row_deletes：删除的行数

锁指标 ​

sql
-- 查看锁相关指标
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';

关键指标：

  • Innodb_row_lock_current_waits：当前等待锁的数量
  • Innodb_row_lock_waits：锁等待总次数
  • Innodb_row_lock_time：锁等待总时间
  • Innodb_row_lock_time_avg：平均锁等待时间
  • Innodb_row_lock_time_max：最长锁等待时间

日志指标 ​

sql
-- 查看日志相关指标
SHOW GLOBAL STATUS LIKE 'Innodb_log%';

关键指标：

  • Innodb_log_waits：日志缓冲区等待数
  • Innodb_log_write_requests：日志写入请求数
  • Innodb_log_writes：实际日志写入次数
  • Innodb_log_bytes_written：写入的日志字节数
  • Innodb_log_compressed_pages_written：压缩后写入的页数

表空间指标 ​

sql
-- 查看表空间相关指标
SHOW GLOBAL STATUS LIKE 'Innodb_data%';

关键指标：

  • Innodb_data_read：读取的数据字节数
  • Innodb_data_written：写入的数据字节数
  • Innodb_data_reads：数据读取次数
  • Innodb_data_writes：数据写入次数
  • Innodb_data_fsyncs：fsync操作次数

自适应哈希索引指标 ​

sql
-- 查看自适应哈希索引指标
SHOW GLOBAL STATUS LIKE 'Innodb_adaptive_hash%';

关键指标：

  • Innodb_adaptive_hash_searches：哈希索引搜索次数
  • Innodb_adaptive_hash_hash_searches：哈希搜索次数
  • Innodb_adaptive_hash_non_hash_searches：非哈希搜索次数

MyISAM 存储引擎指标 ​

sql
-- 查看MyISAM相关指标
SHOW GLOBAL STATUS LIKE 'Key%';
SHOW GLOBAL STATUS LIKE 'Handler%';

关键指标：

  • Key_blocks_used：使用的键块数
  • Key_blocks_unused：未使用的键块数
  • Key_blocks_not_flushed：未刷新到磁盘的键块数
  • Key_read_requests：键读取请求数
  • Key_reads：从磁盘读取键的次数
  • Key_write_requests：键写入请求数
  • Key_writes：键写入磁盘的次数
  • Handler_read_first：读取索引第一个条目的次数
  • Handler_read_key：通过键读取行的次数
  • Handler_read_next：通过键读取下一行的次数
  • Handler_read_prev：通过键读取前一行的次数
  • Handler_read_rnd：通过随机位置读取行的次数
  • Handler_read_rnd_next：读取下一行的次数
  • Handler_write：插入行的次数
  • Handler_update：更新行的次数
  • Handler_delete：删除行的次数

Memory 存储引擎指标 ​

sql
-- 查看Memory存储引擎相关指标
SHOW GLOBAL STATUS LIKE 'Created_tmp%';

关键指标：

  • Created_tmp_disk_tables：在磁盘上创建的临时表数量
  • Created_tmp_tables：创建的临时表总数
  • Created_tmp_files：创建的临时文件数量

存储引擎监控工具 ​

Performance Schema ​

sql
-- 启用Performance Schema
SET GLOBAL performance_schema = 1;

-- 查看存储引擎相关的性能数据
SELECT * FROM performance_schema.file_instances WHERE file_name LIKE '%.ibd' OR file_name LIKE '%.frm';
SELECT * FROM performance_schema.events_waits_current WHERE event_name LIKE 'wait/io/file/innodb%';

Sys Schema ​

sql
-- 使用Sys Schema查看存储引擎状态
SELECT * FROM sys.innodb_buffer_pool_stats\G;
SELECT * FROM sys.innodb_lock_waits\G;
SELECT * FROM sys.schema_table_lock_waits\G;

第三方监控工具 ​

Prometheus + Grafana ​

  • Exporter：使用mysql_exporter收集MySQL指标
  • Dashboard：使用预配置的MySQL仪表盘监控存储引擎指标
  • 告警：设置基于存储引擎指标的告警规则

Zabbix ​

  • 模板：使用MySQL模板监控存储引擎关键指标
  • 监控项：配置InnoDB缓冲池、锁等待等监控项
  • 触发器：设置基于阈值的触发器

Nagios ​

  • 插件：使用check_mysql_health插件监控存储引擎状态
  • 命令：配置自定义命令监控特定存储引擎指标
  • 服务：创建服务检查存储引擎健康状态

存储引擎指标告警阈值 ​

InnoDB 告警阈值 ​

指标告警阈值严重程度
缓冲池命中率< 95%警告
缓冲池命中率< 90%严重
活跃事务数> 100警告
活跃事务数> 500严重
锁等待时间> 1000ms警告
锁等待时间> 5000ms严重
脏页比例> 75%警告
脏页比例> 90%严重

MyISAM 告警阈值 ​

指标告警阈值严重程度
键缓存命中率< 90%警告
键缓存命中率< 80%严重
表锁等待> 10警告
表锁等待> 50严重

存储引擎指标优化建议 ​

InnoDB 优化 ​

  1. 缓冲池优化

    • 调整 innodb_buffer_pool_size 为服务器内存的 50-80%
    • 对于大内存服务器，使用多个缓冲池实例：innodb_buffer_pool_instances
    • 启用缓冲池预读：innodb_read_ahead_threshold

  2. 日志优化

    • 调整 innodb_log_file_size 以减少日志切换频率
    • 设置适当的 innodb_log_buffer_size 减少磁盘I/O

  3. 并发优化

    • 调整 innodb_thread_concurrency 控制并发线程数
    • 启用 innodb_adaptive_hash_index 提高查询性能
    • 设置 innodb_old_blocks_time 改善缓冲池使用效率

MyISAM 优化 ​

  1. 键缓存优化

    • 调整 key_buffer_size 为服务器内存的 20-30%
    • 使用 myisam_max_sort_file_size 控制排序文件大小

  2. 并发优化

    • 对于只读表，使用 myisam_read_buffer_size 提高读取性能
    • 对于写操作，使用 myisam_write_buffer_size 提高写入性能

Memory 存储引擎优化 ​

  1. 内存使用优化

    • 限制 max_heap_table_size 防止内存过度使用
    • 合理设置 tmp_table_size 控制临时表大小

  2. 性能优化

    • 对于频繁使用的临时表，考虑使用 Memory 存储引擎
    • 避免在 Memory 表中存储大字段

常见问题（FAQ） ​

Q1: 如何监控 InnoDB 缓冲池的使用情况？ ​

A1: 可以使用以下命令监控 InnoDB 缓冲池的使用情况：

sql
-- 查看缓冲池状态
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages%';

-- 计算缓冲池使用率
SELECT 
  (1 - (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_free') / 
   (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total')) * 100 AS buffer_pool_usage_percent;

Q2: 如何识别 MyISAM 表的性能问题？ ​

A2: 可以通过以下指标识别 MyISAM 表的性能问题：

  • 键缓存命中率：如果键缓存命中率低于 90%，考虑增加 key_buffer_size
  • 表锁等待：如果 Table_locks_waited 与 Table_locks_immediate 的比例过高，说明存在表锁竞争
  • 全表扫描：如果 Handler_read_rnd_next 值过高，说明存在大量全表扫描

Q3: 如何监控存储引擎的 I/O 性能？ ​

A3: 可以使用以下方法监控存储引擎的 I/O 性能：

  1. 使用 Performance Schema：

    sql
    SELECT event_name, count_star, sum_timer_wait, avg_timer_wait
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE event_name LIKE 'wait/io/file/%' 
ORDER BY sum_timer_wait DESC;

  2. 使用 iostat 命令：

    bash
    iostat -x -d 1

  3. 使用 MySQL 状态变量：

    sql
    SHOW GLOBAL STATUS LIKE 'Innodb_data%';
SHOW GLOBAL STATUS LIKE 'Innodb_log%';

Q4: 如何优化 InnoDB 的锁等待问题？ ​

A4: 可以通过以下方法优化 InnoDB 的锁等待问题：

  1. 分析锁等待：

    sql
    SELECT * FROM sys.innodb_lock_waits\G;

  2. 优化 SQL 语句：

    • 减少事务持有的锁时间
    • 使用更精确的 WHERE 条件
    • 避免长时间运行的事务

  3. 调整 InnoDB 参数：

    • innodb_lock_wait_timeout：调整锁等待超时时间
    • innodb_rollback_on_timeout：设置超时后是否回滚事务
    • innodb_deadlock_detect：对于高并发场景，考虑关闭死锁检测

Q5: 如何监控存储引擎的磁盘空间使用情况？ ​

A5: 可以使用以下方法监控存储引擎的磁盘空间使用情况：

  1. 查看表空间大小：

    sql
    SELECT table_schema, table_name, engine, 
       ROUND(data_length / 1024 / 1024, 2) AS data_mb, 
       ROUND(index_length / 1024 / 1024, 2) AS index_mb, 
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb 
FROM information_schema.tables 
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys') 
ORDER BY total_mb DESC;

  2. 监控 InnoDB 表空间：

    sql
    SELECT file_name, 
       ROUND(file_size / 1024 / 1024, 2) AS size_mb, 
       ROUND(tablespace_name) AS tablespace 
FROM information_schema.files 
WHERE tablespace_name IS NOT NULL;

  3. 使用系统命令：

    bash
    df -h
du -sh /path/to/mysql/data/*