Moogsoft Docs

Table Compression Utility

The moog_snapshots_online_table_change_utility is a command line utility. Using Percona table compression, the utility optimizes the snapshots table in MoogDb to minimize the size of your historic database. It also runs online, avoiding downtime, and can compress the size of your 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 for instructions on how to install them 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 Moogsoft AIOps, run the following command:

    $MOOGSOFT_HOME/bin/utils/moog_snapshots_online_table_change.sh -H hostname -P port -d historic_database_name -u username -p password
  • If you are not upgrading Moogsoft AIOps and, for example, installing a standalone database server, run the following command instead:

    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

-H

String

Hostname of the database server.

-P

Integer

Port of the database server.

-d

String

Name of the database.

-u

String

Database username.

-p

String

Database password.

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`.
Installing missing utility dependencies

The following commands need to be run (requires root permissions for the yum command) to install the required Perl packages which this utility needs:

curl -L -O http://mirror.as29550.net/mirror.centos.org/7.6.1810/os/x86_64/Packages/perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64.rpm;
curl -L -O http://mirror.as29550.net/mirror.centos.org/7.6.1810/os/x86_64/Packages/perl-Compress-Raw-Zlib-2.061-4.el7.x86_64.rpm;
curl -L -O http://mirror.as29550.net/mirror.centos.org/7.6.1810/os/x86_64/Packages/perl-DBD-MySQL-4.023-6.el7.x86_64.rpm;
curl -L -O http://mirror.as29550.net/mirror.centos.org/7.6.1810/os/x86_64/Packages/perl-DBI-1.627-4.el7.x86_64.rpm;
curl -L -O http://mirror.as29550.net/mirror.centos.org/7.6.1810/os/x86_64/Packages/perl-Data-Dumper-2.145-3.el7.x86_64.rpm;
curl -L -O http://mirror.as29550.net/mirror.centos.org/7.6.1810/os/x86_64/Packages/perl-Digest-1.17-245.el7.noarch.rpm;
curl -L -O http://mirror.as29550.net/mirror.centos.org/7.6.1810/os/x86_64/Packages/perl-Digest-MD5-2.52-3.el7.x86_64.rpm;
curl -L -O http://mirror.as29550.net/mirror.centos.org/7.6.1810/os/x86_64/Packages/perl-IO-Compress-2.061-2.el7.noarch.rpm;
curl -L -O http://mirror.as29550.net/mirror.centos.org/7.6.1810/os/x86_64/Packages/perl-Net-Daemon-0.48-5.el7.noarch.rpm;
curl -L -O http://mirror.as29550.net/mirror.centos.org/7.6.1810/os/x86_64/Packages/perl-PlRPC-0.2020-14.el7.noarch.rpm;
curl -L -O http://centos.mirroring.pulsant.co.uk/7.7.1908/os/x86_64/Packages/perl-IO-Socket-IP-0.21-5.el7.noarch.rpm;
curl -L -O http://centos.mirroring.pulsant.co.uk/7.7.1908/os/x86_64/Packages/perl-IO-Socket-SSL-1.94-7.el7.noarch.rpm;
curl -L -O http://centos.mirroring.pulsant.co.uk/7.7.1908/os/x86_64/Packages/perl-Mozilla-CA-20130114-5.el7.noarch.rpm;
curl -L -O http://centos.mirroring.pulsant.co.uk/7.7.1908/os/x86_64/Packages/perl-Net-LibIDN-0.12-15.el7.x86_64.rpm;
curl -L -O http://centos.mirroring.pulsant.co.uk/7.7.1908/os/x86_64/Packages/perl-Net-SSLeay-1.55-6.el7.x86_64.rpm;
yum install *.rpm