![]() ![]() You can see from the query results that if a table has significantly more allocated but unused space than used space then that increases the size of the table on disk. WHERE INNODB_TABLESPACES.FILE_SIZE > 10 * 1024 * 1024 JOIN information_schema.INNODB_TABLESPACES ON (INNODB_TABLESPACES.NAME = TABLES.TABLE_SCHEMA || ‘/’ || TABLES.TABLE_NAME) INNODB_TABLESPACES.FILE_SIZE / 1024 / 1024 AS “MB on disk” TABLES.DATA_FREE / 1024 / 1024 AS “MB allocated but unused (estimate)”, (TABLES.DATA_LENGTH + TABLES.INDEX_LENGTH) / 1024 / 1024 AS “MB used (estimate)”, ![]() Since every other table exists in its own .idb file, the allocated but unused space is unavailable for them to grow into.įirst run the following query to identify tables with significant allocated but unused space. ![]() InnoDB does not release this allocated space back to the operating system automatically (in case the table grows larger in the future) but this can cause problems. A classic example of this would be a table containing jobs for a work queue in which rows are repeatedly added to the end of the table and removed from the beginning. Over time, when a table receives a lot of inserts and deletes, the amount of space it occupies on disk can grow significantly larger than the current data in the table. MySQL managed database is configured to use inodb_file_per_table=ON which means that for every table its data and indexes are stored together in a separate .idb file. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |