Technology

September 13, 2024

Beyond SQL: Recognizing When to Transition to Elasticsearch for Enhanced Query Performance

The shift from MySQL to Elasticsearch boosts CRM performance, improving query speed, reducing CPU load & resolving scalability issues while cutting AWS Aurora costs.

Understanding the Context

Initially, the system relied heavily on a MySQL database to manage and store denormalized contact data for a CRM system. Cached in a table referred to as the "contacts table," this data supported contact information displayed on the CRM's main page. Frequent updates to the table, triggered by any additions, updates, or deletions, placed a significant load on the database. As a result, the system struggled to maintain consistency and scalability as it scaled.

The complexity of the MySQL setup became evident as both the volume of data and the frequency of operations grew. This strain manifested as performance degradation, with deadlocks becoming a recurring issue. Multiple processes attempting concurrent writes to the cache table caused operational bottlenecks, hampering efficiency. Additionally, frequent updates put immense pressure on the CPU, especially within the AWS Aurora cluster, further driving up operational costs.

At a certain point, the limitations of SQL became too pronounced to ignore. While SQL databases are robust and versatile, they are not always the optimal choice for every query or storage requirement—especially when dealing with vast amounts of denormalized data requiring fast, flexible indexing. It was under these circumstances that Elasticsearch (ES) emerged as a potential solution to address the performance and scalability challenges.

Planning the Transition to Elasticsearch

The goal of the transition was clear: improve system performance by migrating the denormalized data from the MySQL cached table to Elasticsearch. Renowned for its full-text search capabilities and scalability, Elasticsearch was an attractive option. Its architecture, designed for handling large datasets efficiently, promised to enhance query performance while reducing strain on the existing MySQL infrastructure.

To achieve this, the team planned to set up an Elasticsearch cluster via a cloud-based Elasticsearch service. The migration strategy involved transferring all denormalized data from MySQL to Elasticsearch while maintaining synchronization between the two systems. This required a robust data synchronization process to ensure any changes in the MySQL database would be promptly reflected in the Elasticsearch index. Maintaining data consistency between systems was paramount, particularly given the CRM's reliance on real-time contact data.

Key objectives of the transition included:

  1. Improved Query Performance: Elasticsearch’s architecture is optimized for rapid data retrieval, particularly in complex querying scenarios. The anticipated speed increase would result in a more responsive CRM interface.
  1. Reduced CPU Load: Offloading query and search operations to Elasticsearch was expected to substantially reduce the MySQL database's CPU load, driving down AWS Aurora costs and freeing up resources for other critical operations.
  1. Deadlock Mitigation: Elasticsearch's ability to handle concurrent operations efficiently would help alleviate the deadlock issues that plagued the MySQL setup, leading to smoother system performance.

Synchronization Strategies

Two synchronization options were evaluated for keeping Elasticsearch and MySQL in sync:

  1. Change Data Capture (CDC): By monitoring the MySQL binary log (binlog), CDC captures real-time changes and propagates them to Elasticsearch. While reliable, this method added significant complexity and was not deemed the most suitable for the system's architecture.
  1. Backend-Triggered Syncing: A more tailored solution, this approach leveraged existing Kafka infrastructure to track and synchronize changes post-CRUD operations. It provided greater flexibility and control while reducing system complexity compared to CDC.

Ultimately, the backend-triggered method was chosen, aligning with the system's architecture and allowing for a smoother transition.

Old Design vs. New Design

In the previous architecture, all contact data was stored and queried directly from MySQL’s cache table, which, while functional at the outset, became increasingly inefficient as the system scaled. The shift to Elasticsearch allowed the team to capitalize on its powerful search and indexing features, which improved query speed and offloaded significant workloads from MySQL. This reduced operational costs and enhanced overall system efficiency.

Old Design

New Design

Results

The migration to Elasticsearch yielded several measurable benefits:

  1. Query Speed Improvement: Query speeds improved by 50%, leading to a significantly more responsive CRM. Complex queries, such as filtering and sorting, saw considerable gains.
  1. Advanced Query Support: Elasticsearch enabled more sophisticated query capabilities, such as fuzzy searches, which enhanced the CRM’s search functionality beyond what MySQL could offer.
  1. Reduced Database Load: Offloading search operations from MySQL to Elasticsearch resulted in a 40% reduction in write IOPS, cutting AWS Aurora costs and improving performance for other database-dependent processes.
Challenges and Lessons Learned

Despite the success of the transition, several challenges were encountered. Adapting to Elasticsearch’s architecture, particularly for team members more familiar with SQL, required an initial learning curve. Concepts such as aggregations, filters, and fuzzy search were foreign, but training and hands-on experience helped bridge the knowledge gap.

The initial data synchronization process from MySQL to Elasticsearch also presented difficulties. Migrating large volumes of data efficiently was no small feat. To address this, the team implemented a Kafka-driven synchronization pipeline, ensuring consistent updates to Elasticsearch. Additionally, an on-demand sync feature was added to handle any potential discrepancies during synchronization.

Conclusion

The shift from MySQL to Elasticsearch delivered substantial improvements in performance, scalability, and cost efficiency. By recognizing SQL’s limitations and making a calculated move to Elasticsearch, the team effectively enhanced the CRM system’s capabilities. This transition not only resolved existing bottlenecks but also set the stage for future growth and scalability.

Case Study from Arkusnexus

3065 Beyer Blvd B-2
San Diego CA 92154 - 349
619-900-1164

[email protected]

mind hub tijuana