修改租户参数

连接sys租户修改租户参数

使用root连接sys租户

[admin]# mysql -h192.168.1.71 -P2883 -D oceanbase -uroot@sys -pOceanBase010 -A

查看参数

mysql> show parameters like 'writing_throttling_trigger_percentage';
+-------+----------+--------------+----------+---------------------------------------+-----------+-------+-------------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+
| zone  | svr_type | svr_ip       | svr_port | name                                  | data_type | value | info                                                                                                                                      | section | scope  | source  | edit_level        |
+-------+----------+--------------+----------+---------------------------------------+-----------+-------+-------------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+
| zone3 | observer | 192.168.1.73 |     2882 | writing_throttling_trigger_percentage | NULL      | 60    | the threshold of the size of the mem store when writing_limit will be triggered. Rang:(0，100]. setting 100 means turn off writing limit  | TRANS   | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
| zone2 | observer | 192.168.1.72 |     2882 | writing_throttling_trigger_percentage | NULL      | 60    | the threshold of the size of the mem store when writing_limit will be triggered. Rang:(0，100]. setting 100 means turn off writing limit  | TRANS   | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
| zone1 | observer | 192.168.1.71 |     2882 | writing_throttling_trigger_percentage | NULL      | 60    | the threshold of the size of the mem store when writing_limit will be triggered. Rang:(0，100]. setting 100 means turn off writing limit  | TRANS   | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
+-------+----------+--------------+----------+---------------------------------------+-----------+-------+-------------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+

修改my_tenant租户的参数

mysql> alter system set writing_throttling_trigger_percentage='70' server='192.168.1.71:2882' tenant='my_tenant';
Query OK, 0 rows affected (0.50 sec)

查看租户参数

mysql> show parameters like 'writing_throttling_trigger_percentage';
+-------+----------+--------------+----------+---------------------------------------+-----------+-------+-------------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+
| zone  | svr_type | svr_ip       | svr_port | name                                  | data_type | value | info                                                                                                                                      | section | scope  | source  | edit_level        |
+-------+----------+--------------+----------+---------------------------------------+-----------+-------+-------------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+
| zone3 | observer | 192.168.1.73 |     2882 | writing_throttling_trigger_percentage | NULL      | 60    | the threshold of the size of the mem store when writing_limit will be triggered. Rang:(0，100]. setting 100 means turn off writing limit  | TRANS   | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
| zone2 | observer | 192.168.1.72 |     2882 | writing_throttling_trigger_percentage | NULL      | 60    | the threshold of the size of the mem store when writing_limit will be triggered. Rang:(0，100]. setting 100 means turn off writing limit  | TRANS   | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
| zone1 | observer | 192.168.1.71 |     2882 | writing_throttling_trigger_percentage | NULL      | 60    | the threshold of the size of the mem store when writing_limit will be triggered. Rang:(0，100]. setting 100 means turn off writing limit  | TRANS   | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
+-------+----------+--------------+----------+---------------------------------------+-----------+-------+-------------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+

连接my_tenant租户中查看参数

[admin]# mysql -h192.168.1.71 -P2883 -D oceanbase -uroot@my_tenant -pOceanBase010 -A

mysql> show parameters like 'writing_throttling_trigger_percentage';
+-------+----------+--------------+----------+---------------------------------------+-----------+-------+-------------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+
| zone  | svr_type | svr_ip       | svr_port | name                                  | data_type | value | info                                                                                                                                      | section | scope  | source  | edit_level        |
+-------+----------+--------------+----------+---------------------------------------+-----------+-------+-------------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+
| zone1 | observer | 192.168.1.71 |     2882 | writing_throttling_trigger_percentage | NULL      | 77    | the threshold of the size of the mem store when writing_limit will be triggered. Rang:(0，100]. setting 100 means turn off writing limit  | TRANS   | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
| zone2 | observer | 192.168.1.72 |     2882 | writing_throttling_trigger_percentage | NULL      | 70    | the threshold of the size of the mem store when writing_limit will be triggered. Rang:(0，100]. setting 100 means turn off writing limit  | TRANS   | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
| zone3 | observer | 192.168.1.73 |     2882 | writing_throttling_trigger_percentage | NULL      | 70    | the threshold of the size of the mem store when writing_limit will be triggered. Rang:(0，100]. setting 100 means turn off writing limit  | TRANS   | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
+-------+----------+--------------+----------+---------------------------------------+-----------+-------+-------------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+

 连接用户租户修改租户参数

 使用root连接my_tenant租户

[admin]# mysql -h192.168.1.71 -P2883 -D oceanbase -uroot@my_tenant#ob_cluster -pOceanBase010 -A

修改参数

mysql> alter system set writing_throttling_maximum_duration='1h' server='192.168.1.71:2882';
Query OK, 0 rows affected (0.33 sec)

 查看修改的参数

mysql> show parameters like 'writing_throttling_maximum_duration';
+-------+----------+--------------+----------+-------------------------------------+-----------+-------+--------------------------------------------------------------------------+---------+--------+---------+-------------------+
| zone  | svr_type | svr_ip       | svr_port | name                                | data_type | value | info                                                                     | section | scope  | source  | edit_level        |
+-------+----------+--------------+----------+-------------------------------------+-----------+-------+--------------------------------------------------------------------------+---------+--------+---------+-------------------+
| zone1 | observer | 192.168.1.71 |     2882 | writing_throttling_maximum_duration | NULL      | 1h    | maximum duration of writting throttling(in minutes), max value is 3 days | TRANS   | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
| zone2 | observer | 192.168.1.72 |     2882 | writing_throttling_maximum_duration | NULL      | 2h    | maximum duration of writting throttling(in minutes), max value is 3 days | TRANS   | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
| zone3 | observer | 192.168.1.73 |     2882 | writing_throttling_maximum_duration | NULL      | 2h    | maximum duration of writting throttling(in minutes), max value is 3 days | TRANS   | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
+-------+----------+--------------+----------+-------------------------------------+-----------+-------+--------------------------------------------------------------------------+---------+--------+---------+-------------------+

修改租户变量 

连接用户租户修改租户变量

 少数变量（如lower_case_table_names）需要在租户创建时候指定，修改变量前请参考OceanBase官方文档“系统参考-->系统变量（MySQL模式/Oracle模式）”。

ALTER TENANT语法

ALTER TENANT {tenant_name | ALL}
    [SET] [tenant_option_list] [opt_global_sys_vars_set];

tenant_option_list:
    tenant_option [, tenant_option ...]

tenant_option:
            COMMENT [=]'string' 
          | {CHARACTER SET | CHARSET} [=] charset_name 
          | COLLATE [=]  collation_name 
          | ZONE_LIST [=] (zone [, zone...]) 
          | PRIMARY_ZONE [=] zone 
          | RESOURCE_POOL_LIST [=](pool_name [, pool_name...]) 
          | DEFAULT TABLEGROUP [=] {NULL | tablegroup_name}
          | {READ ONLY | READ WRITE}
          | LOCALITY [=] 'locality_description'
          | RENAME GLOBAL_NAME TO new_tenant_name
      
opt_global_sys_vars_set:
      VARIABLES system_var_name = expr [,system_var_name = expr] ...

连接sys租户修改租户变量

查看变量语法

show [global | session] variables [like '%变量关键字符%'];
show [global | session] variables [where variable_name = '变量名'];

配置变量语法

set [global | session] 变量 = '变量值';

参考：

https://www.oceanbase.com/docs/common-oceanbase-database-cn-10000000001697402

https://www.oceanbase.com/docs/common-oceanbase-database-cn-10000000001700535

本栏目推荐文章
• Jmeter 参数化之用户参数
• 函数的参数类型
• 手把手教你MongoDB 数据库连接URL 格式、authSource参数
• 【LeetCode 2701. 连续递增交易】MySQL用户变量编程得到严格递增连续子序列的开始位置，结束位置，长度等统计信息
• 11-函数进阶-参数类型
• 【Leetcode 2474. 购买量严格增加的客户】MySQL用户变量编程解决严格递增连续子序列问题
• 【LeetCode 2494. 合并在同一个大厅重叠的活动】MySQL用户变量编程解决区间合并问题
• Ansible - 定义变量
• Python逆向爬虫入门教程: 酷狗音乐加密参数signature逆向解析
• Chrome 浏览器插件 V3 版本 Manifest.json 文件中 Action 的类型（Types）、方法（Methods）和事件（Events）的属性和参数解析

变量 OceanBase 参数变量oceanbase参数 oceanbase参数 系统 oceanbase参数 部分 字符串 字符 变量 参数 参数 变量 程序java 变量 用户 参数 变量 剧本 常用 参数 参数 变量shell 变量 参数shell 变量 函数 参数 过程