Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Cannot see long queries

Description

If you have a big query, a long string, PMM is unable to display the query. There are no scollbars to see the rest.

Additionally, long queries seem to cause issues with the 'explain' tab where PMM is not sending the entire query to be explain'd

 

FR and Documentation  request:

https://jira.percona.com/browse/PMM-6692?focusedCommentId=269339&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel 

How to test

None

How to document

None

AFFECTED CS IDs

CS0013164

Attachments

9
  • 06 Nov 2020, 01:44 AM
  • 20 Oct 2020, 12:52 PM
  • 12 Oct 2020, 12:15 PM
  • 12 Oct 2020, 12:13 PM
  • 12 Oct 2020, 10:58 AM
  • 09 Oct 2020, 01:23 PM
  • 09 Oct 2020, 01:22 PM
  • 09 Oct 2020, 01:19 PM
  • 25 Sep 2020, 07:33 PM

Smart Checklist

Activity

Show:

Lalit Choudhary November 11, 2020 at 7:21 AM

  and

This is the fix from mysql side. all we need is documentation in pmm for these settings.

Setting to Fix this issue:

mysql my.cnf settings:  increasing digest length

 

max_digest_length=4000 performance_schema_max_digest_length=4000

 

mysql [localhost] {msandbox} ((none)) > show variables like '%digest_length'; +--------------------------------------+-------+ | Variable_name | Value | +--------------------------------------+-------+ | max_digest_length | 4000 | | performance_schema_max_digest_length | 4000 | +--------------------------------------+-------+ 2 rows in set (0.00 sec)

M November 11, 2020 at 1:47 AM

The "Configuring Performance Schema" docs are missing those consumers that are required for examples/explains to work at all(at least with RDS):

events_statements_current events_statements_history global_instrumentation thread_instrumentation statements_digest

 

performance_schema_max_digest_length sets the length for the normalized query/digest
performance_schema_max_sql_text_length sets the length for examples/explains

Matthew Boehm November 6, 2020 at 1:45 AM

 I hit this today with client. I made a video. Please see zoom_0.mp4

These two are the solution?
max_digest_length=4000
performance_schema_max_digest_length=4000

Lalit Choudhary October 12, 2020 at 12:23 PM
Edited

So this is not a bug in PMM. But this could be a documentation + Feature request as follows,

Documentation Bug:

we can do is document this behavior as a note when using  query-source=perfschema in pmm Query Analytics documentation on how long Query digest affected due to mysql max_digest_length and performance_schema_max_digest_length  default setting. 

https://www.percona.com/doc/percona-monitoring-and-management/2.x/qan-intro.html

Feature request:

The current message in the PMM QAN dashboard for the query under Examples, Explain, and Tables tab for such long query cases is not clear and the user may not understand the cause. Hence PMM QAN should identify such long query Query truncation issues and print valid messages under these tabs that due to mysql max_digest_length and performance_schema_max_digest_length  default settings this query Examples, Explain, and Tables can not be print.

 

 

Lalit Choudhary October 12, 2020 at 12:18 PM

After checking this issue found that this is due to mysql  DIGEST_TEXT length limit setting controlled by   max_digest_length bytes, and performance_schema_max_digest_length bytes.

Ref: https://dev.mysql.com/doc/refman/5.7/en/performance-schema-statement-digests.html

DIGEST_TEXT is the text of the normalized statement digest. This is a copy of the original normalized statement that was computed to a maximum of max_digest_length bytes, further truncated as necessary to performance_schema_max_digest_length bytes.

 

Example:

 

mysql [localhost] {msandbox} (performance_schema) > select * from events_statements_summary_by_digest where schema_name='test' and digest='9fa3fd4ec9780e36fa4a5732ce22e55c'\G *************************** 1. row *************************** SCHEMA_NAME: test DIGEST: 9fa3fd4ec9780e36fa4a5732ce22e55c DIGEST_TEXT: CREATE TABLE `test` . `t4` ( `id` INTEGER (?) NOT NULL AUTO_INCREMENT , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak01` TINYINT (?) DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak02` SMALLINT (?) DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak03` MIDDLEINT (?) DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak04` INTEGER (?) DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak05` INT8 (?) DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak06` FLOAT4 DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak07` FLOAT8 DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak08` DECIMAL (...) DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak09` DATE DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak10` DATETIME DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak11` TIMESTAMP NOT NULL DEFAULT NOW ON UPDATE NOW , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak12` TIME DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak13` YEAR (?) DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak14` VARCHARACTER (?) DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak15` CHARACTER (?) DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak16` BLOB , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak17` TEXT , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak18` MEDIUMTEXT , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak19` MEDIUMBLOB , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak20` COUNT_STAR: 4 SUM_TIMER_WAIT: 145410216000 MIN_TIMER_WAIT: 321451000   mysql [localhost] {msandbox} (performance_schema) > desc events_statements_summary_by_digest; +-----------------------------+---------------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------------+---------------------+------+-----+---------------------+-------+ | SCHEMA_NAME | varchar(64) | YES | | NULL | | | DIGEST | varchar(32) | YES | | NULL | | | DIGEST_TEXT | longtext | YES | | NULL | | | COUNT_STAR | bigint(20) unsigned | NO | | NULL | | | SUM_TIMER_WAIT | bigint(20) unsigned | NO | | NULL | | | MIN_TIMER_WAIT | bigint(20) unsigned | NO | | NULL | |

 

Setting to Fix this issue:

mysql my.cnf settings:  increasing digest length

 

max_digest_length=4000 performance_schema_max_digest_length=4000

 

 

mysql [localhost] {msandbox} ((none)) > show variables like '%digest_length'; +--------------------------------------+-------+ | Variable_name | Value | +--------------------------------------+-------+ | max_digest_length | 4000 | | performance_schema_max_digest_length | 4000 | +--------------------------------------+-------+ 2 rows in set (0.00 sec)

a full query showing on PMM- QAN

 

mysql [localhost] {msandbox} (test4) > select * from performance_schema.events_statements_summary_by_digest where schema_name='test4' and digest='f477afc3a0ff6669f33287c8c8c361b4'\G *************************** 1. row *************************** SCHEMA_NAME: test4 DIGEST: f477afc3a0ff6669f33287c8c8c361b4 DIGEST_TEXT: CREATE TABLE `test4` . `t44` ( `id` INTEGER (?) NOT NULL AUTO_INCREMENT , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak01` TINYINT (?) DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak02` SMALLINT (?) DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak03` MIDDLEINT (?) DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak04` INTEGER (?) DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak05` INT8 (?) DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak06` FLOAT4 DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak07` FLOAT8 DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak08` DECIMAL (...) DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak09` DATE DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak10` DATETIME DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak11` TIMESTAMP NOT NULL DEFAULT NOW ON UPDATE NOW , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak12` TIME DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak13` YEAR (?) DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak14` VARCHARACTER (?) DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak15` CHARACTER (?) DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak16` BLOB , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak17` TEXT , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak18` MEDIUMTEXT , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak19` MEDIUMBLOB , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak20` LONGBLOB , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak21` LONGTEXT , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak22` MEDIUMTEXT , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak23` VARCHARACTER (?) DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak24` VARBINARY (?) DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak25` ENUM (...) DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak26` SET (...) DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak27` FLOAT4 (...) DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak28` FLOAT8 (...) DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak29` FLOAT4 DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak30` FLOAT8 DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak31` DECIMAL (...) DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak32` DATE DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak133` DATETIME DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak114` TIMESTAMP NOT NULL DEFAULT NOW ON UPDATE NOW , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak124` TIME DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak134` YEAR (?) DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak144` VARCHARACTER (?) DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak154` CHARACTER (?) DEFAULT ? , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak164` BLOB , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak174` TEXT , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak1232` MEDIUMTEXT , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak129` MEDIUMBLOB , `tcolsdakgfkdsfdsfsgjfskgkfgkdagfkak204` LONGBLOB , PRIMARY KEY ( `id` ) ) ENGINE = `InnoDB` COUNT_STAR: 1 SUM_TIMER_WAIT: 48262314000 MIN_TIMER_WAIT: 48262314000

 

 

Details

Assignee

Reporter

Priority

Labels

Needs QA

Yes

Needs Doc

Yes

Affects versions

Parent

Smart Checklist

Created September 25, 2020 at 7:34 PM
Updated October 9, 2024 at 8:36 AM
Configure

Flag notifications