mysql数据库的优化(上)

Posted by admin

07月 24, 2009

当网站在线访问量持续增大时，对于服务器的压力也增大了，此时服务器会不间断出现内存用尽、死机等现象。这个问题困扰了我好长时间，其实MySQL使用是很具伸缩性的算法，通常能用很少的内存运行或给MySQL合适的内存，以得到更好的性能。

安装好mysql后，配制文件在/usr/local/mysql/share/mysql目录中，根据需要你可以选择合适的配制文件，有my-huge.cnf 、my-medium.cnf、my-large.cnf、my-small.cnf，不同流量的网站根据需要选择不同的配制文件。

一般情况而言，my-medium.cnf配制文件就能满足大多网站需要；我们会把配置文件拷贝到/etc/my.cnf 只需要修改这个配置文件就可以了，使用mysqladmin variables extended-status –u root –p 命令可以看到目前的参数，有３个配置参数是最重要的，即key_buffer_size，query_cache_size，table_cache。

key_buffer_size只对MyISAM表起作用，该参数指定了索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。一般我们设为16M，实际上稍微大一点的站点这个数字是远远不够的，通过检查状态值Key_read_requests和 Key_reads,可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得）。或者如果你装了phpmyadmin 可以通过服务器运行状态看到，笔者推荐用phpmyadmin管理mysql，以下的状态值都是本人通过phpmyadmin获得的实例分析:

这个服务器已经运行了20天

key_buffer_size – 128M
key_read_requests – 650759289
key_reads - 79112

比例接近1:8000 健康状况非常好。

另外一个估计key_buffer_size的办法：把你网站数据库的每个表的索引所占空间大小加起来看看。以此服务器为例，比较大的几个表索引加起来大概125M 这个数字会随着表变大而变大。

从4.0.1开始，MySQL提供了查询缓冲机制。使用查询缓冲，MySQL将SELECT语句和查询结果存放在缓冲区中，今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。根据MySQL用户手册，使用查询缓冲最多可以达到238%的效率。

通过调节以下几个参数可以知道query_cache_size设置得是否合理：

Qcache inserts
Qcache hits
Qcache lowmem prunes
Qcache free blocks
Qcache total blocks

Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，同时Qcache_hits的值非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小。Qcache_hits的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。此外，在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多，query_cache_type指定是否使用查询缓冲。

我设置:

query_cache_size = 32M query_cache_type= 1

得到如下状态值:

Qcache queries in cache 12737 表明目前缓存的条数
Qcache inserts 20649006
Qcache hits 79060095 　看来重复查询率还挺高的
Qcache lowmem prunes 617913　有这么多次出现缓存过低的情况
Qcache not cached 189896
Qcache free memory 18573912　　目前剩余缓存空间
Qcache free blocks 5328 这个数字似乎有点大　碎片不少
Qcache total blocks 30953

如果内存允许32M应该要往上加点。

数据库