注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

技术行者

时代的车轮在按照摩尔定律滚动。

 
 
 

日志

 
 

MySQL 数据库性能实时监控  

2012-01-09 16:26:00|  分类: Mysql |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

SHOW STATUS;

FLUSH STATUS;

 

查看当前连接数 SHOW STATUS LIKE 'Thread_%';

Thread_cached:被缓存的线程的个数

Thread_running:处于激活状态的线程的个数

Thread_connected:当前连接的线程的个数

Thread_created:总共被创建的线程的个数

 

Thread cache hits 

Thread_connected = SHOW GLOBAL STATUS LIKE 'Thread_created';

Connections = SHOW GLOBAL STATUS LIKE 'Connections';

TCH=(1 - ( Connections/ Threads_created)) * 100

 

查看活动连接内容

SHOW PROCESSLIST;

 

如果 TCH数小于90%,创建连接耗费了时间,增大Thread_cached数量

 

QPS

Questions = SHOW GLOBAL STATUS LIKE 'Questions';

Uptime = SHOW GLOBAL STATUS LIKE 'Uptime';

QPS=Questions/Uptime 

 

TPS

Com_commit = SHOW GLOBAL STATUS LIKE 'Com_commit';

Com_rollback = SHOW GLOBAL STATUS LIKE 'Com_rollback';

Uptime = SHOW GLOBAL STATUS LIKE 'Uptime';

TPS=(Com_commit + Com_rollback)/Uptime

 

QPS 和 TPS值一定要实时监控,如果接近架构搭建时的测试峰值,愿上帝与你同在

 

Read/Writes Ratio

Qcache_hits = SHOW GLOBAL STATUS LIKE 'Qcache_hits';

Com_select = SHOW GLOBAL STATUS LIKE 'Com_select';

Com_insert = SHOW GLOBAL STATUS LIKE 'Com_insert';

Com_update = SHOW GLOBAL STATUS LIKE 'Com_update';

Com_delete = SHOW GLOBAL STATUS LIKE 'Com_delete';

Com_replace = SHOW GLOBAL STATUS LIKE 'Com_replace';

R/W=(Com_select + Qcache_hits) / (Com_insert + Com_update + Com_delete + Com_replace) * 100

 

读写比,优化数据库的重要依据,读的多就去优化读,写的多就去优化写

 

Slow queries per minute

Slow_queries = SHOW GLOBAL STATUS LIKE 'Slow_queries';

Uptime = SHOW GLOBAL STATUS LIKE 'Uptime';

SQPM=Slow_queries / (Uptime/60)

 

Slow queries /Questions Ratio

Slow_queries = SHOW GLOBAL STATUS LIKE 'Slow_queries';

Questions = SHOW GLOBAL STATUS LIKE 'Questions';

S/Q=Slow_queries/Questions 

 

新版本上线时要着重关注慢查询,让测试去踢开发者的屁股吧

 

Full_join per minute

Select_full_join = SHOW GLOBAL STATUS LIKE 'Select_full_join';

Uptime = SHOW GLOBAL STATUS LIKE 'Uptime';

FJPM=Select_full_join / (Uptime/60)

 

没有使用索引而造成的full_join,优化索引去吧

 

Innodb buffer read hits

Innodb_buffer_pool_reads = SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';

Innodb_buffer_pool_read_requests = SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';

IFRH=(1 - Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100

 

InnoDB Buffer命中率 目标 95%-99%;

 

Table Cache

Open_tables= SHOW GLOBAL STATUS LIKE 'Open_tables';

Opened_tables= SHOW GLOBAL STATUS LIKE 'Opened_tables';

table_cache= SHOW GLOBAL STATUS LIKE 'table_cache';

 

table_cache应该大于 Open_tables 小于 Opened_tables 

 

Temp tables to Disk ratio

Created_tmp_tables = show global status like 'Created_tmp_tables';

Created_tmp_disk_tables = show global status like 'Created_tmp_disk_tables';

TDR=(Created_tmp_disk_tables/Created_tmp_tables)*100

 

 

 

SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_%';

Innodb_row_lock_current_waits

The number of row locks currently being waited for. Added in MySQL 5.0.3.

Innodb_row_lock_time

The total time spent in acquiring row locks, in milliseconds. Added in MySQL 5.0.3.

Innodb_row_lock_time_avg

The average time to acquire a row lock, in milliseconds. Added in MySQL 5.0.3.

Innodb_row_lock_time_max

The maximum time to acquire a row lock, in milliseconds. Added in MySQL 5.0.3.

Innodb_row_lock_waits

The number of times a row lock had to be waited for. Added in MySQL 5.0.3.

  评论这张
 
阅读(395)| 评论(2)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017