Releasing LOB space in MySQL or Maria databases

You can release the large object (LOB) storage in the MySQL or Maria databases.

Pre-requisite:

Consider the following points when you plan an upgrade:
  • You might be able to avoid the downtime by running the alter table statement before you upgrade:
    alter table vc_persistent_record modify persistent_data longtext null, add compessed_data longblob null, lock=none
    If this command fails, an online change of the table before upgrade isn't possible with your version of MySQL. Downtime is required during the upgrade.
  • Based on the MySQL version you are using, the upgrade or pre-upgrade table change temporarily uses additional space to rebuild the table.
  • For bext results, run the statement on a table clone to estimate the upgrade time.
  • Use the alter table command only if the table does not include a compressed_data column.

Procedure:

Release LOB storage for MySQL and Maria databases using the following steps:

  1. Set the versioned_config.upgrade.enabled=true property in the conf/server/installed.properties file.
  2. Restart the IBM® UrbanCode® Deploy server.
  3. Check on the progress of the conversion: 
    select count(*) from vc_persistent_record where persistent_data is not null

    The compression process is complete when the row count reported by the query is 0.

  4. Check the database size: 
    select table_schema, sum((data_length+index_length)/1024/1024) AS MB from information_schema.tables group by 1
  5. Compress the database table.
    • Use the optimize statement for MySQL v5.6 and later, and MariaDB v10.0 and later:
      optimize table vc_persistent_record

      See the MySQL documentation about using the optimizing statement: https://dev.mysql.com/doc/refman/5.7/en/optimize-table.html.

    • For all other versions of MySQL, complete these steps:
      1. Backup your database:
        mysqldump -h hostname -u username -p password --databases dbname > ~/mysql-backup.sql
      2. Create a new database and restore the old database:
        mysql -h hostname -u username -p password < mysql-backup.sql
      Note: The backup and restore must be done when the system is offline to prevent loss of new data while restoring.

Post-requisite:

  1. Verify that the space is reduced:
    select table_schema, sum((data_length+index_length)/1024/1024) AS MB from information_schema.tables group by 1
  2. Set the versioned_config.upgrade.enabled parameter back to false in the conf/server/installed.properties file.