澳门新葡亰娱乐官网DBA_Oracle Event等待事件分析(概念)

澳门新葡亰娱乐官网 5

这个案例很有代表性,如果不深入到细节中,很容易在中途得出错误的结论。下面详细描述思考过程,希望能给大家带来些启发。

InnoDB Adaptive Hash Index

显示了“自适应哈希索引”的使用情况,哈希索引只能用来搜索等值的查询.

# Hash table size 17700827, node heap has 35112 buffer(s)
# 3577.42 hash searches/s, 507.49 non-hash searches/s
  • Hash Index Cells Total
    自适应哈希表的槽数=innodb_buffer_pool_size/256
  • Hash Index Cells Used 用到自适应哈希表的查询个数

2014-12-18 Created By
BaoXinjian

曾经遇到过这样的应用,要求对用户的登录、退出行为做记录。此部分的逻辑很简单,用户每登录一次应用,向数据库中一个日志表中插入一行,退出应用的时候再向日志表中插入一行。

InnoDB Buffer Pool Activity

显示Innodb缓冲池的内部活动,页的创建,读取,写入.如果有突然的增加或者减少,需要检查应用

  • Pages Created
  • Pages Read
  • Pages Written

澳门新葡亰娱乐官网 1一、摘要

此日志表是个日分区表,每天一个分区。每天大约会插入千万行,除了插入并发很高以外,就没有其他的大并发操作。另外,每天晚上会将当天的数据推送到数据仓库,在数据仓库中再进行分析、对比。

InnoDB Buffer Pool

  • Pool Size InnoDB缓冲池的页数量,每页大小16K
  • Database Pages 数据页大小
  • Free Pages 空闲页大小
  • Modified Pages “脏”数据页.如果脏数据页太多,则需要检查磁盘IO状态.

项目上线后,有些用户反映登录变慢了。而且,只有上午八九点钟左右的时候慢,过了这一段时间就没有用户反映有问题。经过对比
AWR,发现变慢是不定时的,从 8 点开始,到 9
点左右为止,在半小时一次的报告中,偶尔会有那么一两份 AWR 会显示
BufferBusy Waits 比较高,然后就正常了。

InnoDB Checkpoint Age

  • Uncheckpointed Bytes
    显示了未写入磁盘的数据量.如果该值的大小接近innodb_log_file_size *
    n
    的总大小,则需要增加innodb_log_file_size的值,但是要注意,如果发生宕机,则需要更长的回复时间.(从redolog恢复)

Oracle的等待事件是衡量Oracle运行状况的重要依据及指标。

看到这个情况,很容易让人认为是某个时间段有很多人一起在访问同一张表,其他时间又不一起访问了。究竟是不是这么回事呢?

InnoDB Current Lock Waits

  • InnoDB Lock Wait Secs
    显示每秒处于锁等待的innodb事务总数.如果有一个非常大的值,应该检查LOCK
    WAIT transactions,请看下面的模板

等待事件的概念是在Oracle7.0.1.2中引入的,大致有100个等待事件。

先来确定一下等待是针对哪个对象。通过 V$SEGMENT_STATISTICS,查找
STATISTIC_NAME列为 buffer busy waits 的,或者,查看
V$ACTIVE_SESSION_HISTORY 中的历史等待事件,根据 P1、 P2
列的值,就可以定位争用是针对哪个对象的。

InnoDB I/O

  • File Reads 显示每秒文件的读次数
  • File Writes 显示每秒文件的写次数
  • Log Writes 写日志的次数
  • File Fsyncs
    调用fsync()函数的次数.与innodb_flush_log_at_trx_commit值的设置有关.

在Oracle
8.0中这个数目增加到了大约150个,在Oracle8i中大约有200个事件,在Oracle9i中大约有360个等待事件。

根据文件号、块号查找的结果来看,绝大多数的 Buffer Busy Waits
都出现在日志表上。

InnoDB I/O Pending

显示了InnoDB挂起的同步或异步IO操作.如果挂起的操作太多,则需要更大的RAM,更大的缓冲池,更快的磁盘.

  • Pending Aio Log Ios
  • Pending Aio Sync Ios
  • Pending Buf Pool Flushes
  • Pending Chkp Writes
  • Pending Ibuf Aio Reads
  • Pending Log Flushe
  • Pending Log Writes
  • Pending Normal Aio Reads
  • Pending Normal Aio Writes

 

日志表每天分区的数据量最高接近千万行,就按每天 1000 万行算,除以 3600×
24,平均每秒 116
个并发插入。当然,还要考虑高低峰的问题,晚上应用基本上没什么人用的,这几百万行大部分都是白天插入的。所以,再乘个
2,每秒 232 的插入量,这是最高的了。也并不是很多,这点量和 Oracle 宣称的
ASSM 支持的高并发插入相比,应该不会有 Buffer Busy Waits。

InnoDB Insert Buffer

插入缓冲,并不是缓存的一部分,而是物理页,对于非聚集索引的插入或更新操作,不是每一次直接插入索引页.而是先判断插入的非聚集索引页是否在缓冲池中.如果在,则直接插入,如果不再,则先放入一个插入缓冲区中.然后再以一定的频率执行插入缓冲和非聚集索引页子节点的合并操作.
使用条件:非聚集索引,非唯一

  • Ibuf Inserts
    插入的记录数
  • Ibuf Merged
    合并的页的数量
  • Ibuf Merges
    合并的次数
    如果merges/merged的值等于3/1,则代表插入缓冲对于非聚集索引页的IO请求大约降低了3倍

澳门新葡亰娱乐官网 2二、等待事件分类

但无论如何, Buffer Busy Waits
是产生了,有可能以主机的硬件来论,现在已经是并发插入量的极限了。但奇怪的是,这种情况每天只会在刚上班后不久出现,其他时段正常。

InnoDB Insert Buffer Usage

  • Ibuf Cell Count
    分段大小
  • Ibuf Used Cells
    插入缓冲区的大小
  • Ibuf Free Cells
    “自由列表”的长度

难道是刚上班时向日志表的插入量高?

InnoDB Internal Hash Memory Usage

显示了InnoDB内部各种哈希结构(不可人工干预),占用的内存大小.

  • Adaptive Hash Memory
    自适应哈希索引占用的内存大小
  • Page Hash Memory
  • Dictionary Cache Memory
  • File System Memory
  • Lock System Memory
  • Recovery System Memory
  • Thread Hash Memory

主要有两种类别的等待事件,即空闲(idle)等待事件和非空闲(non-idle)等待事件。

但统计的结果显示,白天有好几个时段,日志表的插入量都很大,并不是早上上班时段特别大,有时下午还会比上午插入的稍多些,但没有发现下午日志表上有
Buffer
BusyWaits,下午也从来没人反映过慢,而且整库的压力上下午基本差不多。如果全天都有
Buffer Busy
Waits,我想我也会放弃进一步调查。但有时下午的插入量多,反而没有等待。那说明
ASSM
是足以支撑这个量级的并发插入的。想解决问题的话,第一步是定位问题,这我们都知道。可如何定位这个问题呢?

InnoDB Lock Structures

  • InnoDB Lock Structs
    该图形显示了在innodb内部有多少锁结构(不是死锁).这大致与当前事务锁住的行数有关系.可以用来判断是否存在锁争用.
    对于数量多少算好或者算坏,没有硬性的规定.实际情况下,大量的事务在等待锁,很明显,该值越小越好.

    这个数据来源是SHOW ENGINE INNODB STATUS;
    # 23 lock struct(s), heap size 3024, undo log entries 27
    # LOCK WAIT 12 lock struct(s), heap size 3024, undo log entries 5
    # LOCK WAIT 2 lock struct(s), heap size 368
    

1.
空闲事件指Oracle正等待某种工作,在诊断和优化数据库的时候,我们不用过多注意这部分事件。

InnoDB Log

相关变量:innodb_log_buffer_size

  • InnoDB Log Buffer Size
  • Log Bytes Written
    Log sequence number
    当前日志的位置
  • Log Bytes Flushed
    Log flushed up to
    日志已经刷新的位置
  • Unflushed Log
    是log_bytes_written与log_bytes_flushed的差值,表示日志缓存区里还有多少数据没被刷新到日志文件里.
    如果这个差值超过了innodb_log_buffer_size设置的30%,则需要考虑是否加大该参数值.
  •  dispatcher timer
  •  lock element cleanup
  •  Null event
  •  parallel query dequeue wait
  •  parallel query idle wait –
    Slaves
  •  pipe get
  •  PL/SQL lock timer
  •  pmon timer- pmon
  •  rdbms ipc message
  •  slave wait
  •  smon timer
  •  SQL*Net break/reset to
    client
  •  SQL*Net message from client
  •  SQL*Net message to client
  •  SQL*Net more data to client
  •  virtual circuit status
  •  client message

InnoDB Memory Allocation

Total memory allocated 8824815616; in additional pool allocated 0
  • Total Mem Alloc
    InnoDB申请的总内存量,单位字节
  • Additional Pool Alloc
    分配给额外内存的总量,单位字节

2.
非空闲等待事件专门针对Oracle的活动,指数据库任务或应用运行过程中发生的等待,这些等待事件是我们在调整数据库的时候应该关注与研究的。

InnoDB Row Lock Time

  • InnoDB Row Lock Time
    该模板读取的Innodb_row_lock_time状态变量,表示InnoDB引擎在每次申请数据行锁定时等待的总时间(以毫秒为单位).
  •  db file scattered read
  •  db file sequential read
  •  buffer busy waits
  •  free buffer waits
  •  enqueue
  •  latch free
  •  log file parallel write
  •  log file sync

InnoDB Row Lock Waits

  • InnoDB Row Lock Waits
    读取的Innodb_row_lock_waits状态变量,表示InnoDB经过这么长时间才获得一个行锁.(毫秒)

 

InnoDB Row Operations

Number of rows inserted 50678311, updated 66425915, deleted 20605903, read 454561562

大致能表现InnoDB内部的操作

  • Row Read
  • Row Deleted
  • Row Updated
  • Row Inserted

澳门新葡亰娱乐官网 3三、查询视图

InnoDB Semaphores Wait Time

  • InnoDB Sem Wait Time Ms
    显示当前正在等待互斥量的InnoDB线程的等待时间的总耗时(毫秒).
    分析:
    正常情况下,InnoDB Semaphores Wait Time和 InnoDB Semaphores
    Waits应该是空的.除非服务器运行着高并发的工作负载,它促使InnoDB采取让操作系统等待的措施.信息位于SHOW
    ENGINE INNODB STATUS的SEMAPHORES片段.
    相关php脚本代码部分如下:

    elseif (strpos($line, 'seconds the semaphore:') > 0) {
    # --Thread 907205 has waited at handler/ha_innodb.cc line 7156 for 1.00 seconds the semaphore:
    increment($results, 'innodb_sem_waits', 1);
    increment($results, 'innodb_sem_wait_time_ms', to_int($row[9]) * 1000);
    }
    

    其中innodb_sem_waits的值是多少,表示有多少线程在等待,而innodb_sem_wait_time_ms表示所有线程等待的时间,默认单位是秒,在脚本中乘以1000,所以监控图中的单位是毫秒.

  • 应对这个问题

    InnoDB采取多阶段等待策略.首先,尝试对锁进行循环等待.如果经过了一个预设的循环等待周期(innodb_sync_spin_loops

    30,当前配置文件默认为30次)之后还没有成功,就会退到更昂贵更复杂的等待阵列里,如果并发量太大的话,则导致系统负载突增.
    循环等待的成本相对比较低,但是需要不停地检查一个资源是否被锁定,消耗CPU周期,也就是说,当另外一条线程能处理事情时,循环等待也会独占处理器.
    循环等待的替换方案就是让操作系统做上下文切换(等待阵列),每秒钟几千次的切换会引发大量的系统开销.

  • 解决办法
    根据具体的应用,调整参数;或者优化应用,减少并发.

InnoDB Semaphores Waits

  • InnoDB Sem Waits
    显示当前正在等待互斥量的InnoDB线程的数量.

  • InnoDB Semaphores
    显示innodb内部的信号活动状态.
    包括Mutex spin waits,RW-shared spins,RW-excl
    spins等各种信号量的数量总和.

  • Spin Rounds
    InnoDB内部预设的互斥量信号数量

  • Spin Waits
    InnoDB内部对锁进行循环等待的数量(与innodb_sync_spin_loops参数有关)
  • Os Wait系统等待
    事务退到操作系统的等待阵列的数量
    在高并发的情况,会发现这个值有尖峰状,不稳定.图像主要显示了,不合理的设计情况下,不同的连接类型之间的行锁或互斥锁的争用.

1.
查看v$event_name视图的字段结构:

InnoDB Tables In Use

# mysql tables in use 2, locked 2
  • InnoDB Tables In Use
    所有事务用到的表的数量
  • InnoDB Locked Tables
    所有事务锁定的表的数量

SQL> desc v$event_name;

InnoDB Transactions Active/Locked

该图显示了InnoDB事务的状态数量.

  • Active Transactions
    正在执行的事务数量
  • Locked Transactions
    锁住的事务数量
  • Current Transactions
    当前的事务数量(包括not started,ACTIVE,…等各种状态)
    not started # 事务已经提交到磁盘
    ACTIVE # 正在执行的事务
  • Read Views(待更新)
    read views open inside InnoDB
名称                   是否为空? 类型
 ----------------------------------------- -----------------------
 EVENT#                NUMBER
 EVENT_ID              NUMBER
 NAME                 VARCHAR2(64)
 PARAMETER1          VARCHAR2(64)
 PARAMETER2          VARCHAR2(64)
 PARAMETER3          VARCHAR2(64)
 WAIT_CLASS_ID        NUMBER
 WAIT_CLASS#          NUMBER
 WAIT_CLASS           VARCHAR2(64)

InnoDB Transactions

显示了InnoDB事务相关的信息

  • InnoDB Transactions
    InnoDB内部的事务总数.由以下数值计算出:
    Trx id counter 89F56195 # 当前事务ID,每创建一个新事务就会累加

    Purge done for trx's n:o < 89F5609C undo n:o < 0 -- InnoDB清除旧版本MVCC时所用的事务ID.这个ID之前的老版本数据已经清除.
    

    该数值就是由当前事务ID减去清除旧数据的事务ID再由十六进制转成十进制的值.(参考ss_get_mysql_stats.php脚本902行)

  • History List
    历史记录的长度.位于InnoDB数据文件的撤销空间里的未清除事务的数目.当一个事务执行了更新并提交后,这个数字就会累加,当清除进程移除一个旧版本数据时,它就会递减.

 

MyISAM Indexs

显示了在MyISAM索引上的读写情况

  • Key Reads Requests
    从键缓存读出索引块的读操作的次数
  • Key Reads
    从磁盘读出索引块的读操作次数
  • Key Write Requests
    向键缓存写一个索引块的请求的个数
  • Key Writes
    把索引块写入磁盘的写操作的次数

2.  查看等待事件总数:

MyISAM Key Cache

  • Key Buffer Size
    键缓存大小
  • Key Buf Bytes Used
    同Key_blocks_used变量
    键缓存里已经被使用的缓存块的个数
  • Key Buf Bytes Unused
    同Key_blocks_unused
    键缓存里尚未被使用过的缓存块的个数
SQL> select count(*) from v$event_name;
  COUNT(*)
----------
      1116

MySQL Binary/Relay Logs

  • Binlog Cache Use
    保存在二进制日志缓存里的事务的个数
  • Binlog Cache Disk Use
    超过binlog_cache_size设置的缓存大小,使用磁盘临时文件的事务的个数
  • Binlog Log Space
    二进制日志的大小
  • Relay Log Space
    中继日志的大小
    如果Binlog Cache Disk Use/Binlog Cache
    Use的值较大,那么应该尝试增加binlog_cache_size的大小.但是,也不要期望改善过多,如果写临时文件的数量从每秒1个减少到每分钟一个,这已经证明优化的足够好了.没必要耗费大量的内存,来处理binlog_cache_size中的事务.

3.  查看等待事件分类情况

MySQL Command Counts

命令计数器,显示了MySQL(在过去1秒内)执行各种命令的次数

  • Questions
    记录了服务器收到的查询和命令的总数.(Com_*变量的总数不一定相等.)
  • Com Select
  • Com Delete
  • Com Insert
  • Com Update
  • Com Replace
  • Com Load
  • Com Delete Multi
  • Com Insert Select
  • Com Update Multi
  • Com Replace Select
SELECT   wait_class#,
           wait_class_id,
           wait_class,
           COUNT ( * ) AS "count"
    FROM   v$event_name
GROUP BY   wait_class#, wait_class_id, wait_class
ORDER BY   wait_class#;

WAIT_CLASS# WAIT_CLASS_IDWAIT_CLASS                count
----------- ------------- -------------------- ----------
          0    1893977003 Other                       717
          1    4217450380 Application                  17
          2    3290255840 Configuration                24
          3    4166625743 Administrative               54
          4    3875070507 Concurrency                  32
          5    3386400367 Commit                        2
          6    2723168908 Idle                         94
          7    2000153315 Network                      35
          8    1740759767 UserI/O                      45
          9    4108307767 SystemI/O                    30
         10    2396326234 Scheduler                     7
         11    3871361733 Cluster                      50
         12     644977587 Queueing                      9

MySQL Connections

  • Max Connections 允许同时保持在打开状态的客户连接的最大个数
  • Max Used Connections 此前曾同时打开处于打开状态的连接的最大个数
  • Aborted Clients 因客户端没有正确地关闭而被丢弃的连接的个数
  • Aborted Connects 试图连接MySQL服务器但没有成功的次数
  • Threads Connectd 现在正处于打开状态的连接的个数
  • Connections 试图连接MySQL服务器的尝试次数

 

MySQL Files and Tables

  • Table Cache
  • Open Tables 当前处于打开状态的数据表的个数.不包括TEMPORARY
  • Open Files
    当前处于打开状态的文件的个数,如果与open_files_limit接近,则应该加大open_files_limit的值.
  • Opened Tables
    MySQL服务器已打开的数据表总数(包括显式定义的临时表).如果这个值很高,应该慎重考虑,是否加大数据表缓存(table_open_cache).

4.  相关的几个视图:

MySQL Handler

  • Handler_writer 向数据表里插入一个数据行的请求的个数
  • Handler_update 对数据表里的一个数据行进行修改的请求的个数
  • Handler_delete 从数据表删除一个数据行的请求的个数
  • Handler_read_first 读取索引中第一个索引项的请求的个数
  • Handler_read_key 根据一个索引值而读取一个数据行的请求的个数
  • Handler_read_next 按索引顺序读取下一个数据行的请求的个数
  • Handler_read_prev 按索引逆序读取前一个数据行的请求的个数
  • Handler_read_rnd 根据某个数据行的位置而读取该数据行的请求的个数
  • Handler_read_rnd_next
    读取下一个数据行的请求的个数.如果这个数字很高,就说明有很多语句需要通过全表扫描才能完成或有很多查询没有使用适当的索引

V$SESSION:
代表数据库活动的开始,视为源起。

MySQL Network Traffic

  • Bytes Send 发送字节数
  • Bytes Received 收到字节数

V$SESSION_WAIT:
视图用以实时记录活动SESSION的等待情况,是当前信息。

MySQL Processlist

  • State Closing Tables
    该线程将已更改的表数据刷新到磁盘,并关闭已使用的表。这应该是一个快速的操作。如果没有,请确认您没有一个完整的磁盘,并且磁盘没有非常重的使用。

  • State Copying To Tmp Table
    服务器正在复制到内存中的临时表

  • State End
    这发生创建视图、删除、插入、查询或更新语句之最后,但是在修改ALTER
    TABLE之前

  • State Freeing Items
    线程执行了一个命令。在此状态下完成的一些项目将涉及查询缓存。这个状态通常会被清理干净

  • State Init
    这发生在ALTER
    TABLE、DELETE、INSERT、SELECT或UPDATE语句的初始化之前。在这个状态下,服务器所采取的操作包括刷新二进制日志、InnoDB日志和一些查询缓存清理操作。
    对于最终状态,以下操作可能会发生:
    删除表中的数据之后删除查询缓存条目
    将事件写入二进制日志
    释放内存缓冲区,包括blob

  • State Locked
    查询被另一个查询锁定。
    在MySQL
    5.5.3中,这个状态被删除了,因为它相当于表锁状态,不再出现在SHOW
    PROCESSLIST输出中。

  • State Login
    登录
    连接线程的初始状态,直到客户机成功地进行了身份验证。

  • State Preparing
    这个状态发生在查询优化期间。

  • State Reading From Net
    服务器正在从网络读取一个数据包。

  • State Sengding Data
    线程正在读取和处理SELECT语句的行,并将数据发送给客户机。因为在这个状态期间发生的操作往往会执行大量的磁盘访问(读),所以在给定查询的生命周期中,它通常是运行时间最长的状态。

  • State Sorting Result
    对于SELECT语句,这类似于创建排序索引,但对于非临时表

  • State Statistics
    服务器正在计算统计数据,以开发查询执行计划。如果一个线程在这个状态中存在很长时间,那么服务器可能会执行其他的工作。

  • State Updating
    线程正在寻找更新的行,并正在更新它们

  • State Writing To Net
    服务器正在向网络写入一个包。

  • State None
    什么都没有的,空的,注意不是NULL状态

  • State Other
    其他

V$SESSION_WAIT_HISTORY:
是对V$SESSION_WAIT的简单增强,记录活动SESSION的最近10次等待。

MySQL Query Cache

V$SQLTEXT: 
当数据库出现瓶颈时,通常可以从V$SESSION_WAIT找到那些正在等待资源的SESSION,通过SESSION的SID,联合V$SESSION和V$SQLTEXT视图就可以捕获这些SESSION正在执行的SQL语句。

MySQL Query Cache Memory

V$ACTIVE_SESSION_HISTORY:
是ASH的核心,用以记录活动SESSION的历史等待信息,每秒采样一次,这部分内容记录在内存中,期望值是记录一个小时的内容。

MySQL Query Response Time (Microseconds)

Percona文档

WRH#_ACTIVE_SESSION_HISTORY:
是V$ACTIVE_SESSION_HISTORY在AWR的存储地。

MySQL Query Time Histogram(Count)

Percona文档

V$ACTIVE_SESSION_HISTORY:
中的信息会被定期(每小时一次)的刷新到负载库中,并缺省保留一个星期用于分析。

MySQL Replication

默认用SHOW SLAVE STATUS命令获取各状态值

  • Slave Running 从服务器的I/O线程和SQL线程是否在运行
  • Slave Stopped
  • Slave Lag 复制延迟
  • Slave Open Tmp Tables 从服务器中的SQL线程曾经打开的临时文件的个数
  • Slave Retried Transactions
    从服务器中的SQL线程重新尝试执行一个事务的次数

DBA_HIST_ACTIVE_SESS_HISTORY:
视图是WRH#_ACTIVE_SESSION_HISTORY视图和其他几个视图的联合展现,通常通过这个视图进行历史数据的访问。

MySQL Select Types

  • Select Full
    Join没有使用索引而完成的多表联接操作的次数.这种情况是性能杀手,最好去优化sql.
  • Select Full Range Join利用一个辅助性的参照表(reference
    table)上的区间搜索(range
    search)操作而完成的多数据表联接操作的次数.
    该值表示使用了范围查询联接表的次数.
  • Select
    Range利用第一个数据表上的某个区间而完成的多数据表联接操作的次数.
  • Select Range
    Check该变量记录了在联接时,对每一行数据重新检查索引的查询计划的数量,它的开销很大.
    如果该值较高或正在增加,说明一些查询没有找到好索引.
  • Select
    Scan通过对第一个数据表进行全表扫描而完成的多数据表联接操作的次数.

V$SYSTEM_EVENT:
由于V$SESSION记录的是动态信息,和SESSION的生命周期相关,而并不记录历史信息,所以ORACLE提供视图V$SYSTEM_EVENT来记录数据库自启动以来所有等待事件的汇总信息。通过这个视图,用户可以迅速获得数据库运行的总体概况。

MySQL Sorts

  • Sort Rows 对多少行排序
  • Sort Range 利用一个区间进行的排序操作的次数
  • Sort Merge Passes
    查询导致了文件排序的次数.可以优化sql或者适当增加sort_buffer_size变量
  • Sort Scan 利用一次全表扫作而完成的排序操作的次数

 

MySQL Table Locks

  • Table Locks Immediate
    无需等待就能够立刻得到满足的数据表锁定请求的个数
  • Table Locks Waited
    显示了有多少表被锁住了并且导致服务器级的锁等待(存储引擎级的锁,如InnoDB行级锁,不会使该变量增加).
    如果这个值比较高或者正在增加,那么表明存在严重的并发瓶颈.
  • Slow Queries 慢查询的次数(执行时间超过long_query_time值)

澳门新葡亰娱乐官网 4四、等待事件

MySQL Temporary Objects

  • Created_tmp_tables
    MySQL服务器在对SQL查询语句进行处理时在内存里创建的临时数据表的个数.
    如果该值太高,唯一的解决办法是:优化查询语句.
  • Created_tmp_disk_tables
    MySQL服务器在对SQL查询语句进行处理时在磁盘上创建的临时数据表的个数,如果这个值比较高,可能的原因:
    a.查询在选择BLOB或者TEXT列的时候创建了临时表
    b.tmp_table_size和max_heap_table_size的值也许太小
  • Created_tmp_files MySQL服务器所创建的临时文件的个数

MySQL Threads

  • Thread Cache Size
    线程缓存所能容纳的线程的最大个数.断开的mysql连接会放到这个缓存里,新建立的连接就会重复使用它们而不创建新的线程.
    如果缓存中有自由的线程,MySQL就能很快的响应连接请求,不必为每个连接都创建新的线程.每个在缓存中的线程通常消耗256KB内存.
  • Thread Created 为处理连接创建的线程总数
  1. db file scattered read-DB
    文件分散读取

MySQL Transaction Handler

  • Handler Commit 提交一个事务的请求的个数
  • Handler Rollback 回滚一个事务的请求的个数
  • Handler Savepoint 创建一个事务保存点的请求的个数
  • Handler Savepoint Rollback 回滚到一个事务保存点的请求的个数.

这种情况通常显示与全表扫描相关的等待。当数据库进行全表扫时,基于性能的考虑,数据会分散(scattered)读入Buffer
Cache。如果这个等待事件比较显著,可能说明对于某些全表扫描的表,没有创建索引或者没有创建合适的索引,我们可能需要检查这些数据表已确定是否进行了正确的设置。

然而这个等待事件不一定意味着性能低下,在某些条件下Oracle
会主动使用全表扫描来替换索引扫描以提高性能,这和访问的数据量有关,在CBO
下Oracle 会进行更为智能的选择,在RBO 下Oracle 更倾向于使用索引。

因为全表扫描被置于LRU(Least Recently
Used,最近最少适用)列表的冷端(cold
end),对于频繁访问的较小的数据表,可以选择把他们Cache
到内存中,以避免反复读取。

当这个等待事件比较显著时,可以结合v$session_longops
动态性能视图来进行诊断,该视图中记录了长时间(运行时间超过6
秒的)运行的事物,可能很多是全表扫描操作(不管怎样,这部分信息都是值得我们注意的)。

 

  1. db file sequential read-DB
    文件顺序读取。

这一事件通常显示与单个数据块相关的读取操作(如索引读取)。如果这个等待事件比较显著,可能表示在多表连接中,表的连接顺序存在问题,可能没有正确的使用驱动表;或者可能说明不加选择地进行索引。

在大多数情况下我们说,通过索引可以更为快速的获取记录,所以对于一个编码规范、调整良好的数据库,这个等待很大是很正常的。但是在很多情况下,使用索引并不是最佳的选择,比如读取较大表中大量的数据,全表扫描可能会明显快于索引扫描,所以在开发中我们就应该注意,对于这样的查询应该进行避免使用索引扫描。

 

  1. Free Buffer-释放缓冲区

这个等待事件表明系统正在等待内存中的可用空间,这说明当前Buffer
中已经没有Free 的内存空间。如果应用设计良好,SQL
书写规范,充分绑定变量,那这种等待可能说明Buffer Cache
设置的偏小,你可能需要增大DB_BUFFER_CACHE。

Free Buffer 等待可能说明DBWR
的写出速度不够,或者磁盘存在严重的竞争,可以需要考虑增加检查点、使用更多的DBWR
进程,或者增加物理磁盘的数量,分散负载,平衡IO。

 

  1. Buffer Busy-缓冲区忙

该等待事件表示正在等待一个以unshareable方式使用的缓冲区,或者表示当前正在被读入buffer
cache。一般来说Buffer Busy
Wait不应大于1%。检查缓冲等待统计部分(或V$WAITSTAT),看一下等待是否位于段头(Segment
Header)。如果是,可以考虑增加自由列表(freelist,对于Oracle8i
DMT)或者增加freelist
groups(在很多时候这个调整是立竿见影的,在8.1.6之前,这个freelists参数不能动态修改;在8.1.6及以后版本,动态修改feelists需要设置COMPATIBLE至少为8.1.6).

如果这一等待位于undo
header,可以通过增加回滚段(rollback
segment)来解决缓冲区的问题。如果等待位于undo
block上,我们可能需要检查相关应用,适当减少大规模的一致性读取,或者降低一致性读取(consistent
read)的表中的数据密度或者增大DB_CACHE_SIZE。

如果等待处于data
block,可以考虑将频繁并发访问的表或数据移到另一数据块或者进行更大范围的分布(可以增加pctfree值
,扩大数据分布,减少竞争),以避开这个”热点”数据块,或者可以考虑增加表中的自由列表或使用本地化管理的表空间(Locally
Managed Tablespaces)。

如果等待处于索引块,应该考虑重建索引、分割索引或使用反向键索引。为了防止与数据块相关的缓冲忙等待,也可以使用较小的块:在这种情况下,单个块中的记录就较少,所以这个块就不是那么”繁忙”;或者可以设置更大的pctfree,使数据扩大物理分布,减少记录间的热点竞争。

在执行DML (insert/update/
delete)时,Oracle向数据块中写入信息,对于多事务并发访问的数据表,关于ITL的竞争和等待可能出现,为了减少这个等待,可以增加initrans,使用多个ITL槽。在Oracle9i
中,引入了一个新概念:ASSM(Segment Space Management
Auto)。通过这个新特性Oracle 使用位图来管理空间使用。

ASSM 结合LMT 彻底改变了Oracle
的存储机制,位图freelist 能够减轻缓冲区忙等待(buffer busy
wait),这个问题在Oracle9i 以前的版本里曾是一个严重的问题。

Oracle 宣称ASSM 显著地提高了DML
并发操作的性能,因为(同一个)位图的不同部分可以被同时使用,这样就消除了寻找剩余空间的串行化。根据Oracle
的测试结果,使用位图freelist
会消除所有分段头部(对资源)的争夺,还能获得超快的并发插入操作。在Oracle9i
之中,Buffer Busy wait 不再常见!

 

  1. latch free-latch 释放

latch是一种低级排队机制,用于保护SGA中共享内存结构。latch就像是一种快速地被获取和释放的内存锁。用于防止共享内存结构被多个用户同时访问。如果latch不可用,就会记录latch释放失败(latch
free miss )。有两种与闩有关的类型:

  • 立刻
  • 可以等待

假如一个进程试图在立刻模式下获得闩,而该闩已经被另外一个进程所持有,如果该闩不能立可用的话,那么该进程就不会为获得该闩而等待。它将继续执行另一个操作。

大多数latch问题都与以下操作相关:

没有很好的是用绑定变量(library cache
latch)、重作生成问题(redo allocation latch)、缓冲存储竞争问题(cache
buffers LRU chain),以及buffer cache中的存在”热点”块(cache buffers
chain)。

通常我们说,如果想设计一个失败的系统,不考虑绑定变量,这一个条件就够了,对于异构性强的系统,不使用绑定变量的后果是极其严重的。

另外也有一些latch等待与bug有关,应当关注Metalink相关bug的公布及补丁的发布。当latch
miss ratios大于0.5%时,就应当研究这一问题。

Oracle的latch机制是竞争,其处理类似于网络里的CSMA/CD,所有用户进程争夺latch,
对于愿意等待类型(willing-to-wait)的latch,如果一个进程在第一次尝试中没有获得latch,那么它会等待并且再尝试一次,如果经过_spin_count次争夺不能获得latch,
然后该进程转入睡眠状态,持续一段指定长度的时间,然后再次醒来,按顺序重复以前的步骤.在8i/9i中默认值是_spin_count=2000。

如果SQL语句不能调整,在8.1.6版本以上,Oracle提供了一个新的初始化参数:
CURSOR_SHARING可以通过设置CURSOR_SHARING = force
在服务器端强制绑定变量。设置该参数可能会带来一定的副作用,对于Java的程序,有相关的bug,具体应用应该关注Metalink的bug公告。

 

  1. Log Buffer Space-日志缓冲空间

当你将日志缓冲(log
buffer)产生重做日志的速度比LGWR 的写出速度快,或者是当日志切换(log
switch)太慢时,就会发生这种等待。这个等待出现时,通常表明redo log buffer
过小,为解决这个问题,可以考虑增大日志文件的大小,或者增加日志缓冲器的大小。

另外一个可能的原因是磁盘I/O
存在瓶颈,可以考虑使用写入速度更快的磁盘。在允许的条件下设置可以考虑使用裸设备来存放日志文件,提高写入效率。在一般的系统中,最低的标准是,不要把日志文件和数据文件存放在一起,因为通常日志文件只写不读,分离存放可以获得性能提升。

 

  1. Log File Switch-日志文件切换

当这个等待出现时,表示所有的提交(commit)的请求都需要等待”日志文件切换”的完成

Log file Switch
主要包含两个子事件:

log file switch (archiving needed)

log file switch (checkpoint
incomplete)

log file switch (archiving
needed)

这个等待事件出现时通常是因为日志组循环写满以后,第一个日志归档尚未完成,出现该等待。出现该等待,可能表示io
存在问题。解决办法:

可以考虑增大日志文件和增加日志组

移动归档文件到快速磁盘

调整log_archive_max_processes .

log file switch (checkpoint
incomplete)-日志切换(检查点未完成)

当你的日志组都写完以后,LGWR
试图写第一个log file,如果这时数据库没有完成写出记录在第一个log file
中的dirty 块时(例如第一个检查点未完成),该等待事件出现。

该等待事件通常表示你的DBWR
写出速度太慢或者IO 存在问题。

为解决该问题,你可能需要考虑增加额外的DBWR
或者增加你的日志组或日志文件大小。

 

  1. log file sync-日志文件同步

当一个用户提交或回滚数据时,LGWR
将会话期的重做由日志缓冲器写入到重做日志中。日志文件同步过程必须等待这一过程成功完成。为了减少这种等待事件,可以尝试一次提交更多的记录(频繁的提交会带来更多的系统开销)。将重做日志置于较快的磁盘上,或者交替使用不同物理磁盘上的重做日志,以降低归档对LGWR的影响。

对于软RAID,一般来说不要使用RAID 5,RAID5
对于频繁写入得系统会带来较大的性能损失,可以考虑使用文件系统直接输入/输出,或者使用裸设备(raw
device),这样可以获得写入的性能提高。

 

  1. log file single
    write该事件仅与写日志文件头块相关,通常发生在增加新的组成员和增进序列号时。

头块写单个进行,因为头块的部分信息是文件号,每个文件不同。更新日志文件头这个操作在后台完成,一般很少出现等待,无需太多关注。

 

  1. log file parallel write

从log buffer 写redo 记录到redo log
文件,主要指常规写操作(相对于log file sync)。如果你的Log group
存在多个组成员,当flush log buffer
时,写操作是并行的,这时候此等待事件可能出现。

尽管这个写操作并行处理,直到所有I/O
操作完成该写操作才会完成(如果你的磁盘支持异步IO或者使用IO
SLAVE,那么即使只有一个redo log file member,也有可能出现此等待)。

这个参数和log file sync
时间相比较可以用来衡量log file 的写入成本。通常称为同步成本率。

 

  1. control file parallel
    write-控制文件并行写

当server
进程更新所有控制文件时,这个事件可能出现。如果等待很短,可以不用考虑。如果等待时间较长,检查存放控制文件的物理磁盘I/O
是否存在瓶颈。

多个控制文件是完全相同的拷贝,用于镜像以提高安全性。对于业务系统,多个控制文件应该存放在不同的磁盘上,一般来说三个是足够的,如果只有两个物理硬盘,那么两个控制文件也是可以接受的。在同一个磁盘上保存多个控制文件是不具备实际意义的。减少这个等待,可以考虑如下方法:

减少控制文件的个数(在确保安全的前提下)

如果系统支持,使用异步IO

转移控制文件到IO 负担轻的物理磁盘

 

  1. control file sequential read/ control
    file single write

控制文件连续读/控制文件单个写对单个控制文件I/O
存在问题时,这两个事件会出现。如果等待比较明显,检查单个控制文件,看存放位置是否存在I/O
瓶颈。

 

  1. direct path
    write-直接路径写该等待发生在,系统等待确认所有未完成的异步I/O
    都已写入磁盘。

对于这一写入等待,我们应该找到I/O
操作最为频繁的数据文件(如果有过多的排序操作,很有可能就是临时文件),分散负载,加快其写入操作。

如果系统存在过多的磁盘排序,会导致临时表空间操作频繁,对于这种情况,可以考虑使用Local管理表空间,分成多个小文件,写入不同磁盘或者裸设备。

 

  1. Idle Event-空闲事件

最后我们来看几个空闲等待事件。一般来说,空闲等待是指系统因为无事可做的等待,或者等待用户的请求或响应等,通常我们可以忽略这些等待事件。空闲事件可以通过stats$idle_event
表查询得到。

我们看一下系统的主要空闲等待事件,对这些事件大家应该有个大致的印象,如果你的Top
5
等待事件中,主要都是这些事件,那么一般来说你的系统是比价清闲的。

 

Thanks and Regards

参考:RuleV5 –

澳门新葡亰娱乐官网 5

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图