单副本 oceanbase-3.2.4.1 企业版(for ORACLE) ERROR 4013 (HY001): No memory or reach tenant memory limit

OceanBase
#1

【 使用环境 】 测试环境
【 OB 】
【 使用版本 】3.2.4
【问题描述】无缘无故报错
ERROR 4013 (HY001): No memory or reach tenant memory limit
【复现路径】问题出现前后相关操作
【附件及日志】推荐使用OceanBase敏捷诊断工具obdiag收集诊断信息,详情参见链接（右键跳转查看）：

【SOP系列 22 】——故障诊断第一步(自助诊断和诊断信息收集)

单机安装oceanbase的3.2.4版本，
启动运行没多久后（并未手动做任何数据库操作），报错如下：
开始都正常的，不清楚是什么逐渐占用了数据库内存？

[admin@lnpg log]$ obclient -h192.168.207.143 -P2881 -uadmin@t_lndb -padmin -A
ERROR 4013 (HY001): No memory or reach tenant memory limit
[admin@lnpg log]$ free -g
              total        used        free      shared  buff/cache   available
Mem:             31          26           0           0           4           4
Swap:             7           7           0
[admin@lnpg log]$ 
[admin@lnpg log]$ ls -lt|head
总用量 63022936
-rw-r--r-- 1 admin admin   7685552 6月  27 14:36 observer.log
-rw-r--r-- 1 admin admin    569383 6月  27 14:36 observer.log.wf
-rw-r--r-- 1 admin admin 199791545 6月  27 14:36 rootservice.log
-rw-r--r-- 1 admin admin  11797566 6月  27 14:36 rootservice.log.wf
-rw-r--r-- 1 admin admin 268435825 6月  27 14:36 observer.log.20240627143616297
-rw-r--r-- 1 admin admin  19512919 6月  27 14:36 observer.log.wf.20240627143616297
-rw-r--r-- 1 admin admin   3138577 6月  27 14:35 election.log
-rw-r--r-- 1 admin admin 268435482 6月  27 14:32 observer.log.20240627143221340
-rw-r--r-- 1 admin admin  19420650 6月  27 14:32 observer.log.wf.20240627143221340
[admin@lnpg log]$ tail -10 observer.log
[2024-06-27 14:36:28.139234] WDIAG [SERVER] ob_inner_sql_connection.cpp:906 [14578][0][YB42C0A8CF8F-00061BC6C737FE67-0-0] [lt=6] [dc=0][errcode=-4013] failed to close result(close_ret=-4006, ret=-4013)
[2024-06-27 14:36:28.139245] ERROR issue_dba_error (ob_log.cpp:2322) [14578][0][YB42C0A8CF8F-00061BC6C737FE67-0-0] [lt=2] [dc=0][errcode=-4388] Unexpected internal error happen, please checkout the internal errcode(errcode=-4013, file="ob_malloc.h", line_no=54, info="allocate memory fail")
[2024-06-27 14:36:28.139252] EDIAG [LIB] ob_malloc.h:54 [14578][0][YB42C0A8CF8F-00061BC6C737FE67-0-0] [lt=6] [dc=0][errcode=-4013] allocate memory fail(attr=tenant_id=1, label=MysqlRequesReco, ctx_id=0, prio=0, nbyte=2088960) BACKTRACE:0x1142dc68 0x1141f093 0x6244a74 0x624458f 0x6244307 0x627dadf 0x627d86f 0x627a53e 0x603b743 0xcc25235 0xfbc2770 0xcffebbb 0xd001173 0xd008c31 0xd009578 0x10d6a4ac 0xc4780db 0xc47ab84 0xc486447 0x620e02d 0x620dab2 0x6376bee 0x10daac5f 0x10daaa9a 0x11d4aa6f
[2024-06-27 14:36:28.139261] WDIAG [SERVER] ob_inner_sql_connection.cpp:931 [14578][0][YB42C0A8CF8F-00061BC6C737FE67-0-0] [lt=8] [dc=0][errcode=-4013] failed to process record(executor={ObIExecutor:, sql:"     update __all_weak_read_service set min_version=1719470188073115, max_version=1719470188073115     where level_id = 0 and level_value = '' and min_version = 1719447944749745 and max_version = 1719447944749745 "}, record_ret=-4013, ret=-4013)
[2024-06-27 14:36:28.139270] WDIAG [SERVER] ob_inner_sql_connection.cpp:953 [14578][0][YB42C0A8CF8F-00061BC6C737FE67-0-0] [lt=6] [dc=0][errcode=-4013] failed to process final(executor={ObIExecutor:, sql:"     update __all_weak_read_service set min_version=1719470188073115, max_version=1719470188073115     where level_id = 0 and level_value = '' and min_version = 1719447944749745 and max_version = 1719447944749745 "}, aret=-4013, ret=-4013)
[2024-06-27 14:36:28.139276] WDIAG [SERVER] ob_inner_sql_connection.cpp:1386 [14578][0][Y0-0000000000000000-0-0] [lt=4] [dc=0][errcode=-4013] execute sql failed(ret=-4013, tenant_id=1005, sql=     update __all_weak_read_service set min_version=1719470188073115, max_version=1719470188073115     where level_id = 0 and level_value = '' and min_version = 1719447944749745 and max_version = 1719447944749745 )
[2024-06-27 14:36:28.139284] WDIAG [SERVER] ob_inner_sql_connection.cpp:1404 [14578][0][Y0-0000000000000000-0-0] [lt=3] [dc=0][errcode=-4013] execute_write failed(ret=-4013, tenant_id=1005, sql="     update __all_weak_read_service set min_version=1719470188073115, max_version=1719470188073115     where level_id = 0 and level_value = '' and min_version = 1719447944749745 and max_version = 1719447944749745 ")
[2024-06-27 14:36:28.139291] WDIAG [COMMON.MYSQLP] ob_mysql_proxy.cpp:117 [14578][0][Y0-0000000000000000-0-0] [lt=5] [dc=0][errcode=-4013] execute sql failed(ret=-4013, conn=0x7f6677b6cbd0, start=1719470188138927, sql="     update __all_weak_read_service set min_version=1719470188073115, max_version=1719470188073115     where level_id = 0 and level_value = '' and min_version = 1719447944749745 and max_version = 1719447944749745 ")
[2024-06-27 14:36:28.139309] WDIAG [STORAGE.TRANS] ob_tenant_weak_read_cluster_service.cpp:298 [14578][0][Y0-0000000000000000-0-0] [lt=3] [dc=0][errcode=-4013] execute update cluster weak read version sql fail(ret=-4013, ret="OB_ALLOCATE_MEMORY_FAILED", tenant_id=1005, sql=     update __all_weak_read_service set min_version=1719470188073115, max_version=1719470188073115     where level_id = 0 and level_value = '' and min_version = 1719447944749745 and max_version = 1719447944749745 , affected_rows=0)
[2024-06-27 14:36:28.139321] WDIAG [STORAGE.TRANS] ob_tenant_weak_read_cluster_service.cpp:640 [14578][0][Y0-0000000000000000-0-0] [lt=7] [dc=0][errcode=-4013] persist CLUSTER weak read version if need fail(ret=-4013, ret="OB_ALLOCATE_MEMORY_FAILED", wrs_pkey_={tid:1105009185915106, partition_id:0, part_cnt:0}, min_version_=1719447944749745, max_version_=1719447944749745, new_min_version=1719470188073115, new_max_version=1719470188073115, record_exist=true, affected_rows=0, error_count_for_change_leader_=517, last_error_tstamp_for_change_leader_=1719470188139316)
[admin@lnpg log]$ 
[admin@lnpg log]$ tail -10 rootservice.log
[2024-06-27 14:36:37.911036] WDIAG [RS] ob_freeze_info_manager.cpp:684 [13770][0][YB42C0A8CF8F-00061BC6C0D8CFBD-0-0] [lt=4] [dc=0][errcode=-4014] fail to get freeze info(ret=-4014)
[2024-06-27 14:36:37.911038] WDIAG [RS] ob_freeze_info_updater.cpp:134 [13770][0][YB42C0A8CF8F-00061BC6C0D8CFBD-0-0] [lt=2] [dc=0][errcode=-4014] fail to broadcase freeze info(ret=-4014)
[2024-06-27 14:36:37.911041] WDIAG [RS] ob_freeze_info_updater.cpp:101 [13770][0][YB42C0A8CF8F-00061BC6C0D8CFBD-0-0] [lt=3] [dc=0][errcode=-4014] fail to broadcase freeze info(ret=-4014)
[2024-06-27 14:36:37.911045] INFO  [RS] ob_freeze_info_manager.cpp:278 [13770][0][YB42C0A8CF8F-00061BC6C0D8CFBD-0-0] [lt=3] [dc=0] invalid freeze info in memory, need reload
[2024-06-27 14:36:37.911466] ERROR issue_dba_error (ob_log.cpp:2322) [13770][0][YB42C0A8CF8F-00061BC6C0D8CFBD-0-0] [lt=2] [dc=0][errcode=-4388] Unexpected internal error happen, please checkout the internal errcode(errcode=-4013, file="ob_malloc.h", line_no=54, info="allocate memory fail")
[2024-06-27 14:36:37.911476] EDIAG [LIB] ob_malloc.h:54 [13770][0][YB42C0A8CF8F-00061BC6C0D8CFBD-0-0] [lt=9] [dc=0][errcode=-4013] allocate memory fail(attr=tenant_id=1, label=MysqlRequesReco, ctx_id=0, prio=0, nbyte=2088960) BACKTRACE:0x1142dc68 0x1141f093 0x6244a74 0x624458f 0x6244307 0x627dadf 0x627d86f 0x627a53e 0x603b743 0xcc25235 0xfbc2770 0xcffebbb 0xd001173 0xd0097f1 0xd009e1c 0x10d69d2c 0x10d699e4 0x62c77ad 0x63461b6 0x96860c0 0x9685aca 0x969de92 0x8e63de0 0x1156261d 0x11561f56 0x77ad8c7 0x10daac5f 0x10daaa9a 0x11d4aa6f
[2024-06-27 14:36:37.911519] WDIAG [RS] ob_freeze_info_manager.cpp:1090 [13770][0][YB42C0A8CF8F-00061BC6C0D8CFBD-0-0] [lt=13] [dc=0][errcode=-4014] freeze info is unvalid(ret=-4014, ret="OB_INNER_STAT_ERROR")
[2024-06-27 14:36:37.911526] WDIAG [RS] ob_freeze_info_updater.cpp:109 [13770][0][YB42C0A8CF8F-00061BC6C0D8CFBD-0-0] [lt=7] [dc=0][errcode=-4014] fail to check_snapshot_gc_ts(ret=-4014, ret="OB_INNER_STAT_ERROR")
[2024-06-27 14:36:37.912006] ERROR issue_dba_error (ob_log.cpp:2322) [13770][0][YB42C0A8CF8F-00061BC6C0D8CFBD-0-0] [lt=3] [dc=0][errcode=-4388] Unexpected internal error happen, please checkout the internal errcode(errcode=-4013, file="ob_malloc.h", line_no=54, info="allocate memory fail")
[2024-06-27 14:36:37.912012] EDIAG [LIB] ob_malloc.h:54 [13770][0][YB42C0A8CF8F-00061BC6C0D8CFBD-0-0] [lt=5] [dc=0][errcode=-4013] allocate memory fail(attr=tenant_id=1, label=MysqlRequesReco, ctx_id=0, prio=0, nbyte=2088960) BACKTRACE:0x1142dc68 0x1141f093 0x6244a74 0x624458f 0x6244307 0x627dadf 0x627d86f 0x627a53e 0x603b743 0xcc25235 0xfbc2770 0xcffebbb 0xd001173 0xd0097f1 0xd009e1c 0x10d69d2c 0x10d699e4 0x7707987 0x969b9cf 0x969df23 0x8e63de0 0x1156261d 0x11561f56 0x77ad8c7 0x10daac5f 0x10daaa9a 0x11d4aa6f

安装参考的文档如下：
裸机部署单副本 oceanbase-3.2.4.1 企业版(for ORACLE)集群
https://www.modb.pro/db/1803064752108298240

#3

你好，你提的这个技术问题牵涉到OceanBase企业版范围内的功能细节。建议你通过以下方式寻求帮助：

1.如你所在的企业客户已签署OceanBase企业版销售合同，请你联系客户经理；

2.如你所在的企业客户尚未签署OceanBase企业版销售合同，你可通过OceanBase官网商务咨询页面留下你的联系方式，OceanBase企业版的业务顾问会在一个工作日内与你联系。

OceanBase官网商务咨询

https://www.oceanbase.com/contactus?fromPage=https%3A%2F%2Fwww.oceanbase.com%2Fsoftwarecenter-enterprise&dataSources=softwarecenter-enterprise_footercontact_d2022

#4

OceanBase分布式数据库-海量数据 笔笔算数参考下这个 内存相关问题

#5

就是测试的，明显的不正常。
[admin@lnpg ~]$ obclient -h127.0.0.1 -uroot -P2881 -p111111
ERROR 4013 (HY001): No memory or reach tenant memory limit
[admin@lnpg ~]$ free -g
total used free shared buff/cache available
Mem: 31 30 0 0 0 0
Swap: 7 1 6
[admin@lnpg ~]$ ps -ef|grep obser
admin 19819 1 73 14:58 ? 00:06:57 /home/admin/oceanbase/bin/observer -i ens192 -P 2882 -p 2881 -z zone1 -d /home/admin/oceanbase/store/obdemo -r 192.168.207.143:2882:2881 -c 10001 -n obdemo -o __min_full_resource_pool_memory=268435456,system_memory=16G,datafile_size=20G,config_additional_dir=/db/ob/admin/1/obdemo/etc3;/db/ob/admin/log1/obdemo/etc2
admin 21106 19795 0 15:07 pts/1 00:00:00 grep --color=auto obser
[admin@lnpg ~]$ kill -9 19819
[admin@lnpg ~]$ /home/admin/oceanbase/bin/observer -i ens192 -P 2882 -p 2881 -z zone1 -d /home/admin/oceanbase/store/obdemo -r ‘192.168.207.143:2882:2881’ -c 10001 -n obdemo -o “__min_full_resource_pool_memory=268435456,system_memory=6G,datafile_size=20G,config_additional_dir=/db/ob/admin/1/obdemo/etc3;/db/ob/admin/log1/obdemo/etc2”
/home/admin/oceanbase/bin/observer -i ens192 -P 2882 -p 2881 -z zone1 -d /home/admin/oceanbase/store/obdemo -r 192.168.207.143:2882:2881 -c 10001 -n obdemo -o __min_full_resource_pool_memory=268435456,system_memory=6G,datafile_size=20G,config_additional_dir=/db/ob/admin/1/obdemo/etc3;/db/ob/admin/log1/obdemo/etc2
devname: ens192
rpc port: 2882
mysql port: 2881
zone: zone1
data_dir: /home/admin/oceanbase/store/obdemo
rs list: 192.168.207.143:2882:2881
cluster id: 10001
appname: obdemo
optstr: __min_full_resource_pool_memory=268435456,system_memory=6G,datafile_size=20G,config_additional_dir=/db/ob/admin/1/obdemo/etc3;/db/ob/admin/log1/obdemo/etc2
[admin@lnpg ~]$ obclient -h127.0.0.1 -uroot -P2881 -p111111
ERROR 1045 (42000): Access denied for user ‘root’@‘xxx.xxx.xxx.xxx’ (using password: YES)
[admin@lnpg ~]$ obclient -h127.0.0.1 -uroot -P2881 -p111111
ERROR 1045 (42000): Access denied for user ‘root’@‘xxx.xxx.xxx.xxx’ (using password: YES)
[admin@lnpg ~]$ netstat -tunlp|grep 288
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 0.0.0.0:2881 0.0.0.0:* LISTEN 21124/observer
tcp 0 0 0.0.0.0:2882 0.0.0.0:* LISTEN 21124/observer
[admin@lnpg ~]$ obclient -h127.0.0.1 -uroot -P2881 -p111111
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221487622
Server version: OceanBase 3.2.4.1 (r101000052023010822-346aa35c32e99d1b82d713f75f0072c45bdf7aab) (Built Jan 8 2023 22:52:43)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

obclient [(none)]> select * from oceanbase.v$ob_cluster;
±-----------±-------------±---------------------------±-------------±---------------±------------±------------------±----------------------------±-----------------±---------------------------±-------------------±--------------------±--------------------±-----------------------------+
| cluster_id | cluster_name | created | cluster_role | cluster_status | switchover# | switchover_status | switchover_info | current_scn | standby_became_primary_scn | primary_cluster_id | protection_mode | protection_level | redo_transport_options |
±-----------±-------------±---------------------------±-------------±---------------±------------±------------------±----------------------------±-----------------±---------------------------±-------------------±--------------------±--------------------±-----------------------------+
| 10001 | obdemo | 2024-06-26 14:05:22.882883 | PRIMARY | VALID | 0 | NOT ALLOWED | NONE SYNCED STANDBY CLUSTER | 1719472123400753 | 0 | NULL | MAXIMUM PERFORMANCE | MAXIMUM PERFORMANCE | ASYNC NET_TIMEOUT = 30000000 |
±-----------±-------------±---------------------------±-------------±---------------±------------±------------------±----------------------------±-----------------±---------------------------±-------------------±--------------------±--------------------±-----------------------------+
1 row in set (0.009 sec)

obclient [(none)]> select * from oceanbase.v$tenant;
ERROR 1146 (42S02): Table ‘oceanbase.v$tenant’ doesn’t exist
obclient [(none)]> show tables;
ERROR 1046 (3D000): No database selected
obclient [(none)]> use oceanbase;
Database changed
obclient [oceanbase]> select table_name from user_tables;
ERROR 4013 (HY001): No memory or reach tenant memory limit
obclient [oceanbase]> select * from oceanbase.v$tenant;
ERROR 4013 (HY001): No memory or reach tenant memory limit
obclient [oceanbase]> alter system major freeze;
ERROR 4013 (HY001): No memory or reach tenant memory limit
obclient [oceanbase]> alter system major freeze;
ERROR 4013 (HY001): No memory or reach tenant memory limit
obclient [oceanbase]> alter system major freeze;
ERROR 4013 (HY001): No memory or reach tenant memory limit
obclient [oceanbase]> alter system major freeze;
ERROR 4013 (HY001): No memory or reach tenant memory limit
obclient [oceanbase]> alter system major freeze;
ERROR 4013 (HY001): No memory or reach tenant memory limit
obclient [oceanbase]> exit
Bye
[admin@lnpg ~]$ free -g
total used free shared buff/cache available
Mem: 31 26 3 0 1 4
Swap: 7 1 6
[admin@lnpg ~]$

#6

补充信息：
[admin@lnpg ~]$ free -g
total used free shared buff/cache available
Mem: 31 29 0 0 1 1
Swap: 7 1 6
[admin@lnpg ~]$ lscpu|grep CPU
CPU op-mode(s): 32-bit, 64-bit
CPU(s): 16
On-line CPU(s) list: 0-15
CPU 系列： 6
型号名称： Intel(R) Xeon(R) Gold 6258R CPU @ 2.70GHz
CPU MHz： 2693.671
NUMA 节点0 CPU： 0-15
[admin@lnpg ~]$ cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)
应该如何配置内存相关才合理呢？谢谢！

#7

现在仅仅是测试

#8

image
image815×120 3.53 KB

该主机的swap都耗尽了，此时机器 应该非常卡顿。物理内存不够，导致大量的页交换。

  1. 建议检查 该主机上是否只有observer在吃内存。
  2. 如果该主机上只运行了observer程序 ，建议检查observer的内存配置
#9

已经做了释放了。重新启动看前面回复内容。

#10
  1. 建议检查 该主机上是否只有observer在吃内存。
    –是的
  2. 如果该主机上只运行了observer程序 ，建议检查observer的内存配置
    –如何检查设置呢？谢谢！
#11

total used free shared buff/cache available
Mem: 31 30 0 0 0 0

内存是没了吧

#12

system_memory=6G 太多了。可以设置成2G

看下 show parameters LIKE ‘%log_disk_size%’;

show parameters like ‘%datafile%’ ;

show parameters LIKE ‘%memory%’;

#13

【SOP 系列 19】OceanBase 生态组件重启方式 - 社区问答- OceanBase社区-分布式数据库

image
image863×132 6.32 KB

#14

感谢！您要的信息如下：如何设置参数，能保证后续不再提示内存不足？


[admin@lnpg ~]$ free -g
              total        used        free      shared  buff/cache   available
Mem:             31          13          15           0           2          17
Swap:             7           4           3
[admin@lnpg ~]$ /home/admin/oceanbase/bin/observer -i ens192 -P 2882 -p 2881 -z zone1 -d /home/admin/oceanbase/store/obdemo -r '192.168.207.143:2882:2881' -c 10001 -n obdemo -o "__min_full_resource_pool_memory=268435456,system_memory=2G,datafile_size=20G,config_additional_dir=/db/ob/admin/1/obdemo/etc3;/db/ob/admin/log1/obdemo/etc2"
/home/admin/oceanbase/bin/observer -i ens192 -P 2882 -p 2881 -z zone1 -d /home/admin/oceanbase/store/obdemo -r 192.168.207.143:2882:2881 -c 10001 -n obdemo -o __min_full_resource_pool_memory=268435456,system_memory=2G,datafile_size=20G,config_additional_dir=/db/ob/admin/1/obdemo/etc3;/db/ob/admin/log1/obdemo/etc2
devname: ens192
rpc port: 2882
mysql port: 2881
zone: zone1
data_dir: /home/admin/oceanbase/store/obdemo
rs list: 192.168.207.143:2882:2881
cluster id: 10001
appname: obdemo
optstr: __min_full_resource_pool_memory=268435456,system_memory=2G,datafile_size=20G,config_additional_dir=/db/ob/admin/1/obdemo/etc3;/db/ob/admin/log1/obdemo/etc2
[admin@lnpg ~]$ obclient -h127.0.0.1 -uroot -P2881 -p111111
ERROR 1045 (42000): Access denied for user 'root'@'xxx.xxx.xxx.xxx' (using password: YES)
[admin@lnpg ~]$ obclient -h127.0.0.1 -uroot -P2881 -p111111
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221225476
Server version: OceanBase 3.2.4.1 (r101000052023010822-346aa35c32e99d1b82d713f75f0072c45bdf7aab) (Built Jan  8 2023 22:52:43)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient [(none)]> show parameters LIKE ‘%log_disk_size%’;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '‘%log_disk_size%’' at line 1
obclient [(none)]> show variables LIKE ‘%log_disk_size%’;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '‘%log_disk_size%’' at line 1
obclient [(none)]> show variables LIKE '%log_disk_size%';
Empty set (0.012 sec)

obclient [(none)]> show parameters LIKE '%log_disk_size%';
Empty set (0.002 sec)

obclient [(none)]> show parameters LIKE '%datafile%';
+-------+----------+-----------------+----------+--------------------------+-----------+-------+-------------------------------------------------------------------------------+---------+---------+---------+-------------------+
| zone  | svr_type | svr_ip          | svr_port | name                     | data_type | value | info                                                                          | section | scope   | source  | edit_level        |
+-------+----------+-----------------+----------+--------------------------+-----------+-------+-------------------------------------------------------------------------------+---------+---------+---------+-------------------+
| zone1 | observer | 192.168.207.143 |     2882 | datafile_disk_percentage | NULL      | 90    | the percentage of disk space used by the data files. Range: [5,99] in integer | SSTABLE | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| zone1 | observer | 192.168.207.143 |     2882 | datafile_size            | NULL      | 20G   | size of the data file. Range: [0, +∞)                                         | SSTABLE | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
+-------+----------+-----------------+----------+--------------------------+-----------+-------+-------------------------------------------------------------------------------+---------+---------+---------+-------------------+
2 rows in set (0.002 sec)

obclient [(none)]> show parameters LIKE '%memory%';
+-------+----------+-----------------+----------+-------------------------------------+-----------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
| zone  | svr_type | svr_ip          | svr_port | name                                | data_type | value | info                                                                                                                                                                                                                                   | section  | scope   | source  | edit_level        |
+-------+----------+-----------------+----------+-------------------------------------+-----------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
| zone1 | observer | 192.168.207.143 |     2882 | ob_esi_memory_limit                 | NULL      | 0M    | specifies the memory usage upper limit for the obesi process. Range: [64M,], 0 stands for adaptive                                                                                                                                     | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| zone1 | observer | 192.168.207.143 |     2882 | global_write_halt_residual_memory   | NULL      | 30    | disable write to memstore when observer memstore free memory(plus memory hold by blockcache) lower than this limit, Range: (0, 100)limit calc by (memory_limit - system_memory) * global_write_halt_residual_memory/100                | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| zone1 | observer | 192.168.207.143 |     2882 | global_major_freeze_residual_memory | NULL      | 40    | post global major freeze when observer memstore free memory(plus memory hold by frozen memstore and blockcache) reach this limit. Rang:(0，100)limit calc by (memory_limit - system_memory) * global_major_freeze_residual_memory/100  | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| zone1 | observer | 192.168.207.143 |     2882 | memory_chunk_cache_size             | NULL      | 0M    | the maximum size of memory cached by memory chunk cache. Range: [0M,], 0 stands for adaptive                                                                                                                                           | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| zone1 | observer | 192.168.207.143 |     2882 | memory_limit_percentage             | NULL      | 80    | the size of the memory reserved for internal use(for testing purpose). Range: [10, 90]                                                                                                                                                 | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| zone1 | observer | 192.168.207.143 |     2882 | sql_audit_memory_limit              | NULL      | 3G    | the maximum size of the memory used by SQL audit virtual table when the function is turned on. The upper limit is 3G, with defaut 10% of avaiable memory. Range: [64M, +∞]                                                             | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| zone1 | observer | 192.168.207.143 |     2882 | system_memory                       | NULL      | 2G    | the memory reserved for internal use which cannot be allocated to any outer-tenant, and should be determined to guarantee every server functions normally. Range: [0M,)                                                                | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| zone1 | observer | 192.168.207.143 |     2882 | rootservice_memory_limit            | NULL      | 2G    | max memory size which can be used by rs tenant The default value is 2G. Range: [2G,)                                                                                                                                                   | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| zone1 | observer | 192.168.207.143 |     2882 | memory_limit                        | NULL      | 20G   | the size of the memory reserved for internal use(for testing purpose). Range: [0M,)                                                                                                                                                    | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| zone1 | observer | 192.168.207.143 |     2882 | memory_reserved                     | NULL      | 500M  | the size of the system memory reserved for emergency internal use. Range: [10M, total size of memory]                                                                                                                                  | SSTABLE  | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
+-------+----------+-----------------+----------+-------------------------------------+-----------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
10 rows in set (0.002 sec)

obclient [(none)]> exit
Bye
[admin@lnpg ~]$ free -g
              total        used        free      shared  buff/cache   available
Mem:             31          23           5           0           2           7
Swap:             7           4           3
[admin@lnpg ~]$ free -g
              total        used        free      shared  buff/cache   available
Mem:             31          23           5           0           2           7
Swap:
#15

没过一会就又报内存不足了

obclient [(none)]> use oceanbase;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
obclient [oceanbase]> select * from GV$OB_MEMSTORE limit 1\G
ERROR 1146 (42S02): Table ‘oceanbase.GV$OB_MEMSTORE’ doesn’t exist
obclient [oceanbase]> status
ERROR 4013 (HY001): No memory or reach tenant memory limit
obclient [oceanbase]>
obclient [oceanbase]> exit
Bye
[admin@lnpg ~]$ free -g
total used free shared buff/cache available
Mem: 31 27 1 0 2 3
Swap: 7 4 3

#16

这个也看下SELECT a.svr_ip, a.svr_port, a.zone,b.status, cpu_total, cpu_assigned, round(mem_total / 1024 / 1024 / 1024, 2) AS mem_total_gb, round(mem_assigned / 1024 / 1024 / 1024, 2) AS mem_assign_gb, round((mem_total - mem_assigned) / 1024 / 1024 / 1024, 2) AS mem_free_gb, round(c.total_size / 1024 / 1024 / 1024, 2) AS disk_total_gb, round(c.used_size / 1024 / 1024 / 1024, 2) AS disk_assign_gb, round((c.total_size - c.used_size) / 1024 / 1024 / 1024, 2) AS disk_free_gb FROM oceanbase.__all_virtual_server_stat a JOIN oceanbase.__all_server b ON a.svr_ip = b.svr_ip AND a.svr_port = b.svr_port JOIN __all_virtual_disk_stat c ON c.svr_ip = b.svr_ip AND c.svr_port = b.svr_port ORDER BY a.zone, a.svr_ip\G;

#17

  1. 重启observer

  2. observer重启完成后，立即连接数据库，执行如下SQL:
    select
    zone,
    svr_ip,
    name,
    value
    from
    __all_virtual_tenant_parameter_stat
    where
    name in (
    ‘memory_limit’,
    ‘memory_limit_percentage’,
    ‘system_memory’,
    ‘memstore_limit_percentage’,
    ‘memory_reserved’,
    ‘rootservice_memory_limit’,
    ‘sql_audit_memory_limit’
    )
    order by
    name,
    svr_ip;

select
variable_name,
variable_value
from
information_schema.GLOBAL_VARIABLES
where
variable_name like(’%percentage%’)
order by
variable_name;

3.执行完步骤2后，重复执行如下SQL，每隔3秒执行一次。
select
tenant_id,
ip,
active / 1024 / 1024 USED_MB,
freeze_trigger / 1024 / 1024 freeze_MB,
freeze_cnt
FROM
gv$memstore;

select
tenant_id,
svr_ip,
mod_name,
sum(hold) / 1024 / 1024 module_sum_MB
from
oceanbase.__all_virtual_memory_info
group by
tenant_id,
svr_ip,
mod_name
having
module_sum_MB > 200
order by
tenant_id,
svr_ip,
module_sum_MB desc;

#18

执行如下：

[admin@lnpg ~]$ free -g
              total        used        free      shared  buff/cache   available
Mem:             31          22           4           0           4           8
Swap:             7           7           0
[admin@lnpg ~]$ ps -ef|grep observer
admin    25342     1 99 12:49 ?        00:02:55 /home/admin/oceanbase/bin/observer -i ens192 -P 2882 -p 2881 -z zone1 -d /home/admin/oceanbase/store/obdemo -r 192.168.207.143:2882:2881 -c 10001 -n obdemo -o __min_full_resource_pool_memory=268435456,system_memory=2G,datafile_size=20G,config_additional_dir=/db/ob/admin/1/obdemo/etc3;/db/ob/admin/log1/obdemo/etc2
admin    26406 21025  0 12:51 pts/1    00:00:00 grep --color=auto observer
[admin@lnpg ~]$ kill -9 25342
[admin@lnpg ~]$ 
[admin@lnpg ~]$ 
[admin@lnpg ~]$ 
[admin@lnpg ~]$ 
[admin@lnpg ~]$ free -g
              total        used        free      shared  buff/cache   available
Mem:             31           9          16           0           4          21
Swap:             7           7           0
[admin@lnpg ~]$ free -g
              total        used        free      shared  buff/cache   available
Mem:             31           9          16           0           4          21
Swap:             7           7           0
[admin@lnpg ~]$ /home/admin/oceanbase/bin/observer -i ens192 -P 2882 -p 2881 -z zone1 -d /home/admin/oceanbase/store/obdemo -r '192.168.207.143:2882:2881' -c 10001 -n obdemo -o "__min_full_resource_pool_memory=268435456,system_memory=4G,datafile_size=20G,config_additional_dir=/db/ob/admin/1/obdemo/etc3;/db/ob/admin/log1/obdemo/etc2"
/home/admin/oceanbase/bin/observer -i ens192 -P 2882 -p 2881 -z zone1 -d /home/admin/oceanbase/store/obdemo -r 192.168.207.143:2882:2881 -c 10001 -n obdemo -o __min_full_resource_pool_memory=268435456,system_memory=4G,datafile_size=20G,config_additional_dir=/db/ob/admin/1/obdemo/etc3;/db/ob/admin/log1/obdemo/etc2
devname: ens192
rpc port: 2882
mysql port: 2881
zone: zone1
data_dir: /home/admin/oceanbase/store/obdemo
rs list: 192.168.207.143:2882:2881
cluster id: 10001
appname: obdemo
optstr: __min_full_resource_pool_memory=268435456,system_memory=4G,datafile_size=20G,config_additional_dir=/db/ob/admin/1/obdemo/etc3;/db/ob/admin/log1/obdemo/etc2

[admin@lnpg ~]$ free -g
              total        used        free      shared  buff/cache   available
Mem:             31          15          10           0           5          15
Swap:             7           7           0
[admin@lnpg ~]$ obclient -h127.0.0.1 -uroot -P2881 -p111111
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221487617
Server version: OceanBase 3.2.4.1 (r101000052023010822-346aa35c32e99d1b82d713f75f0072c45bdf7aab) (Built Jan  8 2023 22:52:43)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient [(none)]> use oceanbase;
select
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

zone,
svr_ip,
name,
value
from
__all_virtual_tenant_parameter_stat
where
name in (
'memory_limit',
'memory_limit_percentage',
'system_memory',
'memstore_limit_percentage',
'memory_reserved',
'rootservice_memory_limit',
'sql_audit_memory_limit'
)
order by
name,
svr_ip;
Database changed
obclient [oceanbase]> select
    -> zone,
    -> svr_ip,
    -> name,
    -> value
    -> from
    -> __all_virtual_tenant_parameter_stat
    -> where
    -> name in (
    -> 'memory_limit',
    -> 'memory_limit_percentage',
    -> 'system_memory',
    -> 'memstore_limit_percentage',
    -> 'memory_reserved',
    -> 'rootservice_memory_limit',
    -> 'sql_audit_memory_limit'
    -> )
    -> order by
    -> name,
    -> svr_ip;
+-------+-----------------+---------------------------+-------+
| zone  | svr_ip          | name                      | value |
+-------+-----------------+---------------------------+-------+
| zone1 | 192.168.207.143 | memory_limit              | 20G   |
| zone1 | 192.168.207.143 | memory_limit_percentage   | 80    |
| zone1 | 192.168.207.143 | memory_reserved           | 500M  |
| zone1 | 192.168.207.143 | memstore_limit_percentage | 50    |
| zone1 | 192.168.207.143 | rootservice_memory_limit  | 2G    |
| zone1 | 192.168.207.143 | sql_audit_memory_limit    | 3G    |
| zone1 | 192.168.207.143 | system_memory             | 4G    |
+-------+-----------------+---------------------------+-------+
7 rows in set (0.003 sec)

obclient [oceanbase]> select
    -> variable_name,
    -> variable_value
    -> from
    -> information_schema.GLOBAL_VARIABLES
    -> where
    -> variable_name like('%percentage%')
    -> order by
    -> variable_name;
+-------------------------------------+----------------+
| variable_name                       | variable_value |
+-------------------------------------+----------------+
| ob_plan_cache_evict_high_percentage | 90             |
| ob_plan_cache_evict_low_percentage  | 50             |
| ob_plan_cache_percentage            | 5              |
| ob_reserved_meta_memory_percentage  | 10             |
| ob_sql_audit_percentage             | 3              |
| ob_sql_work_area_percentage         | 5              |
| ob_temp_tablespace_size_percentage  | 0              |
+-------------------------------------+----------------+
7 rows in set (0.009 sec)

obclient [oceanbase]> select
    -> tenant_id,
    -> ip,
    -> active / 1024 / 1024 USED_MB,
    -> freeze_trigger / 1024 / 1024 freeze_MB,
    -> freeze_cnt
    -> FROM
    -> gv$memstore;
+-----------+-----------------+-------------+------------------------+------------+
| tenant_id | ip              | USED_MB     | freeze_MB              | freeze_cnt |
+-----------+-----------------+-------------+------------------------+------------+
|         1 | 192.168.207.143 | 57.97367096 |            76.99995995 |          0 |
|       500 | 192.168.207.143 |  0.00000000 | 3078632557772.79999733 |          0 |
|      1005 | 192.168.207.143 | 17.99182892 |           151.19998932 |          1 |
|      1008 | 192.168.207.143 | 45.97911835 |           716.79998398 |          0 |
+-----------+-----------------+-------------+------------------------+------------+
4 rows in set (0.002 sec)

obclient [oceanbase]> 
obclient [oceanbase]> select
    -> tenant_id,
    -> svr_ip,
    -> mod_name,
    -> sum(hold) / 1024 / 1024 module_sum_MB
    -> from
    -> oceanbase.__all_virtual_memory_info
    -> group by
    -> tenant_id,
    -> svr_ip,
    -> mod_name
    -> having
    -> module_sum_MB > 200
    -> order by
    -> tenant_id,
    -> svr_ip,
    -> module_sum_MB desc;
+-----------+-----------------+------------------+---------------+
| tenant_id | svr_ip          | mod_name         | module_sum_MB |
+-----------+-----------------+------------------+---------------+
|       500 | 192.168.207.143 | CO_STACK         | 2994.98052979 |
|       500 | 192.168.207.143 | LogHotCache      | 1092.00000000 |
|       500 | 192.168.207.143 | CallbackTask     |  812.00195313 |
|       500 | 192.168.207.143 | LightyQueue      |  463.20596313 |
|       500 | 192.168.207.143 | FixedQueue       |  324.69847107 |
|       500 | 192.168.207.143 | PartitTableTask  |  306.35388184 |
|       500 | 192.168.207.143 | ConcurObjPool    |  298.64251709 |
|       500 | 192.168.207.143 | OB_KVSTORE_CACHE |  270.39654541 |
|       500 | 192.168.207.143 | IoControl        |  227.98437500 |
+-----------+-----------------+------------------+---------------+
9 rows in set (0.008 sec)

obclient [oceanbase]> 
obclient [oceanbase]> 
obclient [oceanbase]> exit
Bye
[admin@lnpg ~]$ obclient -h127.0.0.1 -uroot -P2881 -p111111
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221487632
Server version: OceanBase 3.2.4.1 (r101000052023010822-346aa35c32e99d1b82d713f75f0072c45bdf7aab) (Built Jan  8 2023 22:52:43)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient [(none)]> select tenant_id, svr_ip, mod_name, sum(hold) / 1024 / 1024 module_sum_MB from oceanbase.__all_virtual_memory_info group by tenant_id, svr_ip, mod_name having module_sum_MB > 200 order by tenant_id, svr_ip, module_sum_MB desc;
+-----------+-----------------+------------------+---------------+
| tenant_id | svr_ip          | mod_name         | module_sum_MB |
+-----------+-----------------+------------------+---------------+
|       500 | 192.168.207.143 | CO_STACK         | 2994.98052979 |
|       500 | 192.168.207.143 | LogHotCache      | 1092.00000000 |
|       500 | 192.168.207.143 | CallbackTask     |  812.00195313 |
|       500 | 192.168.207.143 | LightyQueue      |  463.20596313 |
|       500 | 192.168.207.143 | FixedQueue       |  324.69847107 |
|       500 | 192.168.207.143 | PartitTableTask  |  306.35388184 |
|       500 | 192.168.207.143 | ConcurObjPool    |  298.69781494 |
|       500 | 192.168.207.143 | OB_KVSTORE_CACHE |  270.39654541 |
|       500 | 192.168.207.143 | IoControl        |  227.98437500 |
+-----------+-----------------+------------------+---------------+
9 rows in set (0.009 sec)

obclient [(none)]> exit
Bye
[admin@lnpg ~]$ obclient -h192.168.207.143 -P2881 -uadmin@t_lndb -padmin -A
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221487638
Server version: OceanBase 3.2.4.1 (r101000052023010822-346aa35c32e99d1b82d713f75f0072c45bdf7aab) (Built Jan  8 2023 22:52:43)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient [ADMIN]> select zone, svr_ip, name, value from __all_virtual_tenant_parameter_stat where name in ( 'memory_limit', 'memory_limit_percentage', 'system_memory', 'memstore_limit_percentage', 'memory_reserved', 'rootservice_memory_limit', 'sql_audit_memory_limit' ) order by name, svr_ip;
ORA-00900: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '__all_virtual_tenant_parameter_stat where name in ( 'memory_limit', 'memory_limi' at line 1
obclient [ADMIN]> select table_name from user_tables;
+------------+
| TABLE_NAME |
+------------+
| TEST       |
| ABC        |
| AB         |
| A          |
| T1         |
+------------+
5 rows in set (0.074 sec)

obclient [ADMIN]> exit
Bye
[admin@lnpg ~]$ free -g
              total        used        free      shared  buff/cache   available
Mem:             31          20           6           0           5          10
Swap:             7           7           0
[admin@lnpg ~]$ free -g
              total        used        free      shared  buff/cache   available
Mem:             31          20           5           0           5          10
Swap:             7           7           0
[admin@lnpg ~]$ free -g
              total        used        free      shared  buff/cache   available
Mem:             31          20           5           0           5          10
Swap:             7           7           0
[admin@lnpg ~]$ free -g
              total        used        free      shared  buff/cache   available
Mem:             31          20           5           0           5          10
Swap:             7           7           0
[admin@lnpg ~]$ obclient -h127.0.0.1 -uroot -P2881 -p111111
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221487673
Server version: OceanBase 3.2.4.1 (r101000052023010822-346aa35c32e99d1b82d713f75f0072c45bdf7aab) (Built Jan  8 2023 22:52:43)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient [(none)]> select
    -> tenant_id,
    -> ip,
    -> active / 1024 / 1024 USED_MB,
    -> freeze_trigger / 1024 / 1024 freeze_MB,
    -> freeze_cnt
    -> FROM
    -> gv$memstore;
ERROR 4013 (HY001): No memory or reach tenant memory limit
obclient [(none)]> 
obclient [(none)]> select
    -> tenant_id,
    -> svr_ip,
    -> mod_name,
    -> sum(hold) / 1024 / 1024 module_sum_MB
    -> from
    -> oceanbase.__all_virtual_memory_info
    -> group by
    -> tenant_id,
    -> svr_ip,
    -> mod_name
    -> having
    -> module_sum_MB > 200
    -> order by
    -> tenant_id,
    -> svr_ip,
    -> module_sum_MB desc;
ERROR 4013 (HY001): No memory or reach tenant memory limit
obclient [(none)]>