三. 修改table_cache值      

  下面来尝试修改table_cache值, 还是一样找到my.cnf
  [root@xuegod64 etc]# vim my.cnf
  威尼斯城真人赌钱网站 1

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

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

  威尼斯城真人赌钱网站 2

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

参数优化基于一个前提,就是在我们的数据库中通常都使用InnoDB表,而不使用MyISAM表。在优化MySQL时,有两个配置参数是最重要的,即table_cache和key_buffer_size。
table_cache
table_cache指定表高速缓存的大小。每当MySQL访问一个表时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables,可以决定是否需要增加table_cache的值。如果你发现
open_tables等于table_cache,并且opened_tables在不断增长,那么你就需要增加table_cache的值了(上述状态值可以使用SHOW
STATUS LIKE
‘Open%tables’获得)。注意,不能盲目地把table_cache设置成很大的值。如果设置得太高,可能会造成文件描述符不足,从而造成性能不稳定或者连接失
table_cache – 64
open_tables – 64
opened-tables – 431
uptime – 1662790 (measured in seconds)
虽然open_tables已经等于table_cache,但是相对于服务器运行时间来说,opened_tables的值也非常低。因此,增加table_cache的值应该用处不大。
目前从找的材料看 没有必要修改

 

thread_cache

一.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%';

威尼斯城真人赌钱网站 3

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

威尼斯城真人赌钱网站 4

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

SHOW GLOBAL STATUS LIKE '%created_tmp_disk_tables%';

威尼斯城真人赌钱网站 5

总结建议:

    对于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,不同的流量的网站和不同配制的服务器环境,当然需要有不同的配制文件了。


注意:该参数值设置的过大反而会是服务器整体效率降低!

# 1) Use the CHANGE MASTER TO command (fully described in our manual)

# the syntax is:
#
# CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,
# MASTER_USER=, MASTER_PASSWORD= ;
#
# where you replace , , by quoted strings and
# by the master’s port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST=’125.564.12.1′, MASTER_PORT=3306,
# MASTER_USER=’joe’, MASTER_PASSWORD=’secret’;
#
# OR
#
# 2) Set the variables below. However, in case you choose this method,
then
# start replication for the first time (even unsuccessfully, for
example
# if you mistyped the password in master-password and the slave fails
to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables’ values below will be ignored
and
# overridden by the content of the master.info file, unless you
shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 – 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 2
#
# The replication master for this slave – required
#master-host =
#
# The username the slave will use for authentication when connecting
# to the master – required
#master-user =
#
# The password the slave will authenticate with when connecting to
# the master – required
#master-password =
#
# The port the master is listening on.
# optional – defaults to 3306
#master-port =
#
# binary logging – not required for slaves, but recommended
#log-bin

# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname

# Uncomment the following if you are using BDB tables
#bdb_cache_size = 4M
#bdb_max_lock = 10000

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 – 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout


补充

优化table_cachetable_cache指定表高速缓存的大小。每当MySQL访问一个表时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables,可以决定是否需要增加
table_cache的值。如果你发现open_tables等于table_cache,并且opened_tables在不断增长,那么你就需要增加table_cache的值了(上述状态值可以使用SHOW
STATUS LIKE
‘Open%tables’获得)。注意,不能盲目地把table_cache设置成很大的值。如果设置得太高,可能会造成文件描述符不足,从而造成性能不稳定或者连接失败。对于有1G内存的机器,推荐值是128-256。

案例1:该案例来自一个不是特别繁忙的服务器 table_cache – 512open_tables
– 103opened_tables – 1273uptime – 4021421 (measured in
seconds)该案例中table_cache似乎设置得太高了。在峰值时间,打开表的数目比table_cache要少得多。

案例 2:该案例来自一台开发服务器。table_cache – 64open_tables –
64opened-tables – 431uptime – 1662790 (measured in
seconds)虽然open_tables已经等于table_cache,但是相对于服务器运行时间来说,opened_tables的值也非常低。因此,增加table_cache的值应该用处不大。案例3:该案例来自一个upderperforming的服务器table_cache
– 64open_tables – 64opened_tables – 22423uptime –
19538该案例中table_cache设置得太低了。虽然运行时间不到6小时,open_tables达到了最大值,opened_tables的值也非常高。这样就需要增加table_cache的值。优化key_buffer_sizekey_buffer_size指定索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads,可以知道key_buffer_size
设置是否合理。比例key_reads /
key_read_requests应该尽可能的低,至少是1:100,1:1000更好(上述状态值可以使用SHOW
STATUS LIKE
‘key_read%’获得)。key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表,但是内部的临时磁盘表是
MyISAM表,也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。对于1G内存的机器,如果不使用
MyISAM表,推荐值是16M(8-64M)。

案例1:健康状况key_buffer_size – 402649088 (384M)key_read_requests –
597579931key_reads – 56188案例2:警报状态key_buffer_size – 16777216
(16M)key_read_requests – 597579931key_reads –
53832731案例1中比例低于1:10000,是健康的情况;案例2中比例达到1:11,警报已经拉响。

对于1G内存的服务器明显感觉到吃力严重时甚至每天都会死机
或者时不时的服务器卡一下 这个问题曾经困扰了我半…

四.table_cache总结

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

  比较适合的值建议:

  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的办法:把你网站数据库的每个表的索引所占空间大小加起来看看以此服务器为例:比较大的几个表索引加起来大概125M
这个数字会随着表变大而变大。

mysql> SHOW STATUS LIKE key_read%;
+——————-+————+
| Variable_name | Value |
+——————-+————+
| Key_read_requests | 1430416782 |
| Key_reads | 269031 |
+——————-+————+
2 rows in set
[3:42:39 PM] bruce: 总的内存需求公式是:global buffer + connections*
buffer per connection.
global buffer包括:key_buffer_size & innodb_buffer_size
buffer per connection:一般按照4M计算(最差情况),包括:read_buffer,
sort_buffer, thread stack,等等。
[3:47:04 PM] bruce: 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

| Open_tables   | 345     |

参数说明:

二. 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';    

  威尼斯城真人赌钱网站 7

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

  威尼斯城真人赌钱网站 8

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

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

  威尼斯城真人赌钱网站 9

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

  威尼斯城真人赌钱网站 10
  2.1演示下open_tables和opened_tables值的变化(在另一台mysql上进行)

     第一步:

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

  威尼斯城真人赌钱网站 11

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

  威尼斯城真人赌钱网站 12
  第二步:

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

  威尼斯城真人赌钱网站 13

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

  威尼斯城真人赌钱网站 14
  第三步:

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

  威尼斯城真人赌钱网站 15

SHOW  GLOBAL STATUS LIKE 'opened_tables';

  威尼斯城真人赌钱网站 16

对于有1G内存的机器,推荐值是128-256。


    表现:数据库查询效率慢,show processlist 发现比较多的查询正在opening
table。

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

Open tables 256 Opened tables 9046

发现当前一个配置问题 (mysql +linux 在线系统)
show global status like open%tables%;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Open_tables | 966 |
| Opened_tables | 2919 |
+—————+——-+
2 rows in set
根据这些天潜心研究 (下面是有关知识)
table_cache指示表高速缓存的大小。当Mysql访问一个表时,如果在Mysql表缓冲区中还有空间,那么这个表就被打开并放入表缓冲区,这样做的好处是可以更快速地访问表中的内容。一般来说,可以通过查看数据库运行峰值时间的状态值Open_tables和Opened_tables,用以判断是否需要增加table_cache的值
show global status like open%tables%;查看打开表的情况
Open_tables表示打开表的数量,Opened_tables表示打开过的表数量,如果Opened_tables数量过大,说明配置中table_cache(5.1.3之后这个值叫做table_open_cache)值可能太小。
比较适合的值:
Open_tables / Opened_tables * 100% >= 85%
  Open_tables / table_cache * 100% <= 95%
当前我设置这个在线db的 table_cache=1024
由此引发一个问题
966/2919 仅仅为33% 这个是明显有问题的。 (这个系统是主机
有从机器从他那里取数据)
请问我究竟该如何设置这个值 table_cache=??

 

table_cache
指定表高速缓存的大小。每当MySQL访问一个表时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables,可以决定是否需要增加table_cache的值。如果你发现open_tables等于
table_cache,并且opened_tables在不断增长,那么你就需要增加table_cache的值了(上述状态值可以使用SHOW
STATUS LIKE
‘Open%tables’获得)。注意,不能盲目地把table_cache设置成很大的值。如果设置得太高,可能会造成文件描述符不足,从而造成性能不稳定或者连接失败。

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

(mysql +linux 在线系统)
show global status like open%tables%; +—————+——-+ |
Variable_name | Value | +—————+——-+ |…

+—————+———+

对于有1G内存的机器,推荐值是128-256。

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

| Variable_name          | Value      |

得到以下状态:

key_buffer_sizequery_cache_sizetable_cache

key_buffer_size =512M

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

table_cache指定表高速缓存的大小。每当MySQL访问一个表时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables,可以决定是否需要增加table_cache的值。如果你发现open_tables等于table_cache,并且opened_tables在不断增长,那么你就需要增加table_cache的值了(上述状态值可以使用SHOW
STATUS LIKE
‘Open%tables’获得)。注意,不能盲目地把table_cache设置成很大的值。如果设置得太高,可能会造成文件描述符不足,从而造成性能不稳定或者连接失败。

    Opened_tables数值非常大,说明cache太小,导致要频繁地open
table,可以查看下当前的table_open_cache设置:

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获得的实例分析:

Qcache_free_blocks,如果该值非常大,则表明缓冲区中碎片很多query_cache_type指定是否使用查询缓冲

MYSQL默认的table_open_cache为64,这个数值是偏小的,如果max_connections较大,则容易引起性能问题。

要求
MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求,这就起作用,然后主线程花些时间(尽管很短)
检查连接并且启动一个新线程。back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接,你需要增加它,换句话说,这值对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。
Unix
listen(2)系统调用的手册页应该有更多的细节。检查你的OS文档找出这个变量的最大值。试图设定back_log高于你的操作系统的限制将是无效的。

虽然open_tables已经等于table_cache,但是相对于服务器运行时间来说,已经运行了20天,opened_tables的值也非常低。因此,增加table_cache的值应该用处不大。如果运行了6个小时就出现上述值
那就要考虑增大table_cache。 123下一页阅读全文

| Key_read_requests      | 3633676486 |

关掉这个功能只需要在他前面加上#号

相关文章