Query Cache Hit Ratio

query_cache_stats.sh

#!/usr/bin/env bash

# Current size compared with maximum available size:
# ((query_cache_size-Qcache_free_memory)/query_cache_size)*100
# Hit Ratio Among all queries
# Qcache_hits / (QCache_hits + Com_select)

export DB_NAME=$(grep [\']db[\'] -A 20 app/etc/env.php | grep dbname | head -n1 | sed "s/.*[=][>][ ]*[']//" | sed "s/['][,]//");
export MYSQL_HOST=$(grep [\']db[\'] -A 20 app/etc/env.php | grep host | head -n1 | sed "s/.*[=][>][ ]*[']//" | sed "s/['][,]//");
export DB_USER=$(grep [\']db[\'] -A 20 app/etc/env.php | grep username | head -n1 | sed "s/.*[=][>][ ]*[']//" | sed "s/['][,]//");
export MYSQL_PWD=$(grep [\']db[\'] -A 20 app/etc/env.php | grep password | head -n1 | sed "s/.*[=][>][ ]*[']//" | sed "s/[']$//" | sed "s/['][,]//");
Qcache_hits=$(mysql -h $MYSQL_HOST -u $DB_USER --password=$MYSQL_PWD $DB_NAME -e "SHOW STATUS LIKE 'Qcache_hits';" | tail -n1 | awk '{print $2}')
Qcache_inserts=$(mysql -h $MYSQL_HOST -u $DB_USER --password=$MYSQL_PWD $DB_NAME -e "SHOW STATUS LIKE 'Qcache_inserts';" | tail -n1 | awk '{print $2}')
Com_select=$(mysql -h $MYSQL_HOST -u $DB_USER --password=$MYSQL_PWD $DB_NAME -e "SHOW GLOBAL STATUS LIKE 'Com_select';" | tail -n1 | awk '{print $2}')
query_cache_all=$(mysql -h $MYSQL_HOST -u $DB_USER --password=$MYSQL_PWD $DB_NAME -e "SELECT $Qcache_hits / ($Qcache_hits + $Com_select)" | tail -n1)
query_cache_cacheable=$(mysql -h $MYSQL_HOST -u $DB_USER --password=$MYSQL_PWD $DB_NAME -e "SELECT $Qcache_hits / ($Qcache_hits + $Qcache_inserts)" | tail -n1)

query_cache_size=$(mysql -h $MYSQL_HOST -u $DB_USER --password=$MYSQL_PWD $DB_NAME -e "SHOW GLOBAL VARIABLES LIKE 'query_cache_size';" | tail -n1 | awk '{print $2}')
Qcache_free_memory=$(mysql -h $MYSQL_HOST -u $DB_USER --password=$MYSQL_PWD $DB_NAME -e "SHOW STATUS LIKE 'Qcache_free_memory';" | tail -n1 | awk '{print $2}')
cache_size_ratio=$(mysql -h $MYSQL_HOST -u $DB_USER --password=$MYSQL_PWD $DB_NAME -e "SELECT ($query_cache_size-$Qcache_free_memory)/$query_cache_size" | tail -n1)

echo "Current Query Cache Size: $query_cache_size bytes; Current size vs available ratio: $cache_size_ratio"
echo "Cache Query Hit Ratio among all queries: $query_cache_all"
echo "Cache Query Hit Ratio among cacheable queries: $query_cache_cacheable"
#