MySQL51性能优化方案.docx
- 文档编号:24332121
- 上传时间:2023-05-26
- 格式:DOCX
- 页数:40
- 大小:547.57KB
MySQL51性能优化方案.docx
《MySQL51性能优化方案.docx》由会员分享,可在线阅读,更多相关《MySQL51性能优化方案.docx(40页珍藏版)》请在冰豆网上搜索。
MySQL51性能优化方案
MySQL5.1性能优化方案
1.平台数据库
1.1.操作系统
RedHatEnterpriseLinuxServerrelease5.4(Tikanga)
ELF32-bitLSBexecutable,Intel80386,version1(SYSV),forGNU/Linux2.6.9,dynamicallylinked(usessharedlibs),forGNU/Linux2.6.9,stripped
32位Linux服务器,单独作为MySQL服务器使用。
1.2.MySQL
系统使用的是MySQL5.1,最新的MySQL5.5较之老版本有了大幅改进。
主要体现在以下几个方面:
1)默认存储引擎更改为InnoDB
InnoDB作为成熟、高效的事务引擎,目前已经广泛使用,但MySQL5.1之前的版本默认引擎均为MyISAM,此次MySQL5.5终于将默认数据库存储引擎改为InnoDB,并且引进了Innodbplugin1.0.7。
此次更新对数据库的好处是显而易见的:
InnoDB的数据恢复时间从过去的一个甚至几个小时,缩短到几分钟(InnoDBplugin1.0.7,InnoDBplugin1.1,恢复时采用红-黑树)。
InnoDBPlugin支持数据压缩存储,节约存储,提高内存命中率,并且支持adaptiveflushcheckpoint,可以在某些场合避免数据库出现突发性能瓶颈。
MultiRollbackSegments:
原来InnoDB只有一个Segment,同时只支持1023的并发。
现已扩充到128个Segments,从而解决了高并发的限制。
2)多核性能提升
MetadataLocking(MDL)Framework替换LOCK_openmutex(lock),使得MySQL5.1及过去版本在多核心处理器上的性能瓶颈得到解决。
3)制功能(Replication)加强
过去的异步复制方式意味着极端情况下的数据风险,MySQL5.5将首次支持半同步(semi-syncreplication)在MySQL的高可用方案中将产生更多更加可靠的方案。
4)增强表分区功能
MySQL5.5的分区更易于使用的增强功能,以及TRUNCATEPARTITION命令都可以为管理和维护数据库节省大量的时间,并且具有更加灵活高效的分区方式。
1.3.CPU
系统所用CPU是单个4核CPU。
对于CPU密集的负载,MySQL通常从更快的CPU中获益,而不是更多CPU。
MySQL5.1的架构对多CPU的扩展性不好,并且MySQL不能在多个CPU上并行地运行某个查询,因此在对于单个CPU进行密集的查询时,CPU速度限制了响应时间。
为了实现低延迟,即快速响应时间,需要快速的CPU,因为单个查询只能使用一个CPU。
值得注意的是,MySQL5.5在多核心处理器上的性能有了很大的提升。
另外,MySQL在64位架构上工作得更好,比32位架构更能有效地使用大量内存。
尽管本系统使用的是32位操作系统,CPU运行在32位模式下,但它仍支持64位计算。
(cat/proc/cpuinfo|grepflags|grep'lm'|wc-l)
1.4.磁盘空间
系统的磁盘空间目前没有压力。
1.5.内存
内存总大小为4G,只供操作系统和数据库使用。
1.6.数据库的表和文件
数据库addb共有339张表:
其中InnoDB表303张,MyISAM表34张,MEMORY表2张。
InnoDB数据文件ibdata1大小为30138MB,一周后ibdata1大小为30234MB,
MyISAM数据文件(包括表结构、索引及数据)总大小约为1642MB,一周后约为1639MB。
可以看出,数据库的数据量较稳定,InnoDB数据文件增加了约106MB,总大小一周内没有大的变化。
MyISAM表中,值得注意的是表terminalalarm_bak,该表总大小约为1623MB,占整个MyISAM表总大小比重近99%。
二进制日志单个文件大小为1GB,二进制日志文件总大小接近20GB。
1.7.数据分布情况
服务器某时间点非精确值:
数据量范围
表数量(总共339张,其中分区表2张)
1000万 4张(MyISAM表1张) 500万 6张 100万 5张 50万 4张 10万 12张(MyISAM表1张) 5万 9张(MyISAM表1张) 1万 23张(MyISAM表2张) 1 136张(MyISAM表9张,MEMORY表2张) rows=0(无数据) 140张 观察系统中数据量很大且未进行表分区的InnoDB表 ●adrotateresultdetail_fail的数据量达到4千万,createTime列是datatime类型,且有索引,意味着存在以该列为查询条件或关联条件查询的需求,因此可以在该列上以自然月份进行表分区。 ●terminalalarm的数据量也突破千万,AlarmTime列是datatime类型,且有索引,意味着存在以该列为查询条件或关联条件查询的需求,因此可以在该列上以自然月份进行表分区。 在事件ev_terminalalarm中会查询该表,若进行表分区,也能一定程度上提高事件的执行效率。 ●terminalalarminfo表仅自增列有索引,主要用于存储数据,可不用分区。 ●Terminallogin表的loginTime列是datatime类型,且有索引,意味着存在以该列为查询条件或关联条件查询的需求,因此可以在该列上以自然月份进行表分区。 ●adplayinfo_bak表存在多个以INT类型为索引的列,根据实际业务情况选择查询频率高且能以范围值来分区的整型列对该表进行分区。 ●adrotateresultdetail的createTime列是datatime类型,且有索引,意味着存在以该列为查询条件或关联条件查询的需求,因此可以在该列上以自然月份进行表分区。 ●upfile_bak表仅自增列有索引,若存在查询或者统计业务则可以createTime列进行分区,若该表没有查询方面业务可不必进行分区。 除去配置参数等属性表,对于数据量大且不断递增的业务数据表,最直接的办法可以按照时间字段进行分区,或是根据查询业务来选择合适的列进行表分区和创建索引,这样能够有效提高存储和查询效率。 1.8.服务器配置参数 记录查询: 普通日志log、慢速日志log_slow_queries MySQL有两种查询日志: 普通日志和慢速日志,它们都会记录查询。 普通日志记录了服务器接收到的每一个查询,也包含了没有被执行的查询,比如因为错误而未被执行的查询,还有一些非查询事件,比如连接和断开连接,普通日志不包含执行时间或其他只有在查询结束之后才能得到的信息。 相反,慢速日志只包含了已经执行过的查询,如果是启动状态,它记录了执行时间超过了特定长度的查询。 两种日志都有助于分析,但是慢速日志更有利找到性能较慢的查询。 一个相关配置是log_queries_not_using_indexes,它使服务器把没有使用索引的查询记录到慢速查询日志中,无论它们执行速度有多快。 尽管打开慢速日志相对于执行慢速查询来说,通常只增加了很少的时间,但是如果没有使用索引的查询非常快,例如从小数据量表中查询,这样就会记录它们可能导致服务器变慢,甚至还会使用大量的磁盘空间,慢速日志也许就会被那些快速高效的查询塞满。 慢查询日志可以用来找到执行时间长的查询,可以用于优化。 慢日志打开后,通过设置long_query_time来配置记录查询超过的指定时间,默认值为10秒,根据系统的负载和性能要求进行设置(SETGLOBALlong_query_time=…)。 检查又长又慢的查询日志会很麻烦,可以使用MySQLdumpslow命令获得日志中显示的查询摘要来处理慢查询日志。 系统两种日志都没有开启,可以在需要的时候打开慢速日志来帮助分析性能较慢的查询。 具体实施参考MySQL手册。 需要注意的是查询在日志中只出现一次并不意味着它是一个不好的查询,也不意味将来也会慢,查询时快是慢有多种原因: 1)表也许被锁定,导致查询处于等待状态; 2)数据或索引也许没有被缓存在内存中; 3)或者正在进行批处理大量的数据,使得磁盘I/O变慢; 4)服务器可能同时在运行其他的查询,影响了当前查询的效率。 因此,只能把慢速查询日志看成调优工作的一部分,可以用它来找到可疑的查询,但需要对它们进行仔细地排查和分析。 ◆启用系统慢速日志,分析查询性能慢的时候可以观察该日志信息。 Qcache_hits Com_select Qcache_inserts 检查是否从查询缓存中受益的最直接办法就是检查缓存命中率。 它是提供缓存提供的查询结果的数量,而不是服务器执行的数量。 当服务器收到select语句的时候,Qcache_hits和Com_select这两个变量会根据查询缓存的情况进行递增。 查询缓存命中率的计算公式: Qcache_hits/(Qcache_hits+Com_select),根据公式计算得出查询缓存命中率为7%。 初看上去该命中率很低,但注意到com_select等于qcache_inserts+qcache_not_cache+权限检查错误的总和,即这个比率中包含了缓存失效的因素,而对于数据变更频繁的系统来说,缓存是及其容易失效的,表的任何时刻的数据插入或更新都会使该表的缓存失效,所以本系统缓存的插入率很低,抛开失效的缓存因素,用如下公式计算缓存命中率: Qcache_hits/(Qcache_hits+Qcache_inserts)=84.87%,该比值要好得多,意味着大部分的查询都命中了缓存,换一种说法就是仍有一小部分查询没有被缓存。 没被缓存和缓存失效是两个概念,分别计数,但都会引起com_select的值增加。 命中率要多少才好,这视情况而定,因为对于每一个查询,不执行它所节约的资源远大于缓存中保存结果以及让查询失效的开销,如果缓存命中代表了开销最大的查询,那么即使很低的命中率也是有好处的。 缓存可能会因为碎片、内存不足或数据改变而失效。 如果已经给缓存分配了足够的内存,并且把Query_cache_min_res_unit调整到了合适的值,那么大部分缓存失效都应该是由数据改变而引起的。 Com_update,Com_delete等的值知道有多少查询修改了数据,也可以通过检查Qcache_lowmen_prunes的值了解有多少查询因为内存不足而失效。 ◆接近85%的命中率可以满足系统要求,如果该命中率持续降低则需要对系统进行性能分析并调整。 系统表数据变更频繁,查询缓存的失效率较高,如果对变更频繁大表的查询频率较高,则使用SQL_NO_CACHE和SQL_CACHE来控制是否需要使用查询缓存。 Query_cache_size 分配给查询的总内存必须是1024的倍数,系统设置为128MB。 在服务器启动的时候,MySQL会为查询缓存一次性分配变量所定义数量的内存。 如果更新了变量,MySQL会立即删除所有缓存的查询,重新把缓存设置为定义的大小,并重新初始化缓存的内存。 Query_cache_type Query_cache_type设置在何场景下使用QueryCache。 系统的查询缓存是开启状态。 _cache_type可以设置为0(OFF),1(ON)或者2(DEMOND),分别表示完全不使用querycache,除显式要求不使用querycache(使用sql_no_cache)之外的所有的select都使用querycache,只有显示要求才使用querycache(使用sql_cache)。 Query_cache_limit 该选项限制了MySQL存储的最大结果为2M,如果查询的结果比这个值大,那么就不会被缓存。 服务器在产生结果的同时进行缓存,它无法预先知道结果是否会超过这一限制。 如果在缓存的过程中发现已经超过了限制,MySQL会自动增加Qcache_not_cached的值,并且丢掉已经缓存过的值。 如果预先判断会有这种情况,可以给查询加上SQL_NO_CHACHE来避免这种开销。 ◆以查询某表(18列)中的5000条结果为例,结果集数据大小约为1.4M,该设置是能满足要求的,保持该值即可。 但如果查询结果数据过万的情况较多的话则应适当增加该值,最大不要超过4M。 Qcache_free_memory 如果缓存由大结果和小结果混合而成,那么就很难找到一个合适的大小,既能避免碎片,也能避免过多的内存分配,但是缓存大结果没有太大的益处,可以通过降低Query_cache_limit的值阻止缓存大结果,它有时有助于在碎片和在缓存中保存结果的开销中得到平衡。 Query_cache_min_res_unit Qcache_free_blocks Qcache_total_blocks Qcache_lowmen_prunes 可以通过检查Qcache_free_blocks的值来观察缓存中碎片的情况,它可以显示缓存中有多少内存块处于空闲状态。 碎片最严重的情况就是在每两个存储了数据的块之间都有一个比最小值稍小的可用块,这样每隔一个存储块就有一个自由块,因此,如果Qcache_free_blocks大致等于Qcache_total_blocks/2,则说明碎片非常严重。 Qcache_lowmem_prunes表示由于缓存内存不足被清除出查询缓存的条数,如果Qcache_lowmem_prunes的值正在增加,并且有大量的自由块,就说明碎片导致查询正被从缓存中永久删除。 查询缓存碎片率=Qcache_free_blocks/Qcache_total_blocks*100% 如果查询缓存碎片率超过20%,可以用FLUSHQUERYCACHE整理缓存碎片,或者试试减小query_cache_min_res_unit,如果你的查询都是小数据量的话。 使用FLUSHQUERYCACHE命令移除碎片,该命令会把所有的存储块向上移动,并把自由块移到底部。 当它运行的时候,会阻止访问查询缓存,这会锁定整个服务器,但它通常会很快,除非缓存特别大。 如果缓存没有碎片,但是命中率却不高,那么就应该给缓存分配较少的内存,如果服务器找不到足够大小的块来存储结果,就应该从缓存中清理掉一些查询,可以使用RESETQUERYCACHE命令从缓存中移除查询。 当服务器清理查询的时候,Qcache_lowmen_prunes值会增加,如果它的值增加得很快,可能有两个原因: 1)如果有很多自由块,就可能是有碎片引起的;2)如果自由块比较少,就可能表示工作负载使用的内存大小超过了所分配的内存,可以检查Qcache_free_memory知道为使用的内存数量。 如果有很多自由块,碎片很少,由于内存不足引起的清理工作也很少,但命中率仍然不高,这说明工作负载也许不能从缓存中受益,一定有什么阻止了查询使用缓存,很多update语句可能会是原因,另一个原因可能是查询是不可缓存的。 查询缓存分配的最小块的大小Query_cache_min_res_unit为4MB。 当查询进行的时候,MySQL把查询结果保存在查询缓存中,但如果要保存的结果比较大,超过query_cache_min_res_unit的值,这时候MySQL会一边检索结果,一边保存结果,所以,有时候并不是把所有结果全部得到后再进行一次性保存,而是每次分配一块query_cache_min_res_unit大小的内存空间保存结果集,使用完后,接着再分配一个这样的块,如果还不够,接着再分配一个块,依此类推,也就是说,有可能在一次查询中,MySQL要进行多次内存分配的操作。 当一块分配的内存没有完全使用时,MySQL会把这块内存截掉,把没有使用的那部分归还以重复利用,当连续操作后剩下的内存大小不足以分配一个内存单元时,内存碎片便产生了。 通常无法避免所有的碎片,但是仔细选择Query_cache_min_res_unit可以避免在查询缓存中造成大量的内存浪费,关键在于每一个新块和服务器已分配给存储结果的块的数量之间找到平衡,如果值过小,服务器将会浪费较少的内存,但会更频繁地分配块,这对服务器意味着更多的工作。 如果值过大,碎片将会很多,合适的折中是在浪费内存和增加处理时间上取得平衡。 ◆空缓存百分比: Qcache_free_blocks/Qcache_total_blocks≈16%,且系统Qcache_free_blocks值较高,有可能是出现碎片了,使用flushquerycache整理查询缓存并消除碎片,该命令不会从缓存中移除任何查询。 同时定期观察内存碎片情况。 Key_buffer_size Key_reads Key_reads_requests 键缓存读命中率: 100-((Key_reads*100)/Key_reads_requests)=99.975 Key_read_requests和Key_reads是两个计数器,Key_read_requests是从缓存读取索引的请求次数,Key_reads是从磁盘读取索引的请求次数。 key_buffer_size指定MyISAM表索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度。 MyISAM键缓存默认只有一个缓冲区,MyISAM自身只缓存了索引,没有数据,它让操作系统缓存数据,它的值应该占到所有保留内存的25%到50%,操作系统缓存用来保存从MYD文件中读取出来的数据。 该变量给键缓冲分配指定大小的空间,但是操作系统只有在实际用到这些空间的时候才会进行分配,也可以创建多个键缓存,如果对于一个非默认大小的键缓存设置为0,MySQL就会把每一个索引从特定的缓存移到默认的缓存中,并且在没有对象使用特定的缓存时就将其删掉,给一个不存在的缓存设置这个变量将会创建缓存,对一个已有的缓存设置非零值将会冲洗缓存,这是一个在线操作,它会阻止所有访问该缓存的动作,直到缓存冲洗完成。 另一个参考指标是单位时间内Key_reads值的变化情况。 ◆系统使用MyISAM表查询频率较低,键缓存读命中率在99%以上,表明键缓存能满足系统的性能要求。 Key_blocks_unused Key_blocks_used 键缓存使用率=Key_blocks_used/(Key_blocks_used+Key_blocks_unused)=37% ◆尽管键缓存使用率较低,说明key_buffer_size设置较高,MySQL没有将其使用完,基于键缓存各方面都能满足系统要求且内存够用,不必调整。 table_cache_size/table_open_cache(5.1.2之后叫做table_open_cache) Open_tables Opened_tables Open_tables表示当前打开的表缓存数,如果执行flushtables操作,则此系统会关闭一些当前没有使用的表缓存而使得此状态值减小;opend_tables表示曾经打开的表缓存数,会一直进行累加,如果执行flushtables操作,值不会减小。 应该将Open_tables的值和table_cache进行对照。 如果每秒有太多Opened_tables,那么说明table_cache还不够大,表缓存没有被完全利用上时,显式的临时表也能导致Opened_tables增加。 table_cache指定表高速缓存的大小。 设置该变量不会立即生效,要等到下一个线程打开表的时候才会生效,当它生效的时候,MySQL会检查变量的值,如果值大于缓存表中的数量,线程就可以把新打开的表插入到缓存中,这样可以更快地访问表内容。 如果值小于缓存表中的数量,MySQL就会从缓存中删除掉没有使用的表。 通过检查峰值时间的状态值Open_tables和Opened_tables,可以决定是否需要增加table_cache的值。 如果发现open_tables等于table_cache,并且opened_tables在不断增长,那么就需要增加table_cache的值了。 ◆Open_tables值与table_cache相等,且观察到Opened_tables较大,应适当增加table_cache,可将其设置为512。 thread_cache_size thread_cache_size是缓存的同时操作的线程数。 线程缓存保存了和当前连接无关的线程,这些线程可以供新连接使用。 当一个新连接被创建出来并且缓存中有一个线程的时候,MySQL会把这个线程从缓存中删除,并且把它赋给连接。 连接关闭时,MySQL会回收线程,把它放回到缓存中。 如果缓存中没空间了,MySQL就会销毁该线程。 只要缓存中有自由的线程,MySQL就能很快地响应连接请求,因为它不需要为每个连接都创建新的线程。 设置该变量不会立即生效,需要等到下一次线程关闭的时候,MySQL会检查缓存中是否有空间存储线程。 如果是,他会把线程缓存起来,供另外一个连接使用,如果不是,它会直接结束线程,这种情况下,缓存中线程的数量,以及线程缓存使用的内存数量不会立即下降。 只有当新连接为了使用线程而把它从缓存中移走的时候才会看到下降。 MySQL只有在连接关闭的时候才会把线程加入缓存,也只有在创建新连接的时候才从缓存中移除线程。 Connections thread_connected threads_created Connections变量表示连接意图的数量,而不是当前接连的数量(threads_connected),如果它的值快速增加,比如每秒几百,就应该检查连接以及操作系统的网络设置。 本系统中该值正常。 thread_cache_size定义了MySQL能在缓存中保存的线程数量,可以通过观察threads_created变量的值,以确定线程缓存是否足够大。 如果Threads_created的值较大或正在增加,可以尝试增加thread_cache_size的值,通过检查Threads_created知道有多少缓存已经在缓存中了。 如果每秒创建的线程数量少于10个,缓存的大小就是足够的。 另外,可以观察thread_connected值的变化来设置线程缓存,本系统中它的值保持在100以下。 大多数情况,非常大的线程缓存是没有必要的,通常需要把线程缓存保持足够大以使threads_created不会经常增加,但是如果它的值非常大,本系统已超过一万就属于非常大了,那么就应该把它设置得小一点,因为操作系统不能很好地处理太多的线程,即使它们处于睡眠状态也不行。 通常情况,据物理内存设置规则如下: 1G内存设为8,2G内存设为16,3G内存设为32,4G或4G以上设为64。 ◆本系统内存为4G,且thread_connected的增幅并不大,thread_cache_size设置为64,不需要更改。 read_buffer_size read_buffer_size是MySQL读入缓冲区大小。 对表进行顺序扫描的请求将分配一个读入缓冲区,MySQL会为它分配一段内存缓冲区。 read_buffer_size变量控制这一缓冲区的大小。 如果对表的顺序扫描请求非常频繁,并且频繁扫描进行得太慢,可以通过增加该变量值以及
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- MySQL51 性能 优化 方案