## GreatDB系統引數調整

GreatDB SqlNode引數調整
![image.png](https://img-blog.csdnimg.cn/2145a748362c483287a540d1350d57d2.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5LiH6YeM5pWw5o2u5bqT,size_20,color_FFFFFF,t_70,g_se,x_16)
示例，SqlNode 所有節點調整 max_connections 引數值

```powershell
[root@greatdb1 ~]# greatsql -ugreatdb -pgreatdb -h192.168.0.81 -P3306

GreatDB Cluster[(none)]> select * from information_schema.greatdb_SqlNode_variables where variable_name='max_connections';
+-------------------+-----------------+----------------+
| SQLNODE           | VARIABLE_NAME   | VARIABLE_VALUE |
+-------------------+-----------------+----------------+
| 192.168.0.81:3306 | max_connections | 2000           |
| 192.168.0.82:3306 | max_connections | 2000           |
| 192.168.0.83:3306 | max_connections | 2000           |
+-------------------+-----------------+----------------+
3 rows in set (0.14 sec)

GreatDB Cluster[(none)]> CALL mysql.greatdb_set_var_for_all_SqlNodes('max_connections',1000,true);
Query OK, 1 row affected (0.04 sec)

GreatDB Cluster[(none)]> select * from information_schema.greatdb_SqlNode_variables where variable_name='max_connections';
+-------------------+-----------------+----------------+
| SQLNODE           | VARIABLE_NAME   | VARIABLE_VALUE |
+-------------------+-----------------+----------------+
| 192.168.0.81:3306 | max_connections | 1000           |
| 192.168.0.82:3306 | max_connections | 1000           |
| 192.168.0.83:3306 | max_connections | 1000           |
+-------------------+-----------------+----------------+
3 rows in set (0.14 sec)
```

## GreatDB DataNode引數調整
![image.png](https://img-blog.csdnimg.cn/46c440503b694274be460cebed786633.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5LiH6YeM5pWw5o2u5bqT,size_20,color_FFFFFF,t_70,g_se,x_16)
示例，調整節點datanode1的 max_connections 引數值

```powershell
[root@greatdb1 ~]# greatsql -ugreatdb -pgreatdb -h192.168.0.81 -P3306

GreatDB Cluster[(none)]> call mysql.greatdb_set_var_for_DataNode('datanode1','max_connections',2000,0);
Query OK, 1 row affected (0.03 sec)

## GreatDB 引數調整的傳統方式

如果需要的調整的引數不在 SqlNode 和 DataNode “可調整的引數列表”中，可以分別連線到每個 SqlNode 節點或每個 DataNode 節點進行修改。
例如 SESSION 級別調整 long_query_time 引數

GreatDB Cluster[(none)]> set session long_query_time=3;
Query OK, 0 rows affected (0.00 sec)

GreatDB Cluster[(none)]> select @@session.long_query_time;
+---------------------------+
| @@session.long_query_time |
+---------------------------+
|                  3.000000 |
+---------------------------+
1 row in set (0.00 sec)

GreatDB Cluster[(none)]> select @@global.long_query_time;
+--------------------------+
| @@global.long_query_time |
+--------------------------+
|                 0.300000 |
+--------------------------+
1 row in set (0.00 sec)
SESSION 級別調整的引數只在當前會話中生效，其他會話或其他新連線的會話均不生效。

例如 GLOBAL 級別調整 long_query_time 引數
[root@greatdb1 ~]# greatsql -ugreatdb -pgreatdb -h192.168.0.81 -P3306

GreatDB Cluster[(none)]> set global long_query_time=3;
Query OK, 0 rows affected (0.00 sec)

GreatDB Cluster[(none)]> select @@session.long_query_time;
+---------------------------+
| @@session.long_query_time |
+---------------------------+
|                  0.300000 |
+---------------------------+
1 row in set (0.00 sec)

GreatDB Cluster[(none)]> select @@global.long_query_time;
+--------------------------+
| @@global.long_query_time |
+--------------------------+
|                 3.000000 |
+--------------------------+
1 row in set (0.00 sec)

GreatDB Cluster[(none)]> exit
Bye
[root@greatdb1 ~]# greatsql -ugreatdb -pgreatdb -h192.168.0.81 -P3306

[root@greatdb1 ~]# /greatdb/sh/sqlnode3306.sh restart
[root@greatdb1 ~]# greatsql -ugreatdb -pgreatdb -h192.168.0.81 -P3306

GLOBAL 級別調整的引數在當前會話中不生效，在其他已連線的會話中也不生效，只在新連線中生效，但是例項重啟後不會生效。

使用 persist 選項修改引數會將配置資訊載入到配置檔案中，下次重啟例項會自動讀取配置檔案使引數生效

```powershell
[root@greatdb1 ~]# greatsql -ugreatdb -pgreatdb -h192.168.0.81 -P3306

GreatDB Cluster[(none)]> set persist long_query_time=3;
Query OK, 0 rows affected (0.00 sec)

GreatDB Cluster[(none)]> exit
Bye
[root@greatdb1 ~]# greatsql -ugreatdb -pgreatdb -h192.168.0.81 -P3306

GreatDB Cluster[(none)]> exit
Bye
[root@greatdb1 ~]# /greatdb/sh/sqlnode3306.sh restart
[root@greatdb1 ~]# greatsql -ugreatdb -pgreatdb -h192.168.0.81 -P3306

persist 就相當於 GLOBAL 級別的調整加配置持久化，重啟例項後依然生效。

傳統方式修改引數需要分別連線到每個 SqlNode 節點或每個

## DataNode 節點進行修改

```powershell
[root@greatdb2 ~]# greatsql -ugreatdb -pgreatdb -h192.168.0.82 -P3306
GreatDB Cluster[(none)]> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.300000 |
+-----------------+----------+
1 row in set (0.01 sec)
```

這裡可以看到，SQLNODE 192.168.0.81 修改的引數，SQLNODE 192.168.0.82 並不會自動同步。

## GreatDB 常用引數列表

![image.png
image.png](https://img-blog.csdnimg.cn/2ed4faad8be04442a46a8c9c035afaff.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5LiH6YeM5pWw5o2u5bqT,size_19,color_FFFFFF,t_70,g_se,x_16)

## GreatDB 系統檢視
![GreatDB 狀態屬性說明
狀態屬性	說明
greatdb_cluster_initialized	標識 SqlNode 上是否已經初始化 GreatDB cluster
greatdb_cluster_name	當前叢集的名稱
greatdb_cluster_user	叢集各節點間建立連線使用的使用者名稱
greatdb_read_only_mode	當為 true 時表示叢集當前處於只讀模式](https://img-blog.csdnimg.cn/40950e42e846498b8d5560fe88df4e4a.png)

```powershell
[root@greatdb1 ~]# greatsql -ugreatdb -pgreatdb -h192.168.0.81 -P3306

GreatDB Cluster[(none)]> \s
--------------
greatsql  Ver 8.0.25-15-greatdbcluster5.0.7-GA for Linux on x86_64 (GreatDB Cluster, Release GA, Revision fbb08fbed60)

Connection id:          38
Current database:
Current user:           greatdb@192.168.0.81
SSL:                    Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.25-15-greatdbcluster5.0.7-GA GreatDB Cluster, Release GA, Revision fbb08fbed60
Protocol version:       10
Connection:             192.168.0.81 via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:               3306
Binary data as:         Hexadecimal
Uptime:                 36 min 40 sec

Threads: 14  Questions: 1362  Slow queries: 0  Opens: 207  Flush tables: 3  Open tables: 115  Queries per second avg: 0.619
```

--------------
GreatDB 日常節點巡檢檢視
![檢視名稱	說明
GREATDB_SHARDS	叢集中的 Shard 及其狀態
GREATDB_TABLE_DISTRIBUTION	表型別、在後端 Shard 的分佈情況以及後端表引擎型別
GREATDB_SQLNODES	叢集中的 SqlNode 及其狀態
GREATDB_DCSTATS	當前 SqlNode 中字典快取使用情況
GREATDB_SQLNODE_STATUS	各個 SqlNode 部分 status 的值
GREATDB_SQLNODE_VARIABLES	各個 SqlNode 部分 variables 的值
GREATDB_DATANODES	叢集中的 DataNode 及其狀態
GREATDB_DATANODE_STATUS	各個 DataNode 部分 status 屬性值
GREATDB_DATANODE_VARIABLES	各個 DataNode 部分 variables 的值
GREATDB_CONNECTION_POOL	SqlNode 到 DataNode 連線池狀態](https://img-blog.csdnimg.cn/f96e3c36ab934d609ec52db48cba2a56.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5LiH6YeM5pWw5o2u5bqT,size_16,color_FFFFFF,t_70,g_se,x_16)

GreatDB Cluster[(none)]> select * from information_schema.GREATDB_DCSTATS;
+------------+------------+-------------------+-----------------+--------------+
| CACHE_NAME | CACHE_SIZE | CACHE_INSTANCE_NO | CACHED_ELEMENTS | LRU_ELEMENTS |
+------------+------------+-------------------+-----------------+--------------+
| SQLNODE    |          0 |                 1 |               3 |            0 |
| SHARD      |          0 |                 1 |               3 |            0 |
| DATANODE   |          0 |                 1 |               9 |            0 |
| TABLE      |      20480 |                 8 |               0 |            0 |
+------------+------------+-------------------+-----------------+--------------+
4 rows in set (0.00 sec)

GreatDB Cluster[(none)]> select * from information_schema.GREATDB_SQLNODE_STATUS where VARIABLE_NAME='greatdb_cluster_initialized';
+-------------------+-----------------------------+----------------+
| SQLNODE           | VARIABLE_NAME               | VARIABLE_VALUE |
+-------------------+-----------------------------+----------------+
| 192.168.0.81:3306 | greatdb_cluster_initialized | ON             |
| 192.168.0.82:3306 | greatdb_cluster_initialized | ON             |
| 192.168.0.83:3306 | greatdb_cluster_initialized | ON             |
+-------------------+-----------------------------+----------------+
3 rows in set (0.03 sec)

GreatDB Cluster[(none)]> select * from information_schema.GREATDB_SQLNODE_VARIABLES where VARIABLE_NAME='long_query_time';
+-------------------+-----------------+----------------+
| SQLNODE           | VARIABLE_NAME   | VARIABLE_VALUE |
+-------------------+-----------------+----------------+
| 192.168.0.81:3306 | long_query_time | 3.000000       |
| 192.168.0.82:3306 | long_query_time | 3.000000       |
| 192.168.0.83:3306 | long_query_time | 0.300000       |
+-------------------+-----------------+----------------+
3 rows in set (0.06 sec)

GreatDB Cluster[(none)]> select * from information_schema.GREATDB_DATANODES;
+---------+-----------+----------+------------+--------------+------+-----------+---------------+-----------------------------------------------------------+-----------------------------------------------------------+
| NODE_ID | NODE_NAME | SHARD_ID | SHARD_NAME | HOST         | PORT | NODE_TYPE | NODE_STATE    | EXECUTED_GTID                                             | RECEIVED_TRANSACTION_SET                                  |
+---------+-----------+----------+------------+--------------+------+-----------+---------------+-----------------------------------------------------------+-----------------------------------------------------------+
|      36 | datanode1 |       36 | shard1     | 192.168.0.81 | 4406 | NODE_MGR  | STATE_STANDBY | 33360000-0000-0000-0000-000000000000:1-13:1000011-1000021 | 33360000-0000-0000-0000-000000000000:1-13:1000011-1000021 |
|      43 | datanode2 |       36 | shard1     | 192.168.0.82 | 4406 | NODE_MGR  | STATE_ACTIVE  | 33360000-0000-0000-0000-000000000000:1-13:1000011-1000021 | 33360000-0000-0000-0000-000000000000:1-13:1000011-1000021 |
|      50 | datanode3 |       36 | shard1     | 192.168.0.83 | 4406 | NODE_MGR  | STATE_STANDBY | 33360000-0000-0000-0000-000000000000:1-13:1000011-1000021 | 33360000-0000-0000-0000-000000000000:1-13:1000011-1000021 |
|      64 | datanode4 |       64 | shard2     | 192.168.0.81 | 4407 | NODE_MGR  | STATE_ACTIVE  | 36340000-0000-0000-0000-000000000000:1-28                 | 36340000-0000-0000-0000-000000000000:1-28                 |
|      71 | datanode5 |       64 | shard2     | 192.168.0.82 | 4407 | NODE_MGR  | STATE_STANDBY | 36340000-0000-0000-0000-000000000000:1-28                 | 36340000-0000-0000-0000-000000000000:1-28                 |
|      78 | datanode6 |       64 | shard2     | 192.168.0.83 | 4407 | NODE_MGR  | STATE_STANDBY | 36340000-0000-0000-0000-000000000000:1-28                 | 36340000-0000-0000-0000-000000000000:1-28                 |
|      92 | datanode7 |       92 | shard3     | 192.168.0.81 | 4408 | NODE_MGR  | STATE_ACTIVE  | 39320000-0000-0000-0000-000000000000:1-14                 | 39320000-0000-0000-0000-000000000000:1-14                 |
|      99 | datanode8 |       92 | shard3     | 192.168.0.82 | 4408 | NODE_MGR  | STATE_STANDBY | 39320000-0000-0000-0000-000000000000:1-14                 | 39320000-0000-0000-0000-000000000000:1-14                 |
|     106 | datanode9 |       92 | shard3     | 192.168.0.83 | 4408 | NODE_MGR  | STATE_STANDBY | 39320000-0000-0000-0000-000000000000:1-14                 | 39320000-0000-0000-0000-000000000000:1-14                 |
+---------+-----------+----------+------------+--------------+------+-----------+---------------+-----------------------------------------------------------+-----------------------------------------------------------+
9 rows in set (0.01 sec)

GreatDB Cluster[(none)]> select * from information_schema.GREATDB_CONNECTION_POOL;
+-----------+-------+------+
| NODE_NAME | TOTAL | IDLE |
+-----------+-------+------+
| datanode1 |   101 |  100 |
| datanode2 |   101 |  100 |
| datanode3 |   101 |  100 |
| datanode4 |   101 |  100 |
| datanode5 |   101 |  100 |
| datanode6 |   101 |  100 |
| datanode7 |   101 |  100 |
| datanode8 |   101 |  100 |
| datanode9 |   101 |  100 |
+-----------+-------+------+
9 rows in set (0.00 sec)
```

## GreatDB 日誌任務巡檢檢視
![在這裡插入圖片描述](https://img-blog.csdnimg.cn/6f98d834c9ba42cab074697b76a12804.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5LiH6YeM5pWw5o2u5bqT,size_17,color_FFFFFF,t_70,g_se,x_16)

```powershell

GreatDB Cluster[(none)]> select * from information_schema.GREATDB_MIGRATE_TABLE_TASK;
+---------+---------------+-------------------------------------+----------------+----------------+------------------+-----------------------+---------------------+-------------+
| TASK_ID | OBJECT_NAME   | INFO                                | MIGRATE_STATUS | STAGE_SEQUENCE | STAGE            | COMPLETED_SUBTASK_NUM | UNKNOWN_SUBTASK_NUM | SUBTASK_NUM |
+---------+---------------+-------------------------------------+----------------+----------------+------------------+-----------------------+---------------------+-------------+
|      43 | test.t_normal | test.t_normal from shard2 to shard1 | COMPLETE       |              1 | CREATE TABLE     |                     1 |                   0 |           1 |
|      43 | test.t_normal | test.t_normal from shard2 to shard1 | COMPLETE       |              2 | COPY REMOTE DATA |                     1 |                   0 |           1 |
|      43 | test.t_normal | test.t_normal from shard2 to shard1 | COMPLETE       |              3 | APPLY_REMOTE_LOG |                     1 |                   0 |           1 |
|      43 | test.t_normal | test.t_normal from shard2 to shard1 | COMPLETE       |              4 | CATCH_REMOTE_LOG |                     1 |                   0 |           1 |
|      43 | test.t_normal | test.t_normal from shard2 to shard1 | COMPLETE       |              5 | CHANGE_TOPOLOGY  |                     1 |                   0 |           1 |
+---------+---------------+-------------------------------------+----------------+----------------+------------------+-----------------------+---------------------+-------------+
5 rows in set (0.00 sec)

萬里GreatDB資料庫的學習之路--GreatDB引數調整與系統檢視（3）

相關文章