Skip to content

Instantly share code, notes, and snippets.

@andrewrcollins
Created October 29, 2023 16:28
Show Gist options
  • Save andrewrcollins/cfb1cf36026a8ebafe5c4af3c9dc48f2 to your computer and use it in GitHub Desktop.
Save andrewrcollins/cfb1cf36026a8ebafe5c4af3c9dc48f2 to your computer and use it in GitHub Desktop.
Perform "warm up" on MySQL database tables.
#!/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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment