Fast and Cost-effective MySQL Analysis Solution Based on Alibaba Cloud DLA

Background

Scenario 1

MySQL databases are widely used in various business systems. In addition to online business logic reading and writing, MySQL databases require data analysis, such as BI reports, visual dashboards, and big data applications. When the data volume of a single MySQL database reaches a certain level, using MySQL for data analysis leads to poor performance, which will affect the read and write performance of online businesses. In this case, we need a new data analysis solution.

Scenario 2

MySQL data needs to be analyzed with log data. Some companies use open-source big data systems (such as Hive, Hadoop, and Spark) to build data warehouses. This solution works but involves extremely high human and resource costs. We must find a way to analyze MySQL data with data from other systems at a low cost.

Scenario 3

When the data volume in a MySQL database exceeds the limit of a single database instance, database administrators (DBAs) split the data in a database into multiple tables in multiple databases. This makes data analysis very complicated. Therefore, a new analysis solution is required.

Solution Evaluation Factors

Which of the following factors must be considered to resolve the problems in the MySQL analysis scenarios? Which solution should we select? The key factors to keep in mind while answering these questions include:

Solution Selection

A variety of solutions are available for MySQL data analysis, including direct analysis on read-only MySQL instances, user-created open-source data warehouses, and DLA construction solutions. Let’s take a closer look at the advantages and disadvantages of these solutions.

Analysis Based on Read-only MySQL Instances

  • The costs of additional read-only instances are high.

User-created Open-source Data Warehouse

  • Highest Cost: You need to purchase additional servers to build a system, which increases the hardware costs. The overall cost of this solution is the highest.

AnalyticDB

Use open-source or commercial AnalyticDB to synchronize data through data synchronization tools, and then perform SQL-based data analysis.

  • Additional resources need to be purchased, leading to high costs.

DLA Construction Solution

  • Strong Analysis Capabilities: DLA provides the same user experience as MySQL. The increase in data volume has little impact on analysis performance.
  • Extremely Low Cost: You do not need to purchase any servers. You are billed based on the number of query requests, and no maintenance costs are charged.
  • No Impact on Source Databases: Data analysis has no impact on online businesses.

Evaluation Data and Technical Principles of the DLA Construction Solution

Let’s take a closer look at the evaluation data and technical principles of the DLA construction solution.

Low Cost and High Performance

Low Cost

Ease of Use

Compatibility with Multiple Data Sources

Impact on Source Databases

DLA-based queries have no impact on the source databases. When the DLA solution is used to synchronize data from source databases, the impact on the source databases is maintained at less than 10%. The following figure shows the CPU utilization for different specifications of source databases. As the specifications of a database increase, the number of connections automatically increases, but the average CPU utilization of the source database is kept below 10%.

  • During concurrent synchronization of a table, sharding is performed by the index column to quickly locate a data range based on indexes. This reduces the impact of synchronization on the source databases.
  • By default, the DLA solution synchronizes data at off-peak hours to prevent impacting online businesses.

DLA Practices

Complete the following steps for using DLA to build a low-cost MySQL analytics solution. (For more information, visit the official documentation)

Original Source:

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store