MySQL 5.6 全方位效能调优 中文

Copyright©2013,Oracleand/oritsaffiliates.Allrightsreserved.InsertInformationProtectionPolicyClassificationfromSlide1212MySQL5.6PerformanceTuning杜修文[e-mail]nsultantCopyright©2013,Oracleand/oritsaffiliates.Allrightsreserved.编号以下资讯用于说明我们产品一般性的方向.其目的只限于提供参考,不可成为任何合约的内容或附件.也不会当成交付任何材料，代码，或功能的承诺,且不可当成采购决策的依据.Oracle保有于其中提到的任何Oracle产品的开发，发行和推出时间决定权利.Copyright2012OracleCorporation3免责声明Copyright©2013,Oracleand/oritsaffiliates.Allrightsreserved.编号议程基础平台:硬件,存储引擎和版本服务器的调优索引,查询和架构优化MySQLPerformanceSchema简介MySQLEnterpriseMonitor和QueryAnalyzerCopyright©2013,Oracleand/oritsaffiliates.Allrightsreserved.编号设置基础效能调优课程–4天●本课程–45分钟精华的技术文本链结社区或企业版●MySQLEnterpriseMonitor除外Copyright©2013,Oracleand/oritsaffiliates.Allrightsreserved.编号6硬件的选择最多可达64个CPU内核(MySQL5.6以上的版本)内存Linux,Solaris,Windows硬盘●快速的硬盘(一万到一万五千转SAS)●RAID10,有电池支持的写快取(RAID控制器)●SSD(支持较高的吞吐)--MySQL5.6有冗余的网络和电源主从服务器都一样Copyright©2013,Oracleand/oritsaffiliates.Allrightsreserved.编号MySQL存储引擎Copyright©2013,Oracleand/oritsaffiliates.Allrightsreserved.编号MySQL引擎调优的决策PluggableStorageEnginesMemory,IndexandStorageManagementInnoDBMyISAMNDBCopyright©2013,Oracleand/oritsaffiliates.Allrightsreserved.编号InnoDB事务性具完全符合ACID的要求●崩溃回复●多版并行控制(MVCC)●行级锁数据和索引可存于内存于5.6,InnoDB提供●稳定的读的效能●全文检索索引●改善表分区以加强导入速度Copyright©2013,Oracleand/oritsaffiliates.Allrightsreserved.编号MyISAMMyISAM传统用例：●高速的读●非事务性或不需崩溃回复●表级锁●支持空间资讯(RTREE索引)Copyright©2013,Oracleand/oritsaffiliates.Allrightsreserved.编号MySQL版本Copyright©2013,Oracleand/oritsaffiliates.Allrightsreserved.编号MySQL数据库架构效能,可靠，易用支援许多开发环境和语言高效能的多线程连线处理全DML,DDL解析，成本为基础的优化器，查询和其结果集的快取依表弹性指定储存引擎，应应用系统的特性选择储存引擎弹性的日志和实体存储的选择Copyright©2013,Oracleand/oritsaffiliates.Allrightsreserved.编号13MySQL版本–调优的决策2008200920102012MySQL5.0可达4个内核(SunMicro时代)MySQL5.1(InnoDBPlugin)可达16个内核(Oracle时代)MySQL5.5可达32个内核MySQL5.6可达48个内核以上Copyright©2013,Oracleand/oritsaffiliates.Allrightsreserved.编号14MySQL5.6:扩充性●用户可充份发挥新一代硬件和操作系统的效能●随著数据量和用户数增加时容量也增加Copyright©2013,Oracleand/oritsaffiliates.Allrightsreserved.编号服务器的调优Copyright©2013,Oracleand/oritsaffiliates.Allrightsreserved.编号16调优的原则绝不先在生产环境调整先要有好的基准则试或可靠的工作负荷以一个好的基准线开始一次只调整一个事情Copyright©2013,Oracleand/oritsaffiliates.Allrightsreserved.编号调优的原则-续监看结果●查询效能-queryanalyzer,slowquerylog,等.●吞吐量●单一查询时间●平均查询时间●CPU-top,vmstat●IO-iostat,top,vmstat,bonnie++记录和存结果Copyright©2013,Oracleand/oritsaffiliates.Allrightsreserved.编号基准测试自行测试–可用generalquerylog的产出–JMeter,LoadRunner,VisualStudiomysqlslapsupersmackmybenchSysBenchDBT2Copyright©2013,Oracleand/oritsaffiliates.Allrightsreserved.编号MySQL变数系统:●my.cnf/my.ini●某些是动态●某些是Session/全域STATUS:●Session/GlobalSYSTEMVARIABLESSTATUSVARIABLESdatadiraborted_clientsgeneral-logconnectionsinnodb_buffer_pool_sizecreated_tmp_disk_tablesmax_connectionsthreads_createdportuptime……Copyright©2013,Oracleand/oritsaffiliates.Allrightsreserved.编号MySQL状态调整:系统变数监看:StatusVariablesSHOW[GLOBAL|SESSION]STATUS●mysqlSHOWglobalstatuslike‘max_used_connections’“观查”boxidentifiesstatusvariables状态变数WATCHmax_used_connectionsCopyright©2013,Oracleand/oritsaffiliates.Allrightsreserved.编号默认及配置的文档5.6●更新默认以配合现代系●自动调整量的变数5.6以前–过时的配置样本文档–例:my-innodb-heavy-4G.cnf建议:●考虑默认的5.6配置档●重新评估较旧的配置档内容Copyright©2013,Oracleand/oritsaffiliates.Allrightsreserved.编号22InnoDB的调优innodb_buffer_pool_size●可用内存的80%●mysqlshowstatuslike'Innodb_buffer%';innodb_log_file_size=~512MB5.5+●回复时间相对于效能●高速的写WATCHInnodb_buffer_pool_readsInnodb_buffer_pool_read_requestsCopyright©2013,Oracleand/oritsaffiliates.Allrightsreserved.编号InnoDB的调优-下阶段innodb_flush_log_at_trx_commit(小心使用)●1在每次提交时同步到文档(fsync)●0/2可能损件一到二秒的数据innodb_flush_method=O_Direct●依据工作负荷和硬件innodb_buffer_pool_instances=8●只在5.5和5.6依据您的工作负荷Copyright©2013,Oracleand/oritsaffiliates.Allrightsreserved.编号MyISAM的调优快取●key_buffer_cache–可用内存的25%●SystemCache–可用内存的75%多个KeyBuffers预先载入KeyBuffers详情请参阅:WATCHKey_read_requestsKey_readsKey_buffer_sizeCopyright©2013,Oracleand/oritsaffiliates.Allrightsreserved.编号通用的系統變數table_open_cache●5.6默认改为自400到-1(autosized)thread_cache_size●目标Threads_created~thread_cache_size共通的趨勢WATCH%opened%%thread%Threads_createdCopyright©2013,Oracleand/oritsaffiliates.Allrightsreserved.编号只用于当-●特定的查询和数据●很少Inserts/Updates/Deletes查询快取和结果集●0或OFF●1或ON快取所有的除非SELECTSQL_NO_CACHE●2或DEMAND不会快取除非SELECTSQL_CACHEWATCHqcache_hitsqcache_insertsqcache_not_cachedqcache_total_blocksqcache_free_memory通用服务器系统变数查询快取Copyright©2013,Oracleand/oritsaffiliates.Allrightsreserved.编号通用服务器系统变数tmp_table_size●在“inmemory”表中占用最多的量●Memoryvs.MyISAM(ondisk)如果临时表●tmp_table_size或max_heap_table_size或●BLOB/TEXT将MyISAM表至硬盘上临时表–小心使用内存WATCHcreated_tmp_tablescreated_tmp_disk_tablesCopyright©2013,Oracleand/oritsaffiliates.Allrightsreserved.编号系统变数-小心使用soft_buffer_size●在groupby和orderby时用于排序●如果100M代表每个排序用100M的内存●在实验环境中混搭使用●2M-256Kin5.6建议●用默认或全然用测试●动态配置依工作负荷况查询更大不一定是更好每个线程况JOIN用份内存WATCH%opened%%thread%Threads_createdCopyright©2013,Oracleand/oritsaffiliates.Allrightsreserved.编号系统变数-小心使用-续join_buffer_size●在不使用索引的joins时●每个线程每个join最小分minimumallocatedperjoinperthread建议●用默认值●设为动态配置●基准测试●调整查询依工作负