Created
October 29, 2023 16:28
-
-
Save andrewrcollins/cfb1cf36026a8ebafe5c4af3c9dc48f2 to your computer and use it in GitHub Desktop.
Perform "warm up" on MySQL database tables.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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