MYSQL数据库优化设定.docx
《MYSQL数据库优化设定.docx》由会员分享，可在线阅读，更多相关《MYSQL数据库优化设定.docx（39页珍藏版）》请在冰豆网上搜索。
MYSQL数据库优化设定
mysql优化实践总结
key_buffer_size+（read_buffer_size+sort_buffer_size）*max_connections=458624K
MySQL如何计算打开文件数
open_files_limit加入f
open_files_limit=n
open_files_limit比内核最大限制数ulimit-n还大，因此以open_files_limit为准
设置mysql打开文件限制方法：
把set-variable=open_files_limit=10240加到配置文件中。
[client]
port =3306
socket =/tmp/mysql.sock
[mysqld]
port =3306
socket =/tmp/mysql.sock
#关闭不需要的表类型
skip-bdb
skip-innodb
skip-locking
back_log=500
local-infile=0
interactive_timeout=10
#open_files_limit=3000
skip-locking
#避免MySQL的外部锁定，减少出错几率增强稳定性
max_tmp_tables
客户端可以同时打开的临时表的最大数。
max_connect_errors=30000
如果中断的与主机的连接超过该数目，该主机则阻塞后面的连接。
你可以用FLUSHHOSTS语句解锁锁定的主机。
max_connections=1000
允许的并行客户端连接数目。
#禁止MySQL中用”LOADDATALOCALINFILE”命令。
这个命令会利用MySQL把本地文件读到数据库中，然后用户就可以非法获取敏感信息了。
网络上流传的一些攻击方法中就有用它的，它也是很多新发现的SQLInjection攻击利用的手段！
local-infile=0
————————————————————————–
wait_timeout=10
#设置超时时间,能避免长连接
有时候，如果负载过重，连接会挂起，并且会占用连接表空间。
如果有多个交互用户或使用了到数据库的持久连接，那么将这个值设低一点并不可取！
指定一个请求的最大连接时间，对于4GB左右内存的服务器可以设置为5-10。
服务器在关闭它之前在一个连接上等待行动的秒数。
也可见interactive_timeout。
————————————————————————–
connect_timeout=3
####################################################################
connect_timeout3
<=MYSQLD用來判斷此連線是否有效的時間，建議預設如果網路緩慢可以調高
###################################################################
max_connections=3000（内存1G） 10000（内存2G）
long_query_time=1
key_buffer=256M（内存1G） 512M（内存2G）
max_allowed_packet=2M
table_cache=512（内存1G） 1024内存2G）
sort_buffer_size=2M（内存1G） 32（内存2G）
read_buffer_size=2M（内存1G）32（内存2G）
join_buffer_size=2M（内存1G） 32（内存2G）
myisam_sort_buffer_size=64M（内存1G） 128（内存2G）
thread_cache=8（内存1G） 64（内存2G）
query_cache_limit=1M（内存1G） 4（内存2G）
query_cache_size=32M（内存1G） 64（内存2G）
query_cache_type=1
#TrynumberofCPU’s*2forthread_concurrency
thread_concurrency=4
#skip-networking
#ReplicationMasterServer（default）
#binaryloggingisrequiredforreplication
#log-bin（关闭二进制）
#requireduniqueidbetween1and2^32-1
#defaultsto1ifmaster-hostisnotset
#butwillnotfunctionasamasterifomitted
server-id =1
[mysqldump]
quick
max_allowed_packet=16M
[mysql]
no-auto-rehash
#RemovethenextcommentcharacterifyouarenotfamiliarwithSQL
#safe-updates
[isamchk]
key_buffer=256M
sort_buffer_size=256M
read_buffer=2M
write_buffer=2M
[myisamchk]
key_buffer=256M
sort_buffer_size=256M
read_buffer=2M
write_buffer=2M
[mysqlhotcopy]
interactive-timeout
query_cache table_cache key_buffer_size
########################################################
进入MySQL，用命令：
showvariables;
showvariableslike“max%”;
查看数据库最大可连接数的变量值：
max_connections
但实际MySQL服务器允许的最大连接数16384;
添加了最大允许连接数，对系统消耗增加不大。
查看threads_connected
进入MySQL，用命令：
showstatus;
showstatuslike“thread%”;
查看当前活动的连接线程变量值：
threads_connected
如果threads_connected==max_connections时，
数据库系统就不能提供更多的连接数了，这时，如果程序还想新建连接线程，数据库系统就会拒绝，如果程序没做太多的错误处理，就会出现类似强坛的报错信息。
该错误的简便的检查办法是，在刷新强坛页面时，不断监视threads_connected的变化。
如果max_connections足够大，而threads_connected值不断增加以至达到max_connections，那么，就应该检查程序了
此处重要的值是Threads_created，每次mysqld需要创建一个新线程时，这个值都会增加。
如果这个数字在连续执行SHOWSTATUS命令时快速增加，就应该尝试增大线程缓存。
例如，可以在f中使用thread_cache_size =512来实现此目的
thread_cache_size线程缓存大小
Threads_created太大,就要增加f中thread_cache_size的值.可以用Threads_created/Connections计算cache命中率
Threads_created 942
Connections 10841380
从上面的对比中.如何多的connections中才942个Threads_created.建议减小thread_cache_size值
########################################################
1.key_buffer_size
key_buffer_size只对MyISAM表起作用
key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。
一般我们设为16M
showstatuslike‘key_read%’; ———>key_buffer
key_reads/key_read_requests应该尽可能的低，至少是1:
100，1:
1000更好
showstatuslike‘Qcache%’;————>query_cache_size
Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况,同时Qcache_hits的值非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小
Qcache_hits的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。
Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多query_cache_type指定是否使用查询缓冲
得到如下状态值:
Qcacheche指定表高速缓存的大小。
得到如下状态值:
Qcachequeriesincache12737表明目前缓存的条数
Qcacheinserts20649006
Qcachehits79060095 看来重复查询率还挺高的
Qcachelowmemprunes617913 有这么多次出现缓存过低的情况
Qcachenotcached189896
Qcachefreememory18573912 目前剩余缓存空间
Qcachefreeblocks5328这个数字似乎有点大 碎片不少
Qcachetotalblocks30953
SHOWSTATUSLIKE‘Open%tables’;———–>table_cache
。
每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。
通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加table_cache的值。
如果你发现open_tables等于table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了
如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。
对于有1G内存的机器，推荐值是128－256。
笔者设置table_cache=256
得到以下状态:
Opentables256
Openedtables9046
虽然open_tables已经等于table_cache，但是相对于服务器运行时间来说,已经运行了20天，opened_tables的值也非常低。
因此，增加table_cache的值应该用处不大。
如果运行了6个小时就出现上述值那就要考虑增大table_cache
SHOWSTATUSLIKE“com_select”;
SHOWSTATUSLIKE“handler_read_rnd_next”;
Handler_read_rnd_next/Com_select得出了表扫描比率
在本例中是521:
1。
如果该值超过4000，就应该查看read_buffer_size，例如read_buffer_size=4M。
如果这个数字超过了8M，就应该与开发人员讨论一下对这些查询进行调优了！
SHOWSTATUSLIKE‘created_tmp%’;
备注：
定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。
通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。
比例key_reads/key_read_requests应该尽可能的低，至少是1:
100，1:
1000更好（上述状态值可以使用showstatuslike‘key_reads’获得）。
key_buffer_size只对MyISAM表起作用。
即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。
可以使用检查状态值created_tmp_disk_tables得知详情。
#要求MySQL能有的连接数量。
当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间（尽管很短）检查连接并且启动一个新线程。
back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。
只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。
你的操作系统在这个队列大小上有它自己的限制。
试图设定back_log高于你的操作系统的限制将是无效的。
默认数值是50
#一个包的最大尺寸。
消息缓冲区被初始化为net_buffer_length字节，但是可在需要时增加到max_allowed_packet个字节。
缺省地，该值太小必能捕捉大的（可能错误）包。
如果你正在使用大的BLOB列，你必须增加该值。
它应该象你想要使用的最大BLOB的那么大max_allowed_packet=4M
max_allowed_packet客户机通信所使用的缓冲区大小的最大值。
如果有客户机要发送大量的BLOB或TEXT的值，该服务器变量值可能需要增大。
#允许的同时客户的数量。
增加该值增加mysqld要求的文件描述符的数量。
这个数字应该增加，否则，你将经常看到链接过多，请联系空间商错误。
默认数值是100
max_connections=1024
#指定表高速缓存的大小。
每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。
通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加的值。
如果你发现open_tables等于table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了（上述状态值可以使用showstatuslike‘Open_tables’获得）。
注意，不能盲目地把table_cache设置成很大的值。
如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败.table_cache=512
#每个线程排序所需的缓冲sort_buffer_size=4M
#当一个查询不断地扫描某一个表，MySQL会为它分配一段内存缓冲区。
read_buffer_size变量控制这一缓冲区的大小。
如果你认为连续扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。
read_buffer_size=4M
#加速排序操作后的读数据，提高读分类行的速度。
如果正对远远大于可用内存的表执行GROUPBY或ORDERBY操作，应增加read_rnd_buffer_size的值以加速排序操作后面的行读取。
仍然不明白这个选项的用处……read_rnd_buffer_size=8M
#用于REPAIRTABLE。
不明白这个选项的用处，XX上找到的设置方向也是五花八门，有128M、64M、32M等，折中选一个myisam_sort_buffer_size=64M
#可以复用的保存在中的线程的数量。
如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。
如果有很多新的线程，为了提高性能可以这个变量值。
通过比较Connections和Threads_created状态的变量，可以看到这个变量的作用thread_cache_size=128
#查询结果缓存。
第一次执行某条SELECT语句的时候，服务器记住该查询的文本内容和它返回的结果。
服务器下一次碰到这个语句的时候，它不会再次执行该语句。
作为代替，它直接从查询缓存中的得到结果并把结果返回给客户端。
query_cache_size=32M
#最大并发线程数,cpu数量*2 thread_concurrency=2
#设置超时时间,能避免长连接wait_timeout=120
#关闭不需要的表类型,如果你需要,就不要加上这个
skip-innodb
skip-bdb
#设定缓存的连接数,节省连接时的开销
back_log =64
#禁用文件系统外部锁
external-locking =0
#禁用BDB,如果你确实不需要的话,innodb也是如此
skip-bdb
#索引缓冲,如果是专用的数据库服务器,可以设置高达服务器内存的一半,如果不是专用的,还是设置得低一点
key_buffer =512M
#缓存数据表数量,如果内存较大,可以设置稍微高一点,否则还是设置低一点
#设置这个参数可以参见系统状态中的open_tables（表示当前打开的数据表总数）和opened_tables（表示所有打开的数据表总数）
table_cache =128对于有1G内存的机器，推荐值是128－256。
#禁用dns解析,如果你的授权信息中采用dns授权方式了,就不能启用该选项
skip-name-resolve
skip-name-resolve
主机名不被解析。
所有在授权表的Host的列值必须是IP数字或localhost。
#记录慢查询和没有使用索引的查询,便于帮助分析问题所在
long_query_times指定慢查询的阈值，缺省是10秒
long_query_time =1
log-slow-queries =/usr/local/mysql/data/slow.log
log-queries-not-using-indexes
（1）、back_log：
要求MySQL能有的连接数量。
当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间（尽管很短）检查连接并且启动一个新线程。
back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。
只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。
你的操作系统在这个队列大小上有它自己的限制。
试图设定back_log高于你的操作系统的限制将是无效的。
当你观察你的主机进程列表，发现大量264084|unauthenticateduser|xxx.xxx.xxx.xxx|NULL|Connect|NULL|login|NULL的待连接进程时，就要加大back_log的值了。
默认数值是50，我把它改为500。
（2）、interactive_timeout：
服务器在关闭它前在一个交互连接上等待行动的秒数。
一个交互的客户被定义为对mysql_real_connect（）使用CLIENT_INTERACTIVE选项的客户。
默认数值是28800，我把它改为7200。
（3）、key_buffer_size：
索引块是缓冲的并且被所有的线程共享。
key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引（对所有读和多重写），到你能负担得起那样多。
如果你使它太大，系统将开始换页并且真的变慢了。
默认数值是8388600（8M），我的MySQL主机有2GB内存，所以我把它改为402649088（400MB）。
（4）、max_connections：
允许的同时客户的数量。
增加该值增加mysqld要求的文件描述符的数量。
这个数字应该增加，否则，你将经常看到Toomanyconnections错误。
默认数值是100，我把它改为1024。
（5）、record_buffer：
每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。
如果你做很多顺序扫描，你可能想要增加该值。
默认数值是131072（128K），我把它改为16773120（16M）
（6）、sort_buffer：
每个需要进行排序的线程分配该大小的一个缓冲区。
增加这值加速ORDERBY或GROUPBY操作。
默认数值是2097144（2M），我把它改为16777208（16M）。
（7）、table_cache：
为所有线程打开表的数量。
增加该值能增加mysqld要求的文件描述符的数量。
MySQL对每个唯一打开的表需要2个文件描述符。
默认数值是64，我把它改为512。
（8）、thread_cache_size：
可以复用的保存在中的线程的数量。
如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。
如果有很多新的线程，为了提高性能可以这个变量值。
通过比较Connections和Threads_created状态的变量，可以看到这个变量的作用。
我把它设置为80。
（10）、wait_timeout：
服务器在关闭它之前在一个连接上等待行动的秒数。
默认数值是28800，我把它改为7200。
################################################################################################
my-f这个配制文件就能满足我们的大多需要
du-h–max-depth=1
1.修复并优化所有数据库:
本地:
#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-p
Enterpassword:
database1OK
database2OK
———-
修复指定的数据库用
#mysqlcheck-A-o-rDatabase_NAME-p
即可
另外如果只是对某个表进行修复可以用：
myisamchk或isamchk
其中myisamchk适用于MYISAM类型的数据表，而isamchk适用于ISAM类型的数据表。
这两条命令的主要参数相同，一般新的系统都使用MYISAM作为缺省的数据表类型，这里以myisamchk为例子进行说明。
当发现某个数据表出现问题时可以使用：
myisamchktablename.MYI
进行检测，如果需要修复的
升级会员 