Ship Google Cloud SQL metrics

• Overview
• MySQL
• PostgreSQL

Overview

Google Cloud SQL is a fully-managed database service that is used to set up, maintain, manage, and administer MySQL and PostgreSQL databases in the cloud. Telegraf is a plug-in driven server agent for collecting and sending metrics and events from databases, systems and IoT sensors.

To send your Prometheus-format Google Cloud SQL metrics to Layerlog, you need to add the inputs.stackdriver and outputs.http plug-ins to your Telegraf configuration file.

Configuring Telegraf to send your metrics data to Layerlog

Before you begin, you’ll need: GCP project

Set relevant credentials in GCP

1. Navigate to the Project selector and choose the project to send metrics from.
2. In the Service account details screen, give the service account a unique name and select Create and continue.
3. In the Grant this service account access to project screen, add the following roles: Compute Viewer, Monitoring Viewer, and Cloud Asset Viewer.
4. Select Done.
5. Select your project in the Service accounts for project list.
6. Select KEYS.
7. Select Keys > Add Key > Create new key and choose JSON as the type.
8. Select Create and Save.

You must be a Service Account Key Admin to select Compute Engine and Cloud Asset roles.

Add an environment variable for the key

On your machine, run:

export GOOGLE_APPLICATION_CREDENTIALS=<<PATH-TO-YOUR-GCP-KEY>>

Replace <<PATH-TO-YOUR-GCP-KEY>> with the path to the JSON file created in the previous step.

Set up Telegraf v1.17 on a dedicated machine

For Windows:

wget https://dl.influxdata.com/telegraf/releases/telegraf-1.19.2_windows_amd64.zip

After downloading the archive, extract its contents into C:\Program Files\Logzio\telegraf\.

The configuration file is located at C:\Program Files\Logzio\telegraf\.

For MacOS:

brew install telegraf

The configuration file is located at /usr/local/etc/telegraf.conf.

For Linux:

Ubuntu & Debian

sudo apt-get update && sudo apt-get install telegraf

The configuration file is located at /etc/telegraf/telegraf.conf.

RedHat and CentOS

sudo yum install telegraf

The configuration file is located at /etc/telegraf/telegraf.conf.

SLES & openSUSE

# add go repository
zypper ar -f obs://devel:languages:go/ go
# install latest telegraf
zypper in telegraf

The configuration file is located at /etc/telegraf/telegraf.conf.

FreeBSD/PC-BSD

sudo pkg install telegraf

The configuration file is located at /etc/telegraf/telegraf.conf.

Add the inputs.stackdriver plug-in

First you need to configure the input plug-in to enable Telegraf to scrape the GCP data from your hosts. To do this, add the below code to the configuration file.

[[inputs.stackdriver]]

  project = "<<YOUR-PROJECT>>"

  metric_type_prefix_include = [
    "cloudsql.googleapis.com",
  ]

  interval = "1m"

• Replace <<YOUR-PROJECT>> with the name of your GCP project.

The full list of data scraping and configuring options can be found here

Add the outputs.http plug-in

After you create the configuration file, configure the output plug-in to enable Telegraf to send your data to Layerlog in Prometheus-format. To do this, add the following code to the configuration file:

[[outputs.http]]
  url = "https://<<LISTENER-HOST>>:8053"
  data_format = "prometheusremotewrite"
  [outputs.http.headers]
     Content-Type = "application/x-protobuf"
     Content-Encoding = "snappy"
     X-Prometheus-Remote-Write-Version = "0.1.0"
     Authorization = "Bearer <<PROMETHEUS-METRICS-SHIPPING-TOKEN>>"

Replace the placeholders to match your specifics. (They are indicated by the double angle brackets << >>):

• Replace <<PROMETHEUS-METRICS-SHIPPING-TOKEN>> with a token for the Metrics account you want to ship to.
  Here’s how to look up your Metrics token.
• Replace <<LISTENER-HOST>> with the Layerlog Listener URL for your region, configured to use port 8052 for http traffic, or port 8053 for https traffic. For example, listener.layerlog.com if your account is hosted on AWS US East, or listener-nl.layerlog.com if hosted on Azure West Europe.

Start Telegraf

On Windows:

telegraf.exe --service start

On MacOS:

telegraf --config telegraf.conf

On Linux:

Linux (sysvinit and upstart installations)

sudo service telegraf start

Linux (systemd installations)

systemctl start telegraf

Check Layerlog for your metrics

Give your metrics some time to get from your system to ours.

Log in to your Layerlog account and navigate to the current instructions page inside the Layerlog app. Install the pre-built dashboard to enhance the observability of your metrics.

To view the metrics on the main dashboard, log in to your Layerlog Metrics account, and open the Layerlog Metrics tab.

Configuring Telegraf to send your metrics data to Layerlog

Before you begin, you’ll need: GCP project

Set relevant credentials in GCP

1. Navigate to the Project selector and choose the project to send metrics from.
2. In the Service account details screen, give the service account a unique name and select Create and continue.
3. In the Grant this service account access to project screen, add the following roles: Compute Viewer, Monitoring Viewer, and Cloud Asset Viewer.
4. Select Done.
5. Select your project in the Service accounts for project list.
6. Select KEYS.
7. Select Keys > Add Key > Create new key and choose JSON as the type.
8. Select Create and Save.

You must be a Service Account Key Admin to select Compute Engine and Cloud Asset roles.

Add an environment variable for the key

On your machine, run:

export GOOGLE_APPLICATION_CREDENTIALS=<<PATH-TO-YOUR-GCP-KEY>>

Replace <<PATH-TO-YOUR-GCP-KEY>> with the path to the JSON file created in the previous step.

Set up Telegraf v1.17 on a dedicated machine

For Windows:

wget https://dl.influxdata.com/telegraf/releases/telegraf-1.19.2_windows_amd64.zip

After downloading the archive, extract its contents into C:\Program Files\Logzio\telegraf\.

The configuration file is located at C:\Program Files\Logzio\telegraf\.

For MacOS:

brew install telegraf

The configuration file is located at /usr/local/etc/telegraf.conf.

For Linux:

Ubuntu & Debian

sudo apt-get update && sudo apt-get install telegraf

The configuration file is located at /etc/telegraf/telegraf.conf.

RedHat and CentOS

sudo yum install telegraf

The configuration file is located at /etc/telegraf/telegraf.conf.

SLES & openSUSE

# add go repository
zypper ar -f obs://devel:languages:go/ go
# install latest telegraf
zypper in telegraf

The configuration file is located at /etc/telegraf/telegraf.conf.

FreeBSD/PC-BSD

sudo pkg install telegraf

The configuration file is located at /etc/telegraf/telegraf.conf.

Add the inputs.stackdriver plug-in

First you need to configure the input plug-in to enable Telegraf to scrape the GCP data from your hosts. To do this, add the below code to the configuration file.

[[inputs.stackdriver]]

  project = "<<YOUR-PROJECT>>"

  metric_type_prefix_include = [
    "cloudsql.googleapis.com",
  ]

  interval = "1m"

• Replace <<YOUR-PROJECT>> with the name of your GCP project.

The full list of data scraping and configuring options can be found here

Add the outputs.http plug-in

After you create the configuration file, configure the output plug-in to enable Telegraf to send your data to Layerlog in Prometheus-format. To do this, add the following code to the configuration file:

[[outputs.http]]
  url = "https://<<LISTENER-HOST>>:8053"
  data_format = "prometheusremotewrite"
  [outputs.http.headers]
     Content-Type = "application/x-protobuf"
     Content-Encoding = "snappy"
     X-Prometheus-Remote-Write-Version = "0.1.0"
     Authorization = "Bearer <<PROMETHEUS-METRICS-SHIPPING-TOKEN>>"

Replace the placeholders to match your specifics. (They are indicated by the double angle brackets << >>):

• Replace <<PROMETHEUS-METRICS-SHIPPING-TOKEN>> with a token for the Metrics account you want to ship to.
  Here’s how to look up your Metrics token.
• Replace <<LISTENER-HOST>> with the Layerlog Listener URL for your region, configured to use port 8052 for http traffic, or port 8053 for https traffic. For example, listener.layerlog.com if your account is hosted on AWS US East, or listener-nl.layerlog.com if hosted on Azure West Europe.

Start Telegraf

On Windows:

telegraf.exe --service start

On MacOS:

telegraf --config telegraf.conf

On Linux:

Linux (sysvinit and upstart installations)

sudo service telegraf start

Linux (systemd installations)

systemctl start telegraf

Check Layerlog for your metrics

Give your metrics some time to get from your system to ours.

Log in to your Layerlog account and navigate to the current instructions page inside the Layerlog app. Install the pre-built dashboard to enhance the observability of your metrics.

To view the metrics on the main dashboard, log in to your Layerlog Metrics account, and open the Layerlog Metrics tab.