In MySQL Enterprise Monitor 2.1, the data received by the MySQL Enterprise Service Manager from MySQL Enterprise Agent instances is inserted into tables, with the information within the tables purged periodically to remove the old data, according to the defined purging interval.
In installations with a large volume of data, the purging of old information can take a considerable amount of time, slowing down the MySQL Enterprise Service Manager and using up valuable disk space.
In MySQL Enterprise Monitor 2.2, the tables used for storing this information are changed, and the older tables are used only when viewing historical information. The information within the table is not purged until all of the table data is older than the specified purge period, when the old tables are dropped.
The following instructions are designed to reduce the disk space usage for customers that are either using MySQL Enterprise Monitor 2.1 currently, or that have already upgraded an existing MySQL Enterprise Monitor 2.1 installation to MySQL Enterprise Monitor 2.2. If you have performed a new (not upgrade) installation of MySQL Enterprise Monitor 2.2, these steps are not required.
To reduce the space used by the tables in both 2.1 and 2.2 until the data is finally purged, follow these steps:
If you are running MySQL Enterprise Monitor 2.1, upgrade to MySQL Enterprise Monitor 2.2 using an upgrade installer. This migrates the existing table data and prepare you for the next stage.
WarningPerforming a upgrade installation from MySQL Enterprise Monitor 2.1 to MySQL Enterprise Monitor 2.2 requires approximately 150% of your current disk space requirements while the information is migrated. Once the migration is complete, your disk space usage returns to a value slightly higher than within your MySQL Enterprise Monitor 2.1 installation.
Start up your MySQL Enterprise Service Manager and MySQL Enterprise Agent instances.
Connect to the MySQL server being used by your MySQL Enterprise Service Manager. You can find a sample mysql command for connecting to your server within the
configuration_report.txtfile within your MySQL Enterprise Service Manager installation.Once connected to MySQL, change to the
memdatabase:mysql> use mem;Now execute the following
ALTER TABLEstatements to change the original InnoDB tables to use MyISAM:ALTER TABLE dc_ng_double_age0 ENGINE=MYISAM; ALTER TABLE dc_ng_double_age1 ENGINE=MYISAM; ALTER TABLE dc_ng_double_age2 ENGINE=MYISAM; ALTER TABLE dc_ng_double_now ENGINE=MYISAM; ALTER TABLE dc_ng_long_age0 ENGINE=MYISAM; ALTER TABLE dc_ng_long_age1 ENGINE=MYISAM; ALTER TABLE dc_ng_long_age2 ENGINE=MYISAM; ALTER TABLE dc_ng_long_now ENGINE=MYISAM; ALTER TABLE dc_ng_string_now ENGINE=MYISAM;
NotePlease keep in mind the following points when running these statements
Performing this operation should not affect the operation of your MySQL Enterprise Service Manager, since new data is written into different tables.
The time required to perform the conversation depends on the size of your original tables. It may take a considerable amount of time if you have a large volume of old data.
You need slightly more than twice the amount of diskspace currently being used by the tables until the conversion completes.
Once the conversion has completed, you can continue to use your MySQL Enterprise Service Manager without restarting or any further administration. The existing old table data is removed as soon as the data in the old table is older than your selected purging interval. The new MyISAM tables should take up approximately 60% of the space of the InnoDB tables.