当前位置:网站首页>Let data fly -- quick Bi performance optimization solution and Practice

Let data fly -- quick Bi performance optimization solution and Practice

2020-12-07 19:16:36 Aliyun yunqi

Quick BI“ Data portals ” The importance of platform construction in enterprise data
After the initial construction of data center is completed , How to let customers intuitively feel the value of data platform ? Business decision makers 、 Managers of all departments 、 How can business operators go through a unified window , Quickly see the data provided by the data center , And use these data to support enterprise development in an all-round way ?
be based on Quick BI Build enterprise data portal , Effectively solve the above problems .
Quick BI“ Data portals ” It is the portal and window provided by data platform for business personnel , In the way of scenario analysis , For all kinds of people and roles in the enterprise , Provide unified visualization services . As a visual tool for truly reaching users ,Quick BI“ Data portals ” The importance of platform construction in enterprise data is particularly prominent .

Why is it right Quick BI To optimize ?
After the construction of enterprise data center is completed , Data center as enterprise unified data “ Supplier ”, More and more departments and business people will become “ Demand side ”, Hope to support the business through the data in the data center . With “ Demand side ” More and more , Concurrency requirements are getting higher and higher , As a unified entrance Quick BI The pressure on data portals is increasing . therefore , With the gradual deepening of the promotion and use of data platform in the enterprise , Need to be right Quick BI Overall optimization , To meet growing business needs .

The purpose of this article is to illustrate the problem. This article is based on the actual customer case Quick BI Practical exploration of performance optimization , This paper summarizes and puts forward the test method and performance optimization solution in the construction of data medium platform , throw away a brick in order to get a gem , Reference for other similar projects .

Typical data platform technology architecture

Based on the overall solution of Alibaba Cloud Data Center , The selection and design of data platform technology implementation , The typical technical architecture of data platform is shown in the figure below , There are five levels in the selection of technical architecture : Business data source storage technology 、 Data source access technology 、 Data storage and computing technology in data center 、 Data service and data application technology .
image.png

Data storage Computing , Offline data storage and calculation in the data center adopts MaxCompute Offline computing engine ; Alibaba cloud is used for real-time computing StreamCompute Stream computing technology to achieve ; Data development and management adopt Dataphin Intelligent data construction and management big data R & D platform .

Data service layer , The main points API Interface and database service , General general queries use RDS, Multidimensional analysis uses ADB, Search services use ElasticSearch, Use of online interface OneService service .

Data application layer , Use alicloud intelligent reporting tool Quick BI To achieve various customized data report analysis requirements ; And based on alicloud product technology system to achieve customer personalized data application needs . Based on Quick BI The data portal of the product is shown in the orange part of the figure .

Quick BI Pressure measurement method

1、 Pressure test target
Quick BI The pressure test target of platform portal in data mainly focuses on two types of release changes and user experience feedback , Do well in advance : Performance checkpoint 、 Performance tuning work , Meet the ultimate experience of daily customer reports 、 And special performance .
image.png

Two sticking points :
1) Pressure measurement , Ensure that online content is free from performance problems and hidden dangers
2) When a new report goes online , A simple pressure test is needed for the new online report , Avoid single report leading to system performance bottleneck .

A checkpoint :
When the customer intuitively uses the experience data, the portal report display is too slow , Pressure test the whole system , Check for performance bottlenecks and optimize .
In order to ensure that the data platform portal to meet the customer's daily report visualization performance requirements .

2、 Piezometric strategy

1) Pressure test environment
Quick BI Data platform portal usually has only formal environment at the customer site ,Quick BI The pressure test interface of portal page is all query request , The pressure test will not pollute the online data . Of course, to avoid affecting online users , The pressure test will be carried out at the node in the low peak period of the user, such as in the early morning . Most project data portal environments are as follows :
image.png

2) Pressure test implementation

When the pressure measurement is carried out , The main process is as follows :

image.png
(1) Access to customer needs , If the needs of customers may be to support 100 concurrent , The return time should not exceed 3s、 How many daily peak users PV etc. .
(2) According to the needs of customers, combine online PV Traffic forecast , Calculate the experience qps And limits qps.
(3) Preparation , Need to communicate with customers about the time point of pressure test and pressure test plan , At the same time, coordinate with the product side to follow up during the pressure test , Observe whether the product triggers abnormality during pressure measurement .
(4) Preparation of pressure measuring tools
(5) Preparation of pressure measurement data
QuickBI The portal involves multiple pages , A page contains multiple interface requests , If you manually grasp the interface record API It's a lot of work to enter the parameters , And the interface input data aging is not high 、 It's easy to make mistakes . Therefore, a real-time page recording request is needed to realize the rapid preparation of pressure test data .

3、 Pressure test method
Usually the data center Quick BI The performance bottleneck of portal is the database that provides data service , Therefore, during the pressure test , We mainly distinguish the report pages of different data source types , Carry out pressure test , As shown in the following table :
As shown in the following table :
image.png
(1) Touch height test
In the beginning qps Start putting pressure on , Observe the system personality indicators and business indicators , When it's stable, it goes up qps Concurrency number , In turn, cycle , Finally, the goal is achieved qps Even beyond the target qps, Stable for a while , Record the goals qps Under the system key indicators and business indicators ;
(2) Constant pressure test
It's usually smaller than the target qps Steady pressure , Continuous pressure test at least 2min, Observe the performance of the system , No problem , Adjust to target qps, Keep the pressure on 2min Or longer , Observe the performance of the system , Record the key indicators and business success rate of the system .
(3) Mixed pressure measurement
It refers to the simultaneous pressure test of multiple scenes , In this case, it is often necessary to fully evaluate the impact of the total traffic of multiple scenarios on modules and even products .
When the modules are mixed for pressure measurement , We need to evaluate each other qps Possible impact on modules and downstream modules .

A customer Quick BI Performance optimization practice

1、Quick BI Data portal pressure testing and tuning
In the actual customer case , In order to fundamentally solve Quick BI Data portal performance issues , The following scheme is used for the overall optimization and pressure test verification :
image.png
Firstly, optimization is divided into task optimization and product optimization :

Task optimization
(1) The first round of pressure measurement : First of all, Quick BI Do a round of pressure testing , Record current system performance data .
(2) Find the optimization object :ADB Products are based on top10 Time consuming SQL, Targeted exploration of performance bottlenecks ,Quick BI On the product side, we can find the Yuancang , Find custom SQL Data sets , The data sets with high time consumption and non parameter transfer are selected .
(3) Optimize :
ADB To optimize the table DDL Mainly and specification evaluation , By means of ADB Search in the library INFORMATION_SCHEMA.PARTITIONS, The partition of each table group is as follows :
image.png

In order to distribute the data evenly , Avoid the long tail problem , According to the product suggestion , By renaming the original table -> Create new table -> The way data is written back , take ADB Central African 128 Partition of the table DDL reform , The partition is adjusted to 128 Partition .

Quick BI By customizing SQL How data sets are consolidated into result tables , Reduce the complexity of each query when using such datasets SQL Performance consumption of . This kind of data set found through the meta warehouse is as follows , Two of these datasets are not custom-defined SQL Data sets , also SQL Very complicated , Yes ADB Great impact on system performance , Optimize and adjust these two datasets , Sink the processing logic into Dataphin Of ads layer , And synchronize the result table to ADB, for Quick BI Direct access to .
image.png

(4) The second round of pressure measurement : Yes Quick BI The second round of pressure test is carried out for each scene page , Verify the optimization effect .

Product optimization :
(1)Quick BI product : It will be upgraded to 3.6.1 After version , Support data cache function , You can cache the data of the default display page of each scene , Reduce performance consumption on back-end database .

(2)ADB: After optimization , Limit the current according to the situation , So as to ensure the normal use of the vast majority of users in the case of resource shortage . Follow up from 2.0 Version upgraded to 3.0 edition , Write efficiency is expected to improve 50%, Read efficiency is expected to improve 40%, also ADB 3.0 Version supports separation of storage and computing .

In addition to Quick BI When the independent deployment is officially launched ,GTS On site re protection , All related product sides are re insured remotely , In order to ensure the smooth operation of customer data portal environment .

meanwhile , because ADB Insufficient resources , Yes ADB Specifications are evaluated , Temporary use of coupons by United business side , take ADB Resources are temporarily expanded by , Give priority to ensure that customers go online , According to the actual use of customers after online, decide whether to maintain the expansion specifications .

The purpose of system tuning is to meet the customer's requirements for the performance of the data portal in the data center , So the pressure testing of data portal is essential , Through the analysis of ,20 individual qps Can meet the current customer's use needs , The actual pressure measurement is , Pressure test is carried out for data portal scenarios respectively , The pressure test method is as follows :
image.png

2、 After the fact monitoring
After the expansion and tuning are completed , We also need to monitor the usage of the system , The monitoring indicators are as follows :
image.png
By monitoring the indicators, we find that , After expansion and optimization :

(1) everyday 1:30~8:30 about , Data writing in data center ADB library ,CPU And other resources will take up a lot , The utilization rate can reach 90%+, But because it's non business use time , So there is no impact on business usage .

(2) working hours CPU Use average 40% about
During daytime use by business personnel , The current configuration of the system can theoretically support 100 User concurrency (20qps) Use , And the customer side will promote the portal system in the data platform in the short term , So keep the current system configuration , To cope with the influx of users for follow-up Promotion .

Quick BI Performance optimization suggestions

1、Quick BI The development of specification
Summary of the above performance test and performance optimization results , Developers are using it Quick BI In the process of visual development , We should follow certain development specifications , In order to avoid some performance risks in the early stage , Improve the performance of the overall platform .

Customize SQL modeling
image.png

Use Quick BI Data visualization development , Most of them SQL All are Quick BI Of SQL The engine automatically generates , There is no need for developers to pay attention to . And in the Quick BI The professional version supports “ Ad hoc analysis SQL” function ( Pictured above ) in , Allow developers to customize SQL Create a dataset , Here we need developers to follow the following principles SQL Development :

(1) Only if you have to use ad hoc queries SQL Medium “ Parameters ” Transfer function , In order to meet the needs of special scenes , Only use “ Ad hoc analysis SQL” To create a dataset . In other scenes , It is required to put the data query process into the data calculation process , That is to use Dataphin And other tools will need to process the data in advance to calculate , Form a special data table ,Quick BI Use the data directly as a result , Not in Quick BI in , When creating data sets, it is more complicated SQL The data processing .
(2) Customize SQL, It is not recommended to use more than 3 More than one union Type of operation . It is not recommended to use more than 5 More than fields group by operation . Dissatisfaction , It is recommended to use the above 1) Ways in , Front to data computing environment , Process the data , And then Quick BI Using data in .
(3) SQL The writing standard of , It needs to be strictly observed 《 Data platform model design & Data development specification 》 The requirements of .
4) Can be measured by simple performance testing in ad hoc analysis SQL Written in SQL Whether the script works , Written in this process SQL, After the page is executed , It is suggested that the time to return the results should not exceed 1s Time for , Otherwise, the corresponding page query may not meet the customer's requirements for the corresponding time .

Data set table Association
Quick BI On the dataset management page , Support the establishment of data sets through the association of data tables
image.png

Performance issues are more likely to occur here . The following specifications should be followed in the development process :
(1) The ability to use data table association should be reduced as much as possible , If you can Dataphin Such as data processing tools will be associated with processing well in advance , This association process is required to be brought to the computing layer .
(2) As in front 1) It's not enough , You need to use the data from the same data source as much as possible .
(3) If the previous 1)2) Can't satisfy , Try to use RDS or ADB The data sets in the database are related . Avoid using ODPS The data source of .
(4) Try to avoid full association between two tables , Or Cartesian product , This may result in a huge expansion of the data set , There are performance issues .
(5) A simple performance test can be used to measure whether the operation of data table Association in the dataset is feasible , Association in a dataset , When the page refreshes the preview data , It is suggested that the time to return the results should not exceed 1s Time for , Otherwise, the corresponding page query may not meet the customer's requirements for the corresponding time .

Data set caching
Quick BI On the dataset page , Supports cache configuration of data sets , Here's the picture :
image.png

Quick BI Of 3.6.1 After the release, support for ODPS and ADB The data set of the data source is configured for caching , Technically, it will cache the data set with cache enabled to Quick BI It is configured during installation and deployment Redis above , To reduce frequent access to the source database , Accelerate query performance . Use this function , We need to pay attention to :
(1) Redis The data cache is updated on an hourly basis , Therefore, the data set with cache enabled will not be synchronized with the data source in real time , If the source data changes , Query results will not respond in real time , Only according to Redis To recognize the latest data changes .
(2) Redis Space is limited ( It depends on the configuration of the installation and deployment ), Therefore, it is not recommended that all datasets should open the caching function , But should choose the concurrent query degree to be higher , Poor performance data sets , Open the function with emphasis .

2、 AnalyticDB for MySQL Table design specification

ADB Data model :
ADB yes MPP Distributed architecture , The data model is as follows :
image.png
When users design tables , We need to focus on the following four points :
Distribution keys ( Primary partition key )
Distribution keys ( It also becomes the primary partition key ) According to the distribution bond Hash value , Break the table data into pieces .
therefore , When selecting the distribution key , Make sure the data is evenly distributed as much as possible , Avoid data skew . That's the top priority !
meanwhile , Choose as many tables as possible join The associated key when , Avoid data shuffle.
For some code tables with little data and few updates , You can choose to create as “ Dimension table ”, To avoid data shuffle, Lifting performance .

The partitioning key ( Secondary partition key )
Under each level one partition , According to List Value, Allocate data to multiple chunks .

Partition keys are usually based on “ date ”, And according to the definition of the number of secondary partitions , Sort by partition key value , Most reserved N Secondary partitions . This gives the data “ Life cycle ” Characteristics of .

Primary key (Primary Key)
Determine the uniqueness of records through the primary key , And the distribution key and partition key must be included in the primary key .

To ensure the performance of primary keys , You usually have to choose “ Numerical type ” As the primary key , And strictly control the number of primary keys , Usually controlled in 4 Within columns .

Gather Columns (Clustered Key)
By physically sorting the same data together , It can reduce IO And improve query performance . Generally, the aggregation column selects those with a certain amount of duplicate data 、 And it is often used as a query filter , For example, product type 、 Personnel department, etc .

3、AnalyticDB for MySQL The development of specification
AnalyticDB for MySQL Have a strong self-developed storage 、MPP Computing engines and advanced optimizers , Usually customers don't need to pay too much attention to SQL Standard or not . however , The following basic principles can be fully utilized ADB Characteristics , The best performance has been achieved :

Try to avoid nesting functions on columns
as follows , If you nest functions on Columns , Will cause the index on this column to be invalid , So we need to scan the whole table data , Increasing system resource consumption will also affect query performance .
image.png

therefore , We're writing SQL Try to avoid nesting functions on columns , above SQL The following changes can be made :
image.png

Try to make sure join Time distribution is based on :
If two tables Join Is it based on distributed keys , It requires a lot of data shuffle, as follows :
for example :
surface customer The distribution bond of is UserId
surface order The distribution bond of is OrderId
SQL:
Select * from customer c join order o on c.userId=o.userID

stay SQL When it comes to execution, it is necessary to order surface shuffle data , This will increase the resource consumption of the system , Including memory 、 The Internet 、CPU etc. , Query response time will also increase
image.png
therefore , We need to change Order The distribution key of the table is UserID, So the top SQL It will be executed in a single ECU The calculation is done locally , To improve performance , as follows :
image.png

Add as many filters as you can
By default , Customers don't need to care which columns need to be indexed ,ADB Indexes are created on all columns . But if the filtration condition is not good , Even missing , You can't play ADB Powerful self-developed index performance , Full data scanning is needed . therefore , We need to be based on the nature of the business and the data , Add as many filters as you can .

Link to the original text
This article is the original content of Alibaba cloud , No reprint without permission .

版权声明
本文为[Aliyun yunqi]所创,转载请带上原文链接,感谢
https://chowdera.com/2020/11/20201119102328906q.html