Geo-tracking application migrates to managed ClickHouse

OverviewOverview

A company that runs a successful geo-tracking application for thousands of families in hundreds of countries around the world in multiple languages was looking to improve the performance and customer experience of their real-time location tracking solution.

Due to the vast number of personal geo type variables, the solution required fast processing and large amounts of encrypted private geo-data to be stored.

As it turned out, their setup was a mixed bag of issues related to performance, reliability, privacy, data security and scaling. It required a central architectural review.

Problems, challenges, and migration objectivesProblems, challenges, and migration objectives

1. User geo-data was stored in MySQL on a hybrid solution across cloud and on-premise setup.
2. The app hit the speed limit impacting the end-user experience.
3. Infrastructure management required significant time and effort for the entire development team.
4. The solution was unstable due to constant issues with storage performance.
5. As the client base grew real-time data capturing became very slow and disrupted user experience.
6. Because of the hybrid hosting of the solution and constant data storage performance issues, it was impossible to run complex queries or extract values from the available data in real-time.

The solutionThe solution

We reviewed the application’s business and non-functional requirements. Every part of the existing data management infrastructure was reviewed and accounted for to determine its place in the product architecture and possible future return on investment. As it turned out, on-premise storage maintenance and upgrades constituted over half of all infrastructure and hardware costs.

Full migration to the Managed ClickHouse was the way forward — this would avoid multiple design compromises associated with the legacy approach.

The benefitsThe benefits

AgilityAgility

Managed ClickHouse architecture and our advanced web console allow storage capacity and replication adjustments and configuration to align tightly with the business needs.

FlexibilityFlexibility

Our solution quickly reduced computational costs during the summer holiday season, when usage dropped and promptly scaled up when people started going back to work.

ResilienceResilience

After deployment to ClickHouse, the solution uptime increased close to 100%, maintenance costs decreased dramatically, and the engineering team was able to focus on developing new features.

Migration strategyMigration strategy

Infrastructure assessmentInfrastructure assessment

To assess the requirements for the new solution, we monitored performance metrics over 24-hour periods on average usage days.

This gave us a standard pattern we could use to create a reliable demand model for the service.

Cloud architecture planCloud architecture plan

Using the collated data, we designed the architectural concept for deployment on a production-ready ClickHouse cluster.

Our solution architecture reduced read-write latency, thus improving the service’s end-user experience.

Service testingService testing

The whole testing process was easy — the team quickly set up a cluster and used relevant use-cases from our documentation to set up all the necessary services and build the solution. Like any new client, they received trial period credits so they could focus on testing without wasting time on processing payments.

During the proof of concept, the team performed read-write load testing while working with our solution architects to figure out the optimal config for their current and future needs.

After a successful experience with ClickHouse, the testing team discovered new opportunities with the Visualization service to improve their internal product analytics flow.

Logging of user requests and application errorsLogging of user requests and application errors

Our customer success team developed Visualization-based use-cases for the team that gave them logging analysis tools for specific error codes.

By implementing this improved bug fixing and feature request flow, the team could streamline the application release schedule.

MonitoringMonitoring

We created detailed graphs for them, to get insights about cluster performance and user interaction with the team’s geo-data processing and delivery system.

Project deploymentProject deployment

Keeping in mind the hybrid nature of the initial project architecture, we developed a four-stage deployment sequence to ensure seamless migration that would have zero impact on end-user experience:

1. The team created an organization and project structure with clusters, transfers and Visualization dashboards from our web console.
2. While the new data started flowing into ClickHouse, the team created transfers for legacy data kept on the old on-premise storage while seamlessly accessible from their new cloud. However, the client decided to shorten this dual setup period due to the performance instability of the on-premise storage.
3. After a transitional period, the team used the data Transfer capabilities of our service to migrate all the relevant data from the on-premise storage into the cloud. It was as easy as a few mouse clicks for the team to execute this.
4. All the subsystems within the geo-data service were completely switched to working with the new cloud-based storage and monitoring solution.
5. The project migration took less than 2 weeks.

Key resultsKey results

The successful project provided some key learning experiences.

While we used our cloud expertise to provide a scalable infrastructure solution, our client learned new ways to use our services for reliable real-time data monitoring and logging:

• Three times cost reduction for data storage and processing. The team removed all the logistics and integration load requirements to perform maintenance and upgrades on their on-premise hardware, which meant they could eliminate a sizable segment of their monthly expenditure.
• Close to 100% service uptime. The team quickly created a replica of their production services that they could use in load testing. Their tests revealed poorly performing code paths that would have crashed their on-premise infrastructure under heavy load periods.
• Resource scalability is now available 24/7 and in near real-time from an easy to use web-based management console. The team realized they couldn’t afford any downtime or the delays they faced from their bare metal on-prem implementation. The lack of downtime alone underlined the strategic importance of cloud-based storage for their business. As their cloud infrastructure scales automatically, it helps them avoid these issues and keeps their customers happy.
• The new data processing solution on Managed ClickHouse decreased latency to sub-seconds improving the end-user experience. The team took a highly strategic approach to their cloud migration definition and architectural reviews. This architecture ensures their cloud migration solution helps them achieve all their customer experience goals and allows them to improve their geo-location based services.

Reduced maintenance effort allows the team to focus on feature development

Managed ClickHouse solution takes care of provisioning & scalability, backups, networking, patching, and balancing needs. The team can now focus on productive product development rather than wasting time on managing infrastructure.