QAN: unused index columns and full tbl scan columns are always empty

Percona Monitoring and Management (PMM) PMM 3.x
1

Hi Team,

Full table or unused index columns are always zero in the QAN dashboard.
Can someone help me with the below? Why are we not able to see the below unused index or full table columns data?

**m_full_scan_cnt!=0 or m_full_scan_sum !=0 or m_no_index_used_cnt !=0 or m_no_index_used_sum !=0 or m_no_good_index_used_cnt !=0 or **
**m_no_good_index_used_sum != 0**

PMM server version: 3.1.0
MariaDB version: 10.6
Query Source: slowlog

SELECT
    any(node_name) as Node_name,
    any(service_name) as App_Name,
    any(schema) as Schema,
    any(username) as Service_account,
    fingerprint Query,
    any(example) Sample_Query,
    sum(m_full_scan_sum)/sum(m_full_scan_cnt)*1000 as Full_TBL_or_Unused_index_Score,
    sum(m_query_time_sum)/sum(m_query_time_cnt)*1000 as Latency_ms,
    sum(num_queries)/($__to-$__from)*1000 QPS,
    sum(m_rows_examined_sum)/sum(m_rows_sent_sum) as Rows_exam_per_sent,
    sum(m_rows_examined_sum)/($__to-$__from)*1000 as Rows_examined,
    sum(m_rows_affected_sum)/($__to-$__from)*1000 as Rows_affected
FROM pmm."metrics"
WHERE $__timeFilter(period_start) and (m_full_scan_cnt!=0 or m_full_scan_sum !=0 or m_no_index_used_cnt !=0 or m_no_index_used_sum !=0 or m_no_good_index_used_cnt !=0 or 
m_no_good_index_used_sum != 0)
GROUP BY fingerprint
ORDER BY (Full_TBL_or_Unused_index_Score) desc limit 10;
2

Can you share your slowlog file? We need to test it.

3

Hi @nurlan

I am sharing the details, which are that the query does not have any index on the column and is scanning the entire table.

Here the below select query is scanning the entire table, but we are not seeing that information in the logs as well.

CREATE TABLE tt_ent_log_tbl (
tt_ent_log_asoctn_id bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘Surrogate key’,
tt_ent_log_id bigint(20) NOT NULL COMMENT ‘OT Event Log Id’,
entity_type varchar(20) DEFAULT NULL COMMENT ‘The type of the entity for which the event occured’,
entity_id bigint(20) DEFAULT NULL COMMENT ‘The id of the object for which the event occured’,
status char(1) NOT NULL DEFAULT ‘A’ COMMENT ‘Value is “A” or “I”, to indicate status is Active or Inactive’,
crt_user varchar(255) NOT NULL COMMENT ‘Creation User’,
crt_dt datetime NOT NULL DEFAULT current_timestamp() COMMENT ‘Creation Timestamp’,
last_upd_user varchar(255) NOT NULL COMMENT ‘Last Update User’,
last_upd_dt datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT ‘Last Update Timestamp’,
source_sys varchar(255) DEFAULT NULL COMMENT ‘Source from which record came from’,
PRIMARY KEY (tt_ent_log_asoctn_id),
KEY idx_tt_ent_log_assostn_02 (tt_ent_log_id),
KEY idx_tt_ent_log_assostn_01 (entity_id,entity_type)
) ENGINE=InnoDB;

MariaDB [tds]> select * from tt_ent_log_tbl where status=‘L’;
Empty set (1 min 27.678 sec)

MariaDB [tds]> explain select * from tt_ent_log_tbl where status=‘L’;
±-----±------------±--------------------------±-----±--------------±-----±--------±-----±---------±------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±-----±------------±--------------------------±-----±--------------±-----±--------±-----±---------±------------+
| 1 | SIMPLE | ot_event_log_associations | ALL | NULL | NULL | NULL | NULL | 30971587 | Using where |
±-----±------------±--------------------------±-----±--------------±-----±--------±-----±---------±------------+
1 row in set (0.000 sec)

MariaDB [tds]> select count(1) from tt_ent_log_tbl;
±---------+
| count(1) |
±---------+
| 37709813 |
±---------+
1 row in set (27.457 sec)

SET timestamp=1745419812;
set autocommit=1;

User@Host: ts_ap_usr[ts_ap_us] @ [10.6.12.142]

Thread_id: 1155096 Schema: tds QC_hit: No

Query_time: 0.000417 Lock_time: 0.000156 Rows_sent: 1 Rows_examined: 2

Rows_affected: 0 Bytes_sent: 1377

SET timestamp=1745419812;
select v1_0.addr_id,v1_0.addr_detail,v1_0.addr_line_1,v1_0.addr_line_2,v1_0.addr_line_3,v1_0.cas_loc_key,v1_0.city,v1_0.cntry_cd,v1_0.comm_id,v1_0.comm_type_c
d,v1_0.county,v1_0.crt_dt,v1_0.crt_user,v1_0.last_upd_dt,v1_0.last_upd_user,v1_0.postal_cd,v1_0.postal_cd2,v1_0.source_sys,v1_0.state_cd,v1_0.status from addr
esses v1_0 where v1_0.comm_id=26135451 and v1_0.status=‘A’ order by v1_0.last_upd_dt desc;

Time: 250423 7:48:51

User@Host: tpd[temp_usr] @ localhost

Thread_id: 1158802 Schema: tds QC_hit: No

Query_time: 81.177402 Lock_time: 0.000183 Rows_sent: 0 Rows_examined: 37709366

Rows_affected: 0 Bytes_sent: 1046

SET timestamp=1745419731;
select * from tt_ent_log_tbl where status=‘L’;

Time: 250423 7:50:12

User@Host: vh_ap_ur[vh_ap_ur] @ [10.1.15.160]

Thread_id: 1159971 Schema: tds QC_hit: No

Query_time: 0.000603 Lock_time: 0.000395 Rows_sent: 8 Rows_examined: 8

Rows_affected: 0 Bytes_sent: 807

SET timestamp=1745419812;
DESCRIBE location_types;

User@Host: ts_ap_ur[ts_ap_ur] @ [10.18.92.142]

Thread_id: 1155096 Schema: tds QC_hit: No

Query_time: 0.000276 Lock_time: 0.000114 Rows_sent: 1 Rows_examined: 2

Rows_affected: 0 Bytes_sent: 982

SET timestamp=1745419812;
select v1_0.vendor_pref_id,v1_0.crt_dt,v1_0.crt_user,v1_0.facility_id,v1_0.last_upd_dt,v1_0.last_upd_user,v1_0.pref_cat_cd,v1_0.pref_cd,v1_0.pref_id,v1_0.source_sys,v1_0.status,v1_0.vendor_id from vend_prnces v1_0 where v1_0.vendor_id=24093 and v1_0.pref_id!=0 and v1_0.pref_cat_cd=‘PH_USG’ and v1_0.status=‘A’ and v1_0.pref_cd=‘PPH’ order by v1_0.last_upd_dt desc;

User@Host: tm_ap_usr[tm_ap_usr] @ [10.18.12.142]

Thread_id: 1155096 Schema: tds QC_hit: No

Query_time: 0.000286 Lock_time: 0.000119 Rows_sent: 1 Rows_examined: 2

Rows_affected: 0 Bytes_sent: 922

SET timestamp=1745419812;
select v1_0.phone_num_id,v1_0.cas_loc_key,v1_0.comm_id,v1_0.comm_type_cd,v1_0.crt_dt,v1_0.crt_user,v1_0.last_upd_dt,v1_0.last_upd_user,v1_0.phone_ext,v1_0.phone_num,v1_0.source_sys,v1_0.status from ph_ners v1_0 where v1_0.comm_id=26135457 and v1_0.status=‘A’ order by v1_0.last_upd_dt desc;

User@Host: tm_ap_ur[tm_ap_ur] @ [10.14.12.142]

Thread_id: 1155096 Schema: tds QC_hit: No

:

4

@nurlan Can you please help me with the issue?

5

Hi, would be good if you could put into code block.

6

Hi @Naresh9999,
So far I see that your slowlog output doesn’t have info about full_scan and no_good_index_used and that’s why there are no information about that in QAN.

Please check if slow log verbosity has correct value Slow Query Log Extended Statistics - MariaDB Knowledge Base

1 Like
7

Sure, @nurlan

I will check and let you know the details.

8

Hi @nurlan

It’s showing empty. I have set the value to full, and now I am able to see full scan details for the above query.

Thank you so much, @nurlan, for the help.

MariaDB [(none)]> show global variables like ‘%log_slow_verbosity%’;
±-------------------±------+
| Variable_name | Value |
±-------------------±------+
| log_slow_verbosity | |
±-------------------±------+
1 row in set (0.001 sec)

1 Like
9

@nurlan After enabling the log_slow_verbosity to all, I can see all of the information, but here I don’t see unused index-related information.

image
image1530×222 10.2 KB