Moogsoft Docs

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:

for PACKAGE in perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64.rpm perl-Compress-Raw-Zlib-2.061-4.el7.x86_64.rpm perl-DBD-MySQL-4.023-6.el7.x86_64.rpm perl-DBI-1.627-4.el7.x86_64.rpm perl-Data-Dumper-2.145-3.el7.x86_64.rpm perl-Digest-1.17-245.el7.noarch.rpm perl-Digest-MD5-2.52-3.el7.x86_64.rpm perl-IO-Compress-2.061-2.el7.noarch.rpm perl-Net-Daemon-0.48-5.el7.noarch.rpm perl-PlRPC-0.2020-14.el7.noarch.rpm perl-IO-Socket-IP-0.21-5.el7.noarch.rpm perl-IO-Socket-SSL-1.94-7.el7.noarch.rpm perl-Mozilla-CA-20130114-5.el7.noarch.rpm perl-Net-LibIDN-0.12-15.el7.x86_64.rpm perl-Net-SSLeay-1.55-6.el7.x86_64.rpm
do
	curl -L -O http://mirror.centos.org/centos/7/os/x86_64/Packages/${PACKAGE};
done;

yum install *.rpm