Boost Database Performance with AlloyDB Index Advisor: The Ultimate Optimization Tool
1374
Of your peers have already read this article.
4:00 Minutes
The most insightful time you'll spend today!
Intro
One of the time consuming tasks for DBAs is to maximize query performance. To optimize slow-running queries, they often have to do detailed analysis, understand optimizer plans, and go through a lot of trial and error before getting to the best set of indexes that help with improved query performance. However, even with expert knowledge of a database’s internals, it is challenging to choose an effective set of indexes, especially when workloads change over time. The complexity of queries including several joins, filters, and subqueries make the process of identifying the right set of indexes very challenging for DBAs. Also, the effect of an index on the query plan needs to be taken into account when considering further indexes. Creating an index may cause the query optimizer to select completely different join orders and join/scan methods. This effect is hard to predict and quantify for DBAs.
Imagine if the database itself is intelligent enough to identify such queries, and recommends creating specific B-tree indexes?
Google Cloud’s AlloyDB for PostgreSQL is a fully-managed and fully PostgreSQL-compatible database for demanding transactional and analytical workloads that provides enterprise-grade performance and availability. AlloyDB offers Index Advisor, a built-in feature that helps alleviate the guesswork of tuning query performance with deep analysis of the different parts of a query including subqueries, joins, and filters. It periodically analyzes the database workload, identifies queries that can benefit from indexes, and recommends new indexes that can increase query performance.
How the AlloyDB Index Advisor works
First, let’s review a few AlloyDB terms relevant to the Index Advisor’s work.
- PostgreSQL’s system catalog has schema metadata, such as information about tables and columns, and internal bookkeeping information.
- HypoPG is an open source PostgreSQL extension that helps with hypothetical indexes without actually creating them to see if the index helps with query execution.
- Query Optimizer generates optimal execution plan.
1. AlloyDB’s Index Advisor tracks the user query workload and analyzes it using statistics from the system catalog; it then identifies queries that could be improved significantly and potential candidate indexes for those cases. Note that there could be a large number of candidate indexes.
2. The Index Advisor evaluates each of these queries using hypothetical indexes based on the HypoPG, an open source extension and AlloyDB’s Query Optimizer.
3. The results of this evaluation are used to intelligently select the best set of indexes for the workload and to generate recommendations.
You can then simply copy and run the suggested index creation SQL commands. The Index Advisor consumes minimal resources and analyzes queries at a frequency that you define. It can also be constrained to have a user-specified maximum storage budget for the new indexes it recommends.
How to use the Index Advisor
1. Index Advisor is enabled by default. Its recommendation engine analyzes your workload at the specified frequency (every 24 hours by default) to capture any potential new index recommendations.
2. Run your queries that are representative of your workload to the instance. Index advisor tracks these queries automatically.
3. To request an index recommendation immediately, you can use the google_db_advisor_recommend_indexes() function. This function performs on-demand analysis and recommends indexes for the top 100 queries. SELECT * FROM google_db_advisor_recommend_indexes();
4. To view the recommended indexes and the queries based on periodic analysis, use the following query (also see the Usage Example section). SELECT DISTINCT recommended_indexes, queryFROM google_db_advisor_workload_report r JOIN google_db_advisor_workload_statements sON r.query_id = s.query_id;
Note that Index advisor analyzes queries issued by the connected user. For the user with the pg_read_all_stats role, it analyzes all tracked queries.
Currently, Index advisor only recommends new indexes. In future, the Index Advisor could be used to report unused indexes as well; dropping these can reduce index maintenance overhead for your transactional workload.
Evaluation
We will discuss two cases in this section.
1. Decision support benchmark: We used a sample internal benchmark that modeled a decision support system. The benchmark dataset contained 450+ columns across 24 tables. The result quantified the performance of 100+ complex analytical queries. The benchmark initially had a minimal number of indexes and the normalized total query execution time was around 200+ minutes. We then enabled the AlloyDB Index Advisor and it recommended an additional 17 indexes. Creating those indexes and re-running the queries reduced the total execution time by half to ~100 minutes (1.8x).
2. Real-world AlloyDB customer workload: This is a real-world workload from a large financial customer. They have a portfolio of complex analytical queries and business insights demand fast response times. Before using Index Advisor, DBAs had already created a few indexes that they considered to be useful to speed up these queries. Many of their queries joined 30+ tables, had 10+ filters and multiple subqueries. Those made it complex for DBAs to correctly identify the most beneficial indexes to create. AlloyDB Index Advisor analyzed these complex structures and identified four additional indexes. By adding the suggested indexes, performance of 17 queries were up by 5x – 73x. The response time of these queries dropped from seconds to milliseconds.
Usage Example
An example: We use the following example to demonstrate the use of Index Advisor. The example simulates a retail application performing analytics on its orders table.
1. Create a sample orders table.
CREATE TABLE orders (
o_orderkey bigint NOT NULL,
o_custkey int NOT NULL,
o_orderstatus "char" NOT NULL,
o_totalprice numeric(13,2) NOT NULL,
o_orderdate date NOT NULL,
o_orderpriority character varying(15) NOT NULL,
o_clerk character varying(15) NOT NULL,
o_shippriority int NOT NULL,
o_comment character varying(79) NOT NULL
);2. Insert 100K random orders into the table.
INSERT INTO orders
SELECT col, col, substr(md5(random()::text), 1, 1), random(), date '2023-01-01' + col * interval '1 hour', substr(md5(random()::text), 1, 1), concat('clerk', col), col%10, concat('comment', col)
FROM generate_series(1,1000000) g(col);3. Analyze the table to populate statistics.
ANALYZE orders;4. Run a query that counts the number of orders in the second week of Jan. You can run the query with timing on so that you can see how long it takes to run the query before and after the index is created.
\timing
SELECT COUNT(*) FROM ORDERS WHERE o_orderdate >= '2023-01-09' and o_orderdate <= '2023-01-15';
Time: 42.196 ms5. Manually request an index recommendation.
SELECT * FROM google_db_advisor_recommend_indexes();
index | estimated_storage_size_in_mb
--------------------------------------------------+------------------------------
CREATE INDEX ON "public"."orders"("o_orderdate") | 25
(1 row)6. View recommended indexes for tracked queries.
SELECT DISTINCT recommended_indexes, query
FROM google_db_advisor_workload_report r, google_db_advisor_workload_statements s
WHERE r.query_id = s.query_id AND recommended_indexes != '';
recommended_indexes | query
--------------------------------------------------+------------------------------------------------------------------------------------------------
CREATE INDEX ON "public"."orders"("o_orderdate") | SELECT COUNT(*) FROM ORDERS WHERE o_orderdate >= '2023-01-09' and o_orderdate <= '2023-01-15';
(1 row)6. View recommended indexes for tracked queries.
SELECT DISTINCT recommended_indexes, query
FROM google_db_advisor_workload_report r, google_db_advisor_workload_statements s
WHERE r.query_id = s.query_id AND recommended_indexes != ”;
recommended_indexes | query
————————————————–+————————————————————————————————
CREATE INDEX ON “public”.”orders”(“o_orderdate”) | SELECT COUNT(*) FROM ORDERS WHERE o_orderdate >= ‘2023-01-09’ and o_orderdate <= ‘2023-01-15’;
(1 row)
7. Add the recommended index.
CREATE INDEX ON "public"."orders"("o_orderdate");8. Re-run the query.
\timing
SELECT COUNT(*) FROM ORDERS WHERE o_orderdate >= '2023-01-09' and o_orderdate <= '2023-01-15';
Time: 1.403 ms (compared to 42.196ms before the index)Learn more
- To learn more about AlloyDB’s Index Advisor, see Enable and use the index advisor | AlloyDB for PostgreSQL | Google Cloud
- To learn about AlloyDB, read AlloyDB for PostgreSQL intelligent scalable storage | Google Cloud Blog
- Start building on Google Cloud with $300 in free credits and 20+ always free products. https://cloud.google.com/free
Scaling Your Spanner Databases: The Benefits of Read-Only Replicas and Zero-Downtime Moves
1476
Of your peers have already read this article.
1:30 Minutes
The most insightful time you'll spend today!
As Google Cloud’s fully managed relational database that offers near unlimited scale, strong consistency, and availability up to 99.999%, Cloud Spanner powers applications at any scale in industries such as financial services, games, retail, and healthcare.
When you set up a Spanner instance, you can choose from two different kinds of configurations: regional and multi-regional. Both configuration types offer high availability, near unlimited scale, and strong consistency. Regional configurations offer 99.99% availability and can survive zone outages. Multi-regional configurations offer 99.999% availability and can survive two zone outages and entire regional outages.
Today, we’re announcing a number of significant enhancements to Spanner’s regional and multi-regional capabilities:
- Configurable read-only replicas let you add read-only replicas to any regional or multi-regional Spanner instance to deliver low latency reads to clients in any geography
- Spanner’s zero-downtime instance move service gives you the freedom to move your production Spanner instances from any configuration to another on the fly, with zero downtime, whether it’s regional, multi-regional, or a custom configuration with configurable read-only replicas
- We’re also dropping the list prices of our nine-replica global multi-regional configurations nam-eur-asia1 and nam-eur-asia3 to make them even more affordable for global workloads
Let’s take a look at each of these enhancements in a bit more detail.
Configurable read-only replicas
One of Spanner’s most powerful capabilities is its ability to deliver high performance across vast geographic territories. Spanner achieves this performance with read-only replicas. As its name suggests, a read-only replica contains an entire copy of the database and it can serve stale reads without requiring a round trip back to the leader region. In doing so, read-only replicas deliver low latency stale reads to nearby clients and help increase a node’s overall read scalability.
For example, a global online retailer would likely want to ensure that its customers worldwide can search and view products from its catalog efficiently. This product catalog would be ideally suited for Spanner’s nam-eur-asia1 multi-region configuration, which has read/write replicas in the United States and read-only replicas in Belgium and Taiwan. This would ensure that customers can view the product catalog with low latency around the globe.
Until today, read-only replicas were available in several multi-region configurations: nam6, nam9, nam12, nam-eur-asia1, and nam-eur-asia3. But now, with configurable read-only replicas, you can add read-only replicas to any regional or multi-regional Spanner instance so that you can deliver low-latency stale reads to clients everywhere.
To add read-only replicas to a configuration, go to the Create Instance page in the Google Cloud console. You’ll now see a “Configure read-only replicas” section. In this section, select the region for the read-only replica, along with the number of replicas you want per node, and create the instance. It’s as simple as that!
The following snapshot shows how to add a read-only replica in us-west2 (Los Angeles) to the nam3 multi-regional configuration.
As we roll out configurable read-only replicas, we do not yet offer read-only replicas in every configuration/region pair. If you find that your desired read-only replica region is not yet listed, simply fill out this request form.
Configurable read-only replicas are available today for $1/replica/node-hour plus storage costs. Full details on pricing are available at Cloud Spanner pricing.
Also announcing: Spanner’s zero-downtime instance move service
Now that you can use configurable read-only replicas to create new instance configurations that are tailored to your specific needs, how can you migrate your current Spanner instances to these new configurations without any downtime? Spanner database instances are mission critical and can scale to many petabytes and millions of queries per second. So you can imagine that moving a Spanner instance from one configuration to another — say us-central1 in Iowa to nam3 with a read-only replica in us-west2 — is no small feat. Factor in Spanner’s stringent availability of up to 99.999% while serving traffic at extreme scale, and it might seem impossible to move a Spanner instance from us-central1 to nam3 with zero downtime.
However, that’s exactly what we’re announcing today! With the instance move service, now generally available, you can request a zero-downtime, live migration of your Spanner instances from any configuration to any other configuration — whether they are regional, multi-regional, or custom configurations with configurable read-only replicas.
To request an instance move, select “contact Google” in the Edit Instance of the Google Cloud Console and fill out the instance move request form. Once you make a move request, we’ll contact you to let you know the start date of your instance configuration move, and then move your configuration with zero downtime and no code changes while preserving the SLA guarantees of your configuration.
When moving an instance, both the source and destination instance configurations are subject to hourly compute and storage charges, as outlined in Cloud Spanner pricing. Depending on your environment, instance moves can take anywhere from a few hours to a few days to complete. Most importantly, during the instance move, your Spanner instance continues to run without any downtime, and can continue to rely on Spanner’s high availability, near unlimited scale, and strong consistency to serve your mission-critical production workloads.
Price drops for global 9-replica Spanner multi-regional configurations
Finally, we’re also pleased to announce that we’re making it even more compelling to use Spanner’s global configurations of nam-eur-asia1 and nam-eur-asia3 by dropping the compute list price of these configurations from $9/node/hour to $7/node/hour. With write quorums in North America and read-only replicas in both Europe and Asia, these configurations are perfectly suited for global applications with strict performance requirements and 99.999% availability. And now, they’re even more cost-effective to use!
Learn more
- If you are new to Spanner, try Spanner at no charge with a 90-day free trial instance.
- Learn more about multi-regional Spanner configurations by reading Demystifying Cloud Spanner multi-region configurations
How to Decide Whether to Run a Database on Kubernetes
5602
Of your peers have already read this article.
3:30 Minutes
The most insightful time you'll spend today!
Today, more and more applications are being deployed in containers on Kubernetes—so much so that we’ve heard Kubernetes called the Linux of the cloud.
Despite all that growth on the application layer, the data layer hasn’t gotten as much traction with containerization. That’s not surprising, since containerized workloads inherently have to be resilient to restarts, scale-out, virtualization, and other constraints. So handling things like state (the database), availability to other layers of the application, and redundancy for a database can have very specific requirements. That makes it challenging to run a database in a distributed environment.
However, the data layer is getting more attention, since many developers want to treat data infrastructure the same as application stacks.
Operators want to use the same tools for databases and applications, and get the same benefits as the application layer in the data layer: rapid spin-up and repeatability across environments. In this blog, we’ll explore when and what types of databases can be effectively run on Kubernetes.
Before we dive into the considerations for running a database on Kubernetes, let’s briefly review our options for running databases on Google Cloud Platform (GCP) and what they’re best used for.
- Fully managed databases. This includes Cloud Spanner, Cloud Bigtable and Cloud SQL, among others. This is the low-ops choice, since Google Cloud handles many of the maintenance tasks, like backups, patching and scaling. As a developer or operator, you don’t need to mess with them. You just create a database, build your app, and let Google Cloud scale it for you. This also means you might not have access to the exact version of a database, extension, or the exact flavor of database that you want.
- Do-it-yourself on a VM. This might best be described as the full-ops option, where you take full responsibility for building your database, scaling it, managing reliability, setting up backups, and more. All of that can be a lot of work, but you have all the features and database flavors at your disposal.
- Run it on Kubernetes. Running a database on Kubernetes is closer to the full-ops option, but you do get some benefits in terms of the automation Kubernetes provides to keep the database application running. That said, it is important to remember that pods (the database application containers) are transient, so the likelihood of database application restarts or failovers is higher. Also, some of the more database-specific administrative tasks—backups, scaling, tuning, etc.—are different due to the added abstractions that come with containerization.
Tips for running your database on Kubernetes
When choosing to go down the Kubernetes route, think about what database you will be running, and how well it will work given the trade-offs previously discussed.
Since pods are mortal, the likelihood of failover events is higher than a traditionally hosted or fully managed database. It will be easier to run a database on Kubernetes if it includes concepts like sharding, failover elections and replication built into its DNA (for example, ElasticSearch, Cassandra, or MongoDB). Some open source projects provide custom resources and operators to help with managing the database.
Next, consider the function that database is performing in the context of your application and business. Databases that are storing more transient and caching layers are better fits for Kubernetes. Data layers of that type typically have more resilience built into the applications, making for a better overall experience.
Finally, be sure you understand the replication modes available in the database. Asynchronous modes of replication leave room for data loss, because transactions might be committed to the primary database but not to the secondary database(s). So, be sure to understand whether you might incur data loss, and how much of that is acceptable in the context of your application.
After evaluating all of those considerations, you’ll end up with a decision tree looking something like this:
How to deploy a database on Kubernetes
Now, let’s dive into more details on how to deploy a database on Kubernetes using StatefulSets.
With a StatefulSet, your data can be stored on persistent volumes, decoupling the database application from the persistent storage, so when a pod (such as the database application) is recreated, all the data is still there.
Additionally, when a pod is recreated in a StatefulSet, it keeps the same name, so you have a consistent endpoint to connect to. Persistent data and consistent naming are two of the largest benefits of StatefulSets. You can check out the Kubernetes documentation for more details.
If you need to run a database that doesn’t perfectly fit the model of a Kubernetes-friendly database (such as MySQL or PostgreSQL), consider using Kubernetes Operators or projects that wrap those database with additional features. Operators will help you spin up those databases and perform database maintenance tasks like backups and replication. For MySQL in particular, take a look at the Oracle MySQL Operator and Crunchy Data for PostgreSQL.
Operators use custom resources and controllers to expose application-specific operations through the Kubernetes API. For example, to perform a backup using Crunchy Data, simply execute pgo backup [cluster_name]. To add a Postgres replica, use pgo scale cluster [cluster_name].
There are some other projects out there that you might explore, such as Patroni for PostgreSQL. These projects use Operators, but go one step further. They’ve built many tools around their respective databases to aid their operation inside of Kubernetes. They may include additional features like sharding, leader election, and failover functionality needed to successfully deploy MySQL or PostgreSQL in Kubernetes.
While running a database in Kubernetes is gaining traction, it is still far from an exact science. There is a lot of work being done in this area, so keep an eye out as technologies and tools evolve toward making running databases in Kubernetes much more the norm.
When you’re ready to get started, check out GCP Marketplace for easy-to-deploy SaaS, VM, and containerized database solutions and operators that can be deployed to GCP or Kubernetes clusters anywhere.
Google Cloud Next 21 for Data Analytics Unplugged
4932
Of your peers have already read this article.
4:30 Minutes
The most insightful time you'll spend today!
October 23rd (this past Saturday!) was my 4th Googlevarsery and we are wrapping an incredible Google Next 2021!
When I started in 2017, we had a dream of making BigQuery Intelligent Data Warehouse that would power every organization’s data driven digital transformation.
This year at Next, It was amazing to see Google Cloud’s CEO, Thomas Kurian, kick off his keynote with CTO of WalMart, Suresh Kumar , talking about how his organization is giving its data the “BigQuery treatment”.
AS I recap Next 2021 and reflect on our amazing journey over the past 4 years, I’m so proud of the opportunity I’ve had to work with some of the world’s most innovative companies from Twitter to Walmart to Home Depot, Snap, Paypal and many others.
So much of what we announced at Next is the result of years of hard work, persistence and commitment to delivering the best analytics experience for customers.
I believe that one of the reasons why customers choose Google for data is because we have shown a strong alignment between our strategy and theirs and because we’ve been relentlessly delivering innovation at the speed they require.
Unified Smart Analytics Platform
Over the past 4 years our focus has been to build industries leading unified smart analytics platforms. BigQuery is at the heart of this vision and seamlessly integrates with all our other services. Customers can use BigQuery to query data in BigQuery Storage, Google Cloud Storage, AWS S3, Azure Blobstore, various databases like BigTable, Spanner, Cloud SQL etc. They can also use any engine like Spark, Dataflow, Vertex AI with BigQuery. BigQuery automatically syncs all its metadata with Data Catalog and users can then run a Data Loss Prevention service to identify sensitive data and tag it. These tags can then be used to create access policies.
In addition to Google services, all our partner products also integrate with BigQuery seamlessly. Some of the key partners highlighted at Next 21 included Data Ingestion (Fivetran, Informatica & Confluent), Data preparation (Trifacta, DBT), Data Governance (Colibra), Data Science (Databricks, Dataiku) and BI (Tableau, PowerBI, Qlik etc).
Planet Scale analytics with BigQuery
BigQuery is an amazing platform and over the past 11 years we have continued to innovate in various aspects. Scalability has always been a huge differentiator for BigQuery. BigQuery has many customers with more than 100 petabytes of data and our largest customer is now approaching an exabyte of data. Our large customers have run queries over trillions of rows.
But scale for us is not just about storing or processing a lot of data. Scale is also how we can reach every organization in the world. This is the reason we launched BigQuery Sandbox which enables organizations to get started with BigQuery without a credit card. This has enabled us to reach tens of thousands of customers. Additionally to make it easy to get started with BigQuery we have built integrations with various Google tools like Firebase, Google Ads, Google Analytics 360, etc.
Finally, to simplify adoption we now provide options for customers to choose whether they would like to pay per query, buy flat rate subscriptions or buy per second capacity. With our autoscaling capabilities we can provide customers best value by mixing flat rate subscription discounts with auto scaling with flex slots.
Intelligent Data Warehouse to empower every data analyst to become a data scientist
BigQuery ML is one of the biggest innovations that we have brought to market over the past few years. Our vision is to make every data analyst a data scientist by democratizing Machine learning. 80% of time is spent in moving, prepping and transforming data for the ML platform. This also causes a huge data governance problem as now every data scientist has a copy of your most valuable data. Our approach was very simple. We asked:”what if we could bring ML to data rather than taking data to an ML engine?”
That is how BigQuery ML was born. Simply write 2 lines of SQL code and create ML models.
Over the past 4 years we have launched many models like regression, matrix factorization, anomaly detection, time series, XGboost, DNN etc. These models are used by customers to solve complex business problems simply from segmentation, recommendations, time series forecasting, package delivery estimation etc. The service is very popular: 80%+ of our top customers are using BigQueryML today. When you consider that the average adoption rate of ML/AI is in the low 30%, 80% is a pretty good result!
We announced tighter integration of BQML with Vertex AI. Model explainability will provide the ability to explain the results of predictive ML classification and regression models by understanding how each feature contributes to the predicted result. Also users will be able to manage, compare and deploy BigQuery ML models in Vertex; leverage Vertex Pipelines to train and predict BigQuery ML models.
Real-time streaming analytics with BigQuery
Customer expectations are changing and everyone wants everything in an instant: according to Gartner, by the end of 2024, 75% of enterprises will shift from piloting to operationalizing AI, driving a 5X increase in streaming data and analytics infrastructures.
The BigQuery’s storage engine is optimized for real-time streaming. BigQuery supports streaming ingestion of 10s of millions of events in real-time and there is no impact on query performance. Additionally customers can use materialized views and BI Engine (which is now GA) on top of streaming data. We guarantee always fast, always fresh data. Our system automatically updates MVs and BI Engine.
Many customers also use our PubSub service to collect real-time events and process these through Dataflow prior to ingesting into BigQuery. This is a streaming ETL pattern which is very popular. Last year,we announced PubSub Lite to provide customers with a 90% lower price point and aTCO that is lower than any DIY Kafka deployment.
We also announced Dataflow Prime, it is our next generation platform for Dataflow. Big Data processing platforms have only focused on horizontal scaling to optimize workloads. But we have seen new patterns and use cases like streaming AI where you may have a few steps in pipelines that perform data prep and then customers have to run a GPU based model. Customers want to use different sizes and shapes of machines to run these pipelines in the most optimum manner. This is exactly what Dataflow Prime does. It delivers vertical auto scaling with the right fitting for your pipelines. We believe this should lower costs for pipelines significantly.
With Datastream as our change data capture service (built on Alooma technology), we have solved the last key problem space for customers. We can automatically detect changes in your operational databases like MySQL, Postgres, Oracle etc and sync them in BigQuery.
Most importantly, all these products work seamlessly with each other through a set of templates. Our goal is to make this even more seamless over next year.
Open Data Analytics with BigQuery
Google has always been a big believer in Open Source initiatives. Our customers love using various open source offerings like Spark, Flink, Presto, Airflow etc. With Dataproc & Composer our customers have been able to run various of these open source frameworks on GCP and leverage our scale, speed and security. Dataproc is a great service and delivers massive savings to customers moving from on-prem Hadoop environments. But customers want to focus on jobs and not clusters.
That’s why we launched Dataproc Serverless Spark (GA) offering at Next 2021. This new service adheres to one of our key design principles we started with: make data simple.
Just like with BigQuery, you can simply RUN QUERY. With Spark on Google Cloud, you simply RUN JOB. ZDNet did a great piece on this. I invite you to check it out!
Many of our customers are moving to Kubernetes and wanted to use that as the platform for Spark. Our upcoming Spark on GKE offering will give the ability to deploy spark workloads on existing Kubernetes clusters.
But for me the most exciting capability we have is, the ability to run Spark directly on BigQuery Storage. BigQuery storage is highly optimized analytical storage. By running Spark directly on it, we again bring compute to data and avoid moving data to compute.
BigSearch to power Log Analytics
We are bringing the power of Search to BigQuery. Customers already ingest massive amounts of log data into BigQuery and perform analytics on it. Our customers have been asking us for better support for native JSON and Search. At Next 21 we announced the upcoming availability of both these capabilities.
Fast cross column search will provide efficient indexing of structured, semi-structured and unstructured data. User friendly SQL functions let customers rapidly find data points without having to scan all the text in your table or even know which column the data resides in.
This will be tightly integrated with native JSON, allowing customers to get BigQuery performance and storage optimizations on JSON as well as search on unstructured or constantly changing data structures.
Multi & Cross Cloud Analytics
Research on multi cloud adoption is unequivocal — 92% of businesses in 2021 report having a multi cloud strategy. We have always believed in providing customers choice to our customers and meeting them where they are. It was clear that all our customers wanted us to take our gems like BigQuery to other clouds as their data was distributed on different clouds.
Additionally it was clear that customers wanted cross cloud analytics not multi-cloud solutions that can just run in different clouds. In short, see all their data with a single pane of glass, perform analysis on top of any data without worrying about where it is located, avoid egress costs and finally perform cross cloud analysis across datasets on different clouds.
With BigQuery Omni, we deliver on this vision, with a new way of analyzing data stored in multiple public clouds. Unlike competitors, BigQuery Omni does not create silos across different clouds. BigQUery provides a single control plane that shows an analyst all data they have access to across all clouds. Analyst just writes the query and we send it to the right cloud across AWS, Azure or GCP to execute it locally. Hence no egress costs are incurred.
We announced BQ Omni GA for both AWS and Azure at Google Next 21 and I’m really proud of the team for delivering on this vision. Check out Vidya’s session and learn from Johnson and Johnson how they innovate in a multi-cloud world.
Geospatial Analytics with BigQuery and Earth Engine
We have partnered with our Google Geospatial team to deliver GIS functionality inside BigQuery over the years. At Next we announced that customers will be able to integrate Earth Engine with BigQuery, Google Cloud’s ML technologies, and Google Maps Platform.
Think about all the scenarios and use-cases your team’s going to be able to enable sustainable sourcing, saving energy or understanding business risks.
We’re integrating the best of Google and Google Cloud together to – again – make it easier to work with data to create a sustainable future for our planet.
BigQuery as a Data Exchange & Sharing Platform
BigQuery was built to be a sharing platform. Today we have 3000+ organizations sharing more than 250 petabytes of data across organizations. Google also brings more than 150 public datasets to be used across various use cases. In addition to this, we are also bringing some of the most unique datasets like Google Trends to BigQuery. This will enable organizations to understand in real-time trends and apply to their business problems.
I am super excited about the Analytics Hub Preview announcement. Analytics Hub will provide the ability for organizations to build private and public analytics exchanges. This will include data, insights, ML Models and visualizations. This is built on top of the industry leading security capabilities of BigQuery.
Breaking Data Silos
Data is distributed across various systems in the organization and making it easy to break the data silo and make all this data accessible to all is critical. I’m also particularly excited about the Migration Factory we’re building with Informatica and the work we are doing for data movement, intelligent data wrangling with players like Trifacta and FiveTran, with whom we share over 1,000 customers (and growing!). Additionally we continue to deliver native Google service to help our customers.
We acquired Cask in 2018 and launched our self service Data Integration service in Data Fusion. Now Fusion allows customers to create complex pipelines with just simple drag and drop. This year we focused on unlocking SAP data for our customers. We have launched various SAP connectors and accelerators to achieve this.
At GCP Next we also announced our BigQuery Migration service in preview. Many of our customers are migrating their legacy data warehouses and data lakes to BigQuery. BigQuery Migration Service provides end-to-end tools to simplify migrations for these customers.
And today, to make migrations to BigQuery easier for even more customers, I am super excited to announce the acquisition of CompilerWorks. CompilerWorks’ Transpiler is designed from the ground up to facilitate SQL migration in the real world and will help our customers accelerate their migrations. It supports migrations from over 10 legacy enterprises data warehouses and we will be making it available as part of our BigQuery Migration service in the coming months.
Data Democratization with BigQuery
Over the past 4 years we have focused a lot on making it very easy to derive actionable insights from data in BigQuery. Our priority has been to provide a strong ecosystem of partners that can provide you with great tools to achieve this but also deliver native Google capabilities.
With our BI engine GA announcement which we introduced in 2019, previewed earlier this year and showcased with tools like Microsoft PowerBI and Tableau, is now available for all to play with.
BigQuery + Data Studio are like peanut butter and Jelly. They just work well together. We launched BI Engine first with Data Studio and scaled it to all the users. More than 40% of our BigQuery customers use Data Studio. Once we knew BI Engine works extremely well we now have made it an integral part of BigQuery API and launched it for all our internal and partner BI tools.
We announced GA for BI Engine at Next 2021 but we were already GA with Data Studio for the past 2 years. We recently moved the Data Studio team back into Google Cloud making the partnership even stronger. If you have not used Data Studio, I encourage you to take a look and get started for free today here!!
Connected Sheets for BigQuery is one of my favorite combinations. You can give every business user in your organization the ability to analyze billions of records using standard Google Sheets experience. I personally use it everyday to analyze all our product data.
We acquired Looker in Feb 2020 with a vision of providing a semantic modeling layer to our customers with a governed BI solution. Looker is tightly integrated with BigQuery including BigQuery ML. Our latest partnership with Tableau where Tableau customers will soon be able to leverage Looker’s semantic model, enabling new levels of data governance while democratizing access to data.
Finally, I have a dream that one day we will bring Google Assistant to your enterprise data. This is the vision of Data QnA. We are in early innings on this and we will continue to work hard to make this vision a reality.
Intelligent Data Fabric to unify the platform
Another important trend that shaped our market is the Data Mesh. Earlier this year, Starburst invited me to talk about this very topic. We have been working for years on this concept, and although we would love for all data to be neatly organized in one place, we know that our customers’ reality is that it is not (If you want to know more about this, read about my debate on this topic with Fivetran’s George Fraser, a16z’s Martin Casado and Databricks’ Ali Ghodsi).
Everything I’ve learned from customers over my years in this field is that they don’t just need a data catalog or a set of data quality and governance tools, they need an intelligent data fabric. That is why we created Dataplex, whose general availability we announced at Next.
Dataplex enables customers to centrally manage, monitor, and govern data across data lakes, data warehouses, and data marts, while also ensuring data is securely accessible to a variety of analytics and data science tools. It lets customers organize and manage data in a way that makes sense for their business, without data movement or duplication. It provides logical constructs – lakes, data zones, and assets – which enable customers to abstract away the underlying storage systems to build a foundation for setting policies around data access, security, lifecycle management, and so on. Check out Prajakta Damle’s session and learn from Deutsche Bank how they are thinking about a unified data mesh across distributed data.
Closing Thoughts
Analysts have recognized our momentum and, as I look back at this year, I couldn’t thank our customers and partners enough for the support they provided my team and I across our large Data Analytics portfolio: in March, Google BigQuery was named a Leader in The Forrester Wave™: Cloud Data Warehouse, Q1 2021. And in June, Dataflow was named a Leader in The Forrester Wave™: Streaming Analytics, Q2 2021 report.
If you want to get a taste for why customers choose us over other hyperscalers or cloud data warehousing, I suggest you watch the Data Journey series we’ve just launched, which documents the stories of organizations modernizing to the cloud with us.
The Google Cloud Data Analytics portfolio has become a leading force in the industry and I couldn’t be more excited to have been part of it. I do miss you, my customers and partners, and I’m frankly bummed that we didn’t get to meet in person like we’ve done so many times before (see a photo of my last in-person talk before the pandemic), but this Google Next was extra special, so let’s dive into the product innovation and their themes.
I hope that I will get to see you in person next time we run Google Next!
Demystifying Transactional Locking in Cloud Spanner
3000
Of your peers have already read this article.
3:30 Minutes
The most insightful time you'll spend today!
Cloud Spanner is a fully managed relational database with unlimited scale, strong consistency, and up to 99.999% availability. It is designed for highly concurrent applications that read and update data, for example, to process payments or for online game play. To ensure the consistency across multiple concurrent transactions, Cloud Spanner uses a combination of shared locks and exclusive locks to control access to the data. In this blog, we will explore the different types of locks present in Cloud Spanner. We will also discuss some common cases of transactional locking in Cloud Spanner, and what to look out for to detect when these cases might be occurring.
What is a lock?
Before we get into the details, let us first quickly recap and define a lock in the context of database systems.
“Locks” in databases are a mechanism for concurrency control. Locks are typically held on a resource, which may mean rows, columns, tables or even entire databases. When a resource is locked by a transaction, it cannot be accessed by another transaction until the lock is released.
Timeline view of transactions
Before we proceed to discuss transaction locking in the context of read and read-write transactions, it is important to recap the timeline view of the transactions in Spanner.
Please also keep in mind the concept of write buffering in transactions, since we refer to it in the Common Transaction Patterns section below. Write buffering refers to the Cloud Spanner server(s) accepting the writes. Note that these writes are not durable until a commit has been performed.
Types of locks in Cloud Spanner
Cloud Spanner operations acquire locks when the operations are part of a read-write transaction. Read-only transactions do not acquire locks. Unlike other approaches that lock entire tables or rows, the granularity of transactional locks in Spanner is a cell, or the intersection of a row and a column. This means that two transactions can read and modify different columns of the same row at the same time. To maximize the number of transactions that have access to a particular data cell at a given time, Cloud Spanner uses different lock modes.
Here is a brief description of the different lock types. Learn more about each lock type in the Cloud Spanner documentation.
- ReaderShared Lock – Acquired when a read-write transaction reads data.
- WriterShared Lock – Acquired when a read-write transaction writes data without reading it.
- Exclusive Lock – Acquired when a read-write transaction which has already acquired a ReaderShared lock tries to write data after the completion of read. It is a special case for a transaction to hold both the ReaderShared lock and WriterShared lock at the same time.
- WriterSharedTimestamp Lock – Special type of lock acquired when inserting new rows with the transaction’s commit timestamp as part of the primary key.
Handling Lock Conflicts
Since read-write transactions use locks to execute atomically, they run the risk of deadlocking. For example, consider the following scenario: transaction Txn1 holds a lock on record A and is waiting for a lock on record B, and Txn2 holds a lock on record B and is waiting for a lock on record A. The only way to make progress in this situation is to abort one of the transactions so it releases its lock, allowing the other transaction to proceed.
Cloud Spanner uses the standard “wound-wait” algorithm to handle deadlock detection. Under the hood, Spanner keeps track of the age of each transaction that requests conflicting locks. It also allows older transactions to abort younger transactions, where “older” means that the transaction’s earliest read, query, or commit happened sooner.
Common Transaction Patterns
Armed with the basics of transactions and locks in Cloud Spanner, we will now walk through a few practical use-cases. We take the example of an application which queries and updates users’ balance in a table named accounts. The accounts table has the following columns –
Case 1: Transaction waiting to get exclusive lock because of higher priority shared-lock
Sequence
- Txn1 begins.
- Txn2 begins.
- Txn2 reads the table and acquires a ReadShared Lock.
- Txn1 buffers its write.
- Txn1 tries to commit, but since Txn2 has higher priority (because it has executed its first operation first), Txn1 will have to wait until Txn2 releases the ReadShared Lock.
After committing, Txn2 releases its ReadShared Lock. Txn1 is now able to upgrade to an Exclusive Lock. It acquires the Exclusive Lock and commits.
Note 1: UPDATE WHERE is always transformed into SELECT WHERE, so an UPDATE statement is not a blind write, but a read-write operation.
Note 2: While the above example considers the use-case of querying and updating a single row, the same transaction pattern is also applicable across a key-range.
What to watch out for
- Timeouts due to Transactions waiting for acquiring locks. Refer to transaction statistics and lock statistics to understand how to detect this.
When does this typically happen
- Concurrent updates to a single key/key-range in a table.
Case 2: Transaction aborted because of concurrent execution succeeding
Sequence
- Txn1 begins.
- Txn2 begins.
- Txn1 reads a row from the table and acquires a ReadShared Lock.
- Txn2 reads the same row as Txn1 and acquires a ReadShared Lock.
- Txn1 buffers its write.
- Txn2 buffers its write.
- Txn1 tries to commit, has higher priority (because it has executed its first operation first), it will get priority in upgrading to an Exclusive Lock. It acquires the Exclusive Lock and commits.
Since Txn1 has committed and was the higher priority transaction, Txn1 will abort Txn2.. The abort will likely happen before Txn2 tries to commit.
What to watch out for
- High number of transaction aborts due to wounding of transactions. Refer to transaction statistics and lock statistics to understand how to detect this.
When does this typically happen
- Concurrent updates to a single key/key-range. Typically this happens in the case of hot keys being present in the table.
Case 3: Transaction waiting to get exclusive lock because of higher priority shared-lock & getting aborted because of prior succeeding concurrent execution
Sequence
- Txn1 begins.
- Txn2 begins.
- Txn2 reads the table and acquires a ReadShared Lock.
- Txn1 reads the table and acquires a ReadShared Lock.
- Txn1 buffers its write.
- Txn2 buffers its write.
- Txn1 tries to commit, but since Txn2 holds a ReadShared Lock, it has to wait until it gets cleared.
- Since Txn2 has higher priority (because it has executed its first operation first), it will acquire the Exclusive Lock first. Txn2 upgrades its ReaderShared Lock to an Exclusive Lock and commits.
Finally, after Step 8, when Txn1 tries to commit (since ReaderShared Lock from Txn2 is now cleared) it gets aborted. This is done to prevent deadlock with the higher priority transaction (Txn2).
Note: In Case 1, even though Txn2 had acquired a ReaderShared Lock earlier as in Case 3, it never upgraded to an Exclusive Lock (since there were no writes). Hence there was no need to abort Txn1, it could simply wait for Txn2 to release its ReaderShared lock and then commit.
What to watch out for
- High number of transaction aborts due to high contention between transactions. Refer to transaction statistics and lock statistics to understand how to detect this.
When does this typically happen
- Concurrent updates to a single key/key-range. Typically this happens in the case of hot keys being present in the table.
Recommendations
In order to mitigate these issues and reduce their occurrence. We recommend adopting the following best practices:
- If you need to perform more than one read at the same timestamp, and know in advance that you only need to read, consider using a read-only transaction. Because read-only transactions don’t write, they don’t hold locks and they don’t block other transactions. Further, read-only transactions never abort, so you don’t need to wrap them in retry loops.
- Always acquire ReadShared Locks on the smallest subset of keys or key ranges. This reduces the chances of lock contention.
- Analyze your code to only include critical path code within a transaction. Avoiding unneeded remote calls or complex, long-running business logic is a good way to ensure a transaction process quickly.
- Analyze your needs for multi-split transactions. Since transactions that update more than one split use a 2-phase commit protocol, they hold locks for a longer duration, thereby increasing chances of lock contention.
Get started today
Spanner’s unique architecture allows it to scale horizontally without compromising on the consistency guarantees that developers rely on in modern relational databases. Try out Spanner today for free for 90 days or for as low as $65 USD per month.
Four Key Takeaways from Google and Quantum Metric’s Back-to-School Retail Benchmark Study
4773
Of your peers have already read this article.
2:00 Minutes
The most insightful time you'll spend today!
Is it September yet? Hardly! School is barely out for the summer. But according to Google and Quantum Metric research, the back-to-school and off-to-college shopping season – which in the U.S. is second only to the holidays in terms of purchasing volume1 – has already begun. For retailers, that means planning for this peak season has kicked off as well.
We’d like to share four key trends that emerged from Google research and Quantum Metric’s Back-to-School Retail Benchmarks study of U.S. retail data, explore the reasons behind them, and outline the key takeaways.
- Out-of-stock and inflation concerns are changing the way consumers shop. Back-to-school shoppers are starting earlier every year, with 41% beginning even before school is out – even more so when buying for college1. Why? The behavior is driven in large part by consumers’ concerns that they won’t be able to get what they need if they wait too long. 29% of shoppers start looking a full month before they need something1.
Back-to-school purchasing volume is quite high, with the majority spending up to $500 and 21% spending more than $1,0001. In fact, looking at year-over-year data, we see that average cart values have not only doubled since November 2021, but increased since the holidays1. And keep in mind that back-to-school spending is a key indicator leading into the holiday season.
That said, as people are reacting to inflation, they are comparing prices, hunting for bargains, and generally taking more time to plan. This is borne out by the fact that 76% of online shoppers are adding items to their carts and waiting to see if they go on sale before making the purchase1. And, to help stay on budget and reduce shipping costs, 74% plan to make multiple purchases in one checkout1. That carries over to in-store shopping, when consumers are buying more in one visit to reduce trips and save on gas.
2. The omnichannel theme continues. Consumers continue to use multiple channels in their shopping experience. As the pandemic has abated, some 82% expect that their back-to-school buying will be in-store, and 60% plan to purchase online. But in any case, 45% of consumers report that they will use both channels; more than 50% research online first before ever setting foot in a store2. Some use as many as five channels, including video and social media, and these 54% of consumers spend 1.5 times more compared to those who use only two channels4.
And mobile is a big part of the journey. Shoppers are using their phones to make purchases, especially for deadline-driven, last-minute needs, and often check prices on other retailers’ websites while shopping in-store. Anecdotally, mobile is a big part of how we ourselves shop with our children, who like to swipe on the phone through different options for colors and styles. We use our desktops when shopping on our own, especially for items that require research and represent a larger investment – and our study shows that’s quite common.
3. Consumers are making frequent use of wish lists. One trend we have observed is a higher abandonment rate, especially for apparel and general home and school supplies, compared to bigger-ticket items that require more research. But that can be attributed in part to the increasing use of wish lists. Online shoppers are picking a few things that look appealing or items on sale, saving them in wish lists, and then choosing just a few to purchase. Our research shows that 39% of consumers build one or two wish lists per month, while 28% said they build one or two each week, often using their lists to help with budgeting1.
4. Frustration rates have dropped significantly. Abandonment rates aside, shopper annoyance rates are down by 41%, year over year1. This is despite out-of-stock concerns and higher prices. But one key finding showed that both cart abandonment and “rage clicks” are more frequent on desktops, possibly because people investing time on search also have more time to complain to customer service.
And frustration does still exist. Some $300 billion is lost each year in the U.S. from bad search experiences5. Data collected internationally shows that 80% of consumers view a brand differently after experiencing search difficulties, and 97% favor websites where they can quickly find what they are looking for5.
Lessons to Learn
What are the key takeaways for retailers? In general, consider the sources of customer pain points and find ways to erase friction. Improve search and personalization. And focus on improving the customer experience and building loyalty. Specifically:
80% of shoppers want personalization6. Think about how you can drive personalized promotions or experiences that will drive higher engagement with your brand.
46% of consumers want more time to research1. Drive toward providing more robust research and product information points, like comparison charts, images, and specific product details.
43% of consumers want a discount1, but given current economic trends, retailers may not be offering discounts. In order to appease budget-conscious shoppers, retailers can consider other retention strategies such as driving loyalty using points, rewards, or faster-shipping perks.
Be sure to keep returns as simple as possible so consumers feel confident when making a purchase, and reduce possible friction points if a consumer decides to make a return. 43% of shoppers return at least a quarter of the products they buy and do not want to pay for shipping or jump through hoops1.
How We Can Help
Google-sponsored research shows that price, deals, and promotions are important to 68% of back-to-school shoppers.7 In addition, shoppers want certainty that they will get what they want. Google Cloud can make it easier for retailers to enable customers to find the right products with discovery solutions. These solutions provide Google-quality search and recommendations on a retailer’s own digital properties, helping to increase conversions and reduce search abandonment. In addition, Quantum Metric solutions, available on the Google Cloud Marketplace, are built with BigQuery, which helps retailers consolidate and unlock the power of their raw data to identify areas of friction and deliver improved digital shopping experiences.
We invite you to watch the Total Retail webinar “4 ways retailers can get ready for back-to-school, off-to college” on demand and to view the full Back-to-School Retail Benchmarks report from Quantum Metric.
Sources:
1. Back-to-School Retail Benchmarks report from Quantum Metric
2. Google/Ipsos,Moments 2021, Jun 2021, Online survey, US, n=335 Back to School shoppers
3. Google/Ipsos, Moments 2021, Jun 2021, Online survey, US, n=2,006 American general population 18+
4. Google/Ipsos, Holiday Shopping Study, Oct 2021 – Jan 2022, Online survey, US, n=7,253, Americans 18+ who conducted holiday shopping activities in past two days
5. Google Cloud Blog, Nov 2021, “Research: Search abandonment has a lasting impact on brand loyalty”
6. McKinsey & Company, “Personalizing the customer experience: Driving differentiation in retail”
7. Think with Google, July 2021, “What to expect from shoppers this back-to-school season”
More Relevant Stories for Your Company
Migrate Your Microsoft SQL Server Workloads to Google Cloud
Enterprise database workloads are the backbone of many of your applications and ecosystems. Also, guaranteed availability is critical when choosing a cloud provider. Many enterprises built their mission-critical applications on Microsoft SQL Server 2008, and it’s common still to run into older versions of SQL Server as you’re working toward
What is Cloud Spanner and Is It the Right Database for You?
You’ve probably heard about Cloud Spanner, but what exactly does it do that is so different from other databases out there? How do you know if it’s worth spending your valuable time trying out? Cloud Spanner is the first enterprise-grade, globally distributed, and strongly consistent database service built for the
Transform ‘Dark Data’ from Documents with Document AI, Cloud Functions and Workflows
At enterprises across industries, documents are at the center of core business processes. Documents store a treasure trove of valuable information whether it's a company's invoices, HR documents, tax forms and much more. However, the unstructured nature of documents make them difficult to work with as a data source. We
Firestore Cheatsheet to Unlock Application Innovation
Your product teams might ask - “Why does it take so long to build a feature or application?” Building applications is a heavy lift due to the technical complexity, which includes the complexity of backend services that are used to manage and store data. Every moment focused on this technical
