mysql数据库优化--数据库参数

注意：全文使用的引擎是innodb

1. 数据库配置文件详解

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.5/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%.
innodb_buffer_pool_size = 10G
innodb_log_buffer_size = 64M
#InnoDB redo log大小，通常设置256MB 就足够了
innodb_log_file_size = 256M
#InnoDB redo log文件组，通常设置为 2 就足够了
innodb_log_files_in_group = 2
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# 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
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
secure-file-priv=/var/lib/mysql-files
user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

2.重要调优参数

• innodb_buffer_pool_size（默认128M）
  缓冲池是用于存储InnoDB表，索引和其他辅助缓冲区的缓存数据的内存区域，更大的缓冲池可以减少磁盘I/O来多次访问同一表数据。
  如果是专用的数据库服务器，可以设置为实际内存的百分之七十，如果一个机器部署了很多服务还是自己粗略估算一下吧，最好能空闲30%的内存用于应对瞬时流量。
• innodb_log_file_size（log文件日志大小）
• innodb_log_files_in_group（log文件日志个数）