最新文章专题视频专题问答1问答10问答100问答1000问答2000关键字专题1关键字专题50关键字专题500关键字专题1500TAG最新视频文章推荐1 推荐3 推荐5 推荐7 推荐9 推荐11 推荐13 推荐15 推荐17 推荐19 推荐21 推荐23 推荐25 推荐27 推荐29 推荐31 推荐33 推荐35 推荐37视频文章20视频文章30视频文章40视频文章50视频文章60 视频文章70视频文章80视频文章90视频文章100视频文章120视频文章140 视频2关键字专题关键字专题tag2tag3文章专题文章专题2文章索引1文章索引2文章索引3文章索引4文章索引5123456789101112131415文章专题3
当前位置: 首页 - 科技 - 知识百科 - 正文

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

来源:懂视网 责编:小采 时间:2020-11-09 20:14:10
文档

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

MySQL数据库常见调优方法及参数设置_MySQL:1. 关闭 SELinux vim /etc/selinux/config 更改 SELINUX=enforcing 为 SELINUX=disabled 2. 更改 IO Schedule, 对于 SSD 硬盘无需更改 echo deadline > /sys/block/sda/queue/scheduler 3. 更改 ulimi
推荐度:
导读MySQL数据库常见调优方法及参数设置_MySQL:1. 关闭 SELinux vim /etc/selinux/config 更改 SELINUX=enforcing 为 SELINUX=disabled 2. 更改 IO Schedule, 对于 SSD 硬盘无需更改 echo deadline > /sys/block/sda/queue/scheduler 3. 更改 ulimi

1. 关闭 SELinux

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

2. 更改 IO Schedule, 对于 SSD 硬盘无需更改

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

3. 更改 ulimit

vim /etc/security/limits.conf

* soft nofile 65535
* hard nofile 65535
root soft nofile 65535
root hard nofile 65535

4. 更改内核参数

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

5. MySQL 5.6.10 数据库自身设置

# 以下针对 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 5.6.10 编译参数:

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

文档

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

MySQL数据库常见调优方法及参数设置_MySQL:1. 关闭 SELinux vim /etc/selinux/config 更改 SELINUX=enforcing 为 SELINUX=disabled 2. 更改 IO Schedule, 对于 SSD 硬盘无需更改 echo deadline > /sys/block/sda/queue/scheduler 3. 更改 ulimi
推荐度:
标签: 数据库 方式 常见
  • 热门焦点

最新推荐

猜你喜欢

热门推荐

专题
Top