MySQL

Host metrics refer to the metrics collected from the operating system of the host where your applications are running. These metrics include CPU, memory, disk, and network usage. Understanding host metrics is crucial as it helps you identify potential problems or bottlenecks that could affect the overall performance of your applications.

In this tutorial, we will show you how to collect host metrics, send them to GreptimeDB and visualize them.

Create Service

To experience the full power of GreptimeCloud, you need to create a service which contains a database with authentication. Open the GreptimeCloud console, signup and login. Then click the New Service button and config the following:

• Service Name: The name you want to describe your service.
• Description: More information about your service.
• Region: Select the region where the database is located.
• Plan: Select the pricing plan you want to use.

Now create the service and we are ready to write some metrics to it.

Write data

Prerequisites

• MySQL CLI

Example Application

We will write a Bash script and showcase the core code to collect host metrics and send them to GreptimeDB. For reference, you can view the complete demo on GitHub.

To begin, create a new directory named quick-start-mysql to host our project. Then create a new file named quick-start.sh and make it executable:

touch quick-start.sh
chmod +x quick-start.sh

Write code to collect CPU and memory metrics and format the data into SQL format:

#!/bin/bash

generate_data()
{
 unameOut="$(uname -s)"
 case "${unameOut}" in
  Linux*)
   user_cpu_util=$(top -bn1 | grep "Cpu(s)" | awk '{print $2 + $4}')
   sys_cpu_util=$(top -bn1 | grep "Cpu(s)" | awk '{print $6}')
   idle_cpu_util=$(top -bn1 | grep "Cpu(s)" | awk -F "," '{print $4}' | awk -F " " '{print $1}')
   mem_util=$(free | grep Mem | awk '{print $3}')
   ;;
  Darwin*)
   user_cpu_util=$(top -l 1 | awk '/^CPU usage: / { print substr($3, 1, length($3)-1) }')
   sys_cpu_util=$(top -l 1 | awk '/^CPU usage: / { print substr($5, 1, length($5)-1) }')
   idle_cpu_util=$(top -l 1 | awk '/^CPU usage: / { print substr($7, 1, length($7)-1) }')
   mem_util=$(top -l 1 | awk '/^PhysMem:/ { print substr($6, 1, length($6)-1) }')
   ;;
  *)
   user_cpu_util=$(shuf -i 10-15 -n 1)
   sys_cpu_util=$(shuf -i 5-10 -n 1)
   idle_cpu_util=$(shuf -i 70-80 -n 1)
   mem_util=$(shuf -i 50-60 -n 1)
 esac

 cat <<EOF
    ("$unameOut",$user_cpu_util,$sys_cpu_util,$idle_cpu_util,$mem_util)
EOF
}

Then send the metrics to GreptimeDB every 5 seconds:

# Create table
mysql --ssl-mode=REQUIRED -u $username -p$password -h $host -P 4002 -A $database \
    -e "CREATE TABLE IF NOT EXISTS monitor (host STRING, user_cpu DOUBLE, sys_cpu DOUBLE, idle_cpu DOUBLE, memory DOUBLE, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, TIME INDEX(ts), PRIMARY KEY(host));"

# Insert metrics
echo Sending metrics to GreptimeCloud...
while true
do
 sleep 2
 mysql --ssl-mode=REQUIRED -u $username -p$password -h $host -P 4002 -A $database \
        -e "INSERT INTO monitor(host, user_cpu, sys_cpu, idle_cpu, memory) VALUES $(generate_data);"
done

For information on the host, database, username, and password required for the MySQL API, please refer to the MySQL documentation in GreptimeDB or GreptimeCloud.

Congratulations on successfully completing the core section of the demo! You can now run the complete demo by following the instructions in the README.md file on the GitHub repository.

The connection information can be found on the service page of GreptimeCloud console.

Visualize Data

Visualizing data in panels and monitoring metrics is important in a developer's daily work. From the GreptimeCloud console, click on Open Prometheus Workbench, then click on + New Ruleset and Add Group. Name the group host-monitor and add panels.

To add panels for all the tables you're concerned with, select a table and click on Add Panel one by one. Once you've added all the necessary panels, click on the Save button to save them. You can then view the panels in your daily work to monitor the metrics. Additionally, you can set up alert rules for the panels to be notified when the metrics exceed the threshold.