Help Center > > User Guide> GaussDB(for MySQL) DB Instance Management (Development Tool)> Database Management

Database Management

Updated at: Jun 18, 2021 GMT+08:00

Function Overview

You can go to the Database Management page in either of the following ways:

  • In the database list of the Home page, locate the target database and click Manage in the Operation column.
  • Choose Database Management on the top menu bar.
Figure 1 Database Management
Table 1 Functions

No.

Description

1

Displays the name of the current database.

2

Allows you to switch between system and non-system databases.

3

Displays the IP address, port number, and character set of the current DB instance.

4

Allows you to open the SQL window of the current database.

5

Periodically collects metadata such as databases, tables, and fields of the DB instance and stores the data to the databases of DAS.

Advantages:

  • Reduces queries on user databases and the impact on the performance of user databases.
  • Improves the search performance for DB instances that contain a large number of tables and supports pagination queries.

Only structural metadata is collected, which means that user's table data is not included.

6

Database management consists of three functional modules: objects, SQL tuning, and metadata collection.

7

Allows you to perform operations.

Object List

If there are too many tables, the system will not collect the metadata and display the table list to mitigate the impact on database performance.

You can manage tables, views, stored procedures, events, triggers, and functions of databases. The main operations include creating, querying, and modifying objects.

SQL Tuning

SQL tuning helps you query the SQL statements that are executed frequently, consume a large amount of resources, or take a long time to execute. Therefore, you can optimize operations according to the diagnosis results.

  1. On the Database Management page, click the SQL Tuning tab. You can search for SQL tuning history by date to view details or you can add or delete SQL tuning records.

    Figure 2 SQL Tuning

  2. Click Add SQL Performance Tuning. In the displayed dialog box, set Task Type to SQL statement or SQL file. Then, edit the SQL statements under SQL Statement and click OK.

    Figure 3 Adding a SQL performance tuning task
    • The following types of statements will be changed to the SELECT statements for tuning:

      SELECT ... FOR UPDATE, UPDATE ... SET ... WHERE ..., DELETE FROM ... WHERE ..., INSERT INTO ... SELECT ...

    • Except for the preceding statements, other statements do not support SQL tuning. However, the diagnosis task can be successfully submitted and a message is displayed indicating that the diagnosis fails.
    • The following shows the unsupported syntaxes:
      • alter table t add index idx_name(name)
      • show databases
      • grant SELECT,PROCESS on d.t to 'am'@'%' with grant option
      • insert into t(i, v) values (1, 'a').
    • Only the diagnosis of the SELECT, INSERT, UPDATE, and DELETE statements is supported. An INSERT statement must contain a SELECT clause.
    • SQL statements for querying system databases information_schema, test, and mysql are not supported.
    • View statements are not supported.

  3. In the SQL tuning list, locate the target task and click View Details in the Operation column.

    Figure 4 Viewing task details
    Figure 5 Task details

    You can view the tuning details at the bottom of the task list.

    Figure 6 Tuning details

Metadata Collection

DAS periodically collects the metadata of DB instance databases, tables, and fields and stores the collected data in the DAS database. In addition, it collects only structural metadata, but not data in user tables, which ensures data security. Metadata collection delivers the following advantages:

  • Reduces queries on user databases and the impact on the performance of user databases.
  • Improves the search performance for DB instances that contain a large number of tables and supports pagination queries.
  1. On the Database Management page, enable Auto Metadata Collection in the upper right corner and click the Metadata Collection tab.

    Figure 7 Metadata Collection

  2. On the displayed page, click Collect Now to start the collection. You can also stop the collection or view the collection details.

    Figure 8 Collect Now
    • Clear Collected Data: Clears the collected data such as metadata, database structure, and table structure.
    • Delete Logs: Deletes logs. Deleted logs cannot be restored. Exercise caution when performing this operation.

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel