1-800-591-0442 | 24/7 Live Support Location | Careers | Contact Us

Using Amazon ElasticSearch to Improve Performance when Querying Data in MySQL

June 18 2018

Search tools have increasingly grown power and functionality over time and both users and companies have become more reliant on them to identify information and patterns quickly and efficiently. However, even the most robust search tool can experience issues when compiling information from large sets of data. This can be due to either complex relational database joins or the sheer volume of data a single query must parse through to identify the necessary information.

Every industry experiences this at some point in time and how each company chooses to address these issues depends largely on their philosophy. A recent client of ClearScale’s, an AWS Premier Consulting Partner, recognized the need to expand their search abilities but were very much aware of limitations that their existing MySQL database had. They asked ClearScale to provide for them the means to quickly query their data and return results to their users.

The Challenge

Because our client's data was so massive, they were experiencing issues being able to effectively return results because of the complex joins their MySQL database had. They needed a way to quickly access and return results using their RESTful API and asked ClearScale to develop a solution.

The solution also needed to offload the queries from the existing MySQL database to reduce the impact to performance. It needed to have a scalable architecture, so that as the size of the data grew and the reliance on the search queries that the overall solution would not be impacted adversely.

The Solution

In order to achieve success, ClearScale recognized that moving the data into Amazon ElasticSearch was the best solution. This would allow for a more scalable solution long term, but since the API queries needed to then use ElasticSearch for the results, there was no simple out-of-the-box solution readily available. To do so, would mean denormalizing documents stored in the ElasticSearch data store and updating them whenever the MySQL tables were updated.

Live Sync from MySQL to Amazon ElasticSearch Diagram alt

Although ClearScale was able to set up a dedicated indexing pipeline from the MySQL instances into ElasticSearch for the initial upload of the data, trying to keep it in-sync with the MySQL tables was the larger challenge. To do so, there were two possible approaches that could be taken, neither of which would really be feasible for long-term use. Option 1: an SQL-based approach, where a query is run and uploaded to ElasticSearch on a semi-frequent basis. This one is difficult to implement with incremental indexing. Or, option 2: using a replication log-based approach where the indexer listens for replication events that the MySQL database generates and then indexes those events. The latter approach could be partially successful, but the only data that could be uploaded would be from individual rows and not allow table joins; this means loss of rich data and ultimately is counter to the long-term vision of what the client was looking for.

ClearScale decided on implementing a hybrid-approach that adopted the best of both models. ElasticSearch documents were created from data obtained from MySQL queries, but the queries themselves were triggered based on events generated from the replication logs. The data was then sent over the dedicated indexing pipeline and an AWS ElasticSearch cluster was created to house the data.

ClearScale then modified the existing RESTful API to perform search queries against the ElasticSearch instance. Working closely with the client, ClearScale determined what search criteria would be available to consumers of the API. The resulting search service would group the search criteria into an equivalent ElasticSearch query and return the results directly to the client, thus bypassing the MySQL database and maintaining a high throughput of queries to results.

The Benefits

The approach that ClearScale adopted had immediate success. Because of the ElasticSearch cluster coupled with a MySQL RDS Replica used during the initial data import, the solution allowed for indexing rates of around 100 documents per second keeping performance relatively high. In addition, offloading the queries from the MySQL database onto the ElasticSearch solution improved the overall performance of the MySQL database which in turn allowed updates from the database to the ElasticSearch cluster to propagate in just a few seconds. Finally, having the search service directly interact with ElasticSearch to produce results from queries from clients meant a higher response rate overall within a scalable instance.

ClearScale has been devising creative big data solutions for complex client requirements since 2011. With the wealth of information our engineers, architects and solution designers have obtained through countless client interactions, ClearScale can find favorable outcomes that are scalable and dynamic enough to meet your current and future needs.

Get in touch today to speak with a Cloud expert and discuss how we can help:

Call us at 1-800-591-0442
Send us an email: sales@clearscale.net
Fill out a Contact Form
Read our Customer Case Studies

San Francisco

Headquarters

71 Stevenson St.

Suite 400

San Francisco, CA 94105

O: 1-800-591-0442

F: 1-415-655-6601

San Jose

5450 Thornwood Dr Suite #L

San Jose, CA 95123

Denver

1400 16th Street,

Suite 400

Denver, CO 80202

O: 1-720-932-8028

Phoenix

1910 S. Stapley Drive,

Suite 221

Mesa, AZ 85204

O: 1-480-386-5057

New York

165 Broadway, 23rd Floor

New York City, NY 10006

O: 1-646-759-3656

Toronto

100 King Street West

Suite 5600

Toronto, Ontario, M5X 1C9

O: 1-416-479-5447

© 2017 ClearScale, LLC. All Rights Reserved.    About Us  |  Careers  |  Privacy Policy
Share