DB初期設定 - HTN20190109の日記

MySQL

(8.0.35)

OS: Rocky Linux 8.9
CPU : 2
メモリ : 4GB

-- 1. ラージページ設定

https://support.oracle.com/knowledge/Oracle%20Database%20Products/2118592_1.html

https://dev.mysql.com/doc/refman/8.0/en/large-page-support.html

-- 1.1 /proc/sys/vm/nr_hugepages

grep -i Hugepagesize /proc/meminfo

select @@innodb_buffer_pool_size;

select @@innodb_buffer_pool_size/2048/1024;

1600.00000000

-- 1.2 /proc/sys/vm/hugetlb_shm_group

cat /proc/sys/vm/hugetlb_shm_group
cat /etc/group | grep mysql

-- 1.3 /proc/sys/kernel/shmall

cat /proc/sys/kernel/shmall

-- 1.4 /proc/sys/kernel/shmmax

cat /proc/sys/kernel/shmmax

-- 1.5 /etc/sysctl.conf

sudo sysctl -w vm.hugetlb_shm_group=27
sudo sysctl -w vm.nr_hugepages=1600

vim /etc/sysctl.conf

vm.hugetlb_shm_group = 27 # set to a group number of which mysql is a member
vm.nr_hugepages = 1600 # set to number of huge pages desired

-- 1.6 /etc/security/limits.conf

vim /etc/security/limits.conf

mysql soft memlock unlimited
mysql hard memlock unlimited

-- 1.7 OS再起動
reboot

-- 1.8 Verify the huge pages settings

grep -i huge /proc/meminfo

-- 1.9 large_pagesの設定

vim /etc/my.cnf
large_pages = ON

systemctl restart mysqld

-- 2. バイナリログ場所の設定

https://dev.mysql.com/doc/refman/8.0/ja/using-systemd.html
https://dev.mysql.com/doc/refman/8.0/ja/replication-options-binary-log.html#option_mysqld_log-bin

mkdir -p /var/lib/mysql/binlog
chown mysql: /var/lib/mysql/binlog

vim /etc/sysconfig/mysql

MYSQLD_OPTS="--log-bin=/var/lib/mysql/binlog/bl_"

systemctl restart mysqld

show variables like '%log_bin%';

flush logs;

Oracle

(19c)

OS : CentOS7.6
CPU : 2
メモリ : 4GB

前提: 下記作業実施済
インストール、データベース作成、DBパラメータ設定

-- 1. HugePages設定

https://docs.oracle.com/cd/F19136_01/unxar/administering-oracle-database-on-linux.html#GUID-CC72CEDC-58AA-4065-AC7D-FD4735E14416

-- 1.1 HugePagesサポート確認

oracleユーザーとしてログイン

ulimit -l
grep Hugepagesize /proc/meminfo

-- 1.2 limits.confファイルのmemlock設定を編集

sudo vim /etc/security/limits.conf

* soft memlock 3842905
* hard memlock 3842905

現行RAMの90パーセント以上に設定 (KB単位)

-- 1.3 memlock設定確認

oracleユーザーとして再度ログイン

ulimit -l
grep Hugepagesize /proc/meminfo

-- 1.4 hugepages構成の推奨値を計算するスクリプトを作成、実行

cat <<-'EOF' > hugepages_settings.sh

#!/bin/bash
#
# hugepages_settings.sh
#
# Linux bash script to compute values for the
# recommended HugePages/HugeTLB configuration
# on Oracle Linux
#
# Note: This script does calculation for all shared memory
# segments available when the script is run, no matter it
# is an Oracle RDBMS shared memory segment or not.
#
# This script is provided by Doc ID 401749.1 from My Oracle Support
# http://support.oracle.com

# Welcome text
echo "
This script is provided by Doc ID 401749.1 from My Oracle Support
(http://support.oracle.com) where it is intended to compute values for
the recommended HugePages/HugeTLB configuration for the current shared
memory segments on Oracle Linux. Before proceeding with the execution please note following:
* For ASM instance, it needs to configure ASMM instead of AMM.
* The 'pga_aggregate_target' is outside the SGA and
you should accommodate this while calculating the overall size.
* In case you changes the DB SGA size,
as the new SGA will not fit in the previous HugePages configuration,
it had better disable the whole HugePages,
start the DB with new SGA size and run the script again.
And make sure that:
* Oracle Database instance(s) are up and running
* Oracle Database 11g Automatic Memory Management (AMM) is not setup
(See Doc ID 749851.1)
* The shared memory segments can be listed by command:
# ipcs -m

Press Enter to proceed..."

read

# Check for the kernel version
KERN=`uname -r | awk -F. '{ printf("%d.%d\n",$1,$2); }'`

# Find out the HugePage size
HPG_SZ=`grep Hugepagesize /proc/meminfo | awk '{print $2}'`
if [ -z "$HPG_SZ" ];then
echo "The hugepages may not be supported in the system where the script is being executed."
exit 1
fi

# Initialize the counter
NUM_PG=0

# Cumulative number of pages required to handle the running shared memory segments
for SEG_BYTES in `ipcs -m | cut -c44-300 | awk '{print $1}' | grep "[0-9][0-9]*"`
do
MIN_PG=`echo "$SEG_BYTES/($HPG_SZ*1024)" | bc -q`
if [ $MIN_PG -gt 0 ]; then
NUM_PG=`echo "$NUM_PG+$MIN_PG+1" | bc -q`
fi
done

RES_BYTES=`echo "$NUM_PG * $HPG_SZ * 1024" | bc -q`

# An SGA less than 100MB does not make sense
# Bail out if that is the case
if [ $RES_BYTES -lt 100000000 ]; then
echo "***********"
echo "** ERROR **"
echo "***********"
echo "Sorry! There are not enough total of shared memory segments allocated for
HugePages configuration. HugePages can only be used for shared memory segments
that you can list by command:

# ipcs -m

of a size that can match an Oracle Database SGA. Please make sure that:
* Oracle Database instance is up and running
* Oracle Database 11g Automatic Memory Management (AMM) is not configured"
exit 1
fi

# Finish with results
case $KERN in
'2.4') HUGETLB_POOL=`echo "$NUM_PG*$HPG_SZ/1024" | bc -q`;
echo "Recommended setting: vm.hugetlb_pool = $HUGETLB_POOL" ;;
'2.6') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
'3.8') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
'3.10') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
'4.1') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
'4.14') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
'4.18') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
'5.4') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
'5.15') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
*) echo "Kernel version $KERN is not supported by this script (yet). Exiting." ;;
esac

# End

EOF

chmod +x hugepages_settings.sh

./hugepages_settings.sh

-- 1.5 カーネル・パラメータ設定

sudo sysctl -w vm.nr_hugepages=354

sudo vim /etc/sysctl.conf

vm.nr_hugepages=354

-- 1.6 使用可能なhugepagesを確認

grep Huge /proc/meminfo

sudo reboot

grep Huge /proc/meminfo

-- 2. 統合監査設定
https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/recommended-and-best-practices-complete-upgrading-oracle-database.html#GUID-A44905C3-3D2E-4FAD-BA35-8F8E956D874C

SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

SHUTDOWN IMMEDIATE

lsnrctl stop

cd $ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME

lsnrctl start

STARTUP

SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

select count(*) from UNIFIED_AUDIT_TRAIL;
select * from UNIFIED_AUDIT_TRAIL;

-- 3. TDE暗号化

https://docs.oracle.com/cd/F19136_01/asoag/configuring-transparent-data-encryption.html#GUID-C5D20EAE-3369-411F-9A7E-13AB14139F1D
https://tech-oracle.blog.ss-blog.jp/2021-10-01

-- 3.1 初期化パラメータ設定

alter system set wallet_root='/u01/app/oracle/oradata' scope=spfile;

shutdown immediate
startup

alter system set tde_configuration='KEYSTORE_CONFIGURATION=FILE' scope=both;

shutdown immediate
startup

-- 3.2 キーストア作成

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE IDENTIFIED BY oracle;

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY oracle ;

-- 3.3 マスタ鍵作成

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY oracle WITH BACKUP;

-- 3.4 自動ログインキーストア作成

ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/app/oracle/oradata/tde' IDENTIFIED BY oracle;

-- 3.5 設定確認

select * from v$encryption_wallet;

shutdown immediate
startup

select * from v$encryption_wallet;

-- 3.6 PDBでのマスター鍵作成
conn / as sysdba

alter session set container = pdb1;

select * from v$encryption_wallet;

ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY oracle WITH BACKUP;

select * from v$encryption_wallet;

create tablespace tbs2 datafile '/u01/app/oracle/oradata/orcl/pdb1/tbs2.dbf' size 10M encryption using 'AES256' encrypt;

PostgreSQL

(15)

OS: Rocky Linux 8.9
CPU : 2
メモリ : 4GB

-- 1. HugePages設定
https://zatoima.github.io/postgresql-hugepages-setting.html

https://www.postgresql.jp/document/15/html/kernel-resources.html#LINUX-HUGE-PAGES

-- 1.1 /etc/sysctl.conf

grep Huge /proc/meminfo

sudo systemctl stop postgresql-15

/usr/pgsql-15/bin/postgres -D $PGDATA -C shared_memory_size_in_huge_pages
grep Huge /proc/meminfo

ls /sys/kernel/mm/hugepages

sudo sysctl -w vm.nr_hugepages=543

cat /proc/sys/vm/hugetlb_shm_group
cat /etc/group | grep postgres

sudo vim /etc/sysctl.conf

vm.hugetlb_shm_group = 26
vm.nr_hugepages=543

-- 1.2 /etc/security/limits.conf

ulimit -l

sudo vim /etc/security/limits.conf

postgres soft memlock unlimited
postgres hard memlock unlimited

sudo reboot

grep Huge /proc/meminfo
ulimit -l

-- 2. pg_statsinfo インストール

https://github.com/ossc-db/pg_statsinfo/releases/tag/15.2
https://github.com/ossc-db/pg_statsinfo/blob/15/doc/pg_statsinfo-ja.md#%E3%82%A4%E3%83%B3%E3%82%B9%E3%83%88%E3%83%BC%E3%83%AB

sudo systemctl stop postgresql-15
sudo systemctl disable postgresql-15

cd
wget https://github.com/ossc-db/pg_statsinfo/archive/refs/tags/15.2.tar.gz

tar xzvf 15.2.tar.gz
cd pg_statsinfo-15.2

sudo dnf -y install rpm-build

make USE_PGXS=1 PG_CONFIG=/usr/pgsql-15/bin/pg_config
sudo make install USE_PGXS=1 PG_CONFIG=/usr/pgsql-15/bin/pg_config

/usr/pgsql-15/bin/pg_ctl status

/usr/pgsql-15/bin/pg_ctl stop -m smart -D $PGDATA

cd $PGDATA

vim postgresql.conf

shared_preload_libraries = 'pg_statsinfo' # 事前ロードを行う
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # ログファイル名を指定する

log_min_messages = 'log' # ログへ出力するメッセージレベル。
track_functions = 'all' # ストアドプロシージャの呼び出しに関する統計情報を収集する
log_checkpoints = on # チェックポイントを記録
log_autovacuum_min_duration = 0 # 自動バキュームを記録

pg_statsinfo.snapshot_interval = 10min # スナップショットの取得間隔
pg_statsinfo.enable_maintenance = 'on' # 自動メンテナンス設定
pg_statsinfo.maintenance_time = '00:02:00' # 自動メンテナンス実行時刻設定
pg_statsinfo.repolog_min_messages = disable # ログ蓄積機能の設定

pg_statsinfo.syslog_min_messages = 'error' # syslogに出力するログレベルを指定する。
pg_statsinfo.textlog_line_prefix = '%t %p %c-%l %x %q(%u, %d, %r, %a) '
# pg_statsinfoがテキストログファイルに出力する際、各行の先頭に追加される書式を指定する。log_line_prefixと同じ形式で指定する。
pg_statsinfo.syslog_line_prefix = '%t %p %c-%l %x %q(%u, %d, %r, %a) '
# pg_statsinfoがsyslog経由でログを出力する際、各行の先頭に追加される書式を指定する。

/usr/pgsql-15/bin/pg_ctl start -m smart -D $PGDATA

psql -d postgres -c "SELECT statsinfo.snapshot('')"
psql -d postgres -c "SELECT * FROM statsrepo.snapshot"

SQL Server

(2022)

OS: Windows Server 2022
CPU : 2
メモリ : 4GB

-- 1. sql_option

USE [master]
GO

select * from sys.databases;
go

ALTER DATABASE [test] SET ANSI_NULL_DEFAULT ON WITH NO_WAIT
GO
ALTER DATABASE [test] SET ANSI_NULLS ON WITH NO_WAIT
GO
ALTER DATABASE [test] SET ANSI_PADDING ON WITH NO_WAIT
GO
ALTER DATABASE [test] SET ANSI_WARNINGS ON WITH NO_WAIT
GO
ALTER DATABASE [test] SET ARITHABORT ON WITH NO_WAIT
GO
ALTER DATABASE [test] SET CONCAT_NULL_YIELDS_NULL ON WITH NO_WAIT
GO
ALTER DATABASE [test] SET QUOTED_IDENTIFIER ON WITH NO_WAIT
GO

select * from sys.databases;
go

-- 2. トランザクション分離レベルの設定

use test
go

DBCC USEROPTIONS
ALTER DATABASE test SET READ_COMMITTED_SNAPSHOT ON with rollback after 1 seconds;
DBCC USEROPTIONS

-- 3. 暗号化の有効化

-- 3.1 データベースマスターキーの作成(masterデータベース)

use master
go
create master key
encryption by password = 'password'
go

-- 3.2 証明書オブジェクトの作成(masterデータベース)

create certificate master_cert
with subject = 'master_database_certificate'
,start_date='2022-8-21'
,expiry_date='2032-8-21'
go

-- 3.3 データベース暗号化キーの作成(ユーザーデータベース)
use test
go
create database encryption key
with algorithm = AES_256
encryption by server certificate master_cert
go

-- 3.4 データベース暗号化の有効化(ユーザーデータベース)
use master
go
alter database test
set encryption on
go