So many queries to get the name of procedure from mysql.proc

MySQL & MariaDB Percona Server for MySQL 5.7
1

Whenever we are querying process list there some queries we can see like SELECT db, name, type, comment FROM mysql.proc where db=‘test’ and name like ‘test_proc’ ORDER BY name, type
and when the load increases all threads are showing this query only with different proc name and and CPU going to 100% and in cpu only system CPU is high 70~80 %
Also this query show taking 100ms~200ms

we are having 2000 procedures

why this query need to executed and how we can resolve this ?

2

Hello @erpramodprajapat,
I would suggest enabling the slow query log, and set long_query_time=0 to capture metrics on this query which will tell you what user is executing the query and from what IP. Then you can track it down in the code and remove it.

3

This query is not any code , I think MySQL itself executing it when application code calling a procedure

4

MySQL itself does not run any SQL. I suggest you try to audit where this query is coming from.

5

I suspected same and searched code base but didn’t find this query, whereas on some mysql documentations says that one sp_find() method call this query to get the procedure header,

6

Did you enable any MySQL logs such as slow log or general query log? This will be the definitive answer.

7

Yes, I have this query in slow logs when load increases, in a hour its logged 72K times with different db and different proc names, I have configured slow queries for more than 100ms, its user is showing as application user and host is also showing the application ip.
But in code we are not having this query that I am pretty sure .

8

Then this query is coming from your application. It may not be your code; it may be some library or ORM you are using.

The better question now is ‘why does your application user have permissions to SELECT from any system tables?’ You should review the GRANTs for the app user and restrict them down to only necessary privileges. Normal apps do not need to select from any tables in the mysql system database.

9

@erpramodprajapat

If it’s java, consider adding cacheCallableStmts=true to your jdbc connection string.

https://dev.mysql.com/doc/connector-j/en/connector-j-connp-props-performance-extensions.html#cj-conn-prop_cacheCallableStmts

What is happening is that Java has a parsing stage and it has to query the database for stored procedure metadata before each procedure call. If you tell java to cache it, it will no longer do this query.

The partner parameter to this is callableStmtCacheSize. This is set to 100 (meaning 100 statements). If your threads each process more than 100 unique procedures you can raise this default from 100 to something higher, but 100 should work for most people, so you do not have to add the callableStmtCacheSize parameter to the connection string (in addition to cacheCallableStmts) unless necessary.