MySQL性能优化与数据备份.ppt

《MySQL性能优化与数据备份.ppt》由会员分享，可在线阅读，更多相关《MySQL性能优化与数据备份.ppt（54页珍藏版）》请在三一办公上搜索。

1、MySQL优化实践,MySQL性能优化知识分享,Agenda,内容提要MySQL常见工具应用MySQL优化MySQL架构MySQL备份,Summary,用于员工培训和分享，主要针对有一定开发经验的工程师适用于高并发，海量数据的互联网环境以解决具体问题为主要目标，比如个人空间的DB优化针对优化任何一个方面都是个很深的话题，本次技术分享只能做到概要，如果大家有需求，有时间可以针对某方面的优化在做详细的技术交流,Agenda,内容提要MySQL常见工具应用MySQL优化MySQL架构MySQL备份,ySQL常见工具应用,常见：mysql，mysqladmin，mysqldump，myslqimpor

2、t mysqlcheck，myisamchk,，mysqlpack 备份：mysqlhotcopy，xtrabackup 性能：mysqlreport，mysqlbinlog，mytop，innotop msyqldumpslow/mysqlsla，mysqltuner压力测试：mysqlslap其他官方工具和第三方工具,常见工具一,mysql:1 类似于sqlplus，mysql help 2“-e”参数,常用于MySQL检查和监控脚本中 3“-H”“-X”“-prompt”“-tee”4 与f的联系mysqladmin:常用于监控脚本（状态检查，统计信息的flush,创建和删除数据库，关闭

3、MySQL Server）mysqladmin utest ptest hlocalhost ping mysqladmin utest ptest hlocalhost status mysqladmin utest ptest hlocalhost processlist mysqladmin utest ptest hlocalhost shutdown ps：mysql与mysqladmin区别,常见工具二,mysqldump:1“-T”指定格式文本文件 同select*into OUTFILE from 2“-d”参数只生成结构“-delayed-insert”延迟插入（队列）mys

4、qlimport:1 导入指定格式的文件，如csv文件 2 LOAD DATA INFILE mysqlcheck:1 4项功能：check,repair,analyze,optimite 2 innodb不支持修复功能myisamchk：1 类似mysqlcheck c/-r,只对myisam的索引文件有效 2 常用修复表的二种方法：mysqlcheck-utest-ptest-r-o DB myisamchk-r*.MYI,性能分析一,mysqlreport:1 perl语言编写的MySQL数据库监控脚本 2 友好的方式显示MySQL状态变量 3 优点：快速的查看各种状态参数组，无须手工计

5、算 4 mysqlreport user root password 1 mysqlreport host 117.79.91.30 user admin password 1 5 报告样单：http:/6 详细解释:http:/,性能分析二,mytop:1 类似于系统的top,对mysql进行即时监控 2 mytop-utest-ptest-h remot_host 3 报告样单：http:/4 详细解释:第一行主机名称，至今 运行时间 第二行的 Queries-至今查询总数，另外还有目前每秒处理的查询数和速度。第三行的 Key Efficiency-缓存命中率，如果太低了你可能要调整你的

6、MySQL 设置，或者调整一下表的结构，后面还有目前的进出速度。,性能分析三,mysqlsla:1 一款MySQL的日志分析工具 2 功能非常强大.数据报表,非常有利于分析慢查询的原因,包括执行频率,数据量,查询消耗等 3 mysqlsla-user=root-password=-ex-socket=/tmp/mysql.sock-lt slow slow.log mysqlsla-user=root-password=-ex-socket=/tmp/mysql.sock-lt general/data/mysql/test.log 5 4 报告样单：http:/5 详细解释:http:/,A

7、genda,内容提要MySQL常见工具应用MySQL优化MySQL架构MySQL备份,MySQL优化的大方向,服务器硬件，存储，网络环境（磁盘读/写速度，CPU主频周期，内存带宽，网络连接速度，网络带宽等）服务器系统（版本选择，内核选择，内核参数等）应用:(缓存系统，TCMalloc)f各项配置（log-bin，禁用dns查询，超时时间，文件系统外部锁，table_cache,Query_cache等）存储引擎，表，SQL查询，索引（根据应用选择合适存储引擎（Myisam OR INODB OR Other），设计好SQL和索引(explain，profiling)，应用各种工具分析系统性能瓶

8、颈，有针对性的调节）基础架构（mysql复制，负载均衡，读写分离，mysql集群,大表的切割(水平切割和垂直切割)等）,MySQL 架构,影响MySQL性能的因素,商业需求对性能的影响 1 不合理的需求，如论坛帖子总量的统计（实时更新）2 无用功能堆积，使系统过于复杂，影响整体性能系统架构对于性能的影响 1 不适合存在库的数据（二进制多媒体数据，流水队列数据超大文本数据）2 是否利用了应用层cache机制（系统各种配置及规则数据，活跃用户的基本信息数据，个性化定制数据，准实时的统计信息数据，访问频繁但变更少的数据）3 数据层实现是否精简？（优化的SQL）Schema设计对性能的影响硬件环境对性

9、能的影响（IOPS，磁盘和内存，CPU，存储）,硬件设备,多CPU多Core?，内存永远不嫌大-优化数据库最廉价有效的方案使用RAID10多磁盘提IO能力或者用NAS，SANUPS，RAID要带电池(BBU(电池备份单元)硬盘-优化完参数后，提高性能最显著的方法全千兆网络环境,系统调优,Linux最常见，熟悉的人多，好维护sun的Solaris和服务器对MySQL有专门的优化全部采用64位版本选择稳定内核（权衡稳定，性能，功能）综合比较XFS文件系统是个不错的选择调整系统默认内核参数（例如TCP/IP堆栈连接数）,应用优化,数据库只负责数据，不管逻辑使用Google的TCMalloc库，提高并

10、发的稳定性对软件尽量都采用静态编译优化，提高性能避免硬盘操作使用大量的缓存，降低对数据库的查询请求-增加缓存层(持久化，非持久化)架构上的调整（基于主从复制的扩展，Sharding，分区）key-value database-Tokyo Cabinet；Redis；MongDB；MemcacheDB.把复杂的判断和逻辑留给代码，而不是数据库,Schema的优化,高效的模型设计（需求为首，性能为目标）表字段适度冗余-尽量减少join 大字段垂直分拆-summary优化 大表水平拆分-基于类型的分拆考虑扩充的情况下用最省的类型-bool or int；char()or varchar();ip类型

11、-inet_aton 和inet_ntoa 函数命名规范表的设计。范式，反范式,索引的优化,MySQL只能在索引的最左边上搜索有效索引在存储引擎上实现，而不是服务器层一般针对数据分散的关键字进行建立索引尽量把索引建立在int,varchar类似的字段上在建立聚集索引的时候，要照顾到查询的sql不要建立过多的索引（可考虑聚集索引），否则更新 索引时间长尽量不要使用唯一索引,索引的优化(续),主键占用空间越小越好不要用随机值做主键。比如MD5根据主键查询速度最快联合索引-最左前缀不做不必要的索引-空间；时间；缓存只有几个值的字段不必索引,MySQL Server优化,MySQL 安装优化 源码静态

12、编译，定制化MySQL MySQL 日志设置优化 binlog：binlog_cache_size max_binlog_cache_size max_binlog_size sync_binlog slowlog：slow_query_log long_query_time slow_query_log_file long_queries_not_using_indexs,Mf的优化,show status like show innodb statusshow(global)variables like skip-name-resolvethread_concurrencydefault-

13、character-set,Query Cache优化,实现原理（query语句-hash桶(hash链表)，Result Set-内存Cache）ps:任何表的任何一条数据发生变化，会通知Query cache负面影响 query语句的hash运算以及hash查找消耗资源（每秒几千?）query cache的失效问题（表变更频繁）query cache缓存的是Result Set，而不是数据页(多次cache)适度使用query cache（扬长避短）SQL Hint:SQL_NO_CACHE和SQL_CACHE 有些Result Set很大，内存不足？query_cache_limit,Q

14、uery Cache优化(续),show variables like%query_cache%;have_query_cache query_cache_limit(1MB)query_cache_min_res_unit(4KB)query_cache_size query_cache_type query_cache_wlock_invalidateshow status like Qcache%;flush status/flush query cache,网络连接优化,max_connections 整个MySQL允许的最大连接（500-800）max_user_connection

15、s 每个用户允许的最大连接back_log 在连接请求等待队列中允许存放的最大请求数 ps:注意OS级别对网络监听队列的限制net_buffer_length 传输消息前的net buffer初始化大小max_allowed_packet 一次消息传输量的最大值 关于timeout(connect_timeout,interactive_timeout,wait_timeout,net_read_timeout,net_write_timeout)ps：connect_timeout在获取连接阶段（authenticate）起作用，interactive_timeout和wait_timeou

16、t在连接空闲阶段（sleep）起作用，而net_read_timeout和net_write_timeout则是在连接繁忙阶段（query）起作用。,线程池优化,thread_cache_size 线程池中应该存放的连接线程数 ps:短连接，不小于实际并发请求数 长连接（50-100）thread_stack 每个连接线程被创建时，给他分配的内存大小show variables like thread%;show status like connections;show status like%thread%;Thread cache命中率：hit=(connections Thread_cr

17、eated)/connections*100%,Sort Buffer,Join Buffer,sort_buffer_size 对数据进行排序时的buffer（单个thread）ps：order by/group by join_buffer_size(join-ALL,index,rang,index_merge;”Full join”)show variables like%buffer%;show status like sort%;ps：看sort_merge_passes值，若很大，调sort_buffer_size,MyISAM优化,默认的存储引擎，使用B+Tree进行索引支持静

18、态，动态，压缩的数据格式，但不支持事务，外键支持文本和索引的压缩数据和索引文件单独存放适合多读写少的操作，几乎没有并发性要定期优化表，提供外部的扩展工具来修复数据文件,MyISAM优化(续一),key_buffer_size 索引缓存大小 指标：1 系统索引的总大小 2 系统可用物理内存 3 根据系统当年的Key Cache命中率key_buffer_cache_size 索引缓存中的Cache Block Sizekey_cache_division_limit LRU链表中的Hot Area与Warm Area的分界值，取值1100，默认值为100key_cache_age_thresho

19、ld 控制Cache Block中Hot Area何时被降至Warm Area(100-300,默认值300)多个Key Cache问题,MyISAM优化(续二),show status like key%;Hit=(1-Key_reads/Key_read_requests)*100%Key Cache 使用：Key_Buffer_UsageRatio=(Key_blocks_used/(key_blocks_used+key_blocks_unused)*100%该值一般为99%以上甚至100%，若过低，则key_buffer_size过大Key_Buffer_Read_HitRatio=

20、(1-Key_reads/Key_read_requests)*100%尽可能的高，若值过低，则key_buffer_size过小Key_Buffer_Write_HitRatio=(1-Key_writes/Key_read_requests)*100%,MyISAM优化(续三),表读取缓存化（Sequential Scan-全表/Random Scan-索引）ps:myisam不缓存数据（.MYD）文件，读数据需调用文件系统的相关指令，因为需为此操作分配内存缓冲区read_buffer_size：以Sequential Scan方式扫描数据的bufferread_rnd_buffer_si

21、ze:以Random Scan方式扫描数据的bufferbulk_insert_buffer_sizemyisam_sort_buffer_sizemyisam_max_sort_file_sizemyisam_max_extra_sort_file_sizemyisam_repair_threadsmyisam-recover,InnoDB优化,使用表空间，数据和索引存放在一起，数据有自动恢 复能力内存自我管理，有独立的内存缓冲池支持事务，外键，行级锁，支持聚集索引适合大量的读写操作，有一定的并发性参数的调优对Innodb非常重要-默认参数性能很差Innodb不会对参数做任何自我优化通过配置

22、参数可以达到跟MyISAM读速度不相上下,innodb_buffer_pool_size,最重要的参数缓存Innodb的索引和数据-根据你的数据量来分配，太大就浪费可以设置为物理内存的80%-要给操作系统和其他缓存留有足够的内存，不然会有内存竞争负载过重时，Innodb分配的值可能会多10%,innodb_log_file_szie,对写操作频繁的数据库很重要更大的日志文件=更少的数据刷新更大的日志文件=更长的恢复时间怎么选择合适自己应用的值？-innodb_log_buffer_size设置为每秒的数据量，innodb_log_file_size设置为半个小时的数据量（因为默认有两个日志文件

23、）-怎么确定每秒数据量见这里,innodb_flush_log_at_trx_commit,看应用的要求 1：默认值，最安全。每个事务提交时不仅会写到日志，也会刷新到磁盘。即使停电也不会丢失数据 0：最不安全，效率最高。事务提交时不做任何操作，每秒钟刷新到日志和磁盘。数据库崩溃会丢失1秒的事务 2：每个事务提交时写到日志，每秒刷新到磁盘一次。系统崩溃会丢失1秒的事务,innodb_flush_method,Innodb刷新数据到磁盘的方法默认是fsync()-操作系统和数据库会缓存两份数据(double buffering)O_DIRECT-绕过操作系统的缓存-如果是大量随机写入操作，O_DI

24、RECT会提高效率。但是顺序写入和读取效率都会降低。所以使用O_DIRECT需要根据需求测试。,Mf 配置,在取舍之间追求一种平衡（稳定/性能）业务决定存储引擎，存储引擎决定具体的配置参数当然，你的硬件选择，操作系统等都决定你的参数选 择,SQL语句,如果你一眼看不明白这个SQL语句是干嘛的，就重写吧explain每一个SQL语句，确认是否用到索引-select*from table where 不要在innodb引擎的表上使用count(*)批量insert和update开启慢查询日志，把超过一秒的操作拉出来分析 利用profiling来分析查询.,Agenda,内容提要MySQL常见工具应

25、用MySQL优化MySQL架构MySQL备份,MySQL架构,Replication 常见架构常规复制架构（Master-Slaves）特点：读用于读压力大（简单读写分离）,Master,Slave,slave,slave,Dual Master复制架构,Dual Master复制架构 特点：特定场合下Master的切换方便（维护）配合第三方HA可以实现自动切换，减少异常停机时间,Master,Master,级联复制架构,级联复制架构 特点：解决Master因为附属Slave太多而成为瓶颈问题 缺点是延时教长。,Slave,slave,Master,Slave,Slave,Slave,Dual

26、 Master与级联复制结合架构,Dual Master与级联复制结合架构,Master,Master,Slave,Slave,Slave,分区,5.1之后才出现的分区Range PartitioningList PartitioningHash PartitioningKey PartitioningSubpartitioning其他高可用方案：MySQL Cluster DRBD 磁盘网络镜像方案,性能问题收集？,系统相关命令（sar,iostat,vmstat,mpstat,uptime）数据库相关（show globalprocesslist,status,variables,info

27、rmation_schema）性能收集脚本（mysqlreport,innotop,mytop）专业监控工具（MySQL Monitor,MySQL administrator,cacti,nagios）,Agenda,内容提要MySQL常见工具应用MySQL优化MySQL架构MySQL备份,备份目的,灾难恢复防止用户的误操作程序上的bug应用测试业务特殊的需求审计,备份的准备工作,数据丢失的允许程度数据库的大小数据增长的速度数据库恢复或还原的时间，技术难度业务处理的频繁程度哪些表中数据变化是频繁的，哪些表中的数据句是固 定的什么时候大量用数据库，导致频繁的插入和更新操作现有的数据库备份资源有

28、哪些？(存储，磁盘，磁带，光盘)有无可能为数据库备份投入新的设备,备份的方式,备份的方式,备份,逻辑备份,物理备份,冷备份,热备份,备份带来的影响,备份需要花费的时间是否影响应用如何选择冷备份，热备份，温备份数据库的大小业务的需求提早做出备份计划,怎么去做备份,统计你的数据库服务器(实例,库)建立自己的数据库备份服务器熟悉备份数据库的存储引擎(充分掌握存储引擎的特性)使用MySQL的异步复制自己编写脚本第三方备份工具和存储技术,备份工具,mysqldumpmysqlhotcopyLVM snapshots/ZFS snapshotsInnoDB Hot Backup/XtrabackupZRM for MySQL,推荐网站:推荐书籍：MySQL 性能调优与架构设计 构建高性能Web站点 Linux 服务器性能调整,谢谢!联络：,