![file](https://img2023.cnblogs.com/other/2685289/202308/2685289-20230829152524057-1800624819.png) 作者 | sqlboy-yuzhenc ## 背景介紹 在實際應用中,我們經常需要將特定的任務通知給特 ...
OceanBase的系統參數分為好幾個級別,下麵我將從級別從高到底一一介紹下OceanBase資料庫里的那些系統參數。
系統配置項(系統參數)
OceanBase的系統配置項,我們可以稱為parameter,是OB最高級別的系統參數。系統配置項分為集群級配置項和租戶級配置項。大部分需要關註的系統配置項都是集群級別的,parameter更多的是在集群級別對整個集群的各種功能進行配置。通過show parameters語法可以查詢系統配置項,可以看到scope為cluster即表示這是一個集群級別的配置項,edit_level為dynamic_effective表示這個參數動態修改生效。
修改集群級系統配置項也需要到sys租戶下修改,修改語句為:alter system set parameter=’xxxx’ 。對於集群級配置項,生效範圍為集群、zone、機器,修改集群級別的配置項也可以指定zone和server,如不指定那將對整個集群生效。此外,修改系統配置項會預設自動持久化到內部表和參數文件。
修改租戶級系統配置項在sys租戶和業務租戶下都可以修改,在sys租戶下修改需要指定TENANT='xxxx',在業務租戶下僅可以修改本租戶的系統配置項。
obclient> show parameters like '%enable_sql_audit%'; +-------+----------+------------+----------+------------------+-----------+-------+---------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+ | zone | svr_type | svr_ip | svr_port | name | data_type | value | info | section | scope | source | edit_level | +-------+----------+------------+----------+------------------+-----------+-------+---------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+ | zone1 | observer | 10.10.10.1 | 2882 | enable_sql_audit | NULL | True | specifies whether SQL audit is turned on. The default value is TRUE. Value: TRUE: turned on FALSE: turned off | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE | | zone2 | observer | 10.10.10.2 | 2882 | enable_sql_audit | NULL | True | specifies whether SQL audit is turned on. The default value is TRUE. Value: TRUE: turned on FALSE: turned off | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE | | zone3 | observer | 10.10.10.3 | 2882 | enable_sql_audit | NULL | True | specifies whether SQL audit is turned on. The default value is TRUE. Value: TRUE: turned on FALSE: turned off | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE | +-------+----------+------------+----------+------------------+-----------+-------+---------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+ obclient> ALTER SYSTEM SET mysql_port=8888; obclient> ALTER SYSTEM SET mysql_port=8888 ZONE='z1'; obclient> ALTER SYSTEM SET mysql_port=8888 SERVER='192.168.100.1:2882';
系統變數
OceanBase的系統變數,我們可以稱為variable,都是租戶級別的,在各個租戶里設置。這個就類似於MySQL的variables。不管是oracle還是mysql模式,都可以通過show variables like查看參數值,並且修改參數也是直接通過set來修改。此外,variable也像MySQL一樣分為global和session級別,修改參數時加上global就可以自動持久化。
obclient> show variables like '%ob_query_time%'; +------------------+----------+ | VARIABLE_NAME | VALUE | +------------------+----------+ | ob_query_timeout | 10000000 | +------------------+----------+ obclient> show variables like '%ob_compatibility_mode%'; +-----------------------+--------+ | VARIABLE_NAME | VALUE | +-----------------------+--------+ | ob_compatibility_mode | ORACLE | +-----------------------+--------+ SET ob_query_timeout = 20000000; SET GLOBAL ob_query_timeout = 20000000;
隱藏參數
除了系統配置項和系統變數,OB還有一些隱藏參數,__xx_xx格式的參數為隱藏參數。隱藏參數無法通過SHOW PARAMETERS語句來查詢,只能通過系統表查詢:oceanbase.__all_virtual_sys_parameter_stat。隱藏參數和系統配置項類似,也分為集群級和租戶級,修改和方式也和系統配置項一樣。
obclient> SELECT * FROM oceanbase.__all_virtual_sys_parameter_stat WHERE name='__easy_memory_limit'; +-------+----------+------------+----------+---------------------+-----------+-------+--------------+-------------------------------------------------------------------------------------+-------------+----------+---------------+---------+---------+-------------------+ | zone | svr_type | svr_ip | svr_port | name | data_type | value | value_strict | info | need_reboot | section | visible_level | scope | source | edit_level | +-------+----------+------------+----------+---------------------+-----------+-------+--------------+-------------------------------------------------------------------------------------+-------------+----------+---------------+---------+---------+-------------------+ | zone2 | observer | 10.10.10.1 | 2882 | __easy_memory_limit | NULL | 4G | NULL | max memory size which can be used by libeasy. The default value is 4G. Range: [1G,) | NULL | OBSERVER | NULL | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE | | zone3 | observer | 10.10.10.1 | 2882 | __easy_memory_limit | NULL | 4G | NULL | max memory size which can be used by libeasy. The default value is 4G. Range: [1G,) | NULL | OBSERVER | NULL | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE | | zone1 | observer | 10.10.10.1 | 2882 | __easy_memory_limit | NULL | 4G | NULL | max memory size which can be used by libeasy. The default value is 4G. Range: [1G,) | NULL | OBSERVER | NULL | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE | +-------+----------+------------+----------+---------------------+-----------+-------+--------------+-------------------------------------------------------------------------------------+-------------+----------+---------------+---------+---------+-------------------+ 3 rows in set (0.04 sec) obclient> SELECT count(*) FROM oceanbase.__all_virtual_sys_parameter_stat; +----------+ | count(*) | +----------+ | 1239 | +----------+ 1 row in set (0.02 sec)