Hologres Uncover: Optimize Copy, Increase Batch Import Performance by + 5 times

Brief introduction:Discover the technical principle of Hologres optimized COPY, and realize the performance of batch import increased by 5 times +

Hologres (Chinese name) interactive analysis is ali cloud from the research on the number of one-stop real-time warehouse, the cloud native system combines real-time service and analysis of large data, fully compatible with PostgreSQL deal with large data ecological seamless get through, can use the same set of data architecture also supports real-time written real-time query and real-time offline federal analysis. Its emergence simplifies the architecture of business, provides real-time decision-making ability for business, and enables big data to play a greater role in business value. From the birth of Ali Group to the commercialization on the cloud, with the development of the business and the evolution of technology, Hologres is also continuously optimizing the competitiveness of its core technology. In order to make you better understand Hologres, we plan to continue to launch a series of revealing the underlying technical principles of Hologres, from high-performance storage engine to efficient query engine. High throughput write to high QPS query, all-round interpretation of Hologres, please continue to pay attention!

Highlights of previous seasons:

• VLDB 2020 paper”Alibaba Hologres: A cloud-Native Service for Hybrid Serving/Analytical Processing“
• Hologres disclosure:First time! Alibaba cloud native real-time data warehouse core technology revealed
• Hologres disclosure:First unveiling of cloud native Hologres storage engine
• Hologres disclosure:Hologres efficient distributed query engine
• Hologres disclosure:Core principles for high performance native acceleration of MaxCompute[
  
  ] (https://topic.atatech.org/art…
  
  In this issue, we will introduce the technical principle of Hologers Copy import performance optimization to achieve 5+ times performance improvement.

background

In PostgreSQL, the COPY command is used for text file data import, which plays an important role in the PG ecosystem:

• Perfect compatibility with PG dozens of data types, including custom types and so on.
• Text parsing import details are very well considered, is also the industry benchmark.
• Business value, low cost of business integration, PSQL one command or Copy Manager and so on without third party integration dependencies.
• Frequent use in daily development and testing, such as TPCH testing, functional verification, online problem repetition, etc.

Hologres is fully compatible with the PG(PostgreSQL) ecosystem and supports importing data with the COPY command, but in PostgreSQL, the import performance of COPY is less than satisfactory at 20MB/s. However, in big data processing scenarios, high performance writing is often required, and the performance of PG COPY writing can not meet the actual business. Based on this, Hologres optimizes the COPY write performance to achieve high performance and high availability. This article will introduce the technical principle of Hologres’ optimization of COPY command, so as to achieve 5 times + import performance.

Copy Bottleneck Positioning

In earlier versions, COPY the implementation flowchart imported in Hologres as follows:

Execution process:

1. The FE (FrontEnd) is responsible for receiving user requests, incoming data, and parsing (including branch, split, Format parsing, etc.).

2.FE writes the shuffled Service into ARROW.

3.HQE (Hologres Query Engine) obtains data from the Shuffle Service and completes the Shuffle according to the distribution key.

SE (Storage Engine) writes the data stream to PANGU.

During the work of Copy, we can see that the process of FE has been in the state of full CPU. The following figure is the record of performance debugging:

The following bottlenecks can be identified:

• Performance loss introduced by intermediate nodes of Shuffle Service. As shown in the red box in the figure above, the internal data structure of PG HeapTuple needs to be converted to Arrow and written into Shuffle Service.
• Time taken by PG parsing itself, such as I/O, slicing of the data stream, parsing by type Format, and so on.

Technical Difficulties in Transformation

After locating the bottleneck and combining with the existing technical architecture of Hologres, there are mainly the following difficulties in improving performance:

• PostgreSQL’s process model has a lot of Global Runtime Context, so it’s expensive to rework multiple threads.
• Type resolution relies on the PostgreSQL type system, so it can’t be easily accelerated with third-party text parsing libraries such as Arrow.
• FE Meta management process requires the interaction between PostgreSQL system table and COPY, so as to ensure the stability and correctness of PostgreSQL COPY system table behavior.

Hologres optimization

In order to overcome the above difficulties and solve the import efficiency problem, Hologres has made optimization for Fe, mainly by decoupling the Fe workflow:

• Make sure the Request process is only responsible for receiving requests, data flows and simple data splitting.
• The work of text parsing is distributed to PQE with the support of PG computing power.
• The optimized execution scheme is shown in the figure below. After optimization, the execution process of importing the data into Hologres via COPY is as follows:
• FE is responsible for receiving user requests, slicing incoming data into rows and writing to a shared Queue.
• The Coordinator dispatches both FE and PQE to the same machine, so that queues can be implemented as Shared Memory, and the interaction between FE and PQE is more efficient.
• PQE retrieves the corresponding slice from the Queue, and performs row splitting, column splitting, and specific type parsing.
• HQE drags data from PQE, calculates the distribution and shuffles it.
• The SE is responsible for writing incoming data to Pangu.

5 times + performance improvement

Through the optimization of the system, the performance of Hologres COPY import has made a qualitative leap. Take TPCH LINEITEM table as an example, 100 million data, 13G file, COPY import performance is as follows:

** As you can see from the figure, the optimized upload speed of Hologres COPY is up to 200MB/s+, which is more than 5 times faster than PostgreSQL! 支那

conclusion

Hologres is committed to one-stop real-time data warehouse, has a very large ecological family in data access, support a variety of heterogeneous data sources offline, real-time write, including DataWorks data integration, Flink, MaxCompute and other rich write methods. In the big data scenario, Hologres is fully compatible with PostgreSQL’s Copy command, and based on this, the system is optimized to achieve Copy import performance 5 times faster than PG. The import of data by the COPY command is another new way for the Hologres data access ecology to meet the requirements of high-performance business writing and facilitate the rapid construction of enterprise-level real-time data warehouse.

Author: Zheng Xiaowen (Lu Lai) is currently engaged in the development of Hologres engine for interactive analysis.

In the future, we will successively launch the series of unveiling the underlying technical principles of Hologres. The specific planning is as follows. Please keep your attention!

• Hologres disclosure:First time! Alibaba cloud native real-time data warehouse core technology revealed
• Hologres disclosure:First unveiling of cloud native Hologres storage engine
• Hologres disclosure:Deep resolution efficient distributed query engine
• Hologres disclosure:Core principles for high performance native acceleration of MaxCompute
• Hologres: How to use COPY for efficient imports (this article)
• Hologres Unveiled: How does __ support high throughput Upsert
• Hologres Unveiled: How does __ support ultra high QPS in online service scenarios
• Hologres Unravels: How __ supports high concurrency queries
• Hologres Unveiled: How does __ support high availability architectures
• Hologres Unveiled: How does __ support resource isolation and support multiple loads
• Hologres reveals the principle and practice of Proxima, a __ vector search engine
• Hologres Unveiled: __ Read Execution Plan, Query Performance Increases tenfold
• Hologres: How to design shards and Table groups in a distributed system
• Hologres Unveiled: How does __ support more Postgres Eco expansion packs
• Hologres Reveals: High Puff writes Hologres in N positions
• .

Thank you for reading, and welcome to experience Hologres, please refer to the user manual.

Copyright Notice:The content of this article is contributed by Aliyun real-name registered users, and the copyright belongs to the original author. Aliyun developer community does not own the copyright and does not bear the corresponding legal liability. For specific rules, please refer to User Service Agreement of Alibaba Cloud Developer Community and Guidance on Intellectual Property Protection of Alibaba Cloud Developer Community. If you find any suspected plagiarism in the community, fill in the infringement complaint form to report, once verified, the community will immediately delete the suspected infringing content.