Pleasanter Technical Manual

テーマ

SQL Server のインメモリ OLTP を使う（CodeDefiner の改修案） ​

第1版作成 最終更新 （日本時間）
確認バージョン1.5.1.01.5.8.1

本体の標準機能ではありません

CodeDefiner が作るのはディスクベースの通常のテーブルだけで、メモリ最適化テーブルを作る設定はありません。このページは、SQL Server のインメモリ OLTP を一部のテーブルに使う場合の設計メモです。

前提にした現行実装 ​

  • SQL Server のテーブルは CreateTable.sql で create table した後、CreatePk.sql で primary key clustered を後から付けます（CreateTable.sql、CreatePk.sql）。インデックスも CreateIx.sql で後から作ります。
  • 列の構成が変わったテーブルは、新しいテーブルを作って MigrateTable.sql / MigrateTableWithIdentity.sql でデータを移し、名前を付け替えます（CodeDefiner）。
  • 全テーブルに共通の Comments 列は nvarchar(max) です（_Bases_Comments.json）。
  • Binaries の Bin・Thumbnail・Icon は image 型です（Binaries_Bin.json）。
  • Items.FullText と Binaries.Bin には全文索引が張られます（CreateFullText.sql（SQL Server））。
  • テーブルは 1 種類につきベース・_deleted・_history の 3 つが作られます（データベースのテーブル構成）。

メモリ最適化テーブルの制約 ​

SQL Server 2014 以降の機能で、Pleasanter の列定義（nvarchar(max) など）をそのまま使うには 2016 以降が前提です。

項目通常のテーブルメモリ最適化テーブル
同時実行制御ロック・ラッチ楽観的（行のバージョン）
主キークラスター化か非クラスター化非クラスター化だけ（ハッシュか BW-tree）
インデックス後から create index できるcreate table の中で定義する
LOB（nvarchar(max) など）使える2016 以降で使える（行外に格納）
image 型使える使えない（varbinary(max) にする）
全文索引使える使えない
alter table使える2016 以降で列の追加・削除・変更ができる
永続性―SCHEMA_AND_DATA（永続）か SCHEMA_ONLY（再起動で消える）
ファイルグループ不要MEMORY_OPTIMIZED_DATA のファイルグループが必要

対象にするテーブル ​

全テーブルではなく、読み書きが多く、制約に当たりにくいテーブルに絞ります。

対象理由注意
Sessionsリクエストのたびに読み書きされる（セッション管理の実装）Value が nvarchar(max)
Permissionsアクセス権の判定のたびに読まれる
Statuses状態の更新が多い
Orders一覧の並び順で読まれるData が nvarchar(max)
Linksリンクの解決で読まれるSubset が nvarchar(max)
LoginKeys認証のたびに読まれるTenantNames が nvarchar(max)
対象にしない理由
Items全文索引が使えなくなる
Binariesimage 型があり、データが大きい
Sitesnvarchar(max) の列が多く、更新は少ない
SysLogsINSERT が中心でデータ量が大きい
Results / Issues件数が多く、Body・Comments が nvarchar(max)
Quartz.NET のテーブル外部ライブラリが管理する
_history / _deleted読まれる頻度が低い

nvarchar(max) の列は行外に格納されるため、その列を読むときはメモリ最適化の効果が薄れます。

設定案 ​

Rds.json に SQL Server 用の設定を足します。新しい Dbms の値は不要です。

json
{
    "MemoryOptimized": {
        "Enabled": false,
        "Tables": ["Sessions", "Permissions", "Statuses", "Orders", "Links", "LoginKeys"],
        "Durability": "SCHEMA_AND_DATA",
        "IncludeDeletedTables": false,
        "IncludeHistoryTables": false
    }
}
項目内容
Enabledインメモリ OLTP を使うか
Tablesメモリ最適化にするテーブル（ベースの名前）
DurabilitySCHEMA_AND_DATA か SCHEMA_ONLY
IncludeDeletedTables / IncludeHistoryTables_deleted / _history も対象にするか

改修箇所 ​

箇所改修内容
Implem.ParameterAccessor/Parts/Rds.csMemoryOptimized の設定クラスと、テーブル名と TableTypes から対象かを返す判定を足す
App_Data/Definitions/Sqls/SQLServer/メモリ最適化テーブルを作る SQL（主キーとインデックスを create table 内に書き、with (memory_optimized = on, durability = ...) を付ける）と、ファイルグループを足す SQL を追加する
Functions/Rds/RdsConfigurator.csDB の作成・更新時に MEMORY_OPTIMIZED_DATA のファイルグループを足す（Provider が Local のときだけ）
Functions/Rds/TablesConfigurator.csテーブルを作るときに対象なら新しい SQL を使う
Functions/Rds/Parts/Indexes.csインデックスを create table 内の定義として出力する
Functions/Rds/Parts/Tables.cs既存のテーブルがメモリ最適化かを sys.tables.is_memory_optimized で調べ、設定と違えば作り直す

ファイルグループの追加は次のような SQL です。

sql
alter database [Implem.Pleasanter]
add filegroup [InMemoryData] contains memory_optimized_data;

alter database [Implem.Pleasanter]
add file (name = N'InMemoryData', filename = N'C:\Data\InMemoryData')
to filegroup [InMemoryData];

通常のテーブルとの切り替え ​

図を読み込み中…

どちらの向きも、新しいテーブルを作って insert ... select でデータを移し、sp_rename で名前を付け替えます。既存の MigrateTable.sql / MigrateTableWithIdentity.sql と同じ手順です。メモリ最適化テーブルの列の変更は、SQL Server 2016 以降なら alter table でもできます。

インデックスの種類 ​

ハッシュインデックスの BUCKET_COUNT は行数の 1〜2 倍が目安ですが、行数は環境ごとに違います。範囲検索や order by が多いことも考えると、既定は BW-tree（非クラスター化）にし、ハッシュは設定で選べるようにするのが扱いやすくなります。

注意点 ​

  • Azure SQL Database では Premium か Business Critical の層でインメモリ OLTP が使え、ファイルグループは自動で管理されます。Amazon RDS for SQL Server にも制限があります。Provider が Local 以外のときはファイルグループの追加を飛ばします。
  • メモリ最適化テーブルと通常のテーブルを 1 つのトランザクションで使う場合、メモリ最適化テーブル側で REPEATABLE READ と SERIALIZABLE は使えません。Pleasanter は分離レベルを指定していないので、既定の READ COMMITTED で動きます。
  • PostgreSQL と MySQL には同じ機能がありません。CodeDefiner の migrate は insert ... select でデータを移すので、移行元がメモリ最適化テーブルでもそのまま読めます。移行先では通常のテーブルとして作ります。

関連ページ ​

変更履歴

第1版セッションの仕組みとデータベースのテーブル構成の解説を追加し、性能とスケールアウトの説明を修正