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?

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
Ehsan Khodarahmi
  • 139
  • 5

2 Answers2

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.

Rick James
  • 80,479
  • 5
  • 52
  • 119
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.

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536