[MUSIC PLAYING]
JOLENE TEO: Ever wonder how growing startups are leveraging
Google Cloud database services?
Stay tuned to find out more.
Hi, everyone.
I'm Jolene Teo, and I'm a customer engineer
at Google Cloud.
And welcome back to the Google Cloud Technical
Guides for Startups, where we are creating a series of videos
for technical enablement to help startups to start, build,
and grow their businesses successfully and sustainably
on Google Cloud.
In our previous episode, we covered
networking configurations.
And moving on to today's episode,
episode seven of the series, we will
be going through databases, with a focus on Cloud SQL.
For today, we're going to first go
through the big picture on databases
and when to choose which.
We'll then deep dive into Cloud SQL
and also run through a demo on Cloud SQL,
as well as how it interacts with some of the other services
in Google Cloud.
I'll then share with you some tips and tricks around cost
as well as performance optimization.
We'll then go through a sample architecture, as well as
a customer success story.
And then we'll finally wrap up with some links you can go
through with after this video.
All right, so let's get started.
Let me give you an overview on how Google Cloud can help you
with your database needs.
As you can see, there is a full suite
of database services available to meet
your different requirements.
Over here, you can see highlights of each type,
from in-memory, to relational, to non-relational,
as well as what each service is good for.
Not only do we have our Google-built,
Cloud-native database services where
you can use MySQL or PostgreSQL for ease of migration
and compatibility, we also do support traditional databases
such as SQL Server.
And finally, to complete the portfolio,
we also work very closely with our partners,
such as MongoDB, Confluent, and many more,
to provide you with their integrated support.
At this point, you must be wondering,
how do you go about choosing which database service, then?
Well, I've got your back.
Here is a flowchart to help you out
with your startup's database service decision making.
Depending on your application, you
might need one or several of these services available.
Now, because many startups leverage SQL
databases for the transactional processing
in their applications, which Cloud SQL supports,
today we're going to zoom into database configurations
with Cloud SQL.
Building, running, and maintaining infrastructure
means less time creating value for your app.
Run the same relational databases
that you know without the hassle of self-management.
This frees up time for you to focus
on building great applications for your startup.
Cloud SQL is a fully managed database service
where Google takes care of the administrative tasks required
to run a database, making it easy for you
and simple to operate.
Cloud SQL also has full compatibility with MySQL,
PostgreSQL, and SQL Server.
With Cloud SQL, you can easily create read replicas
and backups, and there is also automatic failover,
ensuring that your database will always
be available when you need it.
Cloud SQL also offers easy integration with just
about any application, from Google Kubernetes Engine
to Compute Engine, and many more.
Open up analytics possibilities with Cloud SQL's
native integration with BigQuery,
where BigQuery can directly query from your Cloud SQL
databases, running federated queries,
enabling you to not need to move your data.
Cloud SQL resides in secure Google data centers, ensuring
business continuity, with reliable and secure services
backed by a 24/7 SRE team with a 99.95% SLA.
There is also automatic data encryption
at rest and in transit.
You can also easily migrate your databases to Cloud SQL
with minimal downtime using the Database Migration Service.
Lastly, monitor and troubleshoot with Cloud SQL Insights, which
is a simple and open tool that can help developers
to understand database performance issues on Cloud
SQL.
Maintenance is also very easy with automated patching.
To dive a little deeper with Cloud SQL databases,
Cloud SQL supports read replicas and cross-region read replicas.
You can leverage replication to horizontally scale
your application needs.
To highlight, you can leverage cross-region read replicas
for disaster recovery.
Cloud SQL also has a built-in high availability option,
which helps you to reduce downtime
when a zone or instance becomes unavailable.
With high availability, your data
will still continue to be available to client
applications.
Cloud SQL also provides you with both automated and on-demand
backups with point-in-time recovery.
Backups help to protect your data from loss or damage.
You can create a backup any time you want on demand,
and this could come in really handy
when you're may be about to perform a risky operation
on your database.
You can also enable automatic backups.
Where possible, schedule your backups
where your instance has the least activity.
Right, it's about time to give you
a tour on how it looks like.
Now on to the demo.
We are going to continue on with the guestbook application demo.
In this demo, I'm going to show you
how you can create your Cloud SQL instances
and then populate them with data being
imported from Cloud Storage.
We'll then explore some SQL statements within Cloud Shell,
and then we'll connect to BigQuery as well
as Data Studio.
And last but not least, we'll wrap up
with a connection to Google Compute Engine.
All right, let's get started.
For the first part of the demo, we'll
be going through how to create Cloud SQL instances.
Now moving into the Console, first, we're
going to head over to Cloud SQL and click
on the Create Instance button to create a new instance.
You can easily configure your new instance
according to exactly how you want it to be like.
First, you're going to be prompted
to select your database engine.
Over here you can see the different options available,
and for this demo, I'm going to just select PostgreSQL.
Next, give your instance an instance ID,
and just remember that it has to be unique within the project.
And then following that, put in a password.
And following that, you can also choose a region
to be either single zone or multi zone.
By default, it's multi zone.
And you can select the zone for the primary and failover
instance.
If you do not have specific requirements,
feel free to leave it as Any.
And on the right-hand column, you
can see a summary of the different options
and configurations you have selected.
At this point, you can already create your Cloud SQL instance.
If not, you can continue on to configure the advanced
configurations.
You can select your machine type,
be it whether is it standard, lightweight, shared cores,
or high memory.
You can also select the Custom option
to uniquely make selections for your memory as well as cost.
As far the storage type, the typical selection
is SSD for lower latency and higher throughput.
However, you can also make the selection
and switch over to HDD.
You can choose your storage capacity--
I'll select Custom-- if you intend to go beyond 200 GB.
At the same time, you can check to Enable Automatic Storage
Increases, where if you run out of space,
Google will allocate additional storage to you.
As for connections, you have options
for public IP or private IP.
In this case, I'm just going to go ahead to select both.
For automated backups, you can select a window of time
for this to occur.
It would be best to select the non-peak hours
of your business, where there is lower traffic to your SQL
instances.
Similarly, for patching, upgrades, and maintenance,
you can also select the non-peak hours.
And lastly, you can also set up database flags
as well as make use of labels, which
are really good for billing as well as tracking where
your costs are coming from.
All right, and now we're good to go.
I'm just going to hit Create, and we're just
going to speed up this part until the instance is
being created.
All right, our Cloud SQL instance is up and running.
Moving on to the next section in this demo,
I'm going to show you how you can import data into your Cloud
SQL instance, and we're also going
to connect to it to read the data.
In this case, we will be using guest data for this demo.
So first, we're going to create a database by going over
to the Database tab to create a new database called
guestbook-db.
Over here, I have my Cloud SQL instance.
And first, I'm going to select Import, which
is the button right at the top.
For source, I'm going to select the .sql file that have already
imported into Cloud Storage, and this file is what's going to be
creating the tables.
For the format, I'm going to select SQL and also include
the destination.
And following that, I'm going to repeat this process
to import the data to populate those tables.
In this part of the demo, we're going
to be exploring the imported data using
SQL statements in Cloud Shell.
Now we're going to connect to the SQL instance via Cloud
Shell simply by selecting the button
Connect Using Cloud Shell.
Enter the command to connect and authorize.
And we're going to see this pop up a little bit as well.
And then following that, also enter
in your password to connect.
From here, we can now run a couple of queries.
So first we can list the databases,
and then we can select one.
And finally, we can then list, perhaps, the guests.
All right, and we are done.
Now I'm going to show you how to connect to BigQuery and Data
Studio.
Right, so first, move to the BigQuery page,
add external data source, and then select
your connection type.
In this case, I'm just going to go ahead and select Postgres,
and then give it a name.
Select your location.
Over here, I'm just going to go ahead with us-central1.
And then following that, copy and paste
your Cloud SQL instance ID.
Enter in the password as well.
And then click to create the connection.
You should also see the connection being created
in the left navigation menu.
All right.
And from here, you can just start querying.
In this example query itself, you
can see the external query function as well as
the connection ID.
And this makes it very easy for you to query directly
from Cloud SQL within BigQuery for running
federated queries easily.
On top of data analysis in BigQuery,
you can also easily create a connection
to Data Studio for data visualization.
There are many connectors available,
as you can see over here.
And for this demo, we're going to select the Postgres
connector.
And then we're going to create a new report.
From here, you can easily add charts.
And I'm just going to speed up this part
and show you the end result of how visualization
can look like for you.
All right, and we're done.
Here's a simple dashboard to show you
how you can use data visualization in connection
to Cloud SQL with Data Studio.
And finally, in this demo section,
I'm going to show you how you can connect Cloud SQL back
with Google Compute Engine.
All right, so now we're going to head over
to Google Compute Engine.
And then we're going to SSH in to set up the Cloud SQL proxy.
So first, we're going to make sure that there
are permissions by uploading the service account key that
has both the Cloud SQL client as well as edit [? rules. ?] Next,
we'll then download the Cloud SQL auth proxy,
and also make the Cloud SQL auth proxy executable.
We will then install the PostgreSQL client
onto the Compute Engine instance,
and then start the PostgreSQL auth proxy.
And finally, you can connect to the PSQL client.
And following that, you can already
begin to start querying.
We're going to run a couple of queries
similar to earlier before to show you the connection.
All right, it looks awesome.
And we're done.
Now that you know how to configure your Cloud SQL
database, I'll be sharing with you
some of the best practices for cost, as well as
for performance optimization.
We will be going through some of the top tips,
and I'll also put some links in the description box
below for your further reading.
For performance optimization, first of all,
do consider a microservices architecture, sometimes known
as vertical sharing.
This refers to having multiple smaller databases
for each microservice you may have,
as compared to having one monolith database for all
of your services, which can help you to increase and optimize
your performance.
Remember that this is a general suggestion, and perhaps
in specific architectures, a monolithic architecture
may work for you.
Secondly, for your read-heavy workloads,
do remember to add read replicas to offload traffic
from your primary instance.
Optionally, you can also make use of a load balancer
like HAProxy to manage traffic to your replicas.
Lastly, use good connection management practices,
such as exponential backup and connection pooling.
Using these techniques will help you
to improve your application's use of resources
and also to stay within the Cloud SQL connection limits.
As for costs, the Cloud SQL idle instance recommender
helps to detect instances that might be idle.
It then provides you with recommendations and insights
on how you can reduce costs.
Next, the Cloud SQL over provisioned instance
recommender helps to detect instances
that are unnecessarily large for a given workload.
It then provides you with some recommendations on how
you can resize these instances.
Lastly, do leverage commit use discounts, which
are ideal for predictable workloads
on a one-year or three-year basis, which can give you
significant cost savings.
Now let's explore some of the use cases involving Cloud SQL.
As you can see over here, Cloud SQL
works across many use cases, from gaming to inventory
management, and many more.
Next, let's zoom into one example,
a sample fictitious gaming backend database architecture.
Here, we can see all of the different databases in action.
First, we have Cloud Firestore, a NoSQL document database,
which can be used for authentication as well
as the player database.
Cloud Memorystore is then used for storing
regional, in-memory caches, which speeds up the access
to frequently viewed data.
Cloud SQL or Cloud Spanner then helps do
the hosting of the game data.
You can also log events to Cloud Bigtable, Google Cloud's
NoSQL columnar database.
And finally, you can also connect to BigQuery
for data warehousing and gameplay analytics.
And if we wanted to take a look at a real life example,
we have Krikey.
Krikey is a mobile AR gaming startup
that leverages on Google Cloud to bring diversity
into the mobile AR gaming space.
Krikey migrated to Cloud SQL and also uses
some of the other Google Cloud services, which
has helped them to reduce their rendering time for customers
and also increase the speed of Krikey games.
Using Cloud SQL has helped Krikey
to decrease the load time of videos,
improve the speed of games, resulting
in higher app engagement times.
All in all, I hope that this video has given you
a good understanding of Google Cloud database services
and how you can get started on Cloud SQL.
And after this video, if you are still keen to find out more
and cannot get enough of databases and Cloud SQL,
do check out the links in the description box below.
In our next episode, you will be learning all about Firebase.
We'll first kick off with an overview of what Firebase
is, how you can build better applications,
improve application quality, as well as drive user engagement.
We'll then go through a demo as well as a customer success
story.
All right, and that's a wrap.
See you in the next video.
[MUSIC PLAYING]