How to create a custom QAN dashboard{Query Analytics}

Percona Monitoring and Management (PMM)
21

Hi @steve.hoffman

Thanks for the help.

If slow query source is slow_log file then will it store full query details in the pmm.metrics?
if slow query source is perfschema then will it store only partial query in the pmm.metrics?

Why because today I observed that one instance source is slow log file then its showing full query details. And another instance source is perfschema then its showing partial query(half query) in the pmm.metrics table. Can you please suggest me which source is better in terms of QAN metrics?

22

Hi @steve.hoffman

Thanks for the help. Any update on the below?

If slow query source is slow_log file then will it store full query details in the pmm.metrics?
if slow query source is perfschema then will it store only partial query in the pmm.metrics?

Why because today I observed that one instance source is slow log file then its showing full query details. And another instance source is perfschema then its showing partial query(half query) in the pmm.metrics table.

Can you please suggest me which source is better in terms of QAN metrics?

23

Ok, got confirmation that the default behavior is 2048 characters and the team will fix the --help text from the pmm-admin add command to be clearer. In order to change that you’d need to remove/re-add the node with the truncation value of your choosing (-1 for unlimited).

Before I assume PMM’s treating the two datasources differently though, we should verify what you are seeing in a few places. First can you run the two queries through something like wc -m to get the character count of each.

Second, you might want to look and see how your mysql server is setup too. I’m not aware of any limits for query length on slowlog on the server side (but there could be :man_shrugging: ) but I know you have some options on perf schema.

run SHOW VARIABLES LIKE 'perf%length'; and you should see two values one for max digest length and one for max sql text length 
what’s the value and is it the same as what your character count results were for the truncated perf schema query? If so that’s a setting on your side (I googled and it could be that the 5.7 default is 1024) that you’d have to change. So you may first be running into a truncation issue on your server
then running into a second issue of truncation in PMM. Just note that I think to change performance_schema_max_digest_length, you’d also need to change the server variable max_digest_length.

Before changing anything, I’d consult a real DBA (which I am not) as changing could have impacts on your systems performance!

24

Hi @steve.hoffman

Thank you so much for the detailed information. I will check few queries and then I will add the max-query-length parameter.