mysql-構成
13843 ワード
mysql-構成システムレベル構成 システムリソース数 limit設定-demo カーネルパラメータ-demo 重要資源制限 ファイルオープン数 プロセス、スレッド数制限 tcpがサポートするキュー数 ネットワーク接続時間 mysqlアプリケーションレベル構成 mysql起動方式 mysql起動 システムレベルの構成
システムリソース数
limit設定-demo
cat/etc/security/limits.d/90-nproc.conf
カーネルパラメータ-demo
cat/etc/sysctl.conf
重要なリソース制限
ファイルのオープン数カーネルパラメータ制限-合計制限 /etc/sysctl.conf file-maxは、システムのすべてのプロセスで開くことができるファイルの数を設定します.nr_Openは、単一プロセスで割り当てられる最大ファイル数です. limit制限-各ユーザの最大値制限 /etc/security/limits.d/90-nproc.conf softソフトリミット値はプログラムの最大オープンファイル数リミット値hardハードリミット値であり、ユーザーのソフトリミット値を制限する調整可能な最大上限である
カーネルパラメータ制限ファイルのオープン数は総量、プロセスレベルであり、limitの制限はユーザープロセスレベルカーネル構成とlimitに注意する必要がある.
プロセス、スレッド数の制限
Limit制限-ユーザーあたりの最大値制限
/etc/security/limits.d/90-nproc.confソフトリミット値はプログラムの最大プロセス数リミット値hardハードリミット値であり,ユーザのソフトリミット値を制限する調整可能な最大上限である.
tcpがサポートするキュー数
net.ipv4.tcp_max_syn_backlog = 65535
ネットワーク接続時間
mysqlアプリケーションレベルの構成
mysql起動方式
mysql起動
サービス起動スクリプト-/etc/init.d/mysqld
テスト環境mysql構成-my.conf
生産環境mysql構成-my.conf
システムリソース数
limit設定-demo
cat/etc/security/limits.d/90-nproc.conf
# Default limit for number of user's processes to prevent
# accidental fork bombs.
# See rhbz #432903 for reasoning.
* soft nproc 512000
* hard nproc 512000
* soft nofile 512000
* hard nofile 512000
カーネルパラメータ-demo
cat/etc/sysctl.conf
# Kernel sysctl configuration file for Red Hat Linux
#
# For binary values, 0 is disabled, 1 is enabled. See sysctl(8) and
# sysctl.conf(5) for more details.
# Controls IP packet forwarding
net.ipv4.ip_forward = 0
# Controls source route verification
net.ipv4.conf.default.rp_filter = 1
# Do not accept source routing
net.ipv4.conf.default.accept_source_route = 0
# Controls the System Request debugging functionality of the kernel
#kernel.sysrq = 0
# Controls whether core dumps will append the PID to the core filename.
# Useful for debugging multi-threaded applications.
kernel.core_uses_pid = 1
# Controls the use of TCP syncookies
net.ipv4.tcp_syncookies = 1
# Disable netfilter on bridges.
#net.bridge.bridge-nf-call-ip6tables = 0
#net.bridge.bridge-nf-call-iptables = 0
#net.bridge.bridge-nf-call-arptables = 0
# Controls the default maxmimum size of a mesage queue
kernel.msgmnb = 65536
# Controls the maximum size of a message, in bytes
kernel.msgmax = 65536
# Controls the maximum shared segment size, in bytes
kernel.shmmax = 68719476736
# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 4294967296
fs.file-max = 1048576
fs.nr_open = 1048576
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.core.netdev_max_backlog = 4096
net.core.somaxconn = 4096
net.ipv4.ip_local_port_range = 1024 65535
net.ipv4.tcp_fin_timeout = 15
net.ipv4.tcp_keepalive_time = 300
net.ipv4.tcp_max_syn_backlog = 8192
net.ipv4.tcp_max_tw_buckets = 5000
net.ipv4.tcp_no_metrics_save = 1
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_syn_retries = 2
net.ipv4.tcp_synack_retries = 2
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
vm.max_map_count = 562144
重要なリソース制限
ファイルのオープン数
* soft nofile 512000
* hard nofile 512000
カーネルパラメータ制限ファイルのオープン数は総量、プロセスレベルであり、limitの制限はユーザープロセスレベルカーネル構成とlimitに注意する必要がある.
プロセス、スレッド数の制限
Limit制限-ユーザーあたりの最大値制限
/etc/security/limits.d/90-nproc.confソフトリミット値はプログラムの最大プロセス数リミット値hardハードリミット値であり,ユーザのソフトリミット値を制限する調整可能な最大上限である.
* soft nproc 512000
* hard nproc 512000
tcpがサポートするキュー数
net.ipv4.tcp_max_syn_backlog = 65535
ネットワーク接続時間
net.ipv4.tcp_max_tw_buckets = 8000
net.ipv4.tcp_tw_reuse = 1
# 。 TIME-WAIT sockets TCP ， 0， ；
net.ipv4.tcp_tw_recycle = 1
# TCP TIME-WAIT sockets ， 0， 。 net.ipv4.tcp_timestamps（ ） 。
net.ipv4.tcp_fin_timeout = 10
#fin
mysqlアプリケーションレベルの構成
mysql起動方式
#su - mysql -c "/etc/init.d/mysqld JJecom_3861 start"
mysql起動
#!/bin/sh
#
# This script will be executed *after* all the other init scripts.
# You can put your own initialization stuff in here if you don't
# want to do the full Sys V style init stuff.
touch /var/lock/subsys/local
su - mysql -c "/etc/init.d/mysqld JJecom_3861 start"
サービス起動スクリプト-/etc/init.d/mysqld
#!/bin/bash
# ***************************************************************************
# Script : mysqld
# Purpose : start,stop,and restart mysql instance server
# Author :
# Creation Date :
# log file :
# cfg file :
# ***************************************************************************
mysql_instance=$1
mysql_port=`echo $1|awk -F_ '{print $2}'`
mysql_username="dbuser"
mysql_password="dbpasswd"
WHO=`whoami`
if [ ${WHO} != "mysql" ]
then
echo "Failure,Please switch to mysql user to run it....."
echo " "
exit 0;
fi
function_start_mysql()
{
printf "Starting $mysql_instance MySQL...
"
/usr/bin/mysqld_safe --defaults-file=/data/mysql/${mysql_instance}/my.cnf > /dev/null 2>&1 &
sleep 2
printf "checking $mysql_instance MySQL process......
"
ps -ef|grep $mysql_port
netstat -lntup|grep $mysql_port
printf "Start $mysql_instance MySQL is completed
"
}
function_stop_mysql()
{
printf "Stoping $mysql_instance MySQL...
"
/usr/bin/mysqladmin -u${mysql_username} -p${mysql_password} -S /data/mysql/${mysql_instance}/mysql.sock shutdown
printf "checking $mysql_instance MySQL process......
"
sleep 2
ps -ef|grep $mysql_port
netstat -lntu|grep $mysql_port
printf "Stop $mysql_instance MySQL is completed
"
}
function_restart_mysql()
{
printf "Restarting $mysql_instance MySQL...
"
function_stop_mysql
printf "
"
function_start_mysql
printf "Restart $mysql_instance MySQL is completed
"
}
function_status_mysql()
{
printf "Checking $mysql_instance MySQL status...
"
mysql -S /data/mysql/${mysql_instance}/mysql.sock -u${mysql_username} -p${mysql_password} -e "status;show variables like 'server_id';show variables like '%datadir%';"
printf "
"
printf "Checking $mysql_instance MySQL status completed
"
}
function_kill_mysql()
{
printf "checking $mysql_instance MySQL process......
"
ps -ef|grep $mysql_port
netstat -lntu|grep $mysql_port
kill -9 $(ps -ef | grep 'bin/mysqld_safe'| grep ${mysql_port} | awk '{printf $2}')
kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')
printf "checking $mysql_instance MySQL process......
"
sleep 2
ps -ef|grep $mysql_port
netstat -lntu|grep $mysql_port
}
case $2 in
start)
function_start_mysql;;
stop)
function_stop_mysql;;
status)
function_status_mysql;;
kill)
function_kill_mysql;;
restart)
function_stop_mysql
function_start_mysql;;
*)
echo "Usage: mysqld mysql_instance {start|stop|status|restart|kill}";;
esac
テスト環境mysql構成-my.conf
#my.conf
[client]
socket = /data/mysql/JJecom_3861/mysql.sock
[mysqld]
########basic settings########
server_id = 167820064
socket = /data/mysql/JJecom_3861/mysql.sock
pid_file = /data/mysql/JJecom_3861/mysql.pid
datadir = /data/mysql/JJecom_3861
character-set-server = utf8
port = 3861
autocommit = 1
character_set_server = utf8
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
skip-name-resolve
log_timestamps=SYSTEM
########cache and limit########
max_connections = 2000
max_connect_errors = 1000
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 8M
read_rnd_buffer_size = 8M
lock_wait_timeout = 50
tmp_table_size = 8M
table_open_cache = 2000
table_definition_cache = 1400
sort_buffer_size = 8M
join_buffer_size = 8M
max_allowed_packet = 32M
query_cache_type = 1
query_cache_size = 128M
net_buffer_length = 1M
#########log settings########
log_error = error.log
slow_query_log = 1
slow_query_log_file = slow.log
log_queries_not_using_indexes = on
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 7
long_query_time = 1
min_examined_row_limit = 100
log_bin_trust_function_creators = 1
#########replication settings########
master_info_repository = TABLE
relay_log_info_repository = TABLE
log_bin = bin.log
sync_binlog = 1
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
lower_case_table_names = 1
binlog_format = row
relay_log = relay.log
relay_log_recovery = 1
binlog_gtid_simple_recovery = 1
slave_skip_errors = ddl_exist_errors
#slave-parallel-type = LOGICAL_CLOCK
#slave-parallel-workers = 16
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = ON
binlog-checksum = CRC32
master-verify-checksum = 1
slave-sql-verify-checksum = 1
#########safety########
skip_name_resolve = 1
local_infile = OFF
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
plugin-load = validate_password.so
#########innodb settings########
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 5
innodb_io_capacity = 640
innodb_io_capacity_max = 8000
innodb_flush_method = O_DIRECT
innodb_undo_logs = 128
innodb_undo_tablespaces = 0
innodb_flush_log_at_trx_commit = 1
innodb_flush_neighbors = 1
innodb_log_file_size = 1G
innodb_log_buffer_size = 16777216
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 67108864
innodb_data_file_path = ibdata1:100M:autoextend
#########semi sync replication settings########
#plugin_dir = /usr/local/mysql/lib/plugin
#plugin_load = rpl_semi_sync_master=semisync_master.so;
#loose_rpl_semi_sync_master_enabled = 1
#loose_rpl_semi_sync_slave_enabled = 1
#loose_rpl_semi_sync_master_timeout = 5000
#[mysqld-5.7]
#innodb_buffer_pool_dump_pct = 40
#innodb_page_cleaners = 4
#innodb_undo_log_truncate = 1
#innodb_max_undo_log_size = 2G
#innodb_purge_rseg_truncate_frequency = 128
#binlog_gtid_simple_recovery =1
#log_timestamps = system
#transaction_write_set_extraction = MURMUR32
#show_compatibility_56 = on
[mysql]
######### client ########
max_allowed_packet = 32M
socket = /data/mysql/JJecom_3861/mysql.sock
[mysqldump]
quick
max_allowed_packet = 32M
生産環境mysql構成-my.conf
[client]
port=3306
socket = /data/mysql/PrdkDS_3258/mysql.sock
[mysqld]
########basic settings########
port=3306
socket = /data/mysql/PrdkDS_3258/mysql.sock
pid_file = /data/mysql/PrdkDS_3258/mysql.pid
server_id=16777102
datadir = /data/mysql/PrdkDS_3258
character-set-server = utf8
autocommit = 1
character_set_server = utf8
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
skip-name-resolve
log_timestamps=SYSTEM
########cache and limit########
read_only= 1
max_connections= 2000
max_connect_errors = 1000
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 8M
read_rnd_buffer_size = 8M
lock_wait_timeout = 50
tmp_table_size = 8M
table_open_cache = 2000
table_definition_cache = 1400
sort_buffer_size = 8M
join_buffer_size = 8M
max_allowed_packet = 32M
query_cache_type = 0
query_cache_size = 128M
net_buffer_length = 1M
#########log settings########
log_error = error.log
slow_query_log = 1
slow_query_log_file = slow.log
log_queries_not_using_indexes = on
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 7
long_query_time = 1
min_examined_row_limit = 100
log_bin_trust_function_creators = 1
#########replication settings########
master_info_repository = TABLE
relay_log_info_repository = TABLE
log_bin = bin.log
sync_binlog = 1
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
lower_case_table_names = 1
binlog_format = row
relay_log = relay.log
relay_log_recovery = 1
binlog_gtid_simple_recovery = 1
slave_skip_errors = ddl_exist_errors
#slave-parallel-type = LOGICAL_CLOCK
#slave-parallel-workers = 16
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = ON
binlog-checksum = CRC32
master-verify-checksum = 1
slave-sql-verify-checksum = 1
#########safety########
skip_name_resolve = 1
local_infile = OFF
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
plugin-load = validate_password.so
#########innodb settings########
innodb_buffer_pool_size= 4G
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 5
innodb_io_capacity = 640
innodb_io_capacity_max = 8000
innodb_flush_method = O_DIRECT
innodb_undo_logs = 128
innodb_undo_tablespaces = 0
innodb_flush_log_at_trx_commit = 1
innodb_flush_neighbors = 1
innodb_log_file_size = 1G
innodb_log_buffer_size = 16777216
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 67108864
innodb_data_file_path = ibdata1:100M:autoextend
#########semi sync replication settings########
#plugin_dir = /usr/local/mysql/lib/plugin
#plugin_load = rpl_semi_sync_master=semisync_master.so;
#loose_rpl_semi_sync_master_enabled = 1
#loose_rpl_semi_sync_slave_enabled = 1
#loose_rpl_semi_sync_master_timeout = 5000
#[mysqld-5.7]
#innodb_buffer_pool_dump_pct = 40
#innodb_page_cleaners = 4
#innodb_undo_log_truncate = 1
#innodb_max_undo_log_size = 2G
#innodb_purge_rseg_truncate_frequency = 128
#binlog_gtid_simple_recovery =1
#log_timestamps = system
#transaction_write_set_extraction = MURMUR32
#show_compatibility_56 = on
[mysql]
######### client ########
max_allowed_packet = 32M
port=3306
socket = /data/mysql/PrdkDS_3258/mysql.sock
[mysqldump]
quick
max_allowed_packet = 32M