openstack_db_archive.sh

#!/bin/bash
#
# purge tables of "deleted" records by archiveing them in sensible chunks to the shadow tables
# this work was started in PAASINFRA-206
#

# default to archiving all records flagged as deleted,
# use the -n option to enable dry run mode
unset DRY_RUN

# tables to arhive deleted records from
DATABASE=nova
TABLES="security_group_rules security_group_instance_association security_groups instance_info_caches instance_system_metadata instances reservations compute_node_stats "

## process the command line arguments
while getopts "hn" opt; do
  case $opt in
    h)
      echo "openstack_db_archive.sh - archive records flagged as deleted into the shadow tables."
      echo "Records are archived from the following tables:"
      echo
      for TABLE in ${TABLES}
      do
        echo "    ${DATABASE}.${TABLE}"
      done
      echo
      echo "Options:"
      echo " -n dry run mode - pass --dry-run to pt-archiver"
      exit 0
      ;;
    n)
      DRY_RUN="--dry-run"
      ;;
    \?)
      echo "Invalid option: -$OPTARG" >&2
      exit 1
      ;;
    :)
      echo "Option -$OPTARG requires an argument." >&2
      exit 1
      ;;
  esac
done

echo
echo `date` "OpenStack Database Archiver starting.."
echo

echo `date` "Purging nova.instance_system_metadata of deleted instance data"
# this is back to front (on delete if you can find a record in instances flagged for deletion)
# --where 'EXISTS(SELECT * FROM instances WHERE deleted!=0 AND uuid=instance_system_metadata.instance_uuid)'
# to delete where there is no active record:
# --where 'NOT EXISTS(SELECT * FROM instances WHERE deleted=0 AND uuid=instance_system_metadata.instance_uuid)'

TABLE=instance_system_metadata
SHADOW_TABLE="shadow_${TABLE}"
pt-archiver ${DRY_RUN} --statistics --sleep-coef 0.75 --progress 100 --commit-each --limit 10 \
  --source h=localhost,D=nova,t=${TABLE} --no-check-charset  \
  --dest h=localhost,D=${DATABASE},t=${SHADOW_TABLE} \
  --where 'EXISTS(SELECT * FROM instances WHERE deleted!=0 AND uuid=instance_system_metadata.instance_uuid)'


for TABLE in ${TABLES}
do
        SHADOW_TABLE="shadow_${TABLE}"

        ACTIVE_RECORDS=`mysql -B -e "select count(id) from ${DATABASE}.${TABLE} where deleted=0" | tail -1`
        DELETED_RECORDS=`mysql -B -e "select count(id) from ${DATABASE}.${TABLE} where deleted!=0" | tail -1`

        LOCAL_ABORTS=`mysql -B -e "SHOW STATUS LIKE 'wsrep_%'" | grep -e wsrep_local_bf_aborts -e wsrep_local_cert_failures`

	echo
	echo
        echo `date` "Archiving ${DELETED_RECORDS} records to ${SHADOW_TABLE} from ${TABLE}, leaving ${ACTIVE_RECORDS}"
        echo `date` "LOCAL_ABORTS before"
	echo ${LOCAL_ABORTS}


        pt-archiver ${DRY_RUN} --statistics --progress 100 --commit-each --limit 10 \
          --source h=localhost,D=${DATABASE},t=${TABLE} \
          --dest h=localhost,D=${DATABASE},t=${SHADOW_TABLE} \
            --ignore --no-check-charset --sleep-coef 0.75 \
            --where "deleted!=0"

        echo `date` "Finished archiving ${DELETED_RECORDS} to ${SHADOW_TABLE} from ${TABLE}"
        echo `date` "LOCAL_ABORTS before"
	echo ${LOCAL_ABORTS}
        LOCAL_ABORTS=`mysql -B -e "SHOW STATUS LIKE 'wsrep_%'" | grep -e wsrep_local_bf_aborts -e wsrep_local_cert_failures`
        echo `date` "LOCAL_ABORTS after"
	echo ${LOCAL_ABORTS}
        echo
done

echo
echo `date` "OpenStack Database Archiver finished."
echo