首页    技术博客    PHP教程    数据库技术    前端开发   HTML5    Nginx    php论坛
新用户注册 | 会员登录
加入会员，解锁专属内容
取消
热门标签 | HotTags
当前位置:  开发笔记 > 数据库 > 正文

数据库服务器CPU突然持续100%后自动下降原因诊断-mysql教程

作者：此女我很爱_484 | 来源：互联网 | 2017-05-12 15:28
1、CPU接近100%nmon数据8月5日在9：209：40之间，出现CPU接近100%的情况，特点表现为9：20左右CPU急剧攀升，在9：45左右又快速下降2、原因分析结果总述2.1持续时间与恢复方式此次CPU攀高时间持续约20分钟，在无人工干预的情况下自动恢复2.2原因分析

1、CPU接近100% nmon数据 8月5日在9：209：40之间，出现CPU接近100%的情况，特点表现为9：20左右CPU急剧攀升，在9：45左右又快速下降 2、原因分析结果总述 2.1 持续时间与恢复方式 此次CPU攀高时间持续约20分钟，在无人工干预的情况下自动恢复 2.2 原因分析

1、CPU接近100% nmon数据

\

8月5日在9：20—9：40之间，出现CPU接近100%的情况，特点表现为9：20左右CPU急剧攀升，在9：45左右又快速下降

2、原因分析结果总述

2.1 持续时间与恢复方式

此次CPU攀高时间持续约20分钟，在无人工干预的情况下自动恢复

2.2 原因分析总述：

经过分析，原因为：4条SQL语句ORACLE优化器对LB_T_XXXVIDER视图、LB_T_XXXJECT_PROVIDER表、LA_XXCKAGE表的基数数据评估发生了巨大的差错，导致选择了错误的执行计划，消耗大量的CPU资源

2.3 错误执行计划估算数据与正确执行计划估算数据对比

此处为选择一条最严重的SQL语句为例，其它语句原因相同

错误执行计划基数估算值

Execution Plan

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

0

SELECT STATEMENT

315 (100)

1

COUNT STOPKEY

2

VIEW

1

180

315 (2)

3

SORT ORDER BY STOPKEY

1

151

315 (2)

4

HASH UNIQUE

1

151

314 (1)

5

FILTER

6

NESTED LOOPS OUTER

1

151

313 (1)

7

NESTED LOOPS

1

86

35 (0)

8

TABLE ACCESS BY INDEX ROWID

LB_T_XXXJECT_PROVIDER

1

61

34 (0)

9

INDEX RANGE SCAN

IDX_LB_T_XXXJECT_PROVIDER_003

183

3 (0)

10

TABLE ACCESS BY INDEX ROWID

LA_XXCKAGE

1

25

1 (0)

11

INDEX UNIQUE SCAN

PK_LA_XXCKAGE

1

0 (0)

12

VIEW PUSHED PREDICATE

LB_T_XXXVIDER

1

65

278 (1)

13

MERGE JOIN OUTER

1

64

278 (1)

14

TABLE ACCESS BY INDEX ROWID

XXCC_SUPPLIER

1

45

146 (0)

15

INDEX FULL SCAN

PK_XXCC_SUPPLIER

1

145 (0)

16

SORT JOIN

17998

333K

132 (2)

17

VIEW

17998

333K

131 (1)

18

SORT GROUP BY

17998

544K

131 (1)

19

TABLE ACCESS FULL

XXCC_SUPPLIER_CONTACT

30058

909K

130 (0)

正确执行计划基数估算值

Execution Plan

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

0

SELECT STATEMENT

64460 (100)

1

COUNT STOPKEY

2

VIEW

224K

38M

64460 (1)

3

SORT ORDER BY STOPKEY

224K

28M

64460 (1)

4

HASH UNIQUE

224K

28M

58849 (1)

5

FILTER

6

HASH JOIN OUTER

224K

28M

53237 (1)

7

NESTED LOOPS

8

NESTED LOOPS

347

29842

528 (0)

9

TABLE ACCESS BY INDEX ROWID

LB_T_XXXJECT_PROVIDER

347

21167

181 (0)

10

INDEX RANGE SCAN

IDX_PROJECT_PROVIDER_COMB1

182

4 (0)

11

INDEX UNIQUE SCAN

PK_LA_XXCKAGE

1

0 (0)

12

TABLE ACCESS BY INDEX ROWID

LA_XXCKAGE

1

25

1 (0)

13

VIEW

LB_T_XXXVIDER

9125K

409M

52700 (1)

14

HASH JOIN OUTER

9125K

556M

52700 (1)

15

TABLE ACCESS FULL

XXCC_SUPPLIER

26139

1148K

404 (0)

16

VIEW

6283K

113M

52287 (1)

17

SORT GROUP BY

6283K

185M

52287 (1)

18

TABLE ACCESS FULL

XXCC_SUPPLIER_CONTACT

10M

309M

148 (9)
  • cardinality feedback used for this statement

说明上面统计信息与实际数据存在非常大的差异，是引起执行计划错误的真正原因

2.4 错误执行计划与正确执行计划CPU资源消耗差异巨大对比

此处为选择一条最严重的SQL语句为例，其它语句比例相近

SQL ID: bj75p9188y410

#

Plan Hash Value

Total Elapsed Time(ms)

Executions

1st Capture Snap ID

Last Capture Snap ID

1

3990363694

4,585,425

9

33676

33677

2

6178145

2,838

2

33677

33677

3

2354817963

1,461

1

33677

33677

错误执行计划CPU消耗是最优执行计划CPU消耗的348倍

2.5 问题自动修复原因

ORACLE11G 的新功能cardinality feedback可以在上次运行完成后，得到上一次运行的基数真正结果，智能的调整后面语句运行时的执行计划，通过cardinality feedback功能得到准确基数数据后调整的执行计划，会给出下面提示：

cardinality feedback used for this statement

3、数据库时间与AWR快照对应信息

此在列出时间与AWR快照对应信息的原因为后续分析依赖时间与快照的关系，展示阶段性数据

序号

snap_id

BEGIN_INTERVAL_TIME

END_INTERVAL_TIME

1

33675

05-8月 -13 08.30.41.054

05-8月 -13 09.00.09.786

2

33676

05-8月 -13 09.00.09.786

05-8月 -13 09.30.10.502

3

33677

05-8月 -13 09.30.10.502

05-8月 -13 10.00.26.364

4

33678

05-8月 -13 10.00.26.364

05-8月 -13 10.30.18.791

5

33679

05-8月 -13 10.30.18.791

05-8月 -13 11.00.24.540

4、总体原因具体分析

8月5日9：00—10：00AWR报告分析

SQL ordered by CPU Time

NO

CPU Time (s)

Executions

CPU per Exec (s)

Elapsed Time (s)

%CPU

%IO

SQL Id

SQL Text

1

2,927.66

12

243.97

4,589.72

63.79

0.00

bj75p9188y410

select * from ( select distinc...

2

1,771.52

3,820

0.46

2,660.05

66.60

0.00

gwz243zx18jk0

SELECT * FROM PUB_STRU_TYPE_RE...

3

1,687.68

6

281.28

1,981.37

85.18

0.00

gmfktzfsd6hj3

select * from ( select row_.*,...

4

1,320.77

18

73.38

2,050.40

64.42

0.00

1d44jc4at7xt6

select count(0) from ( select ...

5

870.33

745

1.17

1,288.11

67.57

0.00

4x0jsx8xmrq3c

select count(1) rcount from ( ...

6

856.20

63

13.59

1,096.32

78.10

0.00

1ztsa8nc1arn1

SELECT DISTINCT RFX2.rootId F...

7

752.07

429

1.75

1,145.77

65.64

0.00

9rpn6kmf9vwwh

select p.package_id, p.package...

8

729.02

94

7.76

853.01

85.46

0.00

bq1x40gqtd1r3

SELECT DISTINCT RFX2.rootId F...

9

683.45

12

56.95

1,538.29

44.43

37.32

f760vj05hjpww

SELECT DISTINCT RFX1.rootId F...

10

595.38

2

297.69

632.09

94.19

0.00

fh86uz0ch9z9w

select count(0) from ( select ...

分析：

（1） 上述标红色语句共四条，其中第4条和第10条其实为同一条SQL语句

（2） 上述四条标红色SQL语句分析为最消耗CPU资源的语句

（3） 上述四条标红色SQL语句在出问题前都没有运行，基本都集中在9：00以后开始运行

（4） 上述标红色语句的问题有共同特性，都调用了LB_T_XXXVIDER视图、LB_T_XXXJECT_PROVIDER表、LA_XXCKAGE

（5） 上述标红色语句都是因为优化器对LB_T_XXXVIDER视图、LB_T_XXXJECT_PROVIDER表、LA_XXCKAGE表的基数数据评估发生了巨大的差错，导致选择了错误的执行计划，消耗大量的CPU资源，导致CPU接近100%

（6） 上述标红色语句都是在几次错误选择后有效的利用了ORACLE11g的新特性cardinalityfeedback功能，最终得到准确的基数数据，自行修正了执行计划

5、问题语句逐条剖析

5.1 第一条：SQL_ID：bj75p9188y410

SQL ID: bj75p9188y410

#

Plan Hash Value

Total Elapsed Time(ms)

Executions

1st Capture Snap ID

Last Capture Snap ID

1

3990363694

4,585,425

9

33676

33677

2

6178145

2,838

2

33677

33677

3

2354817963

1,461

1

33677

33677

分析：

（1）标红色的为错误的执行计划及其统计信息

（2）9：00—10：00之间，共发生9次错误的执行计划

（3）在前面发生9次错误的执行计划之后，由于ORACLE 11G的cardinality feedback新功能，最终得到准确的基数，在9：30-10：00之间，自动纠正了执行计划

5.2 第二条：SQL_ID：gmfktzfsd6hj3

SQL ID: gmfktzfsd6hj3

#

Plan Hash Value

Total Elapsed Time(ms)

Executions

1st Capture Snap ID

Last Capture Snap ID

1

617277444

2,602,874

6

33677

33678

2

2335536944

2,725

2

33678

33678

3

687995303

1,437

1

33678

33678

分析：

（1）标红色的为错误的执行计划及其统计信息

（2）9：30—10：30之间，共发生6次错误的执行计划

（3）在前面发生6次错误的执行计划之后，由于ORACLE 11G的cardinality feedback新功能，最终得到准确的基数，在10：00-10：30之间，自动纠正了执行计划

5.3 第三条：SQL_ID：1d44jc4at7xt6

SQL ID: 1d44jc4at7xt6

#

Plan Hash Value

Total Elapsed Time(ms)

Executions

1st Capture Snap ID

Last Capture Snap ID

1

3265929876

2,029,525

4

33676

33677

2

2949667951

19,116

13

33676

33677

3

1227972422

1,761

1

33676

33677

分析：

（1）标红色的为错误的执行计划及其统计信息

（2）9：00—10：00之间，共发生4次错误的执行计划，其中

（3）在前面发生4次错误的执行计划之后，由于ORACLE 11G的cardinality feedback新功能，最终得到准确的基数，在9：30-10：00之间，自动纠正了执行计划

5.4 第四条：SQL_ID：fh86uz0ch9z9w

SQL ID: fh86uz0ch9z9w

#

Plan Hash Value

Total Elapsed Time(ms)

Executions

1st Capture Snap ID

Last Capture Snap ID

1

3265929876

1,247,089

4

33676

33681

2

2949667951

2,624

2

33681

33681

3

1227972422

1,291

1

33681

33681

分析：

（1）标红色的为错误的执行计划及其统计信息

（2）9：00—11：30之间，共发生4次错误的执行计划，其中两次发生在9：00-9：30间

（3）在前面发生4次错误的执行计划之后，由于ORACLE 11G的cardinality feedback新功能，最终得到准确的基数，在11：00-11：30之间，自动纠正了执行计划

6、引发执行计划错误原因分析

6.1 表统计信息统计历史

6.1.1 LB_T_XXXJECT_PROVIDER

序号

用户名

表名

分析历史时间

1

BIDPRO

LB_T_XXXJECT_PROVIDER

11-8月 -13 20.06.16.630512

2

BIDPRO

LB_T_XXXJECT_PROVIDER

03-8月 -13 20.22.23.332654

3

BIDPRO

LB_T_XXXJECT_PROVIDER

26-7月 -13 22.18.08.386638

在8月5日开标前，该表已经有10天未统计

6.1.2 LA_XXCKAGE

序号

用户名

表名

分析历史时间

1

BIDPRO

LA_XXCKAGE

15-8月 -13 20.01.28.232128

2

BIDPRO

LA_XXCKAGE

09-8月 -13 20.04.35.224700

3

BIDPRO

LA_XXCKAGE

26-7月 -13 20.08.49.666682

在8月5日开标前，该表已经有10天未统计

6.1.3 XXCC_SUPPLIER

序号

用户名

表名

分析历史时间

1

BIDPRO

XXCC_SUPPLIER

18-8月 -13 20.13.21.031834

2

BIDPRO

XXCC_SUPPLIER

10-8月 -13 20.07.04.740643

3

BIDPRO

XXCC_SUPPLIER

31-7月 -13 22.00.39.107474

4

BIDPRO

XXCC_SUPPLIER

22-7月 -13 22.01.26.170018

在8月5日开标前，该表已经有5天未统计

6.1.4 XXCC_SUPPLIER_CONTACT

序号

用户名

表名

分析历史时间

1

BIDPRO

XXCC_SUPPLIER_CONTACT

17-8月 -13 20.03.30.834585

2

BIDPRO

XXCC_SUPPLIER_CONTACT

09-8月 -13 22.03.30.402420

3

BIDPRO

XXCC_SUPPLIER_CONTACT

26-7月 -13 22.07.06.696581

在8月5日开标前，该表已经有10天未统计

6.2 表数据变化分析

6.2.1 8月5日前最后一次统计时间至8月5日时的block_changes数量

序号

表名

最后一次统计时间

block_changes

1

LB_T_XXXJECT_PROVIDER

26-7月 -13 22.18

158560

2

LA_XXCKAGE

26-7月 -13 20.08

168224

3

XXCC_SUPPLIER

31-7月 -13 22.00

608

4

XXCC_SUPPLIER_CONTACT

26-7月 -13 22.07

576

6.3 错误执行计划预估数据量与真正数据量差异对比

SQL_ID

执行计划对错区分

对表的ROWS评估数

XXCC_SUPPLIER_CONTACT

XXCC_SUPPLIER

LA_XXCKAGE

LB_T_XXXJECT_PROVIDER

bj75p9188y410

错误执行计划

30058

1

1

1

正确执行计划

10M

26139

1

347

gmfktzfsd6hj3

错误执行计划

30058

1

1

1

正确执行计划

10M

26139

1

347

1d44jc4at7xt6

错误执行计划

30058

1

1

1

正确执行计划

10M

26139

1

347

fh86uz0ch9z9w

错误执行计划

30058

1

1

1

正确执行计划

7763K

26139

1

347

7、解决方案建议

建议对上述四条发生执行计划错误的SQL语句，采用SQL_PROFILE对执行计划进行固定，可以避免下次开标时出现同样的问题


推荐阅读
  • mysql

    READ COMMITTED 和 REPEATABLE READ 事务隔离级别的对比与分析

    在数据库事务处理中，InnoDB 存储引擎提供了多种隔离级别，其中 READ COMMITTED 和 REPEATABLE READ 是两个常用的选项。本文详细对比了这两种隔离级别的特点和差异，不仅从理论角度分析了它们对“脏读”和“幻读”的处理方式，还结合实际应用场景探讨了它们在并发控制和性能表现上的不同。特别关注了行锁机制在不同隔离级别下的行为，为开发者选择合适的隔离级别提供了参考。 ... [详细]
  • 数据库

    如何解决数据列消失的问题？

    在使用PFC进行数据处理时，遇到了数据列消失的问题。具体表现为，在数据窗口dw_1中，原本点击排序按钮cb_1后，会弹出一个排序窗口并显示所有字段。然而，目前点击排序按钮时，数据列却无法正常显示。为了解决这一问题，需要检查数据源的配置和按钮事件的触发逻辑，确保数据列能够正确加载和显示。 ... [详细]
  • oracle

    为何数据库设计中应谨慎考虑外键的使用：9个关键原因

    在数据库设计中，谨慎使用外键至关重要。本文探讨了九个关键原因，包括数据完整性的维护、性能优化、系统复杂性的管理、数据迁移的灵活性以及对外部系统的依赖性控制。通过深入分析这些因素，可以帮助开发人员和架构师做出更明智的设计决策，确保数据库系统的高效与稳定。 ... [详细]
  • sql

    InnoDB当前仅支持一次创建一个FULLTEXT索引

    InnoDB当前仅支持一次创建一个FULLTEXT索引 ... [详细]
  • mysql

    MySQL 数据备份与恢复的常见方法及实践经验总结

    MySQL 数据备份与恢复的常见方法及其实践经验总结。物理备份涉及直接复制数据库文件，适用于大规模数据库环境，但无法在异构系统（如 Windows）中恢复。逻辑备份则侧重于导出建表语句和数据插入语句，便于跨平台迁移和部分数据恢复。此外，本文还探讨了增量备份、全量备份以及使用工具如 mysqldump 和 Percona XtraBackup 的具体应用场景和优缺点。 ... [详细]
  • mysql

    揭秘腾讯云CynosDB计算层设计优化背后的不为人知的故事与技术细节

    揭秘腾讯云CynosDB计算层设计优化背后的不为人知的故事与技术细节
    揭秘腾讯云CynosDB计算层设计优化背后的不为人知的故事与技术细节 ... [详细]
  • mysql

    MySQL 数据变更后如何实现实时同步至 Elasticsearch

    MySQL 数据变更后如何实现实时同步至 Elasticsearch
    在 MySQL 数据变更后，如何实现与 Elasticsearch 的实时同步是一个常见的需求。本文介绍了通过配置 MySQL 的 Binlog 功能，结合中间件如 Canal 或 Debezium，将数据变更事件实时捕获并同步到 Elasticsearch 中的方法。此外，还探讨了如何处理数据删除操作，确保 Elasticsearch 中的数据与 MySQL 保持一致。文章还简要对比了 VSCode 和 Dev 两种开发环境的优缺点，为开发者提供参考。 ... [详细]
  • sql

    掌握PHP编程必备知识与技巧——全面教程

    掌握PHP编程必备知识与技巧——全面教程在当今的PHP开发中，了解并运用最新的技术和最佳实践至关重要。本教程将详细介绍PHP编程的核心知识与实用技巧。首先，确保你正在使用PHP 5.3或更高版本，最好是最新版本，以充分利用其性能优化和新特性。此外，我们还将探讨代码结构、安全性和性能优化等方面的内容，帮助你成为一名更高效的PHP开发者。 ... [详细]
  • mysql

    触发此操作以刷新我点击更新的行中的特定列

    在数据表中，我需要触发一个操作来刷新特定列的数据。例如，对于以下表格：| ID | Name | IsDeleted ||----|-------|-----------|| 1 | test | True || 2 | test2 | False |我希望在点击“更新”按钮时，能够仅刷新选定行的“IsDeleted”列。这将有助于确保数据的实时性和准确性。 ... [详细]
  • sql

    深入掌握Scala面向对象编程与Spark源码解析

    在第二课中，我们将深入探讨Scala的面向对象编程核心概念及其在Spark源码中的应用。首先，通过详细的实战案例，全面解析Scala中的类和对象。作为一门纯面向对象的语言，Scala的类设计和对象使用是理解其面向对象特性的关键。此外，我们还将介绍如何通过阅读Spark源码来进一步巩固对这些概念的理解。这不仅有助于提升编程技能，还能为后续的高级应用开发打下坚实的基础。 ... [详细]
  • mysql

    如何使用R语言高效连接并操作MySQL数据库

    如何使用R语言高效连接并操作MySQL数据库
    如何使用R语言高效连接并操作MySQL数据库 ... [详细]
  • mysql

    深入解析MySQL并发参数优化与调整方法

    本文详细探讨了MySQL并发参数的优化与调整方法，旨在帮助读者深入了解如何通过合理配置这些参数来提升数据库性能。文章不仅介绍了常见的并发参数及其作用，还提供了实际操作中的调整策略和最佳实践，适合希望提高数据库管理技能的技术人员阅读。 ... [详细]
  • mysql

    解决MySQL 5.1服务器无法正确识别中文字符的问题

    解决MySQL 5.1服务器无法正确识别中文字符的问题
    在使用MySQL 5.1服务器时，可能会遇到无法正确识别中文字符的问题。由于相关资料较少且不够全面，本文将详细阐述解决方案。首先，需要检查MySQL的配置文件，确保字符集设置正确，并通过命令行工具验证当前的字符编码配置。此外，建议更新到最新版本以避免此类问题。 ... [详细]
  • 数据库

    SQL Server 客户端连接远程数据库时未指定端口导致 “用户 'sa' 登录失败 (错误 18456)” 的解决方案

    SQL Server 客户端连接远程数据库时未指定端口导致 “用户 'sa' 登录失败 (错误 18456)” 的解决方案 ... [详细]
  • join

    MySQL WITH 语句详解及其应用实例总结

    本文将深入探讨MySQL 8.0中的WITH语句，通过具体应用实例详细解析其在处理复杂SQL查询时的优势。WITH语句能够显著减少临时表的使用，提高代码的可读性和维护性，从而优化查询性能。 ... [详细]
author-avatar
此女我很爱_484
这个家伙很懒，什么也没留下！
Tags | 热门标签
RankList | 热门文章
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有