How to fetch the slow queries from the QAN database

Percona Monitoring and Management (PMM)
1

Hi All,

How to fetch the slow queries from the QAN db externally, like using api or something?

We would like to automate Jira tickets by hitting the api or click House DB, so that the automation script will create the Jira tickets for the development team.

2

Can someone help me on this?

3

Can someone help me on this?

4

Hi @Naresh9999
Start from https://www.youtube.com/watch?v=vk1QBiMVzz4 and Advanced Query Analysis in Percona Monitoring and Management with Direct ClickHouse Access
this is how you can see how to create a dashboard with QAN data.
At the same time - you can use Clickhouse data source for Alerts and create an Alert when the query is slaw - then use some webhooks for notifications to get the ticket created :thinking:

5

Hi @Roma_Novikov

Thanks for reply. Can you pls help me if I can access QAN api and fetch the queries using the QAN api?

Is there any documentation for QAN api?

6

yes, you can use QAN api for this.
Open https://PMM_SERVER/swagger and replace the default filename to /swagger-dev.json

image
image1778×418 25.5 KB

This will give you a full API
Then check the method v0/qan/GetReport

Here is an example:

curl 'https://pmmdemo.percona.com/v0/qan/GetReport' \
  -H 'accept: application/json, text/plain, */*' \

  --data-raw '{"columns":["load","num_queries","query_time"],"group_by":"queryid","include_only_fields":[],"keyword":"","labels":[],"limit":"25","offset":0,"order_by":"-load","main_metric":"load","period_start_from":"2024-10-11T01:17:07+03:00","period_start_to":"2024-10-11T13:17:07+03:00","search":""}'

You can see more examples if you inspect QAN requests to this API endpoint.

Additionally, remember about Authentication

7

Thanks @Roma_Novikov

This is what I need. Can I pull the queries based on the filters like time range, prod or specific server?

Is there any blog available?

8

Hi @Roma_Novikov

Is it feasible to connect to the Click House database remotely using any program or utility?

Like MySQL DB below:

mysql -uremote_db_user -p -h192,168.1.100 -P3306

Need some thing like below Click house DB:

clickhouse-client -uremote_db_user -p -h192,168.1.100 -P8123

9

@Roma_Novikov @nurlan

Can you please help me with the below?

Is it feasible to connect to the Click House database remotely using any program or utility?

Like MySQL DB below:

mysql -uremote_db_user -p -h192,168.1.100 -P3306

Need some thing like below Click house DB:

clickhouse-client -uremote_db_user -p -h192,168.1.100 -P8123

10

Hello @Naresh9999

You can access Docker PMM Server Clickhouse as below,
Use clickhouse-client as shown in the following example:

sudo docker exec -it pmm-server clickhouse-client -h 127.0.0.1 -d pmm

11

@lalit.choudhary

I don’t want to connect from the docker server; i want to connect the database from external script.

12

Hi Team,

Can someone please help me with the requirement?

13

Hello @Naresh9999

This customization is outside PMM support scope, but if you would like to access Clickhouse from the docker host, you can use the Dockers port mapping --publish option.

Clickhouse under pmm-server has these ports available for connection depending on connection method.

2024.10.24 06:17:38.911594 [ 78 ] {} <Information> Application: Listening for native protocol (tcp): 127.0.0.1:9000
2024.10.24 06:17:38.911622 [ 78 ] {} <Information> Application: Listening for MySQL compatibility protocol: 127.0.0.1:9004
2024.10.24 06:17:38.911647 [ 78 ] {} <Information> Application: Listening for PostgreSQL compatibility protocol: 127.0.0.1:9005
2024.10.24 06:17:38.911672 [ 78 ] {} <Information> Application: Listening for Prometheus: http://127.0.0.1:9363
2024.10.24 06:17:38.911679 [ 78 ] {} <Information> Application: Ready for connections.

Here are some examples: Run ClickHouse with Docker and Connect Using MySQL Client