Few times a normal query/proc is taking time to execute

MySQL & MariaDB Percona Server for MySQL 5.7
,
1

I ma having procedure that executing well its only scanning 2 rows max and 99.5% times its executing well less than 100ms, but at random times its taking 1.5 seconds or more sometimes, I have PMM in place, I explored locks there is no spikes in locks graph also, I have went through all the graphs not find any suspicious.
So why this taking some time more time, what else can contribute other than locks , what else I can explore what was going inside that time in database.

2

Hello @erpramodprajapat,

You need to review the procedure and queries inside - check for explain plan for the queries to understand what part of the procedure is taking longer and act accordingly.

Thanks,
K

3

I have done explain of every query , its looks fine, there is nothing with queries normally same queries are working fine, some random time only suddenly its taking time

4

@erpramodprajapat We need more information… Procedure, queries, definitions, explain plan etc to comment on this… or else it will only be guess work…
For eg,

  • at times proc is processing large amount of data, the other time it is not!
  • you have other queries overriding the bufferpool data and thus proc might need to read from disk!
5

Hi erpramodprajapat,

The same query/SP can take longer depending on the input parameters.

For example, is not the same checking how many bank transactions happening on a single day than on an entire year. Same query with a different input parameters not only will scan a different amount of rows but also can potentially have a different execution plan.

Do you know what input parameters make the SP take longer to reproduce the issue at will?
If not then examining a stored procedure with upstream MySQL can be challenging but not with Percona Server.

With upstream MySQL, enabling slow query log will only log the SP call but with Percona Server you can enable extended verbosity (Slow Query Log - Percona Server for MySQL) and every query inside the stored procedure will be logged. Then you can examine the slow query log and identify the exact input parameters that make the SP take longer and decide based on what you find.

Regards