IT培训分享大规模数据库的性能和伸缩性的优化

在需要支持移动/平板电脑应用及普通桌面浏览器访问的时代，网站的普及率和有效性很大程度上取决于其可用性和性能。一个访问缓慢的网站会使得访问者或潜在的客户流失，并导致商业的失败。IT培训认为一个访问速度相当快的网站将会决定访客是否会使用网站提供的产品或服务。

拥有大规模数据库的网站始终需要适当的关注、配置、优化、调整和维护，以确保网站的快速加载。这篇文章将讨论如何优化有海量数据的MySQL数据库。

选择InnoDB作为存储引擎

大型产品的数据库对于可靠性和并发性的要求较高，InnoDB作为默认的MySQL存储引擎，相对于MyISAM来说是个更佳的选择。

优化数据库结构

组织数据库的schema、表和字段以降低I/O的开销，将相关项保存在一起，并提前规划，以便随着数据量的增长，性能可以保持较高的水平。

设计数据表应尽量使其占用的空间最小化，表的主键应尽可能短。

对于InnoDB表，主键所在的列在每个辅助索引条目中都是可复制的，因此如果有很多辅助索引，那么一个短的主键可以节省大量空间。

仅创建你需要改进查询性能的索引。索引有助于检索，但是会增加插入和更新 *** 作的执行时间。

InnoDB的ChangeBuffering特性

InnoDB提供了changebuffering的配置，可减少维护辅助索引所需的磁盘I/O。大规模的数据库可能会遇到大量的表 *** 作和大量的I/O，以保证辅助索引保持最新。当相关页面不在缓冲池里面时，InnoDB的changebuffer将会更改缓存到辅助索引条目，从而避免因不能立即从磁盘读取页面而导致耗时的I/O *** 作。当页面被加载到缓冲池时，缓冲的更改将被合并，更新的页面之后会刷新到磁盘。这样做可提高性能，适用于MySQL55及更高版本。

Interpretation

在处理性能问题时，我们最关注的是数据库正在等待什么。

当进程因为某些原因不能进行 *** 作时，它需要等待。花费时间最多的等待事件是我们最需要关注的，因为降低它，我们能够获得最大的好处。

AWR报告中的"Top 5 Timed Events"部分就提供了这样的信息，可以让我们只关注主要的问题。

•

Top 5 Timed Events

正如前面提到的，"Top 5 Timed Events"是AWR报告中最重要的部分。它指出了数据库的sessions花费时间最多的等待事件，如下：

Top 5 Timed Events Avg %Total

~~~~~~~~~~~~~~~~~~ wait Call

Event Waits Time (s) (ms) Time Wait Class

------------------------------ ------------ ----------- ------ ------ ----------

db file scattered read 10,152,564 81,327 8 296 User I/O

db file sequential read 10,327,231 75,878 7 276 User I/O

CPU time 56,207 205

read by other session 4,397,330 33,455 8 122 User I/O

PX Deq Credit: send blkd 31,398 26,576 846 97 Other

-------------------------------------------------------------

Top 5 Events部分包含了一些跟Events（事件）相关的信息。它记录了这期间遇到的等待的总次数，等待所花费的总时间，每次等待的平均时间；这一部分是按照每个Event占总体call time的百分比来进行排序的。

根 据Top 5

Events部分的信息的不同，接下来我们需要检查AWR报告的其他部分，来验证发现的问题或者做定量分析。等待事件需要根据报告期的持续时间和当时数据

库中的并发用户数进行评估。如：10分钟内1000万次的等待事件比10个小时内的1000万等待更有问题；10个用户引起的1000万次的等待事件比

10,000个用户引起的相同的等待要更有问题。

就像上面的例子，将近60%的时间是在等待IO相关的事件。

• 事件"db file scattered read"一般表明正在做由全表扫描或者index fast full scan引起的多块读。

• 事件"db file sequential read"一般是由不能做多块读的 *** 作引起的单块读（如读索引）

其他20%的时间是花在使用或等待CPU time上。过高的CPU使用经常是性能不佳的SQL引起的（或者这些SQL有可能用更少的资源完成同样的 *** 作）；对于这样的SQL，过多的IO *** 作也是一个症状。关于CPU使用方面，我们会在之后讨论。

在以上基础上，我们将调查是否这个等待事件是有问题的。若有问题，解决它；若是正常的，检查下个等待事件。

过多的IO相关的等待一般会有两个主要的原因：

• 数据库做了太多的读 *** 作

• 每次的IO读 *** 作都很慢

Top 5 Events部分的显示的信息会帮助我们检查：

• 是否数据库做了大量的读 *** 作：

上面的图显示了在这段时间里两类读 *** 作都分别大于1000万，这些 *** 作是否过多取决于报告的时间是1小

时或1分钟。我们可以检查AWR报告的elapsed time如果这些读 *** 作确实是太多了，接下来我们需要检查AWR报告中 SQL

Statistics 部分的信息，因为读 *** 作都是由SQL语句发起的。

• 是否是每次的IO读 *** 作都很慢：

上面的图显示了在这段时间里两类读 *** 作平均的等待时间是小于8ms的

至于8ms是快还是慢取决于底层的硬件设备；一般来讲小于20ms的都可以认为是可以接受的。

我们还可以在AWR报告"Tablespace IO Stats"部分得到更详细的信息

Tablespace IO Stats DB/Inst: VMWREP/VMWREP Snaps: 1-15

-> ordered by IOs (Reads + Writes) desc

Tablespace

------------------------------

Av Av Av Av Buffer Av Buf

Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)

-------------- ------- ------ ------- ------------ -------- ---------- ------

TS_TX_DATA

14,246,367 283 76 46 145,263,880 2,883 3,844,161 83

USER

204,834 4 107 10 17,849,021 354 15,249 98

UNDOTS1

19,725 0 30 10 10,064,086 200 1,964 49

AE_TS

4,287,567 85 54 67 932 0 465,793 37

TEMP

2,022,883 40 00 58 878,049 17 0 00

UNDOTS3

1,310,493 26 46 10 941,675 19 43 00

TS_TX_IDX

1,884,478 37 73 10 23,695 0 73,703 83

>SYSAUX

346,094 7 56 39 112,744 2 0 00

SYSTEM

101,771 2 79 35 25,098 0 653 27

如上图，我们关心Av Rd(ms)的指标。如果它高于20ms并且同时有很多读 *** 作的，我们可能要开始从OS的角度调查是否有潜在的IO问题。

限流算法目前程序开发过程常用的限流算法有两个：漏桶算法和令牌桶算法。

漏桶算法

漏桶算法的原理比较简单，请求进入到漏桶中，漏桶以一定的速率漏水。当请求过多时，水直接溢出。可以看出，漏桶算法可以强制限制数据的传输速度。如图所示，把请求比作是水滴，水先滴到桶里，通过漏洞并以限定的速度出水，当水来得过猛而出水不够快时就会导致水直接溢出，即拒绝服务。

来自网络

漏桶的出水速度是恒定的，那么意味着如果瞬时大流量的话，将有大部分请求被丢弃掉（也就是所谓的溢出）。

令牌桶算法

令牌桶算法的原理是系统以一定速率向桶中放入令牌，如果有请求时，请求会从桶中取出令牌，如果能取到令牌，则可以继续完成请求，否则等待或者拒绝服务。这种算法可以应对突发程度的请求，因此比漏桶算法好。

来自网络

漏桶算法和令牌桶算法的选择

两者的主要区别漏桶算法能够强行限制处理数据的速率，不论系统是否空闲。而令牌桶算法能够在限制数据的平均处理速率的同时还允许某种程度的突发流量。如何理解上面的含义呢？漏桶算法，比如系统吞吐量是 120/s，业务请求 130/s，使用漏斗限流 100/s，起到限流的作用，多余的请求将产生等待或者丢弃。对于令牌桶算法，每秒产生 100 个令牌，系统容量 200 个令牌。正常情况下，业务请求 100/s 时，请求能被正常被处理。当有突发流量过来比如 200 个请求时，因为系统容量有 200 个令牌可以同一时刻处理掉这 200 个请求。如果是漏桶算法，则只能处理 100 个请求，其他的请求等待或者被丢弃。

用于查看哪些实例的哪些 *** 作使用了大量的临时段SELECT to_number(decode(SID NULL SID)) sid operation_type OPERATION trunc(EXPECTED_SIZE/ ) ESIZE trunc(ACTUAL_MEM_USED/ ) MEM trunc(MAX_MEM_USED/ ) MAX MEM NUMBER_PASSES PASS trunc(TEMPSEG_SIZE/ ) TSIZEFROM V$SQL_WORKAREA_ACTIVEORDER BY ; 查询有热块查询的SQL语句select hash_valuefrom v$sqltext a (select distinct a owner a segment_name a segment_type fromdba_extents a (select dbarfil dbablkfrom (select dbarfil dbablkfrom x$bh order by tch desc) where rownum < ) bwhere a RELATIVE_FNO = b dbarfiland a BLOCK_ID <= b dbablk and a block_id + a blocks > b dbablk) bwhere a sql_text like % ||b segment_name|| % and b segment_type = TABLE order by　a hash_value a address a piece; 全表扫描select opname target b num_rows b tablespace_name count(target) from v$session_longops a all_all_tables bwhere a TARGET=b owner|| ||b table_namehaving count(target)> group by　 opname target b num_rows b tablespace_name 查看磁盘排序和缓存排序次数select　to_char(sn snap_time yyyy mm dd hh ) time_ avg(newmen value oldmen value) sorts_memeory avg(newdsk value olddsk value) disk_sortfromstats$sysstat oldmen stats$sysstat newmen stats$sysstat newdsk stats$sysstat olddsk stats$snapshot snwhere　 newdsk snap_id=sn snap_idand olddsk snap_id=sn snap_id and newmen snap_id=sn snap_idand newdsk snap_id=sn snap_id and oldmen name= sorts (memory) and newmen name= sorts (memory) and olddsk name= sorts (disk) and newdsk name= sorts (disk) group by　to_char(sn snap_time yyyy mm dd hh ) 执行最慢的前 个SQL？？？select from (selectto_char(snap_time dd Mon HH :mi:ss ) mydate executions exec loads　loads parse_callsparse disk_reads reads buffer_getsgets rows_processed rows_proc sorts　sorts sql_text hash_valuefromperfstat stats$sql_summary sql perfstat stats$snapshot snwheresql snap_id >(select min(snap_id) min_snapfrom stats$snapshot where snap_time > sysdate $days_back)andsql snap_id = sn snap_idorder by $sortskey desc)　tt where rownum< ; SQL缓存池的命中率查询（pinhitratio gethitratio应该大于 %以上）select namespace gethitratio pinhitratio reloads invalidationsfrom v$librarycachewhere namespace in ( SQL AREA TABLE/PROCEDURE BODY TRIGGER ) 数据库的常规参数我就不说了 除了V$parameter中的常规参数外 ORACLE还有大量的隐含参数 下面的语句就可以查询到数据库的所有隐含参数以及其值与参数的描述 SELECT NAME VALUE decode(isdefault TRUE Y N ) as Default decode(ISEM TRUE Y N ) as SesMod decode(ISYM IMMEDIATE I DEFERRED D FALSE N ) as SysMod decode(IMOD MODIFIED U SYS_MODIFIED S N ) as Modified decode(IADJ TRUE Y N ) as Adjusted descriptionFROM ( GV$SYSTEM_PARAMETERSELECT x inst_id as instance x indx+ ksppinm as NAME ksppity ksppstvl as VALUE ksppstdf as isdefault decode(bitand(ksppiflg/ ) TRUE FALSE ) as ISEM decode(bitand(ksppiflg/ ) IMMEDIATE DEFERRED FALSE ) as ISYM decode(bitand(ksppstvf ) MODIFIED FALSE ) as IMOD decode(bitand(ksppstvf ) TRUE FALSE ) as IADJ ksppdesc as DESCRIPTIONFROM x$ksppi x x$ksppsv yWHERE x indx = y indxAND substr(ksppinm ) = _ AND x inst_id = USERENV( Instance ))ORDER BY NAME 想知道现在哪个用户正在利用临时段吗？这个语句将告诉你哪个用户正在利用临时段 SELECT b tablespace b segfile# b segblk# b blocks a sid a serial# a username a osuser a status c sql_textFROM v$session a v$sort_usage b v$sql cWHERE a saddr = b session_addrAND a sql_address = c address(+)ORDER BY b tablespace b segfile# b segblk# b blocks; 查看磁盘碎片select tablespace_name sqrt(max(blocks)/sum(blocks))( /sqrt(sqrt(count(blocks)))) FSFIfrom dba_free_spacegroup by tablespace_name order by 查看表空间的名称及大小select t tablespace_name round(sum(bytes/( )) ) ts_sizefrom dba_tablespaces t dba_data_files dwhere t tablespace_name = d tablespace_namegroup by t tablespace_name; 查看表空间物理文件的名称及大小select tablespace_name file_id file_name round(bytes/( ) ) total_spacefrom dba_data_filesorder by tablespace_name; 查看回滚段名称及大小select segment_name tablespace_name r status (initial_extent/ ) InitialExtent (next_extent/ ) NextExtent max_extents v curext CurExtentFrom dba_rollback_segs r v$rollstat vWhere r segment_id = v usn(+)order by segment_name 耗资源的进程（top session)select s schemaname schema_name decode(sign( mand) to_char(mand) Action Code # || to_char(mand) ) action statussession_status s osuser os_user_name s sid p spid s serial# serial_num nvl(s username [Oracle process] ) user_name s terminal terminal s program program st value criteria_value　from v$sesstat st v$session s　 v$process pwhere st sid = s sid and　 st statistic# = to_number( ) and　 ( ALL = ALL or s status = ALL ) and p addr = s paddr order by st value descp spid asc s username asc s osuser asc 查看锁（lock）情况select /+ RULE / ls osuser os_user_name ls username user_name decode(ls type RW Row wait enqueue lock TM DML enqueue lock TX Transaction enqueue lock UL User supplied lock ) lock_type o object_name object decode(ls lmode null Row Share Row Exclusive Share Share Row Exclusive Exclusive null)lock_mode o owner ls sid ls serial# serial_num ls id ls id from sys dba_objects o (　 select s osuser s username l type l lmode s sid s serial# l id l id from v$session s v$lock l　 where s sid = l sid ) ls　where o object_id = ls id ando owner<> SYS order by o owner o object_name 查看低效率的SQL语句SELECT EXECUTIONS DISK_READS BUFFER_GETS ROUND((BUFFER_GETS DISK_READS)/BUFFER_GETS ) Hit_radio ROUND(DISK_READS/EXECUTIONS ) Reads_per_run SQL_TEXTFROM　 V$SQLAREAWHERE　EXECUTIONS> AND BUFFER_GETS > AND (BUFFER_GETS DISK_READS)/BUFFER_GETS < ORDER BY DESC lishixinzhi/Article/program/Oracle/201311/17408

以上就是关于IT培训分享大规模数据库的性能和伸缩性的优化全部的内容，包括:IT培训分享大规模数据库的性能和伸缩性的优化、如何使用AWR报告来诊断数据库性能问题、如何查看高并发下mysql数据库的性能等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！