Description:
The MEM agent seems to run the following query against the MEM service_manager:
SELECT GROUP_CONCAT('\\\\\n*', t.table_schema, '.', t.table_name) AS table_list
FROM information_schema.tables t
LEFT JOIN information_schema.table_constraints c ON (t.table_schema = c.table_schema
AND t.table_name = c.table_name
AND c.constraint_type IN ('PRIMARY KEY','UNIQUE'))
WHERE t.table_schema NOT IN ('mysql','information_schema')
AND t.engine NOT IN ('ARCHIVE','FEDERATED')
AND c.table_name IS NULL
;
This query is very slow when having many tables. See also issue.
How to repeat:
Create 30k tables and let the agent execute the query above.
This bug is dependent on bug #43818
http://bugs.mysql.com/bug.php?id=43818
I have filed it for possible workarounds for the MEM agent. The main stuff should be fixed within MySQL.
Suggested fix:
Better let us talk about workarounds:
A local MEMORY table would do the job about 100 times faster. When using appropriate indexes again about 4 times faster (in total 400 times).
But this MEMORY table consumes about 100 Mbyte for 30k tables (we should consider our recommendations to customers in our own product).
If this query is done more often a copy of the I_S table could help to speed up the query.
Description: The MEM agent seems to run the following query against the MEM service_manager: SELECT GROUP_CONCAT('\\\\\n*', t.table_schema, '.', t.table_name) AS table_list FROM information_schema.tables t LEFT JOIN information_schema.table_constraints c ON (t.table_schema = c.table_schema AND t.table_name = c.table_name AND c.constraint_type IN ('PRIMARY KEY','UNIQUE')) WHERE t.table_schema NOT IN ('mysql','information_schema') AND t.engine NOT IN ('ARCHIVE','FEDERATED') AND c.table_name IS NULL ; This query is very slow when having many tables. See also issue. How to repeat: Create 30k tables and let the agent execute the query above. This bug is dependent on bug #43818 http://bugs.mysql.com/bug.php?id=43818 I have filed it for possible workarounds for the MEM agent. The main stuff should be fixed within MySQL. Suggested fix: Better let us talk about workarounds: A local MEMORY table would do the job about 100 times faster. When using appropriate indexes again about 4 times faster (in total 400 times). But this MEMORY table consumes about 100 Mbyte for 30k tables (we should consider our recommendations to customers in our own product). If this query is done more often a copy of the I_S table could help to speed up the query.