Perform "warm up" on MySQL database tables.

warm-up.sh

#!/bin/sh

#
# warm-up.sh
#
# Perform "warm up" on database tables.
#

#####

# display help message
help() {
  cat <<help

Usage:
  warm-up.sh [options] [table ...]

Options:
  -a  Perform "warm up" on all tables
  -q  Quiet
  -h  Display this help message

Arguments:
  table  A table to process

help

  # failure
  exit 1
}

# default options
all_tables="no"
quiet="no"

# parse options
while getopts "aqh" var
do
  # process option
  case ${var} in
    a) all_tables="yes" ;;
    q) quiet="yes" ;;
    h) help ;;
    # error
    ?) help ;;
  esac
done

# shift positional parameters
shift $(( OPTIND - 1 ))

##### start script name

script=warm-up.sh

##### end script name

##### start script action

. /vagrant/mysql-pool.sh

#
# "warm up" table
#
# 8.5.9 Optimizing InnoDB for Systems with Many Tables
# https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-many-tables.html
#
# InnoDB computes index cardinality values for a table the first time
# that table is accessed after startup, instead of storing such values
# in the table. This step can take significant time on systems that
# partition the data into many tables. Since this overhead only applies
# to the initial table open operation, to warm up a table for later use,
# access it immediately after startup by issuing a statement such as
# SELECT 1 FROM tbl_name LIMIT 1.
#
warm_up_table() {
  add_query "SELECT 1 FROM \`${1}\` LIMIT 1;" 1 1 "warm up: ${1}"
}

#
# rebuild table
#
# 13.1.7 ALTER TABLE Syntax - Table Options
# https://dev.mysql.com/doc/refman/5.5/en/alter-table.html#alter-table-options
#
# When you specify an ENGINE clause, ALTER TABLE rebuilds the table.
# This is true even if the table already has the specified storage
# engine.
#
rebuild_table() {
  add_query "ALTER TABLE \`${1}\` ENGINE=InnoDB;" 1 1 "rebuild: ${1}"
}

#
# analyze table
#
analyze_table() {
  add_query "ANALYZE TABLE \`${1}\`;" 1 1 "analyze: ${1}"
}

#
# analyze partitions table
#
# 13.1.7.1 ALTER TABLE Partition Operations
# https://dev.mysql.com/doc/refman/5.5/en/alter-table-partition-operations.html
#
# Some MySQL storage engines, such as InnoDB, do not support per-partition
# optimization. For a partitioned table using such a storage engine, ALTER
# TABLE ... OPTIMIZE PARTITION rebuilds the entire table. ...
#
# To work around this problem, use the statements ALTER TABLE ... REBUILD
# PARTITION and ALTER TABLE ... ANALYZE PARTITION instead.
#
analyze_partitions_table() {
  add_query "ALTER TABLE \`${1}\` ANALYZE PARTITION ALL;" 1 1 "analyze partitions: ${1}"
}

#
# optimize table
#
# 13.7.2.4 OPTIMIZE TABLE Syntax
# https://dev.mysql.com/doc/refman/5.5/en/optimize-table.html
#
# OPTIMIZE TABLE reorganizes the physical storage of table data
# and associated index data, to reduce storage space and improve
# I/O efficiency when accessing the table.
#
optimize_table() {
  add_query "OPTIMIZE TABLE \`${1}\`;" 1 1 "optimize: ${1}"
}

if [ "${all_tables}" = "yes" ]
then
  tables_sql_file=${tmp}/tables.sql
  tables_file=${tmp}/tables.txt

  cat > ${tables_sql_file} <<tables_sql_file
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.tables WHERE (TABLE_SCHEMA = '${db_database}') ORDER BY TABLE_NAME;
tables_sql_file

  cat ${tables_sql_file} |
  mysql -u ${db_username} -p${db_password} ${db_database} 2>> /dev/null |
  grep -v TABLE_NAME > ${tables_file}

  set -- $( cat ${tables_file} )
fi

#if [ "${all_tables}" = "yes" ]
#then
#  tables_sql_file=${tmp}/tables.sql
#  tables_file=${tmp}/tables.txt
#
#  cat > ${tables_sql_file} <<tables_sql_file
#SELECT TABLE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE (TABLE_SCHEMA = '${db_database}') AND (PARTITION_NAME IS NOT NULL) GROUP BY TABLE_NAME ORDER BY TABLE_NAME;
#tables_sql_file
#
#  cat ${tables_sql_file} |
#  mysql -u ${db_username} -p${db_password} ${db_database} 2>> /dev/null |
#  grep -v TABLE_NAME > ${tables_file}
#
#  set -- $( cat ${tables_file} )
#fi

for table
do
  warm_up_table ${table}

  #rebuild_table ${table}

  #analyze_table ${table}

  #optimize_table ${table}
done

execute_queries

##### end script action