数据库监控策略浅谈

kider

作者：邵宗文   2009-06-02   

      目前数据库监控的软件很多，如monyog,mon,mtop,cacti，还有强大的nagios等，但是很多数据库管理员会困惑从何处着手去监控，因为如果只是监控一下慢sql数量，com_select等读写操作的数量值，是很难有效的做到前期捕获表设计，以及sql语句问题的。
　　其实要做好一个全面的数据库监控，首先需要知道监控的目的，这样才能有选择性的去部署监控，所谓工欲善其事，必先利其器。
　　以我为例，因为我是做数据库平台的，大概数据库平台上跑了2百多个产品项目，且项目产品属于公司不同的应用部门，因此就会产生一个数据库实例里面跑好几个不同应用部门的项目库，当出现数据库服务器性能瓶颈或是访问变慢时候，需要去排查数据库时候就会产生很多麻烦，比如一个数据库实例出现大量的com_select操作，但是你要知道是哪个部门的哪个应用在使用，可光知道了是哪个部门在使用，并找到了对应的开发人员，但往往随之而来的就是开发人员会问你，是在什么时间，大概那种类型的sql语句导致的，甚至希望你能提供从哪个机器发起的请求等等一些更细节的问题，这个时候如果没有强大的监控策略，就很容易陷入被动，被开发人员给问僵了。
　　另外还有很多时候，为了能缩短项目的数据库使用申批流程时间，需要授权给开发人员比较大的权限如建表，建索引权限，改表字段权限，使自己从陪同开发人员加班的苦海中解放出来，不过随之带来的问题就是，很多sql缺乏索引，或是一个库下面建了上千个表，或是有的表没规划好，跑到千万rows了。
　　针对上述经常遇到的问题，我做的数据库策略就是，必须要对数据库的读写操作sql进行定时采样抓取(mysql 5.1的话能打开query参数，mysql 5.0的话可以用tcpdump抓取)，当出现某个实例有大量频繁读写异常时候，我就能有目的性的查对应的sql日志存档就能进行分析，找到问题的所在。
　　另外对于表的详细监控可以使用google-patch(http://code.google.com/p/google-mysql-tools/)的补丁来分析rows变化，因为当sql没有加合适索引时候，是会进行全表扫描，从而会使rows_read变的特别大。库和表的状况信息可以通过information_schema这个东东进行分析。(后面我会提供sp_dbdata来汇总)

附sp_dbdata的存储过程脚本

1. DELIMITER $$
   
2. DROP PROCEDURE IF EXISTS `mysql`.`sp_dbdata`$$
   
3. create procedure sp_dbdata (IN db_name varchar(100),IN f_limit int)
   
4. BEGIN if db_name <>
   
5. 'ALL' THEN
   
6. set @sqltext=concat('SELECT concat(table_schema,\'.\',table_name),concat(round(table_rows/1000000,2),\'M\') rows,concat(round(data_length/(1024*1024*1024),2),\'G\') DATA,concat(round(index_length/(1024*1024*1024)\,2),\'G\') idx,
   
7. concat(round((data_length+index_length)/(1024*1024*1024),2),\'G\') total_size,round(index_length/data_length,2) id
   
8. xfrac FROM information_schema.TABLES ','where table_schema ="',db_name,'" ORDER BY data_length+index_length DESC L
   
9. IMIT ',f_limit);
   
10. prepare s1 from @sqltext;
    
11. execute s1;
    
12. deallocate prepare s1;
    
13. else
    
14. set @sqltext=concat('SELECT concat(table_schema,\'.\',table_name),concat(round(table_rows/1000000,2),\'M\') rows,concat(round(data_length/(1024*1024*1024),2),\'G\') DATA,concat(round(index_length/(1024*1024*1024),2),\'G\') idx,
    
15. concat(round((data_length+index_length)/(1024*1024*1024),2),\'G\') total_size,round(index_length/data_length,2) idxfrac FROM information_schema.TABLES ORDER BY data_length+index_length DESC LIMIT ',f_limit);
    
16. prepare s2 from @sqltext;
    
17. execute s2;
    
18. deallocate prepare s2;
    
19. end
    
20. if;
    
21. END$$
    
22. DELIMITER ;
    

复制代码

附某一个我目前平台上跑的投票数据库实例的数据片段：
Call mysql.sp_dbdata(‘ALL’,20);
######TABLESTATUS包括表rows和对应数据大小，索引大小，已经索引和数据比例
concat(table_schema,'.',table_name)     rows    DATA    idx     total_size      idxfrac
tech_survey_2.vote_32803        6.69M   0.15G   0.06G   0.21G   0.43
tech_survey_2.vote_33115        3.19M   0.07G   0.03G   0.10G   0.43
auto_survey_2.vote_28346        3.06M   0.07G   0.03G   0.10G   0.43
finance_survey_2.vote_30796     2.11M   0.05G   0.02G   0.07G   0.43
ent_survey_3.vote_27834 2.02M   0.05G   0.02G   0.06G   0.43
ent_survey_3.userinfo_27834     1.01M   0.03G   0.03G   0.06G   1.12
finance_survey.vote_27014       1.72M   0.04G   0.02G   0.06G   0.43
eladies_survey.vote_29246_old   1.57M   0.04G   0.02G   0.05G   0.43
tech_survey_2.vote_28901        1.51M   0.03G   0.01G   0.05G   0.43
ent_survey_3.vote_28063 1.51M   0.03G   0.01G   0.05G   0.43
finance_survey_2.vote_33024     1.49M   0.03G   0.01G   0.05G   0.43
auto_survey_2.userinfo_28346    0.77M   0.02G   0.02G   0.04G   1.08
news_survey_2.vote_31174        1.34M   0.03G   0.01G   0.04G   0.43
sports_survey.userinfo_26112    0.69M   0.02G   0.02G   0.04G   1.23
ent_survey.vote_30619   1.27M   0.03G   0.01G   0.04G   0.43
finance_survey.userinfo_27014   0.66M   0.02G   0.02G   0.04G   1.23