我是如何在SQL Server中處理每天四億三千萬記錄的

馬非碼的部落格發表於2014-11-27

首先宣告,我只是個程式設計師,不是專業的DBA,以下這篇文章是從一個問題的解決過程去寫的,而不是一開始就給大家一個正確的結果,如果文中有不對的地方,請各位資料庫大牛給予指正,以便我能夠更好的處理此次業務。

專案背景

這是給某資料中心做的一個專案,專案難度之大令人髮指,這個專案真正的讓我感覺到了,商場如戰場,而我只是其中的一個小兵,太多的戰術,太多的高層之間的較量,太多的內幕了。具體這個專案的情況,我有空再寫相關的博文出來。

這個專案是要求做環境監控,我們暫且把受監控的裝置稱為採集裝置,採集裝置的屬性稱為監控指標。專案要求:系統支援不少於10w個監控指標,每個監控指標的資料更新不大於20秒,儲存延遲不超過120秒。那麼,我們可以通過簡單的計算得出較理想的狀態——要儲存的資料為:每分鐘30w,每個小時1800w,也就是每天4億3千兩百萬。而實際,資料量會比這個大5%左右。(實際上大部分是資訊垃圾,可以通過資料壓縮排行處理的,但是別人就是要搞你,能咋辦)

上面是專案要求的指標,我想很多有不少大資料處理經驗的同學都會呲之以鼻,就這麼點?嗯,我也看了很多大資料處理的東西,但是之前沒處理過,看別人是頭頭是道,什麼分散式,什麼讀寫分離,看起來確實很容易解決。但是,問題沒這麼簡單,上面我說了,這是一個非常惡劣的專案,是一個行業惡性競爭典型的專案。

  1. 沒有更多的伺服器,而是這個伺服器除了搭配資料庫、集中採集器(就是資料解析、告警、儲存的程式),還要支援30w點的北向介面(SNMP),在程式沒有優化之前CPU常年佔用80%以上。因為專案要求要使用雙機熱備,為了省事,減少不必要的麻煩,我們把相關的服務放在一起,以便能夠充分利用HA的特性(外部購買的HA系統)
  2. 系統資料正確性要求極其變態,要求從底層採集系統到最上層的監控系統,一條資料都不能差
    我們的系統架構如下,可以看到,其中資料庫壓力非常之大,尤其在LevelA節點:
  3. 硬體配置如下:
    CPU:英特爾® 至強® 處理器 E5-2609 (4核, 2.40GHz, 10MB, 6.4 GT/s)
    記憶體:4GB (2x2GB) DDR3 RDIMM Memory, 1333MHz,ECC
    硬碟:500GB 7200 RPM 3.5” SATA3 硬碟,Raid5.
  4. 資料庫版本
    採用的是SQLServer2012標準版,HP提供的正版軟體,缺少很多企業版的NB功能。

寫入瓶頸

首先遇到的第一個攔路虎就是,我們發現現有的程式下,SQLServer根本處理不了這麼多的資料量,具體情況是怎樣的呢?

我們的儲存結構

一般為了儲存大量的歷史資料,我們都會進行一個物理的分表,否則每天上百萬條的記錄,一年下來就是幾億條。因此,原來我們的表結構是這樣的:

CREATE TABLE [dbo].[His20140822](
	[No] [bigint] IDENTITY(1,1) NOT NULL,
	[Dtime] [datetime] NOT NULL,
	[MgrObjId] [varchar](36) NOT NULL,
	[Id] [varchar](50) NOT NULL,
	[Value] [varchar](50) NOT NULL,
 CONSTRAINT [PK_His20140822] PRIMARY KEY CLUSTERED 
(
	[No] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

No作為唯一的標識、採集裝置Id(Guid)、監控指標Id(varchar(50))、記錄時間、記錄值。並以採集裝置Id和監控指標Id作為索引,以便快速查詢。

批量寫入

寫入當時是用BulKCopy,沒錯,就是它,號稱寫入百萬條記錄都是秒級的

public static int BatchInert(string connectionString, string desTable, DataTable dt, int batchSize = 500)
    {
        using (var sbc = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction)
        {
            BulkCopyTimeout = 300,
            NotifyAfter = dt.Rows.Count,
            BatchSize = batchSize,
            DestinationTableName = desTable
        })
        {
            foreach (DataColumn column in dt.Columns)
                sbc.ColumnMappings.Add(column.ColumnName, column.ColumnName);
            sbc.WriteToServer(dt);
        }

        return dt.Rows.Count;
    }

存在什麼問題?

上面的架構,在每天4千萬的資料都是OK的。但是,調整為上述背景下的配置時,集中監控程式就記憶體溢位了,分析得知,接收的太多資料,放在了記憶體中,但是沒有來得及寫入到資料庫中,最終導致了生成的資料大於消費的資料,導致記憶體溢位,程式無法工作。

瓶頸到底在哪裡?

是因為RAID磁碟的問題?是資料結構的問題?是硬體的問題?是SQLServer版本的問題?是沒有分割槽表的問題?還是程式的問題?

當時時間只有一個星期,一個星期搞不好,專案監管就要我們滾蛋了,於是,有了連續工作48小時的壯舉,有了到處打電話求人的抓雞……

但是,這個時候需要的是冷靜,再冷靜……SQLServer版本?硬體?目前都不大可能換的。RAID磁碟陣列,應該不是。那麼到底是什麼,真TM的冷靜不下來。

大家可能體會不到現場那種緊張的氣氛,其實過了這麼久,我自己也都很難再回到那種情境。但是可以這麼說,或許我們現在有了各種方法,或者處於局外人我們有更多思考,但是當一個專案壓迫你快到放棄的時候,你那時的想法、考慮在現場環境因素的制約下,都可能出現重大的偏差。有可能讓你快速的思考,也有可能思維停滯。有些同事在這種高壓的環境下,甚至出現了更多的低階錯誤,思維已經完全亂了,效率更低了……36小時沒有閤眼,或者只在工地上(下雨天到處都是泥巴,幹了的話到時都是泥灰)眯兩三個小時,然後繼續幹,連續這麼一個星期!或者還要繼續!

很多人給了很多想法,但是好像有用,又好像沒用。等等,為什麼是“好像有用,又好像沒用”?我隱隱約約中,好像抓住了一絲方向,到底是什麼?對了,驗證,我們現在是跑在現場環境下,之前沒有問題,不代表現在的壓力下沒有問題,要在一個大型系統中分析這麼個小功能,影響太大了,我們應該分解它。是的,是“單元測試”,就是單個方法的測試,我們需要驗證每個函式,每個獨立的步驟到底耗時在哪裡?

逐步測試驗證系統瓶頸

修改BulkCopy的引數
首先,我想到的是,修噶BulkCopy的各項引數,BulkCopyTimeoutBatchSize,不斷的測試調整,結果總是在某個範圍波動,實際並沒有影響。或許會影響一些CPU計數,但是遠遠沒有達到我的期望,寫入的速度還是在5秒1w~2w波動,遠遠達不到要求20秒內要寫20w的記錄。

按採集裝置儲存
是的,上述結構按每個指標每個值為一條記錄,是不是太多的浪費?那麼按採集裝置+採集時間作為一條記錄是否可行?問題是,怎麼解決不同採集裝置屬性不一樣的問題?這時,一個同事發揮才能了,監控指標+監控值可以按XML格式儲存。哇,還能這樣?查詢呢,可以用for XML這種形式。

於是有了這種結構:No、MgrObjId、Dtime、XMLData

結果驗證,比上面的稍微好點,但是不是太明顯。

資料表分割槽???
那個時候還沒有學會這個技能,看了下網上的文章,好像挺複雜的,時間不多了,不敢嘗試。

停止其他程式
我知道這個肯定是不行的,因為軟體、硬體的架構暫時沒法修改。但是我希望驗證是不是這些因素影響的。結果發現,提示確實明顯,但是還是沒有達到要求。

難道是SQLServer的瓶頸?
沒轍了,難道這就是SQLServer的瓶頸?上網查了下相關的資料,可能是IO的瓶頸,尼瑪,還能怎麼辦,要升級伺服器,要更換資料庫了嗎,但是,專案方給嗎?

等等,好像還有個東西,索引,對索引!索引的存在會影響插入、更新

去掉索引

是的,去掉索引之後查詢肯定慢,但是我必須先驗證去掉索引是否會加快寫入。如果果斷把MgrObjId和Id兩個欄位的索引去掉。

執行,奇蹟出現了,每次寫入10w條記錄,在7~9秒內完全可以寫入,這樣就達到了系統的要求。

查詢怎麼解決?

一個表一天要4億多的記錄,這是不可能查詢的,在沒有索引的情況下。怎麼辦!?我又想到了我們的老辦法,物理分表。是的,原來我們按天分表,那麼我們現在按小時分表。那麼24個表,每個表只需儲存1800w條記錄左右。

然後查詢,一個屬性在一個小時或者幾個小時的歷史記錄。結果是:慢!慢!!慢!!!去掉索引的情況下查詢1000多萬的記錄根本是不可想象的。還能怎麼辦?

繼續分表,我想到了,我們還可以按底層的採集器繼續分表,因為採集裝置在不同的採集器中是不同的,那麼我們查詢歷史曲線時,只有查單個指標的歷史曲線,那麼這樣就可以分散在不同的表中了。

說幹就幹,結果,通過按10個採集嵌入式並按24小時分表,每天生成240張表(歷史表名類似這樣:His_001_2014112615),終於把一天寫入4億多條記錄並支援簡單的查詢這個問題給解決掉了!!!

查詢優化

在上述問題解決之後,這個專案的難點已經解決了一半,專案監管也不好意思過來找茬,不知道是出於什麼樣的戰術安排吧。

過了很長一段時間,到現在快年底了,問題又來了,就是要拖死你讓你在年底不能驗收其他專案。

這次要求是這樣的:因為上述是模擬10w個監控指標,而現在實際上線了,卻只有5w個左右的裝置。那麼這個明顯是不能達到標書要求的,不能驗收。那麼怎麼辦呢?這些聰明的人就想,既然監控指標減半,那麼我們把時間也減半,不就達到了嗎:就是說按現在5w的裝置,那你要10s之內入庫儲存。我勒個去啊,按你這個邏輯,我們如果只有500個監控指標,豈不是要在0.1秒內入庫?你不考慮下那些受監控裝置的感想嗎?

但是別人要玩你,你能怎麼辦?接招唄。結果把時間降到10秒之後,問題來了,大家仔細分析上面邏輯可以知道,分表是按採集器分的,現在採集器減少,但是數量增加了,發生什麼事情呢,寫入可以支援,但是,每張表的記錄接近了400w,有些採集裝置監控指標多的,要接近600w,怎麼破?

於是技術相關人員開會討論相關的舉措。

在不加索引的情況下怎麼優化查詢?

有同事提出了,where子句的順序,會影響查詢的結果,因為按你刷選之後的結果再處理,可以先刷選出一部分資料,然後繼續進行下一個條件的過濾。聽起來好像很有道理,但是SQLServer查詢分析器不會自動優化嗎?原諒我是個小白,我也是感覺而已,感覺應該跟VS的編譯器一樣,應該會自動優化吧。

具體怎樣,還是要用事實來說話:

結果同事修改了客戶端之後,測試反饋,有較大的改善。我檢視了程式碼:

難道真的有這麼大的影響?等等,是不是忘記清空快取,造成了假象?
於是讓同事執行下述語句以便得出更多的資訊:

--優化之前
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SET STATISTICS IO ON
select Dtime,Value from dbo.his20140825 WHERE  Dtime>='' AND Dtime<='' AND MgrObjId='' AND Id=''
SET STATISTICS IO OFF

--優化之後
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SET STATISTICS IO ON
select Dtime,Value from dbo.his20140825 WHERE MgrObjId='' AND Id='' AND Dtime>='' AND Dtime<=''
SET STATISTICS IO OFF

結果如下:

優化之前反而更好了?

仔細檢視IO資料,發現,預讀是一樣的,就是說我們要查詢的資料記錄都是一致的,物理讀、表掃描也是一直的。而邏輯讀取稍有區別,應該是快取命中數導致的。也就是說,在不建立索引的情況下,where子句的條件順序,對查詢結果優化作用不明顯

那麼,就只能通過索引的辦法了。

建立索引的嘗試

建立索引不是簡單的事情,是需要了解一些基本的知識的,在這個過程中,我走了不少彎路,最終才把索引建立起來。

下面的實驗基於以下記錄總數做的驗證:

按單個欄位建立索引
這個想法,主要是受我建立資料結構影響的,我記憶體中的資料結構為:Dictionary<MgrObjId,Dictionary<Id,Property>>。我以為先建立MgrObjId的索引,再建立Id的索引,SQLServer查詢時,就會更快。

先按MgrObjId建立索引,索引大小為550M,耗時5分25秒。結果,如上圖的預估計劃一樣,根本沒有起作用,反而更慢了。

按多個條件建立索引
OK,既然上面的不行,那麼我們按多個條件建立索引又如何?CREATE NONCLUSTERED INDEX Idx_His20141008 ON dbo.his20141008(MgrObjId,Id,Dtime)

結果,查詢速度確實提高了一倍:

等等,難道這就是索引的好處?花費7分25秒,用1.1G的空間換取來的就是這些?肯定是有什麼地方不對了,於是開始翻查資料,檢視一些相關書籍,最終,有了加大的進展。

正確的建立索引

首先,我們需要明白幾個索引的要點:

  • 索引之後,按索引欄位重複最少的來排序,會達到最優的效果。以我們的表來說,如果建立了No的聚集索引,把No放在where子句的第一位是最佳的,其次是Id,然後是MgrObjId,最後是時間,時間索引如果表是一個小時的,最好不要用
  • where子句的順序決定了查詢分析器是否使用索引來查詢。比如建立了MgrObjId和Id的索引,那麼where MgrObjId='' and Id='' and Dtime=''就會採用索引查詢,而where Dtime='' and MgrObjId='' and Id=''則不一定會採用索引查詢。
  • 把非索引列的結果列放在包含列中。因為我們條件是MgrObjId和Id以及Dtime,因此返回結果中只需包含Dtime和Value即可,因此把Dtime和Value放在包含列中,返回的索引結果就有這個值,不用再查物理表,可以達到最優的速度。

跟上述幾點原則,我們建立以下的索引:CREATE NONCLUSTERED INDEX Idx_His20141008 ON dbo.his20141008(MgrObjId,Id) INCLUDE(Value,Dtime)

耗費時間為:6分多鐘,索引大小為903M。

我們看看預估計劃:

可以看到,這裡完全使用了索引,沒有額外的消耗。而實際執行的結果,1秒都不到,竟然不用一秒就在1100w的記錄中把結果篩選了出來!!帥呆了!!

怎麼應用索引?

既然寫入完成了、讀取完成了,怎麼結合呢?我們可以把一個小時之前的資料建立索引,當前一個小時的資料就不建立索引。也就是,不要再建立表的時候建立索引!!

還能怎麼優化

可以嘗試讀寫分離,寫兩個庫,一個是實時庫,一個是隻讀庫。一個小時內的資料查詢實時庫,一個小時之前的資料查詢只讀庫;只讀庫定時儲存,然後建立索引;超過一個星期的資料,進行分析處理再儲存。這樣,無論查詢什麼時間段的資料,都能夠正確處理了——一個小時之內的查詢實時庫,一個小時到一個星期內的查詢只讀庫,一個星期之前的查詢報表庫。

如果不需要物理分表,則在只讀庫中,定時重建索引即可。

總結

如何在SQLServer中處理億萬級別的資料(歷史資料),可以按以下方面進行:

  • 去掉表的所有索引
  • 用SqlBulkCopy進行插入
  • 分表或者分割槽,減少每個表的資料總量
  • 在某個表完全寫完之後再建立索引
  • 正確的指定索引欄位
  • 把需要用到的欄位放到包含索引中(在返回的索引中就包含了一切)
  • 查詢的時候只返回所需的欄位

相關文章