Apache ShardingSphere 5.0.0-Beta model with DistSQL made the venture much more beloved by builders and ops groups for its benefits, resembling dynamic results, no restart, and stylish syntax shut to plain SQL. With upgrades to five.0.0 and 5.1.0, the ShardingSphere neighborhood has as soon as once more added considerable syntax to DistSQL, bringing extra sensible options.
In this text, the neighborhood co-authors will share the newest features of DistSQL from the angle of cluster governance.
ShardingSphere clusters
In a typical cluster composed of ShardingSphere-Proxy, there are a number of compute nodes and storage nodes, as proven within the determine under.
(Jiang Longtao and Lan Chengxiang, CC BY-SA 4.0)
To make it simpler to grasp, in ShardingSphere, we seek advice from proxy as a compute node and proxy-managed distributed database assets (resembling ds_0 or ds_1) as assets or storage nodes.
Multiple proxy or compute nodes are linked to the identical register heart. They share configuration and guidelines, they usually can sense one another’s on-line standing. These compute nodes additionally share the underlying storage nodes, to allow them to carry out learn and write operations to the storage nodes on the identical time. The consumer utility is linked to any compute node and may carry out equal operations.
Through this cluster structure, you may rapidly scale proxy horizontally when compute assets are inadequate, decreasing the danger of a single level of failure and enhancing system availability. The load-balancing mechanism will also be added between the appliance and compute node.
Compute node governance
Compute node governance is appropriate for cluster mode. For extra details about the ShardingSphere modes, please see Your detailed guide to Apache ShardingSphere’s operating modes.
Cluster preparation
Take a standalone simulation of three proxy compute nodes for example. To use the mode, observe the configuration under:
mode:
sort: Cluster
repository:
sort: ZooKeeper
props:
namespace: governance_ds
server-lists: localhost:2181
retryIntervalMilliseconds: 500
timeToLiveSeconds: 60
maxRetries: 3
operationTimeoutMilliseconds: 500
overwrite: false
Execute the bootup command individually:
sh %SHARDINGSPHERE_PROXY_HOME%/bin/begin.sh 3307
sh %SHARDINGSPHERE_PROXY_HOME%/bin/begin.sh 3308
sh %SHARDINGSPHERE_PROXY_HOME%/bin/begin.sh 3309
After the three proxy cases are efficiently began, the compute node cluster is prepared.
SHOW INSTANCE LIST
Use the shopper to connect with any compute node, resembling 3307:
mysql -h 127.0.0.1 -P 3307 -u root -p
View the checklist of cases utilizing SHOW INSTANCE LIST:
mysql> SHOW INSTANCE LIST;
+----------------+-----------+------+---------+
| instance_id | host | port | STATUS |
+----------------+-----------+------+---------+
| 10.7.5.35@3309 | 10.7.5.35 | 3309 | enabled |
| 10.7.5.35@3308 | 10.7.5.35 | 3308 | enabled |
| 10.7.5.35@3307 | 10.7.5.35 | 3307 | enabled |
+----------------+-----------+------+---------+
The above fields imply:
instance_id: The id of the occasion, which is presently composed of host and porthost: Host deal withport: Port quantitystanding: The standing of the occasion, both enabled or disabled
DISABLE INSTANCE
Use a DISABLE INSTANCE assertion to set the desired compute node to a disabled state. The assertion doesn’t terminate the method of the goal occasion however solely nearly deactivates it.
DISABLE INSTANCE helps the next syntax types:
DISABLE INSTANCE 10.7.5.35@3308;
#or
DISABLE INSTANCE IP=10.7.5.35, PORT=3308;
For instance:
mysql> DISABLE INSTANCE 10.7.5.35@3308;
Query OK, 0 ROWS affected (0.02 sec)
mysql> SHOW INSTANCE LIST;
+----------------+-----------+------+----------+
| instance_id | host | port | STATUS |
+----------------+-----------+------+----------+
| 10.7.5.35@3309 | 10.7.5.35 | 3309 | enabled |
| 10.7.5.35@3308 | 10.7.5.35 | 3308 | disabled |
| 10.7.5.35@3307 | 10.7.5.35 | 3307 | enabled |
+----------------+-----------+------+----------+
After executing the DISABLE INSTANCE assertion by querying once more, you may see that the occasion standing of Port 3308 has been up to date to disabled, indicating that the compute node has been disabled.
If there’s a shopper linked to 10.7.5.35@3308, executing any SQL assertion will immediate an exception:
1000 - Circuit break mode IS ON.
You aren’t allowed to disable the present compute node. If you ship 10.7.5.35@3309 to DISABLE INSTANCE 10.7.5.35@3309, you’ll obtain an exception immediate.
ENABLE INSTANCE
Use an ENABLE INSTANCE assertion to set the desired compute node to an enabled state. ENABLE INSTANCE helps the next syntax types:
ENABLE INSTANCE 10.7.5.35@3308;
#or
ENABLE INSTANCE IP=10.7.5.35, PORT=3308;
For instance:
mysql> SHOW INSTANCE LIST;
+----------------+-----------+------+----------+
| instance_id | host | port | STATUS |
+----------------+-----------+------+----------+
| 10.7.5.35@3309 | 10.7.5.35 | 3309 | enabled |
| 10.7.5.35@3308 | 10.7.5.35 | 3308 | disabled |
| 10.7.5.35@3307 | 10.7.5.35 | 3307 | enabled |
+----------------+-----------+------+----------+
mysql> ENABLE INSTANCE 10.7.5.35@3308;
Query OK, 0 ROWS affected (0.01 sec)
mysql> SHOW INSTANCE LIST;
+----------------+-----------+------+----------+
| instance_id | host | port | STATUS |
+----------------+-----------+------+----------+
| 10.7.5.35@3309 | 10.7.5.35 | 3309 | enabled |
| 10.7.5.35@3308 | 10.7.5.35 | 3308 | enabled |
| 10.7.5.35@3307 | 10.7.5.35 | 3307 | enabled |
+----------------+-----------+------+----------+
After executing the ENABLE INSTANCE assertion, you may question once more and examine that the occasion state of Port 3308 has been restored to enabled.
How to handle compute node parameters
In our article Integrating SCTL into DISTSQL’s RAL: Making Apache ShardingSphere perfect for database management, we defined the evolution of ShardingSphere management language (SCTL) to useful resource and rule administration language (RAL) and the brand new SHOW VARIABLE and SET VARIABLE syntax.
However, in 5.0.0-Beta, the VARIABLE class of DistSQL RAL solely contained solely the next three statements:
SET VARIABLE TRANSACTION_TYPE = xx; （LOCAL, XA, BASE）
SHOW VARIABLE TRANSACTION_TYPE;
SHOW VARIABLE CACHED_CONNECTIONS;
By listening to the neighborhood’s suggestions, we observed that querying and modifying the props configuration of proxy (positioned in server.yaml) can be a frequent operation. Therefore, we’ve added assist for props configuration in DistSQL RAL for the reason that 5.0.0 GA model.
SHOW VARIABLE
First, we’ll overview how one can configure props:
props:
max-connections-size-per-query: 1kernel-executor-size: 16 # Infinite by default.
proxy-frontend-flush-threshold: 128 # The default worth is 128.
proxy-opentracing-enabled: false
proxy-hint-enabled: false
sql-show: false
check-table-metadata-enabled: false
show-process-list-enabled: false
# Proxy backend question fetch dimension. A bigger worth could enhance the reminiscence utilization of ShardingSphere Proxy.
# The default worth is -1, which suggests set the minimal worth for various JDBC drivers.
proxy-backend-query-fetch-size: -1
check-duplicate-table-enabled: false
proxy-frontend-executor-size: 0 # Proxy frontend executor dimension. The default worth is 0, which suggests let Netty determine.
# Available choices of proxy backend executor appropriate: OLAP(default), OLTP. The OLTP possibility could cut back time price of writing packets to shopper, however it might enhance the latency of SQL execution
# and block different shoppers if shopper connections are greater than `proxy-frontend-executor-size`, particularly executing gradual SQL.
proxy-backend-executor-suitable: OLAP
proxy-frontend-max-connections: 0 # Less than or equal to 0 means no limitation.
sql-federation-enabled: false
# Available proxy backend driver sort: JDBC (default), ExperimentalVertx
proxy-backend-driver-type: JDBC
Now, you may carry out interactive queries through the use of the next syntax:
SHOW VARIABLE PROXY_PROPERTY_NAME;
For instance:
mysql> SHOW VARIABLE MAX_CONNECTIONS_SIZE_PER_QUERY;
+--------------------------------+
| max_connections_size_per_query |
+--------------------------------+
| 1 |
+--------------------------------+
1 ROW IN SET (0.00 sec)
mysql> SHOW VARIABLE SQL_SHOW;
+----------+
| sql_show |
+----------+
| FALSE |
+----------+
1 ROW IN SET (0.00 sec)
……
Note: For DistSQL syntax, parameter keys are separated by underscores.
SHOW ALL VARIABLES
Since there are many parameters in proxy, you can even question all parameter values by way of SHOW ALL VARIABLES:
mysql> SHOW ALL VARIABLES;
+---------------------------------------+----------------+
| variable_name | variable_value |
+---------------------------------------+----------------+
| sql_show | FALSE |
| sql_simple | FALSE |
| kernel_executor_size | 0 |
| max_connections_size_per_query | 1 |
| check_table_metadata_enabled | FALSE |
| proxy_frontend_database_protocol_type | |
| proxy_frontend_flush_threshold | 128 |
| proxy_opentracing_enabled | FALSE |
| proxy_hint_enabled | FALSE |
| show_process_list_enabled | FALSE |
| lock_wait_timeout_milliseconds | 50000 |
| proxy_backend_query_fetch_size | -1 |
| check_duplicate_table_enabled | FALSE |
| proxy_frontend_executor_size | 0 |
| proxy_backend_executor_suitable | OLAP |
| proxy_frontend_max_connections | 0 |
| sql_federation_enabled | FALSE |
| proxy_backend_driver_type | JDBC |
| agent_plugins_enabled | FALSE |
| cached_connections | 0 |
| transaction_type | LOCAL |
+---------------------------------------+----------------+
21 ROWS IN SET (0.01 sec)
SET VARIABLE
Dynamic administration of assets and guidelines is a particular benefit of DistSQL. Now you can even dynamically replace props parameters through the use of the SET VARIABLE assertion. For instance:
#Enable SQL log output
SET VARIABLE SQL_SHOW = true;
#Turn on trace operate
SET VARIABLE PROXY_HINT_ENABLED = true;
#Open federal question
SET VARIABLE SQL_FEDERATION_ENABLED = true;
……
The SET VARIABLE assertion can modify the next parameters, however the brand new worth takes impact solely after the proxy restart:
kernel_executor_sizeproxy_frontend_executor_sizeproxy_backend_driver_type
The following parameters are read-only and can’t be modified:
Other parameters will take impact instantly after modification.
How to handle storage nodes
In ShardingSphere, storage nodes aren’t instantly certain to compute nodes. One storage node could play totally different roles in several schemas on the identical time, with a view to implement totally different enterprise logic. Storage nodes are all the time related to a schema.
For DistSQL, storage nodes are managed by way of RESOURCE-related statements, together with:
ADD RESOURCEALTER RESOURCEDROP RESOURCESHOW SCHEMA RESOURCES
Schema preparation
RESOURCE-related statements solely work on schemas, so earlier than working, it is advisable create and use the USE command to efficiently choose a schema:
DROP DATABASE IF EXISTS sharding_db;
CREATE DATABASE sharding_db;
USE sharding_db;
ADD RESOURCE
ADD RESOURCE helps the next syntax types:
ADD RESOURCE resource_0 (
HOST=127.0.0.1,
PORT=3306,
DB=db0,
USER=root,
PASSWORD=root
);
ADD RESOURCE resource_1 (
URL="jdbc:mysql://127.0.0.1:3306/db1?serverTimezone=UTC&useSSL=false",
USER=root,
PASSWORD=root
);
The above two syntax types assist the extension parameter PROPERTIES, which is used to specify the attribute configuration of the connection pool between the proxy and the storage node.
For instance:
ADD RESOURCE resource_2 (
HOST=127.0.0.1,
PORT=3306,
DB=db2,
USER=root,
PASSWORD=root,
PROPERTIES("maximumPoolSize"=10)
),resource_3 (
URL="jdbc:mysql://127.0.0.1:3306/db3?serverTimezone=UTC&useSSL=false",
USER=root,
PASSWORD=root,
PROPERTIES("maximumPoolSize"=10,"idleTimeout"="30000")
);
Specifying Java Database Connectivity (JDBC) connection parameters, resembling useSSL, is supported solely with URL kind.
ALTER RESOURCE
Use ALTER RESOURCE to change the connection data of storage nodes, resembling altering the dimensions of a connection pool or modifying JDBC connection parameters.
Syntactically, ALTER RESOURCE is equivalent to ADD RESOURCE.
ALTER RESOURCE resource_2 (
HOST=127.0.0.1,
PORT=3306,
DB=db2,
USER=root,
PROPERTIES("maximumPoolSize"=50)
),resource_3 (
URL="jdbc:mysql://127.0.0.1:3306/db3?serverTimezone=GMT&useSSL=false",
USER=root,
PASSWORD=root,
PROPERTIES("maximumPoolSize"=50,"idleTimeout"="30000")
);
Since modifying the storage node could trigger metadata modifications or utility information exceptions, ALTER RESOURCE can’t be used to change the goal database of the connection. Only the next values may be modified:
- User identify
- User password
PROPERTIESconnection pool parameters- JDBC parameters
DROP RESOURCE
Use DROP RESOURCE to delete storage nodes from a schema with out deleting any information within the storage node. The assertion instance is as follows:
DROP RESOURCE resource_0, resource_1;
To guarantee information correctness, the storage node referenced by the rule can’t be deleted.
t_order is a sharding desk, and its precise tables are distributed in resource_0and resource_1. When resource_0 and resource_1 are referenced by t_order sharding guidelines, they can’t be deleted.
SHOW SCHEMA RESOURCES
SHOW SCHEMA RESOURCES is used to question storage nodes in schemas and helps the next syntax types:
#Query the storage node within the present schema
SHOW SCHEMA RESOURCES;
#Query the storage node within the specified schema
SHOW SCHEMA RESOURCES FROM sharding_db;
For instance, add 4 storage nodes by way of the ADD RESOURCE command, after which execute a question:
(Jiang Longtao and Lan Chengxiang, CC BY-SA 4.0)
There are many columns within the question end result, however right here we solely present a part of them.
Conclusion
In this text, we’ve launched you to the methods you may dynamically handle storage nodes by way of DistSQL.
Unlike modifying YAML information, executing DistSQL statements occurs in actual time, and there’s no must restart the proxy or compute node, making on-line operations safer. Changes executed by way of DistSQL may be synchronized to different compute nodes within the cluster in actual time by way of the register heart. The shopper linked to any compute node may also question modifications of storage nodes in actual time.
If you have got any questions or ideas about Apache ShardingSphere, please open a difficulty on the GitHub issue list. If you have an interest in contributing to the venture, you are very welcome to hitch the Apache ShardingSphere neighborhood.
Apache ShardingSphere Project Links:
This article initially appeared on FAUN and is republished with permission.
