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

--host, -H

String <host name>

Hostname of the database server.

--port, -P

Integer <port number>

Port number of the database server.

--database, -d

String <database name>

Name of the database.

--user, -u

String <username>

Database username.

--password, -p

String <password>

Database password.

--help, -h

-

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