2

I've installed MySQL server with many databases that use the InnoDB engine. With lots of procedures in each DB, mysql.proc table contains more than 250,000 rows and it becomes ultimately slow when I need to run a query like this one:

SHOW PROCEDURE STATUS WHERE db LIKE '%SomeDB%';

The above query takes more than 5 seconds to execure and tweaking MySQL configuration parameters such as myisam_sort_buffer_size or key_buffer_size (which where already large enough) made no difference. I've 256GB memory installed on my server and I don't think it is because of any kind of memory shortage, maybe some misconfiguration that I have no Idea about it. Any hint?

Improve this question
4
  • @RickJames I do not fetch nor need this information, but seems that the new version of Connector/J does! (after I upgraded from Connector/J 5 to 8, I see that lots of lots of these statements are being called at tomcat startup that causes my application stuck at this stage). I use MySQL 5.7 community edition, I have many (+300) similar but distinct databases (all use InnoDB), each one with lots of functions and procedures.
    – Ehsan Khodarahmi
    Commented Nov 3, 2020 at 16:41
  • @FedericoRazzoli No matter when the procedure is created and wheter it is used or not, result is almost the same. mysql.proc as well as all other tables in mysql db are MyISAM.
    – Ehsan Khodarahmi
    Commented Nov 3, 2020 at 16:44
  • So it's not a metadata lock. Your query is slow because it cannot use an index. 1 Can you use LIKE 'dbname%' instead of LIKE '%dbname%'? 2 If so, do it, and then check if there is an index whose first column is db. If not, create an index on db.
    – Federico Razzoli
    Commented Nov 3, 2020 at 16:50
  • If each database has the same functions, consider creating a separate database for holding all the common functions and procs. Then use dbname.functionname syntax to access them. (And set up suitable GRANTs for users.)
    – Rick James
    Commented Nov 3, 2020 at 17:30

2 Answers 2

Reset to default
4

The "meta" commands (SHOW...) are generally not designed for performance. The presumption is that you rarely, if ever, need to run them. And when you do, it is more of a "one-time" operation.

Please migrate from MyISAM to InnoDB. (This will probably have no effect on the Question at hand, but is important for many other reasons.)

Before 8.0, the information for many of the SHOW commands was scattered around various files. For example, each table had a .frm file containing, effectively, the CREATE TABLE information. "File open" took time, leading to slowness.

8.0 moved all that info into InnoDB tables so, in theory, it should be straightforward to fetch all the data more efficiently. However, the implementation seems to involve layers of VIEWs and inadequate indexing.

In any case, 250K of anything takes time. I could have mentioned the leading wildcard (%) on the LIKE because it prevents the use of an index. However, I am not convinced that there is a relevant index.

Improve this answer
4
  • 1
    I remember MySQL 5.6 having issues with this. When I ran it, a ton of debug into would appear. So, I would do SELECT db,name FROM mysql.proc and SHOW CREATE TABLE db.name\G. I have long stopped using SHOW PROCEDURE STATUS
    – RolandoMySQLDBA
    Commented Nov 3, 2020 at 15:28
  • As I mentioned my DBs are already InnoDB, and actually I fell in trouble when I was upgrading from Connector/J 5 to 8 (which my app depends on for creating a connection pool in tomcat). For whatever reason, it sounds that this new version calls so many SHOW PROCEDURE STATUS at tomcat startup and causes app to literally hangs!
    – Ehsan Khodarahmi
    Commented Nov 3, 2020 at 16:25
  • 1
    File a bug report.
    – Rick James
    Commented Nov 3, 2020 at 17:26
  • That was only 5.6 I saw this. It goes EOL Feb 2021
    – RolandoMySQLDBA
    Commented Nov 3, 2020 at 23:32
3

Please note that most of INFORMATION_SCHEMA is implemented as temporary memory tables (See my old post How is INFORMATION_SCHEMA implemented in MySQL?)

When mysqld starts, information from mysql.proc is loaded into INFORMATION_SCHEMA.ROUTINES.

When you use SHOW PROCEDURE STATUS, the info is retrieved from INFORMATION_SCHEMA.ROUTINES.

Since temp tables will have .frm, Rick already mentioned accessing it would make some slowness. This my be due to I/O if mysqld checks mysql.proc and/or INFORMATION_SCHEMA.ROUTINES.

See also

So, basically, you are at the mercy of how mysqld checks for stored procedures to see it is up-to-date in memory.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.