SQL Server 2014 In Memory OLTP Overview
-
Upload
masayuki-ozawa -
Category
Documents
-
view
1.099 -
download
1
Transcript of SQL Server 2014 In Memory OLTP Overview
B24:SQL Server
SQL Server 2014
In Memory OLTP OverviewMicrosoft MVP for SQL Server
Masayuki Ozawa
はじめに
db tech showcase 東京 2013
本資料は SQL Server 2014 CTP2 をベースに作成しています。そのため、製品版では動作が変わる可能性があります。あらかじめご了承ください。
2
自己紹介
db tech showcase 東京 20133
フリーランスエンジニアとして SQL Server の案件を中心に従事しています 案件等で協力できることがありましたらお声掛けいただけると幸いです
Microsoft MVP for SQL Server (July 2011 - June 2014) コミュニティやブログで SQL Server の情報を発信
コミュニティ活動
SQL Server : SQLTO (http://sqlto.net)
Azure : JAZUG (http://r.jazug.jp/)
ブログ : SE の雑記 (http://engineermemo.wordpress.com)
In Memory OLTP
db tech showcase 東京 20134
DEMO
db tech showcase 東京 2013
いきなりですが
5
システム要件
db tech showcase 東京 2013
CPU プロセッサで cmpxchg16b をサポートしている必要がある
メモリ 推奨される最大サイズは 256 GB まで
Edition
Enterprise Edition x64 32 ビットのエディションではサポートしていない
SQL Server 2014 の各エディションがサポートする機能http://msdn.microsoft.com/ja-jp/library/cc645993(v=sql.120).aspx
AlwaysOn 可用性グループと組み合わせることも可能
メモリ最適化テーブルを使用するための要件http://msdn.microsoft.com/ja-jp/library/dn170449(v=sql.120).aspx
6
テーブルの種類
db tech showcase 東京 2013
ディスクベーステーブル (Disk Based Table)
従来からのテーブル
使用するデータのみディスクからメモリ上に格納
メモリが不足した場合はメモリからデータをキャッシュアウトし領域を確保
データはディスクに書き込まれ永続化される
メモリ最適化テーブル (Memory Optimized Table)
Code Name “Hekaton” (→ Hecto : 100 倍) と呼ばれていた機能
全てのデータをメモリ上に格納
メモリが不足した場合のデータのキャッシュアウトはない
永続化 / 非永続化の 2 種類を選択可能
7
メモリのサイズが不足すると
db tech showcase 東京 2013
メモリが不足するとデータ操作ができなくなる デフォルトでは max server memory の 80% まで利用される
リソースガバナーを使用することで最大メモリを調整できる 以下は上限を 50% にするための設定
CREATE RESOURCE POOL Pool_Hekaton WITH (MAX_MEMORY_PERCENT = 50);
EXEC sp_xtp_bind_db_resource_pool 'TESTDB', 'Pool_Hekaton‘
EXEC sp_xtp_unbind_db_resource_pool 'TESTDB‘
How to: Bind a Database with Memory Optimized Tables to a Resource Poolhttp://msdn.microsoft.com/ja-jp/library/dn465873(v=sql.120).aspx
8
基本構成
db tech showcase 東京 20139
ファイル構成
db tech showcase 東京 2013
データベース
トランザクションログ
(ldf)
ディスクベーステーブル用
ファイルグループ
(mdf/ndf)
メモリ最適化テーブル用
ファイルグループ
(Checkpoint File)
10
基本的な構成要素
db tech showcase 東京 2013
データ データの実体はメモリ上に格納される
トランザクションログ ディスクベーステーブルと同様のログファイルを使用
論理ログレコードとして処理し、コミット時にファイルにログを書き込み
ログの書き込み負荷は通常のテーブルより小さい
メモリ最適化テーブル用ファイルグループ データを永続化するために使用 (チェックポイントファイル)
データの実体ではなく、データの永続化をするために使用 データファイル : 追加されたデータを格納 (INSERT ONLY)
ページサイズ = 256 KB
1 ファイル : 128MB / 最大 4,096 = 512GB
デルタファイル : 削除された行の管理情報を格納 4 KB 書き込み ファイルサイズは固定されていない
データファイルとデルタファイルはペアになっている
ストリームベースのシーケンシャル I/O により処理を実施
11
同時実行制御
db tech showcase 東京 201312
ロック / ラッチフリーモデル
db tech showcase 東京 2013
READCOMMITTED ではなくSNAPSHOT がデフォルト
In Memory OLTP 向けのスナップショット分離モデルを採用
SNAPSHOT / REPEATABLE READ / SERIALIZABLE が利用可能
REPEATABLE READ / SERIALIZABLE はネイティブコンパイルストアドプロシージャで利用
メモリ最適化テーブルでのトランザクション
http://msdn.microsoft.com/ja-jp/library/dn133169(v=sql.120).aspx
同時更新の制御は楽観的同時実行制御
先に更新されたほうが優先
Check & Update (CMPXCHG : Compare and Exchange) という CPU 命令を使用
これを実現するために cmpxchg16b のサポートが必要となる
13
楽観的同時実行制御
db tech showcase 東京 2013
レコード変更の競合は楽観的同時実行制御で管理される
先に更新された内容が優先され、後から更新されたものはエラーとなる
更新 / 削除の競合
追加の競合
14
エラーのタイミング
db tech showcase 東京 201315
Session A Session B
BEGIN TRAN BEGIN TRAN
UPDATE SET
WITH (SNAPSHOT)
UPDATE SET
WITH (SNAPSHOT)
COMMIT TRAN
COMMIT TRAN
Error
41302
3998
Session A Session B
BEGIN TRAN BEGIN TRAN
INSERT INTO
INSERT INTO
COMMIT TRAN
COMMIT TRANError
41325
UPDATE INSERT
DEMO
db tech showcase 東京 201316
In Memory OLTP の同時実行制御
利用するための流れ
db tech showcase 東京 201317
メモリ最適化テーブルを使用する手順
db tech showcase 東京 2013
(ネイティブコンパイルストアドプロシージャの作成)
メモリ最適化テーブルを作成
メモリ最適化テーブル用のファイルグループを作成
設定を有効→自動的にメモリ最適化テーブルとはならない。利用するための手順を踏む必要がある
18
ファイルグループの作成
db tech showcase 東京 2013
ALTER DATABASE [Hekaton]
ADD FILEGROUP [HekatonFG]
CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE [Hekaton]
ADD FILE ( NAME = N'Hekatn_FS', FILENAME =
N'F:¥Hekaton_FS¥Hekatn_FS' ) TO FILEGROUP
[HekatonFG]
19
メモリ最適化テーブルの作成
db tech showcase 東京 2013
DURABILITY (データの永続化) は 2 種類
SCHEMA_AND_DATA (スキーマとデータを永続化)
SCHEMA_ONLY (スキーマのみを永続化)
インデックスは 2 種類
ポイント参照用のハッシュインデックス (NONCLUSTERED HASH)
範囲検索用の非クラスター化インデックス (NONCLUSTERED)
CREATE TABLE dbo.MemoryTable
(
c1 int NOT NULL,
c2 int NOT NULL INDEX NCIX_MemTable_c2 NONCLUSTERED(c2),
c3 uniqueidentifier,
CONSTRAINT PK_MemoryTable_c1 PRIMARY KEY NONCLUSTERED HASH (c1) WITH (BUCKET_COUNT = 100000),
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO
20
テーブルの制限 1/2
db tech showcase 東京 2013
使用できるデータ型に制限がある BLOB (nvarchar(max) / varbinary(max)) はサポートされていない
列の最大サイズは 8,060 バイト サポートされるデータ型
http://msdn.microsoft.com/ja-jp/library/dn133179(v=sql.120).aspx
IDENTITY / UNIQUE 制約 / CHECK 制約 / FOREIGN KEY 制約は非サポート IDENTITY の代わりにシーケンスを使用する
PRIMARY KEY による一意制約は設定できる (SCHEMA_AND_DATA では必須)
サポートされていない Transact-SQL の構造http://msdn.microsoft.com/ja-jp/library/dn246937(v=sql.120).aspx
文字列データの制約 インデックスを設定する文字列型 ((n)char / (n) varchar) は BIN2 照合順序を利用
非 Unicode 文字列 (char / varchar) は CP1252 (Latin1_General 等) を利用
SELECT * FROM fn_helpcollations() WHERE COLLATIONPROPERTY(name,'codepage') = 1252
21
テーブルの制限 2/2
db tech showcase 東京 2013
統計情報の自動更新は無効
統計情報のメンテナンスは手動で行う必要がある
テーブル作成後の定義の変更は不可
列の追加 / データ型の変更
バケットカウントのサイズ変更
インデックスの定義変更 / 追加
テーブルの定義を変更したい場合は、一度データを退避しテーブルを再作成してデータを再ロードする
SQL Server によるインメモリ OLTP のサポート
http://msdn.microsoft.com/ja-jp/library/dn133189(v=sql.120).aspx
Transact-SQL によるインメモリ OLTP のサポート
http://msdn.microsoft.com/ja-jp/library/dn133180(v=sql.120).aspx
22
ハッシュインデックス
db tech showcase 東京 2013
特定の行を検索 (Seek) するのに向いている
範囲検索には不向き Table Scan / Index Scan (全件操作) になる可能性が高い
23
ハッシュインデックのバケットカウント
db tech showcase 東京 2013
ハッシュインデックスを格納するためにメモリ上に存在する領域 (バケツ)
1 バケット = 8 バイト
2 の累乗に自動的に切り上げられる
1,000 と設定した場合は 1,024 に切り上げられる
sys.hash_indexes で実際に設定された値を確認できる
設定した値は格納したデータ件数に関わらず固定でメモリが確保される
ハッシュインデックスのサイズ = バケットカウント ×ポインターサイズ
ハッシュ インデックス
バケット カウントインデックス キー 項目 ハッシュ
24
レンジインデックス
db tech showcase 東京 2013
Bw-Tree 構造を使用したインデックス
ロック/ラッチフリーのツリー構造
特定の範囲を検索するのに向いている
インデックスのソート順であればソートコストはかからないが逆順で取得した場合はソートコストがかかる
25
インデックスのソート順 インデックスのソートの逆順
インデックスの種類によるメモリ使用量の違い
db tech showcase 東京 201326
SELECT * FROM sys.dm_db_xtp_table_memory_stats
WHERE object_id = OBJECT_ID(N'MemoryTable')
50 万件のデータ挿入前後のメモリ使用量 (上 : データ有 / 下 : データ無)
ハッシュインデックス
非クラスター化インデックス
データの永続化
db tech showcase 東京 201327
データの永続化
db tech showcase 東京 2013
メモリ
メモリ最適化テーブル
データの変更
ディスク
ログファイル
データファイル
デルタファイル
コミット時にリカバリに必要となるログのみ
ログファイルに書き込み(WAL ではない)
SCHEMA_ONLY の場合は最小限のログを書き込み
バックグラウンドのチェックポイントによりシーケンシャル Writesys.fn_dblog / sys.fn_dblog_xtp
28
データファイル / デルタファイルによりデータを永続化
ログの書き込みをタイミングを考慮すると…
db tech showcase 東京 201329
SET NOCOUNT ONGODECLARE @i bigint = 1)WHILE (@i <= 100000)BEGIN
INSERT INTO HekatonTable VALUES(@i, ‘AAAAA’)SET @i += 1
END
SET NOCOUNT ONGOBEGIN TRANWHILE (@i <= 100000)BEGIN
INSERT INTO HekatonTable VALUES(@i, ‘AAAAA’)SET @i += 1
ENDCOMMIT TRAN
チェックポイントファイルの構造
db tech showcase 東京 2013
sys.dm_db_xtp_checkpoint_files
データファイル
Max 128MB/file
256KB Page Size
デルタファイル
Write 4KB Page
チェックポイントファイルペア
Timestamp (INSERT) Table ID Row ID Row Payload
Timestamp (INSERT) Table ID Row ID Row Payload
Timestamp (INSERT) Table ID Row ID Row Payload
Timestamp (INSERT) Table ID Row ID Row Payload
Timestamp (INSERT) Timestamp (DELETE) Row ID
Timestamp (INSERT) Timestamp (DELETE) Row ID
Timestamp (INSERT) Timestamp (DELETE) Row ID
Timestamp (INSERT) Timestamp (DELETE) Row ID
複数のチェックポイントファイルはマージされることでファイルの最適化が行われる通常は自動で実行されるが、手動で実行する場合は、sp_xtp_merge_checkpoint_files でファイルをマージ
30
サービス起動時の処理
db tech showcase 東京 2013
永続化しているメモリ最適化テーブルはサービスの起動時にチェックポイントファイルの内容をメモリにロードする
メモリにロードが終わるまではデータベースは復旧中になる
メモリ
メモリ最適化テーブル
デルタマップ(デルタファイルの内容)
デルタマップ(デルタファイルの内容)
データファイル データファイル
削除レコードをフィルタしてロード
31
データの永続化とログ書き込み
db tech showcase 東京 201332
メモリ上のデータの構造
db tech showcase 東京 201333
レコードの構成
db tech showcase 東京 2013
レコードの格納単位は行ベース 行ヘッダーとペイロード (行データ) で構成されている
行ヘッダーにタイムスタンプを保持し、レコードの有効期限を管理 最新のデータはエンドのタイムスタンプが ∞ (infinity)
ステートメント ID は自トランザクションで変更したデータを識別するためのもの 同一のトランザクションではステートメント ID が同じになる
インデックスポインターは連続したデータのチェーンの次の行を示すポインター
開始 TS 終了 TSステートメント
ID
インデックスリンクカウント
インデックスポインター
8 バイト ×インデックス数8 バイト 8 バイト 4 バイト 2 バイト
行ヘッダー ペイロード
34
データ変更処理の実装
db tech showcase 東京 2013
削除 (DELETE)
レコードの終了のタイムスタンプを設定
削除されたレコードのメモリはガベージコレクタ (GC) によって回収
追加 (INSERT)
新規のレコードを追加
変更 (UPDATE)
DELETE & INSERT により変更後のデータを作成
35
テーブルの構造
db tech showcase 東京 2013
Y
ハッシュ インデックス 開始 TS 終了 TSインデックスポインター
ペイロード
200 300 Yamada
100 ∞ null Yamashita
300 ∞ Yamada
36
メモリサイズ
db tech showcase 東京 2013
メモリサイズの考え方 データで使用するメモリサイズ
行ヘッダ + (インデックスポインターサイズ×インデックス数) + データサイズ
ハッシュインデックスで使用するメモリサイズ (バケットカウント× 8) × インデックス数 (固定サイズ)
全件更新をした場合には瞬間で更新前 / 更新後のデータ分メモリが利用される GC により削除データのメモリは回収されるが、動作までは削除データのメモリは確保されている
メモリの使用状況は sys.dm_db_xtp_table_memory_stats で確認可能
メモリ最適化テーブルに移行する際のメモリ要件の推定http://msdn.microsoft.com/ja-jp/library/dn282389(v=sql.120).aspx
37
Native Compile
db tech showcase 東京 201338
ネイティブコンパイル
db tech showcase 東京 2013
従来からの T-SQL でもメモリ最適化テーブルにアクセスすることが可能だが、最高のパフォーマンスを出すためにはネイティブコンパイルされたストアドプロシージャを利用する
ネイティブコンパイルされるオブジェクトは以下の 2 種類 メモリ最適化テーブル
MEMORY_OPTIMIZED = ON のテーブル SQL Server によるインメモリ OLTP のサポート
http://msdn.microsoft.com/ja-jp/library/dn133189(v=sql.120).aspx
Transact-SQL によるインメモリ OLTP のサポートhttp://msdn.microsoft.com/ja-jp/library/dn133180(v=sql.120).aspx
ネイティブコンパイルされたストアドプロシージャ WITH NATIVE_COMPILATION を有効にしたストアドプロシージャ ネイティブでコンパイルされたストアド プロシージャの概要
http://technet.microsoft.com/ja-jp/library/dn133184(v=sql.120).aspx
39
ネイティブコンパイルストアドプロシージャ
db tech showcase 東京 2013
CREATE PROCEDURE dbo.usp_NativeSP_Persist
@param1 int = 0WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNERAS BEGIN ATOMIC WITH
(
TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'Japanese‘
-- 言語は sys.syslanguages から取得可能)
DECLARE @i int = 1
WHILE (@i <= @param1)
BEGIN
INSERT INTO dbo.PersistTable VALUES(@i, RAND() * 1000, N‘dbts2013東京', N‘dbts2013)
SET @i += 1
END
END
GO
40
ネイティブコンパイルストアドの制限
db tech showcase 東京 2013
メモリ最適化テーブルへのアクセスに制限される ディスクベーステーブルへのアクセスはできない
メモリ最適化テーブルと通常のテーブルの JOIN といったことはできない メモリ最適化テーブル間は JOIN できるが OUTER JOIN はサポートされていない
使用できる関数やデータ型の変換に制限がある
ストアドプロシージャ内のトランザクションは BEGIN ATOMIC で担保 BEGIN TRAN / COMMIT TRAN / ROLLBACK TRAN は使用できない
文字列の比較、並べ替えは BIN2 照合順序が必要 char / varchar を使用する場合は合わせて CP1252 が必要
サポートされていない Transact-SQL の構造http://msdn.microsoft.com/ja-jp/library/dn246937(v=sql.120).aspx
ネイティブでコンパイルされたストアドプロシージャの概要http://technet.microsoft.com/ja-jp/library/dn133184(v=sql.120).aspx
41
コンパイルされたファイル
db tech showcase 東京 2013
xtp ディレクトリにコンパイルされた DLL が格納される
DLL は SQL Server にモジュールとしてロードされる
各モジュールのロードタイミングは以下のようになる
メモリ最適化テーブルのロード テーブルの作成 / データベースをオンラインにする際にコンパイル
ネイティブコンパイルされたストアドプロシージャーのロード
ストアドプロシージャの作成 / 初回実行時にコンパイル
最新の統計情報を使用してコンパイルをしたい場合にはデータを挿入後に、統計情報を更新し、その後にネイティブコンパイルされたストアドプロシージャを作成
42
In Memory OLTP のモジュールロード
db tech showcase 東京 201343
参考資料 1/2
db tech showcase 東京 2013
SQL Server 2014 自習書シリーズhttp://www.microsoft.com/ja-jp/sqlserver/2014/technology/self-learning.aspx
Books Online
インメモリ OLTP (インメモリ最適化)http://msdn.microsoft.com/ja-jp/library/dn133186(v=sql.120).aspx
Blog
In-Memory OLTP: High Availability for Databases with Memory-Optimized Tableshttp://blogs.technet.com/b/dataplatforminsider/archive/2013/11/05/in-memory-oltp-high-availability-for-databases-with-memory-optimized-tables.aspx
The 411 on the Microsoft SQL Server 2014 In-Memory OLTP Blog Serieshttp://blogs.technet.com/b/dataplatforminsider/archive/2013/10/15/the-411-on-the-microsoft-sql-server-2014-in-memory-oltp-blog-series.aspx
SQL Server 2014 In-Memory Technologies: Blog Series Introductionhttp://blogs.technet.com/b/dataplatforminsider/archive/2013/06/26/sql-server-2014-in-memory-technologies-blog-series-introduction.aspx
Getting Started with SQL Server 2014 In-Memory OLTPhttp://blogs.technet.com/b/dataplatforminsider/archive/2013/06/26/getting-started-with-sql-server-2014-in-memory-oltp.aspx
Tech Ed
Microsoft SQL Server In-Memory OLTP: Overview of Project "Hekaton“http://channel9.msdn.com/Events/TechEd/NorthAmerica/2013/DBI-B204
Microsoft SQL Server In-Memory OLTP Project "Hekaton": App Dev Deep Divehttp://channel9.msdn.com/Events/TechEd/NorthAmerica/2013/DBI-B307
Microsoft SQL Server In-Memory OLTP Project “Hekaton”: Management Deep Dive http://channel9.msdn.com/Events/TechEd/NorthAmerica/2013/DBI-B308
44
参考資料 2/2
db tech showcase 東京 201345
Sample Database
SQL Server 2014 CTP2 In-Memory OLTP Sample, basedhttps://msftdbprodsamples.codeplex.com/releases/view/114491
Case Study
bwin.partyhttp://www.microsoft.com/casestudies/Microsoft-SQL-Server-2014/bwin.party/Gaming-Site-Can-Scale-to-250-000-Requests-Per-Second-and-Improve-Player-Experience/710000003117
TPP http://www.microsoft.com/casestudies/Microsoft-SQL-Server-2014/TPP/Clinical-Software-Easily-Supports-Thousands-of-New-Users-and-Helps-Doctors-Save-Lives/710000003430
SBI Liquidity Market http://www.microsoft.com/casestudies/Microsoft-SQL-Server-2014/SBI-Liquidity-Market/Leading-Japanese-Financial-Firm-Accelerates-Trading-Platform-with-In-Memory-OLTP/710000003429
White Paper
Main-Memory Databaseshttp://research.microsoft.com/en-us/projects/main-memory_dbs/
SQL Server In-Memory OLTP Internals Overview for CTP2http://download.microsoft.com/download/5/F/8/5F8D223F-E08B-41CC-8CE5-95B79908A872/SQL_Server_2014_In-Memory_OLTP_TDM_White_Paper.pdf