All Products
Search
Document Center

Statements for displaying rules and node topologies

Last Updated: Sep 01, 2020

This topic lists the statements for viewing rules and topology supported by DRDS.

1. SHOW RULE [FROM tablename]

Note:

  • show rule: shows the sharding status of each logical table in a database.
  • show rule from tablename: shows the sharding status of a specified logical table in a database.

The following describes the meanings of important columns:

  • BROADCAST: indicates whether the table is a broadcast table. 0 indicates “No” and 1 indicates “Yes”.
  • DB_PARTITION_KEY: indicates the table shard key. If no table shards exist, this parameter is NULL.
  • DB_PARTITION_POLICY: indicates the database sharding policy. Options are Hash and date policies such as YYYYMM, YYYYDD, and YYYYWEEK.
  • DB_PARTITION_COUNT: indicates the number of database shards.
  • TB_PARTITION_KEY: indicates the table shard key. If no table shards exist, this parameter is NULL.
  • TB_PARTITION_POLICY: indicates the table sharding policy. Options are Hash and date policies such as MM, DD, MMDD, and WEEK.
  • TB_PARTITION_COUNT: indicates the number of table shards.
  1. mysql> show rule;
  2. +------+--------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
  3. | ID | TABLE_NAME | BROADCAST | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT |
  4. +------+--------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
  5. | 0 | dept_manager | 0 | | NULL | 1 | | NULL | 1 |
  6. | 1 | emp | 0 | emp_no | hash | 8 | id | hash | 2 |
  7. | 2 | example | 0 | shard_key | hash | 8 | | NULL | 1 |
  8. +------+--------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
  9. 3 rows in set (0.01 sec)

2. SHOW FULL RULE [FROM tablename]

You can execute this SQL statement to view the sharding rules of logical tables in a database. It displays more detailed information than the SHOW RULE command.

The following describes the meanings of important columns:

  • BROADCAST: indicates whether the table is a broadcast table. 0 indicates “No” and 1 indicates “Yes”.
  • JOIN_GROUP: a reserved field. It is meaningless currently.
  • ALLOW_FULL_TABLE_SCAN: indicates whether to allow data querying when no table shard key is specified for database or table sharding. If this parameter is set to True, each physical table is scanned to find data that meets the condition, which is a full table scan.
  • DB_NAME_PATTERN: the digit 0 inside a pair of braces ({}) in DB_NAME_PATTERN is a placeholder. When the SQL statement is executed, it is replaced by the value calculated by DB_RULES_STR. The number of digits remains unchanged. For example, if the value of DB_NAME_PATTERN is SEQ_{0000}_RDS and the value of DB_RULES_STR is [1,2,3,4], the following four DB_NAMEs are generated: SEQ_0001_RDS, SEQ_0002_RDS, SEQ_0003_RDS, and SEQ_0004_RDS.
  • DB_RULES_STR: indicates the database sharding rule.
  • TB_NAME_PATTERN: the digit 0 inside a pair of braces ({}) in TB_NAME_PATTERN is a placeholder. When the SQL statement is executed, it is replaced by the value calculated by TB_RULES_STR. The number of digits remains unchanged. For example, if the value of TB_NAME_PATTERN is table_{00} and the value of TB_RULES_STR is [1,2,3,4,5,6,7,8], the following eight tables are generated: table_01, table_02, table_03, table_04, table_05, table_06, table_07, and table_08.
  • TB_RULES_STR: indicates the table sharding rule.
  • PARTITION_KEYS: indicates a set of database and table shard keys. When database sharding and table sharding coexist, the database shard key is placed before the table shard key.
  • DEFAULT_DB_INDEX: indicates the database shard in which single database and single table are stored.
  1. mysql> show full rule;
  2. +------+--------------+-----------+------------+-----------------------+----------------------------------------------------+-----------------------------------------------------+-----------------+------------------------------------+----------------+--------------------------------------------------+
  3. | ID | TABLE_NAME | BROADCAST | JOIN_GROUP | ALLOW_FULL_TABLE_SCAN | DB_NAME_PATTERN | DB_RULES_STR | TB_NAME_PATTERN | TB_RULES_STR | PARTITION_KEYS | DEFAULT_DB_INDEX |
  4. +------+--------------+-----------+------------+-----------------------+----------------------------------------------------+-----------------------------------------------------+-----------------+------------------------------------+----------------+--------------------------------------------------+
  5. | 0 | dept_manager | 0 | NULL | 0 | SEQ_TEST_*************RGKKSEQ_TEST_****_0000_RDS | NULL | dept_manager | NULL | NULL | SEQ_TEST_*************RGKKSEQ_TEST_****_0000_RDS |
  6. | 1 | emp | 0 | NULL | 1 | SEQ_TEST_*************RGKKSEQ_TEST_****_{0000}_RDS | ((#emp_no,1,8#).longValue().abs() % 8) | emp_{0} | ((#id,1,2#).longValue().abs() % 2) | emp_no id | SEQ_TEST_*************RGKKSEQ_TEST_****_0000_RDS |
  7. | 2 | example | 0 | NULL | 1 | SEQ_TEST_*************RGKKSEQ_TEST_****_{0000}_RDS | ((#shard_key,1,8#).longValue().abs() % 8).intdiv(1) | example | NULL | shard_key | SEQ_TEST_*************RGKKSEQ_TEST_****_0000_RDS |
  8. +------+--------------+-----------+------------+-----------------------+----------------------------------------------------+-----------------------------------------------------+-----------------+------------------------------------+----------------+--------------------------------------------------+
  9. 3 rows in set (0.01 sec)

3. SHOW TOPOLOGY FROM tablename

You can execute this SQL statement to view the topology of a specified logical table, that is, the database shards to which data in the logical table is partitioned and the table shards in each database shard.

  1. mysql> show topology from emp;
  2. +------+--------------------------------------------------+------------+
  3. | ID | GROUP_NAME | TABLE_NAME |
  4. +------+--------------------------------------------------+------------+
  5. | 0 | SEQ_TEST_*************RGKKSEQ_TEST_****_0000_RDS | emp_0 |
  6. | 1 | SEQ_TEST_*************RGKKSEQ_TEST_****_0000_RDS | emp_1 |
  7. | 2 | SEQ_TEST_*************RGKKSEQ_TEST_****_0001_RDS | emp_0 |
  8. | 3 | SEQ_TEST_*************RGKKSEQ_TEST_****_0001_RDS | emp_1 |
  9. | 4 | SEQ_TEST_*************RGKKSEQ_TEST_****_0002_RDS | emp_0 |
  10. | 5 | SEQ_TEST_*************RGKKSEQ_TEST_****_0002_RDS | emp_1 |
  11. | 6 | SEQ_TEST_*************RGKKSEQ_TEST_****_0003_RDS | emp_0 |
  12. | 7 | SEQ_TEST_*************RGKKSEQ_TEST_****_0003_RDS | emp_1 |
  13. | 8 | SEQ_TEST_*************RGKKSEQ_TEST_****_0004_RDS | emp_0 |
  14. | 9 | SEQ_TEST_*************RGKKSEQ_TEST_****_0004_RDS | emp_1 |
  15. | 10 | SEQ_TEST_*************RGKKSEQ_TEST_****_0005_RDS | emp_0 |
  16. | 11 | SEQ_TEST_*************RGKKSEQ_TEST_****_0005_RDS | emp_1 |
  17. | 12 | SEQ_TEST_*************RGKKSEQ_TEST_****_0006_RDS | emp_0 |
  18. | 13 | SEQ_TEST_*************RGKKSEQ_TEST_****_0006_RDS | emp_1 |
  19. | 14 | SEQ_TEST_*************RGKKSEQ_TEST_****_0007_RDS | emp_0 |
  20. | 15 | SEQ_TEST_*************RGKKSEQ_TEST_****_0007_RDS | emp_1 |
  21. +------+--------------------------------------------------+------------+
  22. 16 rows in set (0.01 sec)

4. SHOW PARTITIONS FROM tablename

You can execute this SQL statement to view the set of database shard keys and table shard keys, which are separated with commas (,). If the final result contains two values, both database sharding and table sharding are performed. The first value is the database shard key and the second value is the table shard key. If only one value is returned, only database sharding is performed. This value is the database shard key.

  1. mysql> show partitions from emp;
  2. +-----------+
  3. | KEYS |
  4. +-----------+
  5. | emp_no,id |
  6. +-----------+
  7. 1 row in set (0.00 sec)

5. SHOW BROADCASTS

You can execute this SQL statement to view the list of broadcast tables.

  1. mysql> show broadcasts;
  2. +------+------------+
  3. | ID | TABLE_NAME |
  4. +------+------------+
  5. | 0 | ***2 |
  6. | 1 | ***_tbl |
  7. +------+------------+
  8. 2 rows in set (0.01 sec)

6. SHOW DATASOURCES

You can execute this SQL statement to view the information about the underlying storage, including the database name, database group name, connection URL, username, storage type, read/write weight, and connection pool information.

The following describes the meanings of important columns:

  • SCHEMA: indicates the database name.
  • GROUP: indicates the database group name. Grouping is intended to manage multiple databases that store the same data, such as the primary and secondary databases that replicate data through an ApsaraDB RDS for MySQL instance. It is mainly used for read/write splitting and primary/secondary failover.
  • URL: indicates the connection information of the underlying ApsaraDB RDS for MySQL instances.
  • TYPE: indicates the underlying storage type. Currently, only ApsaraDB RDS for MySQL is supported.
  • READ_WEIGHT: indicates the read weight of the database. When a primary ApsaraDB RDS for MySQL instance has many read requests, you can use read/write splitting of DRDS to distribute the read traffic, thus reducing the read pressure on the instance. DRDS automatically identifies read and write traffic. It diverts the write traffic to the primary ApsaraDB RDS for MySQL instance and the read traffic to all ApsaraDB RDS for MySQL instances based on the weight setting.
  • WRITE_WEIGHT: indicates the write weight. See the preceding description.
  1. mysql> show datasources;
  2. +------+----------------------------+------------------------------------------------+--------------------------------------------------+----------------------------------------------------------------------------------+-----------+-------+------+------+------+--------------+----------+--------------+---------------+----------------------------------------------+-------------+--------------+
  3. | ID | SCHEMA | NAME | GROUP | URL | USER | TYPE | INIT | MIN | MAX | IDLE_TIMEOUT | MAX_WAIT | ACTIVE_COUNT | POOLING_COUNT | ATOM | READ_WEIGHT | WRITE_WEIGHT |
  4. +------+----------------------------+------------------------------------------------+--------------------------------------------------+----------------------------------------------------------------------------------+-----------+-------+------+------+------+--------------+----------+--------------+---------------+----------------------------------------------+-------------+--------------+
  5. | 0 | seq_test_148************* | rds*****************_seq_test_****_0000_****_1 | SEQ_TEST_*************RGKKSEQ_TEST_****_0000_RDS | jdbc:mysql://rds*****************.mysql.rds.aliyuncs.com:3306/seq_test_****_0000 | ********0 | mysql | 0 | 24 | 72 | 15 | 5000 | 0 | 1 | rds*****************_seq_test_****_0000_**** | 10 | 10 |
  6. | 1 | seq_test_148************* | rds*****************_seq_test_****_0001_****_2 | SEQ_TEST_*************RGKKSEQ_TEST_****_0001_RDS | jdbc:mysql://rds*****************.mysql.rds.aliyuncs.com:3306/seq_test_****_0001 | ********0 | mysql | 0 | 24 | 72 | 15 | 5000 | 0 | 1 | rds*****************_seq_test_****_0001_****| 10 | 10 |
  7. | 2 | seq_test_148************* | rds*****************_seq_test_****_0002_****_3 | SEQ_TEST_*************RGKKSEQ_TEST_****_0002_RDS | jdbc:mysql://rds*****************.mysql.rds.aliyuncs.com:3306/seq_test_****_0002 | ********0 | mysql | 0 | 24 | 72 | 15 | 5000 | 0 | 1 | rds*****************_seq_test_****_0002_**** | 10 | 10 |
  8. | 3 | seq_test_148************* | rds*****************_seq_test_****_0003_****_4 | SEQ_TEST_*************RGKKSEQ_TEST_****_0003_RDS | jdbc:mysql://rds*****************.mysql.rds.aliyuncs.com:3306/seq_test_****_0003 | ********0 | mysql | 0 | 24 | 72 | 15 | 5000 | 0 | 1 | rds*****************_seq_test_****_0003_**** | 10 | 10 |
  9. | 4 | seq_test_148************* | rds*****************_seq_test_****_0004_****_5 | SEQ_TEST_*************RGKKSEQ_TEST_****_0004_RDS | jdbc:mysql://rds*****************.mysql.rds.aliyuncs.com:3306/seq_test_****_0004 | ********0 | mysql | 0 | 24 | 72 | 15 | 5000 | 0 | 1 | rds*****************_seq_test_****_0004_**** | 10 | 10 |
  10. | 5 | seq_test_148************* | rds*****************_seq_test_****_0005_****_6 | SEQ_TEST_*************RGKKSEQ_TEST_****_0005_RDS | jdbc:mysql://rds*****************.mysql.rds.aliyuncs.com:3306/seq_test_****_0005 | ********0 | mysql | 0 | 24 | 72 | 15 | 5000 | 0 | 1 | rds*****************_seq_test_****_0005_**** | 10 | 10 |
  11. | 6 | seq_test_148************* | rds*****************_seq_test_****_0006_****_7 | SEQ_TEST_*************RGKKSEQ_TEST_****_0006_RDS | jdbc:mysql://rds*****************.mysql.rds.aliyuncs.com:3306/seq_test_****_0006 | ********0 | mysql | 0 | 24 | 72 | 15 | 5000 | 0 | 1 | rds*****************_seq_test_****_0006_**** | 10 | 10 |
  12. | 7 | seq_test_148*************| rds*****************_seq_test_****_0007_****_8 | SEQ_TEST_*************RGKKSEQ_TEST_****_0007_RDS | jdbc:mysql://rds*****************.mysql.rds.aliyuncs.com:3306/seq_test_****_0007 | ********0 | mysql | 0 | 24 | 72 | 15 | 5000 | 0 | 1 | rds*****************_seq_test_****_0007_**** | 10 | 10 |
  13. +------+----------------------------+------------------------------------------------+--------------------------------------------------+----------------------------------------------------------------------------------+-----------+-------+------+------+------+--------------+----------+--------------+---------------+----------------------------------------------+-------------+--------------+
  14. 8 rows in set (0.01 sec)

7. SHOW NODE

You can execute this SQL statement to view the accumulative number of read and write operations and accumulative read/write weights of a physical database.

The following describes the meanings of important columns:

  • MASTER_READ_COUNT: indicates the cumulative number of read-only queries processed by the primary ApsaraDB RDS for MySQL instance.
  • SLAVE_READ_COUNT: indicates the cumulative number of read-only queries processed by the secondary ApsaraDB RDS for MySQL instances.
  • MASTER_READ_PERCENT: indicates the actual percentage of read-only queries processed by the primary ApsaraDB RDS for MySQL instance, not the configured percentage.
  • SLAVE_READ_PERCENT: indicates the actual percentage of read-only queries processed by the secondary ApsaraDB RDS for MySQL instances, not the configured percentage.

Note:

  • Read-only queries in transactions are sent to the primary ApsaraDB RDS for MySQL instance.
  • The MASTER_READ_PERCENT and SLAVE_READ_PERCENT fields indicate the cumulative historical data. After the read/write weight ratio has been changed, these values do not immediately reflect the latest read/write weight ratio, which is displayed after a long period of time has passed.
  1. mysql> show node;
  2. +------+--------------------------------------------------+-------------------+------------------+---------------------+--------------------+
  3. | ID | NAME | MASTER_READ_COUNT | SLAVE_READ_COUNT | MASTER_READ_PERCENT | SLAVE_READ_PERCENT |
  4. +------+--------------------------------------------------+-------------------+------------------+---------------------+--------------------+
  5. | 0 | SEQ_TEST_*************RGKKSEQ_TEST_****_0000_RDS | 12 | 0 | 100% | 0% |
  6. | 1 | SEQ_TEST_*************RGKKSEQ_TEST_****_0001_RDS | 0 | 0 | 0% | 0% |
  7. | 2 | SEQ_TEST_*************RGKKSEQ_TEST_****_0002_RDS | 0 | 0 | 0% | 0% |
  8. | 3 | SEQ_TEST_*************RGKKSEQ_TEST_****_0003_RDS | 0 | 0 | 0% | 0% |
  9. | 4 | SEQ_TEST_*************RGKKSEQ_TEST_****_0004_RDS | 0 | 0 | 0% | 0% |
  10. | 5 | SEQ_TEST_*************RGKKSEQ_TEST_****_0005_RDS | 0 | 0 | 0% | 0% |
  11. | 6 | SEQ_TEST_*************RGKKSEQ_TEST_****_0006_RDS | 0 | 0 | 0% | 0% |
  12. | 7 | SEQ_TEST_*************RGKKSEQ_TEST_****_0007_RDS | 0 | 0 | 0% | 0% |
  13. +------+--------------------------------------------------+-------------------+------------------+---------------------+--------------------+
  14. 8 rows in set (0.01 sec)