PMM QAN Example doesn't show the whole query

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

Hi guys,

When I click on the Example tab in QAN, it doesn’t show the whole query, in cases where the query itself is rather large (long).
If so I can guess it’s partly a mysql slow logging setup, but I can’t find any parameter to set up a treshold on how many characters or similar is the limit of the query line length to log in slow logs. Or am I diging on the wrong place? I need it to show longer queries in the Example, that’s my point
Any ideas about this?

1 Like
2

@clouseau Can you verify that you are collecting QAN metrics from the slow query log and not the performance_schema? Use the pmm-admin tool on the client to check.

1 Like
3

Thanks for the reply.

I didn’t add any choice during configuration, and according to the docs, it defaults to slowlog.
The pmm-admin status says:
…
PMM Client:
Connected : true
Time drift : -48.099µs
Latency : 12.650329ms
pmm-admin version: 2.26.0
pmm-agent version: 2.26.0
Agents:
/agent_id/b412f40c-08a3-4acb-bfb0-2f7f559b4570 mysqld_exporter Running
/agent_id/ce06aa98-82fd-494f-9cfd-7f87a030813d node_exporter Running
/agent_id/ebaa1715-4f23-48a3-9c09-5f26e28c0366 vmagent Running
/agent_id/ffdeed49-292e-414e-999e-e6805efbb6f6 mysql_slowlog_agent Running

is there a better way to determine this?

1 Like
4

You can see the slowlog_agent running. That’s the indication. The next step is to determine if this is PMM’s fault or something else. Can you find this query within the slow log, and is the entire query visible in the log, or is it truncated in the log? If the entire query is visible in the log, then this is a PMM issue and we would need to see the entire log section. If redacted/truncated in the log file, then there’s nothing PMM can do.

1 Like
5

Yeah, the query is in the slow_log as complete.
I’ll paste one here - the context is irrelevant, just want to give you an idea how unusaully large it is.

SELECT DISTINCT s.school_id, s.secondary, s.language as primary_language, s.currency, language.xru_language_value as language, language.language_value as language_id_en, s.school,s.school_country, s.school_city_
general, c.course_name, s.csn, s.xru_school_country as xschool_country, s.xru_school_city_general as xschool_city_general, s.coordinate_1, s.coordinate_2, false as xmore_information, c.includes_accommodation, s.
kasp_programme, s.exposure, s.exposure_most_popular, s.exposure_top_price, s.exposure_top_value, s.exposure_best_rated,
                                s.exposure_most_popular_country,
                                s.exposure_most_popular_city,
                                s.exposure_top_leisure_country,
                                s.exposure_top_leisure_city,
                                s.exposure_best_price_country,
                                s.exposure_best_price_city,
                                s.exposure_best_rated_country,
                                s.exposure_most_revs_city,
                                s.commission_tuition,
                                s.commission_accommodation,
                                s.commission_other,
                                s.commission_inscription_fee,
                                s.chain_id,
                                s.bildungsurlaub,
                                s.bundeslaender,
                                s.year_foundation,
                                s.free_cancellation_course_days,
                                s.course_material_costs,
                                s.course_material_costs_period,
                                (SELECT COUNT(*) FROM registration as r WHERE r.school_id = s.school_id AND r.appl_date >= '20210420') as num_registrations,

                                (select COUNT(e.ev_overall*10) from evaluation as e WHERE (e.school_id=s.school_id and e.ev_overall>0) limit 1) ev_number, (select ROUND(AVG(e.ev_overall*10)) from evaluation as e WHERE (e.school_id=s.school_id and e.ev_overall>0) limit 1) ev_overall, (select ROUND(AVG(e.ev_teaching*10)) from evaluation as e WHERE (e.school_id=s.school_id and e.ev_teaching>0) limit 1) ev_teaching, (select ROUND(AVG(e.ev_activity*10)) from evaluation as e WHERE (e.school_id=s.school_id and e.ev_activity>0) limit 1) ev_activity, c.course_cat, country.coordinate_1 countryX, country.coordinate_2 countryY, country.zoom countryZ, s.hometuition as hometuition, s.discount as discount, cy.max_students, cy.start_date_every, cy.starting_dates as xstarting_dates, c.min_level, CONCAT(c.xru_course_comments_static,'<br/>',cy.xru_course_comments) as xcourse_comments, s.minimum_age_children, s.junior_level_no_year, s.junior_level_no_summer, s.student_no_year_junior, s.student_no_summer_junior, cy.exam_fee, cy.exam_dates, c.incl_accommo_type, c.incl_accommo_room_status, c.incl_accommo_meal, s.tuition_hour, s.student_no_summer, s.student_no_year, s.school_district, cy.time_afternoon_start, cy.time_afternoon_finish, c.exam_is_centre, img_names, IF (s.school_id IN (SELECT school_id FROM temp_top_schools),true,false) as top_school ,cur.currency_rate,cur.symbol as currency_rate_value, cur.currency_id as currency_id,cur_target.currency_rate as currency_target_rate, cur_target.symbol as currency_target_value,(SELECT yd.inscription_fee FROM year_data as yd WHERE yd.school_id=s.school_id and yd.year='2022' limit 1) as inscription_fee FROM school s LEFT JOIN country on (s.school_country = country.country) LEFT JOIN course1 c
                                ON s.school_id=c.school_id  and c.course_cat = (SELECT course1.course_cat FROM course1, `course_category`, course_year cy2, course_language cl  where course1.course_id=cy2.course_id AND cy2.year='2022' AND (cy2.hours>='1' OR s.secondary = 2) and cy2.deleted != '1' AND course1.counse_id = cl.course_id AND (cl.lang_id = 2 OR s.secondary = 2) and course1.course_cat=course_category.course_category_id and course1.school_id=s.school_id ORDER BY course_category.super_id ASC, course_category.course_priority ASC limit 1)
                         JOIN course_category cc
                                ON (cc.course_category_id = c.course_cat OR (cc.course_category_id = c.course_cat2))
                                AND (cc.course_priority IS NOT NULL)
                        LEFT JOIN course_categories_super ccs
                                ON (ccs.super_id = cc.super_id)
                        LEFT JOIN course_language c_lang
                                ON ( c.course_id = c_lang.course_id )LEFT JOIN language
                                          ON (s.language = language.language)   LEFT JOIN course_year cy on (c.course_id=cy.course_id AND cy.year='2022' AND (cy.hours>='1' OR s.secondary = 2)) LEFT JOIN images img ON (img.school_id=s.svhool_id AND img.shown_in_search=1) left join currency cur on (s.currency = cur.currency_id)  left join currency cur_target on (cur_target.currency_id = 1)  WHERE s.school_city_general in (SELECT school_city_general FROM city WHERE region_id =114) AND (IFNULL(s.discount,0)<2) AND forced_limited != 1 AND s.xru_school_city_general != '' and cy.deleted != '1' and (cy.price_1week > 0 or cy.price_2weeks > 0 or cy.price_3weeks > 0 or  cy.price_4weeks > 0 or cy.price_5weeks > 0 or cy.price_6weeks > 0 or cy.price_7weeks > 0 or cy.price_8weeks > 0 or cy.price_9weeks > 0 or cy.price_10weeks > 0 or cy.price_11weeks > 0 or cy.price_12weeks > 0 or (cy.price_custom_weeks > 0 and cy.addweek_valid > 0) or (cy.price_custom_weeks2 > 0 and cy.addweek_valid > 2) or (cy.price_custom_weeks3 > 0 and cy.asdweek_valid3 > 0) or (cy.price_custom_weeks > 4 and cy.addweek_valid > 4) or secondary = 2)  GROUP BY school_id ORDER BY school_country,school_city_general,secondary,school,ccs.super_priority, cc.course_priority;
1 Like
6

I know we truncate the query on the main line of the table but there’s a little icon at the end of the line something like (i) and I thought that would show the full query but I don’t know what we have the current limit at…yours appears to be 5851 characters…

1 Like
7

If you still can’t get the full query, I suggest opening a bug report on https://jira.percona.com/ so the team can investigate longer string storage.

1 Like
8

Clicking on (i) does show more, but still there’s a “…” at the end.
Another symptom that it’s incoplete, is that I get an error when I click on Explain

Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘…’ at line 23

…while on smaller (complete shown) queries, it works nicely.

1 Like
9

Then yes, that’s a PMM issue that needs to be addressed. If you can open a JIRA with your sample SQL, that will help the team diagnose the issue.

1 Like
10

Thanks,

created and issue
https://jira.percona.com/browse/PMM-9891

1 Like