哪些因素影响了数据库性能

网络宽带，磁盘IO，查询速度都会影响到数据库的性能。

    具体问题具体分析，举例来说明为什么磁盘IO成瓶颈数据库的性能急速下降了。

   为什么当磁盘IO成瓶颈之后, 数据库的性能不是达到饱和的平衡状态，而是急剧下降。为什么数据库的性能有非常明显的分界点，原因是什么？

    相信大部分做数据库运维的朋友，都遇到这种情况。 数据库在前一天性能表现的相当稳定，数据库的响应时间也很正常，但就在今天，在业务人员反馈业务流量没有任何上升的情况下，数据库的变得不稳定了，有时候一个最简单的insert *** 作， 需要几十秒，但99%的insert却又可以在几毫秒完成，这又是为什么了？

dba此时心中有无限的疑惑，到底是什么原因呢? 磁盘IO性能变差了？还是业务运维人员反馈的流量压根就不对？ 还是数据库内部出问题？昨天不是还好好的吗？

 当数据库出现响应时间不稳定的时候，我们在 *** 作系统上会看到磁盘的利用率会比较高，如果观察仔细一点，还可以看到，存在一些读的IO. 数据库服务器如果存在大量的写IO,性能一般都是正常跟稳定的，但只要存在少量的读IO,则性能开始出现抖动，存在大量的读IO时（排除配备非常高速磁盘的机器），对于在线交易的数据库系统来说，大概性能就雪崩了。为什么 *** 作系统上看到的磁盘读IO跟写IO所带来的性能差距这么大呢？

如果亲之前没有注意到上述的现象，亲对上述的结论也是怀疑。但请看下面的分解。

在写这个文章之前，作者阅读了大量跟的IO相关的代码，如异步IO线程的相关的，innodb_buffer池相关的，以及跟读数据块最相关的核心函数buf_page_get_gen函数以及其调用的相关子函数。为了将文章写得通俗点，看起来不那么累，因此不再一行一行的将代码解析写出来。

咱们先来提问题。 buf_page_get_gen函数的作用是从Buffer bool里面读数据页，可能存在以下几种情况。

提问. 数据页不在buffer bool 里面该怎么办？

  回答：去读文件，将文件中的数据页加载到buffer pool里面。下面是函数buffer_read_page的函数，作用是将物理数据页加载到buffer pool, 图片中显示

buffer_read_page函数栈的顶层是pread64(),调用了 *** 作系统的读函数。

buf_read_page的代码

 如果去读文件，则需要等待物理读IO的完成，如果此时IO没有及时响应，则存在堵塞。这是一个同步读的 *** 作，如果不完成该线程无法继续后续的步骤。因为需要的数据页不再buffer 中，无法直接使用该数据页，必须等待 *** 作系统完成IO .

再接着上面的回答提问：

当第二会话线程执行sql的时候，也需要去访问相同的数据页，它是等待上面的线程将这个数据页读入到缓存中，还是自己再发起一个读磁盘的然后加载到buffer的请求呢？   代码告诉我们，是前者，等待第一个请求该数据页的线程读入buffer pool。

试想一下，如果第一个请求该数据页的线程因为磁盘IO瓶颈，迟迟没有将物理数据页读入buffer pool, 这个时间区间拖得越长，则造成等待该数据块的用户线程就越多。对高并发的系统来说，将造成大量的等待。 等待数据页读入的函数是buf_wait_for_read，下面是该函数相关的栈。

通过解析buf_wait_for_read函数的下层函数，我们知道其实通过首先自旋加锁pin的方式，超过设定的自旋次数之后，进入等待，等待IO完成被唤醒。这样节省不停自旋pin时消耗的cpu,但需要付出被唤起时的开销。

再继续扩展问题： 如果会话线程A 经过物理IO将数据页1001读入buffer之后，他需要修改这个页，而在会话线程A之后的其他的同样需要访问数据页1001的会话线程，即使在数据页1001被入读buffer pool之后，将仍然处于等待中。因为在数据页上读取或者更新的时候，同样需要上锁，这样才能保证数据页并发读取/更新的一致性。

由此可见，当一个高并发的系统，出现了热点数据页需要从磁盘上加载到buffer pool中时，造成的延迟，是难以想象的。因此排在等待热点页队列最后的会话线程最后才得到需要的页，响应时间也就越长，这就是造成了一个简单的sql需要执行几十秒的原因。

再回头来看上面的问题，mysql数据库出现性能下降时，可以看到 *** 作系统有读IO。 原因是，在数据库对数据页的更改，是在内存中的，然后通过检查点线程进行异步写盘，这个异步的写 *** 作是不堵塞执行sql的会话线程的。所以，即使看到 *** 作系统上有大量的写IO，数据库的性能也是很平稳的。但当用户线程需要查找的数据页不在buffer pool中时，则会从磁盘上读取，在一个热点数据页不是非常多的情况下，我们设置足够大的innodb_buffer_pool的size, 基本可以缓存所有的数据页，因此一般都不会出现缺页的情况，也就是在 *** 作系统上基本看不到读的IO。  当出现读的IO时，原因时在执行buf_read_page_low函数，从磁盘上读取数据页到buffer pool, 则数据库的性能则开始下降，当出现大量的读IO，数据库的性能会非常差。

以MySQL为例：

影响数据库性能的主要因素总结如下：

1、sql查询速度

2、网卡流量

3、服务器硬件

4、磁盘IO

以上因素并不是时时刻刻都会影响数据库性能，而就像木桶效应一样。如果其中一个因素严重影响性能，那么整个数据库性能就会严重受阻。另外，这些影响因素都是相对的。

例如：当数据量并没有达到百万千万这样的级别，那么sql查询速度也许就不是个重要因素，换句话说，你的sql语句效率适当低下可能并不影响整个效率多少，反之，这种情况，无论如何怎么优化sql语句，可能都没有太明显的效果。

相关内容拓展：

1、SQL查询速度

风险：效率低下的SQL

2、网卡流量

风险：网卡IO被占满（100Mb/8=100MB）

方案：

①减少从服务器的数量。从服务器都要从主服务器上复制日志，所以，从服务器越多，网络流量越大。

②进行分级缓存。前方大量缓存突然失效会对数据库造成严重的冲击。

③避免使用“select * ”进行查询

④分离业务网络和服务器网络

3、磁盘IO

风险：磁盘IO性能突然下降。

方案：使用更好的磁盘设备解决。

对于数据库整体的性能问题，AWR的报告是一个非常有用的诊断工具。

一般来说，当检测到性能问题时，我们会收集覆盖了发生问题的时间段的AWR报告-但是最好只收集覆盖1个小时时间段的AWR报告-如果时间过长，那么AWR报告就不能很好的反映出问题所在。还应该收集一份没有性能问题的时间段的AWR报告，作为一个参照物来对比有问题的时间段的AWR报告。这两个AWR报告的时间段应该是一致的，比如都是半个小时的，或者都是一个小时的。

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 WaitsTime (s) (ms) Time Wait Class

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

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

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

CPU time 56,207 20.5

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

PX Deq Credit: send blkd 31,398 26,5768469.7 Other

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

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

根 据Top 5 Events部分的信息的不同，接下来我们需要检查AWR报告的其他部分，来验证发现的问题或者做定量分析。等待事件需要根据报告期的持续时间和当时数据 库中的并发用户数进行评估。如：10分钟内1000万次的等待事件比10个小时内的1000万等待更有问题；10个用户引起的1000万次的等待事件比 10,000个用户引起的相同的等待要更有问题。

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

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

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

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

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

需要注意，接下来的分析步骤取决于我们在TOP 5部分的发现。在上面的例子里，3个top wait event表明问题可能与SQL语句执行计划不好有关，所以接下来我们要去分析"SQL Statistics"部分。

同样的，因为我们并没有看到latch相关的等待，latch在我们这个例子里并没有引发严重的性能问题；那么我们接下来就完全不需要分析latch相关的信息。

一 般来讲，如果数据库性能很慢，TOP 5等待事件里"CPU"， "db file sequential read" 和"db file scattered read" 比较明显（不管它们之间的顺序如何），我们总是需要检查Top SQL (by logical and physical reads)部分；调用SQL Tuning Advisor或者手工调优这些SQL来确保它们是有效率的运行。

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

上面的图显示了在这段时间里两类读 *** 作都分别大于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 2837.6 4.6 145,263,8802,883 3,844,1618.3

USER

204,834 4 10.7 1.0 17,849,021 354 15,2499.8

UNDOTS1

19,725 03.0 1.0 10,064,086 200 1,9644.9

AE_TS

4,287,567 855.4 6.7 9320465,7933.7

TEMP

2,022,883 400.0 5.8 878,049 17 00.0

UNDOTS3

1,310,493 264.6 1.0 941,675 19 430.0

TS_TX_IDX

1,884,478 377.3 1.0 23,6950 73,7038.3

>SYSAUX

346,094 75.6 3.9 112,7442 00.0

SYSTEM

101,771 27.9 3.5 25,09806532.7

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

注：对于一些比较空闲的tablespace/files,我们可能会得到一个比较大的Av Rd(ms)值；对于这样的情况，我们应该忽略这样的tablespace/files因为这个很大的值可能是由于硬盘自旋(spin)引起的，没有太大的参考意义。比如对

于一个有1000万次读 *** 作而且很慢的系统，引起问题的基本不可能是一个只有10次read的tablespace/file.

虽 然高"db file scattered read"和"db file sequential read"等待可以是I / O相关的问题，但是很多时候这些等待也可能是正常的；实际上，对一个已经性能很好的数据库系统，这些等待事件往往在top 5等待事件里，因为这意味着您的数据库没有那些真正的“问题”。

诀窍是能够评估引起这些等待的语句是否使用了最优的访问路径。如果"db file scattered read"比较高，那么相关的SQL语句可能使用了全表扫描而没有使用索引（也许是没有创建索引，也许是没有合适的索引）；相应的，如果"db file sequential read"过多，则表明也许是这些SQL语句使用了selectivity不高的索引从而导致访问了过多不必要的索引块或者使用了错误的索引。这些等待可 能说明SQL语句的执行计划不是最优的。

接下来就需要通过AWR来检查这些top SQL是否可以进一步的调优，我们可以查看AWR报告中 SQL Statistics 的部分.

上面的例子显示了20%的时间花在了等待或者使用CPU上，我们也需要检查 SQL statistics 部分来进一步的分析。

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

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

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

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

SQL Statistics

AWR包含了一些不同的SQL统计值：

根据Top 5 部分的Top Wait Event不同，我们需要检查不同的SQL statistic。

在我们这个例子里，Top Wait Event是"db file scattered read"，"db file sequential read"和CPU；我们最需要关心的是SQL ordered by CPU Time, Gets and Reads。

我们会从"SQL ordered by gets"入手，因为引起高buffer gets的SQL语句一般是需要调优的对象。

SQL ordered by Gets

->Resources reported for PL/SQL code includes the resources used by all SQL

statements called by the code.

->Total Buffer Gets: 4,745,943,815

->Captured SQL account for 122.2% of Total

Gets CPU Elapsed

Buffer Gets Executionsper Exec %Total Time (s) Time (s)SQL Id

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

1,228,753,877 168 7,314,011.2 25.9 8022.46 8404.73 5t1y1nvmwp2

SELECT ADDRESSID",CURRENT$."ADDRESSTYPEID",CURRENT$URRENT$."ADDRESS3",

CURRENT$."CITY",CURRENT$."ZIP",CURRENT$."STATE",CURRENT$."PHONECOUNTRYCODE",

CURRENT$."PHONENUMBER",CURRENT$."PHONEEXTENSION",CURRENT$."FAXCOU

1,039,875,759 62,959,363 16.5 21.9 5320.27 5618.96 grr4mg7ms81

Module: DBMS_SCHEDULER

INSERT INTO "ADDRESS_RDONLY" ("ADDRESSID","ADDRESSTYPEID","CUSTOMERID","

ADDRESS1","ADDRESS2","ADDRESS3","CITY","ZIP","STATE","PHONECOUNTRYCODE","PHONENU

854,035,223 168 5,083,543.0 18.0 5713.50 7458.95 4at7cbx8hnz

SELECT "CUSTOMERID",CURRENT$."ISACTIVE",CURRENT$."FIRSTNAME",CURRENT$."LASTNAME",CU<

RRENT$."ORGANIZATION",CURRENT$."DATEREGISTERED",CURRENT$."CUSTOMERSTATUSID",CURR

ENT$."LASTMODIFIEDDATE",CURRENT$."SOURCE",CURRENT$."EMPLOYEEDEPT",CURRENT$.

对这些Top SQL,可以手工调优，也可以调用SQL Tuning Advisor。

分析：

Other SQL Statistic Sections

就像之前提到的那样，AWR报告中有很多不同的部分用来分析各种不同的问题。如果特定的问题并没有出现，那么分析AWR报告的这些部分并不能有很大的帮助。

下面提到了一些可能的问题：

Waits for 'Cursor: mutex/pin' 如 果发现了一些像"Cursor: pin S wait on X" 或"Cursor: mutex X" 类的mutex等待，那么可能是由于parsing引起的问题。检查"SQL ordered by Parse Calls" 和"SQL ordered by Version Count"部分的Top SQL，这些SQL可能引起这类的问题。

单次执行buffer gets过多

SQL_ID为'5t1y1nvmwp2'和'4at7cbx8hnz'的SQL语句总共被执行了168次，但是每次执行引起的buffer gets超过500万。这两个SQL应该是主要的需要调优的候选者。

执行次数过多

SQL_ID 'grr4mg7ms81' 每次执行只是引起16次buffer gets,减少这条SQL每次执行的buffer get可能并不能显著减少总共的buffer gets。这条语句的问题是它执行的太频繁了，6500万次。

改变这条SQL的执行次数可能会更有意义。这个SQL看起来是在一个循环里面被调用，如果可以让它一次处理的数据更多也许可以减少它执行的次数。

->Total Buffer Gets: 4,745,943,815

假设这是一个一个小时的AWR报告，4,745,943,815是一个很大的值；所以需要进一步分析这个SQL是否使用了最优的执行计划

Individual Buffer Gets

上面的例子里单个的SQL的buffer get非常多，最少的那个都是8亿5千万。这三个SQL指向了两个不同的引起过多buffers的原因：

注意：对于某些非常繁忙的系统来讲，以上的数字可能都是正常的。这时候我们需要把这些数字跟正常时段的数字作对比，如果没有什么太大差别，那么这些SQL并不是引起问题的元凶（虽然通过调优这些SQL我们仍然可以受益）

Load Profile

根据Top 5等待事件的不同，"Load Profile"可以提供一些有用的背景资料或潜在问题的细节信息。

Load Profile

~~~~~~~~~~~~Per Second Per Transaction

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

Redo size: 4,585,414.80 3,165,883.14

Logical reads: 94,185.63 65,028.07

Block changes: 40,028.57 27,636.71

Physical reads: 2,206.12 1,523.16

Physical writes: 3,939.97 2,720.25

User calls: 50.08 34.58

Parses: 26.96 18.61

Hard parses: 1.49 1.03

Sorts: 18.36 12.68

Logons: 0.13 0.09

Executes: 4,925.89 3,400.96

Transactions: 1.45

% Blocks changed per Read: 42.50Recursive Call %:99.19

Rollback per transaction %: 59.69 Rows per Sort: 1922.64

在这个例子里，Top 5 Events部分显示问题可能跟SQL的执行有关，那么我们接下来检查load profile部分。

如果您检查AWR report是为了一般性的性能调优，那么可以看到有比较多的redo activity和比较高的physical writes. Physical writes比physical read要高，并且有42%的块被更改了.

此外，hard parse的次数要少于soft parse.

如果mutex等待事件比较严重，如"library cache: mutex X"，那么查看所有parse的比率会更有用。

当然，如果把Load Profile部分跟正常时候的AWR报告做比较会更有用，比如，比较redo size, users calls, 和 parsing这些性能指标。

Instance Efficiency

Instance Efficiency部分更适用于一般性的调优，而不是解决某个具体问题（除非等待事件直接指向这些指标）。

Instance Efficiency Percentages (Target 100%)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Buffer Nowait %: 99.91 Redo NoWait %: 100.00

Buffer Hit %: 98.14In-memory Sort %: 99.98

Library Hit %: 99.91Soft Parse %: 94.48

Execute to Parse %: 99.45 Latch Hit %: 99.97

Parse CPU to Parse Elapsd %: 71.23 % Non-Parse CPU: 99.00

从我们的这个例子来看，最有用的信息是%Non-Parse CPU，它表明几乎所有的CPU都消耗在了Execution而不是Parse上，所以调优SQL会对性能有改善。

94.48％ 的soft parse比率显示hard parse的比例很小，这是可取的。Execute to Parse %很高，说明cursor被很好的重用了。我们总是期望这里的值都是接近100%,但是因为应用的不同，如果这个部分的参数的某些值很小，也是可以认为没 有问题的；如在数据仓库环境，hard parse因为使用了物化视图或histogram而变得很高。所以，重要的是，我们需要把这部分信息和正常时候的AWR报告做比较来判断是否有问题。

---