检查数据库表空间使用情况 mysql 如何检查数据库性能

转载

mob6454cc73e9a6 2023-12-21 09:58:29

文章标签 检查数据库表空间使用情况 mysql 数据库运维操作系统 SQL 文章分类 MySQL 数据库

在系统稳定之后，应该按照本指导方案每个月检查一次产品数据库。

该指导方案适用于Oracle9i数据库，因为有些脚本在9i中才可以运行。

检查方式均为以sysdba身份登录数据库以后在SQLPLUS中执行命令脚本（每小节的“检查方法”部分有详细的命令脚本）。

登陆数据库的命令：

sqlplus “sys/password as sysdba”

一．内存性能评估

在内存性能评估的时候，我们使用内存性能指数（MPI, Memory Performance Index），下表列出了MPI中的各项指数，这个评分系统并不意味着对内存的使用和分配的全方位评估，而只是代表一个晴雨表，反映当前系统内存的使用和分配状况。

MPI指数

分类
所需等级
最高分

缓冲区命中率（Buffer Cache）
>98%
30

数据字典命中率（Dictionary Cache）
>98%
30

库缓存命中率（Library Cache）
>98%
30

内存中的排序（Sort in Memory）
>98%
30

空闲的数据缓冲区比例
10-25%
30

使用最多的前10个SQL占用的内存
<5%
60

是否已经调整使用最多的前25个SQL
是
30

是否尝试固定高速缓存中经常使用的对象
是
10

MPI指数
总分
250

1．缓冲区命中率

显示了对于数据总读取量而言，非磁盘读取（缓冲区命中）的百分比。当然，十分高的命中率并不代表数据库性能一定优良，也有可能是糟糕的SQL引起了大量的缓冲区读操作，只有在已经调整过首要的查询之后，这个命中率才能更好地反映数据库性能。

检查方法：

select (1 - (sum(decode(name, 'physical reads', value, 0)) /
       (sum(decode(name, 'db block gets', value, 0)) +
       sum(decode(name, 'consistent gets', value, 0))))) * 100
       "Hit Ratio"
   from v$sysstat;

评估准则：

等级
分数

<90%
0

90-94%
10

95-98%
20

>98%
30

2．数据字典命中率

显示了对数据字典和其它对象的内存读操作的百分比。

检查方法：

select (1 - (sum(getmisses) / sum(gets))) * 100 "Hit Ratio"
   from v$rowcache;

评估准则：

等级
分数

<85%
0

86-92%
10

92-98%
20

>98%
30

3．库缓存命中率

显示了对SQL和PL/SQL对象的内存读操作的百分比。同样注意，很高的命中率并不总是反映数据库性能优秀。

检查方法：

select sum(pins) / (sum(pins) + sum(reloads)) * 100 "Hit Ratio"
   from v$librarycache;

评估准则：

等级
分数

<90%
0

90-94%
10

94-98%
20

>98%
30

4．内存中的排序

根据初始化参数PGA_AGGREGATE_TARGET或者SORT_AREA_SIZE的值，用户的排序操作可能在内存中执行，也可能在临时表空间中执行。这个检查用以显示在内存中排序占总排序的百分比。

检查方法：

select a.value "Disk Sorts",
       b.value "Memory Sorts",
       round((100 * b.value) /
             decode((a.value + b.value), 0, 1, (a.value + b.value)),
             2) "Pct Memory Sorts"
   from v$sysstat a, v$sysstat b
where a.name = 'sorts (disk)'
   and b.name = 'sorts (memory)';

评估准则：

等级
分数

<90%
0

90-94%
10

94-98%
20

>98%
30

5．空闲的数据缓冲区比例

空闲的记录数除以X$BH表中的记录总数（即所分配的数据块缓冲区的总数）得到的空闲缓冲区百分比。同样注意，拥有众多空闲缓冲区的数据库不一定是最佳环境，因为可能是缓冲区设置过大，浪费内存。

检查方法：

select decode(state,
               0,
               'FREE',
               1,
               decode(lrba_seq, 0, 'AVAILABLE', 'BEING USED'),
               3,
               'BEING USED',
               state) "Block Status",
       count(*)
   from x$bh
group by decode(state,
                 0,
                 'FREE',
                 1,
                 decode(lrba_seq, 0, 'AVAILABLE', 'BEING USED'),
                 3,
                 'BEING USED',
                 state);

评估准则：

等级
分数

<5%
0

5-19%
30

20-25%
20

>25%
0

6．最浪费内存的前10个语句占全部内存读取量的比例

通常一个没有优化系统中，10个最常用的SQL语句的访问量会占到整个系统中内存读操作的50%以上。这些SQL是最需要进行优化的部分，也是优化工作中优先级很高的部分。

检查方法：

select sum(pct_bufgets)
   from (select rank() over(order by buffer_gets desc) as rank_bufgets,
               to_char(100 * ratio_to_report(buffer_gets) over(), '999.99') pct_bufgets
           from v$sqlarea)
where rank_bufgets < 11;

评估准则：

等级
分数

<5%
60

5-19%
50

20-25%
30

>25%
0

7．调整前25个最浪费内存的语句

在没有调整的情况下，绝大多数系统中，访问量占前25位的语句的内存读操作将占用整个系统所有内存读操作的75%，对这部分语句进行调整是至关重要的。这部分脚本用于获得访问量占前25位的SQL语句。

检查方法：

set serveroutput on size 1000000
declare
   top25 number;
   text1 varchar2(4000);
   x     number;
   len1   number;
   cursor c1 is
     select buffer_gets, substr(sql_text, 1, 4000)
       from v$sqlarea
     order by buffer_gets desc;
begin
   dbms_output.put_line('Gets' || '     ' || 'Text');
   dbms_output.put_line('--------' || ' ' || '---------------');
   open c1;
   for i in 1 .. 25 loop
     fetch c1
       into top25, text1;
     dbms_output.put_line(rpad(to_char(top25), 9) || ' ' ||
                         substr(text1, 1, 66));
     len1 := length(text1);
     x     := 66;
     while len1 > x - 1 loop
       dbms_output.put_line('"         ' || substr(text1, x, 66));
       x := x + 66;
     end loop;
   end loop;
end;
/

评估准则：

本部分没有评估准则，需要开发人员或者DBA去确认在这25个SQL中属于应用系统的语句是否都已经作过调优。

8．固定缓存对象

尝试在内存中固定（pin）经常使用的对象，包括表，存储过程等。

检索需要在共享池中要求大于100K连续空间的对象：

select *
   from v$db_object_cache
where sharable_mem > 100000
   and type in ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION');

考察返回的结果，确认是否需要pin到共享池中，返回结果中的KEPT字段如果是YES，那么表示该对象已经固定在了共享池中，为NO，则表示还没有固定。

如果需要固定，使用下面的语句：

exec dbms_shared_pool.keep('SYS.STANDARD');

数据库默认安装的时候没有创建dbms_shared_pool包，所以需要先创建该包。

cd $ORACLE_HOME/rdbms/admin

sqlplus “/ as sysdba”

@dbmspool.sql

如果我们要固定表，那么可以在创建表的时候或者修改表属性时使用CACHE关键字，将表放置到Buffer Cache的LRU列表的MRU端。通常我们需要对于较小的但是频繁使用的表进行这种操作。

alter table table_name cache;

我们也可以将需要频繁使用的表放置到另外一个独立的Buffer Cache中，比如KEEP池。这种操作可以使这些表的数据不至于很快被清除出Default Buffer Cache。

alter table table_name storage (buffer pool keep);

评估准则：

本部分没有评估准则，需要开发人员或者DBA在系统分析以后谨慎执行。

二存储性能评估

在存储性能评估的时候，我们使用磁盘性能指数（DPI, Disk Performance Index），下表列出了DPI中的各项指数，这个评分系统并不意味着对磁盘的使用和分配的全方位评估，而只是代表一个晴雨表，反映当前磁盘的使用和分配上是否存在需要改进或者注意的地方。

DPI指数

分类所需等级最高分

调整表和索引是 30

表的行连接问题无 30

分离关键的Oracle文件是 30

回滚段的平衡 30

临时段的平衡 30

使用最多的前10个SQL的磁盘使用率 <5 60

是否已经调整使用磁盘最多的前25个SQL 是 40

DPI指数总分 250

1．调整表和索引

由于表和索引的数据块通常是被同时读取的，所以应该尽量将表和其相关联的索引放置在不同的磁盘上，以便减少文件的I/O冲突。

检查方法：

select i.index_name, t.table_name, t.tablespace_name
from user_tables t, user_indexes i
where t.table_name = i.table_name
and t.tablespace_name = i.tablespace_name;

返回结果是创建在相同表空间中的表和相关联的索引。建议创建新的表空间用于专门存放索引，并将当前的索引rebuild到新创建的表空间中。

alter index idx_name rebuild tablespace ts_name;

评估准则：

等级

分数

表和索引放在同一磁盘上

存储使用了磁盘阵列，没有进一步调整

存储使用了磁盘阵列，对于RAID类型已经作过调整

表和索引已经规划在不同磁盘上

2．表的行链接问题

当更新一张表，而数据块中又没有足够的剩余空间来容纳所作的修改时，就会发生“行链接”现象，该记录被链接到另外一个有足够空间的数据块中，也就是一条记录跨越了多个数据块，这样在读取该记录的时候就会消耗更多的I/O，当数据库中有大量的“行链接”现象存在时，数据库的整体性能就会下降。

检查方法：

sqlplus /nolog

connect app_user/password

SQL> @$ORACLE_HOME/rdbms/admin/utlchain.sql

SQL> analyze table <table_name> list chained rows;

SQL> select count(*) chained_rows, table_name
from chained_rows
group by table_name;

如果没有返回任何行，则表示没有“行链接”现象。否则将按照已经分析过的表显示每张表中有多少记录出现了“行链接”现象。

“行链接”现象的产生跟PCTFREE参数的设置不当有关系。PCTFREE值默认为10%，如果系统中存在大量行链接，表示这个参数指定的块保留空间过小，不足以容纳块中所有记录的更新操作。此时应该增大相应表的PCTFREE值。

评估准则：

等级

分数

存在行链接现象

不存在行链接现象

3．分离关键的Oracle文件

无论是出于安全性的考虑还是性能的考虑，都建议将关键的Oracle文件分布在可用的独立磁盘上。

首先在错误出现之后，用来被恢复的数据文件和用来恢复的控制文件，重作日志文件，归档日志文件应该分离存放。如果有可能，将下列各个关键文件分布在不同的磁盘上。

系统表空间（SYSTEM），临时表空间（TEMP），回滚表空间（UNDO），联机重作日志文件（REDO）和归档日志文件（ARCH），经常访问的用户表空间，经常访问的用户索引表空间，操作系统盘，$ORACL_EBASE中的关键Oracle软件文件。

至少联机重作日志文件（REDO）和归档日志文件（ARCH）应该跟其它文件存放在不同的磁盘上，并且由于日志文件的大部分时间为只写属性，所以需要考虑RAID5在写方面的弱势，尽量不要将日志文件存放在RAID5的阵列组上。

检查方法：

select file_name, tablespace_name, bytes
from dba_data_files
union all
select file_name, tablespace_name, bytes
from dba_temp_files
union all
select name file_name, NULL, NULL
from v$controlfile
union all
select member file_name, to_char(a.group#) tablespace_name, b.bytes bytes
from v$logfile a, v$log b
where a.group# = b.group#
union all (select value file_name, NULL, NULL
             from v$parameter
            where name like 'log_archive_dest_%'
              and value is not null
           minus
           select value file_name, NULL, NULL
             from v$parameter
            where name like 'log_archive_dest_state%');

返回数据库中所有关键文件存储的位置，由DBA和SA考察返回的结果，确认已经对于关键文件的存储位置作过符合实际情况的调整。

评估准则：

等级

分数

没有调整，全部在单个磁盘上

没有调整，全部在RAID上

已经调整

4．回滚段的平衡

在Oracle 9i和Oracle9i之前如果没有使用回滚段自动管理，那么对于回滚段的性能仍然是需要监控并且调整的。

检查是否使用了回滚段自动管理：

select name, value from v$parameter where name like '%undo_%';

如果返回结果中undo_management的值是AUTO，则表示使用了回滚段自动管理，同时undo_tablespace值显示了自动管理使用的回滚表空间，undo_retention值显示了在回滚表空间中保留回滚数据的时限，以秒为单位。

注意：如果undo_management的值是AUTO但是undo_tablespace没有设置相应的值，那么就会使用SYSTEM表空间中的SYSTEM回滚段，这个是绝对应该避免的现象。

如果没有使用回滚段自动管理，那么需要监控用户使用回滚段的频度，原则上认为不应该有超过1个用户同时使用1个回滚段。

检查方法：

select a.name,
       b.extents,
       b.rssize,
       b.xacts,
       b.waits,
       b.gets,
       optsize,
       status
from v$rollname a, v$rollstat b
where a.usn = b.usn;

检查输出结果，对于所有回滚段而言，如果xacts（活动事务）和waits（段头等待）经常超出1，那么就表明需要增加回滚段数目，以避免可能出现的争用。

增加回滚段的方法：

create rollback segment rs_name tablespace RBS storage(initial 1M next 2M);
alter rollback segment rs_name online;

如果使用了回滚段自动管理，那么可以从v$undostat, v$rollstat, dba_undo_extents等视图中查询当前回滚段的使用和分配情况。

评估准则：

等级

分数

有回滚段等待现象

无回滚段等待现象

使用了回滚段自动管理

5．临时段的平衡

当初始化参数中定义的SORT_AREA_SIZE大小无法满足排序要求的空间，就会使用临时表空间中的临时段进行排序，磁盘排序比内存排序要慢100-10000倍，所以尽量减少磁盘排序是性能调整工作的一个重要部分。

可能引起排序的操作有create index, distinct, order by, group by等。

检查方法：

select name, value from v$sysstat where name like '%sorts%';

返回结果中的sorts (memory)表示内存排序，而sorts (disk)则表示磁盘排序，如果存在大量的磁盘排序，则表明我们需要增加SORT_AREA_SIZE或者HASH_AREA_SIZE等排序区的大小，或者需要检查目前系统中消耗大量磁盘的SQL是否已经经过调整（检查前25位消耗磁盘的SQL在后面部分将提到）。

检查使用磁盘排序的会话信息，可以定位执行了大量磁盘排序的会话。

检查方法：

select b.name, a.sid, a.value
from v$sesstat a, v$statname b
where a.STATISTIC# = b.STATISTIC#
and b.name = 'sorts (disk)'
and a.value > 0
order by a.value desc;

如果有可能我们应该将临时表空间中的多个临时数据文件分布在不同的磁盘上，以减少排序时可能会产生的磁盘冲突。

在Oracle9i中，我们可以设置PGA_AGGREGATE_SIZE初始化参数来指定所有会话将使用的PGA大小，同时也必须设置WORKAREA_SIZE_POLICY参数为AUTO。其它详细信息见内存性能评估中“4。内存中的排序”部分。

评估准则：

等级

分数

对于存在的磁盘排序没有评估

已经就存在的磁盘排序进行过调整

6．最浪费磁盘读操作的前10个语句占所有语句的比例

通常一个没有优化系统中，10个最常用的SQL语句的访问量会占到整个系统中磁盘读操作的50%以上。这些SQL是最需要进行优化的部分，也是优化工作中优先级很高的部分。通常我们的优化目标是将这些SQL的磁盘读操作百分比降低到5-19%。

检查方法：

select sum(pct_bufgets)
from (select rank() over(order by disk_reads desc) as rank_bufgets,
to_char(100 * ratio_to_report(disk_reads) over(), '999.99') pct_bufgets
from v$sqlarea)
where rank_bufgets < 11;

评估准则：

等级

分数

<5%

5-19%

20-25%

>25%

7．调整前25个最浪费磁盘读操作的语句

在没有调整的情况下，绝大多数系统中，访问量占前25位的语句的磁盘读操作将占用整个系统所有磁盘读操作的75%，对这部分语句进行调整是至关重要的。这部分脚本用于获得访问量占前25位的SQL语句。输出结果中的Exec表示该SQL被执行的次数。

检查方法：

set serveroutput on size 1000000
declare
execution number;
top25     number;
text1     varchar2(4000);
x         number;
len1      number;
cursor c1 is
    select executions, disk_reads, substr(sql_text, 1, 4000)
      from v$sqlarea
     order by disk_reads desc;
begin
dbms_output.put_line('Exec' || ' ' || 'Reads' || '      ' || 'Text');
dbms_output.put_line('-----' || ' ' || '--------' || ' ' ||
                       '-------------');
open c1;
for i in 1 .. 25 loop
    fetch c1
      into execution, top25, text1;
    dbms_output.put_line(rpad(to_char(execution), 5) || ' ' ||
                         rpad(to_char(top25), 8) || ' ' ||
                         substr(text1, 1, 66));
    len1 := length(text1);
    x    := 66;
    while len1 > x - 1 loop
      dbms_output.put_line('-              ' || substr(text1, x, 66));
      x := x + 66;
    end loop;
end loop;
end;
/

评估准则：

本部分没有具体的评估准则，需要开发人员或者DBA去确认在这25个SQL中属于应用系统的语句是否都已经作过调优。

8．其它存储相关的调整

1) 字典管理表空间中的Extent总数不超过4096

检查方法：

select a.tablespace_name, sum(a.extents)
from dba_segments a, dba_tablespaces b
where a.tablespace_name = b.tablespace_name
and b.extent_management = 'DICTIONARY'
group by a.tablespace_name
order by sum(a.extents);

检查输出结果，如果显示某个表空间中的extents总数超过了4096，那么需要扩大这个表空间的extent大小，过多的extent对于DMT的空间管理有负面影响。

2) 本地管理表空间中单个Segement的Extent数不超过1024

检查方法：

select segment_name, segment_type, extents, bytes, b.tablespace_name
from dba_segments a, dba_tablespaces b
where a.tablespace_name = b.tablespace_name
and b.extent_management = 'LOCAL'
and a.extents > 1024;

检查输出结果，返回的记录都是单个段的区间大于1024的对象，对于这些对象，应该创建一个单独的具有更大extent大小的表空间，然后将这些对象move到新的表空间中去。

3) 检查字典管理表空间的PCTINCREASE值是否是0

为了表空间中的所有extent大小相同，建议表空间中的所有段都不要设置独立的storage参数。对于表空间的pctincrease参数，建议设置为0，同时应该设置minextents参数，保证初始分配足够的空间给新创建的对象。

对于LMT表空间，storage参数中的pctincrease和next参数均无效，建议设置适当的uniform参数管理表空间的extent分配。

4) 考虑使用分区来避免磁盘争用

分区表在管理的方便性和性能的提高上都有较强的实用性，甚至可以认为分区是提高与大型表有关的性能的最佳方法。通过访问一个表或者索引的较小片段，而不是访问整个表或索引，分区可以很好地提高效率。如果一个表或者索引的分区位于不同的磁盘上，就更可以大大增加数据吞吐量，获得很好的数据库性能。

对于分区的使用，暂时不在本文的叙述范围内，请参阅其它的分区文档。

5) 对于分区表的非分区键索引是否是全局分区索引

由于分区表的数据量通常比较巨大，所以如果在分区表的非分区键上创建索引，那么建议创建为全局分区索引，这样能够更好地提高性能。注意：如果截断了一个分区的数据或者删除了一个分区，那么必须rebuild这个分区表中的全部全局索引，否则这些全局索引将处于invalid状态，导致使用到这些索引的SQL语句失败。

转载于:https://blog.51cto.com/kastal/149180

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。