You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2021/05/20 06:32:21 UTC
[GitHub] [shardingsphere] tristaZero opened a new issue #9568: [New Feature] Support to show execution process for running SQLs by RQL
tristaZero opened a new issue #9568:
URL: https://github.com/apache/shardingsphere/issues/9568
Hi, community,
This is a new plan to support showing the execution process for running SQLs, especially for DML/DDL always needing much time to run. With my thinking, there are three main tasks for this new feature.
The basic process is **Job ID generation, job status report to governance, get current job status by SQL from governance**.
### Tasks
#### Generate Job IDs
- [x] Generate UUID for this job's trunk and its branches
- [x] Pass these IDs through the whole execution
#### Report execution process from one Proxy
- [ ] `ExecutorEngine` reports the context (IDs, beginTime, etc.) of each query to `governance`
- [x] Each branch job reports its status and ID to `governance` once it finishes in `ExecutorCallback`
- [ ] `Governance` handles these requests synchronously
#### Query execution process from other Proxies
- [ ] It is triggered by `show processlist`
- [ ] Get the execution status from all the jobs from `governance`
- [ ] Make up the result based on `show processlist`' original result with the collected data
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero edited a comment on issue #9568: [New Feature] Support to show execution process for running SQLs by RQL
Posted by GitBox <gi...@apache.org>.
tristaZero edited a comment on issue #9568:
URL: https://github.com/apache/shardingsphere/issues/9568#issuecomment-800995595
## The details about the remaining work
### Report execution process from one Proxy
When one Proxy receives the SQL queries from users, it will evaluate whether to report this query execution status to governance in `ExecuteProcessEngine`. You can find where this class is called to track its flow path.
Currently, the public functions of `DriverJDBCExecutor` has basically finished the relevant functions, but we still need to finish
- `ExecuteProcessStrategyEvaluator`
- `GovernanceExecuteProcessReporter`
- `RawExecutor` (To initialize the whole job, please refer to `DriverJDBCExecutor`)
- `RawSQLExecutorCallback` (To finish a unit job, referring to `JDBCExecutorCallback`)
- `CalciteRowExecutor`
- Store `ExecuteProcessContext` to `governance` in `RegistryCenter` [1]
### Query execution process from other Proxies
When users execute `show processlist` from another Proxy. This Proxy needs to go into `ShowProcesslistExecutor` to get `ExecuteProcessContext` and return to the user by DB binary protocol. The following target classes are for this part,
- Add judgement in `MySQLAdminExecutorFactory`
- New `ShowProcesslistExecutor` to prepare required data from `governance`.
### Unit tests
Please add necessary UTs for your implements.
[1] The structure of the [registry center](https://shardingsphere.apache.org/document/current/cn/features/governance/management/registry-center/) is suggested to be
```
namespace
├──states
├ ├──proxynodes
├ ├ ├──${your_instance_ip_a}@${your_instance_pid_x}@${UUID}
├ ├ ├──${your_instance_ip_b}@${your_instance_pid_y}@${UUID}
├ ├ ├──....
├ ├──datanodes
├ ├ ├──${schema_1}
├ ├ ├ ├──${ds_0}
├ ├ ├ ├──${ds_1}
├ ├ ├──${schema_2}
├ ├ ├ ├──${ds_0}
├ ├ ├ ├──${ds_1}
├ ├ ├──...
├ ├──executionnodes
├ ├ ├──${execution_id}
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] taojintianxia commented on issue #9568: [New Feature] Support to show execution process for running SQLs by RQL
Posted by GitBox <gi...@apache.org>.
taojintianxia commented on issue #9568:
URL: https://github.com/apache/shardingsphere/issues/9568#issuecomment-844999160
hi , there
following is the process I try to test the RQL
1. I launched up 3 containers through docker compose, MySQL-A, MySQL-B, zookeeper
2. switch the option `return context.getSqlStatement() instanceof DDLStatement;` in ExecuteProcessStrategyEvaluator.java
3. compiled the shardingsphere project and unzip the proxy districution
4. edit the config-sharding.yaml and luanch up proxy
5. insert 2 million records by sysbench prepare
```shell
sysbench oltp_read_only --mysql-host=127.0.0.1 --mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest --tables=10 --table-size=2000000 --report-interval=30 --time=180 --threads=16 --max-requests=0 --percentile=99 --mysql-ignore-errors="all" --range_selects=off --rand-type=uniform --auto_inc=off prepare
```
this is the structure of test table
6.try to delete one collumn by alter table clause
```sql
alter table sbtest1 drop c;
```
7. since there are a lot of record in that table, this takes time....
in the meanwhile
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] sandynz commented on issue #9568: [New Feature] Support to show execution process for running SQLs by RQL
Posted by GitBox <gi...@apache.org>.
sandynz commented on issue #9568:
URL: https://github.com/apache/shardingsphere/issues/9568#issuecomment-874380832
After some discussion with @tristaZero , it's necessary to optimize `User` and `Host` column in processlist.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero closed issue #9568: [New Feature] Support to show execution process for running SQLs by RQL
Posted by GitBox <gi...@apache.org>.
tristaZero closed issue #9568:
URL: https://github.com/apache/shardingsphere/issues/9568
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] sandynz commented on issue #9568: [New Feature] Support to show execution process for running SQLs by RQL
Posted by GitBox <gi...@apache.org>.
sandynz commented on issue #9568:
URL: https://github.com/apache/shardingsphere/issues/9568#issuecomment-845160259
>
>
> @sandynz Let us wait for the FVT.
That's ok.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] taojintianxia edited a comment on issue #9568: [New Feature] Support to show execution process for running SQLs by RQL
Posted by GitBox <gi...@apache.org>.
taojintianxia edited a comment on issue #9568:
URL: https://github.com/apache/shardingsphere/issues/9568#issuecomment-857651087
hi , there
today I tried several times to test this function. followings are the precodiction :
1. raise up 5 mysql instance and insert 1 million data
2. luanch up a "brand new" zk
3. established a proxy with sharding rule
when proxy is start up, I execute a shell like following :
```
#!/bin/bash
export MYSQL_PWD=root
for (( i = 0; i < 10000000; i=(i+step) )); do
mysql -h127.0.0.1 -P3307 -uroot -Dsharding_db -e "show processlist;" | tee -a result.txt | echo "\n" | tee -a result.txt
done
exit 0
```
this shell will print out the `show processlist;` in a very shot interval . Unfortunately, it always print 0/5 as the status.
I just cut part of the result :
```
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
```
BTW, it works if I set the suspend point in idea :), I saw the process like `Executing 1/5` .... but not in this way.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #9568: [New Feature] Support to show execution process for running SQLs by RQL
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #9568:
URL: https://github.com/apache/shardingsphere/issues/9568#issuecomment-874407827
@sandynz Look forward to your next step. ;-)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] sandynz commented on issue #9568: [New Feature] Support to show execution process for running SQLs by RQL
Posted by GitBox <gi...@apache.org>.
sandynz commented on issue #9568:
URL: https://github.com/apache/shardingsphere/issues/9568#issuecomment-861496174
>
>
> So do you think we can carry on with the remaining tasks? Maybe show the correct user, host, info of its result. Or Add more SQLStatement branches in `ExecuteProcessStrategyEvaluator`?
That's ok.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] sandynz commented on issue #9568: [New Feature] Support to show execution process for running SQLs by RQL
Posted by GitBox <gi...@apache.org>.
sandynz commented on issue #9568:
URL: https://github.com/apache/shardingsphere/issues/9568#issuecomment-876835489
Hi @tristaZero , I'm working on `User` and `Host` column optimization, it need to transfer `grantee` (including `username` and `hostname`) to underlying and then persist to governance, `grantee` just exists in proxy. I considered 3 ways to transfer:
1. Put `Grantee` and `LogicSQL` in `ExecutionGroupContext`. Easier to extend later, but not sure whether they are related or not.
2. Put `Grantee` and `LogicSQL` in another new context class. Easier to extend later, use a dedicated transferring context.
3. Put `Grantee` in method arguments list. `grantee` is not the core information for executor, not clean enough, after addition, it looks like: `jdbcExecutor.execute(grantee, logicSQL, executionGroupContext, isReturnGeneratedKeys, isExceptionThrown)`.
I prefer way 1 or 2, but not sure which one is better. What do you think?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #9568: [New Feature] Support to show execution process for running SQLs by RQL
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #9568:
URL: https://github.com/apache/shardingsphere/issues/9568#issuecomment-874407827
@sandynz Look forward to your next step. ;-)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #9568: [New Feature] Support to show execution process for running SQLs by RQL
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #9568:
URL: https://github.com/apache/shardingsphere/issues/9568#issuecomment-800995595
## The details about the remaining work
### Report execution process from one Proxy
When one Proxy receives the SQL queries from users, it will evaluate whether to report this query execution status to governance in `ExecuteProcessEngine`. You can find where this class is called to track its flow path.
Currently, the public functions of `DriverJDBCExecutor` has basically finished the relevant functions, but we still need to finish
- `ExecuteProcessStrategyEvaluator`
- `GovernanceExecuteProcessReporter`
- `RawExecutor` (To initialize the whole job, please refer to `DriverJDBCExecutor`)
- `RawSQLExecutorCallback` (To finish a unit job, referring to `JDBCExecutorCallback`)
- `CalciteRowExecutor`
- Store `ExecuteProcessContext` to `governance` in `RegistryCenter` [1]
### Query execution process from other Proxies
When users execute `show processlist` from another Proxy. This Proxy needs to go into `ShowProcesslistExecutor` to get `ExecuteProcessContext` and return to the user by DB binary protocol. The following target classes are for this part,
- Add judgement in `MySQLAdminExecutorFactory`
- New `ShowProcesslistExecutor` to prepare required data from `governance`.
### Unit tests
Please add necessary UTs for your implements.
[1] The structure of the `registry center` is suggested to be
```
namespace
├──states
├ ├──proxynodes
├ ├ ├──${your_instance_ip_a}@${your_instance_pid_x}@${UUID}
├ ├ ├──${your_instance_ip_b}@${your_instance_pid_y}@${UUID}
├ ├ ├──....
├ ├──datanodes
├ ├ ├──${schema_1}
├ ├ ├ ├──${ds_0}
├ ├ ├ ├──${ds_1}
├ ├ ├──${schema_2}
├ ├ ├ ├──${ds_0}
├ ├ ├ ├──${ds_1}
├ ├ ├──...
├ ├──authenticationnodes
├ ├ ├──${execution_id}
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] sandynz commented on issue #9568: [New Feature] Support to show execution process for running SQLs by RQL
Posted by GitBox <gi...@apache.org>.
sandynz commented on issue #9568:
URL: https://github.com/apache/shardingsphere/issues/9568#issuecomment-874380832
After some discussion with @tristaZero , it's necessary to optimize `User` and `Host` column in processlist.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] taojintianxia edited a comment on issue #9568: [New Feature] Support to show execution process for running SQLs by RQL
Posted by GitBox <gi...@apache.org>.
taojintianxia edited a comment on issue #9568:
URL: https://github.com/apache/shardingsphere/issues/9568#issuecomment-844999160
hi , there
following is the process I try to test the RQL
1. I launched up 3 containers through docker compose, MySQL-A, MySQL-B, zookeeper
2. switch the option `return context.getSqlStatement() instanceof DDLStatement;` in ExecuteProcessStrategyEvaluator.java
3. compiled the shardingsphere project and unzip the proxy districution
4. edit the config-sharding.yaml and luanch up proxy
5. insert 2 million records by sysbench prepare
```shell
sysbench oltp_read_only --mysql-host=127.0.0.1 --mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest --tables=10 --table-size=2000000 --report-interval=30 --time=180 --threads=16 --max-requests=0 --percentile=99 --mysql-ignore-errors="all" --range_selects=off --rand-type=uniform --auto_inc=off prepare
```
this is the structure of test table
6.try to delete one collumn by alter table clause
```sql
alter table sbtest1 drop c;
```
7. since there are a lot of record in that table, this takes time....
in the meanwhile .....
open another console to proxy and execute following command :
```shell
show processlist;
```
we could see that the `Time` is increasing, and when the alter table finished , the `show processlist` returns empty
and when the alter table executing, in zookeeper, we could see the `executionnode`
when alter table finished , there is no such node in zookeeper.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] taojintianxia edited a comment on issue #9568: [New Feature] Support to show execution process for running SQLs by RQL
Posted by GitBox <gi...@apache.org>.
taojintianxia edited a comment on issue #9568:
URL: https://github.com/apache/shardingsphere/issues/9568#issuecomment-844999160
hi , there
following is the process I try to test the RQL
1. I launched up 3 containers through docker compose, MySQL-A, MySQL-B, zookeeper
2. switch the option `return context.getSqlStatement() instanceof DDLStatement;` in ExecuteProcessStrategyEvaluator.java
3. compiled the shardingsphere project and unzip the proxy distribution
4. edit the config-sharding.yaml and luanch up proxy
5. insert 2 million records by sysbench prepare
```shell
sysbench oltp_read_only --mysql-host=127.0.0.1 --mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest --tables=10 --table-size=2000000 --report-interval=30 --time=180 --threads=16 --max-requests=0 --percentile=99 --mysql-ignore-errors="all" --range_selects=off --rand-type=uniform --auto_inc=off prepare
```
this is the structure of test table
6.try to delete one collumn by alter table clause
```sql
alter table sbtest1 drop c;
```
7. since there are a lot of record in that table, this takes time....
in the meanwhile .....
open another console to proxy and execute following command :
```shell
show processlist;
```
we could see that the `Time` is increasing, and when the alter table finished , the `show processlist` returns empty
and when the alter table executing, in zookeeper, we could see the `executionnode`
when alter table finished , there is no such node in zookeeper.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] sandynz commented on issue #9568: [New Feature] Support to show execution process for running SQLs by RQL
Posted by GitBox <gi...@apache.org>.
sandynz commented on issue #9568:
URL: https://github.com/apache/shardingsphere/issues/9568#issuecomment-801029150
Hi, I'd like to have a try.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] taojintianxia edited a comment on issue #9568: [New Feature] Support to show execution process for running SQLs by RQL
Posted by GitBox <gi...@apache.org>.
taojintianxia edited a comment on issue #9568:
URL: https://github.com/apache/shardingsphere/issues/9568#issuecomment-855846139
Preconditions :
- luanch up 2 mysql instance by docker
- luanch up zk by docker
- compiled by up to date shardingsphere and luanch up the proxy distribution
- data shard by database rule ds_{ user_id % 2}
- insert 1.5m records
execute a sql last a little bit more time `alter table t_order drop status;` , it last about (6.07 sec)
at the same time, open up a new window to connect to proxy and execute `show processlist;`
after serveral times of executing `show processlist;`, the alter table sql finished , `show processlist` returns empty set.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] sandynz commented on issue #9568: [New Feature] Support to show execution process for running SQLs by RQL
Posted by GitBox <gi...@apache.org>.
sandynz commented on issue #9568:
URL: https://github.com/apache/shardingsphere/issues/9568#issuecomment-814615626
Some discussion notes with @tristaZero
- Execution progress could be put in `show processlist` `State` column, keep the same columns as MySQL.
- Add a new `finish` method in `ExecuteProcessEngine` and `ExecuteProcessReporter` to clean up execution node in ZK. If there's execution failure or timeout, just keep it in ZK for now, in order to convinience of debugging.
- Clean up `EXECUTE_ID` thread local variable in `ExecutorDataMap` after execution.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero closed issue #9568: [New Feature] Support to show execution process for running SQLs by RQL
Posted by GitBox <gi...@apache.org>.
tristaZero closed issue #9568:
URL: https://github.com/apache/shardingsphere/issues/9568
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #9568: [New Feature] Support to show execution process for running SQLs by RQL
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #9568:
URL: https://github.com/apache/shardingsphere/issues/9568#issuecomment-849443432
@sandynz Hi
This is feature is greatly included in our next release, but as you know it is a beta feature. So do you think it is possible to add a configured item to `server.yaml` of Proxy to control it works or not, like `check-table-metadata-enabled`
I assume it is not effortful and our next release is expected to make during the next week. I have no idea whether this `switch` is possible to catch up or not. What do you think?
FYI,
```
// ExecuteProcessStrategyEvaluator.java
return xxxxxx && context.getSqlStatement() instanceof DDLStatement;
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero edited a comment on issue #9568: [New Feature] Support to show execution process for running SQLs by RQL
Posted by GitBox <gi...@apache.org>.
tristaZero edited a comment on issue #9568:
URL: https://github.com/apache/shardingsphere/issues/9568#issuecomment-800995595
## The details about the remaining work
### Report execution process from one Proxy
When one Proxy receives the SQL queries from users, it will evaluate whether to report this query execution status to governance in `ExecuteProcessEngine`. You can find where this class is called to track its flow path.
Currently, the public functions of `DriverJDBCExecutor` has basically finished the relevant functions, but we still need to finish
- `ExecuteProcessStrategyEvaluator`
- `GovernanceExecuteProcessReporter`
- `RawExecutor` (To initialize the whole job, please refer to `DriverJDBCExecutor`)
- `RawSQLExecutorCallback` (To finish a unit job, referring to `JDBCExecutorCallback`)
- `CalciteRowExecutor`
- Store `ExecuteProcessContext` to `governance` in `RegistryCenter` [1]
### Query execution process from other Proxies
When users execute `show processlist` from another Proxy. This Proxy needs to go into `ShowProcesslistExecutor` to get `ExecuteProcessContext` and return to the user by DB binary protocol. The following target classes are for this part,
- Add judgement in `MySQLAdminExecutorFactory`
- New `ShowProcesslistExecutor` to prepare required data from `governance`.
### Unit tests
Please add necessary UTs for your implements.
[1] The structure of the [registry center](https://shardingsphere.apache.org/document/current/cn/features/governance/management/registry-center/) is suggested to be
```
namespace
├──states
├ ├──proxynodes
├ ├ ├──${your_instance_ip_a}@${your_instance_pid_x}@${UUID}
├ ├ ├──${your_instance_ip_b}@${your_instance_pid_y}@${UUID}
├ ├ ├──....
├ ├──datanodes
├ ├ ├──${schema_1}
├ ├ ├ ├──${ds_0}
├ ├ ├ ├──${ds_1}
├ ├ ├──${schema_2}
├ ├ ├ ├──${ds_0}
├ ├ ├ ├──${ds_1}
├ ├ ├──...
├ ├──authenticationnodes
├ ├ ├──${execution_id}
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #9568: [New Feature] Support to show execution process for running SQLs by RQL
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #9568:
URL: https://github.com/apache/shardingsphere/issues/9568#issuecomment-864517219
@sandynz If you have any plan or schedule on this issue, please comment here to let the community know what is going on.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] taojintianxia commented on issue #9568: [New Feature] Support to show execution process for running SQLs by RQL
Posted by GitBox <gi...@apache.org>.
taojintianxia commented on issue #9568:
URL: https://github.com/apache/shardingsphere/issues/9568#issuecomment-855846139
luanch up 2 mysql instance by dockers.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #9568: [New Feature] Support to show execution process for running SQLs by RQL
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #9568:
URL: https://github.com/apache/shardingsphere/issues/9568#issuecomment-861467842
Cool, @sandynz
So do you think we can carry on with the remaining tasks? Maybe show the correct user, host, info of its result. Or Add more SQLStatement branches in `ExecuteProcessStrategyEvaluator`?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] taojintianxia edited a comment on issue #9568: [New Feature] Support to show execution process for running SQLs by RQL
Posted by GitBox <gi...@apache.org>.
taojintianxia edited a comment on issue #9568:
URL: https://github.com/apache/shardingsphere/issues/9568#issuecomment-855846139
Preconditions :
- luanch up 2 mysql instance by docker
- luanch up zk by docker
- compiled by up to date shardingsphere and luanch up the proxy distribution
- data shard by database rule ds_{ user_id % 2}
- insert 1.5m records
execute a sql last a little bit more time `alter table t_order drop status;` , it last about (6.07 sec)
at the same time, open up a new window to connect to proxy and execute `show processlist;`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #9568: [New Feature] Support to show execution process for running SQLs by RQL
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #9568:
URL: https://github.com/apache/shardingsphere/issues/9568#issuecomment-844806009
@sandynz Let us wait for the FVT.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] taojintianxia edited a comment on issue #9568: [New Feature] Support to show execution process for running SQLs by RQL
Posted by GitBox <gi...@apache.org>.
taojintianxia edited a comment on issue #9568:
URL: https://github.com/apache/shardingsphere/issues/9568#issuecomment-844999160
hi , there
following is the process I try to test the RQL
1. I launched up 3 containers through docker compose, MySQL-A, MySQL-B, zookeeper
2. switch the option `return context.getSqlStatement() instanceof DDLStatement;` in ExecuteProcessStrategyEvaluator.java
3. compiled the shardingsphere project and unzip the proxy districution
4. edit the config-sharding.yaml and luanch up proxy
5. insert 2 million records by sysbench prepare
```shell
sysbench oltp_read_only --mysql-host=127.0.0.1 --mysql-port=3307 --mysql-user=root --mysql-password='root' --mysql-db=sbtest --tables=10 --table-size=2000000 --report-interval=30 --time=180 --threads=16 --max-requests=0 --percentile=99 --mysql-ignore-errors="all" --range_selects=off --rand-type=uniform --auto_inc=off prepare
```
this is the structure of test table
6.try to delete one collumn by alter table clause
```sql
alter table sbtest1 drop c;
```
7. since there are a lot of record in that table, this takes time....
in the meanwhile .....
open another console to proxy and execute following command :
```shell
show processlist;
```
this is the process.
we could see that the `Time` is increasing, and when the alter table finished , the `show processlist` returns empty
and when the alter table executing, in zookeeper, we could see the `executionnode`
when alter table finished , there is no such node in zookeeper.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] sandynz commented on issue #9568: [New Feature] Support to show execution process for running SQLs by RQL
Posted by GitBox <gi...@apache.org>.
sandynz commented on issue #9568:
URL: https://github.com/apache/shardingsphere/issues/9568#issuecomment-861083398
Hi, I've tested it without breakpoint.
Steps:
1, Set `show-process-list-enabled: true` in `server.yaml` props; enable governance;
2, `actualDataNodes: ds_${0..1}.t_order_${0..1}` in `config-sharding.yaml`
3, Start ZooKeeper and clean cache; Start proxy and insert 36 millions records (every shard 9 million)
4, Execute DDL in MySQL client
```
mysql> alter table t_order add column a varchar(64) after order_id;
Query OK, 0 rows affected (16.10 sec)
```
5, In another MySQL client, execute `show processlist;` repeatedly
```
mysql> show processlist;
+--------------------------------------+------+-----------------+-------------+---------+------+---------------+------+
| Id | User | Host | db | Command | Time | State | Info |
+--------------------------------------+------+-----------------+-------------+---------+------+---------------+------+
| bc5c2ee7-f662-4cdf-875e-6ab7df6477ad | root | 0:0:0:0:0:0:0:1 | sharding_db | Execute | 3 | Executing 0/4 | |
+--------------------------------------+------+-----------------+-------------+---------+------+---------------+------+
1 row in set (0.01 sec)
mysql> show processlist;
+--------------------------------------+------+-----------------+-------------+---------+------+---------------+------+
| Id | User | Host | db | Command | Time | State | Info |
+--------------------------------------+------+-----------------+-------------+---------+------+---------------+------+
| bc5c2ee7-f662-4cdf-875e-6ab7df6477ad | root | 0:0:0:0:0:0:0:1 | sharding_db | Execute | 8 | Executing 2/4 | |
+--------------------------------------+------+-----------------+-------------+---------+------+---------------+------+
1 row in set (0.01 sec)
mysql> show processlist;
+--------------------------------------+------+-----------------+-------------+---------+------+---------------+------+
| Id | User | Host | db | Command | Time | State | Info |
+--------------------------------------+------+-----------------+-------------+---------+------+---------------+------+
| bc5c2ee7-f662-4cdf-875e-6ab7df6477ad | root | 0:0:0:0:0:0:0:1 | sharding_db | Execute | 15 | Executing 3/4 | |
+--------------------------------------+------+-----------------+-------------+---------+------+---------------+------+
1 row in set (0.01 sec)
mysql> show processlist;
Empty set (0.00 sec)
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] sandynz commented on issue #9568: [New Feature] Support to show execution process for running SQLs by RQL
Posted by GitBox <gi...@apache.org>.
sandynz commented on issue #9568:
URL: https://github.com/apache/shardingsphere/issues/9568#issuecomment-866017513
Hi @tristaZero , A general plan:
1. Show `Info` column
2. Support DML statement processlist
- In order to keep DML statements execution performance, execute process will not be reported immediately, it will be cached in local memory for some time, and there is threshold setting (e.g. 100 ms), if the execution completed within the threshold, then cancel the reporting
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #9568: [New Feature] Support to show execution process for running SQLs by RQL
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #9568:
URL: https://github.com/apache/shardingsphere/issues/9568#issuecomment-844794791
Hi @taojintianxia ,
Phase one of this issue is completed, so before entering phase two, we need to functional verification test currently.
Could you give us a hand here?
#### How
- Switch this feature on
Please refer to [DDL report](https://github.com/apache/shardingsphere/pull/10351/commits/b1d45b179718b516a7cfc1175bf20043ff6652fd) and package this project.
- Deployment
1. Zookeeper and MySQL/PostgreSQL proxy are necessary for its test.
2. The sharding rule is recommended.
3. A `large` logic table made up of maybe millions of rows can make the execution process enough longer.
- Execute DDL SQL
1. Run a DDL, like `Alter` etc in one window.
2. Run a query SQL, `show processlist` in another window to check the task triggered by the DDL above exists or not.
3. Once DDL finishes, please redo step 2.
#### Submit
Please attach the following screenshots
- The content of `executionnodes` of zk.
- The result of `show processlist` at the runtime.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero closed issue #9568: [New Feature] Support to show execution process for running SQLs by RQL
Posted by GitBox <gi...@apache.org>.
tristaZero closed issue #9568:
URL: https://github.com/apache/shardingsphere/issues/9568
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] taojintianxia commented on issue #9568: [New Feature] Support to show execution process for running SQLs by RQL
Posted by GitBox <gi...@apache.org>.
taojintianxia commented on issue #9568:
URL: https://github.com/apache/shardingsphere/issues/9568#issuecomment-857651087
hi , there
today I tried several times to test this function. followings are the precodiction :
1. raise up 5 mysql instance and insert 1 million data
2. luanch up a "brand new" zk
3. established a proxy with sharding rule
when proxy is start up, I execute a shell like following :
```
#!/bin/bash
export MYSQL_PWD=root
for (( i = 0; i < 10000000; i=(i+step) )); do
mysql -h127.0.0.1 -P3307 -uroot -Dsharding_db -e "show processlist;" | tee -a result.txt | echo "\n" | tee -a result.txt
done
exit 0
```
this shell will print out the `show processlist;` in a very shot interval . Unfortunately, it always print 0/5 as the status.
I just cut part of the result :
```
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
Id User Host db Command Time State Info
56045088-4ad3-41b4-b2e5-447af0840e1f root 127.0.0.1 sharding_db Execute 1562 Executing 0/5
```
BTW, it works if I set the suspend point in idea :), I saw the 1/5 .... but not in this way.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org