MySQL information_schema comes with useful information about the database instance, status, … etc. which is needed for daily DBA work.
There are some simple queries on the information_schema that I use on my daily basis in which I’m writing this post for my reference and maybe a good reference for someone else too …
Finding tables without Primary or Unique Keys:
PKs are so important, especially, for InnoDB tables as MySQL uses PKs as a clustered index and having no PKs might lead to severe performance problems.
Also having no PKs is one of the main causes of slave lagging problems mainly when using RBR (Row-Based Replication), e.g. if a delete statement on the master will delete 1 million rows on a table without PK, a full table scan will take place. This “might” not be a problem on the master but on the slave 1 million full table scan will take place – because changes to the individual rows are being written to the binary logs in ROW format not the actual statement itself – which of course will cause the slave to be lagged. For more information about the Replication formats, check the manual documentation.
In Galera Cluster setups, tables without PKs might cause replication lag on the slave nodes and some other troubles as well:
“When tables lack a primary key, rows can appear in different order on different nodes in your cluster. As such, queries like SELECT…LIMIT… can return different results. Additionally, on such tables the DELETE statement is unsupported.“.
More information about Galera Cluster limitations, can be checked out here.
So, it is important to find out if there are any tables without PKs or not to fix the problem ASAP:
SELECT t.TABLE_SCHEMA,t.TABLE_NAME,ENGINE
FROM information_schema.TABLES t
INNER JOIN information_schema.COLUMNS c
ON t.TABLE_SCHEMA=c.TABLE_SCHEMA
AND t.TABLE_NAME=c.TABLE_NAME
AND t.TABLE_SCHEMA NOT IN (‘performance_schema’,’information_schema’,’mysql’)
GROUP BY t.TABLE_SCHEMA,t.TABLE_NAME
HAVING sum(if(column_key in (‘PRI’,’UNI’), 1,0))=0;
Finding Foreign key constraints:
If you are using the Percona tool pt-online-schema-change to apply schema changes, having FKs in your tables will make the tool’s operation more complicate and additional options should be used:
“Foreign keys complicate the tool’s operation and introduce additional risk. The technique of atomically renaming the original and new tables does not work when foreign keys refer to the table. The tool must update foreign keys to refer to the new table after the schema change is complete. The tool supports two methods for accomplishing this. You can read more about this in the documentation for –alter-foreign-keys-method.
Foreign keys also cause some side effects. The final table will have the same foreign keys and indexes as the original table (unless you specify differently in your ALTER statement), but the names of the objects may be changed slightly to avoid object name collisions in MySQL and InnoDB.“.
Check out my blog for more information on how to use pt-online-schema-change!
Also, foreign keys are supported only in InnoDB so if it is required to convert your tables to MyISAM, you should check first those constraints and remove them before converting to MyISAM, otherwise, the ALTER statement will fail:
SELECT referenced_table_name parent, table_name child, constraint_name
FROM information_schema.KEY_COLUMN_USAGE
WHERE referenced_table_name IS NOT NULL
ORDER BY referenced_table_name;
Finding Fragmentation:
By the time, tables become fragmented due to many writes (inserts, updates and deletes) so, reorganizing the table and the index will improve the performance and also reclaiming the disk space for use by the operating system might be required (Assuming that innodb_file_per_table option was enabled before creating the InnoDB tables). This can be achieved by executing “OPTIMIZE TABLE” statement but it is expensive. However, we can check the tables’ fragmentation first and then execute “OPTIMIZE TABLE” only on those tables having high fragmentation (the following query will return only the tables in db_name schema having data free more than 100MB):
SELECT TABLE_NAME, (DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 AS sizeMb,DATA_FREE / 1024 / 1024 AS data_free_MB
FROM information_schema.tables
WHERE engine LIKE ‘InnoDB’
and TABLE_SCHEMA = ‘db_name’
AND DATA_FREE > 100 * 1024 * 1024;
Checking if there are any MyISAM tables or not:
MyISAM is a non transactional SE and having a consistent backup where there are MyISAM tables requires locking all tables. So, before considering the backup plan for a system, it is recommended to know if there are any MyISAM tables or not:
SELECT TABLE_SCHEMA, TABLE_NAME
FROM `information_schema`.`TABLES`
WHERE TABLE_SCHEMA NOT IN (‘information_schema’,’performance_schema’,’mysql’)
AND ENGINE=’MyISAM’;
If you have your own useful queries on the information_schema, please feel free to write it down in a comment 🙂
via Planet MySQL
Useful queries on MySQL information_schema