达梦数据库性能问题分析及优化_达梦数据库性能优化

---

一、前言

  在数据库管理过程中，经常收到用户对数据库使用的反馈比如“数据库挂了”，“数据库慢”，这种反馈方式是一种比较模糊的说法，仅通过这种问题描述对问题分析往往没有任何帮助，也无法定位问题原因。数据库在运行过程中可能出现的异常的情况比较多，比如连接数据库缓慢或无法建立新的会话、SQL查询缓慢、SQL数据写入缓慢、某一条SQL语句执行缓慢等等，需要根据具体问题现象进行分析和定位。

---

二、排查思路

  数据库问题的排查应遵循“由硬到软，自底向上”的排查思路进行检查和定位。硬件资源对于数据库的影响非常大，是软件正常运行的基础，当硬件资源达到瓶颈或出现故障的时候会多数据库造成致命性的问题，比如网络不稳定丢包，存储设备故障导致IO异常等都会造成不可预估的异常现象。一套数据库的运行依赖各种数据库服务，对于较为复杂的集群架构，服务器会运行多个数据库相关的后台服务，多个服务之间协同工作实现整个数据库集群的正常工作，如果出现服务中断，异常挂起都会造成影响。
  硬件资源、服务等在部署之初就已经确定，出现故障虽然影响大，但一般属于小概率问题。绝大部的数据库问题都是由于不正确的使用造成的，比如不合理的数据库规划、复杂且高度耦合的业务逻辑、索引的不正确使用等。

  硬件及服务的检查基本以系统日志分析，数据库日志分析为主，同时可能有专门的系统运维工程师进行监控与检查，本文后续主要以数据库SQL运行分析为主，来介绍如何识别及处理异常的SQL语句

---

三、分析排查步骤

1.服务器资源检查

不良的SQL语句最明显的问题，就会造成服务器资源使用率的飙升，影响其他正常的业务无法获取资源造成等待，通过对操作系统cpu及内存的查看，来识别系统资源的瓶颈。

内存使用情况（示例）：

[dmdba@localhost ~]$ free -h
              total        used        free      shared  buff/cache   available
Mem:          254Gi       188Gi       1.4Gi       1.2Gi        64Gi        62Gi
Swap:            0B          0B          0B
[dmdba@localhost ~]$ vmstat 1
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
10  2      0 1423936    192 67698688    0    0    58   577    0    0  2  0 98  0  0
 7  0      0 1419712    192 67701312    0    0   392   688 30630 40959  8  0 92  0  0
 7  0      0 1417280    192 67703680    0    0   820 60804 29251 39335  7  0 93  0  0
1
2
3
4
5
6
7
8
9
10

cpu使用情况（示例）：

top

Tasks: 994 total,   1 running, 993 sleeping,   0 stopped,   0 zombie
%Cpu(s):  7.3 us,  0.1 sy,  0.0 ni, 92.5 id,  0.0 wa,  0.0 hi,  0.1 si,  0.0 st
MiB Mem : 260189.6 total,   1542.4 free, 192699.1 used,  65948.2 buff/cache
MiB Swap:      0.0 total,      0.0 free,      0.0 used.  64180.6 avail Mem 

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND                                                                                                    
 353235 dmdba     20   0  138.8g 133.4g  22016 S 704.0  52.5 294971:41 dmserver                                                                                                   
 354016 dmdba     20   0   49.9g  41.3g   9408 S   1.7  16.3   1745:57 dmserver                                                                                                   
 545332 dmdba     20   0  228992   7232   3904 R   1.0   0.0   0:00.10 top                                                                                                        
 257331 root      20   0  586304  75904  16064 S   0.7   0.0  97:36.60 titanagent                                                                                                 
     10 root      20   0       0      0      0 I   0.3   0.0 201:55.65 rcu_sched                                                                                                  
 354592 dmdba     20   0 1198016  10752   2880 S   0.3   0.0 186:36.88 svc_ctl_linux                                                                                              
3929612 root      20   0       0      0      0 I   0.3   0.0   0:00.05 kworker/73:2-mm_percpu_wq                                                                                  
      1 root      20   0  175104  13888   4992 S   0.0   0.0   3:02.87 systemd                                                                                                    
      2 root      20   0       0      0      0 S   0.0   0.0   0:07.39 kthreadd                

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

通过对内存和cpu的查看，可以定位出目前服务运行的资源瓶颈在哪，是内存跑慢了，还是CPU跑慢了。针对具体的情况可以在进行下步的分析。

2.查询占用cpu最多的线程

查询cpu占用最高的线程（示例）：

[dmdba@localhost ~]$ ps -eLo pcpu,pmem,pid,tid,psr,wchan:14,comm|grep 353235 |sort

#CPU使用率 | 内存使用率 | 进程号 | 线程号 | 运行在哪个核上 | 进程休眠的内核函数地址，运行的任务显示为‘-’ | 进程可执行文件名

58.8 52.5  353235  534530  30 -              dm_sql_thd
59.7 52.5  353235  534533  73 futex_wait_que dm_sql_thd
60.8 52.5  353235  534535  29 futex_wait_que dm_sql_thd
61.1 52.5  353235  534531  74 futex_wait_que dm_sql_thd
67.6 52.5  353235  545369   3 -              dm_sql_thd
 6.9 52.5  353235  417889  55 wait_woken     dm_sql_thd
 6.9 52.5  353235  417891  50 wait_woken     dm_sql_thd
94.2 52.5  353235  307016  61 -              dm_sql_thd
94.9 52.5  353235  307020  78 -              dm_sql_thd
95.5 52.5  353235  307021   6 -              dm_sql_thd
95.6 52.5  353235  307018  26 -              dm_sql_thd
95.8 52.5  353235  307019  49 -              dm_sql_thd

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

通过以上查询可以看到占用CPU较高的都是dm_sql_thd线程，dm_sql_thd 是用户线程。一般通过客户端连接的线程都是属于这个类别。
既然属于用户连接的信息，那么通过数据库的v$sessions视图就可以查询出，这个线程正在执行的动作。

在实际分析过程中也有可能是其他线程占用了大量资源，下面对达梦数据库中的线程进行简单介绍，有助于判断问题点。

线程介绍：

DM 提供了很多动态性能视图，通过它们用户可以直观地了解当前系统中有哪些线程在工作，以及线程的相关信息。
相关动态视图：

2.查询线程对应的SQL语句

查询线程执行的内容（示例）：

select * from v$sessions where thrd_id='307019'

1
2

通过以上的查询就定位到引发CPU占用率较高的SQL语句，可以有针对性的进行优化和处理。

3.数据库内存使用情况分析

  DM的内存池包括共享内存池和一些运行时内存池，共享内存池是 DM Server 在启动时从操作系统申请一大片内存供系统运行时使用，避免运行期间频繁的进行系统调用降低系统运行效率，提供参数MEMORY_POOL设置大小和MEMORY_TARGET参数设置上限；运行时内存池为DM 的一些功能模块在运行时使用的自己运行时内存池，这些运行时内存池是从操作系统申请一片内存作为本功能模块的内存池来使用，如会话内存池、虚拟机内存池等。

查询占用内存高的SQL（示例）：

SELECT
		A.CREATOR,
        B.SQL_TEXT,
        TRUNC(SUM( A.TOTAL_SIZE/1024/1024)) "分配大小(M)",
        TRUNC(SUM(A.DATA_SIZE /1024/1024)) "占用大小(M)"
FROM
        V$MEM_POOL A,
        V$SESSIONS B
WHERE
        A.CREATOR = B.THRD_ID
GROUP BY
        A.CREATOR,
        B.SQL_TEXT
ORDER BY 3 DESC

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

通过上面的语句就可以查询到当前正在执行的SQL那条语句占用的内存比较高，如果占用内存异常需要对sql进行处理。

4.数据库的内存参数优化

合理的配置内存参数也是优化的关键步骤，让数据库可以充分的使用系统资源可以有效改善数据库运行效率

  在优化内存之间需要首先了解数据库的内存结构。数据库管理系统是一种对内存申请和释放操作频率很高的软件，如果每次对内存的使用都使用操作系统函数来申请和释放，效率会比较低，加入自己的内存管理是 DBMS 系统所必须的。DM 数据库管理系统的内存结构主要包括内存池、 缓冲区、排序区、哈希区等。根据系统中子模块的不同功能，对内存进行了上述划分，并采用了不同的管理模式。

内存池 v$mem_pool
在 DM Server 的运行期间， 经常会申请与释放小片内存，而直接向操作系统申请和释放内存时需要发出系统调用，此时可能会引起线程切换， 降低系统运行效率。 于是 DM 采用采用共享内存池的方式： 一次向操作系统申请一片较大内存， 作为共享内存池。 当系统在运行过程中需要申请小片内存时， 可在共享内存池内进行申请，当用完该内存时，再释放掉，即归还给共享内存池。

关于内存池的参数主要有三个：

缓冲区 v$bufferpool
缓冲区包括数据缓冲区、日志缓冲区、字典缓冲区、SQL 缓冲区

数据缓冲区是数据页写入磁盘之前以及从磁盘上读取数据页之后，数据页所存储的地方，分为四种类别，分别为：

根据内存的大小合理配置缓存中的参数是主要针对缓存区的优化手段。

---

四、SQL优化

  通过第三部分的分析，基本已经可以提取出影响数据库运行的sql语句,之后就需要对提取出来的语句进行分析和优化。SQL优化是一个系统化的课题，没有办法几句话说清楚，接下来只做一些粗浅的介绍。

1.查看SQL执行计划

使用“达梦管理工具”查看非常简单，只要在SQL窗口按“F9”就可以显示执行计划。

在DISQL窗口可以在语句前增加 EXPLAIN 就可以显示出语句的执行计划。

SQL> EXPLAIN select * from t1 where NAME='AAA';

1   #NSET2: [1, 1, 60]
2     #PRJT2: [1, 1, 60]; exp_num(3), is_atom(FALSE)
3       #SLCT2: [1, 1, 60]; T1.NAME = 'AAA'
4         #CSCN2: [1, 4, 60]; INDEX33555565(T1)

已用时间: 0.625(毫秒). 执行号:0.

1
2
3
4
5
6
7
8
9

在SQL优化中最基本的优化原则就是“少做事”，让SQL尽量少的去工作，比如通过索引可以快速定位数据所在的数据页，可以避免通过全表扫描去定位数据页，就是一种最为有效的优化方式。
执行计划中“CSCN2”操作符是聚集索引扫描，可以简单的理解为全表扫描操作符，通常情况消除“CSCN2”就可以提升SQL的执行速度。

2.索引优化

上面的例子我只需要给T1表的NAME列增加一个索引就可以提升SQL的执行效率

SQL> create index idx_t1 on t1(name);
操作已执行
已用时间: 22.742(毫秒). 执行号:8100.
SQL> EXPLAIN select * from t1 where NAME='AAA';

1   #NSET2: [1, 1, 60]
2     #PRJT2: [1, 1, 60]; exp_num(3), is_atom(FALSE)
3       #BLKUP2: [1, 1, 60]; IDX_T1(T1)
4         #SSEK2: [1, 1, 60]; scan_type(ASC), IDX_T1(T1), scan_range['AAA','AAA']

已用时间: 0.613(毫秒). 执行号:0.
1
2
3
4
5
6
7
8
9
10
11

同样的语句执行计划中“CSCN2”不见了，变成了“SSEK2” 二级索引数据定位，测试数据量较少，大数据量下可以带来非常明显的性能提升。

在日常SQL优化过程中通过合理的创建索引可以解决80%以上的优化问题。

3.更新统计信息

  对象统计信息描述了对象数据的分布特征。统计信息是优化器的代价计算的依据，可以帮助优化器较精确地估算成本，对执行计划的选择起着至关重要的作用。 统计信息的收集频率是一把双刃剑，频率太低导致统计信息滞后，频率太高又影响查询性能，因此，系统管理员需要根据实际情况，合理安排统计信息收集的频率。
  当发现运行一直很快的SQL突然变慢了，建了索引的列执行计划不走索引，都可以考虑是不是统计信息太久没有更新，数据的分布特征已经发生了变化。

#对库上所有模式下的所有用户表以及表上的所有索引生成统计信息
CALL SP_DB_STAT_INIT ();

#更新用户的索引的统计信息
SP_INDEX_STAT_INIT(USER,'IDX_C1_T1');  

#更新某一个表上的指定索引
stat 100 on index EBASEINFO_UNISCID_INDEX;

#更新某一个表的某一列信息
stat 100 on  E_FR_EPBBASEINFO(ENTNAME);

#收集表的统计信息
DBMS_STATS.GATHER_TABLE_STATS('模式名','表名',null,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
    
#收集列的统计信息
SP_COL_STAT_INIT('模式名','表名','列名');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

---

达梦技术社区地址：https://eco.dameng.com