MySQL Optimize

发表于 | 分类于 ， ，

内存占用分析

查看mysql里的线程，观察是否有长期运行或阻塞的sql

1
show full processlist

疑似mysql连接使用完成后没有真正释放内存

1
show global variables like '%sort_buffer_size%';

http://www.mysqlcalculator.com/ 对比默认值

性能参数查询

MySQL性能监控关注点

QPS（每秒Query 量）：这里的QPS 实际上是指MySQL Server 每秒执行的Query总量：
QPS = Queries / Seconds

TPS（每秒事务量）： 在MySQL Server 中并没有直接事务计数器，我们只能通过回滚和提交计数器来计算出系统的事务量。所以，我们需要通过以下方式来得到客户端应用程序所请求的TPS 值：
TPS = (Com_commit + Com_rollback) / Seconds

Key Buffer 命中率：Key Buffer 命中率代表了MyISAM 类型表的索引的Cache命中率。该命中率的大小将直接影响MyISAM 类型表的读写性能。Key Buffer 命
中率实际上包括读命中率和写命中率两种，MySQL 中并没有直接给出这两个命中率的值，但是可以通过如下方式计算出来：
key_buffer_read_hits = (1 - Key_reads / Key_read_requests) 100%
key_buffer_write_hits= (1 - Key_writes / Key_write_requests) 100%

Innodb Buffer 命中率：这里Innodb Buffer 所指的是innodb_buffer_pool，也就是用来缓存Innodb 类型表的数据和索引的内存空间。类似Key buffer，我们
同样可以根据MySQL Server 提供的相应状态值计算出其命中率：
innodb_buffer_read_hits=(1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100%

Query Cache 命中率：如果我们使用了Query Cache，那么对Query Cache 命中率进行监控也是有必要的，因为他可能告诉我们是否在正确的使用Query Cache。
Query Cache 命中率的计算方式如下：
Query_cache_hits= (Qcache_hits / (Qcache_hits + Qcache_inserts)) * 100%

Table Cache 状态量：Table Cache 的当前状态量可以帮助我们判断系统参数table_open_cache 的设置是否合理。如果状态变量Open_tables 与Opened_tables 之间的比率过低，则代表Table Cache 设置过小：
SHOW STATUS LIKE ‘Open%’;

Thread Cache 命中率：Thread Cache 命中率能够直接反应出我们的系统参数thread_cache_size 设置的是否合理。一个合理的thread_cache_size 参数能够
节约大量创建新连接时所需要消耗的资源。
Thread Cache 命中率计算方式如下：
Thread_cache_hits = (1 - Threads_created / Connections) * 100%

查看引擎

  1. 查看当前引擎
1
show variables like '%engine%'
1
2
3
4
5
default_storage_engine	InnoDB
default_tmp_storage_engine	InnoDB
disabled_storage_engines	
enforce_storage_engine	InnoDB
internal_tmp_disk_storage_engine	InnoDB

事务信息

  1. 事务隔离级别
1
select @@global.tx_isolation, @@session.tx_isolation, @@tx_isolation;

可用：READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE

  1. 查看当前最新事务ID
1
show engine innodb status;

锁事务

  1. 正在锁
1
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
  1. 等待锁
1
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
  1. 查看锁情况
1
2
3
show status like '%lock%'
or
show status like 'innodb_row_lock_%';

  • Innodb_row_lock_current_waits 当前等待锁的数量

  • Innodb_row_lock_time 系统启动到现在，锁定的总时间长度

  • Innodb_row_lock_time_avg 每次平均锁定的时间

  • Innodb_row_lock_time_max 最长一次锁定时间

  • Innodb_row_lock_waits 系统启动到现在总共锁定的次数

Key_blocks_unused 未使用的缓存簇(blocks)数
Key_blocks_used 曾经用到的最大的blocks数
Key_blocks_unused 太小要么增加key_buffer_size，要么就是过渡索引了，把缓存占满了。比较理想的设置：
Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%

区别是否锁表

  1. 当Table_locks_waited与Table_locks_immediate的比值较大，则说明我们的表锁造成的阻塞比较严重。

锁性能

InnoDB加锁的详细信息，主要用于分析一条语句的详细加锁结构以及加了什么锁。

1
2
set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;

查看监控信息，Status字段的监控信息

1
show engine innodb status;

查看其中一条事务信息

1
2
3
4
---TRANSACTION 10701389, ACTIVE 0 sec
4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 161917, OS thread handle 140218872289024, query id 49510107 cloudsqlproxy~35.198.252.157 accounting cleaning up
Trx read view will not see trx with id >= 10701390, sees < 10701369

参考链接:http://www.ywnds.com/?p=11750

参考链接

https://blog.csdn.net/dc666/article/details/78901341