scaling导入mysql_hyperic mysql scaling 案例学习（数据库优化）

weixin_39688687

于 2021-01-20 15:19:36 发布

阅读量73

点赞数

文章标签： scaling导入mysql

本文链接：https://blog.csdn.net/weixin_39688687/article/details/113301485

版权

这是看了Sun 的communityone 上一篇介绍hyperic 在mysql 上scaling 的介绍写的笔记.

hyperic 是一个在大型数据中心用作服务器管理和监控的软件，Hyperic HQ 提供中心服务器来收集多台主机的状态和性能指标，并且在一个中心界面上管理…… 其他的好处看后面的参考资料

Hyperic 默认是支持三种数据库mysql,oracle,postgresql.

后面会用到的几个hyperic 内的术语:

Servers: 一台主机上一个特定的应用类型，比如mysql 服务，jboss 服务.

Services: 对应一个服务里面具体的某一个类型,比如mysql 里面的cpu 消耗,内存消耗,每个表的磁盘消耗.

Metrics: 每一个Services 一个时间点收集上来的数据,比如10：40 cpu 的消耗是60%.

Metrics data points(数据点): 一个时间点的某一个services 的具体数值, 比如上面的60%.

一种标准中等规模的度量数据:

远程管理300台主机，2100 Servers,21000 Services , 46万metric, 每分钟收集2万metric (平均值), 每天就是2千8百万数据点 (这个数据比较保守,性能收集间隔可以大一些.所以实际支持主机还可以多很多)

Hyperic Mysql 版本Scaling 技巧:

批量插入

INSERT INTO TABLE (a,b,c) values (0, 0,0), (1,1,1),(2,2,2),(3,3,3),…,…

减少和数据库来回次数，注意设置mysql 参数max_allowed_packet

其他可选的提高插入速度的方法Set unique_checks=0, insert, set unique_checks=1 .

Set foreign_key_checks=0, insert, set foreign_key_checks=1

Batch Aggregate Inserter

实际的数据点的数据一般很少看到，真正给监控人员看到的都是聚合后的数据(用线性图展现出来的)

HQ agent 将收集到的聚合数据发送给HQ Server , 然后Server 并不马上保存这些数据，而server将几百个agent 的各种service 数据都放在queue 里面，然后批量插入.

好处就是减少插入语句次数和connection 数. 减少CPU 消耗和服务器负载.调优之后的配置一般能达到700 agent . 3个workers , 每次批量插入BatchSize:2000 , QueueSize :4,000,000 , 每分钟可以插入2.2M 数据点. (还算是很保守的)

这个技巧我觉得最厉害，这跟它表的结构也有关系, 它表储存原始数据都是三个列(time,measurement_id,value), 所以任何监控类型数据都可以存在这个表，所以它把数据放在queue 里面，然后批量的2000个才插入一次, 每次queue 里面总有几百万的数据，但是连接数却总能控制在个位数下，如果几百个agent 每种metric 都要从数据连接池里面取连接，而且还是永久性的连接，最少需要上千甚至上万个connection, 使用这种queue + batch 的方式，只有几个connection , 数据库跟hyperic server 服务器(它用的jboss) 性能都伸缩了不少. 数据中心非要这种方式的收集方法.

控制数据增长

hyperic 监控的主机越多，service 越多，每分钟收集的数据量也就越多，控制详细数据和聚合数据的大小对于磁盘消耗显得很重要. 对于每个数据点收集是在hq_metric_data_xh_ys , 一共有18个这样的表只保存2天的详细数据，每个表只保存一个小时的详细数据，然后循环，每个小时进行一次数据压缩，压缩的表已经不会进行insert 操作了，把数据压缩之后保存在eam_measurement_data_1h 中 (5列 $MEASUREMENT_ID , $TIMESTAMP , $VALUE , $MIN , $MAX ) , 每个小时只有一条数据(简单认为缩小了60倍吧)，这种每个小时的数据保存14天，然后再把数据压缩到6个小时保存一条数据放在MEASUREMENT_DATA_6H 表中，保存31天，表结构完全一样，最后保存到MEASUREMENT_DATA_1D 表中，一天只保存一条数据.这个表你想保存多少年都可以了. 按照一台主机监控800台服务器上的16，000个metric 来看，一天也就16，000条数据，多少年都不成问题了.

由于里面每个表的功能基本都是独立，一层一层的级别关系，所以进行很多操作的时候都没有锁，实时收集数据的表只有insert 操作，删除的表都是直接truncate 掉，压缩的表一个小时才做一次,用户查看的都是一个小时几个点的数据，而你又很容易看到几个月甚至几年的综合数据，比如像是可用性，CPU ，磁盘使用. 每种应用都找到自己的特殊表来用. 性能，负载，容量，历史数据都得到了很好的保存。这个想法主要来自RRDtool ，一个主要用来时间系列的画图框架，主要用在nagios, cacti 等监控后台的图表展现上.

分区

所有细节的表由hq_metric_data_xh_ys 表示，每两个表代表一天，一共9天，

所有的数据归档压缩之后直接truncate ,而不是delete (这是针对mysql 5.1 之前没有partition 的功能而做，不知道以后会不会针对mysql 5.1 之后出个特别版), 应用程序上会自动计算应该insert 那个表，每个时间段该取那个表的数据. 所以insert 的表不会跟select 表和truncate 的表有冲突，3种表都自己做自己的工作.

truncate 和 delete 的好处也有提到，这种partition 的设计还是要好好学学.

索引的选择

选用InnoDB 是因为其基于主键的cluster index (和oracle 的IOT 一样), 优点就是select 更快，insert 也更快，索引占的磁盘也更少. 由于它全部都是基于时间点的顺序加入的，所以其cluster index, leaf index, data 都是基于同一个顺序在磁盘上访问，所以即使它不需要事务和锁，InnoDB 还是其最佳选择.

SQL 的选择

由于mysql 的view 不能够把子查询的sql 执行计划和外层的查询计划统一来计算，所以数据过滤操作要放在里面，外面再把表连接起来, 像是select xxx from (select *** from innertable1 union innertable2) as bigtable where bigtable.xxx ?? 这种sql 执行起来就很慢, 而hyperic 要通过程序计算从那些表取数据，然后在外层连接起来. 像是这样

SELECT begin AS timestamp, AVG(value) AS value, MAX(value) AS peak, MIN(value) AS low

FROM

(SELECT 1207631340000 + (2880000 * i) AS begin FROM EAM_NUMBERS WHERE i < 60) n,

(SELECT * FROM HQ_METRIC_DATA_2D_1S

WHERE timestamp between 1207767600000 and 1207804140000 AND

measurement_id = 600332 UNION ALL

SELECT * FROM HQ_METRIC_DATA_2D_0S

WHERE timestamp between 1207724400000 and 1207767599999 AND

measurement_id = 600332 UNION ALL

SELECT * FROM HQ_METRIC_DATA_1D_1S

WHERE timestamp between 1207681200000 and 1207724399999 AND

measurement_id = 600332 UNION ALL