Metrics instrumentation mysql

[srikanthccv]

DB semconv:
https://github.com/open-telemetry/opentelemetry-specification/blob/main/specification/metrics/semantic_conventions/database-metrics.md

[rahulhacker]

@srikanthccv Can I take this ??

[rahulhacker]

@srikanthccv
We are new to OTEL, trying to figure out to start on this feature-request.
Below are our findings:
1)show variables
Sample Output:

| max_connect_errors | 100 |
| max_connections | 151 |
| max_delayed_threads | 20 |

2)show global status: https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html
Sample Output:
| Connections | 30023 |

Are we on the right direction??

[srikanthccv]

@rahulhacker we don't want to make requests to database server and get the data. The client instrumentation should only work with what client library provides.

[rahulhacker]

@srikanthccv @dazuma @elskwid @dmathieu
Still not able to get the clear picture, what to do ? Someone can provide some good reference to start...

[dazuma]

@rahulhacker Please don't tag random people. I'm not a python developer and know next to nothing about this repository.

[rahulhacker]

@srikanthccv Can u guide me for further??

[rahulhacker]

@srikanthccv : Please help on the below:
Steps taken to do setup for the MYSql Instrumentation:
1)We have downloaded and uploaded to our servers and trying to run the test cases.
2)But we are facing error while doing so:
/apps/opentelemetry-python-contrib-main/opentelemetry-python-contrib-main/instrumentation/opentelemetry-
instrumentation-mysql/tests$ python3 test_mysql_integration.py
Traceback (most recent call last):
File "test_mysql_integration.py", line 22, in
from opentelemetry.test.test_base import TestBase
ModuleNotFoundError: No module named 'opentelemetry.test'

[srikanthccv]

@rahulhacker I am not sure what you are trying to do. Please follow this guide for developing locally https://github.com/open-telemetry/opentelemetry-python-contrib/blob/main/CONTRIBUTING.md

[rahulhacker]

@srikanthccv:
https://github.com/open-telemetry/opentelemetry-python-contrib/blob/main/instrumentation/opentelemetry-instrumentation-mysql/src/opentelemetry/instrumentation/mysql/__init__.py

we are done setup to run python-contrib to run locally .
as per ur comments we are using below MySQLInstrumentor to connect a mysql db .

class MySQLInstrumentor(BaseInstrumentor):
_CONNECTION_ATTRIBUTES = {
"database": "database",
"port": "server_port",
"host": "server_host",
"user": "user",
}

We are running this query SHOW VARIABLES LIKE 'max_connections'; to get the max_connection which we are saving as updowncounter

mysql> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
1 row in set (0.00 sec)

correct me if my going in right direction ???

Or
Should i need to find out "mysql-connector-python" something around this in order to get the metrics from MySQL??

[rahulhacker]

@srikanthccv Waiting for ur guidance, can u please help me on the above??

[Sunilwali679]

@srikanthccv We are new to OTEL community . Could you confirm are we in right path ? waiting for your guidelines

we are done setup to run python-contrib to run locally .

As per your comments We are using below MySQLInstrumentor to connect a mysql db .

class MySQLInstrumentor(BaseInstrumentor):
_CONNECTION_ATTRIBUTES = {
"database": "database",
"port": "server_port",
"host": "server_host",
"user": "user",
}

We are modifying the file https://github.com/open-telemetry/opentelemetry-python-contrib/blob/main/instrumentation/opentelemetry-instrumentation-mysql/src/opentelemetry/instrumentation/mysql/__init__.py

We are running this query SHOW VARIABLES LIKE 'max_connections'; to get the max_connection which we are saving as updowncounter

mysql> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
1 row in set (0.00 sec)

correct me if my going in right direction ???

[srikanthccv]

@rahulhacker I mentioned it above here #1154 (comment). You shouldn't make additional requests to database. This is a client library instrumentation and it should only provide this client measurements. The DB server metrics are collected using receiver provided here https://github.com/open-telemetry/opentelemetry-collector-contrib/tree/main/receiver/mysqlreceiver. This is probably not a good first issue if you are brand new to this project.

[rahulhacker]

@srikanthccv Thanks a lot for providing the info, but the reason for choosing this issue and it mentioned in the label as "good for new comers", but anyhow i will try to see if still can able to work.. an raise PR for the same :)

[shalevr]

Hi, @rahulhacker Do you work on this issue?

[lzchen]

@shalevr Are you wanting to take this on? @rahulhacker any progress on this?

[rahulhacker]

@lzchen : I am working on this , will update on the progress.

[rahulhacker]

@srikanthccv @lzchen : Please provide your inputs here
As we are taking reference of below documentation to provide solutions for connections pool
Document
link:
Existing codes are capturing below information based on the my understanding (Tracing queries and Connections):

Creating connections
Executing queries
Handling exceptions

To make our custom code to work need to import some of the Open Telemetry library working with metrics.

Counter
UpDownCounter
Histogram

We are defining out own method to collect the required metrics.

` def _instrument(self, **kwagrs):

           #define the method in this function to reflect the changes
           #custom method declaration
            self._collect_connection_pool_metrics()`



   #this is the custom method we are going to implement to capture the no of connection pool which are in the states(**"idle","used"**)

` def _collect_connection_pool_metrics(self):

             self._connection_pool_metrics_borrowed = UpDownCounter(

                        name = “db.client.connection.usage”,

                        description = “Number of borrowed connections”,

                        value_type= int,

                        unit = “connections”,

                        label_keys = [“state”]

        )`

In the instrument_connection function, need to add the code to call the instance of the UpDownCounter to add the no of connections pools being in used state.

#####Lines to add:

  if connection is active or alive
  `      if connection.is_connected():

                 self._connection_pool_metrics_borrowed.add(1, {‘state’:’used’}`

In the uninstrument_connection function, need to add the code to call the instance of the UpDownCounter to add back the no of connections objects back to the pools with the state as “Idle”.

if connection is active
` if connection.is_connected():

                        self._connection_pool_metrics_borrowed.add(-1, {‘state’:’Idle’}`

[rahulhacker]

@srikanthccv :Can you please review the comments and let me know if we are on the right track ??

[srikanthccv]

I am sorry I won't be able to review or respond to the comments here. I understand I created the issue, but I no longer have time or context to respond meaningfully. I apologise again.

[rahulhacker]

@srikanthccv : Thank you for letting me know. I completely understand, and there's no need to apologize.
The only help I need here if anyone else can provide some input or guidance. I am middle of writing the logic and test cases but to get confirm if we are in right direction.
Please provide any POC