[Feature] Monitoring MySQL Server

[wu-sheng]

Search before asking

• [X] I had searched in the issues and found no similar feature requirement.

Description

@yswdqz finished his first contribution in https://github.com/apache/skywalking-java/pull/286. This is a huge step for a new contributor. After this, he DM me asking for a new place to continue his contributions to the SkyWalking community.

So, I want to begin a new extension for SkyWalking's monitoring target - Database server.

Right now, we have a Virtual Database layer to monitor database performance from a tracing perspective(analyzing metrics from database access spans). But Database Server in the topology is not observed directly.

In this new issue, I want to work with @yswdqz and assist him to help on adding MySQL server monitoring in the SkyWalking 9.2 or 9.3 release

Use case

There are several steps we could follow to do this research and further work

Background

Unlike tracing relative monitoring, SkyWalking could do observability through a pure metrics ecosystem. Such as, we use OpenTelemetry collector + OpenCensus exporter to monitor Linux and K8s. I prefer we continue this tech stack for MySQL server monitoring.

The dataflow would be MySQL Server -> OpenTelemetry Collector -> SkyWalking OpenCensus Receiver -> MAL engine

Steps

1. Do research about the ways of MySQL server and OpenTelemetry integration. Various blogs such as this(just a reference) introduce how this could work.
2. We discuss and pick one possible solution.
3. Learn MAL and get ideas about how to use MAL to analyze metrics. Could take a look at K8s and Linux monitoring.
4. Discuss which metrics we should do for the MySQL server. We could involve more people in this part, such as ppl from MySQL or ShardingSphere community. I could help with this.
5. Add MySQL layer to OAP, and begin to set up dashboards(can't be a menu for now)
6. When all dashboards are ready, we could ask @Fine0830 to help with moving virtual database as a sub-menu of general service(So, general service could have two children, services and virtual database). And change the virtual database to Database, then add MySQL as a sub and a root layer.

Related issues

No response

Are you willing to submit a PR?

• [ ] Yes I am willing to submit a PR!

Code of Conduct

• [X] I agree to follow this project's Code of Conduct

[yswdqz]

OK, I will do my best to resolve it!

[yswdqz]

Please tell me where is the code of K8s and Linux monitoring? I want to take a look.

[wu-sheng]

You don't need codes. MAL is all you need, https://github.com/apache/skywalking/tree/master/oap-server/server-starter/src/main/resources/otel-oc-rules

[yswdqz]

I get it.

[yswdqz]

Through the blog, I learned how to go from MySQL Server to OpenTelemetry Collector, but I am confused about how to go from OpenTelemetry Collector to SkyWalking OpenCensus Receiver, and I don't know much about SkyWalking OpenCensus Receiver, Are OpenCensus Receiver and Opentelemetry Receiver (https://skywalking. apache.org/docs/main/latest/en/setup/backend/opentelemetry-receiver/) the same?

[wu-sheng]

Yes, Opentelemetry collector has OC exporter for it. Take a look at k8s e2e settings. It guides you how it works.

[yswdqz]

OK, I will take a look.

[yswdqz]

So set the exporters of the OpenTelemetry Collector to OTLP gRPC and set the port to 11800, the data will flow to the SkyWalking OpenCensus Receiver, and then I just need to write the yaml. Is the idea right?

[wu-sheng]

Yes, you are correct.

[yswdqz]

I'm trying to configure yaml, but I'm running into a problem: how can I see the final data in oap?

[wu-sheng]

What do you mean? The data would be persistent in the database.

[yswdqz]

I get it. I forget the database and trying to see from the UI before.

[yswdqz]

I configure the setting of the opentelemetry-collector to print metric data in the console, The form of the data is similar to the following:

Metric #16
Descriptor:
     -> Name: mysql.threads
     -> Description: The state of MySQL threads.
     -> Unit: 1
     -> DataType: Sum
     -> IsMonotonic: false
     -> AggregationTemporality: AGGREGATION_TEMPORALITY_CUMULATIVE
NumberDataPoints #0
Data point attributes:
     -> kind: STRING(cached)
StartTimestamp: 2022-07-28 01:09:58.7576015 +0000 UTC
Timestamp: 2022-07-28 02:57:07.0609427 +0000 UTC
Value: 0

The format provided to me by MAL doc is as follows:

instance_trace_count{region="us-west",az="az-1"} 100 

Maybe the data format will change by the SkyWalking OpenCensus Receiver. But I am confused about when I configure the metricsRules by MAL what will the names of these Metrics(such as the instance_trace_count of above) I need to call?

[wu-sheng]

instance_trace_count is the metric family name reported. mysql.threads should be renamed to mysql_threads, I am guessing.

OCMetricHandler should be able to check what you received.

@wankai123 should know more than me about the details.

[yswdqz]

OK, I will see it.

[yswdqz]

I have a general understanding of MAL, and I successfully persisted a part of data to the database. And I am comfused about the expSuffix, such as the vm.yaml：

expSuffix: service(['node_identifier_host_name'] , Layer.OS_LINUX)

I can understand that the layer I should set to MYSQL in the future, but how do I understand service above? Is it the entity type of the setting when I new a dashboard, and what should I configure it to be?

[wu-sheng]

expSuffix means the suffix of all following expresses. This part is going to execute after all following MAL settings.

Here, in the MAL doc, we should find service, https://skywalking.apache.org/docs/main/latest/en/concepts-and-designs/mal/#metric-level-function.

Right now, you have metrics, you need to use this function or other to determine, which entity are these metrics belonging.

A new layer(MYSQL) should be added, then you could config a new dashboard for this entity. Don't worry about the menu, we need some codes changes to support this new layer, as I mentioned in the beginning.

[yswdqz]

I get it ,I missed this part of the doc at the time. Now what should I do? Maybe the step 4?

[wu-sheng]

You could send a pull request to add Layer to the codes. Then step <4>

[yswdqz]

OK, I will try to do it.

[yswdqz]

I saw that there is a DATABASE layer in the Layer enum. Does it conflict with the MYSQL layer?

[wankai123]

I saw that there is a DATABASE layer in the Layer enum. Does it conflict with the MYSQL layer?

No, doesn't

[yswdqz]

I get it.

[yswdqz]

After reading the commit history of Layer.java, I found that I only need to modify this file (Layer.java) to add the MYSQL layer, without changing any other files or documents?

[wu-sheng]

Yes, that is correct.

[yswdqz]

OK, I have sent the PR, https://github.com/apache/skywalking/pull/9407

[yswdqz]

Thank you for your edit, I'm always not rigorous enough. I will do better next time. Now we should do the step <4>?

[wu-sheng]

Yes, let's move on step 4.

[yswdqz]

How can I help with this part？

[wu-sheng]

What do you mean? Step 4 means you review the otel metrics, and summary a list of metrics meaningfully. You could refer examples from Gafana/Prometheus or MySQL examples of other APM tools.