hyperic mysql scaling 案例學習（資料庫優化）

這是看了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 技巧:

1. 批量插入

   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

2. 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） 性能都伸縮了不少. 資料中心非要這種方式的收集方法.

3. 控制資料增長

   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 等監控背景的圖表展現上.

4. 分區

   所有細節的表由hq_metric_data_xh_ys 表示，每兩個表代表一天，一共9天，

   所有的資料歸檔壓縮之後直接truncate ,而不是delete (這是針對mysql 5.1 之前沒有partition 的功能而做，不知道以後會不會針對mysql 5.1 之後出個特别版), 應用程式上會自動計算應該insert 那個表，每個時間段該取那個表的資料. 是以insert 的表不會跟select 表和truncate 的表有沖突，3種表都自己做自己的工作.

   truncate 和 delete 的好處也有提到，這種partition 的設計還是要好好學學.

5. 索引的選擇

   選用InnoDB 是因為其基于主鍵的cluster index (和oracle 的IOT 一樣), 優點就是select 更快，insert 也更快，索引占的磁盤也更少. 由于它全部都是基于時間點的順序加入的，是以其cluster index, leaf index, data 都是基于同一個順序在磁盤上通路，是以即使它不需要事務和鎖，InnoDB 還是其最佳選擇.

6. 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

   SELECT * FROM HQ_METRIC_DATA_1D_0S

   WHERE timestamp between 1207638000000 and 1207681199999 AND

   measurement_id = 600332 UNION ALL

   SELECT * FROM HQ_METRIC_DATA_0D_1S

   WHERE timestamp between 1207631340000 and 1207637999999 AND

   measurement_id = 600332) EAM_MEASUREMENT_DATA

   WHERE timestamp BETWEEN begin AND begin + 2879999 AND measurement_id = 600332

   GROUP BY begin ORDER BY begin;

   不用管裡面多複雜，簡單來說就是裡面要盡量多過濾資料（過濾條件都是程式通過時間計算的），外層得到最少的值才開始group 和order . （它從最差性能的sql -> 中等性能的sql -> 程式配合的最好性能的sql , 我花了幾天時間還是隻能了解個大概）

7. ID 生成政策

   ID都是從10001開始生成，為hard-code 的ID 保留10000個空位，不使用auto-increment , 使用myisam 類型的hq_sequence 表來表示sequence , 

8. 後面還有幾個關于hibernate id 生成政策，建議性能參數和統計值，以及伺服器建議配置等，不一一介紹了.

<p 整個hyperic ="" 在體系架構，程式設計特性和資料庫設計上都設計的很進階，有很多功能是因為資料庫特性限制而設計的，但同時也盡量發揮了每種資料庫的最大特性，尤其是程式和資料庫結合的設計提升性能很多，看得出特為資料中心的管理方式做了很多優化.<="" p="" style="margin-top: 0px; margin-bottom: 10px; padding-top: 0px; padding-bottom: 0px; color: rgb(85, 85, 85); font-family: Verdana, 'BitStream vera Sans', Tahoma, Helvetica, sans-serif; line-height: 17px; ">

參考資料:

1. http://www.scribd.com/doc/2909846/Scaling-MySQL-A-Case-Study-of-Hyperic-HQ

   scribd 上的文檔大部分可以下載下傳，也可以線上觀看，很好的文檔分享站點.