Navigating the High-Concurrency Challenges of User-Facing Analytics

User-facing analytics offers end users direct access to data analytics tools, freeing them from the need to rely on data specialists or IT teams to generate insights. While this democratizes data access and usage, it also presents a challenge: how to manage high concurrency in real time. This article explores different strategies to address this challenge with user-facing analytics.

Why User-Facing Analytics Requires High Concurrency

Let’s look at the social media platform Xiaohongshu as an example of high concurrency user-facing analytics. The bulk of Xiaohongshu’s revenue is derived from advertising. This platform offers advertisers a place to create custom real-time dashboards to monitor the performance of their ads. Each dashboard is filled with graphs and summaries — each translates to one or multiple SQL queries sent to the underlying database. When an advertiser checks the engagement rates or demographic distribution of ad viewers, several queries instantly fetch and process this data.

Now, scale this up to accommodate hundreds of thousands of advertisers, each with unique campaigns and multiple dashboard interactions. The sheer volume of simultaneous queries — whether from refreshing pages, switching between metrics or merely accessing the dashboard — results in a large number of queries per second (QPS).

This transformation toward high-concurrency user-facing analytics isn’t unique to Xiaohongshu or its industry. Across diverse sectors, from IoT services and health care to e-commerce and logistics, the appeal of democratized, real-time insights is universal.

The Challenge of High Concurrency

While high concurrency is becoming increasingly necessary, it poses significant challenges. Keep in mind that it’s not just about managing large query volumes. High concurrency is a critical component for ensuring real-time, accurate data representation for your end users.

What Makes High Concurrency Difficult with User-Facing Analytics?

The dominant challenge of user-facing analytics stems from OLAP-style queries. While there might not be a vast number of these queries when compared to simpler point queries, their inherent complexity compensates for their lower frequency.

Each OLAP-style query often spans multiple tables, involves several aggregations and filters through millions, if not billions, of records. The combinatorial nature of such queries, combined with the need for real-time results, places a considerable strain on the CPU.

Therefore, addressing high concurrency in user-facing analytics isn’t just about scaling infrastructure to accommodate more queries; it’s about optimizing the system to handle the computational heft of OLAP-style queries efficiently.

Handling High Concurrency OLAP CPU Intensive Workloads

The current strategies employed to manage these challenges are multifaceted. Understand that no single approach can universally address all concurrency issues, and that you should always choose a strategy that suits your scenario the best. With that in mind, let’s examine the options and their respective pros and cons.

Precomputation: Preaggregation and Denormalization

One prevalent strategy is precomputation, which involves preaggregating and denormalizing data to speed up query processing.

Pros:

• Anticipated query patterns: Precomputation is particularly beneficial when the query pattern is predictable. By understanding what kind of data will be frequently accessed, it’s possible to pre-process and store it in a manner that facilitates faster retrieval and analysis.

Cons:

• Foreknowledge required: The efficacy of precomputation hinges on the ability to determine query patterns in advance. This necessitates continuous analysis and adjustments as data access patterns evolve.
• Increased storage: Preaggregating and denormalizing data can lead to significantly increased storage requirements. While this ensures faster query execution, it might also mean that large portions of the pre-processed data are seldom used, leading to inefficiencies.

Query Result Caching

Query result caching is another technique to enhance query performance. By storing the (intermediate) results of frequently executed queries in a cache, subsequent requests for the same data can be swiftly fulfilled without the need to reprocess the entire query. Different from precomputation, this process is often automated.

Final Result Caching

Final result caching stores the final results of the executed queries on the disk to speed up subsequent identical query requests.

Pros:

• Simple to implement: Implementing caching mechanisms can be seamlessly integrated into the application layer, providing developers with the flexibility to control cache behavior based on specific application needs.
• Instant retrieval: When a user query matches a cached result (a cache hit), computation is entirely bypassed, resulting in almost-instant result retrieval.
• Prevents redundant requests: Final result caching is especially valuable in scenarios where users might repetitively request the same data, like refreshing a dashboard. By serving cached results, the system conserves computational resources.

Cons:

• Limited applicability: Final result caching is extremely sensitive to change. Even minor alterations in a query or a small change in the underlying data can result in a cache miss, necessitating a fresh computation. This makes caching less effective for dynamic datasets or applications with highly individualized user queries.

Intermediate Result Caching

Instead of solely focusing on the end result, this mechanism caches intermediate computations — like results from post-local aggregations — typically in memory. Even if the queries are not identical or the scanned data changes, part of the cached intermediate results can still be harnessed, partially bypassing the need for re-computation.

Pros:

• Useful for user-facing analytics: In user-facing analytics, queries are generated by a BI tool or front-end application. By following some templates, these queries typically share a similar structure, which is very suitable for intermediate result caching.

Cons:

• Not a panacea: It’s not a silver bullet. There will always be workloads such as large data transformation jobs or ad hoc queries that don’t benefit from this strategy, thereby requiring a combination of different approaches for optimization.

Moving Forward with User-Facing Analytics

Navigating OLAP high concurrency with user-facing analytics is like piecing together a complex puzzle where each piece represents a different layer of the solution. There’s no one-size-fits-all remedy; the dynamic nature of data interactions and user demands ensures that each scenario may warrant a unique approach or combination thereof. It’s essential to dig deep into your specific scenario, understand its nuances and consider multiple options. By combining various strategies tailored to your needs, you can craft an optimal solution that effectively manages concurrency while delivering a seamless user experience.