Table Compression Utility
The Table Compression utility moog_snapshots_online_table_change_utility is a command line utility. Using Percona table compression, it optimizes the snapshots table in the MoogDb database in order to minimize the size of your historic database.
It also runs online, avoiding downtime, and can compress the size of your historic database by up to 85%.
The utility is compatible with all database types.
Note
This utility requires a number of Perl packages to run. See the section at the bottom of this page for instructions on how to install the packages if the utility reports issues with Perl dependencies.
Usage
The command you use to run the utility depends on your system configuration.
If you are installing or upgrading , run the following command:
moog_snapshots_online_table_change.sh -H hostname -P port -d historic_database_name -u username -p password
If you are not upgrading but you are installing a standalone database server (for example), run the following command:
bash <(curl -s -k https://<username>:<password>@speedy.moogsoft.com/repo/aiops/moog_snapshots_online_table_change.sh) -H hostname -P port -d historic_database_name -u username -p password
Argument | Input | Description |
|---|---|---|
| String | Hostname of the database server. |
| Integer | Port number of the database server. |
| String | Name of the database. |
| String | Database username. |
| String | Database password. |
| - | Display the utility syntax and options. |
Example
$MOOGSOFT_HOME/bin/utils/moog_snapshots_online_table_change.sh -H localhost -P 3306 -d historic_moogdb -u root -p Password123 No slaves found. See --recursion-method if host ldev11 has slaves. Not checking slave lag because no slaves were found and --check-slave-lag was not specified. Operation, tries, wait: analyze_table, 10, 1 copy_rows, 10, 0.25 create_triggers, 10, 1 drop_triggers, 10, 1 swap_tables, 10, 1 update_foreign_keys, 10, 1 Altering `moogdb`.`snapshots`... Creating new table... Created new table moogdb._snapshots_new OK. Altering new table... Altered `moogdb`.`_snapshots_new` OK. 2019-07-12T15:47:49 Creating triggers... 2019-07-12T15:47:49 Created triggers OK. 2019-07-12T15:47:49 Copying approximately 36064233 rows... Copying `moogdb`.`snapshots`: 0% 03:05:15 remain Copying `moogdb`.`snapshots`: 0% 02:56:29 remain Copying `moogdb`.`snapshots`: 0% 02:50:16 remain Copying `moogdb`.`snapshots`: 1% 02:53:31 remain Copying `moogdb`.`snapshots`: 1% 02:54:20 remain Copying `moogdb`.`snapshots`: 1% 02:58:53 remain Copying `moogdb`.`snapshots`: 1% 02:59:46 remain Copying `moogdb`.`snapshots`: 2% 03:03:08 remain ….. ….. Copying `moogdb`.`snapshots`: 94% 09:07 remain Copying `moogdb`.`snapshots`: 94% 08:37 remain Copying `moogdb`.`snapshots`: 94% 08:06 remain Copying `moogdb`.`snapshots`: 95% 07:40 remain Copying `moogdb`.`snapshots`: 95% 07:07 remain Copying `moogdb`.`snapshots`: 95% 06:37 remain Copying `moogdb`.`snapshots`: 96% 06:06 remain Copying `moogdb`.`snapshots`: 96% 05:36 remain Copying `moogdb`.`snapshots`: 96% 05:05 remain Copying `moogdb`.`snapshots`: 97% 04:35 remain Copying `moogdb`.`snapshots`: 97% 04:05 remain Copying `moogdb`.`snapshots`: 97% 03:33 remain Copying `moogdb`.`snapshots`: 98% 03:02 remain Copying `moogdb`.`snapshots`: 98% 02:32 remain Copying `moogdb`.`snapshots`: 98% 02:01 remain Copying `moogdb`.`snapshots`: 99% 01:29 remain Copying `moogdb`.`snapshots`: 99% 00:58 remain Copying `moogdb`.`snapshots`: 99% 00:26 remain 2019-07-12T19:28:57 Copied rows OK. 2019-07-12T19:28:57 Analyzing new table... 2019-07-12T19:28:57 Swapping tables... 2019-07-12T19:28:57 Swapped original and new tables OK. 2019-07-12T19:28:57 Dropping old table... 2019-07-12T19:29:05 Dropped old table `moogdb`.`_snapshots_old` OK. 2019-07-12T19:29:05 Dropping triggers... 2019-07-12T19:29:05 Dropped triggers OK. Successfully altered `moogdb`.`snapshots`.
Install missing utility dependencies
Run the following commands (requires root permissions for the Yum command) to install the Perl packages the utility requires:
REPO_URL=http://mirror.centos.org/centos/7/os/x86_64/Packages;
for PACKAGE in perl-Compress-Raw-Bzip2 perl-Compress-Raw-Zlib perl-DBD-MySQL perl-DBI perl-Data-Dumper perl-Digest perl-Digest-MD5 perl-IO-Compress perl-Net-Daemon perl-PlRPC perl-IO-Socket-IP perl-IO-Socket-SSL perl-Mozilla-CA perl-Net-LibIDN perl-Net-SSLeay perl-TermReadKey
do
curl -L -O ${REPO_URL}/$(curl -L ${REPO_URL} 2>/dev/null | egrep ">${PACKAGE}-[0-9].*" | egrep 'x86_64|noarch' | sed "s/.*\(${PACKAGE}.*.rpm\).*/\1/") 2>/dev/null
done;
yum install *.rpm