Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

show full fields from table is consuming heavy CPU resource

San9940San9940 EntrantCurrent User Role Novice
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.

Comments

  • Rick JamesRick James Contributor Current User Role Novice
    * 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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.