Skip to content

DB indexes

There is a process that watches changes in DB indexes and, if any, sends notification email to OPS about them. Currently applies to LH, A3 and EW.

Process takes all indexes for ASG, MG and ASGA environments and writes them in log files. The following query is used:

SELECT table_name, index_name, column_name, seq_in_index
FROM information_schema.statistics
WHERE table_schema = ?
ORDER BY table_name, index_name, seq_in_index

General logic of the process

  1. Check if diff file already exists. If yes - send notification email as there's unresolved conflict (7). If no - continue processing (2)

  2. Write current DB indexes in a file, name follows pattern of ${app}-db-indexes-new.log

  3. Check if old file is present. If yes - create a diff file comparing two versions. Old file name follows pattern of ${app}-db-indexes-old.log

  4. Check if diff file is present. Diff file name follows pattern of ${app}-indexes.diff

  5. If there is no diff file, new file is renamed to old file rewriting it

  6. If there is a diff file and it is empty, diff file and old index file are removed. New file is renamed to old file

  7. If there is non-empty diff file, email is sent that includes diff file contents

In case any exception happens during the process, it is written in exceptions-${today}.log

Solving the conflict

Main point of this monitoring is to make sure no unintentional changes are made to indexes. As such, make sure that the changes are not a mistake.

After confirmation, diff file and old index file must be removed, new index file should be then renamed to old file name pattern. From that point monitoring will resume as usual.