MySQL环境变量(运行参数)详细配置

调整MySQL运行参数，修改/etc/my.cnf文件调整mysql运行参数，重启MySQL后生效。

在MySQL4版本以后，一部分内部变量可以在MySQL运行时用cmd set xxx=xxx 进行设置，不过重启MySQL服务就失效了。

 

红色为常用的变量

mysqld程序--目录和文件
basedir = path          # 使用给定目录作为根目录(安装目录)。
datadir = path          # 从给定目录读取数据库文件。
pid-file = filename      # 为mysqld程序指定一个存放进程ID的文件(仅适用于UNIX/Linux系统); 
  
[mysqld]                        
socket = /tmp/mysql.sock     # 为MySQL客户程序与服务器之间的本地通信指定一个套接字文件(Linux下默认是/var/lib/mysql/mysql.sock文件)

port  = 3306      # 指定MsSQL侦听的端口 

key_buffer = 384M      # key_buffer是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)。索引块是缓冲的并且被所有的线程共享，key_buffer的大小视内存大小而定。

table_cache  = 512       # 为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。可以避免频繁的打开数据表产生的开销。

sort_buffer_size = 2M        # 每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。

 注意：该参数对应的分配内存是每连接独占！如果有100个连接，那么实际分配的总共排序缓冲区大小为100×6=600MB。

read_buffer_size = 2M        # 读查询操作所能使用的缓冲区大小。和sort_buffer_size一样，该参数对应的分配内存也是每连接独享。

query_cache_size = 32M       # 指定MySQL查询结果缓冲区的大小。

read_rnd_buffer_size  = 8M       # 改参数在使用行指针排序之后，随机读用的。

myisam_sort_buffer_size =64M       # MyISAM表发生变化时重新排序所需的缓冲。

thread_concurrency  = 8       # 最大并发线程数，取值为服务器逻辑CPU数量×2，如果CPU支持H.T超线程，再×2

thread_cache = 8            #缓存可重用的线程数

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

[mysqldump]
max_allowed_packet =16M          # 服务器和客户端之间最大能发送的可能信息包 
  
[myisamchk]
key_buffer   = 256M
sort_buffer  = 256M
read_buffer  = 2M
write_buffer = 2M 
  
其他可选参数： 
back_log = 384
    指定MySQL可能的连接数量。
    当MySQL主线程在很短时间内接收到非常多的连接请求，该参数生效，主线程花费很短时间检查连接并且启动一个新线程。 
    back_log参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。
    如果系统在一个短时间内有很多连接，则需要增大该参数的值，该参数值指定到来的TCP/IP连接的侦听队列的大小。
    试图设定back_log高于你的操作系统的限制将是无效的。默认值为50。对于Linux系统推荐设置为小于512的整数。 

max_connections = n 
    MySQL服务器同时处理的数据库连接的最大数量(默认设置是100)。超过限制后会报 Too many connections 错误。

 

record_buffer：
    每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。默认数值是131072(128K)

wait_timeout：
    服务器在关闭它之前在一个连接上等待行动的秒数。

interactive_timeout：
    服务器在关闭它前在一个交互连接上等待行动的秒数。
    一个交互的客户被定义为对 mysql_real_connect()使用 CLIENT_INTERACTIVE 选项的客户。
    默认数值是28800，可以把它改为3600。 

skip-name-resolve            
    禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。
    但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！

log-slow-queries = slow.log                      记录慢查询
          
# > SHOW VARIABLES LIKE '%query_cache%';
# > SHOW STATUS LIKE 'Qcache%';
如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况；
如果Qcache_hits的值非常大，则表明查询缓冲使用非常频繁，如果该值较小反而会影响效率，那么可以考虑不用查询缓冲；
如果Qcache_free_blocks的值非常大，则表明缓冲区中碎片很多。
 
 
##########################################
######      max_allowed_packet      ######
##########################################

通信信息包是发送至MySQL服务器的单个SQL语句，或发送至客户端的单一行。 
在MySQL 5.1服务器和客户端之间最大能发送的可能信息包为1GB。 
当MySQL客户端或mysqld服务器收到大于max_allowed_packet字节的信息包时，将发出“信息包过大”错误，并关闭连接。对于某些客户端，如果通信信息包过大，在执行查询期间，了能回遇到“丢失与MySQL服务器的连接”（lose connection during query...）错误。 

客户端和服务器均有自己的max_allowed_packet变量，因此，如你打算处理大的信息包，必须增加客户端和服务器上的该变量。 
如果你正在使用mysql客户端程序，其max_allowed_packet变量的默认值为16MB。要想设置较大的值，可用下述方式启动mysql： 
mysql> mysql --max_allowed_packet=32M
它将信息包的大小设置为32MB。 
服务器的默认max_allowed_packet值为1MB。如果服务器需要处理大的查询，可增加该值（例如，如果准备处理大的BLOB列）。
 
也能使用选项文件来设置max_allowed_packet。要想将服务器的该变量设置为16MB，可在选项文件中增加下行内容： 
[mysqld]
max_allowed_packet=16M 

增加该变量的值十分安全，这是因为仅当需要时才会分配额外内存。例如，仅当你发出长查询或mysqld必须返回大的结果行时mysqld才会分配更多内存。该变量之所以取较小默认值是一种预防措施，以捕获客户端和服务器之间的错误信息包，并确保不会因偶然使用大的信息包而导致内存溢出。

 
如果你正是用大的BLOB值，而且未为mysqld授予为处理查询而访问足够内存的权限，也会遇到与大信息包有关的奇怪问题。如果怀疑出现了该情况，请尝试在mysqld_safe脚本开始增加ulimit -d 256000，并重启mysqld。

 

 

 

##########################################
#####   MySQL怎样打开和关闭数据库表  #####
##########################################

table_cache, max_connections和max_tmp_tables影响服务器保持打开的文件的最大数量。如果你增加这些值的一个或两个，你可以遇到你的操作系统每个进程打开文件描述符的数量上强加的限制。然而，你可以能在许多系统上增加该限制。请教你的OS文档找出如何做这些，因为改变限制的方法各系统有很大的不同。 

table_cache与max_connections有关。例如，对于200个打开的连接，你应该让一张表的缓冲至少有200 * n，这里n是一个联结(join)中表的最大数量。

 

show variables like '%slow%'

 

Max_used_connections/max_connections =0.85 ---Max_connections

Key_cache_miss_rate=key_reads/key_read_requests 0.1%--Key_buffer_size 对myisam表有效

key_blocks_used/(key_blocks_used+key_blocks_used)--Key_buffer_size 对myisam表有效

Created_tmp_disk_tables / Created_tmp_tables <=0.25 --对应的变量：tmp_table_size,max_heap_table_size

Open_tables 量比较大，可以调整参数table_cache

thread_created 过大，请配置 thread_cache_size

查询缓存(query cache)

　　Qcache_free_blocks：缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。

　　Qcache_free_memory：缓存中的空闲内存。

　　Qcache_hits：每次查询在缓存中命中时就增大

　　Qcache_inserts：每次插入一个查询时就增大。命中次数除以插入次数就是不中比率。

　　Qcache_lowmem_prunes：缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看;如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。(上面的 free_blocks和free_memory可以告诉您属于哪种情况)

　　Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。

　　Qcache_queries_in_cache：当前缓存的查询(和响应)的数量。

　　Qcache_total_blocks：缓存中块的数量。

查询缓存参数配置：

query_cache_limit | 2097152 | 超过此大小的查询将不缓存

　　| query_cache_min_res_unit | 4096 | 缓存块的最小大小 4K，过大，容易造成碎片和浪费

　　| query_cache_size | 203423744 | 查询缓存大小

　　| query_cache_type | ON | 缓存类型，决定缓存什么样的查询，示例中表示不缓存 select sql_no_cache 查询

       | query_cache_wlock_invalidate | OFF |当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成再读表获取结果。

 

查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100% 
----如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。

 

查询缓存利用率 = (query_cache_size - Qcache_free_memory) / query_cache_size * 100% 

---查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小;查询缓存利用率在80%以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小，要不就是碎片太多。

 

查询缓存命中率 = (Qcache_hits - Qcache_inserts) / Qcache_hits * 100%

 

排序使用情况：

Sort_merge_passes | 29 |

　　| Sort_range | 37432840 |

　　| Sort_rows | 9178691532 |

　　| Sort_scan | 1860569 |

调整的参数：Sort_buffer_size

文件打开数（Open_files）：对应参数open_files_limit
比较合适的设置：Open_files / open_files_limit * 100% <= 75%

表锁情况：
Table_locks_immediate表示立即释放表锁数
Table_locks_waited表示需要等待的表锁数
Table_locks_immediate / Table_locks_waited > 5000，最好采用InnoDB引擎

thread_cache_sized :
1G  —> 8
2G  —> 16
3G  —> 32
>3G  —> 64