show full fields from table is consuming heavy CPU resource

We are using MySQL 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 as table_catalog , as table_schema , as table_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 , , ?) as character_maximum_length , internal_dd_char_length (col.type , col.char_length , , ?) 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 (( , ? , else ? end) as character_set_name , (case col.type when ? then if (( , ? , when ? then if (( , ? , . . . . . . . .

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.

  • I think you described the cause quite well (Data Dictionary of 8.0).
  • Sounds like Percona should step up to the plate and find a solution.
  • 3rd party software needs to clean up its act – some packages do an awful lot of SHOWs in the normal course of things. It’s as if they can’t remember the code they generated.