巨量資料的基本功- MYSQL 大量資料索引與匯出

從好夥伴那交接了工作,接手了一個"億筆"等級的資料庫,空間約上百GB資料庫,這個資料庫存放了我們所蒐集的大量文件資料,這樣的資料重複性高,且需要進行語意地分析,所遇到的瓶頸在於查詢速度較慢,尤其是給時間區間的時候,所有的查詢會從首筆逐筆往後查詢,所以當查詢前期資料時,速度較快,然後查詢後期資料時,速度往往就減慢了,以下記錄了我在做這問題的幾個歷程,有幾個環節讓我捶胸頓足啊,還悶到病來,連睡覺的時候都夢到怎麼辦,還想到大二的物件導向,用C++去實作不同的operator,那是一個作業,當時有一個地方想不通,就去睡個午覺,在睡夢中,我竟然夢到了我寫出來了,起床後趁著做夢的記憶,把作業寫完了,這是我唯一一次透過睡夢完成我的作業,以後這種感覺也沒回來了。

1. 以JAVA實作大量資料LUCENE INDEX

巨量資料,又提到文件,直覺就會聯想到Lucene,是的,其實要用JAVA對於這些資料去做LUCENE INDEX是件簡單的事情,然而,上億筆資料透過JAVA的Result去裝,大概也絕對是裝不下的,因為一次的查詢全部放在ResultSet物件,然而若把這件事情交給java.sql.Statement,簡單的設定幾個參數,這件事情就搞定了。

Statement stmt = connection.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,

java.sql.ResultSet.CONCUR_READ_ONLY);

stmt.setFetchSize(Integer.MIN_VALUE);

我們就會看著資料一筆一筆放到Lucene裡了,基本上,2000萬筆資料大約跑個三四個小時,即可完成lucene的index(包含簡單的中文斷詞)

2. 透過HTTP CLIENT與SpringMVC自動更新Lucene Index

由於我們有四個分散的資料蒐集節點,為了避免直接碰到Server端的資料庫,所以我們規劃透過Web Service的方式,藉由每個資料搜集端把資料批次的透過JSON的格式傳送至Server端,其實這種想法有值得挑戰的地方,我們為何不就直接把資料庫安全控管好,透過Socket直接傳到Server端?這樣可以少了一些HTTP的overhead,然而在系統設計的延展性來說,透過Web Service的接口,或許我們可以讓系統的延伸性更高。另外,當四個結點都往中間點塞資料,會不會掛掉?

Scale Out是將運算需求橫向擴充,而非縱向擴充,也就是說,當我們資源缺乏的時候,不是要買更貴的電腦,而是要找更多普通的電腦來分擔這樣的運算需求。

所以若真的Web Server撐不住,現在的Web Server都有支援Cluster,例如:http://tomcat.apache.org/tomcat-6.0-doc/cluster-howto.html,甚至,我們可以採用nginx為Proxy進行分流。

然而原本的想法是,每天某個時間去查詢原本MySQL的資料庫,把今天的資料抓出來透過上述的機制更新到SpringMVC的Lucene Index,但因為MySQL資料庫過於龐大,每天查詢一次可能就需要一天,這樣會造成這樣場景的破滅,因此我們回到關聯試資料庫的議題。

3. MySQL欄位與索引的動機

針對日期,其實透過CHAR去存放與透過日期去存放,差異其實不大,然而若能夠適當的對於欄位進行索引,速度就會差別很大,因此我們試著更新日期欄位的資料格式,並且對於該欄位進行索引,基本上這件事情也不難,只需ALTER TABLE xxx ADD INDEX xxx 類似這樣語法,即可讓資料庫如虎添翼,但是別忘記了,我們的資料庫有上千萬筆資料啊,修改欄位與更改索引是件大工程,而且在MYSQL裡面去執行剛這些語法時,再用SHOW PROCESSLIST;去看狀態時,竟然發現 *** DEAD ***這種狀態,去問我的高手同事們,也覺得奇怪,然而耐心等待一下,有些process仍能完成,*** DEAD ***其實只是假死,後來去Google了一下,原來是MYSQL 5.0的BUG(http://bugs.mysql.com/bug.php?id=30960),這或許就不這麼重要了。後來把MYSQL升級到5.5就不會有這種問題了。

然而還是然而,我需要查快一點的需求還是沒有滿足,在空虛的當下,我想了個方法,先Create一張乾淨的TABLE,這個TABLE都建好索引且欄位型態也都先設計好; 然後再把資料copy進去,當然,第一種做法就是透過JDBC硬幹,第二種做法是透過mysqldump出來再dump進去,第三種做法就是下SQL語法。

4. MySQL JDBC Connection Pool

基本上第一種做法就出問題了,抓出來透過前述第一點已經沒問題了,然而要怎麼寫進去就是一個很大的問題,Connection是很貴滴,如果都不管connection,大概幾萬筆資料就掛了,如果每一筆資料都要new/close一個connection,速度超慢,大概可以跑到我退役了,所以我們就參考了Connection Pool的實作套件-APACHE DBCP(http://commons.apache.org/dbcp/),當然類似的套件也有別的選擇,例如:Proxool 0.9.1。很好,但也沒有很好,因為,跑了三四十萬筆,還是爆了(heap tree out-of-memory),Connection Pool畢竟不是神,但是度的重建一個Connection Pool,這問題是可以處理的。

兩三千萬筆資料,大概花了我幾個小時,終於跑完。

當然,我們也可以採用mysqldump的做法,但千萬要記得,要加上參數-t,因為我們現在只要會出資料,不需要匯出Table的schema,也許你會說,等匯出再去刪,現在的場景是上千萬筆資料,至少不太多的文字編輯器可以讓你再去編輯這樣大的文字檔。

以DBCP而言,需要commons-dbcp-1.4.jar以及commons-pool-1.5.6.jar這兩個library,然後在code裡面就大概下面這樣做法:

BasicDataSource ds = new BasicDataSource();

ds.setDriverClassName(“com.mysql.jdbc.Driver");

ds.setUsername(“id");

ds.setPassword(“password");

ds.setUrl(“jdbc:mysql://"+path+"?useUnicode=true&characterEncoding=utf-8″);

conn = ds.getConnection();

Connection就到手了!!

完成所引厚的資料庫,的確速度變快了,然而若要做到及時分析,速度仍顯不足,但批次做Lucene Index已是綽綽有餘,每天排成轉換,Index到Lucene裡,想要查IP,想要分析語意,隨時要做統計,或許都可以更快一點點了。

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

您的留言將使用 WordPress.com 帳號。 登出 / 變更 )

Twitter picture

您的留言將使用 Twitter 帳號。 登出 / 變更 )

Facebook照片

您的留言將使用 Facebook 帳號。 登出 / 變更 )

Google+ photo

您的留言將使用 Google+ 帳號。 登出 / 變更 )

連結到 %s