浅析MySQL_5.0的my.cnf文件

很多时候安装完mysql，直接把my.cnf的例子文件复制到/etc目录下，很少主动做调整，只是根据需求做简单调整，例如忽略大小写，设置数据库字符集等。

MySQL 5.0版本中，my.cnf有五个my.cnf例子文件，简单说明我贴在下面：

my-huge.cnf

# Example MySQL config file for very large systems.
#
# This is for a large system with memory of 1G-2G where the system runs mainly
# MySQL.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

my-innodb-heavy-4G.cnf

#BEGIN CONFIG INFO
#DESCR: 4GB RAM, InnoDB only, ACID, few connections, heavy queries
#TYPE: SYSTEM
#END CONFIG INFO

#
# This is a MySQL example config file for systems with 4GB of memory
# running mostly MySQL using InnoDB only tables and performing complex
# queries with few connections.
#
# You can copy this file to /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options
# (/var/lib/mysql for this installation) or to
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.
#
# More detailed information about the individual options can also be
# found in the manual.

my-large.cnf

# Example MySQL config file for large systems.
#
# This is for a large system with memory = 512M where the system runs mainly
# MySQL.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

my-medium.cnf

# Example MySQL config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

my-small.cnf

# Example MySQL config file for small systems.
#
# This is for a system with little memory (<= 64M) where MySQL is only used
# from time to time and it's important that the mysqld daemon
# doesn't use much resources.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

总结：

具体情况，要根据业务量调整，主要更改参数整理：

 

default-character-set=utf8

设置数据库默认字符集为utf8

lower_case_table_names=1

不区分大小写

key_buffer

MyISAM表的索引块分配了缓冲区，由所有线程共享。key_buffer_size是索引块缓冲区的大小。键值缓冲区即为键值缓存。key_buffer_size的最大允许设定值为4GB。有效最大值可以更小，取决于可用物理RAM和操作系统或硬件平台强加的每个进程的RAM限制。

增加该值，达到你可以提供的更好的索引处理(所有读和多个写操作)。通常为主要运行MySQL的机器内存的25%。但是，如果你将该值设得过大(例如，大于总内存的50%)，系统将转换为页并变得极慢。MySQL依赖操作系统来执行数据读取时的文件系统缓存，因此你必须为文件系统缓存留一些空间。

max_allowed_packet

包或任何生成的/中间字符串的最大大小。

包消息缓冲区初始化为net_buffer_length字节，但需要时可以增长到max_allowed_packet字节。该值默认很小，以捕获大的(可能是错误的)数据包。

myisam_sort_buffer_size

当在REPAIR TABLE或用CREATE INDEX创建索引或ALTER TABLE过程中排序 MyISAM索引分配的缓冲区。

net_buffer_length

在查询之间将通信缓冲区重设为该值。一般情况不应改变，但如果内存很小，可以将它设置为期望的客户端发送的SQL语句的长度。如果语句超出该长度，缓冲区自动扩大，直到max_allowed_packet字节。

query_cache_size

为缓存查询结果分配的内存的数量。默认值是0，即禁用查询缓存。请注意即使query_cache_type设置为0也将分配此数量的内存。

read_buffer_size

每个线程连续扫描时为扫描的每个表分配的缓冲区的大小(字节)。如果进行多次连续扫描，可能需要增加该值， 默认值为131072。

read_rnd_buffer_size

当排序后按排序后的顺序读取行时，则通过该缓冲区读取行，避免搜索硬盘。将该变量设置为较大的值可以大大改进ORDER BY的性能。但是，这是为每个客户端分配的缓冲区，因此你不应将全局变量设置为较大的值。相反，只为需要运行大查询的客户端更改会话变量。？如何为指定客户端设置会话变量

skip-locking

mysqld使用外部锁定，该值为OFF

避免MySQL的外部锁定，减少出错几率增强稳定性。服务器可以使用外部锁定(文件级锁)来防止其它程序在服务器使用表时修改文件。MySQL5.0该参数为skip-locking，5.1之后则变为如下。

mysql> show variables like 'skip%locking';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| skip_external_locking | ON    |

+-----------------------+-------+

1 row in set (0.01 sec)

sort_buffer_size

每个排序线程分配的缓冲区的大小。增加该值可以加快ORDER BY或GROUP BY操作。

table_cache

所有线程打开的表的数目。增大该值可以增加mysqld需要的文件描述符的数量。你可以检查Opened_tables状态变量来检查你是否需要增加表缓存。如果Opened_tables值较大，并且多次执行FLUSH TABLES(只是强制关闭所有表并重新)，则应增加table_cache变量的值

 

mysql> show status like 'Opened_tables'\G

*************************** 1. row ***************************

Variable_name: Opened_tables

        Value: 0

1 row in set (0.00 sec)

已经打开的表的数量。如果Opened_tables较大，table_cache 值可能太小。

thread_cache_size

服务器应缓存多少线程以便重新使用。当客户端断开连接时，如果线程少于thread_cache_size，则客户端的线程被放入缓存。当请求线程时如果允许可以从缓存中重新利用线程，并且只有当缓存空了时才会创建新线程。如果新连接很多，可以增加该变量以提高性能。(一般情况，如果线程执行得很好，性能提高不明显)。检查Connections和Threads_created状态变量的差，你可以看见线程缓存的效率。

mysql> show status like 'Connections'\G

*************************** 1. row ***************************

Variable_name: Connections

        Value: 2

1 row in set (0.00 sec)

mysql> show status like 'Threads_cached'\G

*************************** 1. row ***************************

Variable_name: Threads_cached

        Value: 0

1 row in set (0.01 sec)

thread_concurrency

在Solaris中，mysqld用该值调用thr_setconcurrency()。该函数使应用程序向线程系统提供需要同时运行的期望的线程数目的提示。

# Try number of CPU's*2 for thread_concurrency

 

参考：

MySQL5.1参考手册