線上交易記憶體內資料庫

by adonis 24. 七月 2014 15:52

前言

SQL Server 2014之後開始支援記憶體內資料庫,本期的重點就擺
在如何設定以支援記憶體內資料庫。

 

線上交易記憶體內資料庫


SQL Server 一直以來,所採用的資料存取方式,就是「最佳化讀寫硬碟」的模式,簡單來說,就是要讀的資料如果在 buffer 中可以找到,則從 buffer中取出,若資料在 buffer中不存在,則利用磁碟 I/O,將資料從磁碟中放置到 buffer,然後再將資料從 buffer中取回。

當然,這樣的招式對於資料庫的讀取效能隨著資料大小愈來愈大或是記憶體空間愈來愈不足,將會導致效能愈來愈慢,因為當記憶體不足時,一些原本在 buffer中的資料將會轉存回磁碟以保留空間容納新的暫存。

以上所提的是資料查詢時的狀況,若是資料異動的話,則動作基本上來說就有點不一樣了:

1.會先存入 log
2.將要異動的資料從磁碟載入 buffer,並且修改
3.交易完成
4.將異動完的資料寫回磁碟

是的,你沒有看錯,是將資料載入到 buffer後,將交易先完成再將資料寫回到磁碟,因為 buffer寫入磁碟的速度較慢,所以要先把交易完成再將 buffer資料寫回。由於我們有 log,所以如果動作發生問題,皆可以利用 log回復。

而記憶體內資料庫,和 SQL Server 2012所謂的「資料行存放區索引」這個功能不同,它仍以資料列為主,而且適用於交易型資料操作, SQL Server 2012的資料行存放區索引因為結構的改變,資料表只能唯讀,但記憶體內資料庫則是不然,和資料行存放區採用完全不同的做法,就一般的測試可以大概提升3到50倍左右的效能。

它有幾項特點,描述如下:

1.以資料表為單位,存取頻繁的資料表放入完全獨立的記憶體區塊,和原來資料表所用的記憶體區塊(buffer)不同。不過不支援像是大型資料型別(MAX、XML或是 SQL CLR)…等。

2.一旦設定資料表為記憶體最佳化(memory_optimized=on),則不再存放於傳統的 mdf,ndf檔案中,完整記錄型式將存在於記憶體中。

3.可以設定是否不需要交易記錄,一般異動最大的效能瓶頸出現在交易記錄的寫入,很多人會問一個資料庫設定多個 log檔案是否能加速資料的新增、修改與刪除,答案是不會,因為 log檔變多,其內容還是循序的存入到 log檔中,並不會因為你增加了多個 log檔案就使得資料表的新增、修改、刪除的速度變快。
你可以設定持久性屬性 durability,可以不經過交易記錄(durability=schema_only),交易記錄所寫的資訊會比之前版本來的更少,也可以使用非同步的延遲交易持久性,批次將記憶體內的交易記錄暫存區寫入硬碟以換來效能,但就有可能會遺失記錄。(速度快自然會有風險)

4.採用記憶體為主,存取記憶體不使用8K分頁(page),而是使用指標(pointer)串列。資料寫回磁碟也是使用循序的串流(streaming)的方式,所以磁碟存取使用循序不是隨機。

5.鎖定機制完全改變,以 highly scalable concurrency control mechanism取代 lock manager,會發揮大量 CPU的平行運算能力。

6.每個資料表都需要有至少一個以上的索引,但索引並不是放在磁碟,而是資料載入記憶體時當下建立索引。

7.可以將預存程序編譯成機械碼(而不是 .NET managed code),不過有些語法並不支援,需要測試,這樣執行的速度會更快。
記憶體資料庫,資料在查詢時, T-SQL可同時查詢記憶體最佳化資料表以及存放在硬碟上的舊格式資料表,此種查詢方式為「Interop查詢」,若是用編譯成機械碼的預存程序,稱為「原生預存程序」查詢,此時僅能存取記憶體最佳化資料表。 

建立資料庫與記憶體最佳化物件

記憶體最佳化資料表的資料並未存在於硬碟上,不過還是有一組資料檔和差異檔案組(checkpoint檔案),以循序的方式寫到硬碟,當 SQL Server正常或異常啟動時,這組檔案可用來搭配交易記錄檔回復資料庫資料,而資料檔與差異檔只能用附加的方式在檔尾新增資料,若資料檔放滿就會變成唯讀資料,不像 mdf、ndf檔案是採用隨機讀寫。

我們為了要放資料檔和差異檔,需要為資料庫指定「記憶體最佳化資料群組(Memory Optimized Data File Group)」,如下圖:

image

然而,這個記憶體最佳化資料群組並沒有辦法使用管理工具,必需使用指令,指令如下:

CREATE DATABASE [InMemberyTest]
CONTAINMENT = NONE
ON  PRIMARY
( NAME = N'InMemberyTest', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\InMemberyTest.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [INMemory] CONTAINS MEMORY_OPTIMIZED_DATA  DEFAULT
( NAME = N'InMemoryDir', FILENAME = N'c:\data\inmemorydir\InMemoryDir' , MAXSIZE = UNLIMITED)
LOG ON
( NAME = N'InMemberyTest_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\InMemberyTest_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

好吧,反正就是給一個檔案群組,設定它是記憶體最佳化資料群組,然後再建立資料表,可以指定是不是「記憶體最佳化資料表」,這個也需要下 T-SQL指令,如下指令:

CREATE TABLE [dbo].[Destination]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](10) COLLATE Chinese_Taiwan_Stroke_BIN2 NOT NULL,
    [age] [int] NOT NULL,

INDEX [ixName] NONCLUSTERED HASH
(
    [Name]
)WITH ( BUCKET_COUNT = 131072),
INDEX [ixNameAge] NONCLUSTERED
(
    [Name] ASC,
    [age] ASC
),
PRIMARY KEY NONCLUSTERED HASH
(
    [ID]
)WITH ( BUCKET_COUNT = 131072)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )

這個資料表,是透過 memory_optimized = ON的方式設定為記憶體資料表,而 durability的屬性設定為 schema_and_data則代表需要有交易記錄,若為 schema_only的話則代表資料變更內容不寫入交易記錄。

至於索引的建立,需要一開始在資料表定義時設定,事後若想新增修改刪除索引則沒有辦法。

建立好的資料表,可用管理工具看到其屬性為true:

image

這些屬性都是沒有辦法改的,只能唯讀。

除了索引的限制之外,記憶體最佳化資料表還有下面的限制:

1.不支援 DML trigger。
2.不支援 Foreign key以及 Check條件約束。
3.除了 Primary key外,不支援 Unique 條件約束。
4.索引的資料不支援可 null的欄位。
5.最多8個索引,包含 Primary key所建立的索引。

存取的方式主要有兩種,一種是 T-SQL指令,而 T-SQL指令可以查詢到硬碟中的資料,也可以查詢記憶體中的資料表,若是撰寫預存程序,設定為「原生編譯」時,這時所取得的是記憶體中的資料表內容。

記憶體資料表的索引,可以設定為 B-Tree類型的索引,也就是原本 SQL Server所支援的索引,利用 B-TREE的結構樹來找尋資料,或者是用 SQL Server 2014的新功能, hash索引,利用雜湊演算法的方式來建立,只是建立的時後需要設定 BUCKET_COUNT屬性,用大概研究過雜湊演算法的朋友應該知道,雜湊的複雜度是1,但前提建立在好的雜湊函數,如果雜湊函數會產生同個位置,則必需使用串列的方式將值放在後面,所以 BUCKET_COUNT的值設定大,則浪費記憶體,線上說明是建議各位把值設定為預期該欄位的唯一值記錄筆數的1~2倍的數量。

下期再繼續介紹後續的

 

LAST

Tags:

評論 (41) -

cours de theatre
cours de theatre United States
2017/9/30 下午 09:07:32 #

Really enjoyed this blog.Much thanks again. Will read on...

回覆

Osimi sea view
Osimi sea view United States
2017/10/9 下午 07:58:03 #

Thanks a lot for the blog article.Much thanks again. Want more.

回覆

Google cheat 2017
Google cheat 2017 United States
2017/10/12 下午 10:36:13 #

Thanks for the blog. Fantastic.

回覆

dragon city hack android 1
dragon city hack android 1 United States
2017/10/15 下午 05:23:31 #

Appreciate you sharing, great article post. Awesome.

回覆

anchor
anchor United States
2017/10/17 下午 04:42:13 #

Thank you for your article. Great.

回覆

sletrokor
sletrokor United States
2017/10/17 下午 10:13:44 #

Great article post.Much thanks again. Want more.

回覆

VigRx Plus Review
VigRx Plus Review United States
2017/10/19 上午 09:17:38 #

Really appreciate you sharing this blog post.Much thanks again. Keep writing.

回覆

go right here
go right here United States
2017/10/19 下午 08:25:17 #

I think this is a real great post.Thanks Again.

回覆

carte grise en ligne
carte grise en ligne United States
2017/10/21 上午 09:25:00 #

A round of applause for your blog. Keep writing.

回覆

phentaslim review
phentaslim review United States
2017/11/3 上午 09:04:36 #

Major thanks for the blog post. Fantastic.

回覆

exercise for spinal stenosis
exercise for spinal stenosis United States
2017/11/15 上午 10:40:47 #

Looking forward to reading more. Great post.Really looking forward to read more. Really Great.

回覆

fashion
fashion United States
2017/11/24 上午 12:49:05 #

Thanks so much for the blog post.Much thanks again. Really Cool.

回覆

Chad Boonswang SEO
Chad Boonswang SEO United States
2017/11/27 上午 03:04:32 #

wow, awesome article post.Really looking forward to read more. Awesome.

回覆

car wrecker scammer
car wrecker scammer United States
2017/11/29 下午 07:28:25 #

Hey, thanks for the blog post.

回覆

porno
porno United States
2017/12/1 下午 07:33:59 #

Awesome blog post.Really thank you!

回覆

Initial coin offering
Initial coin offering United States
2017/12/3 上午 07:44:08 #

I really enjoy the blog. Really Cool.

回覆

free android porn
free android porn United States
2017/12/5 下午 12:35:31 #

Thanks again for the blog article.Really looking forward to read more. Great.

回覆

I really enjoy the blog article.Really thank you! Great.

回覆

I cannot thank you enough for the article post.Really looking forward to read more. Really Great.

回覆

helpful hints
helpful hints United States
2017/12/14 下午 07:11:18 #

Thanks so much for the blog post.Really thank you! Great.

回覆

Christmas Music
Christmas Music United States
2017/12/15 上午 01:46:31 #

Thanks-a-mundo for the article.Really thank you! Want more.

回覆

canon drivers
canon drivers United States
2017/12/16 下午 08:49:03 #

Really appreciate you sharing this article post.Thanks Again. Keep writing.

回覆

tips lose weight
tips lose weight United States
2017/12/17 上午 03:13:15 #

Hey, thanks for the blog post.Really thank you! Want more.

回覆

A big thank you for your blog article.Much thanks again. Great.

回覆

IT
IT United States
2017/12/17 下午 08:23:25 #

I really like and appreciate your post.Much thanks again.

回覆

canon drivers
canon drivers United States
2017/12/23 上午 09:08:41 #

Thank you ever so for you blog post.Really looking forward to read more. Really Great.

回覆

Darwin Horan
Darwin Horan United States
2017/12/23 下午 07:36:50 #

A round of applause for your article post.Thanks Again. Keep writing.

回覆

driver hp
driver hp United States
2017/12/25 下午 09:06:32 #

Muchos Gracias for your blog article.Really thank you! Fantastic.

回覆

رگ خواب
رگ خواب United States
2017/12/26 上午 03:53:10 #

Im thankful for the blog.Much thanks again. Cool.

回覆

canon drivers
canon drivers United States
2017/12/27 下午 08:24:02 #

I really enjoy the blog post. Will read on...

回覆

hp drivers
hp drivers United States
2018/1/2 上午 10:59:05 #

I loved your article post.Thanks Again. Awesome.

回覆

click this
click this United States
2018/1/2 下午 07:07:33 #

wow, awesome article post.Much thanks again. Great.

回覆

printer drivers
printer drivers United States
2018/1/3 上午 10:22:14 #

Very neat article.Really looking forward to read more. Awesome.

回覆

online pokies for real money
online pokies for real money United States
2018/1/4 下午 09:07:48 #

Looking forward to reading more. Great blog post. Will read on...

回覆

hp driver
hp driver United States
2018/1/5 下午 03:54:16 #

Muchos Gracias for your article post. Much obliged.

回覆

FBA
FBA United States
2018/1/6 上午 07:58:37 #

Thanks for the blog.Really looking forward to read more. Cool.

回覆

web hosting
web hosting United States
2018/1/10 下午 02:15:03 #

Fantastic post.Really thank you! Cool.

回覆

wtS2slN5jSB
wtS2slN5jSB United States
2020/9/17 上午 06:58:10 #

753413 314693I don�t normally look at these types of internet sites (I�m a pretty modest person) - but even though I was a bit shocked as I was reading, I was surely a bit excited as effectively. Thanks for producing my day 904201

回覆

新增評論




  Country flag
biuquote
  • 評論
  • 線上預覽
Loading






NET Magazine國際中文電子雜誌

NET Magazine國際中文電子版雜誌,由恆逸資訊創立於2000,自發刊日起迄今已發行超過500篇.NET相關技術文章,擁有超過40000名註冊讀者群。NET Magazine國際中文電子版雜誌希望藉於電子雜誌與NET Developer達到共同學習與技術新知分享,歡迎每一位對.NET 技術有興趣的朋友們多多支持本雜誌,讓作者群們可以有持續性的動力繼續爬文。<請加入免費訂閱>

月分類Month List