# How to create a custom QAN dashboard{Query Analytics}

**URL:** <https://forums.percona.com/t/how-to-create-a-custom-qan-dashboard-query-analytics/20725>\
**Category:** Percona Monitoring and Management (PMM)\
**Created:** [March 18, 2023, 12:57pm UTC](https://forums.percona.com/t/how-to-create-a-custom-qan-dashboard-query-analytics/20725 "2023-03-18T12:57:28Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Naresh9999](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/naresh9999/32/5568_2.png) [@Naresh9999](https://forums.percona.com/u/Naresh9999)\
**Post date:** [March 18, 2023, 12:57pm UTC](https://forums.percona.com/t/how-to-create-a-custom-qan-dashboard-query-analytics/20725/1 "2023-03-18T12:57:28Z")

</div>

Hi All,

How can we create custom QAN dashboard? We would like to create a dash board like Top 10 slow queries or top 20 slow queries in the custom dashboard.

---

<div class="post-metadata">

**Author:** ![Vadim\_Yalovets](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vadim_yalovets/32/43_2.png) [@Vadim\_Yalovets](https://forums.percona.com/u/Vadim_Yalovets)\
**Post date:** [March 18, 2023, 3:48pm UTC](https://forums.percona.com/t/how-to-create-a-custom-qan-dashboard-query-analytics/20725/2 "2023-03-18T15:48:10Z")

</div>

Hi @Naresh9999

Please take a look at the next blogpost.

> **[RED Method for MySQL Performance Analyses](https://www.percona.com/blog/red-method-for-mysql-performance-analyses/)**
>
> The RED Method (Rate, Errors, Duration) is one of the more popular performance monitoring approaches, and you can apply it to databases like MySQL.

The [dashboard](https://pmmdemo.percona.com/graph/d/gZQooogMp/red-method-for-mysql-queries?orgId=1&viewPanel=5) is present on our demo site.

 ![Screenshot 2023-03-18 at 17.46.49](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/2X/9/9e6d72a96472e85ea8615ff101dc2273500e95d2.png)

---

<div class="post-metadata">

**Author:** ![Naresh9999](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/naresh9999/32/5568_2.png) [@Naresh9999](https://forums.percona.com/u/Naresh9999)\
**Post date:** [March 20, 2023, 7:13am UTC](https://forums.percona.com/t/how-to-create-a-custom-qan-dashboard-query-analytics/20725/3 "2023-03-20T07:13:43Z")

</div>

Hi @Vadim_Yalovets

Thanks for the quick help.

When I am trying to run the query then I am getting the below error.  
**Code: 60, e.displayText() = DB::Exception: Table default.undefined doesn’t exist (version 21.3.20.1 (official build))**

 ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/2X/8/880dbc8492084a86ca2b172f01fd7655c7a34ce5.png)

---

<div class="post-metadata">

**Author:** ![Vadim\_Yalovets](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vadim_yalovets/32/43_2.png) [@Vadim\_Yalovets](https://forums.percona.com/u/Vadim_Yalovets)\
**Post date:** [March 20, 2023, 10:59am UTC](https://forums.percona.com/t/how-to-create-a-custom-qan-dashboard-query-analytics/20725/4 "2023-03-20T10:59:01Z")

</div>

Could you check a generated SQL request?

![Screenshot 2023-03-20 at 12.58.05](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/2X/a/ad085c0ff62679edc6bae9f40b22c4f962524405.png)

---

<div class="post-metadata">

**Author:** ![Naresh9999](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/naresh9999/32/5568_2.png) [@Naresh9999](https://forums.percona.com/u/Naresh9999)\
**Post date:** [March 20, 2023, 5:37pm UTC](https://forums.percona.com/t/how-to-create-a-custom-qan-dashboard-query-analytics/20725/5 "2023-03-20T17:37:01Z")

</div>

Hi @Vadim_Yalovets

Here is the generated sql output.

 ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/2X/2/215e7103fd24ec33cf96e149740b87402c388ac9.png)

---

<div class="post-metadata">

**Author:** ![Vadim\_Yalovets](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vadim_yalovets/32/43_2.png) [@Vadim\_Yalovets](https://forums.percona.com/u/Vadim_Yalovets)\
**Post date:** [March 21, 2023, 12:44pm UTC](https://forums.percona.com/t/how-to-create-a-custom-qan-dashboard-query-analytics/20725/6 "2023-03-21T12:44:28Z")

</div>

No table has been specified in your case.  
It has to be pmm.metrics

e.g.

 ![Screenshot 2023-03-21 at 14.42.24](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/2X/c/c025fc1298aeee925af53f552461819e3bb67a43.png)

---

<div class="post-metadata">

**Author:** ![Naresh9999](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/naresh9999/32/5568_2.png) [@Naresh9999](https://forums.percona.com/u/Naresh9999)\
**Post date:** [March 21, 2023, 1:03pm UTC](https://forums.percona.com/t/how-to-create-a-custom-qan-dashboard-query-analytics/20725/7 "2023-03-21T13:03:05Z")

</div>

@Vadim_Yalovets

Sorry its my bad, Thanks for the help.

One last information, I am unable to view or copy the full query from the dashboard. Is there a way we can get full query details from the below Slowest Queries dashboard.

Example: we are seeing end of the query with dots like below.

**coalesce(msi.regstn\_src\_cd,msi2.regstn\_src\_cd) as buyer\_regstn\_src\_cd, m.crm\_id as crm\_id, …**

 ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/2X/7/7423d32067b5829783c43a377a2c17233c6c606e.png)

---

<div class="post-metadata">

**Author:** ![Vadim\_Yalovets](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vadim_yalovets/32/43_2.png) [@Vadim\_Yalovets](https://forums.percona.com/u/Vadim_Yalovets)\
**Post date:** [March 21, 2023, 1:58pm UTC](https://forums.percona.com/t/how-to-create-a-custom-qan-dashboard-query-analytics/20725/8 "2023-03-21T13:58:33Z")

</div>

```auto
SELECT
    fingerprint,
    sum(num_queries)/($ __to-$__ from)*1000 QPS,
    sum(m_query_time_sum)/sum(m_query_time_cnt)*1000 as Latency_ms,
    sum(m_rows_examined_sum)/sum(m_rows_sent_sum) as Rows_exam_per_sent
FROM $table
WHERE $timeFilter
GROUP BY fingerprint
ORDER BY Latency_ms desc limit 20

```

 ![Screenshot 2023-03-21 at 15.57.59](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/2X/9/971ffec7af63e73ef8d22a65dbe4111c2a0804b1.png)

---

<div class="post-metadata">

**Author:** ![Naresh9999](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/naresh9999/32/5568_2.png) [@Naresh9999](https://forums.percona.com/u/Naresh9999)\
**Post date:** [March 21, 2023, 3:58pm UTC](https://forums.percona.com/t/how-to-create-a-custom-qan-dashboard-query-analytics/20725/9 "2023-03-21T15:58:52Z")

</div>

@Vadim_Yalovets

I already tried the above query but still I am getting half/partial queries instead of full queries.  
I am actually using mysql slowlog source as perfschema.

---

<div class="post-metadata">

**Author:** ![Vadim\_Yalovets](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vadim_yalovets/32/43_2.png) [@Vadim\_Yalovets](https://forums.percona.com/u/Vadim_Yalovets)\
**Post date:** [March 22, 2023, 10:32am UTC](https://forums.percona.com/t/how-to-create-a-custom-qan-dashboard-query-analytics/20725/10 "2023-03-22T10:32:20Z")

</div>

Please past the provided earlier query over a default one that is generated when table “metrics” is specified for an element.

 ![Screenshot 2023-03-22 at 12.29.48](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/2X/4/4083b066dee717e579f270ca626db37b4917e8d6.png)

---

<div class="post-metadata">

**Author:** ![Naresh9999](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/naresh9999/32/5568_2.png) [@Naresh9999](https://forums.percona.com/u/Naresh9999)\
**Post date:** [March 22, 2023, 4:01pm UTC](https://forums.percona.com/t/how-to-create-a-custom-qan-dashboard-query-analytics/20725/11 "2023-03-22T16:01:37Z")

</div>

Hi @Vadim_Yalovets

Here is the output for the above query. I can see only half query from the fingerprint column, even I tried to copy the query but its still copying half/partial portion of the query instead of full query.

Is there any restrictions for query length or something?

 ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/2X/a/a4251f4d1e51fb47da89b6f52e7eb17c01636e56.png)

---

<div class="post-metadata">

**Author:** ![Vadim\_Yalovets](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vadim_yalovets/32/43_2.png) [@Vadim\_Yalovets](https://forums.percona.com/u/Vadim_Yalovets)\
**Post date:** [March 22, 2023, 5:18pm UTC](https://forums.percona.com/t/how-to-create-a-custom-qan-dashboard-query-analytics/20725/12 "2023-03-22T17:18:30Z")

</div>

Do you see cut queries in QAN as well?

---

<div class="post-metadata">

**Author:** ![Naresh9999](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/naresh9999/32/5568_2.png) [@Naresh9999](https://forums.percona.com/u/Naresh9999)\
**Post date:** [March 23, 2023, 3:43am UTC](https://forums.percona.com/t/how-to-create-a-custom-qan-dashboard-query-analytics/20725/13 "2023-03-23T03:43:13Z")

</div>

Yes @Vadim_Yalovets  
I see few queries even in QAN as well.

---

<div class="post-metadata">

**Author:** ![Vadim\_Yalovets](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vadim_yalovets/32/43_2.png) [@Vadim\_Yalovets](https://forums.percona.com/u/Vadim_Yalovets)\
**Post date:** [March 23, 2023, 1:28pm UTC](https://forums.percona.com/t/how-to-create-a-custom-qan-dashboard-query-analytics/20725/14 "2023-03-23T13:28:24Z")

</div>

So in such case you should create a [jira ticket](https://jira.percona.com/) regarding queries in QAN.

---

<div class="post-metadata">

**Author:** ![Naresh9999](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/naresh9999/32/5568_2.png) [@Naresh9999](https://forums.percona.com/u/Naresh9999)\
**Post date:** [March 23, 2023, 1:31pm UTC](https://forums.percona.com/t/how-to-create-a-custom-qan-dashboard-query-analytics/20725/15 "2023-03-23T13:31:20Z")

</div>

Sure @Vadim_Yalovets

Can you please suggest me which slow log source is recommended?

1. Perfschema
2. Slow\_log.log file

---

<div class="post-metadata">

**Author:** ![Vadim\_Yalovets](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vadim_yalovets/32/43_2.png) [@Vadim\_Yalovets](https://forums.percona.com/u/Vadim_Yalovets)\
**Post date:** [March 23, 2023, 5:48pm UTC](https://forums.percona.com/t/how-to-create-a-custom-qan-dashboard-query-analytics/20725/16 "2023-03-23T17:48:05Z")

</div>

There is no a preferred source.You can find [the benefits and drawbacks](https://docs.percona.com/percona-monitoring-and-management/setting-up/client/mysql.html#choose-and-configure-a-source) of _Slow query log_ and _Performance Schema_ metrics sources in PMM documentation.

---

<div class="post-metadata">

**Author:** ![Naresh9999](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/naresh9999/32/5568_2.png) [@Naresh9999](https://forums.percona.com/u/Naresh9999)\
**Post date:** [March 23, 2023, 7:40pm UTC](https://forums.percona.com/t/how-to-create-a-custom-qan-dashboard-query-analytics/20725/17 "2023-03-23T19:40:07Z")

</div>

Thanks @Vadim_Yalovets for the detailed information. I thought QAN will show full query details depend up on the slow log source.

---

<div class="post-metadata">

**Author:** ![steve.hoffman](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/steve.hoffman/32/66_2.png) [@steve.hoffman](https://forums.percona.com/u/steve.hoffman)\
**Post date:** [March 24, 2023, 2:47pm UTC](https://forums.percona.com/t/how-to-create-a-custom-qan-dashboard-query-analytics/20725/18 "2023-03-24T14:47:00Z")

</div>

What version of PMM (server and client)?

in PMM 2.32.0 a new parameter was introduced that allowed you to tune the [max query length](https://docs.percona.com/percona-monitoring-and-management/details/commands/pmm-admin.html#mysql) (see max-query-length flag, previously we were truncating at 2048 chars).

you likely need to remove and re-add the node with a larger number or -1 if you want unlimited.

---

<div class="post-metadata">

**Author:** ![Naresh9999](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/naresh9999/32/5568_2.png) [@Naresh9999](https://forums.percona.com/u/Naresh9999)\
**Post date:** [March 24, 2023, 3:54pm UTC](https://forums.percona.com/t/how-to-create-a-custom-qan-dashboard-query-analytics/20725/19 "2023-03-24T15:54:08Z")

</div>

Hi @steve.hoffman

PMM server version is 2.35.  
Client version is 2.33

If we remove node and add node then, we will loose existing data right? Without loosing existing data can’t we add this on top existing command?

I see the pmm-admin add mysql --help default value is -1 then it means by default it should be unlimited?

 ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/2X/4/49d28f7c6032f01a07bf8465d43c80fc1b319b9e.png)

---

<div class="post-metadata">

**Author:** ![steve.hoffman](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/steve.hoffman/32/66_2.png) [@steve.hoffman](https://forums.percona.com/u/steve.hoffman)\
**Post date:** [March 24, 2023, 7:33pm UTC](https://forums.percona.com/t/how-to-create-a-custom-qan-dashboard-query-analytics/20725/20 "2023-03-24T19:33:34Z")

</div>

Hmmm…I’ll raise this to the dev team because the docs say the default is 2048 and it’s not quite clear what the --help is suggesting. I read that as “default is server-defined” which means nothing to me, then -1 would mean unlimited as a hint.

You can remove a node and re-add it with additional options and as long as the node name doesn’t change the metrics will accumulate under the old node name as you would expect. Even if you rename the node, the metrics will still be there for the “old name” until they age out according to your retention policy (30 days by default).

[Next page](https://forums.percona.com/t/how-to-create-a-custom-qan-dashboard-query-analytics/20725.md?page=2)