mysql优化配置
1、⽬的：
通过根据服务器⽬前状况，修改Mysql的系统参数，达到合理利⽤服务器现有资源，最⼤合理的提⾼MySQL性能。
2、服务器参数：
32G内存、4个CPU，每个CPU 8核。
3、MySQL⽬前安装状况。
MySQL⽬前安装，⽤的是MySQL默认的最⼤⽀持配置。拷贝的是my-hugef.编码已修改为UTF-8.具体修改及安装MySQL，可以参考<<Linux系统上安装MySQL 5.5>>帮助⽂档。
4、修改MySQL配置
打开MySQL配置⽂件myf
vi /etc/myf
4.1 MySQL⾮缓存参数变量介绍及修改
4.1.1修改back_log参数值：由默认的50修改为500.（每个连接256kb，占⽤：125M）
back_log=500
back_log值指出在MySQL暂时停⽌回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说，如果MySql的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某⼀连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。将会报：
unauthenticated user | x | NULL | Connect | NULL | login | NULL 的待连接进程时.
back_log值不能超过TCP/IP连接的侦听队列的⼤⼩。若超过则⽆效，查看当前系统的TCP/IP连接的侦听队列的⼤⼩命令：cat /proc/sys/net/ipv4/tcp_max_syn_backlog⽬前系统为1024。对于Linux系统推荐设置为⼩于512的整数。
查看mysql 当前系统默认back_log值，命令：
show variables like 'back_log'; 查看当前数量
4.1.2修改wait_timeout参数值，由默认的8⼩时，修改为30分钟。(本次不⽤)
wait_timeout=1800（单位为妙）
我对wait-timeout这个参数的理解：MySQL客户端的数据库连接闲置最⼤时间值。
说得⽐较通俗⼀点，就是当你的MySQL连接闲置超过⼀定时间后将会被强⾏关闭。MySQL默认的wait-timeout 值为8个⼩时，可以通过命令show variables like 'wait_timeout'查看结果值;。
设置这个值是⾮常有意义的，⽐如你的⽹站有⼤量的MySQL链接请求（每个MySQL连接都是要内存资源开销的），由于你的程序的原因有⼤量的连接请求空闲啥事也不⼲，⽩⽩占⽤内存资源，或者导致MySQL超过最⼤连接数从来⽆法新建连接导致“Too many connections”的错误。在设置之前你可以查看⼀下你的MYSQL的状态（可⽤
show processlist)，如果经常发现MYSQL中有⼤量的Sleep进程，则需要修改wait-timeout值了。
interactive_timeout：服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使⽤CLIENT_INTERACTIVE选项的客户端。
wait_timeout：服务器关闭⾮交互连接之前等待活动的秒数。在线程启动时，根据全局wait_timeout值
或全局 interactive_timeout值初始化会话wait_timeout值，取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义).
这两个参数必须配合使⽤。否则单独设置wait_timeout⽆效
4.1.3修改max_connections参数值，由默认的151，修改为3000（750M）。
max_connections=3000
max_connections是指MySql的最⼤连接数，如果服务器的并发连接请求量⽐较⼤，建议调⾼此值，以增加并⾏连接数量，当然这建⽴在机器能⽀撑的情况下，因为如果连接数越多，介于MySql会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲⽬提⾼设值。可以过'conn%'通配符查看当前状态的连接数量，以定夺该值的⼤⼩。
MySQL服务器允许的最⼤连接数16384；
查看系统当前最⼤连接数：
show variables like 'max_connections';
4.1..4修改max_user_connections值，由默认的0，修改为800
max_user_connections=800
max_user_connections是指每个数据库⽤户的最⼤连接
针对某⼀个账号的所有客户端并⾏连接到MYSQL服务的最⼤并⾏连接数。简单说是指同⼀个账号能够同时连接到mysql服务的最⼤连接数。设置为0表⽰不限制。
⽬前默认值为：0不受限制。
这⼉顺便介绍下Max_used_connections：它是指从这次mysql服务启动到现在，同⼀时刻并⾏连接数的最⼤值。它不是指当前的连接情况，⽽是⼀个⽐较值。如果在过去某⼀个时刻，MYSQL服务同时有1000个请求连接过来，⽽之后再也没有出现这么⼤的并发请求时，则Max_used_connections=1000.请注意与show variables ⾥的
max_user_connections的区别。默认为0表⽰⽆限⼤。
查看max_user_connections值
show variables like 'max_user_connections';
4.1.5修改thread_concurrency值，由⽬前默认的8，修改为64
thread_concurrency=64
thread_concurrency的值的正确与否, 对mysql的性能影响很⼤, 在多个cpu(或多核)的情况下，错误设置了thread_concurrency的值, 会导致mysql不能充分利⽤多cpu(或多核), 出现同⼀时刻只能⼀个cpu(或核)在⼯作的情况。
thread_concurrency应设为CPU核数的2倍. ⽐如有⼀个双核的CPU, 那thread_concurrency 的应该为4; 2个双核的cpu, thread_concurrency的值应为8.
⽐如：根据上⾯介绍我们⽬前系统的配置，可知道为4个CPU，每个CPU为8核，按照上⾯的计算规则，这⼉应为:4*8*2=64
查看系统当前thread_concurrency默认配置命令：
show variables like 'thread_concurrency';
4.1.6添加skip-name-resolve，默认被注释掉，没有该参数。
skip-name-resolve
skip-name-resolve：禁⽌MySQL对外部连接进⾏DNS解析，使⽤这⼀选项可以消除MySQL进⾏DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使⽤IP地址⽅式，否则MySQL将⽆法正常处理连接请求！
4.1.7 skip-networking，默认被注释掉。没有该参数。（本次⽆⽤）
skip-networking建议被注释掉，不要开启
开启该选项可以彻底关闭MySQL的TCP/IP连接⽅式，如果WEB服务器是以远程连接的⽅式访问MySQL数据库服务器则不要开启该选项！否则将⽆法正常连接！
4.1.8 default-storage-engine(设置MySQL的默认存储引擎)
default-storage-engine= InnoDB(设置InnoDB类型，另外还可以设置MyISAM类型)
设置创建数据库及表默认存储类型
show table status like ‘tablename’显⽰表的当前存储状态值
查看MySQL有哪些存储状态及默认存储状态
show engines;
创建表并指定存储类型
CREATE TABLE mytable (id int, title char(20)) ENGINE = INNODB;
修改表存储类型：
Alter table tableName engine =engineName
备注：设置完后把以下⼏个开启：
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:1024M;ibdata2:10M:autoextend（要注释掉，否则会创建⼀个新的把原来的替换的。）
innodb_log_group_home_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 = 1000M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 500M
innodb_log_buffer_size = 20M
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 50
设置完后⼀定记得把MySQL安装⽬录地址（我们⽬前是默认安装所以地址/var/lib/mysql/）下的ib_logfile0和ib_logfile1删除掉。否则重启MySQL起动失败。
4.2 MySQL缓存变量介绍及修改
数据库属于IO密集型的应⽤程序，其主职责就是数据的管理及存储⼯作。⽽我们知道，从内存中读取⼀个数据库的时间是微秒级别，⽽从⼀块普通硬盘上读取⼀个 IO是在毫秒级别，⼆者相差3个数量级。所以，要优化数据库，⾸先第⼀步需要优化的就是IO，尽可能将磁盘IO转化为内存IO。本⽂先从MySQL数据库 IO相关参数(缓存参数)的⾓度来看看可以通过哪些参数进⾏IO优化
4.2.1全局缓存
启动MySQL时就要分配并且总是存在的全局缓存。⽬前有：key_buffer_size(默认值：402653184，即384M)、innodb_buffer_pool_size(默认值：134217728即：128M)、innodb_additional_mem_pool_size（默认值：8388608即：8M）、innodb_log_buffer_size(默认值：8388608即：8M)、query_cache_size(默认值：33554432即：32M)等五个。总共：560M.
这些变量值都可以通过命令如：show variables like '变量名'；查看到。
4.2.1.1：key_buffer_size，本系统⽬前为384M，可修改为400M
key_buffer_size=400M
key_buffer_size是⽤于索引块的缓冲区⼤⼩，增加它可得到更好处理的索引(对所有读和多重写)，对MyISAM(MySQL表存储的⼀种类型，可以百度等查看详情)表性能影响最⼤的⼀个参数。如果你使它
太⼤，系统将开始换页并且真的变慢了。严格说是它决定了数据库索引处理的速度，尤其是索引读的速度。对于内存在4GB左右的服务器该参数可设置为256M或384M.
怎么才能知道key_buffer_size的设置是否合理呢，⼀般可以检查状态值Key_read_requests和Key_reads ，⽐例key_reads / key_read_requests应该尽可能的低，⽐如
1:100，1:1000 ，1:10000。其值可以⽤以下命令查得：show status like 'key_read%';
⽐如查看系统当前key_read和key_read_request值为：
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Key_read_requests | 28535 |
| Key_reads | 269 |前端开发培训学校快速
+-------------------+-------+
可知道有28535个请求，有269个请求在内存中没有到直接从硬盘读取索引.
未命中缓存的概率为：0.94%=269/28535*100%. ⼀般未命中概率在0.1之下⽐较好。⽬前已远远⼤于0.1，证明效果不好。若命中率在0.01以下，则建议适当的修改
key_buffer_size值。
MyISAM、InnoDB、MyISAM Merge引擎、InnoDB、memory(heap)、archive
4.2.1.2：innodb_buffer_pool_size(默认128M)
innodb_buffer_pool_size=1024M(1G)
innodb_buffer_pool_size：主要针对InnoDB表性能影响最⼤的⼀个参数。功能与Key_buffer_size⼀样。InnoDB占⽤的内存，除innodb_buffer_pool_size⽤于存储页⾯缓存数据外，另外正常情况下还有⼤约8%的开销，主要⽤在每个缓存页帧的描述、adaptive hash等数据结构，如果不是安全关闭，启动时还要恢复的话，还要另开⼤约12%的内存⽤于恢复，两者相加就有差不多21%的开销。假设：12G的innodb_buffer_pool_size，最多的时候InnoDB就可能占⽤到14.5G的内存。若系统只有16G，⽽且只运⾏MySQL，且MySQL只⽤InnoDB，
那么为MySQL开12G，是最⼤限度地利⽤内存了。
另外InnoDB和 MyISAM 存储引擎不同， MyISAM 的 key_buffer_size 只能缓存索引键，⽽ innodb_buffer_pool_size 却可以缓存数据块和索引键。适当的增加这个参数的⼤⼩，可以有效的减少 InnoDB 类型的表的磁盘 I/O 。
当我们操作⼀个 InnoDB 表的时候，返回的所有数据或者去数据过程中⽤到的任何⼀个索引块，都会在这个内存区域中⾛⼀遭。
可以通过 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 计算缓存命中率，并根据命中率来调
整 innodb_buffer_pool_size 参数⼤⼩进⾏优化。值可以⽤以下命令查得：show status like 'Innodb_buffer_pool_read%';
⽐如查看当前系统中系统中
| Innodb_buffer_pool_read_requests | 1283826 |
| Innodb_buffer_pool_reads | 519 |
+---------------------------------------+---------+
其命中率99.959%=（1283826-519）/1283826*100% 命中率越⾼越好。
4.2.1.3：innodb_additional_mem_pool_size(默认8M)
innodb_additional_mem_pool_size=20M
innodb_additional_mem_pool_size 设置了InnoDB存储引擎⽤来存放数据字典信息以及⼀些内部数据结构的内存空间⼤⼩，所以当我们⼀个MySQL Instance中的数据库对象⾮常多的时候，是需要适当调整该参数的⼤⼩以确保所有数据都能存放在内存中提⾼访问效率的。
这个参数⼤⼩是否⾜够还是⽐较容易知道的，因为当过⼩的时候，MySQL会记录Warning信息到数据库的error log中，这时候你就知道该调整这个参数⼤⼩了。
查看当前系统mysql的error⽇志 cat /var/lib/mysql/机器名.error 发现有很多waring警告。所以要调⼤为20M.
根据MySQL⼿册，对于2G内存的机器，推荐值是20M。
32G内存的 100M
4.2.1.4：innodb_log_buffer_size(默认8M)
innodb_log_buffer_size=20M
innodb_log_buffer_size 这是InnoDB存储引擎的事务⽇志所使⽤的缓冲区。类似于Binlog Buffer，InnoDB在写事务⽇志的时候，为了提⾼性能，也是先将信息写⼊
Innofb Log Buffer中，当满⾜innodb_flush_log_trx_commit参数所设置的相应条件(或者⽇志缓冲区写满)之后，才会将⽇志写到⽂件 (或者同步到磁盘)中。可以通过
innodb_log_buffer_size 参数设置其可以使⽤的最⼤内存空间。
InnoDB 将⽇志写⼊⽇志磁盘⽂件前的缓冲⼤⼩。理想值为 1M ⾄ 8M。⼤的⽇志缓冲允许事务运⾏时不需要将⽇志保存⼊磁盘⽽只到事务被提交(commit)。因此，如果有⼤的事务处理，设置⼤的⽇志缓冲可以减少磁盘I/O。在myf中以数字格式设置。
默认是8MB，系的如频繁的系统可适当增⼤⾄4MB～8MB。当然如上⾯介绍所说，这个参数实际上还和另外的flush参数相关。⼀般来说不建议超过32MB
注：innodb_flush_log_trx_commit参数对InnoDB Log的写⼊性能有⾮常关键的影响，默认值为1。该参数可以设置为0，1，2，解释如下：
0：log buffer中的数据将以每秒⼀次的频率写⼊到log file中，且同时会进⾏⽂件系统到磁盘的同步操作，但是每个事务的commit并不会触发任何log buffer 到log file的刷新或者⽂件系统到磁盘的刷新操作;
1：在每次事务提交的时候将log buffer 中的数据都会写⼊到log file，同时也会触发⽂件系统到磁盘的同步;
2：事务提交会触发log buffer到log file的刷新，但并不会触发磁盘⽂件系统到磁盘的同步。此外，每秒会有⼀次⽂件系统到磁盘同步操作。
实际测试发现，该值对插⼊数据的速度影响⾮常⼤，设置为2时插⼊10000条记录只需要2秒，设置为0时只需要1秒，⽽设置为1时则需要229秒。因此，MySQL⼿册也建议尽量将插⼊操作合并成⼀个事务，这样可以⼤幅提⾼速度。根据MySQL⼿册，在存在丢失最近部分事务的危险的前提下，可以把该值设为0。
4.5.1.5：query_cache_size(默认32M)
query_cache_size=40M
query_cache_size: 主要⽤来缓存MySQL中的ResultSet，也就是⼀条SQL语句执⾏的结果集，所以仅
仅只能针对select语句。当我们打开了 Query Cache功能，MySQL在接受到⼀条select语句的请求后，如果该语句满⾜Query Cache的要求(未显式说明不允许使⽤Query Cache，或者已经显式申明需要使⽤Query Cache)，MySQL会直接根据预先设定好的HASH算法将接受到的select语句以字符串⽅式进⾏hash，然后到Query Cache中直接查是否已经缓存。也就是说，如果已经在缓存中，该select请求就会直接将数据返回，从⽽省略了后⾯所有的步骤(如SQL语句的解析，优化器优化以及向存储引擎请求数据等)，极⼤的提⾼性能。根据MySQL⽤户⼿册，使⽤查询缓冲最多可以达到238%的效率。
当然，Query Cache也有⼀个致命的缺陷，那就是当某个表的数据有任何任何变化，都会导致所有引⽤了该表的select语句在Query Cache中的缓存数据失效。所以，当我们的数据变化⾮常频繁的情况下，使⽤Query Cache可能会得不偿失
coreelec 教程Query Cache的使⽤需要多个参数配合，其中最为关键的是query_cache_size和query_cache_type，前者设置⽤于缓存 ResultSet的内存⼤⼩，后者设置在何场景下使⽤Query Cache。在以往的经验来看，如果不是⽤来缓存基本不变的数据的MySQL数据库，query_cache_size⼀般256MB是⼀个⽐较合适的⼤⼩。当然，这可以通过计算
Query Cache的命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))来进⾏调整。 query_cache_type可以设置为0(OFF)，1(ON)或者2(DEMOND)，分别表⽰完全不使⽤
query cache，除显式要求不使⽤query cache(使⽤sql_no_cache)之外的所有的select都使⽤query cache，只有显⽰要求才使⽤query cache(使⽤sql_cache)。如果
Qcache_lowmem_prunes的值⾮常⼤，则表明经常出现缓冲. 如果Qcache_hits的值也⾮常⼤，则表明查询缓冲使⽤⾮常频繁，此时需要增加缓冲⼤⼩；
根据命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))进⾏调整，⼀般不建议太⼤，256MB可能已经差不多了，⼤型的配置型静态数据可适当调⼤.
可以通过命令：show status like 'Qcache_%'；查看⽬前系统Query catch使⽤⼤⼩
| Qcache_hits | 1892463 |
| Qcache_inserts | 35627
命中率98.17%=1892463/(1892463 +35627 )*100
4.2.2局部缓存
除了全局缓冲，MySql还会为每个连接发放连接缓冲。个连接到MySQL服务器的线程都需要有⾃⼰的
缓冲。⼤概需要⽴刻分配256K，甚⾄在线程空闲时，它们使⽤默认的线程堆栈，⽹络缓存等。事务开始之后，则需要增加更多的空间。运⾏较⼩的查询可能仅给指定的线程增加少量的内存消耗，然⽽如果对数据表做复杂的操作例如扫描、排序或者需要临时表，则需分配⼤约read_buffer_size，
sort_buffer_size，read_rnd_buffer_size，tmp_table_size ⼤⼩的内存空间. 不过它们只是在需要的时候才分配，并且在那些操作做完之后就释放了。有的是⽴刻分配成单独的组块。tmp_table_size 可能⾼达MySQL所能分配给这个操作的最⼤内存空间了
。注意，这⾥需要考虑的不只有⼀点——可能会分配多个同⼀种类型的缓存，例如⽤来处理⼦查询。⼀些特殊的查询的内存使⽤量可能更⼤——如果在MyISAM表上做成批的插⼊
时需要分配 bulk_insert_buffer_size ⼤⼩的内存；执⾏ ALTER TABLE， OPTIMIZE TABLE， REPAIR TABLE 命令时需要分配 myisam_sort_buffer_size ⼤⼩的内存。
4.2.2.1：read_buffer_size（默认值：2097144即2M）
read_buffer_size=4M
read_buffer_size 是MySql读⼊缓冲区⼤⼩。对表进⾏顺序扫描的请求将分配⼀个读⼊缓冲区，MySql会为它分配⼀段内存缓冲区。read_buffer_size变量控制这⼀
缓冲区的⼤⼩。如果对表的顺序扫描请求⾮常频繁，并且你认为频繁扫描进⾏得太慢，可以通过增加该变量值以及内存缓冲区⼤⼩提⾼其性能.
4.2.2.2：sort_buffer_size（默认值：2097144即2M）
sort_buffer_size=4M
sort_buffer_size是MySql执⾏排序使⽤的缓冲⼤⼩。如果想要增加ORDER BY的速度，⾸先看是否可以让MySQL使⽤索引⽽不是额外的排序阶段。如果不能，可以尝试增加sort_buffer_size变量的⼤⼩
4.2.2.3: read_rnd_buffer_size(默认值：8388608即8M)
read_rnd_buffer_size=8M
read_rnd_buffer_size 是MySql的随机读缓冲区⼤⼩。当按任意顺序读取⾏时(例如，按照排序顺序)，将分配⼀个随机读缓存区。进⾏排序查询时，MySql会⾸先扫描⼀遍该缓冲，以避免磁盘搜索，提⾼查询速度，如果需要排序⼤量数据，可适当调⾼该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开
销过⼤。
4.2.2.4: tmp_table_size(默认值：8388608 即：16M)
tmp_table_size=16M
tmp_table_size是MySql的heap （堆积）表缓冲⼤⼩。所有联合在⼀个DML指令内完成，并且⼤多数联合甚⾄可以不⽤临时表即可以完成。⼤多数临时表是基于内
存的(HEAP)表。具有⼤的记录长度的临时表 (所有列的长度的和)或包含BLOB列的表存储在硬盘上。如果某个内部heap（堆积）表⼤⼩超过tmp_table_size，MySQL可以根据需要⾃
动将内存中的heap表改为基于硬盘的MyISAM表。还可以通过设置tmp_table_size选项来增加临时表的⼤⼩。也就是说，如果调⾼该值，MySql同时将增加heap表的⼤⼩，可达到提⾼
联接查询速度的效果。
4.2.2.5：record_buffer:（默认值：）
record_buffer每个进⾏⼀个顺序扫描的线程为其扫描的每张表分配这个⼤⼩的⼀个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。默认数值是131072
(128K)
4.2.3其它缓存：
4.2.3.1：table_cache(默认值：512)
TABLE_CACHE(5.1.3及以后版本⼜名TABLE_OPEN_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设置成很⼤的值。如果设置得太⾼，可能会造成⽂件描述符不⾜，从⽽造成性能不稳定或者连接失败。
SHOW STATUS LIKE 'Open%tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 356 |
| Opened_tables | 0 |
+---------------+-------+
2 rows in set (0.00 sec)
open_tables表⽰当前打开的表缓存数，如果执⾏flush tables操作，则此系统会关闭⼀些当前没有使⽤的表缓存⽽使得此状态值减⼩；
计算机语言有几种opend_tables表⽰曾经打开的表缓存数，会⼀直进⾏累加，如果执⾏flush tables操作，值不会减⼩。
在mysql默认安装情况下，table_cache的值在2G内存以下的机器中的值默认时256到512，如果机器有4G内存，则默认这个值是2048，但这决意味着机器内存越⼤，这个值应该越⼤，因为table_cache加⼤后，使得mysql对SQL响应的速度更快了，不可避免的会产⽣更多的死锁（dead lock），这样反⽽使得数据库整个⼀套操作慢了下来，严重影响性能。所以平时维护中还是要根据库的实际情况去作出判断，到最适合你维护的库的 table_cache值。
由于MySQL是多线程的机制，为了提⾼性能，每个线程都是独⾃打开⾃⼰需要的表的⽂件描述符,⽽不是通过共享已经打开的.针对不同存储引擎处理的⽅法当然也不⼀样
在myisam表引擎中，数据⽂件的描述符 (descriptor)是不共享的，但是索引⽂件的描述符却是所有线程共享的.Innodb中和使⽤表空间类型有关，假如是共享表空间那么实际就⼀个
数据⽂件，当然占⽤的数据⽂件描述符就会⽐独⽴表空间少.
mysql⼿册上给的建议⼤⼩是:table_cache=max_connections*n
n表⽰查询语句中最⼤表数, 还需要为临时表和⽂件保留⼀些额外的⽂件描述符。
这个数据遭到很多质疑,table_cache够⽤就好，检查 Opened_tables值，如果这个值很⼤，或增长很快那么你就得考虑加⼤table_cache了.
table_cache：所有线程打开的表的数⽬。增⼤该值可以增加mysqld需要的⽂件描述符的数量。默认值是64.
4.2.3.2 thread_cache_size (服务器线程缓存)
thread_cache_size=64
默认的thread_cache_size=8，但是看到好多配置的样例⾥的值⼀般是32，64，甚⾄是128，感觉这个参数对优化应该有帮助，于是查了下：
根据调查发现以上服务器线程缓存thread_cache_size没有进⾏设置，或者设置过⼩，这个值表⽰可以重新利⽤保存在缓存中线程的数量，当断开连接时如果缓存中还有空间，那么客
户端的线程将被放到缓存中，如果线程重新被请求，那么请求将从缓存中读取，如果缓存中是空的或者是新的请求，那么这个线程将被重新创建，如果有很多新的线程，增加这个值
可以改善系统性能.通过⽐较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作⽤。(–>表⽰要调整的值) 根据物理内存设置规则如下：
1G —> 8
2G —> 16
3G —> 32 >3G —> 64
mysql> show status like 'thread%';
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_cached | 0 | <—当前被缓存的空闲线程的数量
| Threads_connected | 1 | <—正在使⽤（处于连接状态）的线程
| Threads_created | 1498 | <—服务启动以来，创建了多少个线程
| Threads_running | 1 | <—正在忙的线程（正在查询数据，传输数据等等操作）
+——————-+——-+
查看开机起来数据库被连接了多少次？
mysql> show status like '%connection%';
+———————-+——-+
| Variable_name | Value |
+———————-+——-+
| Connections | 1504 | –>服务启动以来，历史连接数
| Max_used_connections | 2 |
+———————-+——-+
通过连接线程池的命中率来判断设置值是否合适？命中率超过90%以上，设定合理。
(Connections - Threads_created) / Connections * 100 %
5.查看命令总结
5.1.0查看各参数内存配置⽅式
#全局共享内存 9个变量
show variables like 'innodb_buffer_pool_size'; /* InnoDB 数据和索引缓存（InnoDB Buffer Pool） */
show variables like 'innodb_additional_mem_pool_size'; /* InnoDB 字典信息缓存（InnoDB Additional Memory Pool）*/
show variables like 'innodb_log_buffer_size'; /* InnoDB ⽇志缓冲区（InnoDB Log Buffer） */
show variables like 'binlog_cache_size'; /* ⼆进制⽇志缓冲区（Binlog Buffer）*/
show variables like 'thread_cache_size'; /* 连接线程缓存（Thread Cache）*/
vlookup函数总是出错show variables like 'query_cache_size'; /* 查询缓存（Query Cache）*/
show variables like 'table_open_cache'; /* 表缓存（Table Cache） */
show variables like 'table_definition_cache'; /* 表定义信息缓存（Table definition Cache） */
show variables like 'key_buffer_size'; /* MyISAM索引缓存（Key Buffer） */
#最⼤线程数
show variables like 'max_connections';
#线程独享内存 6个变量
show variables like 'thread_stack'; /* 线线程栈信息使⽤内存(thread_stack) */
show variables like 'sort_buffer_size'; /* 排序使⽤内存(sort_buffer_size) */
show variables like 'join_buffer_size'; /* Join操作使⽤内存(join_buffer_size) */
show variables like 'read_buffer_size'; /* 顺序读取数据缓冲区使⽤内存(read_buffer_size) */
show variables like 'read_rnd_buffer_size'; /* 随机读取数据缓冲区使⽤内存(read_rnd_buffer_size) */
show variables like 'tmp_table_size'; /* 临时表使⽤内存(tmp_table_size) ，我实际计算把tmp_table_size放⼊全局共享内*/
也可以通过系统变量的⽅式直接获取
select @@key_buffer_size;
select @@max_connections
5.1.1mysql内存计算公式
mysql使⽤的内存 = 全局共享内存+最⼤线程数×线程独享内存
mysql used
mem=innodb_buffer_pool_size+innodb_additional_mem_pool_size+innodb_log_buffer_size+binlog_cache_size+thread_cache_size+query_cache_size+table_open_cache+table_definition_cache+ke +max_connections*（
thread_stack+sort_buffer_size+join_buffer_size+read_buffer_size+read_rnd_buffer_size+tmp_table_size）
SET @kilo_bytes=1024;
SET @mega_bytes=@kilo_bytes*1024;
SET @giga_bytes=@mega_bytes*1024;
SELECT
(@@innodb_buffer_pool_size+@@innodb_additional_mem_pool_size+@@innodb_log_buffer_size+@@binlog_cache_size+@@thread_cache_size+@@query_cache_size+@@table_open_cache+ (@@thread_stack+@@sort_buffer_size+@@join_buffer_size+@@read_buffer_size+@@read_rnd_buffer_size+@@tmp_table_size))/@giga_bytes AS MAX_MEMORY_GB;编译原理句柄定义
mysql是什么系统这个理论最⼤的内存使⽤量，在5.5版本中tmp_table_size默认是16M，按默认u⾃⼤连接数151计算，光线程独享的临时表占据的空间都是2416M，我实际计算把tmp_table_size
放⼊全局共享内
我的计算公式
mysql使⽤的内存 = 全局共享内存+最⼤线程数×线程独享内存
mysql used
mem=innodb_buffer_pool_size+innodb_additional_mem_pool_size+innodb_log_buffer_size+binlog_cache_size+thread_cache_size+query_cache_size+table_open_cache+table_definition_cache+ke +max_connections*（
thread_stack+sort_buffer_size+join_buffer_size+read_buffer_size+read_rnd_buffer_size）
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论