KingbaseES运维案例之---服务进程（backend process）终止

最新推荐文章于 2023-12-14 19:06:24 发布
最新推荐文章于 2023-12-14 19:06:24 发布
阅读量778 收藏
点赞数
文章标签： 运维 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lyu1026/article/details/128399797
版权

​

案例说明：
如下图所示：KingbaseES服务进程结构

KingbaseES使用客户端/服务器的模型。 对于每个客户端的连接，KingbaseES主进程接收到客户端连接后，会为其创建一个新的服务进程。 KingbaseES 用服务进程来处理连接到数据库服务的客户端请求。 该进程负责实际处理客户端的数据库请求，连接断开时退出。当Client连接到数据库时，会有对应的kingbase的服务进程为其提供服务，如以下Client查询访问：

如下所示，操作系统对应的服务进程(backend process):

当Client结束访问正常退出数据库连接时，对应的kingbase的服务进程也将结束；但是当客户端异常退出时，会导致数据库端的kingbase服务进程没有正常结束，并占用数据库资源，本案例将详细描述手工方式对服务进程(backend process)终止。手工结束backend process可以使用数据库工具或者操作系统的kill进程方式，但是不同方式对数据库造成的影响不同。

适用版本： KingbaseES V8R3/R6

系统架构：

一、客户端访问

[kingbase@node1 bin]$ ./ksql -h 192.168.8.201 -U system -W prod
Password: 
ksql (V8.0)
Type "help" for help.

prod=#  select count(*) from t1;
 count  
--------
 100000
(1 row)

二、backend process终止方案

1、pg_terminate_backend(pid)方式

Tips：函数 pg_terminate_backend() 实际上是给进程发送了一个 SIGTERM 信号。

# 查询backend process状态信息
prod=# select * from sys_stat_activity where client_addr='192.168.8.200';
 datid | datname |  pid  | usesysid | usename | application_name |  client_addr  | client_hostname | client_port |         backend_start         | xact_start |          quer
y_start          |         state_change          | wait_event_type | wait_event | state | backend_xid | backend_xmin |          query           |  backend_type  
-------+---------+-------+----------+---------+------------------+---------------+-----------------+-------------+-------------------------------+------------+--------------
-----------------+-------------------------------+-----------------+------------+-------+-------------+--------------+--------------------------+----------------
 16385 | prod    | 17100 |       10 | system  | kingbase_*&+_    | 192.168.8.200 |                 |       57476 | 2022-11-29 15:04:57.131987+08 |            | 2022-11-29 15
:05:05.526379+08 | 2022-11-29 15:05:05.539018+08 | Client          | ClientRead | idle  |             |              | select count(*) from t1; | client backend
(1 row)

#终止backend process对应的pid
prod=# select pg_terminate_backend(17100);
 pg_terminate_backend 
----------------------
 t
(1 row)

#进程被终止
prod=# select * from sys_stat_activity where client_addr='192.168.8.200';
 datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | wait
_event_type | wait_event | state | backend_xid | backend_xmin | query | backend_type 
-------+---------+-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+------------+-------------+--------------+-----
------------+------------+-------+-------------+--------------+-------+--------------
(0 rows)

#如下所示，数据库进程正常，对应的backend pross被安全终止
[root@node2 sys_log]# ps -ef |grep kingbase
kingbase 13089     1  0 14:39 ?        00:00:00 /opt/Kingbase/ES/V8R6_054/KESRealPro/V008R006C005B0054/Server/bin/kingbase -D /db/kingbase/v8r6_054/data
kingbase 13090 13089  0 14:39 ?        00:00:00 kingbase: logger   
kingbase 16474 13089  0 14:59 ?        00:00:00 kingbase: checkpointer   
kingbase 16475 13089  0 14:59 ?        00:00:00 kingbase: background writer   
kingbase 16476 13089  0 14:59 ?        00:00:00 kingbase: walwriter   
kingbase 16477 13089  0 14:59 ?        00:00:00 kingbase: autovacuum launcher   
kingbase 16478 13089  0 14:59 ?        00:00:00 kingbase: stats collector   
kingbase 16479 13089  0 14:59 ?        00:00:00 kingbase: ksh writer   
kingbase 16480 13089  0 14:59 ?        00:00:00 kingbase: ksh collector   
kingbase 16481 13089  0 14:59 ?        00:00:00 kingbase: kwr collector   
kingbase 16482 13089  0 14:59 ?        00:00:00 kingbase: logical replication launcher   
kingbase 17100 13089  0 15:04 ?        00:00:00 kingbase: system prod 192.168.8.200(57476) idle
kingbase 17212 12416  0 15:05 pts/0    00:00:00 ./ksql -U system test
kingbase 17214 13089  0 15:05 ?        00:00:00 kingbase: system prod [local] idle

2、操作系统kill pid方式

#查询backend process状态信息
prod=# select * from sys_stat_activity where client_addr='192.168.8.200';
 datid | datname |  pid  | usesysid | usename | application_name |  client_addr  | client_hostname | client_port |         backend_start         | xact_start |          quer
y_start          |         state_change          | wait_event_type | wait_event | state | backend_xid | backend_xmin |          query           |  backend_type  
-------+---------+-------+----------+---------+------------------+---------------+-----------------+-------------+-------------------------------+------------+--------------
-----------------+-------------------------------+-----------------+------------+-------+-------------+--------------+--------------------------+----------------
 16385 | prod    | 18424 |       10 | system  | kingbase_*&+_    | 192.168.8.200 |                 |       57480 | 2022-11-29 15:15:26.813035+08 |            | 2022-11-29 15
:15:28.912910+08 | 2022-11-29 15:15:28.922719+08 | Client          | ClientRead | idle  |             |              | select count(*) from t1; | client backend
(1 row)

#操作系统下执行kill pid结束进程
[root@node2 sys_log]# kill 18424

#如下所示，数据库进程正常，对应的backend pross被安全kill
[root@node2 sys_log]# ps -ef |grep kingbase
kingbase 13089     1  0 14:39 ?        00:00:00 /opt/Kingbase/ES/V8R6_054/KESRealPro/V008R006C005B0054/Server/bin/kingbase -D /db/kingbase/v8r6_054/data
kingbase 13090 13089  0 14:39 ?        00:00:00 kingbase: logger   
kingbase 16474 13089  0 14:59 ?        00:00:00 kingbase: checkpointer   
kingbase 16475 13089  0 14:59 ?        00:00:00 kingbase: background writer   
kingbase 16476 13089  0 14:59 ?        00:00:00 kingbase: walwriter   
kingbase 16477 13089  0 14:59 ?        00:00:00 kingbase: autovacuum launcher   
kingbase 16478 13089  0 14:59 ?        00:00:00 kingbase: stats collector   
kingbase 16479 13089  0 14:59 ?        00:00:00 kingbase: ksh writer   
kingbase 16480 13089  0 14:59 ?        00:00:00 kingbase: ksh collector   
kingbase 16481 13089  0 14:59 ?        00:00:00 kingbase: kwr collector   
kingbase 16482 13089  0 14:59 ?        00:00:00 kingbase: logical replication launcher   
kingbase 17212 12416  0 15:05 pts/0    00:00:00 ./ksql -U system test
kingbase 17214 13089  0 15:05 ?        00:00:00 kingbase: system prod [local] idle

#在数据库视图中已经无此backend process记录
prod=# select * from sys_stat_activity where client_addr='192.168.8.200';
 datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | wait
_event_type | wait_event | state | backend_xid | backend_xmin | query | backend_type 
-------+---------+-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+------------+-------------+--------------+-----
------------+------------+-------+-------------+--------------+-------+--------------
(0 rows)

3、操作系统kill -15 pid和数据库sys_ctl kill TERM PID

1)操作系统kill -15 pid

#查询backend process状态信息
test=# select * from sys_stat_activity where client_addr='192.168.8.200';
 datid | datname |  pid  | usesysid | usename | application_name |  client_addr  | client_hostname | client_port |         backend_start         | xact_start |          quer
y_start          |         state_change          | wait_event_type | wait_event | state | backend_xid | backend_xmin |          query           |  backend_type  
-------+---------+-------+----------+---------+------------------+---------------+-----------------+-------------+-------------------------------+------------+--------------
-----------------+-------------------------------+-----------------+------------+-------+-------------+--------------+--------------------------+----------------
 16385 | prod    | 22955 |       10 | system  | kingbase_*&+_    | 192.168.8.200 |                 |       57498 | 2022-11-29 15:44:29.993305+08 |            | 2022-11-29 15
:44:32.090913+08 | 2022-11-29 15:44:32.100617+08 | Client          | ClientRead | idle  |             |              | select count(*) from t1; | client backend
(1 row)

#操作系统下执行kill -15 pid结束进程
[kingbase@node2 bin]$ kill -15 22955 

[kingbase@node2 bin]$ ps -ef |grep kingbase

#如下所示，数据库进程正常，对应的backend pross被安全kill
kingbase 13089     1  0 14:39 ?        00:00:00 /opt/Kingbase/ES/V8R6_054/KESRealPro/V008R006C005B0054/Server/bin/kingbase -D /db/kingbase/v8r6_054/data
kingbase 22197 13089  0 15:38 ?        00:00:00 kingbase: checkpointer   
kingbase 22198 13089  0 15:38 ?        00:00:00 kingbase: background writer   
kingbase 22199 13089  0 15:38 ?        00:00:00 kingbase: walwriter   
kingbase 22200 13089  0 15:38 ?        00:00:00 kingbase: autovacuum launcher   
kingbase 22201 13089  0 15:38 ?        00:00:00 kingbase: stats collector   
kingbase 22202 13089  0 15:38 ?        00:00:00 kingbase: ksh writer   
kingbase 22203 13089  0 15:38 ?        00:00:00 kingbase: ksh collector   
kingbase 22204 13089  0 15:38 ?        00:00:00 kingbase: kwr collector   
kingbase 22205 13089  0 15:38 ?        00:00:00 kingbase: logical replication launcher   
kingbase 22444 12416  0 15:40 pts/0    00:00:00 ./ksql -U system test
kingbase 22445 13089  0 15:40 ?        00:00:00 kingbase: system test [local] idle

#在数据库视图中已经无此backend process记录
test=# select * from sys_stat_activity where client_addr='192.168.8.200';
 datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | wait
_event_type | wait_event | state | backend_xid | backend_xmin | query | backend_type 
-------+---------+-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+------------+-------------+--------------+-----
------------+------------+-------+-------------+--------------+-------+--------------
(0 rows)

2)数据库sys_ctl kill TERM pid

#查询backend process状态信息
test=# select * from sys_stat_activity where client_addr='192.168.8.200';
 datid | datname |  pid  | usesysid | usename | application_name |  client_addr  | client_hostname | client_port |         backend_start         | xact_start |          quer
y_start          |         state_change          | wait_event_type | wait_event | state | backend_xid | backend_xmin |          query           |  backend_type  
-------+---------+-------+----------+---------+------------------+---------------+-----------------+-------------+-------------------------------+------------+--------------
-----------------+-------------------------------+-----------------+------------+-------+-------------+--------------+--------------------------+----------------
 16385 | prod    | 22443 |       10 | system  | kingbase_*&+_    | 192.168.8.200 |                 |       57494 | 2022-11-29 15:40:42.804868+08 |            | 2022-11-29 15
:40:44.972533+08 | 2022-11-29 15:40:44.985340+08 | Client          | ClientRead | idle  |             |              | select count(*) from t1; | client backend
(1 row)

#执行数据库命令kill进程
[kingbase@node2 bin]$ ./sys_ctl kill  TERM 22443

#如下所示，数据库进程正常，对应的backend pross被安全kill
[kingbase@node2 bin]$ ps -ef |grep kingbase     
kingbase 13089     1  0 14:39 ?        00:00:00 /opt/Kingbase/ES/V8R6_054/KESRealPro/V008R006C005B0054/Server/bin/kingbase -D /db/kingbase/v8r6_054/data
kingbase 22197 13089  0 15:38 ?        00:00:00 kingbase: checkpointer   
kingbase 22198 13089  0 15:38 ?        00:00:00 kingbase: background writer   
kingbase 22199 13089  0 15:38 ?        00:00:00 kingbase: walwriter   
kingbase 22200 13089  0 15:38 ?        00:00:00 kingbase: autovacuum launcher   
kingbase 22201 13089  0 15:38 ?        00:00:00 kingbase: stats collector   
kingbase 22202 13089  0 15:38 ?        00:00:00 kingbase: ksh writer   
kingbase 22203 13089  0 15:38 ?        00:00:00 kingbase: ksh collector   
kingbase 22204 13089  0 15:38 ?        00:00:00 kingbase: kwr collector   
kingbase 22205 13089  0 15:38 ?        00:00:00 kingbase: logical replication launcher   
kingbase 22444 12416  0 15:40 pts/0    00:00:00 ./ksql -U system test
kingbase 22445 13089  0 15:40 ?        00:00:00 kingbase: system test [local] idle

#在数据库视图中已经无此backend process记录
test=# select * from sys_stat_activity where client_addr='192.168.8.200';
 datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | wait
_event_type | wait_event | state | backend_xid | backend_xmin | query | backend_type 
-------+---------+-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+------------+-------------+--------------+-----
------------+------------+-------+-------------+--------------+-------+--------------
(0 rows)

4、操作系统kill -3 pid和数据库sys_ctl kill QUIT PID

1)操作系统kill -3 pid

#查询backend process状态信息
prod=# select * from sys_stat_activity where client_addr='192.168.8.200';
 datid | datname |  pid  | usesysid | usename | application_name |  client_addr  | client_hostname | client_port |         backend_start         | xact_start |          quer
y_start          |         state_change          | wait_event_type | wait_event | state | backend_xid | backend_xmin |                                query                  
              |  backend_type  
-------+---------+-------+----------+---------+------------------+---------------+-----------------+-------------+-------------------------------+------------+--------------
-----------------+-------------------------------+-----------------+------------+-------+-------------+--------------+-------------------------------------------------------
--------------+----------------
 16385 | prod    | 18666 |       10 | system  | kingbase_*&+_    | 192.168.8.200 |                 |       57486 | 2022-11-29 15:17:34.726155+08 |            | 2022-11-29 15
:17:34.736202+08 | 2022-11-29 15:17:34.740584+08 | Client          | ClientRead | idle  |             |              | select setting from pg_settings where name = 'enable_u
pper_colname' | client backend
(1 row)

#操作系统下执行kill -3 pid结束进程
[root@node2 sys_log]# kill -3 18666

#如下所示，除了对应的backend process被终止外，其余的后台辅助进程也被终止并重启
[root@node2 sys_log]# ps -ef |grep kingbase
kingbase 13089     1  0 14:39 ?        00:00:00 /opt/Kingbase/ES/V8R6_054/KESRealPro/V008R006C005B0054/Server/bin/kingbase -D /db/kingbase/v8r6_054/data
kingbase 13090 13089  0 14:39 ?        00:00:00 kingbase: logger   
kingbase 17212 12416  0 15:05 pts/0    00:00:00 ./ksql -U system test
kingbase 18795 13089  0 15:18 ?        00:00:00 kingbase: startup 

#查看对应的sys_log日志(backend process被终止导致数据库的辅助进程也将重启)
prod=# select * from sys_stat_activity where client_addr='192.168.8.200';
WARNING:  terminating connection because of crash of another server process
DETAIL:  The kingbase has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
2022-11-29 15:18:06.741 CST [18666] WARNING:  terminating connection because of crash of another server process
2022-11-29 15:18:06.741 CST [18666] DETAIL:  The kingbase has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2022-11-29 15:18:06.741 CST [18666] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2022-11-29 15:18:06.742 CST [13089] LOG:  server process (PID 18666) exited with exit code 2
2022-11-29 15:18:06.742 CST [13089] DETAIL:  Failed process was running: select setting from pg_settings where name = 'enable_upper_colname'
2022-11-29 15:18:06.742 CST [13089] LOG:  terminating any other active server processes
2022-11-29 15:18:06.743 CST [17214] WARNING:  terminating connection because of crash of another server process
2022-11-29 15:18:06.743 CST [17214] DETAIL:  The kingbase has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2022-11-29 15:18:06.743 CST [17214] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2022-11-29 15:18:06.744 CST [16477] WARNING:  terminating connection because of crash of another server process
2022-11-29 15:18:06.744 CST [16477] DETAIL:  The kingbase has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2022-11-29 15:18:06.744 CST [16477] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2022-11-29 15:18:06.745 CST [13089] LOG:  all server processes terminated; reinitializing
2022-11-29 15:18:06.823 CST [18795] LOG:  database system was interrupted; last known up at 2022-11-29 14:59:17 CST
2022-11-29 15:19:29.897 CST [18795] LOG:  database system was not properly shut down; automatic recovery in progress
2022-11-29 15:19:30.063 CST [18795] LOG:  redo starts at 0/22935D8
2022-11-29 15:19:30.063 CST [18795] LOG:  redo wal segment count 2
2022-11-29 15:19:30.063 CST [18795] LOG:  invalid record length at 0/2293608: wanted 24, got 0
2022-11-29 15:19:30.063 CST [18795] LOG:  redo done at 0/22935D8
2022-11-29 15:19:30.739 CST [13089] LOG:  database system is ready to accept connections

#数据库服务重启后
[root@node2 sys_log]# ps -ef |grep kingbase
kingbase 13089     1  0 14:39 ?        00:00:00 /opt/Kingbase/ES/V8R6_054/KESRealPro/V008R006C005B0054/Server/bin/kingbase -D /db/kingbase/v8r6_054/data
kingbase 13090 13089  0 14:39 ?        00:00:00 kingbase: logger   
kingbase 17212 12416  0 15:05 pts/0    00:00:00 ./ksql -U system test
kingbase 18953 13089  0 15:19 ?        00:00:00 kingbase: checkpointer   
kingbase 18954 13089  0 15:19 ?        00:00:00 kingbase: background writer   
kingbase 18955 13089  0 15:19 ?        00:00:00 kingbase: walwriter   
kingbase 18957 13089  0 15:19 ?        00:00:00 kingbase: stats collector   
kingbase 18958 13089  0 15:19 ?        00:00:00 kingbase: ksh writer   
kingbase 18959 13089  0 15:19 ?        00:00:00 kingbase: ksh collector   
kingbase 18960 13089  0 15:19 ?        00:00:00 kingbase: kwr collector

---如上所示，kill -3 pid用于终止backend process将给数据库带来极大的风险 。

2)数据库sys_ctl kill QUIT PID

#查询backend process状态信息
test=# select * from sys_stat_activity where client_addr='192.168.8.200';
 datid | datname |  pid  | usesysid | usename | application_name |  client_addr  | client_hostname | client_port |         backend_start         | xact_start |          quer
y_start          |         state_change          | wait_event_type | wait_event | state | backend_xid | backend_xmin |          query           |  backend_type  
-------+---------+-------+----------+---------+------------------+---------------+-----------------+-------------+-------------------------------+------------+--------------
-----------------+-------------------------------+-----------------+------------+-------+-------------+--------------+--------------------------+----------------
 16385 | prod    | 21894 |       10 | system  | kingbase_*&+_    | 192.168.8.200 |                 |       57490 | 2022-11-29 15:36:10.034020+08 |            | 2022-11-29 15
:36:13.902728+08 | 2022-11-29 15:36:13.917841+08 | Client          | ClientRead | idle  |             |              | select count(*) from t1; | client backend
(1 row)

#执行数据库命令sys_ctl kill QUIT终止backend process
[kingbase@node2 bin]$ ./sys_ctl kill QUIT  21894

#如下所示，除了对应的backend process被终止外，其余的后台辅助进程也被终止并重启
[kingbase@node2 bin]$ ps -ef |grep kingbase
kingbase 13089     1  0 14:39 ?        00:00:00 /opt/Kingbase/ES/V8R6_054/KESRealPro/V008R006C005B0054/Server/bin/kingbase -D /db/kingbase/v8r6_054/data
kingbase 13090 13089  0 14:39 ?        00:00:00 kingbase: logger   
kingbase 21895 12416  0 15:36 pts/0    00:00:00 ./ksql -U system test
kingbase 22071 13089  0 15:37 ?        00:00:00 kingbase: startup   

#查看对应的sys_log日志(backend process被终止导致数据库的辅助进程也将重启)
test=# select * from sys_stat_activity where client_addr='192.168.8.200';
WARNING:  terminating connection because of crash of another server process
DETAIL:  The kingbase has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

2022-11-29 15:37:13.828 CST [21894] WARNING:  terminating connection because of crash of another server process
2022-11-29 15:37:13.828 CST [21894] DETAIL:  The kingbase has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2022-11-29 15:37:13.828 CST [21894] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2022-11-29 15:37:13.829 CST [13089] LOG:  server process (PID 21894) exited with exit code 2
2022-11-29 15:37:13.829 CST [13089] DETAIL:  Failed process was running: select count(*) from t1;
2022-11-29 15:37:13.829 CST [13089] LOG:  terminating any other active server processes
2022-11-29 15:37:13.830 CST [21896] WARNING:  terminating connection because of crash of another server process
2022-11-29 15:37:13.830 CST [21896] DETAIL:  The kingbase has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2022-11-29 15:37:13.830 CST [21896] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2022-11-29 15:37:13.831 CST [18956] WARNING:  terminating connection because of crash of another server process
2022-11-29 15:37:13.831 CST [18956] DETAIL:  The kingbase has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2022-11-29 15:37:13.831 CST [18956] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2022-11-29 15:37:13.833 CST [13089] LOG:  all server processes terminated; reinitializing
2022-11-29 15:37:13.933 CST [22071] LOG:  database system was interrupted; last known up at 2022-11-29 15:19:30 CST
2022-11-29 15:38:29.154 CST [22071] LOG:  database system was not properly shut down; automatic recovery in progress
2022-11-29 15:38:29.232 CST [22071] LOG:  redo starts at 0/2293680
2022-11-29 15:38:29.232 CST [22071] LOG:  redo wal segment count 2
2022-11-29 15:38:29.232 CST [22071] LOG:  invalid record length at 0/22936B0: wanted 24, got 0
2022-11-29 15:38:29.232 CST [22071] LOG:  redo done at 0/2293680
2022-11-29 15:38:29.767 CST [13089] LOG:  database system is ready to accept connection

---如上所示，sys_ctl kill QUIT pid用于终止backend process将给数据库带来极大的风险 。

5、操作系统kill -9 pid

#查询backend process状态信息
prod=# select * from sys_stat_activity where client_addr='192.168.8.200';
 datid | datname |  pid  | usesysid | usename | application_name  |  client_addr  | client_hostname | client_port |         backend_start         |          xact_start      
     |          query_start          |         state_change          | wait_event_type |     wait_event      | state  | backend_xid | backend_xmin |              query      
         |         backend_type         
-------+---------+-------+----------+---------+-------------------+---------------+-----------------+-------------+-------------------------------+--------------------------
-----+-------------------------------+-------------------------------+-----------------+---------------------+--------+-------------+--------------+-------------------------
---------+------------------------------

 16385 | prod    | 14114 |       10 | system  | kingbase_*&+_     | 192.168.8.200 |                 |       57472 | 2022-11-29 14:48:06.372451+08 |                          
     | 2022-11-29 14:50:16.618969+08 | 2022-11-29 14:50:16.627572+08 | Client          | ClientRead          | idle   |             |              | select count(*) from t1;
         | client backend
       
(1 rows)

#操作系统下执行kill -9 pid结束进程
[root@node2 ~]# kill -9  14114 

#如下所示，除了对应的backend process被终止外，其余的后台辅助进程也被终止并重启
[root@node2 ~]# ps -ef |grep kingbase
.......

kingbase 13089     1  0 14:39 ?        00:00:00 /opt/Kingbase/ES/V8R6_054/KESRealPro/V008R006C005B0054/Server/bin/kingbase -D /db/kingbase/v8r6_054/data
kingbase 13090 13089  0 14:39 ?        00:00:00 kingbase: logger   
kingbase 14010 12416  0 14:47 pts/0    00:00:00 ./ksql -U system test
kingbase 15651 13089  0 14:57 ?        00:00:00 kingbase: startup   

#查看对应的sys_log日志(backend process被终止导致数据库的辅助进程也将重启)
2022-11-29 14:58:00.093 CST [13089] LOG:  server process (PID 14114) was terminated by signal 9: Killed
2022-11-29 14:58:00.093 CST [13089] DETAIL:  Failed process was running: select count(*) from t1;
2022-11-29 14:58:00.093 CST [13089] LOG:  terminating any other active server processes
2022-11-29 14:58:00.093 CST [14602] WARNING:  terminating connection because of crash of another server process
2022-11-29 14:58:00.093 CST [14602] DETAIL:  The kingbase has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2022-11-29 14:58:00.093 CST [14602] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2022-11-29 14:58:00.095 CST [13095] WARNING:  terminating connection because of crash of another server process
2022-11-29 14:58:00.095 CST [13095] DETAIL:  The kingbase has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2022-11-29 14:58:00.095 CST [13095] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2022-11-29 14:58:00.384 CST [13089] LOG:  all server processes terminated; reinitializing
2022-11-29 14:58:00.464 CST [15651] LOG:  database system was interrupted; last known up at 2022-11-29 14:53:42 CST
2022-11-29 14:59:16.706 CST [15651] LOG:  database system was not properly shut down; automatic recovery in progress
2022-11-29 14:59:16.806 CST [15651] LOG:  redo starts at 0/2293488
2022-11-29 14:59:16.806 CST [15651] LOG:  redo wal segment count 2
2022-11-29 14:59:16.806 CST [15651] LOG:  invalid record length at 0/2293560: wanted 24, got 0
2022-11-29 14:59:16.806 CST [15651] LOG:  redo done at 0/2293530
2022-11-29 14:59:17.563 CST [13089] LOG:  database system is ready to accept connections

---如上所示，kill -9 pid用于终止backend process将给数据库带来极大的风险 。

三、总结

对于KingbaseES数据库中异常的backend proces可以采用手工方式终止，执行时选择的方式要注意对数据库带来的风险：

1)相对安全方式：pg_terminate_backend(pid)；kill pid；kill -15 pid；sys_ctl kill  TERM pid。
2)不安全方式： kill -3 pid；sys_ctl kill  QUIT pid；kill -9 pid。

注意：千万不要kill -9，SIGKILL没有信号处理函数，OS会直接停掉进程；Kingbase父进程发现子进程异常退出，会停掉所有进程，释放共享内存，
再重新申请共享内存，拉起所有进程。效果就等于异常重启，启动时肯定会需要时间redo，可能造成几分钟的停止服务。(除非后果可以接受，否则不要kill -9)。

Kingbase 研究院
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
人大金仓 KingbaseES V8驱动 kingbase8-8.2.0.jar
11-15
人大金仓KingbaseES是一款由中国人民大学开发的国产数据库管理系统，具有高性能、高安全性、高稳定性的特点。在本场景中，我们关注的是其V8版本的驱动程序——"kingbase8-8.2.0.jar"。这个JAR文件是Java应用程序归档...
KingbaseES-SQL语言参考手册.pdf
02-28
KingbaseES SQL语言参考手册是针对人大金仓数据库管理系统KingbaseES的一款详细指南，适用于对数据库管理和SQL语言感兴趣的读者。该手册旨在帮助用户理解和掌握如何使用SQL在KingbaseES中执行数据操作。 SQL...
结束连接的进程
岳麓丹枫
10-16 462
结束连接的进程 pg_terminate_backend 是pg的内部方法，另外还有一个叫pg_cancel_backend，这个方法在8.4以前的版本中就一直存在。这两个方法的区别在于，pg_cancel_backend 只是取消当前某一个进程的查询操作，但不能释放数据库连接。但pg_terminate_backend 可以在pg的后台杀死这个进程，从而释放出宝贵的连接资源 select...
KingbaseES V8R6 集群运维系列--sys_monitor.sh stop关闭集群分析
lyu1026的博客
03-02 384
案例说明： 对于KingbaseES V8R6集群关闭整个集群通过执行‘sys_monitor.sh stop’命令完成，本案例解析了在执行‘sys_monitor.sh stop’后，数据库的关闭方式及数据库访问带来的影响。 KingbaseES停止数据库方式：(sys_ctl) 参数-m控制数据库停止模式 smart fast immediate： smart 模式会等待活动的事务提交结束，...
金仓数据库KingbaseES V8R6 集群运维系列--sys_monitor_sh stop关闭集群分析
arthemis_14的博客
05-23 737
在集群执行sys_monitor.sh stop和sys_ctl fast stop关闭数据库服务时：1)都会调用’fast shutdown’方式关闭数据库。2)数据库终止并回滚正在执行的事务，断开客户端的连接。3)sys_monitor.sh stop执行时，数据库shutdown之前将会执行checkpoint，而sys_ctl fast stop不会执行checkpoint。更多信息，参见。
解决人大金仓数据库删除数据库失败的问题
weixin_43169078的博客
11-29 358
【代码】解决人大金仓数据库删除数据库失败的问题。
pg_cancel_backend与pg_terminate_backend函数的区别
weixin_34400525的博客
09-05 430
为什么80%的码农都做不了架构师？>>> ...
[维护]KingbaseES运维工具使用手册.pdf
最新发布
04-22
[维护]KingbaseES运维工具使用手册
postgresql 杀不死的会话
一醉轻王侯的技术博客
05-25 2546
正常情况下， 第一种、第二种方式即可杀死： 第一种是： SELECT pg_cancel_backend(PID); 这种方式只能kill select查询，对update、delete 及DML不生效) 第二种是： SELECT pg_terminate_backend(PID); 这种可以kill掉各种操作(select、update、delete、drop等)操作 kill -SIGINT 或者 kill -SIGTERM 等同于以上两种 kill https://serverfault.com/q
如何使用strace+pstack利器分析程序性能
tycoon的专栏
09-03 2543
如何使用strace+pstack利器分析程序性能   http://www.cnblogs.com/bangerlee/archive/2012/02/20/2356818.html  引言 有时我们需要对程序进行优化、减少程序响应时间。除了一段段地对代码进行时间复杂度分析，我们还有更便捷的方法吗？ 若能直接找到影响程序运行时间的函数调用，再有针对地对相关函数进行代
​ 金仓数据库KingbaseES数据库如何启停和重启
热门推荐
arthemis_14的博客
05-26 1万+
​金仓数据库KingbaseES数据库如何启停和重启
Postgresql取消正在执行的任务或强制终止正在执行的任务
brytlevson的博客
08-01 3560
使用 pg_terminate_backend 函数：如果需要强制终止任务，而不是仅取消执行，可以使用 pg_terminate_backend 函数。执行这些操作需要具有适当的权限。确保你有足够的权限来执行这些命令，或者使用具有适当权限的用户身份连接到数据库。此外，强制终止任务可能会导致未完成的事务被回滚，因此请谨慎使用。这将向所有处于活动状态的任务发送取消信号，并尝试终止它们的执行。此命令将强制终止所有活动状态的任务。
PostgreSQL 杀会话
tttt0611的博客
05-04 4144
PostgreSQL 杀会话
查看和终止 PostgreSQL 连接会话
Tony.Dong的专栏
07-09 1万+
当我们连接PostgreSQL遇到FATAL: sorry, too many clients already.或者FATAL: remaining connection slots are reserved for non-replication superuser connections这种连接数过多的错误时，可以通过pg_stat_activity或者ps命令查看连接进程。使用pg_cancel_backend()、pg_terminate_backend()或者kill命令终止正在运行的语句和进程。
解决postgresql数据库清空表数据一直转圈圈，始终无法删除的问题
weixin_44277893的博客
12-14 1269
解决postgresql数据库清空表数据一直转圈圈，始终无法删除的问题
KingbaseES等待事件分析
Leo_Sui的博客
10-02 141
当一个会话处于等待状态时，wait_event 与 wait_event_type 两列非空，表示会话正在等待的事件和等待事件类型。sys_stat_activity 里记录的等待事件是瞬时信息，没有对等待事件的时间进行累计，所以量化等待事件上存在一些问题。用户可以考虑多次人工采样来收集等待事件信息，如果在等待的连接较多，则需要等待事件对系统性能造成了较大影响，需要做具体分析。该视图能够知道数据库目前正在发生写什么：比如有多少个连接，客户端的情况，每个连接的状态，每个连接上的等待事件等。
jmeter 性能调优的两种方式
weixin_40863562的博客
11-09 2238
数据表如果缺失索引，大部分热数据又都在内存时（例如内存8G，热数据6G），此时数据库只能使用表扫描，并需要处理已在内存中的大量的无关记录，而耗费大量CPU。2)执行explain (query text)或explain (buffers true, analyze true, verbose true) (query text)命令，查看SQL的执行计划（注意，前者不会实际执行SQL，后者会实际执行而且能得到详细的执行信息），对其中的Table Scan涉及的表，建立索引。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值