MySQL

Prerequisites

Before trying GreptimeDB, we need to have GreptimeDB and Grafana installed and running locally.

• GreptimeDB is used for storing and querying data.
• Grafana is used for visualizing data.

Here we use Docker Compose to start GreptimeDB and Grafana. To do this, create a docker-compose.yml file with the following content:

yaml

services:
  grafana:
    image: grafana/grafana-oss:9.5.15
    container_name: grafana
    ports:
      - 3000:3000
  greptime:
    image: greptime/greptimedb:latest
    container_name: greptimedb
    ports:
      - 4000:4000
      - 4001:4001
      - 4002:4002
      - 4003:4003
      - 4004:4004
      - 4242:4242
    command: "standalone start --http-addr 0.0.0.0:4000 --rpc-addr 0.0.0.0:4001 --mysql-addr 0.0.0.0:4002 --postgres-addr 0.0.0.0:4003 --opentsdb-addr 0.0.0.0:4242"
    volumes:
      - ./greptimedb:/tmp/greptimedb
networks: {}

Then run the following command:

shell

docker-compose up

NOTE

The following steps assume that you have followed the documentation above, which uses Docker Compose to install GreptimeDB and Grafana.

Try Out Basic SQL Operations

Connect

sql

mysql -h 127.0.0.1 -P 4002

Also, you can use PostgreSQL to connect the database:

psql -h 127.0.0.1 -p 4003 -d public

Create table

Note: GreptimeDB offers a schemaless approach to writing data that eliminates the need to manually create tables using additional protocols. See Automatic Schema Generation.

Now we create a table via MySQL. Let’s start by creating the system_metrics table which contains system resource metrics, including CPU/memory/disk usage. The data is scraped every 5 seconds.

sql

CREATE TABLE IF NOT EXISTS system_metrics (
    host STRING,
    idc STRING,
    cpu_util DOUBLE,
    memory_util DOUBLE,
    disk_util DOUBLE,
    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(host, idc),
    TIME INDEX(ts)
);

Field descriptions:

• The table can be created automatically if you are using other protocols. See Create Table.
• For more information about creating table SQL, please refer to CREATE.
• For data types, please check data types.

Insert data

Using the INSERT statement is an easy way to add data to your table. The following statement allows us to insert several rows into the system_metrics table.

sql

INSERT INTO system_metrics
VALUES
    ("host1", "idc_a", 11.8, 10.3, 10.3, 1667446797450),
    ("host1", "idc_a", 80.1, 70.3, 90.0, 1667446797550),
    ("host1", "idc_b", 50.0, 66.7, 40.6, 1667446797650),
    ("host1", "idc_b", 51.0, 66.5, 39.6, 1667446797750),
    ("host1", "idc_b", 52.0, 66.9, 70.6, 1667446797850),
    ("host1", "idc_b", 53.0, 63.0, 50.6, 1667446797950),
    ("host1", "idc_b", 78.0, 66.7, 20.6, 1667446798050),
    ("host1", "idc_b", 68.0, 63.9, 50.6, 1667446798150),
    ("host1", "idc_b", 90.0, 39.9, 60.6, 1667446798250);

For more information about the INSERT statement, please refer to INSERT.

Query data

To select all the data from the system_metrics table, use the SELECT statement:

sql

SELECT * FROM system_metrics;

The query result looks like the following:

+-------+-------+----------+-------------+-----------+---------------------+
| host  | idc   | cpu_util | memory_util | disk_util | ts                  |
+-------+-------+----------+-------------+-----------+---------------------+
| host1 | idc_a |     11.8 |        10.3 |      10.3 | 2022-11-03 03:39:57 |
| host1 | idc_a |     80.1 |        70.3 |        90 | 2022-11-03 03:39:57 |
| host1 | idc_b |       50 |        66.7 |      40.6 | 2022-11-03 03:39:57 |
| host1 | idc_b |       51 |        66.5 |      39.6 | 2022-11-03 03:39:57 |
| host1 | idc_b |       52 |        66.9 |      70.6 | 2022-11-03 03:39:57 |
| host1 | idc_b |       53 |          63 |      50.6 | 2022-11-03 03:39:57 |
| host1 | idc_b |       78 |        66.7 |      20.6 | 2022-11-03 03:39:58 |
| host1 | idc_b |       68 |        63.9 |      50.6 | 2022-11-03 03:39:58 |
| host1 | idc_b |       90 |        39.9 |      60.6 | 2022-11-03 03:39:58 |
+-------+-------+----------+-------------+-----------+---------------------+
9 rows in set (0.00 sec)

You can use the count() function to get the number of all rows in the table:

sql

SELECT count(*) FROM system_metrics;

+-----------------+
| COUNT(UInt8(1)) |
+-----------------+
|               9 |
+-----------------+

The avg() function returns the average value of a certain field:

sql

SELECT avg(cpu_util) FROM system_metrics;

+------------------------------+
| AVG(system_metrics.cpu_util) |
+------------------------------+
|            59.32222222222222 |
+------------------------------+

You can use the GROUP BY clause to group rows that have the same values into summary rows. The average memory usage grouped by idc:

sql

SELECT idc, avg(memory_util) FROM system_metrics GROUP BY idc;

+-------+---------------------------------+
| idc   | AVG(system_metrics.memory_util) |
+-------+---------------------------------+
| idc_a |                            40.3 |
| idc_b |              61.942857142857136 |
+-------+---------------------------------+
2 rows in set (0.03 sec)

For more information about the SELECT statement, please refer to SELECT.

Collect Host Metrics

To quickly get started with MySQL, we can use Bash to collect system metrics, such as CPU and memory usage, and send it to GreptimeDB via MySQL CLI. The source code is avaliable on GitHub.

If you have started GreptimeDB using the Prerequisites section, you can use the following command to write data:

shell

curl -L https://raw.githubusercontent.com/GreptimeCloudStarters/quick-start-mysql/main/quick-start.sh | bash -s -- -h 127.0.0.1 -d public -s DISABLED -P 4002

Visualize data

GreptimeDB Dashboard

Visualization plays a crucial role in effectively utilizing time series data. To help users leverage the various features of GreptimeDB, Greptime offers a simple dashboard.

The Dashboard is embedded into GreptimeDB’s binary since GreptimeDB v0.2.0. After starting GreptimeDB, the dashboard can be visited via HTTP endpoint http://localhost:4000/dashboard. The current version of the dashboard supports MySQL, Python and PromQL queries.

Write SQL into the command text, then click Run All. We’ll got all data in system_metrics table.

SELECT * FROM system_metrics;

We offer various chart types to choose from based on different scenarios. The content of the charts will be richer when you have enough data.

We are committed to the ongoing development and iteration of this open source project, and we plan to expand the application of time series data in monitoring, analysis, and other relevant fields in the future.

Grafana

Add Data Source

You can access Grafana at http://localhost:3000. Use admin as both the username and password to log in.

GreptimeDB can be configured as a MySQL data source in Grafana. Click the Add data source button and select MySQL as the type.

Fill in the following information:

• Name: GreptimeDB
• Host: greptimedb:4002. The host greptimedb is the name of GreptimeDB container
• Database: public
• SessionTimezone: UTC

Click Save & Test button to test the connection.

For more information on using MySQL as a data source for GreptimeDB, please refer to Grafana-MySQL.

Create a Dashboard

To create a new dashboard in Grafana, click the Create your first dashboard button on the home page. Then, click Add visualization and select GreptimeDB as the data source.

In the Query section, ensure that you select GreptimeDB as the data source, choose Time series as the format, switch to the Code tab, and write the following SQL statement. Note that we are using ts as the time column.

sql

SELECT ts AS "time", idle_cpu, sys_cpu FROM public.monitor

Click Run query to view the metric data.

Next Steps

Congratulations on quickly experiencing the basic features of GreptimeDB! Now, you can explore more of GreptimeDB’s features by visiting the User Guide documentation.