(8.0.35)
OS: Rocky Linux 8.9
CPU : 2
メモリ : 4GB
※サーバーを初期化する前に設定必要(初期化はサーバーのデータディレクトリが空の場合に実施される)
lower_case_table_names = 1
cp /etc/my.cnf /etc/my.cnf.bk$(date "+%Y%m%d")
vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
lower_case_table_names = 1
activate_all_roles_on_login = ON
admin_address = '192.168.137.177'
admin_port = 3307
binlog_cache_size = 32MB
binlog_expire_logs_seconds = 691200
block_encryption_mode = 'aes-256-cbc'
collation_server = 'utf8mb4_0900_bin'
create_admin_listener_thread = ON
default_authentication_plugin = mysql_native_password
enforce_gtid_consistency = ON
group_concat_max_len = 1048576
gtid_mode = ON
innodb_adaptive_hash_index_parts = 16
innodb_buffer_pool_instances = 1
innodb_buffer_pool_size = 3200MB
innodb_flush_method = 'O_DIRECT'
innodb_io_capacity = 1000
innodb_log_buffer_size = 32MB
innodb_log_writer_threads = OFF
innodb_online_alter_log_max_size = 1073741824
innodb_print_all_deadlocks = ON
innodb_read_io_threads = 8
innodb_redo_log_capacity = 419430400
innodb_rollback_on_timeout = ON
innodb_sync_array_size = 16
innodb_write_io_threads = 8
# large_pages = ON
lc_time_names = 'ja_JP'
local_infile = ON
log_bin_trust_function_creators = ON
log_slow_extra = ON
log_timestamps = SYSTEM
long_query_time = 3
max_allowed_packet = 1073741824
max_connections = 200
max_heap_table_size = 64MB
max_sp_recursion_depth = 10
mysqlx_max_allowed_packet = 1073741824
mysqlx_max_connections = 200
mysqlx_socket = /var/run/mysqld/mysqlx.sock
net_write_timeout = 120
open_files_limit = 10000
performance_schema_show_processlist = ON
plugin_dir = /usr/lib64/mysql/plugin/
read_buffer_size = 256KB
read_rnd_buffer_size = 512KB
skip_name_resolve = ON
slow_query_log = ON
sort_buffer_size = 1MB
table_open_cache = 4895
thread_cache_size = 200
default_time_zone = '+09:00'
tmp_table_size = 64MB
transaction_isolation = READ-COMMITTED
systemctl restart mysqld
select @@activate_all_roles_on_login;
select @@admin_address;
select @@admin_port;
select @@binlog_cache_size;
select @@binlog_expire_logs_seconds;
select @@block_encryption_mode;
select @@collation_server;
select @@create_admin_listener_thread;
select @@datadir;
select @@default_authentication_plugin;
select @@enforce_gtid_consistency;
select @@group_concat_max_len;
select @@gtid_mode;
select @@innodb_adaptive_hash_index_parts;
select @@innodb_buffer_pool_instances;
select @@innodb_buffer_pool_size;
select @@innodb_flush_method;
select @@innodb_io_capacity;
select @@innodb_log_buffer_size;
select @@innodb_log_writer_threads;
select @@innodb_online_alter_log_max_size;
select @@innodb_print_all_deadlocks;
select @@innodb_read_io_threads;
select @@innodb_redo_log_capacity;
select @@innodb_rollback_on_timeout;
select @@innodb_sync_array_size;
select @@innodb_write_io_threads;
select @@large_pages;
select @@lc_time_names;
select @@local_infile;
select @@log_bin_trust_function_creators;
select @@log_error;
select @@log_slow_extra;
select @@log_timestamps;
select @@long_query_time;
select @@lower_case_table_names;
select @@max_allowed_packet;
select @@max_connections;
select @@max_heap_table_size;
select @@max_sp_recursion_depth;
select @@mysqlx_max_allowed_packet;
select @@mysqlx_max_connections;
select @@mysqlx_socket;
select @@net_write_timeout;
select @@open_files_limit;
select @@performance_schema_show_processlist;
select @@pid_file;
select @@plugin_dir;
select @@read_buffer_size;
select @@read_rnd_buffer_size;
select @@skip_name_resolve;
select @@slow_query_log;
select @@socket;
select @@sort_buffer_size;
select @@table_open_cache;
select @@thread_cache_size;
select @@time_zone;
select @@tmp_table_size;
select @@transaction_isolation;
(19c)
OS : CentOS7.6
CPU : 2
メモリ : 4GB
-- 日付書式設定
export NLS_DATE_FORMAT='YYYY/MM/DD'
export NLS_TIMESTAMP_FORMAT='YYYY/MM/DD HH24:MI:SSXFF'
export NLS_TIMESTAMP_TZ_FORMAT='YYYY/MM/DD HH24:MI:SSXFF TZR'
. ~/.bash_profile
mkdir -p /u01/app/oracle/oradata/orcl/fra
rm -rf $ORACLE_HOME/dbs/spfileorcl.ora
cat <<-'EOF' > $ORACLE_HOME/dbs/initorcl.ora
ARCHIVE_LAG_TARGET = 1800
AWR_PDB_AUTOFLUSH_ENABLED = true
CONTROL_FILE_RECORD_KEEP_TIME = 15
CONTROL_FILES = ('/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl')
DB_CACHE_ADVICE = 'OFF'
DB_DOMAIN = 'example.com'
DB_NAME = 'orcl'
DB_RECOVERY_FILE_DEST = '/u01/app/oracle/oradata/orcl/fra'
DB_RECOVERY_FILE_DEST_SIZE = 10G
ENABLE_PLUGGABLE_DATABASE = true
FILESYSTEMIO_OPTIONS = 'SETALL'
LOG_ARCHIVE_DEST_1 = 'location=/u01/app/oracle/oradata/orcl'
LOG_ARCHIVE_FORMAT = 'log%T_%S_%r_%d.arc'
LOG_BUFFER = 8M
# MAX_STRING_SIZE = 'EXTENDED'
# NLS_DATE_FORMAT = 'YYYY/MM/DD'
NLS_NCHAR_CONV_EXCP = 'TRUE'
# NLS_TIMESTAMP_FORMAT = 'YYYY/MM/DD HH24:MI:SSXFF'
# NLS_TIMESTAMP_TZ_FORMAT = 'YYYY/MM/DD HH24:MI:SSXFF TZR'
OPEN_CURSORS = 500
OPEN_LINKS = 20
OPTIMIZER_ADAPTIVE_PLANS = false
OPTIMIZER_DYNAMIC_SAMPLING = 0
PGA_AGGREGATE_LIMIT = 0
PGA_AGGREGATE_TARGET = 300M
PROCESSES = 200
RESULT_CACHE_MAX_SIZE = 0
SEC_MAX_FAILED_LOGIN_ATTEMPTS = 10
SESSION_CACHED_CURSORS = 100
SGA_MAX_SIZE = 700M
SGA_TARGET = 700M
STREAMS_POOL_SIZE = 12M
TEMP_UNDO_ENABLED = true
UNIFIED_AUDIT_COMMON_SYSTEMLOG = 'LOCAL3.INFO'
EOF
sqlplus / as sysdba
create spfile from pfile;
shutdown immediate
startup
show parameter ARCHIVE_LAG_TARGET
show parameter AWR_PDB_AUTOFLUSH_ENABLED
show parameter CONTROL_FILE_RECORD_KEEP_TIME
show parameter CONTROL_FILES
show parameter DB_CACHE_ADVICE
show parameter DB_DOMAIN
show parameter DB_NAME
show parameter DB_RECOVERY_FILE_DEST
show parameter DB_RECOVERY_FILE_DEST_SIZE
show parameter ENABLE_PLUGGABLE_DATABASE
show parameter FILESYSTEMIO_OPTIONS
show parameter LOG_ARCHIVE_DEST_1
show parameter LOG_BUFFER
show parameter MAX_STRING_SIZE
show parameter NLS_DATE_FORMAT
show parameter NLS_NCHAR_CONV_EXCP
show parameter NLS_TIMESTAMP_FORMAT
show parameter NLS_TIMESTAMP_TZ_FORMAT
show parameter OPEN_CURSORS
show parameter OPEN_LINKS
show parameter OPTIMIZER_ADAPTIVE_PLANS
show parameter OPTIMIZER_DYNAMIC_SAMPLING
show parameter PGA_AGGREGATE_LIMIT
show parameter PGA_AGGREGATE_TARGET
show parameter PROCESSES
show parameter RESULT_CACHE_MAX_SIZE
show parameter SEC_MAX_FAILED_LOGIN_ATTEMPTS
show parameter SESSION_CACHED_CURSORS
show parameter SGA_MAX_SIZE
show parameter SGA_TARGET
show parameter STREAMS_POOL_SIZE
show parameter TEMP_UNDO_ENABLED
show parameter UNIFIED_AUDIT_COMMON_SYSTEMLOG
shutdown immediate
startup
-- MAX_STRING_SIZEをEXTENDEに変更
alter pluggable database pdb1 close immediate;
alter pluggable database pdb1 open upgrade;
show con_name;
alter session set container = pdb1;
show parameter MAX_STRING_SIZE;
alter system set MAX_STRING_SIZE=extended scope=both;
show con_name;
alter session set container = CDB$ROOT;
alter pluggable database pdb1 close immediate;
alter pluggable database pdb1 open ;
show con_name;
alter session set container = pdb1;
@?/rdbms/admin/utlrp.sql
show parameter MAX_STRING_SIZE
(15)
OS: Rocky Linux 8.9
CPU : 2
メモリ : 4GB
-- ディレクトリ作成
mkdir -p /var/lib/pgsql/15/data/arc
mkdir -p /var/lib/pgsql/15/data/tbs01
mkdir -p /var/lib/pgsql/15/data/tmp01
-- スキーマ作成
create schema schema01;
\dn+
-- テーブルスペース作成
create tablespace tbs01 owner postgres location '/var/lib/pgsql/15/data/tbs01';
create tablespace tmp01 owner postgres location '/var/lib/pgsql/15/data/tmp01';
\db+
-- 拡張作成
wget https://download.postgresql.org/pub/repos/yum/15/redhat/rhel-8-x86_64/pg_hint_plan_15-1.5.1-1PGDG.rhel8.x86_64.rpm
wget https://download.postgresql.org/pub/repos/yum/15/redhat/rhel-8-x86_64/pg_repack_15-1.5.0-1PGDG.rhel8.x86_64.rpm
sudo rpm -ivh pg_hint_plan_15-1.5.1-1PGDG.rhel8.x86_64.rpm
sudo rpm -ivh pg_repack_15-1.5.0-1PGDG.rhel8.x86_64.rpm
create extension pg_buffercache schema public;
create extension pg_prewarm schema public;
create extension pg_stat_statements schema public;
create extension pg_hint_plan;
create extension pg_repack schema public;
\dx
cp postgresql.conf postgresql.conf.bk$(date "+%Y%m%d")
sed -i "s#^\#\?listen_addresses =.*#listen_addresses = \'*'#" postgresql.conf
sed -i "s#^\#\?max_connections =.*#max_connections = 203#" postgresql.conf
sed -i "s#^\#\?unix_socket_permissions =.*#unix_socket_permissions = 0700#" postgresql.conf
sed -i "s#^\#\?client_connection_check_interval =.*#client_connection_check_interval = 60s#" postgresql.conf
sed -i "s#^\#\?password_encryption =.*#password_encryption = md5#" postgresql.conf
sed -i "s#^\#\?shared_buffers =.*#shared_buffers = 1GB#" postgresql.conf
sed -i "s#^\#\?huge_page_size =.*#huge_page_size = 2MB#" postgresql.conf
sed -i "s#^\#\?temp_buffers =.*#temp_buffers = 64MB#" postgresql.conf
sed -i "s#^\#\?work_mem =.*#work_mem = 32MB#" postgresql.conf
sed -i "s#^\#\?hash_mem_multiplier =.*#hash_mem_multiplier = 1.0#" postgresql.conf
sed -i "s#^\#\?maintenance_work_mem =.*#maintenance_work_mem = 256MB#" postgresql.conf
sed -i "s#^\#\?autovacuum_work_mem =.*#autovacuum_work_mem = 512MB#" postgresql.conf
sed -i "s#^\#\?max_stack_depth =.*#max_stack_depth = 3MB#" postgresql.conf
sed -i "s#^\#\?effective_io_concurrency =.*#effective_io_concurrency = 200#" postgresql.conf
sed -i "s#^\#\?max_parallel_workers_per_gather =.*#max_parallel_workers_per_gather = 0#" postgresql.conf
sed -i "s#^\#\?checkpoint_timeout =.*#checkpoint_timeout = 30min#" postgresql.conf
sed -i "s#^\#\?max_wal_size =.*#max_wal_size = 4GB#" postgresql.conf
sed -i "s#^\#\?min_wal_size =.*#min_wal_size = 4GB#" postgresql.conf
sed -i "s#^\#\?archive_mode =.*#archive_mode = on#" postgresql.conf
sed -i "s#^\#\?archive_command =.*#archive_command = 'test ! -f /var/lib/pgsql/15/data/arc/%f \&\& cp %p /var/lib/pgsql/15/data/arc/%f'#" postgresql.conf
sed -i "s#^\#\?archive_timeout =.*#archive_timeout = 30min#" postgresql.conf
sed -i "s#^\#\?effective_cache_size =.*#effective_cache_size = 2GB#" postgresql.conf
sed -i "s#^\#\?log_destination =.*#log_destination = 'csvlog'#" postgresql.conf
sed -i "s#^\#\?log_filename =.*#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'#" postgresql.conf
sed -i "s#^\#\?log_rotation_size =.*#log_rotation_size = 0#" postgresql.conf
sed -i "s#^\#\?log_min_duration_statement =.*#log_min_duration_statement = 250ms#" postgresql.conf
sed -i "s#^\#\?log_connections =.*#log_connections = on#" postgresql.conf
sed -i "s#^\#\?log_disconnections =.*#log_disconnections = on#" postgresql.conf
sed -i "s#^\#\?log_error_verbosity =.*#log_error_verbosity = VERBOSE#" postgresql.conf
sed -i "s#^\#\?log_line_prefix =.*#log_line_prefix = '[%t]%r %u %d %p[%l]'#" postgresql.conf
sed -i "s#^\#\?log_lock_waits =.*#log_lock_waits = on#" postgresql.conf
sed -i "s#^\#\?log_parameter_max_length_on_error =.*#log_parameter_max_length_on_error = -1#" postgresql.conf
sed -i "s#^\#\?log_temp_files =.*#log_temp_files = 0#" postgresql.conf
sed -i "s#^\#\?log_timezone =.*#log_timezone = 'Asia/Tokyo'#" postgresql.conf
sed -i "s#^\#\?track_activity_query_size =.*#track_activity_query_size = 4096#" postgresql.conf
sed -i "s#^\#\?track_functions =.*#track_functions = pl#" postgresql.conf
sed -i "s#^\#\?compute_query_id =.*#compute_query_id = on#" postgresql.conf
sed -i "s#^\#\?autovacuum_max_workers =.*#autovacuum_max_workers = 5#" postgresql.conf
sed -i "s#^\#\?search_path =.*#search_path = 'schema01'#" postgresql.conf
sed -i "s#^\#\?default_tablespace =.*#default_tablespace = 'tbs01'#" postgresql.conf
sed -i "s#^\#\?temp_tablespaces =.*#temp_tablespaces = 'tmp01'#" postgresql.conf
sed -i "s#^\#\?datestyle =.*#datestyle = 'iso, ymd'#" postgresql.conf
sed -i "s#^\#\?timezone =.*#timezone = 'Asia/Tokyo'#" postgresql.conf
sed -i "s#^\#\?client_encoding =.*#client_encoding = 'UTF8'#" postgresql.conf
sed -i "s#^\#\?lc_messages =.*#lc_messages = 'C'#" postgresql.conf
sed -i "s#^\#\?lc_monetary =.*#lc_monetary = 'C'#" postgresql.conf
sed -i "s#^\#\?lc_numeric =.*#lc_numeric = 'C'#" postgresql.conf
sed -i "s#^\#\?lc_time =.*#lc_time = 'C'#" postgresql.conf
sed -i "s#^\#\?default_text_search_config =.*#default_text_search_config = 'pg_catalog.english'#" postgresql.conf
sed -i "s#^\#\?shared_preload_libraries =.*#shared_preload_libraries = 'auto_explain,pg_buffercache,pg_hint_plan,pg_prewarm,pg_repack,pg_stat_statements'#" postgresql.conf
sed -i "s#^\#\?deadlock_timeout =.*#deadlock_timeout = 20s#" postgresql.conf
sudo systemctl restart postgresql-15
(2022)
OS: Windows Server 2022
CPU : 2
メモリ : 4GB
use test;
go
sp_configure;
go
select * from sys.database_scoped_configurations order by configuration_id;
go
sp_configure 'show advanced options', 1;
go
reconfigure;
go
sp_configure 'Agent XPs', 1;
go
sp_configure 'contained database authentication', 1;
go
sp_configure 'default full-text language', 1041;
go
sp_configure 'default language', 3;
go
sp_configure 'max degree of parallelism', 1;
go
sp_configure 'max server memory (MB)', 2048;
go
sp_configure 'nested triggers', 0;
go
sp_configure 'query wait (s)', 0;
go
sp_configure 'remote admin connections', 1;
go
sp_configure 'remote query timeout (s)', 0;
go
sp_configure 'server trigger recursion', 0;
go
reconfigure;
go
ALTER DATABASE SCOPED CONFIGURATION set PARAMETER_SNIFFING = OFF;
ALTER DATABASE SCOPED CONFIGURATION set BATCH_MODE_ADAPTIVE_JOINS = OFF;
ALTER DATABASE SCOPED CONFIGURATION set ELEVATE_ONLINE = WHEN_SUPPORTED;
ALTER DATABASE SCOPED CONFIGURATION set ELEVATE_RESUMABLE = WHEN_SUPPORTED;
ALTER DATABASE SCOPED CONFIGURATION set PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF;
ALTER DATABASE SCOPED CONFIGURATION set CE_FEEDBACK = OFF;
ALTER DATABASE SCOPED CONFIGURATION set OPTIMIZED_PLAN_FORCING = OFF;
go
sp_configure;
go
select * from sys.database_scoped_configurations order by configuration_id;
go
