MYSQL数据库优化设定.docx

1、MYSQL数据库优化设定mysql优化实践总结key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 458624 KMySQL如何计算打开文件数open_files_limit加入 fopen_files_limit = nopen_files_limit 比内核最大限制数ulimit -n还大，因此以 open_files_limit 为准设置 mysql 打开文件限制方法：把 set-variable=open_files_limit=10240 加到配置文件中。clientport = 3306

2、socket = /tmp/mysql.sockmysqldport = 3306socket = /tmp/mysql.sock#关闭不需要的表类型skip-bdbskip-innodbskip-lockingback_log=500local-infile = 0interactive_timeout=10#open_files_limit = 3000skip-locking# 避免MySQL的外部锁定，减少出错几率增强稳定性max_tmp_tables客户端可以同时打开的临时表的最大数。max_connect_errors = 30000如果中断的与主机的连接超过该数目，该主机则阻塞后

3、面的连接。你可以用 FLUSH HOSTS语句解锁锁定的主机。max_connections = 1000允许的并行客户端连接数目。#禁止MySQL中用”LOAD DATA LOCAL INFILE”命令。这个命令会利用MySQL把本地文件读到数据库中，然后用户就可以非法获取敏感信息了。网络上流传的一些攻击方法中就有用它的，它也是很多新发现的SQL Injection攻击利用的手段！local-infile = 0wait_timeout= 10#设置超时时间,能避免长连接有时候，如果负载过重，连接会挂起，并且会占用连接表空间。如果有多个交互用户或使用了到数据库的持久连接，那么将这个值设低一点

4、并不可取！指定一个请求的最大连接时间，对于4GB左右内存的服务器可以设置为5-10。服务器在关闭它之前在一个连接上等待行动的秒数。也可见interactive_timeout。connect_timeout=3#connect_timeout 3key_bufferkey_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好show status like Qcache%;query_cache_sizeQcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况,同时Qcache_hits的值非常大，则表明查询缓冲使用非常频

5、繁，此时需要增加缓冲大小Qcache_hits的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多query_cache_type指定是否使用查询缓冲得到如下状态值:Qcache che指定表高速缓存的大小。得到如下状态值:Qcache queries in cache 12737 表明目前缓存的条数Qcache inserts 20649006Qcache hits 79060095 看来重复查询率还挺高的Qcache lowmem prunes 617913有这么多次

6、出现缓存过低的情况Qcache not cached 189896Qcache free memory 18573912目前剩余缓存空间Qcache free blocks 5328 这个数字似乎有点大碎片不少Qcache total blocks 30953SHOW STATUS LIKE Open%tables;table_cache。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加table_cache的值。如果你发现open_table

7、s等于table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。对于有1G内存的机器，推荐值是128256。笔者设置table_cache = 256得到以下状态:Open tables 256Opened tables 9046虽然open_tables已经等于table_cache，但是相对于服务器运行时间来说,已经运行了20天，opened_tables的值也非常低。因此，增加table_cache的值应该用处不大。如果运行了6个小时就出现上述值 那就要考虑增大t

8、able_cacheSHOW STATUS LIKE “com_select”;SHOW STATUS LIKE “handler_read_rnd_next”;Handler_read_rnd_next / Com_select 得出了表扫描比率在本例中是 521:1。如果该值超过 4000，就应该查看 read_buffer_size，例如 read_buffer_size = 4M。如果这个数字超过了 8M，就应该与开发人员讨论一下对这些查询进行调优了！SHOW STATUS LIKE created_tmp%;备注：定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查

9、状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用show status like key_reads获得）。key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。#要求MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接

10、请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。试图设定back_log高于你的操作系统的限制将是无效的。默认数值是50#一个包的最大尺寸。消息缓冲区被初始化为net_buffer_length字节，但是可在需要时增加到max_allowed_packet个字节。缺省地，该值太小必能捕捉大的(可能错误)包。如果你正在使用大的

11、BLOB列，你必须增加该值。它应该象你想要使用的最大BLOB的那么大max_allowed_packet = 4Mmax_allowed_packet 客户机通信所使用的缓冲区大小的最大值。如果有客户机要发送大量的BLOB 或TEXT 的值，该服务器变量值可能需要增大。#允许的同时客户的数量。增加该值增加 mysqld要求的文件描述符的数量。这个数字应该增加，否则，你将经常看到链接过多，请联系空间商错误。 默认数值是100max_connections=1024#指定表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检

12、查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加的值。如果你发现open_tables等于table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了（上述状态值可以使用show status like Open_tables获得）。注意，不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败.table_cache=512#每个线程排序所需的缓冲sort_buffer_size = 4M#当一个查询不断地扫描某一个表，MySQL会为它

13、分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果你认为连续扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。read_buffer_size = 4M#加速排序操作后的读数据，提高读分类行的速度。如果正对远远大于可用内存的表执行GROUP BY或ORDER BY操作，应增加read_rnd_buffer_size的值以加速排序操作后面的行读取。仍然不明白这个选项的用处read_rnd_buffer_size = 8M#用于REPAIR TABLE。不明白这个选项的用处，XX上找到的设置方向也是五花八门，有128M、64M、32M等，折中选一个m

14、yisam_sort_buffer_size = 64M#可以复用的保存在中的线程的数量。如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。如果有很多新的线程，为了提高性能可以这个变量值。通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用thread_cache_size = 128#查询结果缓存。第一次执行某条SELECT语句的时候，服务器记住该查询的文本内容和它返回的结果。服务器下一次碰到这个语句的时候，它不会再次执行该语句。作为代替，它直接从查询缓存中的得到结果并把结果返回给客户端。query_cache_

15、size = 32M#最大并发线程数,cpu数量*2 thread_concurrency = 2#设置超时时间,能避免长连接wait_timeout = 120#关闭不需要的表类型,如果你需要,就不要加上这个skip-innodbskip-bdb#设定缓存的连接数,节省连接时的开销back_log = 64#禁用文件系统外部锁external-locking = 0#禁用BDB,如果你确实不需要的话,innodb也是如此skip-bdb#索引缓冲,如果是专用的数据库服务器,可以设置高达服务器内存的一半,如果不是专用的,还是设置得低一点key_buffer = 512M#缓存数据表数量,如果内

16、存较大,可以设置稍微高一点,否则还是设置低一点#设置这个参数可以参见系统状态中的 open_tables(表示当前打开的数据表总数) 和 opened_tables(表示所有打开的数据表总数)table_cache = 128 对于有1G内存的机器，推荐值是128256。#禁用dns解析,如果你的授权信息中采用dns授权方式了,就不能启用该选项skip-name-resolveskip-name-resolve主机名不被解析。所有在授权表的Host的列值必须是IP数字或localhost。#记录慢查询和没有使用索引的查询,便于帮助分析问题所在long_query_times指定慢查询的阈值，缺

17、省是10秒long_query_time = 1log-slow-queries = /usr/local/mysql/data/slow.loglog-queries-not-using-indexes(1)、back_log：要求 MySQL 能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。你的操

18、作系统在这个队列大小上有它自己的限制。 试图设定back_log高于你的操作系统的限制将是无效的。当你观察你的主机进程列表，发现大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时，就要加大 back_log 的值了。默认数值是50，我把它改为500。(2)、interactive_timeout：服务器在关闭它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对 mysql_real_connect()使用 CLIENT_INTERACTIVE

19、选项的客户。 默认数值是28800，我把它改为7200。(3)、key_buffer_size：索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，系统将开始换页并且真的变慢了。默认数值是8388600(8M)，我的MySQL主机有2GB内存，所以我把它改为 402649088(400MB)。(4)、max_connections：允许的同时客户的数量。增加该值增加 mysqld 要求的文件描述符的数量。这个数字应该增加，否则，你将经常看到 Too many conne

20、ctions 错误。 默认数值是100，我把它改为1024 。(5)、record_buffer：每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。默认数值是131072(128K)，我把它改为16773120 (16M)(6)、sort_buffer：每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。默认数值是2097144(2M)，我把它改为 16777208 (16M)。(7)、table_cache：为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。MyS

21、QL对每个唯一打开的表需要2个文件描述符。默认数值是64，我把它改为512。(8)、thread_cache_size：可以复用的保存在中的线程的数量。如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。如果有很多新的线程，为了提高性能可以这个变量值。通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。我把它设置为 80。(10)、wait_timeout：服务器在关闭它之前在一个连接上等待行动的秒数。 默认数值是28800，我把它改为7200。#my-f这个配制文件就能满足我们的大多需要du -hmax-dept

22、h=11. 修复并优化所有数据库:本地:# mysqlcheck -A -r -o -p远程:# mysqlcheck -A -r -o -p -h服务器地址2. 修复并优化指定的数据库:本地:# mysqlcheck -u用户名 -p密码 -r -o 库名远程:# mysqlcheck u用户名 -p密码 -h服务器地址 -r -o 库名有的时候因为掉电或者其他原因导致数据库损坏,我们可以使用mysql自带的mysqlcheck命令来快速修复所有的数据库或者特定的数据库;例如检查优化并修复所有的数据库用:# mysqlcheck -A -o -r -pEnter password:database1 OKdatabase2 OK-修复指定的数据库用# mysqlcheck -A -o -r Database_NAME -p即可另外如果只是对某个表进行修复可以用：myisamchk或isamchk其中myisamchk适用于MYISAM类型的数据表，而isamchk适用于ISAM类型的数据表。这两条命令的主要参数相同，一般新的系统都使用MYISAM作为缺省的数据表类型，这里以myisamchk为例子进行说明。当发现某个数据表出现问题时可以使用：myisamchk tablename.MYI进行检测，如果需要修复的