MariaDB: slow stored procedures, performance schema vs slow log

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

Heyo! I’ve been using PMM to find and resolve performance issues, and i’m running into a slight hurdle:

QAN shows that some stored procedures are quite slow. Now, i know its entirely possible to find out why its slow without something like PMM (SET profiling=1; CALL procedure…; SHOW profiles;) but i have to do that for every stored procedure, only to then find out that for some the effort to optimize them is too large to be worth it.

I think ideally, PMM would be able to “see inside” stored procedures somehow. Maybe thats already possible, but i want to ask to make sure that i’m not breaking things, as i could not find any documentation about this.

I’m currently using performance schema, as the docs recommend that for recent MariaDB versions.

MariaDB has a variable log_slow_disabled_statements which defaults to 'sp'. If i remove sp from it, the slow log will contain the individual queries run from within a stored procedure.

Now, my questions:

  1. Is there a config i’ve missed which either enables the PMM client to read the individual queries done by a procedure from performance schema, or makes MariaDB put those queries into performance schema in the first place?
    2.Does PMM/QAN understand the # Stored_routine: db.ProcedureName syntax which links the individual queries to the procedure?
  2. Does PMM/QAN properly calculate the time which is logged for the individual quries inside a stored procedure? It would have to subtract the difference between this and the previous query from within the same # Stored_routine: db.ProcedureName as the query time seems to be a rolling sum.
2

I think i partially answered myself. After writing this i thought “surely, it’d be simple enough to find out if the slow log stored procedures are supported by searching through the code” and looking at the slow log parser theres nothing that looks like it matches Stored_routine. In the whole repo theres basically no mention of Stored_routine or routine or Stored in a context that makes it seem like its handling the query times.

So it seems like slow log without the sp filter is not just unsupported, but will lead to tons of flawed data since each part of the stored procedure will count as sum(previous procedure statements) + self time instead of just self time. That means a simple SELECT 1+1; could show up as taking 1 hour to complete if its at the end of a 1 hour long procedure (as an extreme example).