如何在MySQL中分配innodb_buffer_pool_size

<p>innodb_buffer_pool_size是整个MySQL服务器最重要的变量。</p><p>1. 为什么需要innodb buffer pool?</p><p>在MySQL5.5之前,广泛使用的和默认的存储引擎是MyISAM。MyISAM使用操作系统缓存来缓存数据。InnoDB需要innodb buffer pool中处理缓存。所以非常需要有足够的InnoDB buffer pool空间。</p><p>2. MySQL InnoDB buffer pool 里包含什么?</p><p>数据缓存</p><p>InnoDB数据页面</p><p>索引缓存</p><p>索引数据</p><p>缓冲数据</p><p>脏页(在内存中修改尚未刷新(写入)到磁盘的数据)</p><p>内部结构</p><p>如自适应哈希索引,行锁等。</p><p>3. 如何设置innodb_buffer_pool_size?</p><p>innodb_buffer_pool_size默认大小为128M。最大值取决于CPU的架构。在32-bit平台上,最大值为2**32 -1,在64-bit平台上最大值为2**64-1。当缓冲池大小大于1G时,将innodb_buffer_pool_instances设置大于1的值可以提高服务器的可扩展性。</p><p>大的缓冲池可以减小多次磁盘I/O访问相同的表数据。在专用数据库服务器上,可以将缓冲池大小设置为服务器物理内存的80%。</p><p>3.1 配置缓冲池大小时,请注意以下潜在问题</p><p>物理内存争用可能导致操作系统频繁的paging</p><p>InnoDB为缓冲区和control structures保留了额外的内存,因此总分配空间比指定的缓冲池大小大约大10%。</p><p>缓冲池的地址空间必须是连续的,这在带有在特定地址加载的DLL的Windows系统上可能是一个问题。</p><p>初始化缓冲池的时间大致与其大小成比例。在具有大缓冲池的实例上,初始化时间可能很长。要减少初始化时间,可以在服务器关闭时保存缓冲池状态,并在服务器启动时将其还原。</p><p>innodb_buffer_pool_dump_pct:指定每个缓冲池最近使用的页面读取和转储的百分比。 范围是1到100。默认值是25。例如,如果有4个缓冲池,每个缓冲池有100个page,并且innodb_buffer_pool_dump_pct设置为25,则dump每个缓冲池中最近使用的25个page。</p><p>innodb_buffer_pool_dump_at_shutdown:默认启用。指定在MySQL服务器关闭时是否记录在InnoDB缓冲池中缓存的页面,以便在下次重新启动时缩短预热过程。</p><p>innodb_buffer_pool_load_at_startup:默认启用。指定在MySQL服务器启动时,InnoDB缓冲池通过加载之前保存的相同页面自动预热。 通常与innodb_buffer_pool_dump_at_shutdown结合使用。</p><p>增大或减小缓冲池大小时,将以chunk的形式执行操作。chunk大小由innodb_buffer_pool_chunk_size配置选项定义,默认值为128 MB。</p><p>缓冲池大小必须始终等于或者是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数。</p><p>如果将缓冲池大小更改为不等于或等于innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数的值,</p><p>则缓冲池大小将自动调整为等于或者是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数的值。</p><p>innodb_buffer_pool_size可以动态设置,允许在不重新启动服务器的情况下调整缓冲池的大小。 可以通过状态变量Innodb_buffer_pool_resize_status报告在线调整缓冲池大小操作的状态。</p><pre class="brush:bash;toolbar:false">mysql&gt;showstatuslike&#39;Innodb_buffer_pool_resize%&#39;; +----------------------------------+-------+ |Variable_name|Value| +----------------------------------+-------+ |Innodb_buffer_pool_resize_status|| +----------------------------------+-------+</pre><p></p><p>3.2 配置示例</p><p>在以下示例中,innodb_buffer_pool_size设置为3G,innodb_buffer_pool_instances设置为8。innodb_buffer_pool_chunk_size默认值为128M。<br/></p><p>3G是有效的innodb_buffer_pool_size值,因为3G是innodb_buffer_pool_instances = 8 * innodb_buffer_pool_chunk_size = 128M的倍数<br/></p><pre class="brush:bash;toolbar:false"> #mysqld--innodb_buffer_pool_size=3G--innodb_buffer_pool_instances=8&amp; mysql&gt;showvariableslike&#39;innodb_buffer_pool%&#39;; +-------------------------------------+----------------+ |Variable_name|Value| +-------------------------------------+----------------+ |innodb_buffer_pool_chunk_size|134217728| |innodb_buffer_pool_dump_at_shutdown|ON| |innodb_buffer_pool_dump_now|OFF| |innodb_buffer_pool_dump_pct|25| |innodb_buffer_pool_filename|ib_buffer_pool| |innodb_buffer_pool_instances|8| |innodb_buffer_pool_load_abort|OFF| |innodb_buffer_pool_load_at_startup|ON| |innodb_buffer_pool_load_now|OFF| |innodb_buffer_pool_size|3221225472| +-------------------------------------+----------------+ 10rowsinset(0.01sec)</pre><p>在以下示例中,innodb_buffer_pool_size设置为3G,innodb_buffer_pool_instances设置为16. innodb_buffer_pool_chunk_size为128M。</p><p>3G不是有效的innodb_buffer_pool_size值,因为3G不是innodb_buffer_pool_instances = 16 * innodb_buffer_pool_chunk_size = 128M的倍数,可以看出innodb_buffer_pool_size的值自动调整到4GB<br/></p><pre class="brush:bash;toolbar:false"> #mysqld--innodb_buffer_pool_size=3G--innodb_buffer_pool_instances=16&amp; mysql&gt;showvariableslike&#39;%innodb_buffer_pool%&#39;; +-------------------------------------+----------------+ |Variable_name|Value| +-------------------------------------+----------------+ |innodb_buffer_pool_chunk_size|134217728| |innodb_buffer_pool_dump_at_shutdown|ON| |innodb_buffer_pool_dump_now|OFF| |innodb_buffer_pool_dump_pct|25| |innodb_buffer_pool_filename|ib_buffer_pool| |innodb_buffer_pool_instances|16| |innodb_buffer_pool_load_abort|OFF| |innodb_buffer_pool_load_at_startup|ON| |innodb_buffer_pool_load_now|OFF| |innodb_buffer_pool_size|4294967296| +-------------------------------------+----------------+ 10rowsinset(0.01sec)</pre><p><br/></p><p>3.3 在线调整InnoDB缓冲池大小</p><pre class="brush:bash;toolbar:false">mysql&gt;SETGLOBALinnodb_buffer_pool_size=3221225472</pre><p>3.4 监控在线缓冲池调整进度</p><pre class="brush:bash;toolbar:false">mysql&gt;SHOWSTATUSWHEREVariable_name=&#39;InnoDB_buffer_pool_resize_status&#39;; +----------------------------------+----------------------------------------------------+ |Variable_name|Value| +----------------------------------+----------------------------------------------------+ |Innodb_buffer_pool_resize_status|Completedresizingbufferpoolat18082415:05:03.| +----------------------------------+----------------------------------------------------+</pre><p>缓冲池大小调整进度也记录在服务器错误日志中。以下为增大时,记录的日志</p><pre class="brush:bash;toolbar:false">2018-08-24T07:05:03.819049Z2[Note]InnoDB:Requestedtoresizebufferpool.(newsize:3221225472bytes) 2018-08-24T07:05:03.819141Z0[Note]InnoDB:Resizingbufferpoolfrom2684354560to3221225472(unit=134217728). 2018-08-24T07:05:03.819155Z0[Note]InnoDB:Disablingadaptivehashindex. 2018-08-24T07:05:03.824902Z0[Note]InnoDB:disabledadaptivehashindex. 2018-08-24T07:05:03.824933Z0[Note]InnoDB:Withdrawingblockstobeshrunken. 2018-08-24T07:05:03.824940Z0[Note]InnoDB:Latchingwholeofbufferpool. 2018-08-24T07:05:03.824959Z0[Note]InnoDB:bufferpool0:resizingwithchunks5to6. 2018-08-24T07:05:03.839564Z0[Note]InnoDB:bufferpool0:1chunks(8192blocks)wereadded. 2018-08-24T07:05:03.839594Z0[Note]InnoDB:bufferpool1:resizingwithchunks5to6. 2018-08-24T07:05:03.848910Z0[Note]InnoDB:bufferpool1:1chunks(8192blocks)wereadded. 2018-08-24T07:05:03.849046Z0[Note]InnoDB:bufferpool2:resizingwithchunks5to6. 2018-08-24T07:05:03.856711Z0[Note]InnoDB:bufferpool2:1chunks(8192blocks)wereadded. 2018-08-24T07:05:03.856741Z0[Note]InnoDB:bufferpool3:resizingwithchunks5to6. 2018-08-24T07:05:03.864867Z0[Note]InnoDB:bufferpool3:1chunks(8192blocks)wereadded. 2018-08-24T07:05:03.864902Z0[Note]InnoDB:Completedtoresizebufferpoolfrom2684354560to3221225472. 2018-08-24T07:05:03.864915Z0[Note]InnoDB:Re-enabledadaptivehashindex. 2018-08-24T07:05:03.864935Z0[Note]InnoDB:Completedresizingbufferpoolat18082415:05:03.</pre><p>以下为减小时,记录的日志</p><pre class="brush:bash;toolbar:false"> 2018-08-24T07:10:20.666816Z2[Note]InnoDB:Requestedtoresizebufferpool.(newsize:2684354560bytes) 2018-08-24T07:10:20.666880Z0[Note]InnoDB:Resizingbufferpoolfrom3221225472to2684354560(unit=134217728). 2018-08-24T07:10:20.666889Z0[Note]InnoDB:Disablingadaptivehashindex. 2018-08-24T07:10:20.673416Z0[Note]InnoDB:disabledadaptivehashindex. 2018-08-24T07:10:20.673508Z0[Note]InnoDB:Withdrawingblockstobeshrunken. 2018-08-24T07:10:20.673519Z0[Note]InnoDB:bufferpool0:starttowithdrawthelast8192blocks. 2018-08-24T07:10:20.678441Z0[Note]InnoDB:bufferpool0:withdrawingblocks.(8192/8192) 2018-08-24T07:10:20.678521Z0[Note]InnoDB:bufferpool0:withdrew8192blocksfromfreelist.Triedtorelocate0pages(8192/8192). 2018-08-24T07:10:20.678919Z0[Note]InnoDB:bufferpool0:withdrawntarget8192blocks. 2018-08-24T07:10:20.678977Z0[Note]InnoDB:bufferpool1:starttowithdrawthelast8192blocks. 2018-08-24T07:10:20.681644Z0[Note]InnoDB:bufferpool1:withdrawingblocks.(8192/8192) 2018-08-24T07:10:20.682168Z0[Note]InnoDB:bufferpool1:withdrew8192blocksfromfreelist.Triedtorelocate0pages(8192/8192). 2018-08-24T07:10:20.682235Z0[Note]InnoDB:bufferpool1:withdrawntarget8192blocks. 2018-08-24T07:10:20.682254Z0[Note]InnoDB:bufferpool2:starttowithdrawthelast8192blocks. 2018-08-24T07:10:20.686560Z0[Note]InnoDB:bufferpool2:withdrawingblocks.(8192/8192) 2018-08-24T07:10:20.686917Z0[Note]InnoDB:bufferpool2:withdrew8192blocksfromfreelist.Triedtorelocate0pages(8192/8192). 2018-08-24T07:10:20.687002Z0[Note]InnoDB:bufferpool2:withdrawntarget8192blocks. 2018-08-24T07:10:20.687010Z0[Note]InnoDB:bufferpool3:starttowithdrawthelast8192blocks. 2018-08-24T07:10:20.690038Z0[Note]InnoDB:bufferpool3:withdrawingblocks.(8192/8192) 2018-08-24T07:10:20.690373Z0[Note]InnoDB:bufferpool3:withdrew8192blocksfromfreelist.Triedtorelocate0pages(8192/8192). 2018-08-24T07:10:20.690433Z0[Note]InnoDB:bufferpool3:withdrawntarget8192blocks. 2018-08-24T07:10:20.690479Z0[Note]InnoDB:Latchingwholeofbufferpool. 2018-08-24T07:10:20.690498Z0[Note]InnoDB:bufferpool0:resizingwithchunks6to5. 2018-08-24T07:10:20.693293Z0[Note]InnoDB:bufferpool0:1chunks(8192blocks)werefreed. 2018-08-24T07:10:20.693357Z0[Note]InnoDB:bufferpool1:resizingwithchunks6to5. 2018-08-24T07:10:20.695947Z0[Note]InnoDB:bufferpool1:1chunks(8192blocks)werefreed. 2018-08-24T07:10:20.696011Z0[Note]InnoDB:bufferpool2:resizingwithchunks6to5. 2018-08-24T07:10:20.698977Z0[Note]InnoDB:bufferpool2:1chunks(8192blocks)werefreed. 2018-08-24T07:10:20.699288Z0[Note]InnoDB:bufferpool3:resizingwithchunks6to5. 2018-08-24T07:10:20.702088Z0[Note]InnoDB:bufferpool3:1chunks(8192blocks)werefreed. 2018-08-24T07:10:20.702398Z0[Note]InnoDB:Completedtoresizebufferpoolfrom3221225472to2684354560. 2018-08-24T07:10:20.702413Z0[Note]InnoDB:Re-enabledadaptivehashindex. 2018-08-24T07:10:20.703896Z0[Note]InnoDB:Completedresizingbufferpoolat18082415:10:20.</pre><p></p><p>4. 配置的innodb_buffer_pool_size是否合适?</p><p>当前配置的innodb_buffer_pool_size是否合适,可以通过分析InnoDB缓冲池的性能来验证。</p><p>可以使用以下公式计算InnoDB缓冲池性能:<br/></p><p>Performance = innodb_buffer_pool_reads / innodb_buffer_pool_read_requests * 100<br/></p><p>innodb_buffer_pool_reads:表示InnoDB缓冲池无法满足的请求数。需要从磁盘中读取。<br/></p><p>innodb_buffer_pool_read_requests:表示从内存中读取逻辑的请求数。<br/></p><p>例如,在我的服务器上,检查当前InnoDB缓冲池的性能:</p><pre class="brush:bash;toolbar:false"> root@localhost[(none)]15:35:31&gt;showstatuslike&#39;innodb_buffer_pool_read%&#39;; +---------------------------------------+-------------+ |Variable_name|Value| +---------------------------------------+-------------+ |Innodb_buffer_pool_read_ahead_rnd|0| |Innodb_buffer_pool_read_ahead|0| |Innodb_buffer_pool_read_ahead_evicted|0| |Innodb_buffer_pool_read_requests|4029033624| |Innodb_buffer_pool_reads|91661| +---------------------------------------+-------------+ 5rowsinset(0.00sec) Performance=91661/4029033624*100=0.0022750120389663</pre><p>意味着InnoDB可以满足缓冲池本身的大部分请求。从磁盘完成读取的百分比非常小。因此无需增加innodb_buffer_pool_size值。</p><p>4.1 什么时候减小innodb_buffer_pool_size?</p><p>在专用MySQL服务器上,多余的innodb_buffer内存不会有问题,但是当使用共享服务器时,可能会有性能影响。因为空闲内存对其他程序和操作系统很有用。</p><p>可以使用SHOW ENGINE INNODB STATUS\G命令检查内存状态:</p><pre class="brush:bash;toolbar:false"> mysql&gt;showengineinnodbstatus\G ... Totallargememoryallocated26386366464 Dictionarymemoryallocated23826297 Bufferpoolsize1572672 Freebuffers8192 Databasepages1553364 Olddatabasepages573246 Modifieddbpages36 Pendingreads0 Pendingwrites:LRU0,flushlist0,singlepage0 Pagesmadeyoung881819,notyoung18198964 0.02youngs/s,0.05non-youngs/s Pagesread681064,created2749237,written3988300 0.02reads/s,0.12creates/s,11.50writes/s Bufferpoolhitrate1000/1000,young-makingrate0/1000not0/1000 Pagesreadahead0.00/s,evictedwithoutaccess0.00/s,Randomreadahead0.00/s LRUlen:1553364,unzip_LRUlen:0 I/Osum[5152]:cur[0],unzipsum[0]:cur[0] ...</pre><p></p><p>Free buffers :表示有多少空闲buffer。如果 此值长时间都较高,则可以考虑减小InnoDB缓冲池大小。</p><p>InnoDB buffer pool 命中率:<br/></p><p>InnoDB buffer pool 命中率 = innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_reads ) * 100</p><p>此值低于99%,则可以考虑增加innodb_buffer_pool_size。<br/></p><p>5. InnoDB缓冲池状态变量有哪些?</p><p>可以运行以下命令进行查看:</p><pre class="brush:bash;toolbar:false"> root@localhost[(none)]16:00:31&gt;showglobalstatuslike&#39;%innodb_buffer_pool_pages%&#39;; +----------------------------------+--------+ |Variable_name|Value| +----------------------------------+--------+ |Innodb_buffer_pool_pages_data|457| |Innodb_buffer_pool_pages_dirty|0| |Innodb_buffer_pool_pages_flushed|36| |Innodb_buffer_pool_pages_free|163363| |Innodb_buffer_pool_pages_misc|0| |Innodb_buffer_pool_pages_total|163820| +----------------------------------+--------+ 6rowsinset(0.00sec)</pre><p></p><p>说明:</p><p>Innodb_buffer_pool_pages_data</p><p>InnoDB缓冲池中包含数据的页数。 该数字包括脏页面和干净页面。 使用压缩表时,报告的Innodb_buffer_pool_pages_data值可能大于Innodb_buffer_pool_pages_total(Bug#59550)。</p><p>Innodb_buffer_pool_pages_dirty</p><p>显示在内存中修改但尚未写入数据文件的InnoDB缓冲池数据页的数量(脏页刷新)。</p><p>Innodb_buffer_pool_pages_flushed</p><p>表示从InnoDB缓冲池中刷新脏页的请求数。</p><p>Innodb_buffer_pool_pages_free</p><p>显示InnoDB缓冲池中的空闲页面</p><p>Innodb_buffer_pool_pages_misc</p><p>InnoDB缓冲池中的页面数量很多,因为它们已被分配用于管理开销,例如行锁或自适应哈希索引。此值也可以计算为Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free - Innodb_buffer_pool_pages_data。</p><p>Innodb_buffer_pool_pages_total</p><p>InnoDB缓冲池的总大小,以page为单位。</p><p>innodb_buffer_pool_reads</p><p>表示InnoDB缓冲池无法满足的请求数。需要从磁盘中读取。</p><p>innodb_buffer_pool_read_requests</p><p>它表示从内存中逻辑读取的请求数。</p><p>innodb_buffer_pool_wait_free</p><p>通常,对InnoDB缓冲池的写入发生在后台。 当InnoDB需要读取或创建页面并且没有可用的干净页面时,InnoDB首先刷新一些脏页并等待该操作完成。 此计数器计算这些等待的实例。 如果已正确设置innodb_buffer_pool_size,则此值应该很小。如果大于0,则表示InnoDb缓冲池太小。</p><p>innodb_buffer_pool_write_request</p><p>表示对缓冲池执行的写入次数。</p><p>6. InnoDB缓冲池当前使用了多少实际GB内存?</p><p>通过将缓冲池中可用的数据与InnoDB页面(InnoDB缓冲池单位)大小相乘,我们可以发现InnoDB缓冲池此时正在使用的实际内存。</p><pre class="brush:bash;toolbar:false"> set@ibpdata=(selectvariable_valuefrominformation_schema.global_statuswherevariable_name=&#39;innodb_buffer_pool_pages_data&#39;); ERROR3167(HY000):The&#39;INFORMATION_SCHEMA.GLOBAL_STATUS&#39;featureisdisabled;seethedocumentationfor&#39;show_compatibility_56&#39; #从MySQL5.7.6开始,GLOBAL_STATUS表中提供的信息从PerformanceSchema获取 mysql&gt;set@ibpdata=(selectvariable_valuefromperformance_schema.global_statuswherevariable_name=&#39;innodb_buffer_pool_pages_data&#39;); mysql&gt;select@ibpdata; +----------+ |@ibpdata| +----------+ |568| +----------+ 1rowinset(0.00sec) mysql&gt;set@idbpgsize=(selectvariable_valuefromperformance_schema.global_statuswherevariable_name=&#39;innodb_page_size&#39;); mysql&gt;select@idbpgsize; +------------+ |@idbpgsize| +------------+ |16384| +------------+ 1rowinset(0.00sec) mysql&gt;set@ibpsize=@ibpdata*@idbpgsize/(1024*1024*1024); QueryOK,0rowsaffected(0.00sec) mysql&gt;select@ibpsize; +-----------------+ |@ibpsize| +-----------------+ |0.0086669921875| +-----------------+ 1rowinset(0.00sec)</pre>
RangeTime:0.006416s
RangeMem:215.18 KB
返回顶部 留言