MySQL Forums :: Newbie :: Is my data fragmented?


Advanced Search

Is my data fragmented?
Posted by: Hamant Patel ()
Date: November 16, 2009 09:00AM

Hi,

I'm having trouble determining if I have fragmented InnoDB tables. A Google search tells me that starting with MySQL 5.1.28 I should be looking at the DATA_FREE field in INFORMATION_SCHEMA, but pre-5.1.28 the fragmentation is shown in the TABLE_COMMENT field. I'm currently running MySQL 5.0.45;

mysql Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu (x86_64) using readline 5.0

And when I perform a select from INFORMATION_SCHEMA, I see the following;

mysql> SELECT TABLE_NAME, ENGINE, DATA_FREE, TABLE_COMMENT FROM TABLES WHERE TABLE_SCHEMA = 'ngmsc_site';
+-------------------------+--------+-----------+----------------------------------------------------------------------------------+
| TABLE_NAME              | ENGINE | DATA_FREE | TABLE_COMMENT                                                                    |
+-------------------------+--------+-----------+----------------------------------------------------------------------------------+
| DelRepTimeOutStore      | InnoDB |         0 | InnoDB free: 0 kB                                                                |
| JBPM_ACTION             | InnoDB |         0 | InnoDB free: 4096 kB; (`ACTIONDELEGATION_`) REFER `ngmsc_site/JBPM_DELEGATION`(` |
| JBPM_BYTEARRAY          | InnoDB |         0 | InnoDB free: 0 kB; (`FILEDEFINITION_`) REFER `ngmsc_site/JBPM_MODULEDEFINITION`( |
| JBPM_BYTEBLOCK          | InnoDB |         0 | InnoDB free: 7168 kB; (`PROCESSFILE_`) REFER `ngmsc_site/JBPM_BYTEARRAY`(`ID_`)  |
| JBPM_COMMENT            | InnoDB |         0 | InnoDB free: 0 kB; (`TOKEN_`) REFER `ngmsc_site/JBPM_TOKEN`(`ID_`); (`TASKINSTAN |
| JBPM_DECISIONCONDITIONS | InnoDB |         0 | InnoDB free: 0 kB; (`DECISION_`) REFER `ngmsc_site/JBPM_NODE`(`ID_`)             |
| JBPM_DELEGATION         | InnoDB |         0 | InnoDB free: 6144 kB; (`PROCESSDEFINITION_`) REFER `ngmsc_site/JBPM_PROCESSDEFIN |
| JBPM_EVENT              | InnoDB |         0 | InnoDB free: 0 kB; (`NODE_`) REFER `ngmsc_site/JBPM_NODE`(`ID_`); (`PROCESSDEFIN |
| JBPM_EXCEPTIONHANDLER   | InnoDB |         0 | InnoDB free: 0 kB                                                                |
| JBPM_ID_GROUP           | InnoDB |         0 | InnoDB free: 0 kB; (`PARENT_`) REFER `ngmsc_site/JBPM_ID_GROUP`(`ID_`)           |
| JBPM_ID_MEMBERSHIP      | InnoDB |         0 | InnoDB free: 0 kB; (`GROUP_`) REFER `ngmsc_site/JBPM_ID_GROUP`(`ID_`); (`USER_`) |
| JBPM_ID_PERMISSIONS     | InnoDB |         0 | InnoDB free: 0 kB                                                                |
| JBPM_ID_USER            | InnoDB |         0 | InnoDB free: 0 kB                                                                |
| JBPM_JOB                | InnoDB |         0 | InnoDB free: 0 kB; (`ACTION_`) REFER `ngmsc_site/JBPM_ACTION`(`ID_`); (`NODE_`)  |
| JBPM_LOG                | InnoDB |         0 | InnoDB free: 0 kB; (`ACTION_`) REFER `ngmsc_site/JBPM_ACTION`(`ID_`); (`CHILD_`) |
| JBPM_MODULEDEFINITION   | InnoDB |         0 | InnoDB free: 0 kB; (`PROCESSDEFINITION_`) REFER `ngmsc_site/JBPM_PROCESSDEFINITI |
| JBPM_MODULEINSTANCE     | InnoDB |         0 | InnoDB free: 0 kB; (`PROCESSINSTANCE_`) REFER `ngmsc_site/JBPM_PROCESSINSTANCE`( |
| JBPM_NODE               | InnoDB |         0 | InnoDB free: 7168 kB; (`DECISIONDELEGATION`) REFER `ngmsc_site/JBPM_DELEGATION`( |
| JBPM_POOLEDACTOR        | InnoDB |         0 | InnoDB free: 0 kB; (`SWIMLANEINSTANCE_`) REFER `ngmsc_site/JBPM_SWIMLANEINSTANCE |
| JBPM_PROCESSDEFINITION  | InnoDB |         0 | InnoDB free: 0 kB; (`STARTSTATE_`) REFER `ngmsc_site/JBPM_NODE`(`ID_`)           |
| JBPM_PROCESSINSTANCE    | InnoDB |         0 | InnoDB free: 0 kB; (`PROCESSDEFINITION_`) REFER `ngmsc_site/JBPM_PROCESSDEFINITI |
| JBPM_RUNTIMEACTION      | InnoDB |         0 | InnoDB free: 0 kB; (`ACTION_`) REFER `ngmsc_site/JBPM_ACTION`(`ID_`); (`PROCESSI |
| JBPM_SWIMLANE           | InnoDB |         0 | InnoDB free: 0 kB; (`ASSIGNMENTDELEGATION_`) REFER `ngmsc_site/JBPM_DELEGATION`( |
| JBPM_SWIMLANEINSTANCE   | InnoDB |         0 | InnoDB free: 0 kB; (`SWIMLANE_`) REFER `ngmsc_site/JBPM_SWIMLANE`(`ID_`); (`TASK |
| JBPM_TASK               | InnoDB |         0 | InnoDB free: 0 kB; (`ASSIGNMENTDELEGATION_`) REFER `ngmsc_site/JBPM_DELEGATION`( |
| JBPM_TASKACTORPOOL      | InnoDB |         0 | InnoDB free: 0 kB; (`TASKINSTANCE_`) REFER `ngmsc_site/JBPM_TASKINSTANCE`(`ID_`) |
| JBPM_TASKCONTROLLER     | InnoDB |         0 | InnoDB free: 0 kB; (`TASKCONTROLLERDELEGATION_`) REFER `ngmsc_site/JBPM_DELEGATI |
| JBPM_TASKINSTANCE       | InnoDB |         0 | InnoDB free: 0 kB; (`SWIMLANINSTANCE_`) REFER `ngmsc_site/JBPM_SWIMLANEINSTANCE` |
| JBPM_TOKEN              | InnoDB |         0 | InnoDB free: 0 kB; (`NODE_`) REFER `ngmsc_site/JBPM_NODE`(`ID_`); (`PARENT_`) RE |
| JBPM_TOKENVARIABLEMAP   | InnoDB |         0 | InnoDB free: 0 kB; (`CONTEXTINSTANCE_`) REFER `ngmsc_site/JBPM_MODULEINSTANCE`(` |
| JBPM_TRANSITION         | InnoDB |         0 | InnoDB free: 7168 kB; (`FROM_`) REFER `ngmsc_site/JBPM_NODE`(`ID_`); (`TO_`) REF |
| JBPM_VARIABLEACCESS     | InnoDB |         0 | InnoDB free: 0 kB; (`PROCESSSTATE_`) REFER `ngmsc_site/JBPM_NODE`(`ID_`); (`SCRI |
| JBPM_VARIABLEINSTANCE   | InnoDB |         0 | InnoDB free: 4096 kB; (`BYTEARRAYVALUE_`) REFER `ngmsc_site/JBPM_BYTEARRAY`(`ID_ |
| MULTIPART_MSG_STORE     | InnoDB |         0 | InnoDB free: 0 kB                                                                |
| RemoteTagMapping        | InnoDB |         0 | InnoDB free: 0 kB                                                                |
| Statistics              | InnoDB |         0 | InnoDB free: 4096 kB                                                             |
| TTS_CONNECTION_DATA     | InnoDB |         0 | InnoDB free: 0 kB                                                                |
| ack_msg_store           | InnoDB |         0 | InnoDB free: 0 kB                                                                |
| config_workflow         | InnoDB |         0 | InnoDB free: 0 kB                                                                |
| fera_JBPM_NODE          | InnoDB |         0 | InnoDB free: 4096 kB                                                             |
| message                 | InnoDB |         0 | InnoDB free: 0 kB                                                                |
| offline_sms_store       | InnoDB |         0 | InnoDB free: 0 kB                                                                |
| offline_sms_store_bak   | MyISAM |         0 |                                                                                  |
| offline_store           | InnoDB |         0 | InnoDB free: 0 kB                                                                |
| process_instance        | InnoDB |        64 | InnoDB free: 0 kB                                                                |
| prov_request            | InnoDB |         0 | InnoDB free: 0 kB                                                                |
+-------------------------+--------+-----------+----------------------------------------------------------------------------------+
46 rows in set (0.38 sec)
Can anyone tell me what the DATA_FREE value of '64' against the 'process_instance' table means? And also, what does 'InnoDB free: 4096 kB' against several of the tables mean?

Thanks in advance.

Hamant

Options: ReplyQuote


Subject Written By Posted
Is my data fragmented? Hamant Patel 11/16/2009 09:00AM
Re: Is my data fragmented? Rick James 11/18/2009 11:54PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.