PolarDB:Rule and topology query statements

• SHOW RULE [FROM tablename]
• SHOW FULL RULE [FROM tablename]
• SHOW TOPOLOGY FROM tablename
• SHOW PARTITIONS FROM tablename
• SHOW BROADCASTS
• SHOW DATASOURCES
• SHOW NODE

SHOW RULE [FROM tablename]

mysql> show rule;
+------+--------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
| ID   | TABLE_NAME   | BROADCAST | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT |
+------+--------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
|    0 | dept_manager |         0 |                  | NULL                | 1                  |                  | NULL                | 1                  |
|    1 | emp          |         0 | emp_no           | hash                | 8                  | id               | hash                | 2                  |
|    2 | example      |         0 | shard_key        | hash                | 8                  |                  | NULL                | 1                  |
+------+--------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+
3 rows in set (0.01 sec)
            

SHOW FULL RULE [FROM tablename]

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

mysql> show full rule;
+------+--------------+-----------+------------+-----------------------+----------------------------------------------------+-----------------------------------------------------+-----------------+------------------------------------+----------------+--------------------------------------------------+
| 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                                 |
+------+--------------+-----------+------------+-----------------------+----------------------------------------------------+-----------------------------------------------------+-----------------+------------------------------------+----------------+--------------------------------------------------+
|    0 | dept_manager |         0 | NULL       |                     0 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0000_RDS   | NULL                                                | dept_manager    | NULL                               | NULL           | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0000_RDS |
|    1 | emp          |         0 | NULL       |                     1 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_{0000}_RDS | ((#emp_no,1,8#).longValue().abs() % 8)              | emp_{0}         | ((#id,1,2#).longValue().abs() % 2) | emp_no      id | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0000_RDS |
|    2 | example      |         0 | NULL       |                     1 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_{0000}_RDS | ((#shard_key,1,8#).longValue().abs() % 8).intdiv(1) | example         | NULL                               | shard_key      | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0000_RDS |
+------+--------------+-----------+------------+-----------------------+----------------------------------------------------+-----------------------------------------------------+-----------------+------------------------------------+----------------+--------------------------------------------------+
3 rows in set (0.01 sec)
            

SHOW TOPOLOGY FROM tablename

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

mysql> show topology from emp;
+------+--------------------------------------------------+------------+
| ID   | GROUP_NAME                                       | TABLE_NAME |
+------+--------------------------------------------------+------------+
|    0 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0000_RDS | emp_0      |
|    1 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0000_RDS | emp_1      |
|    2 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0001_RDS | emp_0      |
|    3 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0001_RDS | emp_1      |
|    4 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0002_RDS | emp_0      |
|    5 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0002_RDS | emp_1      |
|    6 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0003_RDS | emp_0      |
|    7 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0003_RDS | emp_1      |
|    8 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0004_RDS | emp_0      |
|    9 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0004_RDS | emp_1      |
|   10 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0005_RDS | emp_0      |
|   11 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0005_RDS | emp_1      |
|   12 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0006_RDS | emp_0      |
|   13 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0006_RDS | emp_1      |
|   14 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0007_RDS | emp_0      |
|   15 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0007_RDS | emp_1      |
+------+--------------------------------------------------+------------+
16 rows in set (0.01 sec)
            

SHOW PARTITIONS FROM tablename

You can execute this SQL statement to view a set of database and table shard keys, which are separated by commas (,). If two values are returned, 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.

mysql> show partitions from emp;
+-----------+
| KEYS      |
+-----------+
| emp_no,id |
+-----------+
1 row in set (0.00 sec)
            

SHOW BROADCASTS

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

mysql> show broadcasts;
+------+------------+
| ID   | TABLE_NAME |
+------+------------+
|    0 | brd2       |
|    1 | brd_tbl    |
+------+------------+
2 rows in set (0.01 sec)
            

SHOW DATASOURCES

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

mysql> show datasources;
+------+----------------------------+------------------------------------------------+--------------------------------------------------+----------------------------------------------------------------------------------+-----------+-------+------+------+------+--------------+----------+--------------+---------------+----------------------------------------------+-------------+--------------+
| ID   | SCHEMA                     | NAME                                           | GROUP                                            | URL                                                                              | USER      | TYPE  | INIT | MIN  | MAX  | IDLE_TIMEOUT | MAX_WAIT | ACTIVE_COUNT | POOLING_COUNT | ATOM                                         | READ_WEIGHT | WRITE_WEIGHT |
+------+----------------------------+------------------------------------------------+--------------------------------------------------+----------------------------------------------------------------------------------+-----------+-------+------+------+------+--------------+----------+--------------+---------------+----------------------------------------------+-------------+--------------+
|    0 | seq_test_1487767780814rgkk | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0000_iiab_1 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0000_RDS | jdbc:mysql://rds1ur80kcv8g3t6p3ol.mysql.rds.aliyuncs.com:3306/seq_test_wnjg_0000 | jnkinsea0 | mysql | 0    | 24   | 72   | 15           | 5000     | 0            | 1             | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0000_iiab | 10          | 10           |
|    1 | seq_test_1487767780814rgkk | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0001_iiab_2 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0001_RDS | jdbc:mysql://rds1ur80kcv8g3t6p3ol.mysql.rds.aliyuncs.com:3306/seq_test_wnjg_0001 | jnkinsea0 | mysql | 0    | 24   | 72   | 15           | 5000     | 0            | 1             | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0001_iiab | 10          | 10           |
|    2 | seq_test_1487767780814rgkk | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0002_iiab_3 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0002_RDS | jdbc:mysql://rds1ur80kcv8g3t6p3ol.mysql.rds.aliyuncs.com:3306/seq_test_wnjg_0002 | jnkinsea0 | mysql | 0    | 24   | 72   | 15           | 5000     | 0            | 1             | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0002_iiab | 10          | 10           |
|    3 | seq_test_1487767780814rgkk | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0003_iiab_4 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0003_RDS | jdbc:mysql://rds1ur80kcv8g3t6p3ol.mysql.rds.aliyuncs.com:3306/seq_test_wnjg_0003 | jnkinsea0 | mysql | 0    | 24   | 72   | 15           | 5000     | 0            | 1             | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0003_iiab | 10          | 10           |
|    4 | seq_test_1487767780814rgkk | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0004_iiab_5 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0004_RDS | jdbc:mysql://rds1ur80kcv8g3t6p3ol.mysql.rds.aliyuncs.com:3306/seq_test_wnjg_0004 | jnkinsea0 | mysql | 0    | 24   | 72   | 15           | 5000     | 0            | 1             | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0004_iiab | 10          | 10           |
|    5 | seq_test_1487767780814rgkk | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0005_iiab_6 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0005_RDS | jdbc:mysql://rds1ur80kcv8g3t6p3ol.mysql.rds.aliyuncs.com:3306/seq_test_wnjg_0005 | jnkinsea0 | mysql | 0    | 24   | 72   | 15           | 5000     | 0            | 1             | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0005_iiab | 10          | 10           |
|    6 | seq_test_1487767780814rgkk | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0006_iiab_7 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0006_RDS | jdbc:mysql://rds1ur80kcv8g3t6p3ol.mysql.rds.aliyuncs.com:3306/seq_test_wnjg_0006 | jnkinsea0 | mysql | 0    | 24   | 72   | 15           | 5000     | 0            | 1             | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0006_iiab | 10          | 10           |
|    7 | seq_test_1487767780814rgkk | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0007_iiab_8 | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0007_RDS | jdbc:mysql://rds1ur80kcv8g3t6p3ol.mysql.rds.aliyuncs.com:3306/seq_test_wnjg_0007 | jnkinsea0 | mysql | 0    | 24   | 72   | 15           | 5000     | 0            | 1             | rds1ur80kcv8g3t6p3ol_seq_test_wnjg_0007_iiab | 10          | 10           |
+------+----------------------------+------------------------------------------------+--------------------------------------------------+----------------------------------------------------------------------------------+-----------+-------+------+------+------+--------------+----------+--------------+---------------+----------------------------------------------+-------------+--------------+
8 rows in set (0.01 sec)
            

Important columns:

• SCHEMA: indicates the database name.
• GROUP: indicates the database group name. After the databases are grouped, you can manage multiple databases that store the same data in a group. For example, after you replicate the data of a database to an ApsaraDB RDS for MySQL instance, you can manage the primary database and the secondary database in a group. Database grouping enables read/write splitting and primary/secondary switchovers.
• URL: indicates the URL that is used to connect to an underlying ApsaraDB RDS for MySQL database.
• TYPE: indicates the underlying storage type. Only ApsaraDB RDS for MySQL is supported.
• READ_WEIGHT: indicates the read weight. If you want to reduce the number of read requests to the primary ApsaraDB RDS for MySQL instance, you can use the read/write splitting feature to distribute some read requests to the secondary ApsaraDB RDS for MySQL instances. This offloads the read requests from the primary ApsaraDB RDS for MySQL instance. PolarDB-X 1.0 automatically identifies the read and write requests. Then, it sends the write requests to the primary ApsaraDB RDS for MySQL instance and distributes the read requests to each ApsaraDB RDS for MySQL instance based on the specified read weights.
• WRITE_WEIGHT: indicates the write weight.

SHOW NODE

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

mysql> show node;
+------+--------------------------------------------------+-------------------+------------------+---------------------+--------------------+
| ID   | NAME                                             | MASTER_READ_COUNT | SLAVE_READ_COUNT | MASTER_READ_PERCENT | SLAVE_READ_PERCENT |
+------+--------------------------------------------------+-------------------+------------------+---------------------+--------------------+
| 0    | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0000_RDS |                12 |                0 | 100%                | 0%                 |
| 1    | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0001_RDS |                 0 |                0 | 0%                  | 0%                 |
| 2    | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0002_RDS |                 0 |                0 | 0%                  | 0%                 |
| 3    | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0003_RDS |                 0 |                0 | 0%                  | 0%                 |
| 4    | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0004_RDS |                 0 |                0 | 0%                  | 0%                 |
| 5    | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0005_RDS |                 0 |                0 | 0%                  | 0%                 |
| 6    | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0006_RDS |                 0 |                0 | 0%                  | 0%                 |
| 7    | SEQ_TEST_1487767780814RGKKSEQ_TEST_WNJG_0007_RDS |                 0 |                0 | 0%                  | 0%                 |
+------+--------------------------------------------------+-------------------+------------------+---------------------+--------------------+
8 rows in set (0.01 sec)
            

Important columns:

• MASTER_COUNT: indicates the accumulative number of read and write queries processed by the primary ApsaraDB RDS for MySQL instance.
• SLAVE_COUNT: indicates the accumulative number of read-only queries processed by the secondary ApsaraDB RDS for MySQL instances.
• MASTER_PERCENT: indicates the actual percentage of the accumulative read and write queries processed by the primary ApsaraDB RDS for MySQL instance. This is not the specified percentage.
• SLAVE_PERCENT: indicates the actual percentage of the accumulative read and write queries processed by the secondary ApsaraDB RDS for MySQL instances. This is not the specified percentage.