We are using MySQL 8.0.17.8 and have an application build in Ruby on Rail(ruby 2.5.3p105). Whenever a connection is built with the database, as a part of gathering table field stats, it executes “show full fields from table_name” for multiple times. This is working fine in MySQL5.7 with no performance impact but in MySQL8 environment, the same query is using heavy CPU. We observe the below query is causing an issue contributing to more row scan/examine eventually increasing query latency and upsurging load. This seems system schema query triggered whenever we execute “show full fields from table_name”.
select cat.name as table_catalog , sch.name as table_schema , tbl.name as table_name , (col.name collate utf?_tolower_ci) as column_name , col.ordinal_position as ordinal_position , col.default_value_utf? as column_default , if ((col.is_nullable=?) , ?,…) as is_nullable , substring_index (substring_index (col.column_type_utf? , ?,…) , ?,…) as data_type , internal_dd_char_length (col.type , col.char_length , coll.name , ?) as character_maximum_length , internal_dd_char_length (col.type , col.char_length , coll.name , ?) as character_octet_length , if ((col.numeric_precision=?) , ? , col.numeric_precision) as numeric_precision , if (((col.numeric_scale=?) and (col.numeric_precision=?)) , ? , col.numeric_scale) as numeric_scale , col.datetime_precision as datetime_precision , (case col.type when ? then if ((cs.name=?) , ? , cs.name) else ? end) as character_set_name , (case col.type when ? then if ((cs.name=?) , ? , coll.name) when ? then if ((cs.name=?) , ? , coll.name . . . . . . . .
I learn that there is a good change that happened in MySQL 8 in regards to the data dictionary, metadata is of all database tables is stored in transactional data dictionary tables, it enables us to design an INFORMATION_SCHEMA table as a database VIEW over the data dictionary tables. Is this mechanism sometimes is delaying the process? Why we are seeing performance impact due to system schema table queries? information_schema_stats_expiry value is set to default 86400 value.