金沙注册网站-新金沙官网 计算机数据 新金沙官网MySQL数据库优化经验详谈第1,开发进阶篇系列

新金沙官网MySQL数据库优化经验详谈第1,开发进阶篇系列



一.key_buffer

  上一篇了解key_buffer设置,key_buffer_size指定了索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads,可以知道key_buffer_size设置是否合理。比例key_reads
/key_read_requests应该尽可能的低,至少是1:100,1:1000更好(理解为key_reads物理IO次数越少越好)。

--   一共有Key_read_requests个索引请求,一共发生了Key_reads次物理IO
SHOW GLOBAL STATUS LIKE '%key_read%';

新金沙官网 1

--  Key_reads/Key_read_requests ≈ 0.1%以下比较好
SELECT 693206.0/94745304.0

新金沙官网 2

  key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表,但是内部的临时磁盘表是MyISAM表,也要使用该值,可以使用检查状态值created_tmp_disk_tables得知详情。

SHOW GLOBAL STATUS LIKE '%created_tmp_disk_tables%';

新金沙官网 3

总结建议:

    对于1G内存的机器,如果不使用MyISAM表,推荐值是16M(8-64M)。
    单个key_buffer的大小不能超过4G。
    建议key_buffer设置为物理内存的1/4(针对MyISAM引擎),在很多情况下数据要比索引大得多。
    如果机器性能优越,可以设置多个key_buffer,分别让不同的key_buffer来缓存专门的索引。
    Key_reads/Key_read_requests的大小正常情况下得小于0.01。

安装好mysql后,配制文件应该在/usr/local/mysql/share/mysql目录中,配制文件有几个,有my-
huge.cnf my-medium.cnf my-large.cnf
my-small.cnf,不同的流量的网站和不同配制的服务器环境,当然需要有不同的配制文件了。

二. table_cache (table_open_cache)  

  上面讲了索引缓存,这里讲表缓存 table_cache,在mysql
5.1之后叫做”table_open_cache”。这个参数表示数据库用户打开表的缓存数量(最大限制数),用于设置table高速缓存的数量。由于每个客户端连接都会至少访问一个表,因此此参数的值与max_connections有关。例如
对于200个并行运行的连接,应该让表的缓存至少有200 *
N。这里N是可以执行的查询的一个连接中表的最大数量(表数量)。
  表缓存机制是:当某一连接访问一个表时,MySQL会检查当前已缓存表的数量。如果该表已经在缓存中打开,则会直接访问缓存中的表已加快查询速度;如果该表未被缓存,则会将当前的表添加进缓存并进行查询。
  在执行缓存操作之前,table_cache用于限制缓存表的最大数目:如果当前已经缓存的表未达到table_cache,则会将新表添加进来;若已经达到此值,MySQL将根据缓存表的最后查询时间、查询率等规则释放之前的缓存(释放机制与sqlserver一样)。

-- 表缓存限制数(默认是2000次)
SHOW VARIABLES LIKE 'table_open_cache';    

  新金沙官网 4

-- 最大并发连接数
SHOW VARIABLES LIKE 'max_connections';

  新金沙官网 5

  可以通过检查mysqld的状态变量open_tables和opened_tables确定table_cache参数是否过小。
open_tables表示当前打开的表缓存数,如果执行flush
tables操作,则系统会关闭一些当前没有使用的表缓存,而使得些状态值减小。opened_tables表示曾经打开的表缓存数(历史的),会一直进行累加。执行flush
tables值不会减少。

-- 当前打开的表缓存数
SHOW  GLOBAL STATUS LIKE 'open_tables';

  新金沙官网 6

-- 曾经打开的表缓存数
SHOW  GLOBAL STATUS LIKE 'opened_tables';

  新金沙官网 7
  2.1演示下open_tables和opened_tables值的变化(在另一台mysql上进行)

     第一步:

-- 清空表缓存
FLUSH TABLES;
-- 查看值为1(代表当前连接)
SHOW  GLOBAL STATUS LIKE 'open_tables';

  新金沙官网 8

-- 历史值为111
SHOW  GLOBAL STATUS LIKE 'opened_tables';

  新金沙官网 9
  第二步:

-- 执行一个查询
SELECT COUNT(1) FROM User1
-- 再次查询当前缓存数
SHOW  GLOBAL STATUS LIKE 'open_tables';

  新金沙官网 10

--历史值也累加到113
SHOW  GLOBAL STATUS LIKE 'opened_tables';

  新金沙官网 11
  第三步:

-- 再执行一个相同查询,  会发现值没有增加,因为读的是缓存。
SELECT COUNT(1) FROM User1
SHOW  GLOBAL STATUS LIKE 'open_tables';

  新金沙官网 12

SHOW  GLOBAL STATUS LIKE 'opened_tables';

  新金沙官网 13

一般的情况下,my-medium.cnf这个配制文件就能满足我们的大多需要;一般我们会把配置文件拷贝到/etc/my.cnf
只需要修改这个配置文件就可以了,使用mysqladmin variables extended-status
–u root –p 可以看到目前的参数,有3个配置参数是最重要的,即:

三. 修改table_cache值      

  下面来尝试修改table_cache值, 还是一样找到my.cnf
  [root@xuegod64 etc]# vim my.cnf
  新金沙官网 14

  [root@xuegod64 ~]# systemctl stop mysqld.service
  [root@xuegod64 ~]# /bin/systemctl start mysqld.service

-- 服务停止重启后再次查看表缓存限制数。
SHOW VARIABLES LIKE 'table_open_cache';

  新金沙官网 15

key_buffer_sizequery_cache_sizetable_cache

四.table_cache总结

  open_tables是当前表缓存数,类似于sql server的逻辑查询而非物理查询。
该open_tables的值对设置table_cache值有重要的参考价值。
  如果Open_tables的值已经接近table_cache的值,且Opened_tables还在不断变大,则说明mysql正在将缓存的表释放以容纳新的表,此时可能需要加大table_cache的值。下面这台mysql服务器正是这种情况,1990接近最大限制2000,且历史值还在不断变大。
如下图:
  新金沙官网 16

  比较适合的值建议:

  Open_tables / Opened_tables >= 0.85

  当前mysql的值:SELECT 1990.0/3286078.0=0.00061

  Open_tables / table_cache <= 0.95

  当前mysql的值:1990.0/2000.0=0.99500

key_buffer_size只对MyISAM表起作用。

key_buffer_size指定索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度。一般我们设为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可以明确表示不使用查询缓冲。

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图