Database migration to a new database platform or technology can be daunting for various reasons. One of the common concerns is database performance. It is hard to evaluate database performance early in the evaluation cycle without performing actual data migration and application changes. This becomes even more important in the case of Cloud Spanner where modernization is required at both the database and application layers. 

The traditional approach to evaluate database performance is to deploy the application and database together, migrate historical data to the new database and then simulate load tests by running an application on the new database. Doing all this for an early performance evaluation of Cloud Spanner may feel like a lot of effort.

In this post, we will explore a middle ground to performance testing using JMeter. Performance test Cloud Spanner for a custom workload before making application code changes and executing data migration. More detailed step-by-step guide is published here.

Goals

• Estimate the number of Cloud Spanner nodes needed (and get a cost estimate).
• Performance test the most frequently used set of queries and transactions.
• Demonstrate the ability to scale horizontally.
• Better understand the optimizations needed for schema and sql queries.
• Determine latency of DML operations.

Limitations

• You cannot test using non-java client libraries.
• It is more complex to test non-jdbc compliant features like mutations, parallel reads etc.

Preparing for performance tests

1. Identify top SQL queries, latency, frequency / hour and avg number of rows returned or updated for each. This information will also serve as a baseline for the current system.
2. Determine Cloud Spanner region / multi-region deployment. Ideally, load should be generated from the Cloud Spanner instance’s leader region for minimum latency and best performance. Read Demystifying Cloud Spanner multi-region configurations for more details on various configurations of Cloud Spanner.
3. Estimate the number of Cloud Spanner nodes required for a given workload based on (step 1). It is recommended to have a minimum of 2 nodes for linear scaling.
   Note: Peak performance numbers of regional performance and multi regional performance are published. It is based on a 1KB single row transaction with no secondary indexes.
4. Request quota for Cloud Spanner nodes on a given region / multi-region. It can take up to 1 business day. 

Setting up Cloud Spanner

Creating the schema for Cloud Spanner

You can use the following tools to generate a schema for Cloud Spanner if you are migrating from the following source databases. Alternatively, you can model the schema manually. Schema design has a huge performance impact, hence it is recommended to review the schema very carefully.

Note: Manual review and tuning of schema will be needed to optimize and mitigate potential hotspots. You will need to keep in mind schema design best practices when modeling your schema.

Populating seed data into Cloud Spanner

Performance of a database depends on the amount of data present. Existing data (and indexes) determines how much data is scanned on select queries and therefore performance. Hence, it is important to seed data into Cloud Spanner before performance testing. 

For most realistic results, data should be migrated from an existing production source. Sometimes you cannot do that due to schema changes. One way is to utilize a custom ETL job to export and transform data and then import into Cloud Spanner. Another alternative could be to mock seed data using JMeter(More details in later sections).

JMeter performance tests

Writing Tests

JMeter will interact with Cloud Spanner just like your application. It will perform DML operations the same way as your application does. Hence, tests need to simulate production like transactions. For example if a transaction is made up of insert and/or update statements in several tables within transaction boundaries then JMeter should mimic the same.

JMeter has the following hierarchy:

Test Plan > Thread Group(s) > Sampler(s) (aka test)

Test plan is a top level component. It can contain global properties and libraries (like jdbc driver etc).

Thread Group(s) are representative of a database transaction. It contains one or more sampler(s). All the samplers within a thread group execute serially.

Sampler(s) should represent a single DML call. If your transaction needs to have multiple dml calls, you should create multiple samplers. Typically you will use JDBC Sampler for database calls. You can also use JSR 223 Sampler as described here, in case you need to use mutations or parallel reads etc. Results from one sampler can be passed to the next, as in real world application.

Executing tests and collecting results

JMeter tests should be executed as physically close to Cloud Spanner instances as possible to minimize network latency. Therefore it is best to execute them from Compute Engine (via private service connect) from the same region as the Cloud Spanner instance. In-case of multi region Cloud Spanner, GCE instances should be created in the Leader Region of the Cloud Spanner instance for lowest network latency.

JMeter should be executed via command line, it is a CPU intensive application so make sure you have allocated enough resources on the VM.

Refer to the Cloud Spanner monitoring dashboard to ensure that your Cloud Spanner instance’s CPU utilization is at or below the recommended values. In addition, use introspection tools to investigate performance issues with your database. 

You might need to optimize your queries (add indexes) and re-execute tests in multiple waves to tune the performance as needed.

Try it yourself

Follow a detailed step by step tutorial on Measure Cloud Spanner performance test using JMeter to test Cloud Spanner performance yourself.

Expectations for both consumer and commercial products have changed. Consumers want products that evolve with their needs, adapt to their preferences, and stay up-to-date over time. Manufacturers, in turn, need to create products that provide engaging customer experiences not only to better compete in the marketplace, but also to provide new monetization opportunities. 

However, embedding intelligence into new and existing products is challenging. Updating hardware is costly, and existing connected products do not have the capability to add new features. Furthermore, manufacturers do not have sufficient customer insights due to product telemetry and customer data silos, and may lack the AI expertise to quickly develop and deploy these features. 

That’s why today we’re launching Intelligent Products Essentials, a solution that allows manufacturers to rapidly deliver products that adapt to their owners, update features over-the-air using AI at the edge, and provide customer insights using analytics in the cloud. The solution is designed to assist manufacturers in their product development journeys—whether developing a new product or enhancing existing ones. 

With Intelligent Products Essentials, manufacturers can:

• Personalize customer experiences: Provide a compelling ownership experience that evolves over the lifetime of the product. For example, a chatbot that contextualizes responses based on product status and customer profile.
• Manage and update products over-the-air: Deploy updates to products in the field,  gather performance insights and evolve capabilities over time with monetization opportunities.
• Predict parts and service issues: Detect operating thresholds, anomalies and predict failures to proactively recommend service using AI, reducing warranty claims, decreasing parts shortages and increasing customer satisfaction.

In order to help manufacturers quickly deploy these use cases and many more, Intelligent Products Essentials provides the following:

• Edge connections: Connect and ingest raw or time-series product telemetry from various device platforms utilizing IoT Core or Pub/Sub and enable deployment and management of firmware over-the-air and machine learning models with Vertex AI at the edge.
• Ownership App Template: Easily build connected product companion apps that work on smartphones, tablets, and computers. Use a pre-built API and accompanying sample app that can incorporate product or device registration, identity management, and provide application behavior analytics using Firebase.
• Product fleet management: Manage, update and analyze fleets of connected products via APIs, Google Kubernetes Engine, and Looker.
• AI services: Create new features or capabilities for your products using AI and machine learning products such as DialogFlow, Vision AI, AutoML, all from Vertex AI.

Enterprise data integration: Integrate data sources such as Enterprise Asset Management (EAM), Enterprise Resource Planning (ERP), Customer Relationship Management (CRM) systems and others using Dataflow and BigQuery.

Intelligent Products Essentials helps manufacturers build new features across consumer, industrial, enterprise, and transportation products. Manufacturers can implement the solution in-house, or work with one of our certified solution integration partners like Quantifi and Softserve. 

“The focus on intelligent products that Google Cloud is deploying provides a digital option for manufacturers and users. At its heart, systems like Intelligent Product Essentials are all about decision making. IDC sees faster and more effective decision-making as the fundamental reason for the drive to digitize products and processes. It’s how you can make faster and more effective decisions to meet heightened customer expectations, generate faster cash flow, and better revenue realization,” said Kevin Prouty, Group Vice President at IDC. “Digital offerings like Google’s Intelligent Product Essentials potentially go the last mile with the ability to connect the digital thread all the way through to the final user.”

Customers adopting Intelligent Products Essentials

GE Appliances, a Haier company, are enhancing their appliances using new AI-powered intelligent features to enable:

• Intelligent cooking: Help cook the perfect meal to personal preferences, regardless of your expertise and abilities in the kitchen.
• Frictionless service: Build smart appliances that know when they need maintenance and make it simple to take action or schedule services.
• Integrated digital lifestyle: Make appliances useful at every step of the way by integrating them with digital lifestyle services – for example, automating appliance behaviors according to customer calendars, such as oven preheating or scheduling the dishwasher to run in the late evening.

“Intelligent Products Essentials enhances our smart appliances ecosystem, offering richer consumer habit insights. This enables us to develop and offer new features and experiences to integrate with their digital lifestyle.“ —Shawn Stover, Vice-president Smart Home Solutions at GE Appliances.https://www.youtube.com/embed/zaWAJN8aKOw?enablejsapi=1&

Serial 1, Powered by Harley-Davidson, is using Intelligent Product Essentials to manage and update its next generation eBicycles, and personalize its customers’ digital ownership experiences. 

“At Serial 1, we are dedicated to creating the easiest and most intuitive way to experience the fun, freedom, and adventure of riding a pedal-assist electric bicycle. Connectivity is a key component of delivering that mission, and working together to integrate Intelligent Product Essentials into our eBicycles will ensure that our customers enjoy the best possible user experience.”— Jason Huntsman, President, Serial 1. 

Magic Leap, an augmented reality pioneer with industry-leading hardware and software, is building field service solutions with Intelligent Products Essentials with the goal of connecting manufacturers, dealers, and customers to more proactive and intelligent service.

“We look forward to using Intelligent Products Essentials to enable us to rapidly integrate manufacturers’ product data with dealer service partners into our field service solution. We’re excited to partner with Google Cloud as we continue to push the boundaries of physical interaction with the digital world.” — Walter Delph, Chief Business Officer, Magic Leap

Intelligent Product Essentials is available today. To learn more, visit our website.

With Google Cloud, AirAsia is now a “data first” business that can capture, analyze, and report on rising volumes of data to address complex problems and increase revenue streams. The airline is also using AI-powered chatbots to streamline internal operations and provide a faster, more efficient service.

Google Cloud results

• Increases employee survey response rates by 30% and improved safety, scheduling, and employee orientation
• Enables movement towards a zero trust security model while reducing administration load on IT team
• Increases business agility by deploying faster and more frequently

Industries: Travel & Hospitality

Location: Malaysia

Become a digital airline powered by data and machine learning

AirAsia’s vision is simple: allow everyone to fly. Founded in 2001, the airline and sister company AirAsia X have grown to service 150+ destinations in 25 markets, using 274 aircraft to operate 11,000+ weekly flights from 23 hubs across the region. While the airline is known as a provider of low-cost airfares to locations across Asia, this is only one part of its value proposition. AirAsia also aims to deliver innovative, personalized products and services that meet the needs of each of its passengers.

Technology is key to AirAsia’s success and, with strong support from Group Chief Executive Officer Tony Fernandes, in 2016 the airline began a five-year program to become a data-first business and a digital airline.

“We wanted to better ensure we were using data correctly to become more agile, efficient, and customer oriented,” says Lye Kong Wei, Chief of Data Science, Group Head at AirAsia.

AirAsia needed technologies and services that could capture, process, analyze, and report on data, while delivering value for money and meeting its speed and availability requirements. The airline also wanted to minimize infrastructure management and system administration demands on its technology team.

Google Cloud the best fit

AirAsia realized only a cloud service could meet its needs and began evaluating the market. The airline then conducted a proof of concept and found Google Cloud was the best fit for its business. It was already familiar with Google Cloud, having deployed Google Workspace collaboration and productivity applications to its workforce in all countries except China. According to Kong Wei, products such as Forms, Docs, Sheets, and Gmail delivered a considerable improvement in collaboration between various departments, as well as streamlining and automating a range of processes.

The business was particularly excited by the potential of the BigQuery analytics data warehouse to power its digital transformation. “We knew data was a big part of making decisions in the future,” says Kong Wei. “So we needed a platform that could scale to meet our growing appetite for it. Google Cloud—in particular BigQuery—was ideal for this task.”

The AirAsia technology team was impressed by the ease and flexibility with which it could extract, transform, and load customer data from its systems, websites, and mobile applications into BigQuery for analysis. Data, reports, and dashboards were delivered and visualized through Looker Studio.

BigQuery also scaled seamlessly to support data growth and, as a managed service, required minimal administration from the airline’s technology team. “In addition, with BigQuery, we could process queries and requests much faster than previously and tackle more complex problems,” says Kong Wei. “As a result, we could be more innovative about realizing opportunities,” he adds, citing the benefits of being able to view and understand historical measures of booking curves—a measure of how long it took customers to book before a flight. This improves the airline’s ability to manage revenues.

A broad ecosystem

BigQuery and Looker Studio are just two components of a broad ecosystem—powered largely by Google Cloud services—deployed by AirAsia. Pub/Sub provides a scalable message queue that enables AirAsia developers to integrate systems hosted on Google Cloud or externally. Apache Airflow enables the business to create, schedule, and monitor workflows, while Cloud Composer manages the dependencies of PHP and related libraries. App Engine allows AirAsia personnel to develop and host web applications. “Thanks to App Engine, we’ve easily been able to create new applications, services, and APIs powered by the data we have been collecting,” says Kong Wei.

AirAsia is also running the middleware for its APIs in a managed Google Kubernetes Engine environment for increased scalability, resource optimization, and reliability, while Cloud Storage provides storage for data from a range of systems and sources. Dataflow enables the business to transform and process data in stream and batch modes from its website search page as customers look for flights.

Faster deployment and testing

The stability of Google Cloud service means AirAsia has a reliable base from which to launch new products and features. “If we have consistent reliability from our core systems—and Google Cloud incorporates monitoring tools such Cloud Monitoring that enable us to identify issues quickly—developers and product engineers can focus on turning ideas into reality,” says Kong Wei. “With a minimal number of people involved, we can very quickly transform an idea or thought process into a deliverable. Prior to Google Cloud, bringing those ideas to fruition would have been impossible.”

Robust security

AirAsia is also relying on Security Health Analytics, a product that integrates with Security Command Center, to identify misconfigurations and compliance violations in its Google Cloud resources and take action. Security Health Analytics ensures the airline’s budgets go to keeping customers’ travel costs low rather than recovering from security breaches.

The product enables AirAsia to check that resources are configured properly and are compliant with CIS benchmarks as its critical workloads run in Google Kubernetes Engine and App Engine.

“Being able to go to the new Security Health Analytics dashboard eliminates the guesswork of what we have running and if it is secure,” says Muhammad Faeez Bin Azmi, Information Security and Automation Solution Architect. “Now anyone on our team, even non-security professionals, can go to this dashboard and see a list of the misconfigured assets and compliance violations across all of our Google Cloud resources. We can also see the severity of misconfigurations, which helps us prioritize our response.”

“Security Health Analytics has really helped us reduce the amount of time we spend trying to figure out what’s wrong with our resources. It’s allowed us to use our time more effectively to identify and resolve more security issues than we could before.”

A new identity solution

AirAsia had also used a legacy on-premises directory for many years. However, as the company grew and expanded to new markets and regions, it had to manage multiple servers across a number of on-premises data centers and the public cloud, which proved costly and time-consuming.

Its Allstars—the airline’s name for its employees—needed to easily access a number of legacy on-premises apps in addition to a growing number of SaaS apps. As a business, it also needed a more seamless integration between its HR system of record and its identity solution for user provisioning and life cycle management. Solving these challenges with its existing on-premises directory was simply not feasible.

AirAsia brought up its identity concerns with the Google Cloud team, and after a number of conversations, decided to deploy Cloud Identity, Google’s cloud-based Identity and Access Management solution, to help address the identity challenges it was facing.

The airline chose Cloud Identity for a number of reasons—first, it was eager to move to the cloud as quickly as possible. Moving identity management to the cloud was a key enabler of this and the airline’s broader digital transformation. Managing identities from the cloud also enabled the airline to have a single identity and set of credentials for each employee, which they could use to access all the applications they need to be productive, both in the cloud and on-premises.

In addition, deploying Cloud Identity was a key step towards enabling the zero trust security model, which the airline felt was the best approach to strengthen its security posture and fight modern threats. Cloud Identity also integrated seamlessly with its existing technologies, which include not only Google Cloud products like Google Workspace and Chrome OS but also third-party tools like Citrix, Papercut, and others.

And finally, Cloud Identity offered significant cost and resource savings. With Cloud Identity in place, AirAsia’s IT department could spend less time worrying about managing multiple on-premises directory servers and and could instead focus on delivering value to Allstar employees.

Machine learning employed to increase ancillary revenue

In March 2018, AirAsia established the groundwork to use machine learning to optimize pricing for a range of services and began by using AI Platform to sort and predict demand for ancillary services such as baggage, seats, and meals. “By using AI Platform, we can sort based on data about history to predict the future,” says Kong Wei.

Dialogflow in wide use

With Google Cloud well established within the business, AirAsia is using Dialogflow, a voice and conversational interface development suite (and one of the core components of Contact Center AI) that enables businesses to create engaging AI powered voice- and text-based interfaces such as chatbots and voice apps—to streamline operations and reduce costs

“Tony Fernandes, our Group Chief Executive Officer, motivated us to create a range of metrics that would enable us to respond more quickly and efficiently to customers,” says Yuashini Vellasamy, Product Manager at AirAsia.

This resulted in the initial deployment of Dialogflow in AirAsia’s operational areas, from crew scheduling to internal business tasks.

With Dialogflow, AirAsia is able to provide pilots, crew, catering, and other teams with flight times, capacity and any other relevant information about their assignments. Another bot accepts medical certificates and updates from pilots and crew members unable to make rostered assignments and switches those assignments to other pilots and crews. “This improves our on-time performance and operational efficiency, as we are able to optimize the creation of crew schedules,” says Vellasamy.

AirAsia also uses Dialogflow to power a “safety bot” for airport ground staff. “We tried a lot of tools to encourage staff to advise us when they saw a safety issue—but employee usage was low,” says Vellasamy. “However, when we set up the safety bot, which asks users just four questions about an issue they have observed, we saw an increase in employee interaction, which helps ensure proper safety measures.

Beyond crew optimization and overall safety, AirAsia’s HR department in Asia uses a Dialogflow-powered bot to run a post-orientation engagement program for new employees. The bot follows up with employees about issues from parking to AirAsia’s onboarding buddy program, saving HR employees time from scheduling one-on-one meetings. Dialogflow also powered an employee satisfaction survey of 3,000 team members, resulting in a 30% increase in response rate compared to the previous year.

One of Dialogflow’s benefits is its language coverage, which the airline’s marketing team uses to target customers and prospects across a region with diverse languages and cultures. “For weekly campaigns and promotions, our sales and marketing teams simply update the campaign materials and the changes are reflected automatically in Dialogflow and consequently to users,” says Vellasamy. “This enables us to quickly scale and reach our customers globally.”

An upward trajectory

AirAsia is poised to reap further benefits from Google Cloud as its deployment matures. “On the data side, we’re more advanced, while on the application and programming side we have a long way to go,” says Kong Wei. “We’re on a trajectory upwards—we’re looking at a lot of new ideas and how to embrace and deploy them so we can further our data-first and digital airline agendas.”