When you’re serving customers online, speed is essential for a good experience. As the amount of data in a database grows, queries that used to be fast can slow down.

For example, if a query has to scan every row because a table is missing an index, response times that were acceptable with a thousand rows can turn into multiple seconds of waiting once you have a million rows.

If this query is executed every time a user loads your web page, their browsing experience will slow to a crawl, causing user frustration. Slow queries can also impact automated jobs, causing them to time out before completion.

If there are too many of these slow queries executing at once, the database can even run out of connections, causing all new queries, slow or fast, to fail. 

The popular open-source databases MySQL and Google Cloud Platform‘s fully managed version, Cloud SQL for MySQL, include a feature to log slow queries, letting you find the cause, then optimize for better performance.

However, developers and database administrators typically only access this slow query log reactively, after users have seen the effects and escalated the performance degradation.

With Stackdriver Logging and Monitoring, you can stay ahead of the curve for database performance with automatic alerts when query latency goes over the threshold, and a monitoring dashboard that lets you quickly pinpoint the specific queries causing the slowdown.

To get started, import MySQL’s slow query log into Stackdriver Logging.

Once the logs are in Stackdriver, it’s straightforward to set up logs-based metrics that can both count the number of slow queries over time, which is useful for setting up appropriate alerts, and also provide breakdowns by slow SQL statement, allowing speedy troubleshooting.

What’s more, this approach works equally well for managed databases in Cloud SQL for MySQL and for self-managed MySQL databases hosted on Compute Engine. 

For a step-by-step tutorial to set up slow query monitoring, check out Monitoring slow queries in MySQL with Stackdriver. For more ideas about what else you can accomplish with Stackdriver Logging, check out Design patterns for exporting Stackdriver Logging.

In the United Kingdom, the popularity of online grocery shopping is expected to surge from about 6% of the market today to 9% by 2021, according to market research firm Mintel. One of the pioneers of online-only grocery retailing is Ocado, based in Hatfield, Hertfordshire in the U.K. Since starting commercial deliveries in 2002, the company has grown to 600,000 active customers, 260,000 weekly orders, and £1.39 billion in annual revenue.

Ocado takes supermarket trips out of the equation by enabling shoppers to purchase items online through its convenient web and mobile applications. Items are then picked and packed in automated warehouses and shipped directly to customers in a one-hour time slot of their choosing. Ocado’s delivery punctuality is 95%, order accuracy is 99%, and its service footprint now reaches more than 70% of the U.K. population.

“Google Cloud Platform gives us the flexibility and performance to tackle the large and complex data challenges unique to our business.”

—Paul Clarke, Chief Technology Officer, Ocado

The company achieved its success by building in-house almost all the technology and automation that powers its end-to-end e-commerce, fulfillment, and logistics platform. Ocado also developed a new platform, the Ocado Smart Platform (OSP), which offers large brick-and-mortar grocery retailers around the world access to a best-in-class solution for online grocery.

Democratizing machine learning

The shopping journey for online grocery retailing differs significantly from other e-businesses. Customers often buy dozens of products at once, a single household may have multiple buyers using multiple devices, and product shelf life may only be a couple of days.

“We often say that having built an end-to-end platform that can do online grocery scalably and profitably, we can do other forms of online retail; but the reverse does not necessarily follow,” says Paul Clarke, Chief Technology Officer at Ocado. “Google Cloud Platform gives us the flexibility and performance to tackle the large and complex data challenges unique to our business.”

The Ocado business model takes advantage of consumers’ shifting preferences and the links between digital technology and shopping experiences.

“Google Cloud Machine Learning Engine gives us the agility we need. Our developers were able to try out TensorFlow and see firsthand the benefits of machine learning in the cloud.”

—Paul Clarke, Chief Technology Officer, Ocado

The company has been building machine learning into its systems for over five years. Until recently, Ocado machine learning applications required specialist data scientists, typically with PhDs in machine learning, who would build these solutions from the ground up. It also required the specialist who set up the system and costly on-premises infrastructure to train and run these systems.

However, working with Google as a private alpha testing site for Google Cloud Machine Learning Engine accelerated its adoption of artificial intelligence (AI).

“We’ve been talking about how the cloud could democratize AI for some time,” says Paul. “Google Cloud Machine Learning Engine gives us the agility we need. Our developers were able to try out TensorFlow and see firsthand the benefits of machine learning in the cloud.”

TensorFlow is an open source software library for machine learning developed by the Google Brain team. Ocado developers, engineers, and data scientists now use TensorFlow for many of their machine learning projects. They deploy the models they build on Google Cloud Machine Learning Engine, which lets them train models faster across servers, desktop computers, and mobile devices through a single application program interface (API). Additionally, Google Cloud Machine Learning Engine integrates easily with the other Google Cloud Platform products used widely at Ocado.

What do customers really want?

One of the first TensorFlow models Ocado created was a machine learning algorithm that tags and categorizes customer emails and then prioritizes them for response.

The contact center receives thousands of emails each day and Ocado wanted to automate determining which ones needed to be answered immediately and which ones could wait.

For example, a first-time customer expressing their delight in using Ocado doesn’t need to be responded to with the same urgency as a customer who is missing an item from their order or who won’t be home to receive the delivery.

“Enabling agents to respond without having to sort through less-urgent emails improves Ocado’s responsiveness and customer service.”

—James Donkin, General Manager, Ocado

“We get a lot of emails from customers saying, ‘Our service was great,’ or ‘The driver was very courteous,'” says James Donkin, General Manager, Ocado. “But when issues like weather or road conditions potentially affect delivery, we often get surges of urgent questions. Enabling agents to respond without having to sort through less-urgent emails improves Ocado’s responsiveness and customer service.”

Using Google Cloud Machine Learning Engine, TensorFlow, and a large data set culled from several years’ worth of manually categorized customer emails, Ocado experimented on which kind of neural network architecture would best prioritize emails. After testing its models, Ocado implemented the highest-performing one and has been able to respond to urgent messages four times faster. The company also discovered that 7% of its emails don’t require a response at all, which means call center representatives now have more time to devote to higher priority messages.

“Without Google Cloud Machine Learning Engine, it would have been a lot harder to succeed on a project like email classification,” says Roland Plaszowski, who has recently managed several big data projects and initiatives at Ocado.

“Even if we invested significantly in infrastructure, it would be difficult to manage because of the computational intensity. It’s challenging and expensive to run machine learning projects at the same time without infrastructure that you can scale easily.”

Ocado also uses machine learning to predict customer behavior and improve experiences. By analyzing order data, Ocado makes shopping as frictionless as possible. For example, the ordering system can pre-populate customers’ shopping carts with items they are most likely to purchase, remind customers about items they may have forgotten, and notify them of multi-buy offers they haven’t completed, for example, only buying one of a buy one, get one free offer. Based on machine learning from previous purchase data, the Ocado system can also offer new products that are likely to delight customers.

“You will regularly see items that are more personally relevant to you instead of items that are being promoted more generally,” says James. “I’m a vegetarian, so I’m offered specials for vegetarian products that I normally buy and new ones that I’ve never bought. I’m also less likely to see things that I’m not interested in.”

Machines and machine learning

Within the Internet of Things (IoT), Ocado is looking to enhance its warehouse robots with machine learning. An integral part of the OSP, thousands of robots continually stream data into Google Cloud Storage and Google BigQuery.

Ocado data scientists apply machine learning to create a type of swarm intelligence that enables warehouse robots to work cooperatively to achieve a common goal. Projects include modules to search robot telemetry data, such as whether a battery pack is operating within standard tolerances or whether firmware has been successfully loaded, and use it to optimize maintenance schedules or detect patterns in wear and tear.

“Another challenge we’re looking at is how to embed machine learning directly into robots so they become smarter in terms of self-testing, exception handling, and error recovery,” says Paul. “This is a challenging combination of IoT, data analytics, and machine learning that we believe Google BigQuery and Google Cloud Machine Learning are particularly well suited to helping Ocado achieve.”

Scaling for new business

Scalability is also a major reason behind some of Ocado’s cloud initiatives, including the migration of all its on-premises data to the cloud. Ocado wanted to improve customer experiences, empower business teams with greater insight, and reduce IT overhead, so it consolidated onto Google Cloud Platform.

“The old databases just weren’t fast enough,” says Paul. “We needed a solution that could scale with the amount of data we generate and how we use it. Google Cloud Storage and Google BigQuery now provide the backbone, from a data point of view, for the Ocado Smart Platform.”

Ocado estimates its business, product, and transaction data is approaching two petabytes. Combining customer and supply chain data helps both internal Ocado operations and the company’s ambitions to commercialize OSP.

“When compared with other options for expansion internationally, selling OSP as a managed service lets us turn companies that could have been competitors into customers,” says Paul. “We want to build OSP once and then turn it on for multiple business-to-business customers.”

Each time Ocado adds a new hosting customer to OSP, it will launch a customized instance to fit that customer’s requirements. The capacity and performance of each new OSP instance must be able to scale quickly as the backend platform for established retailers with large numbers of products, customers, and transactions.

Ocado’s first OSP customer, Morrisons, is already benefiting from this first-of-a kind solution. Morrisons is one of the UK’s four largest supermarkets and uses OSP to power its online retail business. Using Google Cloud Platform, Ocado has stored, processed, and analyzed terabytes of Morrisons’ data using a dedicated data lake and Google BigQuery.

In addition to using Google Cloud Platform for OSP, Ocado also adopted it for its own online grocery retail business operation. Ocado originally used the Apache Spark and Apache Hadoop open-source frameworks on Google Compute Engine for its data platform. Moving to Google BigQuery frees Ocado business analysts from the complex query setup and workflows associated with Spark and Hadoop. Plus, it lets Ocado share data analytics with suppliers and partners.

Google BigQuery is well integrated with TensorFlow on Google Cloud Machine Learning Engine and Google Cloud Dataproc, the Apache Spark and Apache Hadoop service that lets Ocado use open source data tools for batch processing, querying, streaming, and machine learning. Google Cloud Dataflow and Google Cloud Dataproc handle cluster management, and provide an easy-to-use framework so developers can spend less time and money on administration and more time on delivering valuable business features.

Switching from Hadoop to Google BigQuery revealed a series of cost and performance improvements. For example, Ocado no longer needed to decide how many instances to bring up in a cluster or wait for the instances to spin up. Google handled everything.

“We simply ran our queries and paid for the resources that we use,” adds Roland. “One big win with Google BigQuery is we don’t have to do maintenance. Best of all, we saw Google BigQuery outperform our Hadoop cluster by over 80 times on our largest dataset, and for only two-thirds the cost.”

Editor’s note: We’re hearing here how martech provider Wunderkind easily met the scaling demands of their growing customer base on multiple use cases with Cloud Bigtable and other Google Cloud data solutions.

Wunderkind is a performance marketing channel and we mostly have two kinds of customers: online retailers, and publishers like Gizmodo Media Group, Reader’s Digest, The New York Post and more. We help  retailers boost their e-commerce revenue through real-time messaging solutions designed for email, SMS, onsite, and advertising. Brands want to provide a one-to-one experience to more of their customers, and we use our extensive history with best practices in email marketing and technology to help brands reach more customers through targeted messaging and personalized shopping experiences. With  publishers, it’s a different value proposition, we use the same platform to provide a non disruptive and personalized ad experience for their website. For example, if you are on their site and then you left, we might show an ad tailored to you when you come back later – depending on the campaign. 

After running into limitations with our legacy database system, we turned to Cloud Bigtable and Google Cloud, which helped us be more flexible and easily scale for high traffic demand – which can be a stable 40,000 requests per second, and meet the needs of our growing number of data use cases. 

Three different databases power our core product

In our core offering, companies send us user events from their websites. We store these events and later decide (using our secret sauce) if and how to reach out to those users on behalf of our customers. Because many of our customers are retailers, Black Friday and Cyber Monday are big traffic days for us as. On such days, we can get 31 billion events, sometimes as many as  200K events per second. We show 1.6 billion impressions that have seen close to 1 billion pageviews. And at the end of all this, we securely send about 100 million emails. We noticed the same thing for election time; traffic reached the same high volume. We need scalable solutions to support this level of traffic as well as the elasticity to let us pay only for what we use, and that’s where Google Cloud comes in.

So how does this work? Our externally facing APIs, which are running on Google Kubernetes Engine, receive those user events—up to hundreds of thousand per second. All the components in our architecture need to be able to handle this demand. So from our APIs, those events go to Pub/Sub, Dataflow and from there they are written to Bigtable and BigQuery, Google Cloud’s serverless, and highly scalable data warehouse. This business user activity data underpins almost all our products. Events can be things like product views or additions to shopping carts. When we store this data in Bigtable, we use a combination of email address and the customer ID as the Bigtable key and we record the event details in that record. 

What do we do with this information next? It’s important to mention that we also mark the last time we received an event about a user in Memorystore for Redis, Google Cloud’s fully managed Redis service. This is important because we have another service that is periodically checking Memorystore for users that have not been active for a campaign-specific period of time (it can be 30 minutes, for example), then deciding whether to reach out to them.

How we decide when we reach out is an intelligent part of our product offering, based on the channel, message, product, etc. When we do reach out, we use Memorystore for Redis as a rate limiter or token bucket. In order not to overwhelm the email or texting providers we send API requests to, we throttle those requests using Memorystore. (We prefer to preemptively throttle the outgoing API requests as opposed to handling errors later.)

When we do reach out, often we will need details for a specific product—let’s say if the website belongs to a retailer. We usually get that information from the retailer through various channels and we store product information in Cloud SQL for MySQL. We pull that information when we need to send an email with product information, and we use Memorystore for Redis to cache that information, since many of the products are repeatedly called. Our Cloud SQL instance has 16 vCPUs, 60GBs of memory and 0.5TB of disk space and when we perform those product information updates, we have about a thousand write transactions per second. We are also in the process of migrating some tables from a self managed MySQL instance, and we keep those tables synchronized with Cloud SQL using Datastream. 

Our user history database was originally stored in AWS DynamoDB, but we were running into problems with how they structured the data, and we’d often get hot shards but with no way to determine how or why. That led to our decision to migrate to Bigtable. We set up the migration first by writing the data to two locations from Pub/Sub, performed some backfill of data until that was up and running, and then started working on the reading. We performed this over a few short months, then switched everything to Bigtable. 

So, as mentioned, we are using Bigtable for multiple databases. The instance that stores our user events has about 30 TB with about 50 nodes.

Profile management

A second use case for Bigtable is for user profile management, where we track, for example, user attributes based on subscription activity, whether they’ve opted in or out of various lists, and where we apply list-specific rules that determine which targeted emails we send out to users. 

Our very own URL shortener

Our third use case for Bigtable is our URL shortener. When our customers build out campaigns and choose a URL, we append tracking information to the query string of the URLs and they become long. Many times, we are sending them via SMS texts, so the URLs need to be short. We originally used an external solution, but made the determination that they couldn’t support our future demands. Our calls tend to be very bursty in nature, and we needed to plan for a future state of supporting higher throughput. We use a separate table in Bigtable for this shortened URL. We generate the short slug that is 62 bit-encoded and use it as the rowkey. We use the long slug as a Protobuf-encoded data structure in one of the row cells and we also have a cell for counting how many times it was used. We use Bigtable’s atomic increment to increase that counter to track how many times the short slug was used. 

When the user receives a text message on their phone, they click the short URL, which goes through to us, and we expand it to the long slug (from Bigtable) and redirect them to the appropriate site location. Obviously, for the URL shortener use case, we need to make the conversion very quickly. Bigtable’s low latency helps us meet that demand and we can scale it up to meet higher throughput demands.

Meeting the future with Google Cloud

Our business has grown considerably, and as we keep signing up new clients, we need to scale up accordingly, and Bigtable has met our scaling demands easily. With Bigtable and other Google Cloud products powering our data architecture, we’ve met the demand of incredibly high traffic days in the last year, including Black Friday and Cyber Monday. Traffic for these events went much higher than expected, and Bigtable was there, helping us easily scale on demand. 

We are working on leveraging a more cloud native approach and using Google Cloud managed services like GKE, Dataflow, pub/sub, Cloud SQL , Memorystore, BigQuery and more. Google has those 1st party products and we don’t see the value in rolling out or self managing such solutions ourselves..

Thanks to Google Cloud, we now have reliable and flexible data solutions that will help us meet the needs of our growing customer base, and delight their users with fast, responsive, personalized shopping messaging and experiences. 

Learn more about Wunderkind and Cloud Bigtable. Or check out our recent blog exploring the differences between Bigtable and BigQuery.

Editor’s note: Home-goods and furniture giant Wayfair first partnered up with Google Cloud to transform and scale its storefront—work that proved its value many times over during the unprecedented surge in ecommerce traffic during 2020. Today, we hear how BigQuery’s performance and cost optimization have transformed the company’s internal analytics to create an environment of “yes”. 

At Wayfair, we have several unique challenges relating to the scale of our product catalog, our global delivery network, and our position in a multi-sided marketplace that supports both our customers and suppliers. To give you a sense of our scale we have a team of more than 3,000 engineers with tens of millions of customers.  We supply more than 20 Million items using more than 16,000 supplier partners.

Serving those constituents generates a lot of analytics work.  Wayfair runs over a billion ‘analytic’ database queries a year, from both humans and systems, against multi-petabyte datasets. Scaling our previous environment to meet these challenges required us to maintain dozens of copies of data for different use cases and manage complex data synchronization routines to move trillions of records each day, resulting in long development times and high support costs for our analytics projects. 

Centralizing our data using Cloud Storage and BigQuery enabled us to break down existing silos and build unified pipelines for our batch and real-time operations. BigQuery shines by letting us decouple our compute and storage resources and flexibly ingest structured data in streaming and batch modes. We also benefit from the same decoupling for more complicated machine learning (ML) workflows in Dataproc and Cloud Storage. In particular, BigQuery is extremely low maintenance. From ML and easy data integrations to the integrated query cache, many out-of-the-box features have proven valuable for multiple use cases.

Beyond storage and compute, Google Cloud offers multiple tools to maximize the value of our data. For example, we can easily connect Data Studio to BigQuery for lightning-fast dashboarding of enterprise KPI reporting. When we need to dive deeper into a metric, Looker provides an interface and semantic model that empowers more business users to answer important questions — without understanding SQL or our vast dataset and table structure. 

The combination of all of Google Cloud’s tools enables every Wayfairian the opportunity to self-serve their data visualization and analysis needs. We are able to support all of the requirements of our internal and external users, from descriptive analytics to alerting and ML, in a platform that blends Google’s proprietary technology with open-source standards.we have seen a greater than 90% reduction in the number of analytic queries in production that take more than one minute to run

As a result, we have seen a greater than 90% reduction in the number of analytic queries in production that take more than one minute to run, which delights our users and increases adoption of our analytics tools across the business.

Higher performance means saying “Yes” more often

We went into the BigQuery experience expecting very strong performance on large, aggregate queries and excellent scalability — the kind of performance that could enable consistent 5-15 minute processing queries and 5-10 second response times for large analytics queries. 

We weren’t disappointed — we saw slightly better than linear performance profiles as record counts went up from millions to trillions for a flat resource allocation. BigQuery had the expected high performance for aggregate queries. This made our large data processing pipelines predictable and straightforward to maintain and optimize. 

Below is a plot of gigabytes processed by a query against query run time in seconds; outliers are filtered and the axes are pruned to ranges where we had a large sample size. This focuses on longer-duration queries. As an example of the linear performance, a 2 terabyte query averages 5 minutes — 2.5min/terabyte — and an 18 terabyte query (9 times as much data) averages 25s — only 1.4min/terabyte. This is despite the fact that complex queries [sorting and analytic functions are particularly problematic] are more common with large data sizes. We do see increasing variability at large sizes as slot limits and other resource constraints come into play, but these factors are controllable and ultimately a business optimization decision.

Though individual values vary greatly depending on query complexity [analytic functions/sorting can be computationally expensive], the overall trend line is below 1-1 for bytes versus duration, meaning our BigQuery query runtimes are increasing slower than our data volumes. We are still seeing sub 1:1 threshold runtimes, and we’re already at 100 PB of total data with single queries running on 50PB or more. Going strong!

One unexpected and pleasant surprise has been high performance for smaller queries, such as those required for development, interactive analytics, and internal applications we use for forecasting, segmentation, and other latency-sensitive data workflows. BigQuery’s resource management systems have provided smoother degradation and parallelism profiles than we initially expected for a multi-tenant platform. 

We regularly see sub-second p50 query performance for interactive use cases, with p90 performance coming in under 10 seconds for Looker, Data Studio, and AtScale — meeting the threshold we set to avoid analysts losing focus during a business investigation. The chart below shows p50, p90, and p95 timing for core workloads, as well as the number of queries in each bucket and the number of unique users. [Looker and Atscale will use small numbers of service accounts for a large number of users].

This has enabled two important shifts in the way our customers experience analytics services:

1. Delivering better user productivity. People no longer have long wait times to get results for a basic query delivering an improved overall user experience.
2. Scaling our data transformation. BigQuery has proven, effective data transformation that allows us to embrace the industry shift away towards ELT (extract, load, transform) to transform raw data right within the database. 

We no longer have to ask ourselves, “Can we meet the SLA for this data processing task?” The new conversation is, “Of course, we can meet it. What’s the associated cost based on how many resources we assign?” We can make cost and resource trade-offs clear to our users and say “yes” more often to requirements that have measurable business value. 

Supporting cost vs. performance decisions

Democratized access to internal analytics is a powerful decision-making tool. With BigQuery’s robust tooling, the decision about how to best allocate computational resources is in the hands of the analytics groups closest to the business decision being made. If they want to spend a sprint optimizing a dashboard, they can show exactly how much money it will save. If they choose not to optimize a report, they can offset the decision with concrete cost savings and other realized value. 

Wayfair teams also benefit from visibility into their committed costs by abstracting away the details of the underlying services, including BigQuery slots, the virtual CPUs used to execute SQL queries. 

Take a look at this example view consumed by our teams (the metrics for these dashboards are from the BigQuery INFORMATION_SCHEMA (jobs and reservations tables):

Suppose an organization wants to purchase capacity for a period of time. In that case, we provide an average cost for a slot during that time and charge them based on their slot usage for queries, multiplied by the average price. 

Teams can see whether their consumption matches their reservations with a consistent, high-level number. We apply the same approach to tools like Dataproc by aggregating up the costs of component services and providing an organizational cost for those services.

How we use performance signals to improve user efficiency

Instead of overcorrecting for occasional performance fluctuations by allocating excessive resources, we try to align incentives so that people make the right choices for the business. Showback doesn’t work in a vacuum — it requires context and integrated insights. Teams need to show the cost of running a dashboard as well as the ROI is in terms of end user engagement and what peer dashboards with equivalent source datasets might cost. 

We want to give users the freedom to do things the way they want and need to while also educating them on achieving their goals and using best practices to reduce their costs and improve their performance. The goal is never for all dashboards to load “less than x seconds” — we believe that any dashboard can load in x seconds when it is properly designed and resourced. 

For instance, BigQuery allocates slots for a new workload quickly and efficiently, and queries that are optimized and have dedicated resources consistently complete quickly. When users experience a slowdown, it’s typically because they are sharing resources or inefficiently using their resources. Variable performance is a useful signal to help users discover opportunities to optimize queries and work more productively. We value these signals as we like to focus on the aggregate experience of all users interacting with the tool, not individual interaction. 

A natural way to improve user efficiency is to reward ideal behaviors and discourage those that do not align with our analytics strategy. Some examples include splitting out high-demand reporting from ad-hoc exploration to reduce concurrency errors or encouraging people to use materialized tables instead of writing complex custom SQL. For instance, a dedicated reporting project might only include an organization’s Data Studio dashboard if it directly references a table or meets other optimization requirements. Using this approach, we give our users a high degree of freedom and experimentation at the entry-level while providing a well-documented path to scale. 

Visibility into query performance improves business performance

The detailed tracking information we get from Google Cloud products is critical — Google’s robust analytics performance has enabled us to scale Data Studio to over 15,000 Wayfarians. Of course, a larger user base always comes with concerns that less sophisticated users may not appropriately optimize their queries or make the best use of the infrastructure. 

Evaluating the different Data Studio dashboards helps us identify opportunities to use better query optimization practices, such as reducing custom queries, connecting directly to permanent tables, or using the BI Engine to improve dashboard performance. Recently, our team built new billing projects specific for Data Studio reporting. We can scale up a reservation dedicated to reporting about a major sales event in minutes.

Beyond everyday reporting, holidays and sales events may require higher, guaranteed performance. BigQuery’s flexible capacity commitments allow us to decide at a business level whether an urgent need requires us to reprioritize resources immediately or whether we have the time to optimize queries and rebuild dashboards to be performant. These new billing projects let us evaluate if teams need to scale up resourcing, what areas they can optimize in BigQuery, or if a new reservation is needed to resource that team separately. 

Recommendations for achieving high performance at low cost

Are you wondering how to allocate BigQuery slots? Teams must see direct, immediate return on their investment in performance and optimization. 

Our first rule of thumb is to align resource utilization with ownership as closely as possible. This makes it easier for teams to secure dedicated resources, even if they start out small. We’re comfortable doing this because BigQuery dynamically reassigns capacity between the targeted reservation and other consumers in milliseconds. We always get the total usage of our slots — there is no wasted capacity even when we break assignments down granularly.

Once a team has a reservation, we provide reporting that consumes various BigQuery information schema views, such as query logs and reservation information, and processes it into aggregated reporting. We look for metrics like the average slots available to a query at a point in time, overall query performance, and how long queries took to execute to pinpoint the overall health of a reservation against our business objectives. Flex slots also let us experiment easily with additional capacity, and BigQuery’s performance is so consistent that we can use straightforward models to estimate return on additional slot investment. 

Other key cost optimization tips we have found helpful at Wayfair include: 

• Optimize for worst-case performance. Given BigQuery’s ability to share slots, we expect teams will often see much better performance, but we find that thinking it’s more effective when teams think about the most pessimistic case when resourcing their jobs.
• Take advantage of BI Engine reservations. We like to think critically about the data and workload. BI Engine reservations are excellent for workloads that query lots of small dimension tables or a few larger tables loaded into memory. In cases like that, such as with OLAP tools using pre-computed aggregates, we’ve seen BI engine investments drive a 25% reduction in average query time—excellent ROI.
• Leverage Looker for large, longitudinal datasets. Since Looker supports a wide range of use cases, we see a broader range of performance — p50 is still under a second, but p95 can get as high as a minute. For these cases, we recommend modeling user patterns [first of month, first day of week] and dynamically allocating more slots for these periods while deprioritizing batch workloads in the key windows. 
• Lean in heavily on the BQ cache for broad-based reporting. To avoid a Monday morning reporting lag, We use the BigQuery cache to avoid Monday morning report loading lags. Up to 30% of our Data Studio queries hit the BQ cache, ensuring our Data Studio p95 consistently stays under 10 seconds. 
• Use slots-per-job to improve experience. In our reporting, we find that the most valuable view is slots-per-job. We look for dips in slots available for jobs as this situation often correlates strongly with poor user experiences. For example, our reporting showed us that Looker has the highest volumes of queries and users on Mondays and during each month’s first two business days. We used this information to scale up slots to support these periods of highest demand, delivering on performance SLAs to support business users.

• Get a global picture of usage. We manage slots in a central tool. Teams input their requirements into a calendar to indicate when they want higher or lower capacity to inform our flex slot purchases and annual commitments. It might seem like dozens of relatively inconsistent workloads at the team level, but the picture changes when you look at demand across a week (or our entire business). Understanding global usage makes it easy for us to model baseline capacity recommendations for serving year slots versus flexible demands. 

Reaping the rewards of BigQuery

From our first initial undertaking with Google Cloud to transform our storefront, we had a lot of confidence in the underlying technology offerings and the teams that ran them and their ability to meet our unique requirements. But our experience using BigQuery for internal analytics at Wayfair has exceeded our already high expectations. 

BigQuery’s fast, dynamic allocation of resources and ability to transform data in place are important to our mission of maintaining consistently high performance for our users while closely managing costs. Moreover, our visibility into query and dashboard performance through BigQuery’s DSP views and other performance metrics have helped us make clearer connections between performance goals and the costs required to meet them — and guide our analysts across the company towards attaining high performance on every project.

Engineers that build last-mile delivery services belong to an elite order, a hallowed subcategory. Delivery customers are incredibly demanding when it comes to speed and convenience, and the services they use must take variables like increased traffic, road conditions, human error, and even driver availability into account every day.

Nuro is a company with a new approach to delivery services. Nuro has a fleet of autonomous vehicles designed to address many of the problems related to last-mile delivery. And every day, these vehicles — and their sensors — generate a lot of data before parking for the night. For Nuro engineers, that data can help them understand the impact of new on-road features, make improvements to their vehicles’ software, and ensure even better deliveries for their customers.

For Nuro, the key challenge is how to move petabytes of data as quickly, securely, and easily as possible from their edge environments, like vehicle depots, to Google’s Cloud Storage. For this delivery effort, Nuro selected Google’s Transfer Appliance with its new online transfer capability, now generally available.

Helping Nuro to speed up data delivery from the edge to the cloud

Like many Google Cloud customers, Nuro collects data from remote environments, like vehicle depots, that have different networking and storage capabilities when compared to a traditional data center. For a transfer solution to be effective moving unstructured data from these environments to the cloud, the solution needs to be easy to deploy and automate, while still providing similar performance as a more complicated alternative.

The Transfer Appliance was built for this use case. It arrives to customers as a physical appliance with a preconfigured version of Google’s Storage Transfer Service software already installed. Customers can move files to the appliance by using SFTP or SCP, or, alternately, can mount the appliance as an NFS share and copy target. Data can be stored locally on the appliance or transferred over the network, and secure encryption — at-rest and in-flight — is enabled by default.

With these new appliances, Nuro will be able to automate much of their storage transfer needs. When their autonomous vehicles return to the depot, they can move data like software logs, LIDAR data, and sensor data — all ideal fits for Google’s Cloud Storage — from parked vehicles to the Transfer Appliance. Online transfers can then be performed throughout the day, ensuring a steady stream of valuable data in the cloud for developers to analyze and use in their nightly builds. All of this will help Nuro’s engineering leaders like Jie Pan to run more productive development teams with less operational overhead.

“Our autonomous vehicles generate a tremendous amount of useful data, and our goal is to get that data to our engineers as soon as possible,” said Jie Pan, Engineering Manager at Nuro. “When vehicles return to the depot, we can move data hourly into Cloud Storage over the network. We also have the flexibility to return the Transfer Appliance back to Google Cloud. Most importantly, this rapid transfer architecture gives a meaningful boost to engineering productivity and development velocity.”

Going the extra mile

Engineering and infrastructure leaders understand the value of delivering the right data to the right teams, as fast as possible. By adding preconfigured, over-the-network transfer into a turnkey Transfer Appliance, Google Cloud customers can more easily automate these data deliveries by scheduling regular migrations of on-premises files, objects, and other unstructured data to our Cloud Storage.

As Nuro continues to grow their manufacturing and testing footprint, they plan to use Transfer Appliances to further scale and simplify their data migration from on-premises to Google Cloud. Cutting the time to migrate their data by more than half will make for happier, more productive developers, and that will help Nuro bring us all the future of delivery a little faster.

If you’d like to learn more about Transfer Appliance and its new online transfer capability, click here or reach out to your Google Cloud account team.