Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
针对ISV和OEM实现产品的
MySQL5.6性能优化
杜修文Ivan.Tu@Oracle.Com
MySQLPrincipalSalesConsultant
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
议程
?基础平台:硬件,存储引擎和版本
?服务器的调优
?索引,查询和架构优化
?MySQLPerformanceSchema简介
?MySQLEnterpriseMonitor和QueryAnalyzer
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
设置基础
?效能调优课程–4天
本课程–45分钟
?精华的技术
?文本链结
?社区或企业版
MySQLEnterpriseMonitor除外
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
硬件的选择
?最多可达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引擎
调优的决策
PluggableStorageEngines
Memory,IndexandStorageManagement
InnoDBMyISAMNDB
Copyright?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.编号
lMySQL数据库架构
l效能,可靠，易用
l支援许多开发环境和语言
l高效能的多线程连线
处理
l全DML,DDL解析，
成本为基础的优化器
，查询和其结果集的
快取
l依表弹性指定储存引擎
，应应用系统的特性选择
储存引擎
l弹性的日志和实体存储的选择
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
MySQL版本–调优的决策
2008200920102012
MySQL5.0
可达4个内核
(SunMicro时代)
MySQL5.1
(InnoDBPlugin)
可达16个内核
(Oracle时代)
MySQL5.5
可达32个内核
MySQL5.6
可达48个内核以上
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
MySQL5.6:扩充性
用户可充份发挥新一代硬件和操作系统的效能
随著数据量和用户数增加时容量也增加
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
服务器的调优
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
调优的原则
?绝不先在生产环境调整
?先要有好的基准则试或可靠的工作负荷
?以一个好的基准线开始
?一次只调整一个事情
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
?DBT2
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
MySQL变数
?系统:
my.cnf/my.ini
某些是动态
某些是Session/全域
?STATUS:
Session/Global
SYSTEMVARIABLESSTATUSVARIABLES
datadiraborted_clients
general-logconnections
innodb_buffer_pool_sizecreated_tmp_disk_tables
max_connectionsthreads_created
portuptime
……
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
MySQL状态
?调整:系统变数
?监看:StatusVariables
?SHOW[GLOBAL|SESSION]STATUS
mysql>SHOWglobalstatuslike‘max_used_connections’
?“观查”boxidentifiesstatusvariables
状态变数WATCH
?max_used_connections
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
默认及配置的文档
?5.6
更新默认以配合现代系
自动调整量的变数
?5.6以前
–过时的配置样本文档
–例:my-innodb-heavy-4G.cnf
?建议:
考虑默认的5.6配置档
重新评估较旧的配置档内容
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
InnoDB的调优
?innodb_buffer_pool_size
可用内存的80%
mysql>showstatuslike''Innodb_buffer%'';
?innodb_log_file_size=~512MB5.5+
回复时间相对于效能
高速的写
WATCH
?Innodb_buffer_pool_reads
?Innodb_buffer_pool_read_requests
Copyright?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_size
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
通用的系統變數
?table_open_cache
5.6默认改为自400到-1(autosized)
?thread_cache_size
目标Threads_created~thread_cache_size
共通的趨勢WATCH
?%opened%
?%thread%
?Threads_created
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
?只用于当-
特定的查询和数据
很少Inserts/Updates/Deletes
?查询快取和结果集
0或OFF
1或ON快取所有的除非SELECTSQL_NO_CACHE
2或DEMAND不会快取除非SELECTSQL_CACHE
WATCH
?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_tables
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
系统变数-小心使用
?soft_buffer_size
在groupby和orderby时用于排序
如果100M代表每个排序用100M的内存
在实验环境中混搭使用
2M->256Kin5.6
?建议
用默认或全然用测试
动态配置
依工作负荷况查询
更大不一定是更好
每个线程况JOIN用份内存
WATCH
?%opened%
?%thread%
?Threads_created
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
系统变数-小心使用-续
?join_buffer_size
在不使用索引的joins时
每个线程每个join最小分minimumallocatedperjoinperthread
?建议
用默认值
设为动态配置
基准测试
调整查询
依工作负荷况查询
更大不一定是更好
每个线程况JOIN用份内存
WATCH
?Select_full_join
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
服务器调优的结论
一定要调的:
InnoDBBufferPool
KeyBufferCache(MyISAM)
在评估之后调整的:
innodb_log_file_size
innodb_flush_log_at_trx_commit
innodb_flush_method
innodb_buffer_pool_instances(5.5,5.6+)
table_open_cache
thread_cache_size
querycache(turnoff?)
tmp_table_size(persession)
要小心调整的:
sort_buffer_size
join_buffer_size
read_buffer_size(MyISAM)
read_rnd_buffer_size
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
總結–5.6默認值
一定要調整的:
InnoDBBufferPool
KeyBufferCache(MyISAM)
評估再調整:
innodb_log_file_size5M->48M
innodb_flush_log_at_trx_commit
innodb_flush_method
innodb_buffer_pool_instances1->8
table_open_cache400->2000
thread_cache_size0->8+max_con/100
querycache
tmp_table_size
調整要小心的:
sort_buffer_size2MB->256K
join_buffer_size128K->256K
read_buffer_size(MyISAM)
read_rnd_buffer_size
需要調整的較少5.5->5.6
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
索引,查询和数据库架构
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
InnoDB和MyISAM索引的比对
?InnoDB是“Clustered”索引
主键和数据在一起
次键接在主键之后
数据自主键取出
?MyISAM
主键指向实际数据
次键指向实际数据
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
这意味著
?InnoDB
以主键查找和区域扫瞄很快
要指定一个主键
维持小的主键
Auto-Increment
CoveringIndex(查询所要的数据全在索引中)
?MyISAM
CoveringIndex
SELECTfname,lnameFROMcustomerWHERElname=‘Jones’;
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
MySQL中的索引
?加速取出，筛选和排序
?多字段
CREATEINDEXIDXONTAB1(col1,col2,col3);
?字首索引:
CHAR,VARCHAR,BINARY,andVARBINARY
CREATEINDEXPREONTAB1(COL1(10));
字首是以字节为单位，非字符
面对大字串时非常有用
对字段前面能选择时很有用
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
索引-筛选器
?SELECTFROMtableWHEREcity=‘MIAMI’
?如果没有ORDERBY数据以索引的顺序传回
?不会用索引的场景
行数太少
行数>表的10%
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
索引-Join
?索引加速joins
SELECTX.A,Y.BFROMX,Y
WHEREX.C=‘FL’andY.A=X.A;
?筛选器加在表X的字段C
表X需要一个加在字段C的索引当作筛选器
?表Y以其字段Ajoin到表X
表Y需要一个加在字段A的索引
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
索引-排序
?SELECTnameFROMtableORDERBYname
Yes
?SELECTnameFROMtableWHEREnameBETWEEN
Yes
‘AAA’and‘CCC’ORDERBYname;
?SELECTnameFROMtableWHEREcity=‘MIAMI’
No
ORDERBYname;
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
索引的最佳实务
?避免不必要的索引
mysql>SHOWCREATETABLEtablename
?避免重复
indexkey123(col1,col2,col3)
indexkey12(col1,col2)<-Notneeded!
indexkey1(col1)<--Notneeded!
?索引应限制在16bytes/chars以下
?面临大的字段或URL时
分隔字段或以MD5建立哈希键字段
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
数据架构
?越小越好
不要VARCHAR就默认成255
在临时表及快取会扩张为全尺寸
?用VARCHAR取代BLOB
用MEMORY引擎forGROUPBYandORDERBY
?PROCEDUREANALYSE()
?InnoDB主键
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
ProcedureAnalyse的例子
mysql>selectc.nameCityName,y.nameCountryNamefromCityc,Countryy
whereCountryCode=''TWN''andc.CountryCode=y.codeorderbyc.nameprocedureanalyse(5,1000)\G
1.row
Field_name:world.c.Name
Min_value:Changhwa
Max_value:Yungkang
Min_length:4
Max_length:17
Empties_or_zeros:0
Nulls:0
Avg_value_or_avg_length:7.1905
Std:NULL
Optimal_fieldtype:ENUM(''Changhwa'',''Chiayi'',''Chungho'',
''Chungli'',''Fengshan'',''Fengyuan'',''Hsichuh'',
''Hsinchu'',''Hsinchuang'',''Hsintien'',''Hualien'',''Ilan'',''Kangshan'',
''Kaohsiung'',''Keelung(Chilung)'',''Kueishan'',''Luchou'',
''Lungtan'',''Miaoli'',''Nantou'',''Panchiao'',''Pate'',''Pingchen'',
''Pingtung'',''Sanchung'',''Shulin'',''Taichung'',''Tainan'',
''Taipei'',''Taiping'',''Taitung'',''Tali'',''Taliao'',''Tanshui'',''Taoyuan'',
''Touliu'',''Tsaotun'',''Tucheng'',''Yangmei'',''Yuanlin'',''Yungho'',''Yungkang'')NOTNULL
2.row
Field_name:world.y.Name
Min_value:Taiwan
Max_value:Taiwan
Min_length:6
Max_length:6
Empties_or_zeros:0
Nulls:0
Avg_value_or_avg_length:6.0000
Std:NULL
Optimal_fieldtype:ENUM(''Taiwan'')NOTNULL
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
查询命令
?在MySQL中用IN子句是非常快的!
Select...WhereidxIN(1,23,345,456)
?字段名要放在条件的左边
Select...Wherefunc(idx)=20[indexignored]
Select..Whereidx=otherfunc(20)[mayuseindex]
?当对索引的字段用LIKE避免在开始处用%
Select...WhereidxLIKE(‘ABC%’)会用索引
Select...WhereidxLIKE(‘%XYZ’)一定用全表扫瞄
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
查询命令-续
?开启SlowQueryLog
用:log_queries_not_using_indexes
?用mysqldumpslow:
?
WATCH
?select_scan(fulltablescan)
?select_full_join(joinsw/oIndexes)
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
ExplainPlan可协助调优
?表取用的順序
?使用的索引
?自每個表取用的行數之估計值
EXPLAINSELECTFROM…
EXPLAINFORMAT=JSONSELECTFROM…
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
ExplainPlan
?Cost:23941451=990,655
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
Explain–Workbench和JSON(M)Query>VisualExplain
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
加上索引
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
优化后?Theoriginalcostwas23941451=
990,655
?Thenewcostis111=1
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
Type字段
Access或JoinTypes
Positive
?eqref–uniquekey/primarytoreference
value
?const,system–turnpartofqueryinto
constant
?Null–tableorindexnotevenaccessed
?ref–matchsinglevalue,non-uniqueindex,
ref_or_null=possibleextrastep
?range–WHERE..BETWEEN,>
PossibleIssue
?ALLtablescan(dependsontablesize)
?INDEX(unless“usingIndexinEXTRA
column”
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
Extra字段
Positive
?UsingIndex
?Usingindexforgroupby
PossibleIssue
?Usingtemporary
?Usingfilesort
?UsingWhere
Good–UsingIndex
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
MySQLPerformance
Schema
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
PerformanceSchema-配置
?開啟/關閉PerformanceSchema
在my.cnf中加上：
[mysqld]
performance_schema=on
?開啟個別的偵測工具：
Withinmy.cnfadd:
[mysqld]
--performance_schema_instrument=''wait/synch/cond/%=counted’
off/false/0=Disabled
on/true/1=Enabled&Timed
counted=Enabled&Counted,ratherthanTimed
http://dev.mysql.com/doc/refman/5.6/en/performance-schema.html
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
最常见的查询命令
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
最近10个命令
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
依FileI/O排序的文档
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
用到临时表的命令
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
MySQLEnterpriseMonitor
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
全局的调优顾问
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
自动化的规则
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
特别的调优建议
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
自图关联到查询命令
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
查询分析器
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
Repository
Enterprise
Dashboard
ServiceManager
Agent
EnterpriseMonitor的架构
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
结论
基础平台:硬件,存储引擎和版本
服务器的调优
索引,查询和架构优化
MySQLPerformanceSchema简介
MySQLEnterpriseMonitor和QueryAnalyzer
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
资源
?MySQL培训课程–MySQLPerformanceTuning
?效能调优的网路研讨会
?MySQL效能论坛
?下载MySQL5.6
?试用MySQLEnterpriseMonitor:
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
Copyright?2013,Oracleand/oritsaffiliates.Allrightsreserved.编号
|
|
