Picking the right database for your application is not easy. The choice depends heavily on your use case—transactional processing, analytical processing, in-memory database, and so on—but it also depends on other factors. This post covers the different database options available within Google Cloud across relational (SQL) and non-relational (NoSQL) databases and explains which use cases are best suited for each database option. 

Relational databases 

In relational databases information is stored in tables, rows and columns, which typically works best for structured data. As a result they are used for applications in which the structure of the data does not change often. SQL (Structured Query Language) is used when interacting with most relational databases. They offer ACID consistency mode for the data, which means:

• Atomic: All operations in a transaction succeed or the operation is rolled back.
• Consistent: On the completion of a transaction, the database is structurally sound.
• Isolated: Transactions do not contend with one another. Contentious access to data is moderated by the database so that transactions appear to run sequentially.
• Durable: The results of applying a transaction are permanent, even in the presence of failures.

Because of these properties, relational databases are used in applications that require high accuracy and for transactional queries such as financial and retail transactions. For example: In banking when a customer makes a funds transfer request, you want to make sure the transaction is possible and it actually happens on the most up-to-date account balance, in this case an error or resubmit request is likely fine.

There are three relational database options in Google Cloud: Cloud SQL, Cloud Spanner, and Bare Metal Solution.

Cloud SQL: Provides managed MySQL, PostgreSQL and SQL Server databases on Google Cloud. It reduces maintenance cost and automates database provisioning, storage capacity management, back ups, and out-of-the-box high availability and disaster recovery/failover. For these reasons it is best for general-purpose web frameworks, CRM, ERP, SaaS and e-commerce applications.

Cloud Spanner: Cloud Spanner is an enterprise-grade, globally-distributed, and strongly-consistent database that offers up to 99.999% availability, built specifically to combine the benefits of relational database structure with non-relational horizontal scale. It is a unique database that combines ACID transactions, SQL queries, and relational structure with the scalability that you typically associate with non-relational or NoSQL databases. As a result, Spanner is best used for applications such as gaming, payment solutions, global financial ledgers, retail banking and inventory management that require ability to scale limitlessly with strong-consistency and high-availability. 

Bare Metal Solution: Provides hardware to run specialized workloads with low latency on Google Cloud. This is specifically useful if there is an Oracle database that you want to lift and shift into Google Cloud. This enables data center retirements and paves a path to modernize legacy applications. 

Non-relational databases

Non-relational databases (or NoSQL databases) store compex, unstructured data in a non-tabular form such as documents. Non-relational databases are often used when large quantities of complex and diverse data need to be organized. Unlike relational databases, they perform faster because a query doesn’t have to access several tables to deliver an answer, making them ideal for storing data that may change frequently or for applications that handle many different kinds of data. 

For example, an apparel store might have a database in which shirts have their own document containing all of their information, including size, brand, and color with room for adding more parameters later such as sleeve size, collars, and so on.

Qualities that make NoSQL databases fast:

• Eventual consistency: stores usually exhibit consistency at some later point (e.g., lazily at read time)
• Horizontal scaling, usually using hashed distributions
• Typically, they are optimized for a specific workload pattern (i.e., key-value, graph, wide-column)
• Typically, they don’t support cross shard transactions or flexible isolation modes.

Because of these properties, non-relational databases are used in applications that require large scale, reliability, availability, and frequent data changes.They can easily scale horizontally by adding more servers, unlike some relational databases, which scale vertically by increasing the machine size as the data grows. Although, some relations databases such as Cloud Spanner support scale-out and strict consistency.

Non-relational databases can store a variety of unstructured data such as documents, key-value, graphs, wide columns, and more. Here are your non-relational database options in Google Cloud: 

• Document databases: Store information as documents (in formats such as JSON and XML). For example: Firestore
• Key-value stores: Group associated data in collections with records that are identified with unique keys for easy retrieval. Key-value stores have just enough structure to mirror the value of relational databases while still preserving the benefits of NoSQL. For example: Datastore, Bigtable, Memorystore
• In-memory database: Purpose-built database that relies primarily on memory for data storage. These are designed to attain minimal response time by eliminating the need to access disks. They are ideal for applications that require microsecond response times and can have large spikes in traffic. For example: Memorystore
• Wide-column databases: Use the tabular format but allow a wide variance in how data is named and formatted in each row, even in the same table. They have some basic structure while preserving a lot of flexibility. For example: Bigtable
• Graph databases: Use graph structures to define the relationships between stored data points; useful for identifying patterns in unstructured and semi-structured information. For example: JanusGraph

There are three non-relational databases in Google Cloud:

• Firestore: Is a serverless document database which scales on demand and acts as a backend-as-a-service. It is DBaaS that increases the speed of building applications. It is perfect for all general purpose uses cases such as ecommerce, gaming, IoT and real time dashboards. With Firestore users can interact with and collaborate on live and offline data making it great for real-time application and mobile apps.  
• Cloud Bigtable: Cloud Bigtable is a sparsely populated table that can scale to billions of rows and thousands of columns, enabling you to store terabytes or even petabytes of data. It is ideal for storing very large amounts of single-keyed data with very low latency. It supports high read and write throughput at sub-millisecond latency, and it is an ideal data source for MapReduce operations. It also supports the open-source HBase API standard to easily integrate with the Apache ecosystem including HBase, Beam, Hadoop and Spark along with Google Cloud ecosystem.
• Memorystore: Memorystore is a fully managed in-memory data store service for Redis and Memcached at Google Cloud. It is best for in-memory and transient data stores and automates the complex tasks of provisioning, replication, failover, and patching so you can spend more time coding. Because it offers extremely low latency and high performance, Memorystore is great for web and mobile, gaming, leaderboard, social, chat, and news feed applications.

Conclusion

Choosing a relational or a non-relational database largely depends on the use case. Broadly, if your application requires ACID transactions and your data structure is not going to change much, select a relational database. 

In Google Cloud use Cloud SQL for any general-purpose SQL database and Cloud Spanner for large-scale globally scalable, strongly consistent use cases. In general, if your data structure may change later and if scale and availability is a bigger requirement than consistency then a non-relational database is a preferable choice.  Google Cloud offers Firestore, Memorystore, and Cloud Bigtable to support a variety of use cases across the document, key-value, and wide column database spectrum.

For more comparison resources on each database check out the overview. For more hands-on experience with Bigtable, check out our on-demand training here and learn about migrating databases to managed services check out this whitepaper.  

https://youtube.com/watch?v=2TZXSnCTd7E%3Fenablejsapi%3D1%26

For more #GCPSketchnote, follow the GitHub repo. For similar cloud content follow me on Twitter @pvergadia and keep an eye out on thecloudgirl.dev.

Weather forecasting and climate modeling are two of the world’s most computationally complex and demanding tasks. Further, they’re extremely time-sensitive and in high demand — everyone from weekend travelers to large-scale industrial farming operators wants up-to-date weather predictions. To provide timely and meaningful predictions, weather forecasters usually rely on high performance computing (HPC) clusters hosted in an on-premises data center. These on-prem HPC systems require significant capital investment and have high long-term operational costs. They consume a lot of electricity, have largely fixed configurations, and the underlying computer hardware is replaced infrequently.

Using the cloud instead offers increased flexibility, constantly refreshed hardware, high reliability, geo-distributed compute and networking, and a “pay for what you use” pricing model. Ultimately, cloud computing allows forecasters and climate modelers to provide timely and accurate results on a flexible platform using the latest hardware and software systems, in a cost effective manner. This is a big shift compared with traditional approaches to weather forecasting, and can appear challenging. To help, weather forecasters can now run the Weather Research and Forecasting (WRF) modeling system easily on Google Cloud using the new WRF VM image from Fluid Numerics, and achieve the performance of an on-premises supercomputer for a fraction of the price. With this solution, weather forecasters can get a WRF simulation up and running on Google Cloud in less than an hour!

A closer look at WRF
Weather Research and Forecasting (WRF) is a popular open-source numerical weather prediction modeling system used by both researchers and operational organizations. While WRF is primarily used for weather and climate simulation, teams have extended it to support interactions with chemistry, forest fire modeling, and other use cases. WRF development began in the late 1990s through a collaboration between the National Center for Atmospheric Research (NCAR), National Oceanic and Atmospheric Administration (NOAA), U.S. Air Force, Naval Research Laboratory, University of Oklahoma, and the Federal Aviation Administration. The WRF community comprises more than 48,000 users spanning over 160 countries, with the shared goal of supporting atmospheric research and operational forecasting.

The Google Cloud WRF image is built using Google’s MPI best practices for HPC, with the exception that hyperthreading is not disabled by default, and is easily integrated with other HPC solutions on Google Cloud, including SchedMD’s Slurm-GCP. Normally, installing WRF and its dependencies is a time consuming process. With these new WRF VM images, deploying a scalable HPC cluster with WRF v4.2 pre-installed is quick and easy with our Codelab. OpenMPI 4.0.2 was used throughout this work. Google has had good success with Intel MPI, and we intend to study whether further performance gains can be achieved in this context.

Optimizing WRF

Determining the optimal architecture and build settings for performance and cost was a key part of the process in developing the WRF images. We evaluated how to select the ideal compiler, right CPU platform, and the best file system for handling file IO, so you don’t have to. As a test case for assessing performance, we used the CONUS 2.5km benchmark.

Below, the CONUS 2.5km runtime and cost figure shows the run time required for simulating WRF over a two-hour forecast using 480 MPI ranks (a way of numbering processes) for different machine types available on Google Cloud. For each machine type, we’re showing the lowest measured run time from a suite of tests that varied compiler, compiler optimizations, and task affinity.

We found that compute-optimized c2 instances provided the shortest run time. The Slurm job scheduler allows you to map the MPI tasks to compute hardware using task affinity flags. When optimizing the runtime and cost for each machine type, we compared using srun –map-by core –bind-to core to launch WRF, which maps each MPI process to a physical core (two vCPU per MPI rank), and srun –map-by thread –bind-to thread, which maps each MPI process to a single vCPU. Mapping by core and binding MPI ranks to cores is akin to disabling hyperthreading.

The ideal simulation cost and runtime for CONUS 2.5km for each platform is found when each MPI rank is subscribed to each vCPU. When binding to vCPUs, half as many compute resources are needed when compared to binding to physical cores lowering the per-second cost for the simulation. For CONUS 2.5km, we also found that although mapping MPI ranks to cores results in reduced runtime for the same number of MPI ranks, the performance gains are not significant enough to outweigh the cost savings. For this reason, the WRF-GCP solution does not disable hyperthreading by default.

Runtime and simulation cost can be further reduced by selecting an ideal compiler: the figure below (CONUS 2.5km Compiler Comparisons) shows the simulation runtime for the WRF CONUS 2.5km benchmark on eight c2-standard-60 instances, using GCC 10.30, GCC 11.2.0 and the Intel® OneAPI® compilers (v2021.2.0). In all cases, WRF is built using level 3 compiler optimizations and Cascade Lake target architecture flags. By compiling WRF with the Intel® OneAPI® compilers, the WRF simulation runs about 47% faster than the GCC builds, and at about 68% of the cost, on the same hardware. We’ve used OpenMPI 4.0.2 with each of the compilers as the MPI implementation in this work. With other applications, Google has seen good performance with Intel MPI 2018, and we intend to investigate performance comparisons with this and other MPI implementations.

File IO in WRF can become a significant bottleneck as the number of MPI ranks increases. Obtaining the optimal file IO performance requires using parallel file IO in WRF and leveraging a parallel file system such as Lustre.

Below, we show the speedup in file IO activities relative to serial IO on an NFS file system. For this example, we are running the CONUS 2.5km benchmark on c2-standard-60 instances with 960 MPI ranks. By changing WRF’s file IO strategy to parallel IO, we accelerate file IO time by a factor of 60.

We further speed up IO and reduce simulation costs by using a Lustre parallel file system deployed from open-source Lustre Terraform infrastructure-as-code from Fluid Numerics. Lustre is also available with support from DDN’s EXAScaler solution in the Google Cloud Marketplace. In this case, we use four n2-standard-16 instances for the Lustre Object Storage Server (OSS) instances, each with 3TB of Local SSD. The Lustre Metadata Server (MDS) is an n2-standard-16 instance with a 1TB PD-SSD disk. After mounting the Lustre file system to the cluster, we set the Lustre stripe count to 4 so that file IO can be distributed across the four OSS instances. By switching to the Lustre file system for IO, we speed up file IO by an additional factor of 193, which is orders of magnitude faster than a single NFS server with serial IO.

Adding compute resources and increasing the number of MPI ranks reduces the simulation run time. Ideally, with perfect linear scaling, doubling the number of MPI ranks would cut the simulation time in half. However, adding MPI ranks also increases communication overhead, which can increase the cost per simulation. The communication overhead is due to the increased amount of communication necessitated by splitting the problem more finely across more machines.

To assess the scalability of WRF for the CONUS 2.5km benchmark, we can execute a series of model forecasts where we successively double the number of MPI ranks. Below, we show two- hour forecasts on the c2-standard-60 instances with the Lustre file system, varying the number of MPI ranks from 480 to 1920. In all of these runs, MPI ranks are bound to vCPUs so that the number of vCPUs dedicated to each simulation increases with the increase in MPI ranks. While many HPC workloads run best with simultaneous multithreading (SMT) disabled, we find the best performance for CONUS 2.5km with SMT enabled. Thus, the number of MPI ranks in our runs equals the total number of vCPUs.

As you can see, the CONUS 2.5km Runtime & Cost Scaling figure shows that the run time (blue bars) decreases as the number of MPI ranks and the amount of compute resources increases, at least up to 1920 ranks. When transitioning from 480 to 960 MPI ranks, the run time drops, yielding a speedup of about 1.8x. Doubling again to 1920 MPI ranks, though, we obtained an additional speedup of just 1.5x. This declining trend in the speedup with increasing MPI ranks is a signature of MPI overhead, which increases with more MPI ranks.

Determining your best fit

Most tightly-coupled MPI applications such as WRF exhibit this kind of scaling behavior, where scaling efficiency decreases with increasing MPI ranks. This makes assessing cost-scaling alongside performance-scaling critical when considering Total Cost of Ownership (TCO). Thankfully, per-second billing on Google Cloud makes this kind of analysis a little bit easier. As shown above, a second doubling of the count from 960 cores to 1920 cores can provide an additional 1.5x speedup, but at a 32% higher cost. In some circumstances, this faster turnaround may be needed and worth the extra cost.

If you want to get started with WRF quickly and experiment with the CONUS 2.5km benchmark, we’ve encapsulated this deployment in Terraform scripts and prepared an accompanying codelab.

You can learn more about Google Cloud’s high performance computing offerings at https://cloud.google.com/hpc, and you can find out more about Google’s partner Fluid Numerics at https://www.fluidnumerics.com.

Working to put food on all of our tables, today’s farmers are facing a higher amount of instability from input supply chain issues to weather patterns. Adding to this challenge are problems farmers face when trying to correctly time grain purchases, sales and transport. Farmers have always been stewards of the land, but now the demand for sustainable products has them needing to better prove their regenerative practices.

At Bushel, we understand these problems can’t be solved overnight or by a single company. We focus on empowering agribusinesses and farmers to work even more closely together to build a more sustainable agricultural supply chain by rapidly responding to market changes. With Bushel, farmers can track market prices in real time, instantly buy and sell grain, analyze inventory and transactions, and securely share verified information with grain operators and other producers. We provide the digital tools to streamline how farmers buy and sell commodities throughout the agricultural industry’s supply chain to help address market inefficiencies that can lead to waste, and have the information and resources to help them flex and adapt as complexity increases in farming operations.

Approximately 40% to 50% of all U.S. grain transactions now pass through the Bushel platform. As we continue to grow, Bushel continues to focus on what digital tEmpowering Farmers: Bushel Drives 40-50% of US Grain Transactions, Fueling Sustainable Agricultureools can support each point in the supply chain. Many focus on the first mile at the farm or last mile at the store. But Bushel is focused on modernizing the middle where grain purchasing and processing sit. We aim to help local grain industries and stabilize regional agricultural supply chains.

Starting with a simple mobile app; now scaling into an agricultural ecosystem

Bushel began its journey in 2017 as a small-scale platform for farmers that delivered grain contracts, cash bids, and receipts. As Bushel evolved into a comprehensive agricultural ecosystem, we realized we needed knowledgeable technology partners to help us rapidly scale while saving time and administrative costs. That’s why we started partnering with the Google for Startups Cloud Program to get support from Google and work with Google Cloud Managed Services partner, DoiT International to help support our use of GKE and create a multi-regional deployment as well as migrate our CUDs to new Compute Engine families and continue to optimize our footprint. We’ll also use DoiT’s Flexsave technology to reduce the management overhead of CUDs in the future.

In just one year, we expanded to over 1,200 live grain receiving locations and quickly grew our services portfolio with electronic signature capabilities, commodity balances, and web development. Because that relationship between farmer and agribusiness is so important, we provide more than 200 grain companies with white-labled digital experiences so each farmer sees their local grain facility they do business with on both desktop and mobile. To further our extension into the digital infrastructure of agriculture, we subsequently acquired GrainBridge and FarmLogs to help farmers handle specific jobs and tasks, and provide the needed insights to improve their business operations. Over 2,000 grain receiving locations across the United States and Canada now use Bushel products. We accomplished all this on Google Cloud.

We leverage the secure-by-design infrastructure to protect millions of financial transactions and keep sensitive customer data safe. Our data is processed and stored in Google’s secure data centers, which maintain adherence to a number of compliance frameworks. We utilize Google Kubernetes Engine extensively as it reduces operational overhead and offers auto scaling up to 15,000 nodes.

Database provisioning, storage capacity management, and other time-consuming tasks are automated with our Cloud SQL usage. Query Insights for Cloud SQL streamlines database observability and seamlessly integrates with existing apps and Google Cloud services such as GKE and BigQuery.

Empowering farmers and agribusinesses in North America

The Google Cloud Account Team had been instrumental in helping Bushel build an expansive agricultural platform that powers APIs, apps, websites, and digital solutions. Google’s startup experts are incredibly responsive, with deep technical knowledge that can’t be found elsewhere. Google Cloud also has provided us credits to explore new ways of analyzing the vast amounts of data we generate, verify, and transfer with solutions such as BigQuery and Pub/Sub.

With BigQuery, we can run analytics at scale with 26%–34% lower three-year TCO than cloud data warehouse alternatives. BigQuery delivers actionable insights on a highly secure and scalable platform, includes built-in machine learning capabilities, and integrates with Pub/Sub to ingest and stream analytic events via Dataflow.

With Bushel, farmers across North America are rapidly responding to sudden market changes by tracking grain prices in real time and instantly buying and selling crops. We see a future where this business information becomes insights – where a farmer can not just know where to sell their grain, but when to sell. The burden right now to engage with carbon markets is high, full of paper-based binders and verification forms. We see a world where farming practices recorded digitally can be permissioned along the supply chain for a better picture of how our food is grown.

With the Bushel platform, millions of farmers around the world will have the digital tools to modernize local grain industries, build more sustainable agricultural supply chains, and help to address global food inequity.

If you want to learn more about how Google Cloud can help your startup, visit our page here to get more information about our program, and sign up for our communications to get a look at our community activities, digital events, special offers, and more.