MySQL 5.5.49 大内存优化配置文件优化详解

2024-03-18 14:28:03 36

一、配置文件说明

my-small.cnf
my-medium.cnf
my-large.cnf
my-huge.cnf
my-innodb-heavy-4G.cnf

二、详解my-innodb-heavy-4G.cnf
三、配置文件优化
注：环境说明，CentO5.5x86_64+MySQL-5.5.32相关软件下载：http://yunpan.cn/QtaCuLHLRKzRq

一、配置文件说明

Mysql-5.5.49是Mysql5.5系列中最后一个版本，也是最后一个有配置文件的版本，为什么这么说呢，用过5.6的博友都知道，在mysql5.6中已经不提供配置文件选择，只有一个默认的配置文件，好了，我们今天说的是5.5.49这个版，就不和大家说5.6了，下面我们来具体说一下，mysql5.5.49中，提供可选的几个配置文件，
my-small.cnf
my-medium.cnf
my-large.cnf
my-huge.cnf
my-innodb-heavy-4G.cnf

下面我们就来分别的看一下^_^……
1.my-small.cnf

[root@mysqlsupport-files]#vimmy-small.cnf
#ExampleMySQLconfigfileforsmallsystems.
#Thisisforasystemwithlittlememory(<=64M)whereMySQLisonlyused
#fromtimetotimeandit'simportantthatthemysqlddaemon
#doesn'tusemuchresources.

这是my-small.cnf配置文件中开头的简介，它说明了，这个配置文件是在内存小于等于64M时使用的，小型数据库系统，目的是不占更多的系统资源！
2.my-medium.cnf

[root@mysqlsupport-files]#vimmy-medium.cnf
#ExampleMySQLconfigfileformediumsystems.
#Thisisforasystemwithlittlememory(32M-64M)whereMySQLplays
#animportantpart,orsystemsupto128MwhereMySQLisusedtogetherwith
#otherprograms(suchasawebserver)

这个配置文件是中型数据系统使用，内存在128M左右！
3.my-large.cnf

[root@mysqlsupport-files]#vimmy-large.cnf
#ExampleMySQLconfigfileforlargesystems.
#Thisisforalargesystemwithmemory=512Mwherethesystemrunsmainly
#MySQL.

这个配置文件是大型数据库系统使用，内存在512M左右！
4.my-huge.cnf

[root@mysqlsupport-files]#vimmy-huge.cnf
#ExampleMySQLconfigfileforverylargesystems.
#Thisisforalargesystemwithmemoryof1G-2Gwherethesystemrunsmainly
#MySQL.

这个配置文件是巨型数据库系统使用，内存在1G-2G左右！
5.my-innodb-heavy-4G.cnf

[root@mysqlsupport-files]#vimmy-innodb-heavy-4G.cnf
#BEGINCONFIGINFO
#DESCR:4GBRAM,InnoDBonly,ACID,fewconnections,heavyqueries
#TYPE:SYSTEM
#ENDCONFIGINFO
#ThisisaMySQLexampleconfigfileforsystemswith4GBofmemory
#runningmostlyMySQLusingInnoDBonlytablesandperformingcomplex
#querieswithfewconnections.

这个配置文件主要作用是,支持4G内存，支持InnoDB引擎，支持事务（ACID）等特性所使用！
说明：ACID，指数据库事务正确执行的四个基本要素的缩写。包含：原子性(Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）！
6.总结
从上面的说明我们可以出，基本是通过内存大小来选择mysql的配置文件的，那有博友会说了，现在的服务器动不动就是32G内存或者64G内存，甚至更大的内存，你那个配置文件最大只支持4G内存是不是有点小了，确认会有这样的问题，从mysql5.6以后，为了更大的发挥mysql的性能，已经去除了配置文件选择，只有一个默认的配置文件，里面只有一些基本配置，所有设置管理员都可以根据自己实际的需求进行自行设置，好了说了这么多，我们就来说一说，在企业的用的最多的my-innodb-heavy-4G.cnf配置文件！
二、详解my-innodb-heavy-4G.cnf
1.详细说明
注：下面是my-innodb-heavy-4G.cnf默认配置我没有做任何修改，下面我们就来详细的说一说！

[root@mysqlsupport-files]#vimmy-innodb-heavy-4G.cnf
#BEGINCONFIGINFO
#DESCR:4GBRAM,InnoDBonly,ACID,fewconnections,heavyqueries
#TYPE:SYSTEM
#ENDCONFIGINFO
#
#ThisisaMySQLexampleconfigfileforsystemswith4GBofmemory
#runningmostlyMySQLusingInnoDBonlytablesandperformingcomplex
#querieswithfewconnections.
#
#MySQLprogramslookforoptionfilesinasetof
#locationswhichdependonthedeploymentplatform.
#Youcancopythisoptionfiletooneofthose
#locations.Forinformationabouttheselocations,see:
#http://dev.mysql.com/doc/mysql/en/option-files.html
#
#Inthisfile,youcanusealllongoptionsthataprogramsupports.
#Ifyouwanttoknowwhichoptionsaprogramsupports,runtheprogram
#withthe"--help"option.
#
#Moredetailedinformationabouttheindividualoptionscanalsobe
#foundinthemanual.
#
#
#ThefollowingoptionswillbereadbyMySQLclientapplications.
#NotethatonlyclientapplicationsshippedbyMySQLareguaranteed
#toreadthissection.IfyouwantyourownMySQLclientprogramto
#honorthesevalues,youneedtospecifyitasanoptionduringthe
#MySQLclientlibraryinitialization.
#
#以下选项会被MySQL客户端应用读取,注意只有MySQL附带的客户端应用程序保证可以读取这段内容,如果你想你自己的MySQL应用程序获取这些值,需要在MySQL客户端库初始化的时候指定这些选项
[client]
#password=[your_password]#mysql客户端连接mysql时的密码
port=3306#mysql客户端连接时的默认端口
socket=/tmp/mysql.sock#与mysql服务器本地通信所使用的socket文件路径
#***Application-specificoptionsfollowhere***
#
#TheMySQLserver
#
[mysqld]
#genericconfigurationoptions#一般配置选项
port=3306#mysql服务器监听的默认端口
socket=/tmp/mysql.sock#socket本地通信文件路径
#back_logisthenumberofconnectionstheoperatingsystemcankeepin
#thelistenqueue,beforetheMySQLconnectionmanagerthreadhas
#processedthem.Ifyouhaveaveryhighconnectionrateandexperience
#"connectionrefused"errors,youmightneedtoincreasethisvalue.
#CheckyourOSdocumentationforthemaximumvalueofthisparameter.
#Attemptingtosetback_loghigherthanyouroperatingsystemlimit
#willhavenoeffect.
#back_log是操作系统在监听队列中所能保持的连接数,
#队列保存了在MySQL连接管理器线程处理之前的连接.
#如果你有非常高的连接率并且出现“connectionrefused”报错,
#你就应该增加此处的值.
#检查你的操作系统能打开文件数来获取这个变量的最大值.
#如果将back_log设定到比你操作系统限制更高的值,将会没有效果
back_log=50
#Don'tlistenonaTCP/IPportatall.Thiscanbeasecurity
#enhancement,ifallprocessesthatneedtoconnecttomysqldrun
#onthesamehost.AllinteractionwithmysqldmustbemadeviaUnix
#socketsornamedpipes.
#NotethatusingthisoptionwithoutenablingnamedpipesonWindows
#(viathe"enable-named-pipe"option)willrendermysqlduseless!
#不在TCP/IP端口上进行监听.
#如果所有的进程都是在同一台服务器连接到本地的mysqld,
#这样设置将是增强安全的方法
#所有mysqld的连接都是通过Unixsockets或者命名管道进行的.
#注意在windows下如果没有打开命名管道选项而只是用此项
#(通过“enable-named-pipe”选项)将会导致mysql服务没有任何作用!
#skip-networking#默认是没有开启的
#ThemaximumamountofconcurrentsessionstheMySQLserverwill
#allow.Oneoftheseconnectionswillbereservedforauserwith
#SUPERprivilegestoallowtheadministratortologinevenifthe
#connectionlimithasbeenreached.
#MySQL服务器所允许的同时会话数的上限
#其中一个连接将被SUPER权限保留作为管理员登录.
#即便已经达到了连接数的上限.
max_connections=100
#Maximumamountoferrorsallowedperhost.Ifthislimitisreached,
#thehostwillbeblockedfromconnectingtotheMySQLserveruntil
#"FLUSHHOSTS"hasbeenrunortheserverwasrestarted.Invalid
#passwordsandothererrorsduringtheconnectphaseresultin
#increasingthisvalue.Seethe"Aborted_connects"statusvariablefor
#globalcounter.
#每个客户端连接最大的错误允许数量,如果达到了此限制.
#这个客户端将会被MySQL服务阻止直到执行了”FLUSHHOSTS”或者服务重启
#非法的密码以及其他在链接时的错误会增加此值.
#查看“Aborted_connects”状态来获取全局计数器.
max_connect_errors=10
#Thenumberofopentablesforallthreads.Increasingthisvalue
#increasesthenumberoffiledescriptorsthatmysqldrequires.
#Thereforeyouhavetomakesuretosettheamountofopenfiles
#allowedtoatleast4096inthevariable"open-files-limit"in
#section[mysqld_safe]
#所有线程所打开表的数量.
#增加此值就增加了mysqld所需要的文件描述符的数量
#这样你需要确认在[mysqld_safe]中“open-files-limit”变量设置打开文件数量允许至少2048
table_open_cache=2048
#Enableexternalfilelevellocking.Enabledfilelockingwillhavea
#negativeimpactonperformance,soonlyuseitincaseyouhave
#multipledatabaseinstancesrunningonthesamefiles(notesome
#restrictionsstillapply!)orifyouuseothersoftwarerelyingon
#lockingMyISAMtablesonfilelevel.
#允许外部文件级别的锁.打开文件锁会对性能造成负面影响
#所以只有在你在同样的文件上运行多个数据库实例时才使用此选项(注意仍会有其他约束!)
#或者你在文件层面上使用了其他一些软件依赖来锁定MyISAM表
#external-locking#默认是没有开启的
#Themaximumsizeofaquerypackettheservercanhandleaswellas
#maximumquerysizeservercanprocess(Importantwhenworkingwith
#largeBLOBs).enlargeddynamically,foreachconnection.
#服务所能处理的请求包的最大大小以及服务所能处理的最大的请求大小(当与大的BLOB字段一起工作时相当必要)
#每个连接独立的大小.大小动态增加
max_allowed_packet=16M
#ThesizeofthecachetoholdtheSQLstatementsforthebinarylog
#duringatransaction.Ifyouoftenusebig,multi-statement
#transactionsyoucanincreasethisvaluetogetmoreperformance.All
#statementsfromtransactionsarebufferedinthebinarylogcacheand
#arebeingwrittentothebinarylogatonceaftertheCOMMIT.Ifthe
#transactionislargerthanthisvalue,temporaryfileondiskisused
#instead.Thisbufferisallocatedperconnectiononfirstupdate
#statementintransaction
#在一个事务中binlog为了记录SQL状态所持有的cache大小
#如果你经常使用大的,多声明的事务,你可以增加此值来获取更大的性能.
#所有从事务来的状态都将被缓冲在binlog缓冲中然后在提交后一次性写入到binlog中
#如果事务比此值大,会使用磁盘上的临时文件来替代.
#此缓冲在每个连接的事务第一次更新状态时被创建
binlog_cache_size=1M
#MaximumallowedsizeforasingleHEAP(inmemory)table.Thisoption
#isaprotectionagainsttheaccidentialcreationofaverylargeHEAP
#tablewhichcouldotherwiseuseupallmemoryresources.
#独立的内存表所允许的最大容量.
#此选项为了防止意外创建一个超大的内存表导致永尽所有的内存资源.
max_heap_table_size=64M
#Sizeofthebufferusedfordoingfulltablescans.
#Allocatedperthread,ifafullscanisneeded.
#MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分#配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁，#并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。
read_buffer_size=2M
#Whenreadingrowsinsortedorderafterasort,therowsareread
#throughthisbuffertoavoiddiskseeks.YoucanimproveORDERBY
#performancealot,ifsetthistoahighvalue.
#Allocatedperthread,whenneeded.
#是MySql的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需#要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
read_rnd_buffer_size=16M
#SortbufferisusedtoperformsortsforsomeORDERBYandGROUPBY
#queries.Ifsorteddatadoesnotfitintothesortbuffer,adisk
#basedmergesortisusedinstead-Seethe"Sort_merge_passes"
#statusvariable.Allocatedperthreadifsortisneeded.
#排序缓冲被用来处理类似ORDERBY以及GROUPBY队列所引起的排序
#如果排序后的数据无法放入排序缓冲,
#一个用来替代的基于磁盘的合并分类会被使用
#查看“Sort_merge_passes”状态变量.
#在排序发生时由每个线程分配
sort_buffer_size=8M
#ThisbufferisusedfortheoptimizationoffullJOINs(JOINswithout
#indexes).SuchJOINsareverybadforperformanceinmostcases
#anyway,butsettingthisvariabletoalargevaluereducesthe
#performanceimpact.Seethe"Select_full_join"statusvariablefora
#countoffullJOINs.Allocatedperthreadiffulljoinisfound
#此缓冲被使用来优化全联合(fullJOINs不带索引的联合).
#类似的联合在极大多数情况下有非常糟糕的性能表现,
#但是将此值设大能够减轻性能影响.
#通过“Select_full_join”状态变量查看全联合的数量
#当全联合发生时,在每个线程中分配
join_buffer_size=8M
#Howmanythreadsweshouldkeepinacacheforreuse.Whenaclient
#disconnects,theclient'sthreadsareputinthecacheiftherearen't
#morethanthread_cache_sizethreadsfrombefore.Thisgreatlyreduces
#theamountofthreadcreationsneededifyouhavealotofnew
#connections.(Normallythisdoesn'tgiveanotableperformance
#improvementifyouhaveagoodthreadimplementation.)
#我们在cache中保留多少线程用于重用
#当一个客户端断开连接后,如果cache中的线程还少于thread_cache_size,
#则客户端线程被放入cache中.
#这可以在你需要大量新连接的时候极大的减少线程创建的开销
#(一般来说如果你有好的线程模型的话,这不会有明显的性能提升.)
thread_cache_size=8
#Thispermitstheapplicationtogivethethreadssystemahintforthe
#desirednumberofthreadsthatshouldberunatthesametime.This
#valueonlymakessenseonsystemsthatsupportthethread_concurrency()
#functioncall(SunSolaris,forexample).
#Youshouldtry[numberofCPUs]*(2..4)forthread_concurrency
#此允许应用程序给予线程系统一个提示在同一时间给予渴望被运行的线程的数量.
#此值只对于支持thread_concurrency()函数的系统有意义(例如SunSolaris).
#你可可以尝试使用[CPU数量]*(2..4)来作为thread_concurrency的值
thread_concurrency=8
#QuerycacheisusedtocacheSELECTresultsandlaterreturnthem
#withoutactualexecutingthesamequeryonceagain.Havingthequery
#cacheenabledmayresultinsignificantspeedimprovements,ifyour
#havealotofidenticalqueriesandrarelychangingtables.Seethe
#"Qcache_lowmem_prunes"statusvariabletocheckifthecurrentvalue
#ishighenoughforyourload.
#Note:Incaseyourtableschangeveryoftenorifyourqueriesare
#textuallydifferenteverytime,thequerycachemayresultina
#slowdowninsteadofaperformanceimprovement.
#查询缓冲常被用来缓冲SELECT的结果并且在下一次同样查询的时候不再执行直接返回结果.
#打开查询缓冲可以极大的提高服务器速度,如果你有大量的相同的查询并且很少修改表.
#查看“Qcache_lowmem_prunes”状态变量来检查是否当前值对于你的负载来说是否足够高.
#注意:在你表经常变化的情况下或者如果你的查询原文每次都不同,
#查询缓冲也许引起性能下降而不是性能提升.
query_cache_size=64M
#Onlycacheresultsetsthataresmallerthanthislimit.Thisisto
#protectthequerycacheofaverylargeresultsetoverwritingall
#otherqueryresults.
#只有小于此设定值的结果才会被缓冲
#此设置用来保护查询缓冲,防止一个极大的结果集将其他所有的查询结果都覆盖.
query_cache_limit=2M
#Minimumwordlengthtobeindexedbythefulltextsearchindex.
#Youmightwishtodecreaseitifyouneedtosearchforshorterwords.
#NotethatyouneedtorebuildyourFULLTEXTindex,afteryouhave
#modifiedthisvalue.
#被全文检索索引的最小的字长.
#你也许希望减少它,如果你需要搜索更短字的时候.
#注意在你修改此值之后,
#你需要重建你的FULLTEXT索引
ft_min_word_len=4
#Ifyoursystemsupportsthememlock()functioncall,youmightwantto
#enablethisoptionwhilerunningMySQLtokeepitlockedinmemoryand
#toavoidpotentialswappingoutincaseofhighmemorypressure.Good
#forperformance.
#如果你的系统支持memlock()函数,你也许希望打开此选项用以让运行中的mysql在在内存高度紧张的时候,数据在内存中保持锁定并且防止可能被swappingout
#此选项对于性能有益
#memlock
#Tabletypewhichisusedbydefaultwhencreatingnewtables,ifnot
#specifieddifferentlyduringtheCREATETABLEstatement.
#当创建新表时作为默认使用的表类型,
#如果在创建表示没有特别执行表类型,将会使用此值
default-storage-engine=MYISAM
#Threadstacksizetouse.Thisamountofmemoryisalwaysreservedat
#connectiontime.MySQLitselfusuallyneedsnomorethan64Kof
#memory,whileifyouuseyourownstackhungryUDFfunctionsoryour
#OSrequiresmorestackforsomeoperations,youmightneedtosetthis
#toahighervalue.
#线程使用的堆大小.此容量的内存在每次连接时被预留.
#MySQL本身常不会需要超过64K的内存
#如果你使用你自己的需要大量堆的UDF函数
#或者你的操作系统对于某些操作需要更多的堆,
#你也许需要将其设置的更高一点.
thread_stack=192K
#Setthedefaulttransactionisolationlevel.Levelsavailableare:
#设定默认的事务隔离级别.可用的级别如下:
#READ-UNCOMMITTED,READ-COMMITTED,REPEATABLE-READ,SERIALIZABLE
transaction_isolation=REPEATABLE-READ
#Maximumsizeforinternal(in-memory)temporarytables.Ifatable
#growslargerthanthisvalue,itisautomaticallyconvertedtodisk
#basedtableThislimitationisforasingletable.Therecanbemany
#ofthem.
#内部(内存中)临时表的最大大小
#如果一个表增长到比此值更大,将会自动转换为基于磁盘的表.
#此限制是针对单个表的,而不是总和.
tmp_table_size=64M
#Enablebinarylogging.ThisisrequiredforactingasaMASTERina
#replicationconfiguration.Youalsoneedthebinarylogifyouneed
#theabilitytodopointintimerecoveryfromyourlatestbackup.
#打开二进制日志功能.
#在复制(replication)配置中,作为MASTER主服务器必须打开此项
#如果你需要从你最后的备份中做基于时间点的恢复,你也同样需要二进制日志.
log-bin=mysql-bin
#binaryloggingformat-mixedrecommended
#设定记录二进制日志的格式，有三种格式，基于语句statement、基于行row、混合方式mixed
binlog_format=mixed
#Ifyou'reusingreplicationwithchainedslaves(A->B->C),youneedto
#enablethisoptiononserverB.Itenablesloggingofupdatesdoneby
#theslavethreadintotheslave'sbinarylog.
#如果你在使用链式从服务器结构的复制模式(A->B->C),
#你需要在服务器B上打开此项.
#此选项打开在从线程上重做过的更新的日志,
#并将其写入从服务器的二进制日志.
#log_slave_updates
#Enablethefullquerylog.Everyquery(evenoneswithincorrect
#syntax)thattheserverreceiveswillbelogged.Thisisusefulfor
#debugging,itisusuallydisabledinproductionuse.
#打开查询日志.所有的由服务器接收到的查询(甚至对于一个错误语法的查询)
#都会被记录下来.这对于调试非常有用,在生产环境中常常关闭此项.
#log#默认是没有开启的，会影响服务器性能
#Printwarningstotheerrorlogfile.Ifyouhaveanyproblemwith
#MySQLyoushouldenableloggingofwarningsandexaminetheerrorlog
#forpossibleexplanations.
#将警告打印输出到错误log文件.如果你对于MySQL有任何问题
#你应该打开警告log并且仔细审查错误日志,查出可能的原因.
#log_warnings
#Logslowqueries.Slowqueriesarequerieswhichtakemorethanthe
#amountoftimedefinedin"long_query_time"orwhichdonotuse
#indexeswell,iflog_short_formatisnotenabled.Itisnormallygoodidea
#tohavethisturnedonifyoufrequentlyaddnewqueriestothe
#system.
#记录慢速查询.慢速查询是指消耗了比“long_query_time”定义的更多时间的查询.
#如果log_long_format被打开,那些没有使用索引的查询也会被记录.
#如果你经常增加新查询到已有的系统内的话.一般来说这是一个好主意
slow_query_log
#Allqueriestakingmorethanthisamountoftime(inseconds)willbe
#tratedasslow.Donotuse"1"asavaluehere,asthiswillresultin
#evenveryfastqueriesbeingloggedfromtimetotime(asMySQL
#currentlymeasurestimewithsecondaccuracyonly).
#所有的使用了比这个时间(以秒为单位)更多的查询会被认为是慢速查询.
#不要在这里使用”1″,否则会导致所有的查询,甚至非常快的查询页被记录下来(由于MySQL目前时间的精确度只能达到秒的级别).
long_query_time=2
#***Replicationrelatedsettings#***主从复制相关的设置
#Uniqueserveridentificationnumberbetween1and2^32-1.Thisvalue
#isrequiredforbothmasterandslavehosts.Itdefaultsto1if
#"master-host"isnotset,butwillMySQLwillnotfunctionasamaster
#ifitisomitted.
#唯一的服务辨识号,数值位于1到2^32-1之间.
#此值在master和slave上都需要设置.
#如果“master-host”没有被设置,则默认为1,但是如果忽略此选项,MySQL不会作为master生效.
server-id=1
#ReplicationSlave(commentoutmastersectiontousethis)#复制的Slave(去掉master段的注释来使其生效)
#
#Toconfigurethishostasareplicationslave,youcanchoosebetween
#twomethods:#为了配置此主机作为复制的slave服务器,你可以选择两种方法:
#
#1)UsetheCHANGEMASTERTOcommand(fullydescribedinourmanual)-
#thesyntaxis:#使用CHANGEMASTERTO命令(在我们的手册中有完整描述)-
#语法如下:
#
#CHANGEMASTERTOMASTER_HOST=<host>,MASTER_PORT=<port>,
#MASTER_USER=<user>,MASTER_PASSWORD=<password>;
#
#whereyoureplace<host>,<user>,<password>byquotedstringsand
#<port>bythemaster'sportnumber(3306bydefault).
#你需要替换掉,,等被尖括号包围的字段以及使用master的端口号替换(默认3306).
#Example:案例
#
#CHANGEMASTERTOMASTER_HOST='125.564.12.1',MASTER_PORT=3306,
#MASTER_USER='joe',MASTER_PASSWORD='secret';
#
#OR或者
#
#2)Setthevariablesbelow.However,incaseyouchoosethismethod,then
#startreplicationforthefirsttime(evenunsuccessfully,forexample
#ifyoumistypedthepasswordinmaster-passwordandtheslavefailsto
#connect),theslavewillcreateamaster.infofile,andanylater
#changesinthisfiletothevariablevaluesbelowwillbeignoredand
#overriddenbythecontentofthemaster.infofile,unlessyoushutdown
#theslaveserver,deletemaster.infoandrestarttheslaverserver.
#Forthatreason,youmaywanttoleavethelinesbelowuntouched
#(commented)andinsteaduseCHANGEMASTERTO(seeabove)
#
#设置以下的变量.不论如何,在你选择这种方法的情况下,然后第一次启动复制(甚至不成功的情况下,
#例如如果你输入错密码在master-password字段并且slave无法连接),
#slave会创建一个master.info文件,并且之后任何对于包含在此文件内的参数的变化都会被忽略
#并且由master.info文件内的内容覆盖,除非你关闭slave服务,删除master.info并且重启slave服务.
#由于这个原因,你也许不想碰一下的配置(注释掉的)并且使用CHANGEMASTERTO(查看上面)来代替
#requireduniqueidbetween2and2^32-1
#(anddifferentfromthemaster)
#defaultsto2ifmaster-hostisset
#butwillnotfunctionasaslaveifomitted
#所需要的唯一id号位于2和2^32–1之间
#(并且和master不同)
#如果master-host被设置了.则默认值是2
#但是如果省略,则不会生效
#server-id=2
#
#Thereplicationmasterforthisslave–required
#复制结构中的master–必须
#master-host=<hostname>
#
#Theusernametheslavewilluseforauthenticationwhenconnecting
#tothemaster–required
#当连接到master上时slave所用来认证的用户名–必须
#master-user=<username>
#
#Thepasswordtheslavewillauthenticatewithwhenconnectingto
#themaster–required
#当连接到master上时slave所用来认证的密码–必须
#master-password=<password>
#
#Theportthemasterislisteningon.
#optional-defaultsto3306
#master监听的端口.
#可选–默认是3306
#master-port=<port>
#Maketheslaveread-only.OnlyuserswiththeSUPERprivilegeandthe
#replicationslavethreadwillbeabletomodifydataonit.Youcan
#usethistoensurethatnoapplicationswillaccidentlymodifydataon
#theslaveinsteadofthemaster
#使得slave只读.只有用户拥有SUPER权限和在上面的slave线程能够修改数据.
#你可以使用此项去保证没有应用程序会意外的修改slave而不是master上的数据
#read_only
#***MyISAMSpecificoptions
#***MyISAM相关选项
#SizeoftheKeyBuffer,usedtocacheindexblocksforMyISAMtables.
#Donotsetitlargerthan30%ofyouravailablememory,assomememory
#isalsorequiredbytheOStocacherows.Evenifyou'renotusing
#MyISAMtables,youshouldstillsetitto8-64Masitwillalsobe
#usedforinternaltemporarydisktables.
#关键词缓冲的大小,一般用来缓冲MyISAM表的索引块.
#不要将其设置大于你可用内存的30%,
#因为一部分内存同样被OS用来缓冲行数据
#甚至在你并不使用MyISAM表的情况下,你也需要仍旧设置起8-64M内存由于它同样会被内部临时磁盘表使用.
key_buffer_size=32M
#MyISAMusesspecialtree-likecachetomakebulkinserts(thatis,
#INSERT...SELECT,INSERT...VALUES(...),(...),...,andLOADDATA
#INFILE)faster.Thisvariablelimitsthesizeofthecachetreein
#bytesperthread.Settingitto0willdisablethisoptimisation.Do
#notsetitlargerthan"key_buffer_size"foroptimalperformance.
#Thisbufferisallocatedwhenabulkinsertisdetected.
#MyISAM使用特殊的类似树的cache来使得突发插入
#(这些插入是,INSERT…SELECT,INSERT…VALUES(…),(…),…,以及LOADDATA
#INFILE)更快.此变量限制每个进程中缓冲树的字节数.
#设置为0会关闭此优化.
#为了最优化不要将此值设置大于“key_buffer_size”.
#当突发插入被检测到时此缓冲将被分配.
bulk_insert_buffer_size=64M
#ThisbufferisallocatedwhenMySQLneedstorebuildtheindexin
#REPAIR,OPTIMIZE,ALTERtablestatementsaswellasinLOADDATAINFILE
#intoanemptytable.Itisallocatedperthreadsobecarefulwith
#largesettings.
#此缓冲当MySQL需要在REPAIR,OPTIMIZE,ALTER以及LOADDATAINFILE到一个空表中引起重建索引时被分配.
#这在每个线程中被分配.所以在设置大值时需要小心.
myisam_sort_buffer_size=128M
#ThemaximumsizeofthetemporaryfileMySQLisallowedtousewhile
#recreatingtheindex(duringREPAIR,ALTERTABLEorLOADDATAINFILE.
#Ifthefile-sizewouldbebiggerthanthis,theindexwillbecreated
#throughthekeycache(whichisslower).
#MySQL重建索引时所允许的最大临时文件的大小(当REPAIR,ALTERTABLE或者LOADDATAINFILE).
#如果文件大小比此值更大,索引会通过键值缓冲创建(更慢)
myisam_max_sort_file_size=10G
#Ifatablehasmorethanoneindex,MyISAMcanusemorethanone
#threadtorepairthembysortinginparallel.Thismakessenseifyou
#havemultipleCPUsandplentyofmemory.
#如果一个表拥有超过一个索引,MyISAM可以通过并行排序使用超过一个线程去修复他们.
#这对于拥有多个CPU以及大量内存情况的用户,是一个很好的选择.
myisam_repair_threads=1
#AutomaticallycheckandrepairnotproperlyclosedMyISAMtables.
#自动检查和修复没有适当关闭的MyISAM表.
myisam_recover

#***INNODBSpecificoptions***
#***INNODB相关选项***
#UsethisoptionifyouhaveaMySQLserverwithInnoDBsupportenabled
#butyoudonotplantouseit.Thiswillsavememoryanddiskspace
#andspeedupsomethings.
#如果你的MySQL服务包含InnoDB支持但是并不打算使用的话,
#使用此选项会节省内存以及磁盘空间,并且加速某些部分
#skip-innodb
#AdditionalmemorypoolthatisusedbyInnoDBtostoremetadata
#information.IfInnoDBrequiresmorememoryforthispurposeitwill
#starttoallocateitfromtheOS.Asthisisfastenoughonmost
#recentoperatingsystems,younormallydonotneedtochangethis
#value.SHOWINNODBSTATUSwilldisplaythecurrentamountused.
#附加的内存池被InnoDB用来保存metadata信息
#如果InnoDB为此目的需要更多的内存,它会开始从OS这里申请内存.
#由于这个操作在大多数现代操作系统上已经足够快,你一般不需要修改此值.
#SHOWINNODBSTATUS命令会显示当先使用的数量.
innodb_additional_mem_pool_size=16M
#InnoDB,unlikeMyISAM,usesabufferpooltocachebothindexesand
#rowdata.ThebiggeryousetthisthelessdiskI/Oisneededto
#accessdataintables.Onadedicateddatabaseserveryoumaysetthis
#parameterupto80%ofthemachinephysicalmemorysize.Donotsetit
#toolarge,though,becausecompetitionofthephysicalmemorymay
#causepagingintheoperatingsystem.Notethaton32bitsystemsyou
#mightbelimitedto2-3.5Gofuserlevelmemoryperprocess,sodonot
#setittoohigh.
#InnoDB使用一个缓冲池来保存索引和原始数据,不像MyISAM.
#这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少.
#在一个独立使用的数据库服务器上,你可以设置这个变量到服务器物理内存大小的80%
#不要设置过大,否则,由于物理内存的竞争可能导致操作系统的换页颠簸.
#注意在32位系统上你每个进程可能被限制在2-3.5G用户层面内存限制,
#所以不要设置的太高.
innodb_buffer_pool_size=2G
#InnoDBstoresdatainoneormoredatafilesformingthetablespace.
#Ifyouhaveasinglelogicaldriveforyourdata,asingle
#autoextendingfilewouldbegoodenough.Inothercases,asinglefile
#perdeviceisoftenagoodchoice.YoucanconfigureInnoDBtouseraw
#diskpartitionsaswell-pleaserefertothemanualformoreinfo
#aboutthis.
#InnoDB将数据保存在一个或者多个数据文件中成为表空间.
#如果你只有单个逻辑驱动保存你的数据,一个单个的自增文件就足够好了.
#其他情况下.每个设备一个文件一般都是个好的选择.
#你也可以配置InnoDB来使用裸盘分区–请参考手册来获取更多相关内容
innodb_data_file_path=ibdata1:10M:autoextend
#SetthisoptionifyouwouldliketheInnoDBtablespacefilestobe
#storedinanotherlocation.BydefaultthisistheMySQLdatadir.
#设置此选项如果你希望InnoDB表空间文件被保存在其他分区.
#默认保存在MySQL的datadir中.
#innodb_data_home_dir=<directory>
#NumberofIOthreadstouseforasyncIOoperations.Thisvalueis
#hardcodedto8onUnix,butonWindowsdiskI/Omaybenefitfroma
#largernumber.
#用来同步IO操作的IO线程的数量.Thisvalueis
#此值在Unix下被硬编码为8,但是在Windows磁盘I/O可能在一个大数值下表现的更好.
innodb_write_io_threads=8
innodb_read_io_threads=8
#IfyourunintoInnoDBtablespacecorruption,settingthistoanonzero
#valuewilllikelyhelpyoutodumpyourtables.Startfromvalue1and
#increaseituntilyou'reabletodumpthetablesuccessfully.
#如果你发现InnoDB表空间损坏,设置此值为一个非零值可能帮助你导出你的表.
#从1开始并且增加此值知道你能够成功的导出表.
#innodb_force_recovery=1
#NumberofthreadsallowedinsidetheInnoDBkernel.Theoptimalvalue
#dependshighlyontheapplication,hardwareaswellastheOS
#schedulerproperties.Atoohighvaluemayleadtothreadthrashing.
#在InnoDb核心内的允许线程数量.
#最优值依赖于应用程序,硬件以及操作系统的调度方式.
#过高的值可能导致线程的互斥颠簸.
innodb_thread_concurrency=16
#Ifsetto1,InnoDBwillflush(fsync)thetransactionlogstothe
#diskateachcommit,whichoffersfullACIDbehavior.Ifyouare
#willingtocompromisethissafety,andyouarerunningsmall
#transactions,youmaysetthisto0or2toreducediskI/Otothe
#logs.Value0meansthatthelogisonlywrittentothelogfileand
#thelogfileflushedtodiskapproximatelyoncepersecond.Value2
#meansthelogiswrittentothelogfileateachcommit,butthelog
#fileisonlyflushedtodiskapproximatelyoncepersecond.
#如果设置为1,InnoDB会在每次提交后刷新(fsync)事务日志到磁盘上,
#这提供了完整的ACID行为.
#如果你愿意对事务安全折衷,并且你正在运行一个小的食物,你可以设置此值到0或者2来减少由事务日志引起的磁盘I/O
#0代表日志只大约每秒写入日志文件并且日志文件刷新到磁盘.
#2代表日志写入日志文件在每次提交后,但是日志文件只有大约每秒才会刷新到磁盘上.
innodb_flush_log_at_trx_commit=1
#SpeedupInnoDBshutdown.ThiswilldisableInnoDBtodoafullpurge
#andinsertbuffermergeonshutdown.Itmayincreaseshutdowntimea
#lot,butInnoDBwillhavetodoitonthenextstartupinstead.
#加速InnoDB的关闭.这会阻止InnoDB在关闭时做全清除以及插入缓冲合并.
#这可能极大增加关机时间,但是取而代之的是InnoDB可能在下次启动时做这些操作.
#innodb_fast_shutdown
#ThesizeofthebufferInnoDBusesforbufferinglogdata.Assoonas
#itisfull,InnoDBwillhavetoflushittodisk.Asitisflushed
#oncepersecondanyway,itdoesnotmakesensetohaveitverylarge
#(evenwithlongtransactions).
#用来缓冲日志数据的缓冲区的大小.
#当此值快满时,InnoDB将必须刷新数据到磁盘上.
#由于基本上每秒都会刷新一次,所以没有必要将此值设置的太大(甚至对于长事务而言)
innodb_log_buffer_size=8M
#Sizeofeachlogfileinaloggroup.Youshouldsetthecombinedsize
#oflogfilestoabout25%-100%ofyourbufferpoolsizetoavoid
#unneededbufferpoolflushactivityonlogfileoverwrite.However,
#notethatalargerlogfilesizewillincreasethetimeneededforthe
#recoveryprocess.
#在日志组中每个日志文件的大小.
#你应该设置日志文件总合大小到你缓冲池大小的25%~100%
#来避免在日志文件覆写上不必要的缓冲池刷新行为.
#不论如何,请注意一个大的日志文件大小会增加恢复进程所需要的时间.
innodb_log_file_size=256M
#Totalnumberoffilesintheloggroup.Avalueof2-3isusuallygood
#enough.
#在日志组中的文件总数.
#通常来说2~3是比较好的.
innodb_log_files_in_group=3
#LocationoftheInnoDBlogfiles.DefaultistheMySQLdatadir.You
#maywishtopointittoadedicatedharddriveoraRAID1volumefor
#improvedperformance
#InnoDB的日志文件所在位置.默认是MySQL的datadir.
#你可以将其指定到一个独立的硬盘上或者一个RAID1卷上来提高其性能
#innodb_log_group_home_dir
#MaximumallowedpercentageofdirtypagesintheInnoDBbufferpool.
#Ifitisreached,InnoDBwillstartflushingthemoutagressivelyto
#notrunoutofcleanpagesatall.Thisisasoftlimit,not
#guaranteedtobeheld.
#在InnoDB缓冲池中最大允许的脏页面的比例.
#如果达到限额,InnoDB会开始刷新他们防止他们妨碍到干净数据页面.
#这是一个软限制,不被保证绝对执行.
innodb_max_dirty_pages_pct=90
#TheflushmethodInnoDBwilluseforLog.Thetablespacealwaysuses
#doublewriteflushlogic.Thedefaultvalueis"fdatasync",another
#optionis"O_DSYNC".
#InnoDB用来刷新日志的方法.
#表空间总是使用双重写入刷新方法
#默认值是“fdatasync”,另一个是“O_DSYNC”.
#innodb_flush_method=O_DSYNC
#HowlonganInnoDBtransactionshouldwaitforalocktobegranted
#beforebeingrolledback.InnoDBautomaticallydetectstransaction
#deadlocksinitsownlocktableandrollsbackthetransaction.Ifyou
#usetheLOCKTABLEScommand,orothertransaction-safestorageengines
#thanInnoDBinthesametransaction,thenadeadlockmayarisewhich
#InnoDBcannotnotice.Incaseslikethisthetimeoutisusefulto
#resolvethesituation.
#在被回滚前,一个InnoDB的事务应该等待一个锁被批准多久.
#InnoDB在其拥有的锁表中自动检测事务死锁并且回滚事务.
#如果你使用LOCKTABLES指令,或者在同样事务中使用除了InnoDB以外的其他事务安全的存储引擎
#那么一个死锁可能发生而InnoDB无法注意到.
#这种情况下这个timeout值对于解决这种问题就非常有帮助.
innodb_lock_wait_timeout=120
[mysqldump]
#Donotbufferthewholeresultsetinmemorybeforewritingitto
#file.Requiredfordumpingverylargetables
#不要在将内存中的整个结果写入磁盘之前缓存.在导出非常巨大的表时需要此项
quick
max_allowed_packet=16M
[mysql]
no-auto-rehash
#OnlyallowUPDATEsandDELETEsthatusekeys.
#仅仅允许使用键值的UPDATEs和DELETEs.
#safe-updates
[myisamchk]
key_buffer_size=512M
sort_buffer_size=512M
read_buffer=8M
write_buffer=8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
#Increasetheamountofopenfilesallowedperprocess.Warning:Make
#sureyouhavesettheglobalsystemlimithighenough!Thehighvalue
#isrequiredforalargenumberofopenedtables
#增加每个进程的可打开文件数量.
#警告:确认你已经将全系统限制设定的足够高!
#打开大量表需要将此值设大
open-files-limit=8192

三、配置文件优化（根据实际情况优化）
说明，上文中我对my-innodb-heavy-4G.cnf中默认的所有选项进行了说明，下面我就根据我们公司的实际情况进行优化！
1.服务器的运行环境
硬件服务器：DellR710，双至强E5620CPU、16G内存、6*500G硬盘
操作系统：CentOS5.5X86_64系统
Mysql版本：MySQL5.5.32
适用于：日IP100-200W，日PV200-500W的站点
2.具体优化配置如下

[client]
port=3306
socket=/tmp/mysql.sock
default-character-set=utf8#设置客户端的字符编码
[mysqld]
#genericconfigurationoptions
port=3306
socket=/tmp/mysql.sock
#***charset***
character-set-server=utf8#设置服务器端的字符编码

#***network***
back_log=512
#skip-networking#默认没有开启
max_connections=3000
max_connect_errors=30
table_open_cache=4096
#external-locking#默认没有开启
max_allowed_packet=32M
max_heap_table_size=128M

#***globalcache***
read_buffer_size=8M
read_rnd_buffer_size=64M
sort_buffer_size=16M
join_buffer_size=16M

#***thread***
thread_cache_size=16
thread_concurrency=8
thread_stack=512K

#***querycache***
query_cache_size=128M
query_cache_limit=4M

#***index***
ft_min_word_len=8

#memlock#默认没有开启
default-storage-engine=INNODB
transaction_isolation=REPEATABLE-READ

#***tmptable***
tmp_table_size=64M

#***binlog***
log-bin=mysql-bin
binlog_cache_size=4M
binlog_format=mixed
#log_slave_updates#默认没有开启
#log#默认没有开启，此处是查询日志，开启会影响服务器性能
log_warnings#开启警告日志

#***slowquerylog***
slow_query_log
long_query_time=10
#***Replicationrelatedsettings
server-id=1
#server-id=2
#master-host=<hostname>
#master-user=<username>
#master-password=<password>
#master-port=<port>
#read_only
#***MyISAMSpecificoptions
key_buffer_size=128M
bulk_insert_buffer_size=256M
myisam_sort_buffer_size=256M
myisam_max_sort_file_size=10G
myisam_repair_threads=1
myisam_recover

#***INNODBSpecificoptions***
#skip-innodb#默认没有开启
innodb_additional_mem_pool_size=64M
innodb_buffer_pool_size=6G#注意在32位系统上你每个进程可能被限制在2-3.5G用户层面内存限制,所以不要设置的太高.
innodb_data_file_path=ibdata1:10M:autoextend
#innodb_data_home_dir=<directory>
innodb_write_io_threads=8
innodb_read_io_threads=8
#innodb_force_recovery=1
innodb_thread_concurrency=16
innodb_flush_log_at_trx_commit=2
#说明：innodb_flush_log_at_trx_commit=2如果是游戏服务器，建议此值设置为2；如果是对数据安全要求极高的应用，建议设置为1；设置为0性能最高，但如果发生故障，数据可能会有丢失的危险！默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电池供电缓存（Batterybackedupcache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统挂了时才可能丢数据。
#innodb_fast_shutdown
innodb_log_buffer_size=16M
innodb_log_file_size=512M
innodb_log_files_in_group=3
#innodb_log_group_home_dir
innodb_max_dirty_pages_pct=90
#innodb_flush_method=O_DSYNC
innodb_lock_wait_timeout=120
[mysqldump]
quick
max_allowed_packet=32M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size=2048M
sort_buffer_size=2048M
read_buffer=32M
write_buffer=32M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit=10240

3.总结
MySQL配置文件的优化是根据线上环境的实际需要进行优化，不能随便没有根据的进行优化，写这篇博文就是给博友们一些参考！
4.MySQL状态查看的常用命令

mysql>showstatus;#显示状态信息
mysql>showvariables;#显示系统变量
mysql>showengines;#查看所有引擎
mysql>showengineinnodbstatus;#显示InnoDB存储引擎的状态

本文出自“Shareyourknowledge…”博客

经过毛票票小编的测试，这样的效率很不错，直接拿来就可以使用。

MySQL 5.5.49 大内存优化配置文件优化详解

热门推荐

随机推荐