Analyze the performance of an AlloyDB instance with pgbench

In this tutorial, you integrate an AlloyDB instance and a Compute Engine VM instance on a Virtual Private Cloud (VPC) network. You then verify the integration by installing pgbench on the Compute Engine VM instance, and use this benchmarking tool to conduct performance tests against the AlloyDB instance.

Objectives

In this tutorial, you learn how to:

Create a VPC network for your AlloyDB instance and your Compute Engine VM instance so that communication can pass between the instance and the VM securely.
Create an AlloyDB instance and a Compute Engine VM instance, and assign and configure your VPC network for both instances.
Install pgbench on the Compute Engine VM instance, run it against the AlloyDB instance, and use it to conduct performance tests against the AlloyDB instance.

Costs

This tutorial uses billable components of Google Cloud, including VPC network, AlloyDB, and Compute Engine. Use the pricing calculator to generate a cost estimate based on your projected usage.

Before you begin

In the Google Cloud Console, on the project selector page, select or create a Google Cloud project.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to the project selector page
Make sure that billing is enabled for your Google Cloud project. Learn how to confirm that billing is enabled for your project.
Enable the VPC network, AlloyDB, and Compute Engine APIs.

Enable the APIs
Make sure that you have the AlloyDB Admin role assigned to your user account.

Go to the IAM page

Create a VPC network

In this section, you create a VPC network for your AlloyDB instance and your Compute Engine VM instance so that communication can pass between the instance and the VM securely.

Go to the VPC networkspage in the Google Cloud Console.

Go to the VPC networks page
Click Create VPC network. The Create a VPC networkpage appears.
Enter my-vpc-network for the Nameof your VPC network.
In the Subnet creation modesection, select the Customoption.
Click ADD SUBNET.
Enter my-subnet for the Nameof your subnet.
Select a Regionand enter an IP address rangefor your subnet.
Click DONE.
Click CREATE.

Create an AlloyDB instance

In this section, you create an AlloyDB instance, and assign and configure your VPC network for the instance.

In the Google Cloud console, go to the Clusterspage.

Go to Clusters
Click Create cluster.
Configure your cluster as follows:
1. In the Cluster IDfield of the Basic infosection, enter my-cluster .
2. In the Passwordfield, enter any password you'd like. Take note of this password—you use it again later in this tutorial.
3. In the Regionfield of the Locationsection, select us-central1 (Iowa) .
4. In the Database versionfield, keep the default value.
5. From the Networkmenu, select my-vpc-network. You created this VPC network in Create a VPC network .
6. Because your VPC network hasn't yet initialized private service access, click Set up connection.
  
  Note: A private service connection enables your AlloyDB instance and the Compute Engine VM that you'll be creating to communicate exclusively by using internal IP addresses.
7. In the Create a private service connectionpane that appears:
  1. Select the Use an automatically allocated IP rangeoption to have Google Cloud select an automatically allocated IP range in your network.
  2. Click Continue.
  3. Click Create connection.
  Note: It might take a few seconds for your private service connection to be created. After this happens, the Create a private service connectionpane disappears, and the Private service connection for network [VPC-network-name] is successfully created.status message appears.
Configure your primary instance as follows:
1. In the Instance IDfield, enter an ID for your primary instance.
2. Under Zonal availability, select one of the following options:
  1. To create a highly available production instance with automated failover, select Multiple zones (Highly available).
  2. To create a basic instance that does not need to be highly available, select Single zone.
3. Select a machine type.
Click Create clusterto create the cluster and the primary instance together.

Note: It may take a few minutes for your AlloyDB instance to be created.
Make a note of the private IP address of your AlloyDB instance that appears on the AlloyDB Instancespage. You'll need this address to verify the integration between the AlloyDB instance and the Compute Engine VM instance.

Create a Compute Engine VM instance

In this section, you create a Compute Engine VM instance, and assign and configure your VPC network for the instance.

Go to the VM instancespage in the Google Cloud Console.

Go to the VM instances page
Click Create instance.
In the Namefield of the Create an instancepage, enter a unique identifier for the Compute Engine VM instance that you're creating. For this tutorial, enter my-compute-engine-vm-instance in the field.
In the Access scopessection of the page, select the Allow full access to all Cloud APIsoption.
Expand Advanced options, and then complete the following steps:
1. Click the Networkingsubtab.
2. Click Add a network interface.
3. From the Networkmenu, select my-vpc-network. You created this VPC network in Create a VPC network .
4. Click Done. Two VPC networks appear in the Network interfacessection of the page: defaultand my-vpc-network
Click Deleteto delete the defaultVPC network.

By deleting the defaultVPC network, you force your Compute Engine VM instance to use the VPC network that you created in this tutorial.
Click Create.

It may take a few seconds for your Compute Engine VM instance to be created. After this happens, the instance appears in the VM instancespage, and the status of this instance is Running(as indicated by the check mark).

Verify the integration

In this section, you install pgbench on the Compute Engine VM instance, run it against the AlloyDB instance, and use it to conduct performance tests against the AlloyDB instance.

Connect to your Compute Engine VM instance. To do so, click the SSHmenu to the right of your VM instance, and then select Open in browser window.
Install the Contributed Extensions and Additions to PostgreSQL package on your Compute Engine VM instance.
```
sudo  
apt-get  
-y  
install  
postgresql-contrib
```
Initialize a database for pgbench on your AlloyDB for PostgreSQL instance. For example, use the default postgres database:
```
pgbench  
-i  
--host = 
 INSTANCE_PRIVATE_IP 
  
--port = 
 PORT_NUMBER 
  
--username = 
 USERNAME 
```
Replace the following:
- INSTANCE_PRIVATE_IP : The private IP address of your AlloyDB instance. You made a note of this IP address when you created the instance .
- PORT_NUMBER : The port number reserved for your AlloyDB instance. For this tutorial, the port number is 5432 .
- USERNAME : The administrator username for your AlloyDB instance. For this tutorial, the username is postgres .
At the Password prompt, enter the password for your AlloyDB instance. When you created the instance , you either supplied a password for it or had AlloyDB generate a password for you.

Run pgbench with no arguments to verify basic features.

pgbench  
--host = 
 INSTANCE_PRIVATE_IP 
  
--port = 
 PORT_NUMBER 
  
--username = 
 USERNAME

At the Password prompt, enter the password for your AlloyDB instance.

You should see output similar to the following:

starting  
vacuum...end.
transaction  
type:  
<builtin:  
TPC-B  
 ( 
sort  
of ) 
>
scaling  
factor:  
 1 
query  
mode:  
simple
number  
of  
clients:  
 1 
number  
of  
threads:  
 1 
number  
of  
transactions  
per  
client:  
 10 
number  
of  
transactions  
actually  
processed:  
 10 
/10
latency  
 average 
  
 = 
  
 3 
.794  
ms tps 
  
 = 
  
 263 
.560171  
 ( 
including  
connections  
establishing ) 
 tps 
  
 = 
  
 345 
.375423  
 ( 
excluding  
connections  
establishing )

Use some options to make a useful test. Some important options are -c (the number of clients) and --transactions (the number of transactions per client).

For this tutorial, conduct a performance test against 10 clients that are accessing your AlloyDB for PostgreSQL database and 100 transactions for each client.
```
pgbench  
--host = 
 INSTANCE_PRIVATE_IP 
  
--port = 
 PORT_NUMBER 
  
--username = 
 USERNAME 
  
-c  
 10 
  
--transactions = 
 100 
```

At the Password prompt, enter the password for your AlloyDB instance.

You should see output similar to the following:

starting  
vacuum...end.
transaction  
type:  
   
TPC-B  
 ( 
sort  
of ) 
>
scaling  
factor:  
 1 
query  
mode:  
simple
number  
of  
clients:  
 10 
number  
of  
threads:  
 1 
number  
of  
transactions  
per  
client:  
 100 
number  
of  
transactions  
actually  
processed:  
 1000 
/1000
latency  
 average 
  
 = 
  
 36 
.421  
ms tps 
  
 = 
  
 274 
.563444  
 ( 
including  
connections  
establishing ) 
 tps 
  
 = 
  
 275 
.733225  
 ( 
excluding  
connections  
establishing )

Clean up

After you've finished this tutorial, you can clean up the resources that you created so they won't take up quota and you won't be billed for them in the future.

To avoid incurring charges to your Google Cloud account for the resources used in this tutorial, follow these steps.

Use the Google Cloud console to delete your project, AlloyDB instance, Compute Engine VM instance, and VPC network.

The following sections describe how to delete or turn off these resources.

Delete your project

The easiest way to eliminate billing is to delete the project that you created for this tutorial.

Deleting a project has the following effects:

Everything in the project is deleted. If you used an existing project for this tutorial, then when you delete it, you also delete any other work you've done in the project.
Custom project IDs are lost. When you created this project, you might have created a custom project ID that you want to use in the future. To preserve the URLs that use the project ID, such as an appspot.com URL, delete selected resources inside the project instead of deleting the whole project.

If you plan to explore multiple tutorials and quickstarts, then reusing projects can help you avoid exceeding project quota limits.

Go to the Manage resourcespage in the Google Cloud Console.

Go to the Manage resources page
In the project list, select the project that you want to delete, and then click Delete.
In the dialog, type the project ID, and then click Shut downto delete the project.

Delete the AlloyDB instance

Go to the AlloyDB Instancespage in the Google Cloud Console.

Go to the AlloyDB Instances page
Click the name of your AlloyDB instance. For this tutorial, click my-instance.
Click DELETE.
In the dialog, enter the name of your AlloyDB instance in the text field, and then click DELETE.

Delete the Compute Engine VM instance

Go to the VM instancespage in the Google Cloud Console.

Go to the VM instances page
Click the name of your Compute Engine VM instance. For this tutorial, click my-compute-engine-vm-instance.
Click the Deleteicon. This icon resembles a trashcan.
In the dialog, click DELETE.

Note: It may take a few seconds for your Compute Engine VM instance to be deleted.

Delete the VPC network

Go to the VPC networkspage in the Google Cloud Console.

Go to the VPC networks page
Click the name of your VPC network. For this tutorial, click my-vpc-network.
Click DELETE VPC NETWORK.
In the dialog, click DELETE.