《数据库的优化与调优：从理论到实践.pptx》由会员分享，可在线阅读，更多相关《数据库的优化与调优：从理论到实践.pptx（26页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、数据库的优化与调优：从理论到实践数据库的优化与调优：从理论到实践网易杭研何登成自我简介何登成网易杭州研究院；工作领域工作领域数据库引擎/分布式数据库/分布式KV技术领域技术领域数据库/分布式/并发编程/性能优化联系方式联系方式微博：何_登成博客：何登成的技术博客Outline数据库性能优化与调优：从理论到实践理论理论篇篇从串行到并行从畅通无阻到排队必须了解的硬件知识实践实践篇篇MySQL各版本的优化MySQL使用中的调优理论篇理论篇理论篇从串行到并行欢迎来到并行世界：Moores Law并行世界，串行的副作用：Amdahls law从畅通无阻到排队并发的世界，充满排队：Queueing The
2、ory必须了解的硬件知识CPU/Cache/Memory/Disk.欢迎来到并行世界摩尔定律(Moores Law)失效了？No，只是转变了形式而已。Herb SutterThe Free Lunch Is Over A Fundamental Turn Toward Concurrency in Software.何谓Free Lunch？通过CPU主频提升，软件能够自动提升性能的时代已经一去不复返；是时候是时候考虑多线程考虑多线程/并并发程序了；发程序了；并行世界，串行的副作用Gene Amdahl怎么说(Amdahls law)？P：程序可并行执行比率1-P：串行比率N：N个Proces
3、sorsS(N)：加速比解读解读：程序的极限性能，程序的极限性能，最终取决于程序串行部分最终取决于程序串行部分所占比率所占比率；尽量消除程序中的串行部分；无限制的并行，您无法达到现实生活中无限制的并行，您无法达到(续)硬件资源瓶颈/软件内部等待CPU/Memory/Disk.Mutex/Spinlock/.排队论排队论(Queueing Theory)资源利用率；响应时间/等待时间；吞吐量；这些指标，均与性能相关；Littles LawL：平均队列长度；Lambda：平均吞吐率；W：平均响应时间；写程序，为什么需要了解硬件？看看他们怎么说？Hardware and software worki
4、ng together in harmony.Martin ThompsonKnow Hardware to Design Better Software.未找到出处，暂时算我说的吧硬件与软件性能息息相关硬件各组件的Latency和Throughput；语言那些与硬件相关的特性；当前的硬件发展到了什么阶段？注：来自Martin Thompson CPU Cache Flushing Fallacy 一文；硬件各组件Latency对比注：来自Gregg Brendan Systems Performance:Enterprise and the Cloud一书；授人以鱼不如授人以渔你应该亲自测试
5、这些硬件性能指标Igor OstrovskyGallery of Processor Cache Effects测试内存Latency；测试Cache Lines大小；测试L1/L2/L3 Cache大小；.ccBench Everything You Always Wanted to Know about Synchronization but Were Afraid to Ask（SOSP 13）小工具，可以测试CPU Cache/Cache Coherence/Atomic Operations性能理论结束，该来点实战了吐槽时间吐槽时间前面说了这么多，真的跟MySQL数据库的优化与调优有
6、关系吗？你不是在忽悠吧？接下来，让我们通过实践篇，来看看前面的这些理论，在MySQL数据库中是如何得到实践的。数据库的优化与调优：实践篇实践篇实践篇MySQL各版本的优化基础优化：串行 并行进阶优化：减少系统中的串行点高级优化：软硬件相辅相成MySQL使用中的调优资源利用率没到100%认识MySQL，构建平衡系统基础优化-拿串行开刀(一)臭名远播的prepare_commit_mutex目的：保持事务在InnoDB存储引擎与MySQL Binlog中提交顺序的一致性；InnoDB Prepare：持有此MutexMySQL Binlog Commit；InnoDB Commit：释放此Mute
7、x事务串行化提交；MySQL Group CommitOracle MySQL Group CommitMariaDB Group CommitHow？基础优化-拿串行开刀(二)你的Slave延迟了多久？MySQL Replication基于Binlog的复制两个线程I/O Thread：负责与Master通讯，接收BinlogSQL Thread：负责Binlog在Slave的回放，单线程Parallel Slave ReplicationOracle MySQL MariaDB Implementation效果(见右图)进阶优化-减少系统串行点(一)kernel_mutexInnoDB引擎
8、内部有很多共享资源，如：内存分配；日志系统；事务与锁表；ReadView.早期InnoDB版本，所有的这些共享资源，都通过一个kernel_mutex来保护kernel_mutex，针对MySQL/InnoDB系统来说，就是一个较大的串行点；一字真言拆拆！Kernel mutex removed事务&ReadView：trx_sys_t:lock 锁表：lock_sys_t:mutex.进阶优化-减少系统串行点(二)Index Tree LockInnoDB每一个Index，有一把RWLock(读写锁)；读/写，加S锁；索引页面分裂/合并，加X锁；索引页面分裂时，整个索引树不可访问 二字真言算
9、法算法！C.Mohan,Aries/IMSMO操作，只跟SMO本身冲突，不会堵塞用户的读写；高级优化-软硬件相辅相成(一)高级CPU指令SIMD SSE 4Single-Instruction-Multiple-Data合适的应用场景InnoDB页面计算Checksum；硬件指令加速；高级优化-软硬件相辅相成(二)Cache Coherence&False SharingCache Coherence多线程并发读写同一内存；False Sharing64 Bytes Cache LineG5 Patch问题起因srv_n_rows_read+Per-Thread Counter减少Cache
10、CoherenceCounter Padding消除False Sharingib_counter_t:m_counter(N+1)*(CACHE_LINE_SIZE)/sizeof(Type)高级优化-软硬件相辅相成(三)Compile OptimizationCPU执行L1 ICache高速指令缓存减少ICache MissPerf：定位Cache MissPGO：优化编译优化效果ICache Miss：10%to 8%Performance：44%提升MySQL调优基础mysql变量/参数大集合mysqld Option/Variable Reference必须掌握的调优参数innodb
11、_flush_log_at_trx_commit&sync_binlog在BGC之前，调整这两个参数，能极大降低磁盘的I/O请求(减少排队)，降低请求响应时间，提升TPS；innodb_log_file_size&innodb_log_files_in_group控制日志文件大小。在写入频繁的系统下，小日志文件更易引发系统的同步刷脏页(引入新的串行点)，TPS急剧下降；innodb_io_capacity&innodb_read_io_threads&innodb_write_io_threads&innodb_purge_threads根据硬件配置以及系统的读写请求，恰到好处的配置这些参数，
12、能够最大限度的发挥磁盘的性能，同时保证系统较好的响应时间；建议将建议将MySQL所有的参数都予以掌握，并根据实际情况，调整参数获取最佳性能；所有的参数都予以掌握，并根据实际情况，调整参数获取最佳性能；MySQL调优-关注资源利用率一个数据库系统，其硬件资源(CPU、Disk、SSD.)利用率是不是越高，说明系统调优的越好？用户关注响应时间响应时间(Latency)老板关注资源有没有浪费？资源有没有浪费？调优关注保证用户响应时间的基础上，最大限度提高资源利用率；Finding The KneeMySQL调优-认识MySQL，构建平衡系统认识MySQLMySQL能支持多少并发？消耗多少CPU？指导采购不同的硬件配件；构建平衡系统(Balanced Systems)Balanced Systems硬件有着基本相同的资源利用率吗？每个硬件的利用率都处于拐点吗？指导数据库服务器选型；合适的CPU、磁盘、网络、SSD组合；MySQL调优-终极调优面向程序员的数据库访问性能优化法则写在最后的一句话数据库层面做再大的性能优化，都抵不上应用层的数据库层面做再大的性能优化，都抵不上应用层的优化。同样优化。同样是是MySQL，既可以用，既可以用来支撑来支撑Google/FaceBook/Taobao应用，也可能连你的个人网站都应用，也可能连你的个人网站都撑不住；撑不住；Q&A