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

mysql优化实践,mysql性能优化知识分享,agenda,内容提要 mysql常见工具应用 mysql优化 mysql架构 mysql备份,summary,用于员工培训和分享，主要针对有一定开发经验的工程师 适用于高并发，海量数据的互联网环境 以解决具体问题为主要目标，比如个人空间的db优化 针对优化任何一个方面都是个很深的话题，本次技术分享只能做到概要，如果大家有需求，有时间可以针对某方面的优化在做详细的技术交流,agenda,内容提要 mysql常见工具应用 mysql优化 mysql架构 mysql备份,ysql常见工具应用,常见：mysql ，mysqladmin，mysqldump，myslqimport 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, 创建和删除数据库，关闭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” 延迟插入（队列） mysqlimport: 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 优点：快速的查看各种状态参数组，无须手工计算 4 mysqlreport user root password 1 mysqlreport host 0 user admin password 1 5 报告样单： /mysqlreportguide 6 详细解释: /2010/05/mysqlreport-report-describes.html,性能分析二,mytop: 1 类似于系统的top, 对mysql进行即时监控 2 mytop -utest -ptest -h remot_host 3 报告样单： /mysql/mytop/ 4 详细解释: 第一行主机名称，至今 运行时间 第二行的 queries -至今查询总数，另外还有目前每秒处理的查询数和速度。 第三行的 key efficiency -缓存命中率，如果太低了你可能要调整你的 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 报告样单： /mysqlsla 5 详细解释: /linux_life/blog/item/41435f318de02da05edf0e94.html,agenda,内容提要 mysql常见工具应用 mysql优化 mysql架构 mysql备份,mysql优化的大方向,服务器硬件，存储，网络环境 （磁盘读/写速度，cpu主频周期，内存带宽，网络连接速度，网络带宽等） 服务器系统（版本选择，内核选择，内核参数等） 应用: (缓存系统，tcmalloc) f各项配置（log-bin，禁用dns查询，超时时间，文件系统外部锁，table_cache,query_cache等） 存储引擎，表，sql查询，索引 （根据应用选择合适存储引擎（myisam or inodb or other），设计好sql和索引(explain，profiling) ，应用各种工具分析系统性能瓶颈，有针对性的调节） 基础架构 （mysql复制，负载均衡，读写分离， mysql集群,大表的切割(水平切割和垂直切割)等）,mysql 架构,影响mysql性能的因素,商业需求对性能的影响 1 不合理的需求，如论坛帖子总量的统计（实时更新） 2 无用功能堆积，使系统过于复杂，影响整体性能 系统架构对于性能的影响 1 不适合存在库的数据（二进制多媒体数据，流水队列数据超大文本数据） 2 是否利用了应用层cache机制（系统各种配置及规则数据，活跃用户的基本信息数据，个性化定制数据，准实时的统计信息数据，访问频繁但变更少的数据） 3 数据层实现是否精简？（优化的sql） schema设计对性能的影响 硬件环境对性能的影响（iops，磁盘和内存，cpu，存储）,硬件设备,多cpu多core?，内存永远不嫌大 -优化数据库最廉价有效的方案 使用raid10多磁盘提io能力或者用nas，san ups，raid要带电池(bbu(电池备份单元) 硬盘 -优化完参数后，提高性能最显著的方法 全千兆网络环境,系统调优,linux最常见，熟悉的人多，好维护 sun的solaris和服务器对mysql有专门的优化 全部采用64位版本 选择稳定内核（权衡稳定，性能，功能） 综合比较xfs文件系统是个不错的选择 调整系统默认内核参数（例如tcp/ip堆栈连接数）,应用优化,数据库只负责数据，不管逻辑 使用google的tcmalloc库，提高并发的稳定性 对软件尽量都采用静态编译优化，提高性能 避免硬盘操作 使用大量的缓存，降低对数据库的查询请求 -增加缓存层(持久化，非持久化) 架构上的调整（基于主从复制的扩展，sharding，分区） key-value database -tokyo cabinet；redis；mongdb；memcachedb. 把复杂的判断和逻辑留给代码，而不是数据库,schema的优化,高效的模型设计（需求为首，性能为目标） 表字段适度冗余-尽量减少join 大字段垂直分拆-summary优化 大表水平拆分-基于类型的分拆 考虑扩充的情况下用最省的类型 -bool or int；char() or varchar(); ip类型 -inet_aton 和inet_ntoa 函数 命名规范 表的设计。范式，反范式,索引的优化,mysql只能在索引的最左边上搜索有效 索引在存储引擎上实现，而不是服务器层 一般针对数据分散的关键字进行建立索引 尽量把索引建立在int,varchar类似的字段上 在建立聚集索引的时候，要照顾到查询的sql 不要建立过多的索引（可考虑聚集索引），否则更新 索引时间长 尽量不要使用唯一索引,索引的优化(续),主键占用空间越小越好 不要用随机值做主键。比如md5 根据主键查询速度最快 联合索引 -最左前缀 不做不必要的索引 -空间；时间；缓存 只有几个值的字段不必索引,mysql server优化,mysql 安装优化 源码静态编译，定制化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 status show (global) variables like skip-name-resolve thread_concurrency default-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,query 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_invalidate show status like qcache%; flush status/flush query cache,网络连接优化,max_connections 整个mysql允许的最大连接（500-800） max_user_connections 每个用户允许的最大连接 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_timeout在连接空闲阶段（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_created)/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进行索引 支持静态，动态，压缩的数据格式，但不支持事务， 外键 支持文本和索引的压缩 数据和索引文件单独存放 适合多读写少的操作，几乎没有并发性 要定期优化表，提供外部的扩展工具来修复数据文件,myisam优化(续一),key_buffer_size 索引缓存大小 指标：1 系统索引的总大小 2 系统可用物理内存 3 根据系统当年的key cache命中率 key_buffer_cache_size 索引缓存中的cache block size key_cache_division_limit lru链表中的hot area与warm area的分界值，取值1100，默认值为100 key_cache_age_threshold 控制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 = (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方式扫描数据的buffer read_rnd_buffer_size: 以random scan方式扫描数据的buffer bulk_insert_buffer_size myisam_sort_buffer_size myisam_max_sort_file_size myisam_max_extra_sort_file_size myisam_repair_threads myisam-recover,innodb优化,使用表空间，数据和索引存放在一起，数据有自动恢 复能力 内存自我管理，有独立的内存缓冲池 支持事务，外键，行级锁，支持聚集索引 适合大量的读写操作，有一定的并发性 参数的调优对innodb非常重要 -默认参数性能很差 innodb不会对参数做任何自我优化 通过配置参数可以达到跟myisam读速度不相上下,innodb_buffer_pool_size,最重要的参数 缓存innodb的索引和数据 -根据你的数据量来分配，太大就浪费 可以设置为物理内存的80% -要给操作系统和其他缓存留有足够的内存，不然会有内存竞争 负载过重时，innodb分配的值可能会多10%,innodb_log_file_szie,对写操作频繁的数据库很重要 更大的日志文件 = 更少的数据刷新 更大的日志文件 = 更长的恢复时间 怎么选择合适自己应用的值？ -innodb_log_buffer_size设置为每秒的数据量， innodb_log_file_size设置为半个小时的数据量（因为默认有两个日志文件） -怎么确定每秒数据量见这里,innodb_flush_log_at_trx_commit,看应用的要求 1：默认值，最安全。每个事务提交时不仅会写到日志，也会刷新到磁盘。即使停电也不会丢失数据 0：最不安全，效率最高。事务提交时不做任何操作，每秒钟刷新到日志和磁盘。数据库崩溃会丢失1秒的事务 2：每个事务提交时写到日志，每秒刷新到磁盘一次。系统崩溃会丢失1秒的事务,innodb_flush_method,innodb刷新数据到磁盘的方法 默认是fsync() -操作系统和数据库会缓存两份数据(double buffering) o_direct -绕过操作系统的缓存 -如果是大量随机写入操作，o_direct会提高效率。但是顺序写入和读取效率都会降低。所以使用o_direct需要根据需求测试。,mf 配置,在取舍之间追求一种平衡（稳定/性能） 业务决定存储引擎，存储引擎决定具体的配置参数 当然，你的硬件选择，操作系统等都决定你的参数选 择,sql语句,如果你一眼看不明白这个sql语句是干嘛的，就重写吧 explain每一个sql语句，确认是否用到索引 - select * from table where 不要在innodb引擎的表上使用count(*) 批量insert和update 开启慢查询日志，把超过一秒的操作拉出来分析 利用profiling来分析查询.,agenda,内容提要 mysql常见工具应用 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 master与级联复制结合架构,dual master与级联复制结合架构,master,master,slave,slave,slave,分区,5.1之后才出现的分区 range partitioning list partitioning hash partitioning key partitioning subpartitioning 其他高可用方案： mysql clust