datatxt-cachedb

Info

This directory contains project converted from grafana-rrd-cachedb to handle legacy plaintext metric directories

Usage

prepare directory

./mkdatafiles.sh 2022 07 15
./mkdatafiles.sh 2022 07 14
./mkdatafiles.sh 2022 07 13

mkdir host1
mv 20220714/  20220713/ 20220715/ host1

make few empty dirs to exercise the scripts

mkdir host1/202206{27,26,25}

NOTE: building a one day worth of data takes approx 5 minute on Windows machine, git bash. It is significantly faster to touch files instead of storing a metric in each. The directory tree / file creation is instant under ubuntu

run the scanner to inventory

mvn package
cp src/main/resources/cache.db ~
java -cp target/example.datatxt-cachedb.jar:target/lib/* example.App -p host1 -s --hostname hostt1

can use relative path: --path ..\rrd-cachedb\host1.

to select / reject folders,use the -i and -r arguments:

java -cp target\example.datatxt-cachedb.jar;target\lib\* example.App -p host1 -s -i 20220713,20220714,20220715 --hostname host1

To estimate the run time with big directories may omit the -s (--save) option. The default run will print to console (output is truncated):

hostname: host1
Missing argument: vendor. Using default
Missing argument: sqliteDatabaseName. Using default
Scanning path: /C:/developer/sergueik/selenium_java/rrd-cachedb/host1/
inspect: host1
status: false
inspect: 20220628
status: false
inspect: 20220629
status: true
inspect: 20220630
status: false
found file: data.txt.202206290000
found file: data.txt.202206290001
found file: data.txt.202206290002
found file: data.txt.202206290003
...
Ingesting 1440 files:
about to add data: [memory, cpu, disk, load_average]
reading data for metric cpu = 12
reading data for metric memory = 22
reading data for metric disk = 42.5
reading data for metric load_average = 6
reading data for metric rpm = 102
adding timestamp: 1656475200000
added data: [disk, memory, load_average, cpu, rpm]
...
Saving data
about to insert data row: [host1, 1656475200000, 22, 12, 42.5, 6]
about to insert data row: [host1, 1656475260000, 22, 12, 42.5, 6]
about to insert data row: [host1, 1656475320000, 22, 12, 42.5, 6]
about to insert data row: [host1, 1656475380000, 22, 12, 42.5, 6]
about to insert data row: [host1, 1656475440000, 22, 12, 42.5, 6]
...
Querying data : SQLite	catalog: null	schema: null
hostname = host1        timestamp = 1656561480000       disk = 42.5     cpu = 12
        memory = 22     load_average = 6
hostname = host1        timestamp = 1656561540000       disk = 42.5     cpu = 12
        memory = 22     load_average = 6
...
Done: host1

Verify in the sqlite console:

sqlite3 ~/cache.db

SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.

sqlite> .schema

CREATE TABLE metric_table ( `id` INTEGER,`hostname` TEXT NOT NULL,`timestamp` TEXT,`memory` TEXT,`cpu` TEXT,`disk` TEXT,`load_average` TEXT,PRIMARY KEY(`id`));

sqlite> select count(1) from metric_table;

NOTE: during the execution of the program will be likely getting

sqlite> .schema

Error: database is locked

the app itself supports --query option to run the SQL query, useful for debugging:

java -cp target\example.datatxt-cachedb.jar;target\lib\* example.App -p ..\rrd-cachedb\host1 -s --hostname hostt1 --query

this will prin (truncated):

hostname = hostt1       timestamp = 1656647760000       disk = 42.5     cpu = 12
        memory = 22     load_average = 6
hostname = hostt1       timestamp = 1656647820000       disk = 42.5     cpu = 12
        memory = 22     load_average = 6
hostname = hostt1       timestamp = 1656647880000       disk = 42.5     cpu = 12
        memory = 22     load_average = 6
hostname = hostt1       timestamp = 1656647940000       disk = 42.5     cpu = 12
        memory = 22     load_average = 6

Preparing the wrapper

mkdir -p /tmp/basedir/{10,20,30,40}
mkdir -p /tmp/basedir/10/{1,2,3,4}
mkdir -p /tmp/basedir/20/{1,2}
./count_subdirs1.sh /tmp/basedir/
./count_subdirs2.sh /tmp/basedir/

running with timing the run time:

NUM=10
date && for D in $(tail -$NUM /tmp/result.txt); do ./example.sh $D ; done; date

where example.sh wraps the call to target/example.datatxt-cachedb.jar with all flags and switches (not shown here)

Connecting to mySQL server container

assuming the container named mysql-server was created earlier for some other task

NAME='mysql-server'
docker container start $NAME

NAME='mysql-server'
docker container inspect $NAME | jq '.[]|.Config.Env'

this will show environment used when container was launched:

[
  "MYSQL_DATABASE=test",
  "MYSQL_PASSWORD=password",
  "MYSQL_ROOT_PASSWORD=password",
  "MYSQL_USER=java",
  "PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin",
  "GOSU_VERSION=1.7",
  "MYSQL_MAJOR=8.0",
  "MYSQL_VERSION=8.0.18-1debian9"
]

details may vary, point of interest is MYSQL_PASSWORD, MYSQL_USER, MYSQL_ROOT_PASSWORD

docker container inspect mysql-server | jq '.[]|.Config.ExposedPorts'

{
  "3306/tcp": {},
  "33060/tcp": {}
}

docker container inspect mysql-server | jq '.[]|.NetworkSettings.Ports'

{
  "3306/tcp": [
    {
      "HostIp": "0.0.0.0",
      "HostPort": "3306"
    }
  ],
  "33060/tcp": null
}

update the arguments in App.java run command accordingly - there currently is no application.properties, but all connection specific can be set on the command line

if the error

driverObject=class com.mysql.cj.jdbc.Driver
Connected to product: MySQL
Connected to catalog: test
Exception: Unknown table 'test.cache_table'
java.sql.SQLSyntaxErrorException: Unknown table 'test.cache_table'

is observed

run the commands

docker exec -it mysql-server mysql -u root -ppassword

then in

mysql>

use test

CREATE TABLE `metric_table` ( `id` BIGINT, `hostname` TEXT NOT NULL,  `timestamp` TEXT, `memory` TEXT, `cpu` TEXT, `disk` TEXT, `load_average` TEXT, PRIMARY KEY(`id`) );

\q

run the application with the -vendor mysql option added:

java -cp target/example.datatxt-cachedb.jar:target/lib/* example.App -p host1 -s --hostname host1 -vendor mysql

modify the command to Windows path and separator if testing on Windows:

java -cp target\example.datatxt-cachedb.jar;target\lib\* example.App -p host1 -s --hostname host1

after it completes connect to database node and count inserved rows

docker exec -it mysql-server mysql -u java -ppassword

in

mysql>

run


use test
select count(1) from metric_table;

this will show

+----------+
| count(1) |
+----------+
|     2880 |
+----------+

after confirming thati dummy metric information is in there next step is to attach the mysql-server node to the grafana one in a MySQL Data source plugin (built in, native).

Connecting to Grafana

build the basic-grafana container as covered in basic-grafana

pushd ~/src/springboot_study/basic-grafana
IMAGE=basic-grafana
docker build -f Dockerfile -t $IMAGE .
popd

run basic-grafana container linked to mysql-server one

IMAGE=basic-grafana
docker container run --name $IMAGE --link mysql-server -d -p 3000:3000 $IMAGE

configure MySQL data source through Grafana web interface using mysql-server:3306 host and the user / password seen in the container configuration earlier.

After filling the connection details, and "Save & Test" it will respond with "Database Connection OK"

To address the error

invalid type for column time, must be of type timestamp or unix timestamp, got: string 1656388800

when inserting to handle the

ERROR 1292 (22007): Incorrect datetime value: '1656584700'

need to change the database schema on MySQL table definition:

DROP TABLE IF EXISTS `metric_table`;
CREATE TABLE `metric_table` ( `id` BIGINT, `hostname` TEXT NOT NULL,  `timestamp` TIMESTAMP, `memory` TEXT, `cpu` TEXT, `disk` TEXT, `load_average` TEXT, PRIMARY KEY(`id`) );

and the insert string to

INSERT INTO `metric_table` ( `id`, `hostname`,`timestamp`,`memory`, `cpu`,`disk`,`load_average`) VALUES (?, ?, FROM_UNIXTIME(?), ?, ?, ?, ?);

to address the error

Value column must have numeric datatype, column: cpu type: string value: 12

need to change the database schema on MySQL table definition:

DROP TABLE IF EXISTS `metric_table`;
CREATE TABLE `metric_table` ( `id` BIGINT, `hostname` TEXT NOT NULL,  `timestamp` TIMESTAMP, `memory` INTEGER, `cpu` INTEGER, `disk` FLOAT(6), `load_average` INTEGER, PRIMARY KEY(`id`) );

describe metric_table ;

+--------------+------------+------+-----+---------+-------+
| Field        | Type       | Null | Key | Default | Extra |
+--------------+------------+------+-----+---------+-------+
| id           | bigint(20) | NO   | PRI | NULL    |       |
| hostname     | text       | NO   |     | NULL    |       |
| timestamp    | timestamp  | YES  |     | NULL    |       |
| memory       | int(11)    | YES  |     | NULL    |       |
| cpu          | int(11)    | YES  |     | NULL    |       |
| disk         | float      | YES  |     | NULL    |       |
| load_average | int(11)    | YES  |     | NULL    |       |
+--------------+------------+------+-----+---------+-------+

and the insert placeholder fillers to

preparedStatement.setInt(4, Integer.parseInt(memory));
preparedStatement.setInt(5, Integer.parseInt(cpu));
preparedStatement.setFloat(6, Float.parseFloat(disk));
preparedStatement.setInt(7, Integer.parseInt(load_average));

the tag(s?) wll be picked in "metric column":

the values will be offered in "column":

the data will show in the selected date range

the data can be also viewed / schema improved in a regular desktop DB management tool, e.g. SQLite viewer (note the minor schema differences between database vendors):

Merge Data

run with options

java -cp target\example.datatxt-cachedb.jar;target\lib\* example.App -p host1 -s  --hostname host1 --csv a.csv -q -m -d

this will cause it to load the host inventory strongly typed List<ServerInstanceApplication> and save it into cache database host_inventory.sql

Batch Update

note the mysql server errors logged to docker console

2022-07-29  2:10:54 114 [Warning] Aborted connection 114 to db: 'test' user: 'java' host: '192.168.0.25' (Got an error reading communication packets)

Cleanup

docker container stop $IMAGE
docker container stop mysql-server

Testing Loading properties file

copy the file application.properties from source tree to the current directory:

copy src\main\resources\application.properties .

set the debug to true before loading the properties in App.java (this particular debug setting is too late to update when configuration option debug is loaded, as the properties are cached:

public static void main(String args[]) throws ParseException {

	// NOTE: too late toset after the debug command line options is set
	utils.setDebug(true);
	utils.getProperties("application.properties");

build and run the App

mvn package

java -cp target\example.datatxt-cachedb.jar;target\lib\* example.App -p host1 -s -i 20220713,20220714,20220715 --hostname host1 -d

observe it load the properties from the specified file:

Reading properties file: 'application.properties'
Reading: 'datasource.driver-class-name' = 'org.sqlite.JDBC'

remove the application.properties file copied locally and rerun without recompiling

java -cp target\example.datatxt-cachedb.jar;target\lib\* example.App -p host1 -s -i 20220713,20220714,20220715 --hostname host1 -d

observe it attempt to read file, then fall back to property resource:

Reading properties file: 'application.properties'
Reading properties resource stream: 'application.properties'
Reading: 'datasource.driver-class-name' = 'org.sqlite.JDBC'
Reading: 'datasource.url' = 'jdbc:sqlite:${USERPROFILE}\${datasource.filename}'
Reading: 'jdbc.server' = '192.168.0.64'
Reading: 'datasource.username' = ''

Note

one does not have to have maven-dependency-plugin in the pom.xml build section if this is the case add the argument when building the package:

mvn compile dependency:copy-dependencies package

and modify the launch command to take into account the default location of the dependency jars:

java -cp target\example.datatxt-cachedb.jar;target\dependency\* example.App -p host1 -s -i 20220713,20220714,20220715 --hostname host1 -d

Youtube Videos

Using MySQL to Create a Grafana Dashboard

Author

Serguei Kouzmine

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

README.md

Info

Usage

Preparing the wrapper

Connecting to mySQL server container

Connecting to Grafana

Merge Data

Batch Update

Cleanup

Testing Loading properties file

Note

See Also

Youtube Videos

Author

Name		Name	Last commit message	Last commit date
parent directory ..
screenshots		screenshots
src/main		src/main
.gitignore		.gitignore
README.md		README.md
collect_dir.sh		collect_dir.sh
count_subdirs1.sh		count_subdirs1.sh
count_subdirs2.sh		count_subdirs2.sh
mkdatafiles.sh		mkdatafiles.sh
pom.xml		pom.xml

FilesExpand file tree

datatxt-cachedb

Directory actions

More options

Directory actions

More options

Latest commit

History

datatxt-cachedb

Folders and files

parent directory

README.md

Info

Usage

Preparing the wrapper

Connecting to mySQL server container

Connecting to Grafana

Merge Data

Batch Update

Cleanup

Testing Loading properties file

Note

See Also

Youtube Videos

Author