系统初始化生产脚本,MySQL数据库常见调优方法及参数设置

MySQL 数据库常见调优方法及参数设置

  1. 关闭 SELinux

 

vim /etc/selinux/config 更改 SELINUX=enforcing 为 SELINUX=disabled

 

  1. 改变 IO Schedule, 对于 SSD 硬盘没有须要更换

 

echo deadline > /sys/block/sda/queue/scheduler

 

  1. 更改 ulimit

    vim /etc/security/limits.conf

    • soft nofile 65535
    • hard nofile 65535
      root soft nofile 65535
      root hard nofile 65535
  2. 更换基础参数

    vim /etc/sysctl.conf

    net.core.netdev_max_backlog = 3000
    net.core.optmem_max = 20480
    net.core.rmem_default = 8388608
    net.core.rmem_max = 8388608
    net.core.wmem_default = 1048576
    net.core.wmem_max = 1048576

    net.ipv4.tcp_mem = 786432 1048576 1572864
    net.ipv4.tcp_rmem = 32768 4194304 8388608
    net.ipv4.tcp_wmem = 8192 4194304 8388608

    net.ipv4.tcp_max_syn_backlog = 2048

    net.ipv4.tcp_retries2 = 5
    net.ipv4.tcp_fin_timeout = 30

    net.ipv4.tcp_keepalive_time = 3600
    net.ipv4.tcp_keepalive_intvl = 30
    net.ipv4.tcp_keepalive_probes = 9

    net.ipv4.tcp_max_tw_buckets = 6000
    net.ipv4.ip_local_port_range = 10240 61000

    fs.file-max = 6815744
    vm.swappiness = 0

    kernel.sem = 250 32000 100 128
    kernel.shmmni = 4096
    kernel.ctrl-alt-del = 1

 

 

  1. MySQL 5.6.十 数据库自己设置

 

# 以下针对 24G 内存服务器设置 my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

socket = /tmp/mysql.sock

max_connections = 1024
max_connect_errors = 10000
max_allowed_packet = 16M

skip-name-resolve
lower_case_table_names = 1

thread_cache = 128
table_open_cache = 1024

query_cache_type = 1
query_cache_size = 128M

join_buffer_size = 8M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M

max_heap_table_size = 128M
tmp_table_size = 128M
tmpdir = /dev/shm

binlog_cache_size = 12M
max_binlog_size = 512M
expire_logs_days = 3

innodb_buffer_pool_size = 16G
innodb_use_sys_malloc = 1

# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 128M
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_lock_wait_timeout = 50

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

# slow_query_log = 1
# slow_query_log_file = slow.log
# long_query_time = 1
# log_queries_not_using_indexes

# log-bin = mysql-bin
# server-id = 1
# innodb_flush_log_at_trx_commit = 1
# sync_binlog = 1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

 

沾满 MySQL 五.陆.拾 编写翻译参数:

 

tar zxvf mysql-5.6.10.tar.gz

cd mysql-5.6.10

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql
-DWITH_INNOBASE_STORAGE_ENGINE=1 -DMYSQL_TCP_PORT=3306
-DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci
-DWITH_DEBUG=0 -DCURSES_LIBRARY=/usr/lib64/libncurses.so
-DCURSES_INCLUDE_PATH=/usr/include

make

make install

数据库常见调优方法及参数设置 壹. 停歇SELinux vim /etc/selinux/config 退换 SELINUX=enforcing 为
SELINUX=disabled 贰. 改变 IO Schedule, 对于 SSD 硬盘没有需求…

MySQL数据库常见调优方法及参数设置

#!/bin/bash
# Program:
#       system_init_shell
# History:
#       2012/06/01 25061008@qq.com
# Release:
#       1.1
cat << EOF
 +————————————————————–+
 |          === Welcome to
CentOS 6.x System init
===           |
 +————————————————————–+
 +—————————by opsren————————–+
EOF

 

#update 163 yum
cd /etc/yum.repos.d
mv CentOS-Base.repo  CentOS-Base.repo.bak
wget
mv CentOS-Base-163.repo  CentOS-Base.repo
yum clean metadata
yum makecache

数据库是 IO 密集型应用, 1块带 Cache 的 Raid
卡比以下大很多陈设成效都来得精通!

#add epel
rpm -Uvh

rpm  –import  /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6

  www.2cto.com  

#add rpmforge
rpm -Uvh

rpm –import  /etc/pki/rpm-gpg/RPM-GPG-KEY-rpmforge-dag

  1.  关闭 SELinux   

#update system pack
yum -y install gcc gcc-c++ make autoconf libtool-ltdl-devel gd-devel
freetype-devel libxml2-devel libjpeg-devel libpng-devel openssl-devel
curl-devel bison patch unzip libmcrypt-devel libmhash-devel
ncurses-devel sudo bzip2 mlocate flex lrzsz sysstat lsof setuptool 
system-config-network-tui system-config-firewall-tui ntp libaio-devel
wget ntp

 

#set ntp
echo “*/5 * * * * /usr/sbin/ntpdate ntp.api.bz > /dev/null
2>&1” >> /var/spool/cron/root
service crond restart

1

#set clock
hwclock –set –date=”`date +%D\ %T`”
hwclock –hctosys

vim /etc/selinux/config 更改 SELINUX=enforcing 为 SELINUX=disabled

#set ulimit
echo “ulimit -SHn 102400” >> /etc/rc.local
cat >> /etc/security/limits.conf << EOF
 *           soft   nofile       102400
 *           hard   nofile       102400
 *           soft   nproc        102400
 *           hard   nproc        102400
EOF

 

#set max user processes
sed -i ‘s/1024/102400/’ /etc/security/limits.d/90-nproc.conf

  1.  改换 IO Schedule, 对于 SSD 硬盘不须要更换 

# turnoff the control-alt-delete
sed -i ‘s#exec /sbin/shutdown -r now#\#exec /sbin/shutdown -r now#’
/etc/init/control-alt-delete.conf

1

#close useless service
for i in `ls /etc/rc3.d/S*`
do
   CURSRV=`echo $i|cut -c 15-`
   echo $CURSRV
case $CURSRV in
   crond | irqbalance | network | sshd | rsyslog | sysstat )
   echo “Base services, Skip!”
   ;;
   *)
   echo “change $CURSRV to off”
   chkconfig –level 2345 $CURSRV off
   service $CURSRV stop
   ;;
esac
done
echo “service is init is ok…………..”

echo deadline > /sys/block/sda/queue/scheduler

#set LANG
:> /etc/sysconfig/i18n
cat >> /etc/sysconfig/i18n << EOF
 LANG=”en_US.UTF-8″
EOF

 

#set ssh
sed -i ‘s/^GSSAPIAuthentication yes$/GSSAPIAuthentication no/’
/etc/ssh/sshd_config
sed -i ‘s/#UseDNS yes/UseDNS no/’ /etc/ssh/sshd_config
sed -i ‘s/#Port 22/Port 6343/’ /etc/ssh/sshd_config
service sshd restart

  1.  更改 ulimit  

#set sysctl
true > /etc/sysctl.conf
cat >> /etc/sysctl.conf << EOF
 net.ipv4.ip_forward = 0
 net.ipv4.conf.default.rp_filter = 1
 net.ipv4.conf.default.accept_source_route = 0
 kernel.sysrq = 0
 kernel.core_uses_pid = 1
 net.ipv4.tcp_syncookies = 1
 kernel.msgmnb = 65536
 kernel.msgmax = 65536
 kernel.shmmax = 68719476736
 kernel.shmall = 4294967296
 net.ipv4.tcp_max_tw_buckets = 6000
 net.ipv4.tcp_sack = 1
 net.ipv4.tcp_window_scaling = 1
 net.ipv4.tcp_rmem = 4096 87380 4194304
 net.ipv4.tcp_wmem = 4096 16384 4194304
 net.core.wmem_default = 8388608
 net.core.rmem_default = 8388608
 net.core.rmem_max = 16777216
 net.core.wmem_max = 16777216
 net.core.netdev_max_backlog = 262144
 net.core.somaxconn = 262144
 net.ipv4.tcp_max_orphans = 3276800
 net.ipv4.tcp_max_syn_backlog = 262144
 net.ipv4.tcp_timestamps = 0
 net.ipv4.tcp_synack_retries = 1
 net.ipv4.tcp_syn_retries = 1
 net.ipv4.tcp_tw_recycle = 1
 net.ipv4.tcp_tw_reuse = 1
 net.ipv4.tcp_mem = 94500000 915000000 927000000
 net.ipv4.tcp_fin_timeout = 1
 net.ipv4.tcp_keepalive_time = 1200
 net.ipv4.ip_local_port_range = 1024 65535
EOF
/sbin/sysctl -p
echo “sysctl set OK!!”

1

#disable ipv6
echo “alias net-pf-10 off” >> /etc/modprobe.conf
echo “alias ipv6 off” >> /etc/modprobe.conf
/sbin/chkconfig ip6tables off
echo “ipv6 is disabled!”

vim /etc/security/limits.conf

#disable selinux
sed -i ‘/SELINUX/s/enforcing/disabled/’ /etc/selinux/config
setenforce 0

2

#vim setting

 

sed -i “8 s/^/alias vi=’vim’/” /root/.bashrc
echo ‘syntax on’ > /root/.vimrc

3

cat << EOF
 +————————————————————–+
 |                    ===System init over===                    |
 +————————————————————–+
 +—————————by 邱治军————————–+
EOF
echo
“###############################################################”

*               soft    nofile          65535

关于脚本的分解表明:
壹.将系统私下认可yum源改成16三源
2.充实第3方epel源
叁.扩大第一方rpmforge软件库
四.立异系统的根基软件
五.设置系统时钟同步
陆.当中先是条语句是设定硬件时钟,第二条语句是设定系统石英钟和硬件石英钟同步
7.日增展开最大文件讲述符
八.日增系统经过数(线程)的范围–>max user processes
centos 5与centos 陆分裂的是,只要在/etc/security/limits.conf 设置了root
soft nofile 十2400 和root hard nofile102400,对应的uilmit -u
后的结果就能够是拾2400,这里要求留意区分!
9.关闭control-alt-delete
10.闭馆不用的系统服务
11.装置系统语系
12.设置SSH
13.安装系统基本参数
14.关闭IPV6
15.关闭selinux
1陆.vi有关设置

4

必发88手机客户端 1

*               hard    nofile          65535

5

root            soft    nofile          65535

6

root            hard    nofile          65535

 

  1. 改变基础参数 

01

vim /etc/sysctl.conf

02

 

03

net.core.netdev_max_backlog = 3000

04

net.core.optmem_max = 20480

05

net.core.rmem_default = 8388608

06

net.core.rmem_max = 8388608

07

net.core.wmem_default = 1048576

08

net.core.wmem_max = 1048576

09

 

10

net.ipv4.tcp_mem = 786432 1048576 1572864

11

net.ipv4.tcp_rmem = 32768 4194304 8388608

12

net.ipv4.tcp_wmem =  8192 4194304 8388608

13

 

14

net.ipv4.tcp_max_syn_backlog = 2048

15

 

16

net.ipv4.tcp_retries2 = 5

17

net.ipv4.tcp_fin_timeout = 30

18

 

19

net.ipv4.tcp_keepalive_time = 3600

20

net.ipv4.tcp_keepalive_intvl = 30

21

net.ipv4.tcp_keepalive_probes = 9

22

 

23

net.ipv4.tcp_max_tw_buckets = 6000

24

net.ipv4.ip_local_port_range = 10240 61000

25

 

26

fs.file-max = 6815744

27

vm.swappiness = 0

28

 

29

kernel.sem = 250 32000 100 128

30

kernel.shmmni = 4096

31

kernel.ctrl-alt-del = 1

 

  1. MySQL 伍.陆.10 数据库自个儿设置

 

01

# 以下针对 贰4G 内部存款和储蓄器服务器设置 my.cnf

02

 

03

# For advice on how to change settings please see

04

#

05

 

06

[mysqld]

07

 

08

# Remove leading # and set to the amount of RAM for the most important
data

09

# cache in MySQL. Start at 70% of total RAM for dedicated server, else
10%.

10

 

11

socket = /tmp/mysql.sock

12

 

13

max_connections = 1024

14

max_connect_errors = 10000

15

max_allowed_packet = 16M

16

 

17

skip-name-resolve

18

lower_case_table_names = 1

19

 

20

thread_cache = 128

21

table_open_cache = 1024

22

 

23

query_cache_type = 1

24

query_cache_size = 128M

25

 

26

join_buffer_size = 8M

27

sort_buffer_size = 2M

28

read_buffer_size = 2M

29

read_rnd_buffer_size = 2M

30

 

31

max_heap_table_size = 128M

32

tmp_table_size = 128M

33

tmpdir = /dev/shm

34

 

35

binlog_cache_size = 12M

36

max_binlog_size = 512M

37

expire_logs_days = 3

38

 

39

innodb_buffer_pool_size = 16G

40

innodb_use_sys_malloc = 1

41

 

42

# Set .._log_file_size to 25 % of buffer pool size

43

innodb_log_file_size = 128M

44

innodb_log_buffer_size = 32M

45

innodb_flush_log_at_trx_commit = 2

46

innodb_flush_method = O_DIRECT

47

innodb_lock_wait_timeout = 50

48

 

49

# Remove leading # to turn on a very important data integrity option:
logging

50

# changes to the binary log between backups.

51

# log_bin

52

 

53

# These are commonly set, remove the # and set as required.

54

# basedir = …..

55

# datadir = …..

56

# port = …..

57

# server_id = …..

58

# socket = …..

59

 

60

# Remove leading # to set options mainly useful for reporting servers.

61

# The server defaults are faster for transactions and fast SELECTs.

62

#必发88手机客户端, Adjust sizes as needed, experiment to find the optimal values.

63

# join_buffer_size = 128M

64

# sort_buffer_size = 2M

65

# read_rnd_buffer_size = 2M

66

 

67

# slow_query_log = 1

68

# slow_query_log_file = slow.log

69

# long_query_time = 1

70

# log_queries_not_using_indexes

71

 

72

# log-bin = mysql-bin

73

# server-id = 1

74

# innodb_flush_log_at_trx_commit = 1

75

# sync_binlog = 1

76

 

77

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

屈居 MySQL 伍.6.10 编写翻译参数:

 

1

tar zxvf mysql-5.6.10.tar.gz

2

cd mysql-5.6.10

3

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql
-DWITH_INNOBASE_STORAGE_ENGINE=1 -DMYSQL_TCP_PORT=3306
-DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci
-DWITH_DEBUG=0 -DCURSES_LIBRARY=/usr/lib64/libncurses.so
-DCURSES_INCLUDE_PATH=/usr/include

4

make

5

make install

 

数据库是 IO
密集型应用, 一块带 Cache 的 Raid 卡比以下大多数布署效应都来得精晓!
www.2cto.com 1. 关闭 S…