はじめに

本ドキュメントは、Windows エンジニアが、Linux 上で動作する SQL Server (SQL Server on Linux) を操作するために必要となるスキルアップデートについての情報を記載したものとなります。
(ドキュメント作成者が Ubuntu 16.04 LTS で検証をしているため、公開当初は Ubuntu ベースの情報となっています)

SQL Server on Linux の SQL Server 部分のスキルについては、Windows / Linux 版で共通となりますが、OS 部分に関しては、Windows エンジニアのスキルアップデートが必要になるものが多数あります。

本ドキュメントが、今まで Windows をメインに触ってきたが、Linux の SQL Server を触る必要がある / 興味を持ったエンジニアの方の一助になれば幸いです。

目次

• はじめに
• 目次
• サービス管理
  • サービス管理のファイル
  • SQL Server の設定変更
  • ファイアウォール
    • Ubuntu
    • RHEL
  • リモート接続
    • リモート管理
    • ファイルコピー
  • ソフトウェア更新
• ベストプラクティス
  • SQL Server on Linux
    • SQL Server の設定
      • PROCESS AFFINITY の指定
      • tempdb のデータファイルの分割
      • mssql-conf ツールを使用したメモリ設定
    • Linux の設定
      • CPU
        • CPU の電力制御のユーティリティ
        • CPU クロックのガバナー
        • パフォーマンスと電源消費効率のバランス
        • CPU クロックの下限
        • C-State
      • ディスク
        • Read-Ahead のブロックサイズ
        • sysctl によるカーネルパラメーターの変更
      • NUMA
        • 複数 NUMA ノード環境での自動 NUMA バランシングの無効化
      • 仮想アドレス空間
        • メモリマップ数の上限の変更 (65536 (64KB) -> 262144 (256KB))
      • マウントオプション
        • SQL Server のデータ/ログファイルのファイルシステムのマウントオプションの設定 (relatime -> noatime)
      • Huge Page
        • 透過的な Huge Page の有効化
      • Swap ファイル
        • swap ファイルの適切な設定
      • 仮想マシンでの実行
        • 動的メモリ割り当て (Dynamic Memory) を使用しない
      • OOM Killer
        • OOM (Out of Memory) Killer という動作が存在していることの認識
  • Docker
  • 事前の IO 検証
• アカウント / グループ
• パフォーマンスモニタリング
  • コマンドライン
    • モニタリング
    • ディスク
    • メモリ
    • ネットワーク
  • モニタリングユーティリティ
• プロセス構成
• ディレクトリ構成
  • Windows のディレクトリとの比較
• ログファイル
• データディスクのマウント
• システムデータベースの操作
  • システムデータベースの再構築
  • システムデータベースの移動について
• 可用性
• オフラインインストール
  • RHEL
  • Ubuntu
• コマンド

サービス管理

Windows の場合、SQL Server は「Windows サービス」として管理が行われていましたが、Linux 版の場合は、Linux のシステム・サービスマネージャーである「systemd」で管理が行われている。

本章では、Linux の SQL Server のサービス管理を実施するために必要となるコマンド等を記載している。

サービス管理のファイル

SQL Server のサービス管理のファイルの実体は「/lib/systemd/system/mssql-server.service」となり、このファイルでサービス起動時の設定が行われる。

• 9.6. システムのユニットファイルの作成および変更

$ sudo systemctl enable mssql-server.service

を実行することで、「/etc/systemd/system/multi-user.target.wants/mssql-server.service」にシンボリックリンクが作成され、自動起動の設定が行われている。

SQL Server の設定変更

SQL Server on Windows では、SSMS / SQL Server 構成マネージャー / sp_configure を使用して設定の変更を行う。

SQL Server on Linux では、これらに加えて、一部の設定については「mssql-conf」を使用して設定を変更する。

• Configure SQL Server on Linux with the mssql-conf tool

「mssql-conf」で変更可能な設定については、他の方法で変更が可能だったとしても、このツールから変更を行わないと、SQL Server のサービスを再起動することで、初期化されてしまうので注意が必要である。
(SQL Server on Linux の基本部分はサンドボックス環境で動作しており、サービスの再起動を実施すると、初期状態に初期化されるた、ツールを使用して、起動時に設定を外部からインポートさせる必要がある)

ファイアウォール

Windows の場合、Windows Firewall で SQL Server のポート (TCP:1433 (SQL Server) / UDP : 1434 (SQL Server Browser) のアクセス開放を実施している。
Linux の場合、使用するディストリビューションに応じたファイアウォールにより、TCP 1433 (デフォルトインストール時) にアクセスするための設定を行う。

Ubuntu

Ubuntu 16.04 LTS はデフォルトは FW は無効

$ sudo ufw allow 1433/tcp 

ufwの基本操作

RHEL

4.5. ファイアウォールの使用

リモート接続

Windows の場合、「Remote Desktop」や、「クリップボード共有経由」等で管理やファイル転送を実施することが多い。
Linux の場合、「SSH」「SCP」を使用して、リモート管理やファイル転送を実施する。

リモート管理

インフラエンジニアじゃなくても押さえておきたいSSHの基礎知識

ファイルコピー

SCP (1)

ソフトウェア更新

# sudo apt update
# sudo apr upgrade

apt updateでパッケージ管理のデータベースを更新し、apt upgradeで実際にソフトウェアを更新する。

ベストプラクティス

Performance best practices and configuration guidelines for SQL Server 2017 on Linux

SQL Server on Linux

SQL Server の設定

PROCESS AFFINITY の指定

ALTER SERVER CONFIGURATION  
SET PROCESS AFFINITY NUMANODE = 0 TO <Max NUMA Node ID>

tempdb のデータファイルの分割

SQL Server on Linux は、インストール時の tempdb 分割が行われないため、インストール後に分割する。

mssql-conf ツールを使用したメモリ設定

$ sudo /opt/mssql/bin/mssql-conf set memory.memorylimitmb 4096
$ sud systemctl restart mssql-server

デフォルトでは SQL Server が使用するメモリは 80% に制限されているため、大容量のメモリを搭載している場合、残りの 20% のサイズによっては、上限緩和を検討する。
(本設定は SQL Server の max server memroy とは別の設定)

設定の解除

sudo /opt/mssql/bin/mssql-conf unset memory.memorylimitmb 
systemctl restart mssql-server

Linux の設定

• PASS Summit 2017 - SQL Server on Linux: DBA focused lessons learned from early deployments
• Inside SQL Server 2017 on Linux
• SQL Server 2017 on Linux - Administration
• How to safeguard SQL Server on Linux from OOM-Killer

CPU

CPU の電力制御のユーティリティ

# apt install -y cpufrequtils
# cpufreq-info

CPU クロックのガバナー

$ sudo cpupower frequency-info
$ sudo cpupower frequency-set -g performance

パフォーマンスと電源消費効率のバランス

# x86_energy_perf_policy -v 'performance'

CPU クロックの下限

tuned を使用して設定を実施

min_perf_pct=100

• 2.5. TUNED および KTUNE

C-State

C1 のみにする

$ sudo vi /etc/default/grub
====
GRUB_CMDLINE_LINUX_DEFAULT="intel_idle.max_cstate=1"
====
$ sudo update-grub
$ reboot

• How to set intel_idle.max_cstate=1

ディスク

Read-Ahead のブロックサイズ

# blockdev --report
# blockdev -v --setra 4096 /dev/sda

sysctl によるカーネルパラメーターの変更

• 設定の確認

  $ cat /proc/sys/kernel/sched_min_granularity_ns
  

$ sudo sysctl -a

• 設定の変更

  $ sudo sysctl -w kernel.sched_min_granularity_ns=10000000
  $ sudo sysctl -w kernel.sched_wakeup_granularity_ns=15000000
  $ sudo sysctl -w vm.dirty_ratio=40
  $ sudo sysctl -w vm.dirty_background_ratio=10
  $ sudo sysctl -w vm.swappiness=10
  $ sudo sysctl -p
  

NUMA

複数 NUMA ノード環境での自動 NUMA バランシングの無効化

$ sudo sysctl -w kernel.numa_balancing=0
$ sudo sysctl -p

仮想アドレス空間

メモリマップ数の上限の変更 (65536 (64KB) -> 262144 (256KB))

$ sudo sysctl -w vm.max_map_count=262144
$ sudo sysctl -p

マウントオプション

SQL Server のデータ/ログファイルのファイルシステムのマウントオプションの設定 (relatime -> noatime)

• 設定の確認

  # mount
  または、
  # cat /proc/mounts
  

• 設定の変更

  # vi /etc/fstab
  ====
  該当のボリュームに「noatime」オプションを追加する
  ====
  

• 3.9. RELATIME ドライブアクセス最適化
  ※国内のブログの検証結果では、realtime / noattime の変更による明確な性能差は確認できないという情報が多い

Huge Page

透過的な Huge Page の有効化

ほとんどの Linux 環境では有効になっている。

# cat /sys/kernel/mm/transparent_hugepage/enabled
# cat /proc/meminfo

• How to disable Transparent Huge Pages (THP) in Ubuntu 16.04LTS
• Red Hat Enterprise Linux 7 で transparent hugepages (THP) を無効にする
• Huge Page まとめ

Swap ファイル

swap ファイルの適切な設定

• swap ファイルの確認

# swapon
または、
# cat /proc/swaps

• swap の有効化

  # swapon /dev/dm-1
  

• Ubuntu 16.10 その79 - スワップ領域をパーティションからファイルに移行させるには

• 【 swapon 】スワップ領域を有効にする

• swap の無効化
  • 　【 swapoff 】スワップ領域を無効にする

    swapoff /dev/dm-1
    

    「swapoff failed: Cannot allocate memory 」のメッセージが表示された場合は、スワップファイルの内容をメモリに移動させることができない状態となっている

• swap ファイルのサイズ変更
  • swapサイズ変更手順(ファイル割当)

仮想マシンでの実行

動的メモリ割り当て (Dynamic Memory) を使用しない

仮想マシンのメモリ割り当てとして、動的メモリ割り当てによる可変的なメモリ割り当てを実行しない

OOM Killer

OOM (Out of Memory) Killer という動作が存在していることの認識

メモリ/スワップの枯渇の可能性が出た場合に、メモリを消費しているプロセスを停止させる動作

• OOM Killer に対しての考慮
  • mssql-conf で memory.memorylimitmb を適切に設定
  • swap ファイルのサイズを適切に設定
• OOM Killer の対象となりえるプロセスの確認

  # echo "-17" > /proc/[pid]/oom_adj
  # ps -e | grep "sqlservr" | awk '{system("echo -17 > /proc/"$1"/oom_adj")}'
  

  cron による設定

  # sudo echo '*/1 * * * * root ps -e | grep "sqlservr" | awk '\''{system("echo -1000 > /proc/"$1"/oom_score_adj")}'\'' > /dev/null 2>&1' > /etc/cron.d/sqlservr_oom
  または、
  # sudo echo '*/1 * * * * root ps -e | grep "sqlservr" | awk '\''{system("echo -17 > /proc/"$1"/oom_adj")}'\'' > /dev/null 2>&1' > /etc/cron.d/sqlservr_oom
  

• OOM Killer の設定の確認

  $ sudo syscat /var/log/syslog | grep Killedctl -r | grep "oom"
  

• OOM Killer のログの確認

  # cat /var/log/syslog | grep -i Killed
  

• Linux OOM Killerについて
• LinuxにおけるOOM発生時の挙動
• メモリ不足時に大事なプロセスが OOM Killer に殺されないようにする
• エラーログに対応する～OOM Killer編
• OOM Killer – How To Create OOM Exclusions in Linux
• Linux - OOM Killer の発動を抑制！
• linux/mm/oom_kill.c

Docker

• ローカルまたはリモートストレージ / コンテナーボリュームのマウントを検討
• Docker ボリュームストレージプラグインの活用
• Docker run の実行時に –cap-add sys_ptrace の活用

事前の IO 検証

• FIO / dd / Iometer 等による事前のディスク性能の検証
• SQL Server on Linux では、ファイルの瞬時初期化はデフォルトで有効となっている
• マウントしたドライブを使用している場合「/etc/fstab」の「noattime」の設定の有効化

アカウント / グループ

Windows の場合、アカウントについては「SQL Server のサービスを起動しているサービスアカウント」を基準に各種設定を考慮する必要がある。

Linux の場合、アカウント / グループともに「mssql」が使用されている。
バックアップや BULK INSERT をするファイルに対しては、「mssql」を基準に考慮する必要がある。

id mssql
uid=999(mssql) gid=999(mssql) groups=999(mssql)

「systemctl」からではなく、コマンドで起動する場合は次のようなコマンドを実行する。
「root」で起動した場合、一部ファイルのアクセス権が変更され、以降 mssql で起動しようとした場合にエラーとなる可能性があるため、コマンドラインからの起動は注意をする。

$ sudo -u mssql /opt/mssql/bin/sqlservr

パフォーマンスモニタリング

コマンドライン

モニタリング

• top
  • • [Linux] ある一定期間のtopコマンドの結果をファイルに出力する（備忘録）
  • topコマンドの使い方
• htop
  • htopコマンドで覚えておきたい使い方11個
• sar
  • sar(sysstat)によるボトルネック特定
• vmstat
  • Vmstat の出力結果はどのように解釈すれば良いですか?

ディスク

• iostat
  • iostat how do I set up a logging
  • iostat の await, svctm の 見かた、考え方(※10/30スライド修正)
  • iostat コマンドの読み方
• df
  • dfコマンドについてまとめました【Linuxコマンド集】
• lsblk

メモリ

• memstat
  • Linux のメモリー管理(メモリ－が足りない？,メモリーリークの検出/防止)
  • linuxにおけるメモリと関連コマンド（free, vmstat, top, sar）
  • Linux負荷監視コマンドまとめ
• sar
  • sar(sysstat)によるボトルネック特定
  • sarコマンドでLinuxサーバのシステムモニタリングを行う方法
  • sar (sysstat) on Ubuntu/Debian servers

    ネットワーク

• ifconfig
• ip
• netstat
  • Linuxでプロセスが何のポート使っているかを調べる
• 20 Linux System Monitoring Tools Every SysAdmin Should Know
• Linux Performance

モニタリングユーティリティ

• Nagios
• Collectd
• Telegraf
• Monitoring SQL on Linux
  • How the SQLCAT Customer Lab is Monitoring SQL on Linux • mssql-monitoringhttps://github.com/Microsoft/mssql-monitoring
• PSSDiag
  • Collecting performance data with PSSDIAG for SQL Server on Linux -LinuxPSSDiag
• sysstat
  • sysstat

プロセス構成

メインの PID で、SQL PAL Monitor が動作し、子プロセスで、SQL PAL / sqlservr.exe が動作する構成となっており Windows とは異なり 2 このプロセスが起動することになる。
SQL Server Agent / Full Text Search をインストールした場合、sqlservr のプロセス内で動作することになるため、個別にサービスを停止することはできない

ディレクトリ構成

Windows のディレクトリとの比較

• Filesystem Hierarchy Standard

ログファイル

データディスクのマウント

The systemd unit file for the SQL server should be locally extended by a dependency on the /datadir this will make sure the start is done after the mount and the unmount waits for the stop.
The most generic option to do this would be

RequiresMountsFor=/datadir

I think it automatically depend on the installation path of the scripts already.
Note that you do not need to modify the systemd unit files in the library directory but you can amend them in the

/etc/systemd/system/<sqlserver>.service

# systemctl show --no-pager mssql-server | grep "RequiresMountsFor"
# mkdir /lib/systemd/system/mssql-server.service.d
# vi /lib/systemd/system/mssql-server.service.d/mssql-server.conf
====
[Unit]
RequiresMountsFor=/mnt/backup
====
# systemctl daemon-reload
# systemctl show --no-pager mssql-server | grep "RequiresMountsFor"

• systemdでmount完了を待ってサービスを起動する

システムデータベースの操作

システムデータベースの再構築

# /opt/mssql/bin/sqlservr -c --setup --force-setup

システムデータベースの移動について

• tempdb / msdb / model : ALTER DATABASE で移動
• master : 移動することはできない

可用性

Windows の場合は、OS に含まれている Windows Server Failover Cluser (WSFC) を使用して、OS 側の可用性環境の構築を行い、その上で SQL Server の可用性環境を構築することがある。

Linux 環境では WSFC を利用することはできないため、OS の可用性環境を構築する手法として、2017/12 時点では OSS のソフトウェアである「Pacemaker」を利用した方法が、Microsoft 社からドキュメントで公開されている。
(今後、他のクラスターマネージャー/3rd 製品での対応等が行われる可能性もある)

Pacemaker の操作方法については、次の情報が参考となる。

• High Availability Add-On リファレンス
• 第1章 Pacemaker を使用した Red Hat High Availability クラスターの作成
• 第3章 pcs コマンドラインインターフェース
• 付録B pcs コマンドの使用例

オフラインインストール

RHEL

# yumdownloader --downloadonly --resolve --destdir=/home/user/offlineistall mssql-server

Ubuntu

apt-get download mssql-server
apt-cache depends mssql-server

• Offline install
• sqlunattended

コマンド