Acceleration Engine

Application Scenarios

In direct connect data mode, the acceleration engine can improve the efficiency of data applications in big data scenarios, achieve higher performance, and meet the needs of large-scale data processing.

To use the acceleration engine, you need to configure the acceleration database in Acceleration Engine and monitor the data performance in the acceleration database.

Function Description

Three functions are available:

• Acceleration Engine: You can configure a database (only the StarRocks database is supported) as the acceleration database and monitor the status of the acceleration database.

• SQL Analysis: You can monitor the SQL execution speed and performance in FineBI.

• Excel Upload Record: You can view the upload status of Excel tables.

Prerequisites

This function (optional in FineBI 6.0) needs to be purchased and registered for usage.

Configuring the Acceleration Database

1. Choose Accelerate Engine > Connect Acceleration Database and enter the connection information. After successful connection, click Save.

Note:

Only the StarRocks database is supported as the acceleration database.

Configuration Item	Explanation
Database Name	Enter the name of the database.
Host and Port	Enter the IP address and port number of the database server. If it is a server cluster, you can add multiple IP addresses and port numbers.
Username and Password	Enter the username and password of the database. Note: The user needs to be a root user or a user with the NODE or OPERATE permissions of the SYSTEM layer in the StarRocks database. Authorization method: GRANT OPERATE ON SYSTEM TO USER 'user_name'@'%'

2. After successful connection, you can directly copy the data connection URL, select Data Connection, enter the acceleration database information, and add a data connection. For details, see StarRocks Data Connection.

3. You can click the deletion or edition button in the upper right corner to delete or edit the configured acceleration database.

Monitoring the Acceleration Database Status

After the connection, FineBI retrieves node status information from the acceleration database, which is divided into two modules:

• Display the status information of different frontend (FE) nodes in the acceleration database cluster: FE is responsible for receiving and processing query requests sent by the client, and forwarding these requests to the backend (BE) for execution. FE is also responsible for tasks such as query parsing, optimization, permission control, and result set return.

• Display the status information of different BE nodes in the acceleration database cluster: BE is the component responsible for query execution. BE is responsible for interacting with the storage engine, reading and loading data, executing query plans, performing aggregation, computation and other operations, and returning the final results to the FE for processing and returning to the client.

The following information is displayed in the acceleration database status.

• FE Information: name, IP address, JDBC port number, WEB port number, node role, cluster ID, join status, active status, start time, last heartbeat time, and error information.

• BE Information: BE ID, IP address, startup time, last heartbeat time, active status, CPU core quantity, CPU usage, memory usage, disk usage, disk capacity, available disk capacity, tablet quantity, maximum disk usage, data file occupation, and error information.

FineBI sends the SQL queries to the acceleration database during analysis. If a SQL query takes a long time (exceeds 3 seconds) or encounters an execution error, it is automatically recorded in the SQL Analysis tab page by the system. Then admins/operation personnel can quickly locate problems and optimize performance.

Note:

To use SQL Analysis, the following parameters need to be enabled in the database as a prerequisite.

set global enable_profile = true; ADMIN SET FRONTEND CONFIG ("enable_collect_query_detail_info" = "true");

At the top of the page, you can filter SQL queries by the query status, start time, time consumption, and database user.

1. For example, filter SQL queries with time consumption exceeding 10 seconds. There is a query taking 57 seconds. You can double-click its dashboard ID, component ID, and dataset ID and copy its query ID by right-clicking it. Choose BI Tools > Inquiry > Table Name Query and find the corresponding data table based on the ID. 

2. Click Query ID to view the query information details page. In SQL Details, the specific execution statements of this SQL are displayed.

Above the SQL statements, resources related to FineBI are also marked.

3. Click Execution Time. Then the type, time consumption, and time consumption proportion of each operator and the number of data rows returned by each operator are displayed, helping you identify the reasons for slow performance.

For example, after clicking an operator with a large proportion of node time consumption, you can view the specific node details.

• The respective percentage of Execution Time I/O and Processing Time in the total time of the operator

• Specific information of the operator 

4. View Execution Details and Query Plan

• Execution Details: Statements of execution time that can be copied to a text file for viewing For example, you (intranet users) can copy the statements and send them to the external personnel who thus can troubleshoot execution speed.

• Query Plan: Mainly provided for DBAs to view, helping DBAs further understand query execution details and performance characteristics, and enhance performance optimization, resource management, and troubleshooting to improve overall performance and stability of the database system.

In normal direct connect data mode, the fusion analysis of database tables and uploaded Excel tables in FineBI are limited in the following cases:

• Originally, uploading Excel tables using Insert Into statements shows poor performance and slow upload speed.

• Excel data cannot exceed 10,000 rows, which limits the analysis.

• Admins cannot know which Excel tables have been used and which are not, causing great inconvenience for Excel management.

To solve these problems, FineBI has optimized the fusion analysis of database tables in the acceleration database and uploaded Excel tables.

After the acceleration database is configured in FineBI, the fine_bi_crossdata database is created automatically in the acceleration database by the system. If you upload an Excel table to FineBI in a direct connect mode, the system automatically uses the Excel table to create a table in fine_bi_crossdata database. In this way, the fusion analysis of database tables in the acceleration database and uploaded Excel tables is no longer affected by the above three restrictions.

Select Excel Upload Record. Then you can view the correspondence between the source files, database tables, and FineBI table names. And you can search for the required table based on the table name and update time.