数据库的优化与调优：从理论到实践网易杭研——何登成自我简介何登成网易——杭州研究院；工作领域数据库引擎/分布式数据库/分布式KV技术领域数据
库/分布式/并发编程/性能优化联系方式微博：http://weibo.com/u/2216172320何http://weibo.
com/u/2216172320_http://weibo.com/u/2216172320登成博客：http://hedengc
heng.com/何登成的技术博客Outline数据库性能优化与调优：从理论到实践理论篇从串行到并行从畅通无阻到排队必须了解的硬件
知识实践篇MySQL各版本的优化MySQL使用中的调优理论篇理论篇从串行到并行欢迎来到并行世界： Moore''sLaw并行世界
，串行的副作用： Amdahl''slaw从畅通无阻到排队并发的世界，充满排队： QueueingTheory必须了解的硬件知
识CPU/Cache/Memory/Disk...欢迎来到并行世界摩尔定律(Moore''sLaw)失效了？No，只是转变了形式
而已。HerbSutterhttp://www.gotw.ca/publications/concurrency-ddj.htm
TheFreeLunchIsOver–AFundamentalTurnTowardConcurrencyin
Software.何谓FreeLunch？通过CPU主频提升，软件能够自动提升性能的时代已经一去不复返；是时候考虑多线程/并发
程序了；并行世界，串行的副作用GeneAmdahl怎么说(http://en.wikipedia.org/wiki/Amdahl
''s_lawAmdahl''slaw)？P：程序可并行执行比率1-P：串行比率N：N个ProcessorsS(N)：加速比解读：程
序的极限性能，最终取决于程序串行部分所占比率；尽量消除程序中的串行部分；无限制的并行，您无法达到现实生活中无限制的并行，您无法达到
(续)硬件资源瓶颈/软件内部等待CPU/Memory/Disk...Mutex/Spinlock/...排队论(http://en
.wikipedia.org/wiki/Queueing_theoryQueueingTheory)资源利用率；响应时间/等待时
间；吞吐量；这些指标，均与性能相关；http://en.wikipedia.org/wiki/Little''s_lawLittle
’sLawL：平均队列长度；Lambda：平均吞吐率；W：平均响应时间；写程序，为什么需要了解硬件？看看他们怎么说？Hardwa
reandsoftwareworkingtogetherinharmony.http://mechanical-sy
mpathy.blogspot.com/Martinhttp://mechanical-sympathy.blogspot.co
m/ThompsonKnowHardwaretoDesignBetterSoftware.未找到出处，暂时算我说的吧?硬
件与软件性能息息相关硬件各组件的Latency和Throughput；语言那些与硬件相关的特性；当前的硬件发展到了什么阶段？注：来
自MartinThompsonhttp://mechanical-sympathy.blogspot.com/2013/02/
cpu-cache-flushing-fallacy.htmlCPUCacheFlushinghttp://mechanic
al-sympathy.blogspot.com/2013/02/cpu-cache-flushing-fallacy.htmlF
allacy一文；硬件各组件Latency对比注：来自GreggBrendan《SystemsPerformance:E
nterpriseandtheCloud》一书；授人以鱼不如授人以渔你应该亲自测试这些硬件性能指标IgorOstrovsk
y——http://igoro.com/archive/gallery-of-processor-cache-effects/Ga
lleryofProcessorCacheEffects测试内存Latency；测试CacheLines大小；测试L1/
L2/L3Cache大小；...https://github.com/trigonak/ccbenchccBench——ht
tp://sigops.org/sosp/sosp13/papers/p33-david.pdfEverythingYouAl
waysWantedtoKnowabouthttp://sigops.org/sosp/sosp13/papers/p3
3-david.pdfSynchronizationhttp://sigops.org/sosp/sosp13/papers/p
33-david.pdfbutWereAfraidtohttp://sigops.org/sosp/sosp13/pape
rs/p33-david.pdfAsk（SOSP’13）小工具，可以测试CPUCache/CacheCoherence/A
tomicOperations性能理论结束，该来点实战了吐槽时间前面说了这么多，真的跟MySQL数据库的优化与调优有关系吗？你不
是在忽悠吧？接下来，让我们通过实践篇，来看看前面的这些理论，在MySQL数据库中是如何得到实践的。数据库的优化与调优：实践篇实践篇
MySQL各版本的优化基础优化：串行?并行进阶优化：减少系统中的串行点高级优化：软硬件相辅相成MySQL使用中的调优资源利用率
没到100%认识MySQL，构建平衡系统基础优化-拿串行开刀(一)臭名远播的http://mysqlmusings.blogspo
t.com/2012/06/binary-log-group-commit-in-mysql-56.htmlprepare_com
mit_mutex目的：保持事务在InnoDB存储引擎与MySQLBinlog中提交顺序的一致性；InnoDBPrepare：
持有此MutexMySQLBinlogCommit；InnoDBCommit：释放此Mutex事务串行化提交；MySQLG
roupCommithttp://mysqlmusings.blogspot.com/2012/06/binary-log-gr
oup-commit-in-mysql-56.htmlOracleMySQLGroupCommitStartingwith
MySQL5.6http://hedengcheng.com/?p=112MariaDBGroupCommithttps:
//mariadb.com/kb/en/group-commit-for-the-binary-log/Startingwith
MariaDB5.3How？基础优化-拿串行开刀(二)你的Slave延迟了多久？MySQLReplication基于Binl
og的复制两个线程I/OThread： 负责与Master通讯，接收BinlogSQLThread： 负责Binlog在Sla
ve的回放，单线程ParallelSlaveReplicationOracleMySQLhttp://dev.mysql.
com/doc/refman/5.7/en/replication-options-slave.html#sysvar_slave
_parallel_typeStartingwithMySQL5.7.2https://mariadb.atlassian.
net/browse/MDEV-4506MariaDBImplementationhttps://mariadb.com/kb/
en/parallel-replication/StartingwithMariaDB10.0.5效果(见右图)进阶优化-减
少系统串行点(一)http://mysqlha.blogspot.com/2011/02/so-long-kernel-mutex
.htmlkernel_mutexInnoDB引擎内部有很多共享资源，如：内存分配；日志系统；事务与锁表；ReadView...早
期InnoDB版本，所有的这些共享资源，都通过一个kernel_mutex来保护?kernel_mutex，针对MySQL/Inn
oDB系统来说，就是一个较大的串行点；一字真言拆！！！https://blogs.oracle.com/mysqlinnodb/e
ntry/mysql_5_6_innodb_scalabilityKernelhttps://blogs.oracle.com/
mysqlinnodb/entry/mysql_5_6_innodb_scalabilitymutexhttps://blogs.
oracle.com/mysqlinnodb/entry/mysql_5_6_innodb_scalabilityremoved
事务&ReadView： trx_sys_t::lock锁表： lock_sys_t::mutex...进阶优化-减少系统
串行点(二)IndexTreeLockInnoDB每一个Index，有一把RWLock(读写锁)；读/写，加S锁；索引页面分裂
/合并，加X锁；索引页面分裂时，整个索引树不可访问?二字真言算法！！C.Mohan,http://www.ics.uci.e
du/~cs223/papers/p371-mohan.pdfAries/IMSMO操作，只跟SMO本身冲突，不会堵塞用户的读写；
高级优化-软硬件相辅相成(一)高级CPU指令http://sc.tamu.edu/systems/eos/nehalem.pdfS
IMDhttp://en.wikipedia.org/wiki/SSE4SSE4Single-Instruction-Mul
tiple-Data合适的应用场景InnoDB页面计算Checksum；硬件指令加速；高级优化-软硬件相辅相成(二)https:/
/class.stanford.edu/c4x/Engineering/CS316/asset/A_Primer_on_Memor
y_Consistency_and_Coherence.pdfCacheCoherence&FalseSharingCac
heCoherence多线程并发读写同一内存；FalseSharing64BytesCacheLinehttp://mi
kaelronstrom.blogspot.com/2012/04/mysql-team-increases-scalabilit
y-by-50.htmlG5Patch问题起因srv_n_rows_read++Per-ThreadCounter减少Cach
eCoherenceCounterPadding消除FalseSharingib_counter_t::m_counter[
(N+1)(CACHE_LINE_SIZE)/sizeof(Type)]高级优化-软硬件相辅相成(三)Compile
OptimizationCPU执行L1ICache高速指令缓存http://kristiannielsen.livejourn
al.com/17676.html减少http://kristiannielsen.livejournal.com/17676.h
tmlICachehttp://kristiannielsen.livejournal.com/17676.htmlMissht
tps://en.wikipedia.org/wiki/Perf_(Linux)Perf：定位CacheMisshttps://
en.wikipedia.org/wiki/Profile-guided_optimizationPGO：优化编译优化效果ICac
heMiss： 10%to8%Performance： 44%提升MySQL调优—基础mysql变量/参数大集合http:/
/dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-ref
erence-optvar.htmlmysqldOption/VariableReference必须掌握的调优参数http:/
/dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_in
nodb_flush_log_at_trx_commitinnodb_flush_log_at_trx_commit&http
://dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log
.html#sysvar_sync_binlogsync_binlog在BGC之前，调整这两个参数，能极大降低磁盘的I/O请求(减
少排队)，降低请求响应时间，提升TPS；http://dev.mysql.com/doc/refman/5.5/en/innodb
-parameters.html#sysvar_innodb_log_file_sizeinnodb_log_file_size
&innodb_log_files_in_group控制日志文件大小。在写入频繁的系统下，小日志文件更易引发系统的同步刷脏页(引
入新的串行点)，TPS急剧下降；http://dev.mysql.com/doc/refman/5.5/en/innodb-par
ameters.html#sysvar_innodb_io_capacityinnodb_io_capacity&http:/
/dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_in
nodb_read_io_threadsinnodb_read_io_threads&http://dev.mysql.com
/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_write_io_
threadsinnodb_write_io_threads&http://dev.mysql.com/doc/refman/
5.5/en/innodb-parameters.html#sysvar_innodb_purge_threadsinnodb_p
urge_threads根据硬件配置以及系统的读写请求，恰到好处的配置这些参数，能够最大限度的发挥磁盘的性能，同时保证系统较好的响
应时间；建议将MySQL所有的参数都予以掌握，并根据实际情况，调整参数获取最佳性能；MySQL调优-关注资源利用率一个数据库系统，
其硬件资源(CPU、Disk、SSD...)利用率是不是越高，说明系统调优的越好？用户关注响应时间(Latency)老板关注资源有
没有浪费？调优关注保证用户响应时间的基础上，最大限度提高资源利用率；https://s3.amazonaws.com/rmc_docs/biforum2013_slides/millsap_performance.pdfFindingTheKneeMySQL调优-认识MySQL，构建平衡系统认识MySQLMySQL能支持多少并发？消耗多少CPU？指导采购不同的硬件配件；构建平衡系统(BalancedSystems)BalancedSystems硬件有着基本相同的资源利用率吗？每个硬件的利用率都处于拐点吗？指导数据库服务器选型；合适的CPU、磁盘、网络、SSD组合；MySQL调优-终极调优http://blog.csdn.net/yzsind/article/details/6059209面向程序员的数据库访问性能优化法则写在最后的一句话数据库层面做再大的性能优化，都抵不上应用层的优化。同样是MySQL，既可以用来支撑Google/FaceBook/Taobao应用，也可能连你的个人网站都撑不住；Q&A |
|
