mysql优化

MySQL 是一个很棒的 open source 数据库引擎，大部分的网站和博客都是由 MySQL 驱动的。MySQL 的默认安装占用的内存资源比较大（相对于一个只有 64MB 的 VPS来说），优化 MySQL 可以减少内存消耗，把更多的内存省下来留给其他程序。

MySQL 的配置文件在 /etc/mysql/my.cnf（Debian 5），为了方便调整配置，MySQL 为小资源系统提供了一个叫做 my-small.cnf 的配置文件，是给小于 32MB 内存的服务器设置的。我们可以在这个配置文件的基础上作小部分的调整。

先找到 /usr/share/doc/mysql-server-5.0/examples/my-small.cnf，然后覆盖 /etc/mysql/my.cnf（Debian）。如果是 CentOS 5 的话，路径是：/usr/share/doc/mysql-server-5.0.45/my-small.cnf，覆盖 /etc/my.cnf。

参数说明

如果不使用 BDB table 和 InnoDB table 的话，加入下面2行关闭不需要的表类型很有必要，关闭 innodb 可以省下大量内存，虽然 InnoDB 好处多多但是在一个64MB的 VPS 上并不能体现出来，并且很占内存。

skip-bdb
skip-innodb

key_buffer 是优化性能的重要参数，用来缓存 tables keys 和 indexes，增加这个值可以更好的处理索引，读和写都需要索引。这里设设置成 16K 足够了。table_cache 是所有线程打开的表的数量，增加值可以增大 MySQL 的文件描述符数量，避免频繁的打开表，原始 my-small.cnf 中 table_cache 设置成4有点小，一个 wordpress 的页面通常会涉及到10个左右的表，其他的程序比如 Drupal，MediaWiki 会涉及到更多，将table_cache改为8。

key_buffer = 16K
table_cache = 8

max_connections 是数据库最大的连接数量，可以根据自己博客/网站的访问量来定这个值。如果博客/网站经常出现：Too many connections 错误的信息说明需要增大 max_connections 的值。thread_concurrency 是最大并发线程数，通常设置为 CPU核数量×2，在 VPS 宿主机上如果服务器有2颗物理 CPU，而每颗物理 CPU 又支持 H.T 超线程（一个处理器上整合了两个逻辑处理器单元），所以实际取值为4 × 2 ＝ 8。

如果我们在优化 php.ini 的时候设置了同时只有2个 php-cgi 运行的话，那么我们也应该只设置2个 MySQL 线程同时运行。

max_connections = 16
thread_concurrency = 2

对于博客/新闻网站来说，用得最多的就是查询，所以需要加入 query cache 的设置。query_cache_size 是执行查询所使用的缓冲大小。

query_cache_limit = 256K
query_cache_size = 4M

thread_stack 用来存放每个线程的标识信息，如线程 id，线程运行时环境等，可以通过设置 thread_stack 来决定给每个线程分配多大的内存。

sort_buffer_size 是每个需要排序的线程分配的缓冲区大小，增加该值可以加速 order by 和 group by 的操作。注意：该参数是以每个连接分配内存，也就是说，如果有16个连接，sort_buffer_size 为 64K，那么实际分配的内存为：16 × 64K = 1MB。如果设置的缓存大小无法满足需要，MySQL 会将数据写入磁盘来完成排序。因为磁盘操作和内存操作不在一个数量级，所以 sort_buffer_size 对排序的性能影响很大。

read_buffer_size 是顺序读取数据时的缓冲区大小，与 sort_buffer_size 一样，该参数分配的内存也是以每连接为单位的。read_buffer_size 是用来当需要顺序读取数据的时候，如无发使用索引的情况下的全表扫描，全索引扫描等。在这种时候，MySQL 按照数据的存储顺序依次读取数据块，每次读取的数据快首先会暂存在 read_buffer_size 中，当 buffer 空间被写满或者全部数据读取结束后，再将 buffer 中的数据返回给上层调用者，以提高效率。

read_rnd_buffer_size 是随机读取数据时的缓冲区大小，与顺序读相对应。

net_buffer_size 用来存放客户端连接线程的连接信息和返回客户端的结果集的缓存大小。当 MySQL 接到请求后，产生返回结果集时，会在返回给请求线程之前暂存在在这个缓存中，等积累到一定大小的时候才开始向客户端发送，以提高网络效率。不 过，net_buffer_size 所设置的仅仅只是初始大小，MySQL 会根据实际需要自行申请更多的内存，但最大不会超过 max_allowed_packet。

sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 64K

skip-locking用来避免 MySQL 外部锁定，减少出错几率，增强稳定性。

skip-locking

优化后配置

经优化后，my.cnf 的配置如下，top 查看 mysqld 保持在 5M 一下。

[mysqld]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
skip-locking

key_buffer = 16K
query_cache_limit = 256K
query_cache_size = 4M
max_allowed_packet = 1M
table_cache = 8

max_connections = 16
thread_concurrency = 2

sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 64K

skip-bdb
skip-innodb

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer = 8M
sort_buffer_size = 8M

[myisamchk]
key_buffer = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout

内存计算公式

MySQL memory = key_buffer + max_connections *

(join_buffer + record_buffer + sort_buffer + thread_stack + tmp_table_size)