数据库监控策略浅谈

kider · 发表于 2009-6-3 14:02:52

作者：邵宗文 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的存储过程脚本

DELIMITER $$
DROP PROCEDURE IF EXISTS `mysql`.`sp_dbdata`$$
create procedure sp_dbdata (IN db_name varchar(100),IN f_limit int)
BEGIN if db_name <>
'ALL' THEN
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,
concat(round((data_length+index_length)/(1024*1024*1024),2),\'G\') total_size,round(index_length/data_length,2) id
xfrac FROM information_schema.TABLES ','where table_schema ="',db_name,'" ORDER BY data_length+index_length DESC L
IMIT ',f_limit);
prepare s1 from @sqltext;
execute s1;
deallocate prepare s1;
else
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,
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);
prepare s2 from @sqltext;
execute s2;
deallocate prepare s2;
end
if;
END$$
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

帐号		自动登录	找回密码
密码			注册