Auto DB cleaner

Sometimes, search engines start to "run" around the site quite actively, which causes not only an unnecessary load on the server, but also the growth of the site's database tables. At least on sites with Drupal 8/9 this is a common phenomenon. Today there will be a small attempt to automate the process of clearing cache tables a little. To do this, we will make a small script and add its execution to the CRON of our host.

In Drupal 8/9 versions, you can safely clear 5 cache tables: "cache_dynamic_page_cache" "cache_data" "cache_page" "cache_render" "cache_entity", although you can set only one of them to be cleared. So, let's create a file, for cleaner.sh:

#!/bin/bash
DB_USER="your_database_username"
DB_PASS="your_database_pass"
DB_NAME="your_database_name"

TABLES=("cache_dynamic_page_cache" "cache_data" "cache_page" "cache_render" "cache_entity")

THRESHOLD=$((5 * 1024 * 1024 * 1024))

TOTAL_SIZE=0
for TABLE in "${TABLES[@]}"; do
    TABLE_SIZE=$(mysql -u "$DB_USER" -p"$DB_PASS" -D "$DB_NAME" -sse \
    "SELECT COALESCE(DATA_LENGTH + INDEX_LENGTH, 0) FROM information_schema.TABLES WHERE TABLE_SCHEMA = '$DB_NAME' AND TABLE_NAME = '$TABLE';")
    
    TOTAL_SIZE=$((TOTAL_SIZE + TABLE_SIZE))
done

TOTAL_SIZE_GB=$(echo "scale=2; $TOTAL_SIZE / 1073741824" | bc)

echo "Total size of cache tables: ${TOTAL_SIZE} bytes (~${TOTAL_SIZE_GB} GB)"

if [ "$TOTAL_SIZE" -ge "$THRESHOLD" ]; then
    echo "Total cache size exceeds 5GB (${TOTAL_SIZE_GB} GB). Clearing tables..."
    for TABLE in "${TABLES[@]}"; do
        mysql -u "$DB_USER" -p"$DB_PASS" -D "$DB_NAME" -e "TRUNCATE TABLE $TABLE;"
        echo "Cleared table: $TABLE"
    done
else
    echo "Total cache size is within limits (${TOTAL_SIZE_GB} GB). No action taken."
fi

In this example, we will check whether the sum exceeds the cache size of 5 GB.

Next, if you have a shared host, it is better to write to support and ask for help in setting up the launch of this script by cron (because sometimes you need to set additional permissions to run scripts). You can set the script to run in the morning and in the afternoon.

If you have a VPS host, you probably already know what to do, but just in case, if you are a beginner, you can proceed as follows - move the script itself to the /usr/local/bin folder, then connect to the server via SSH and execute the command

sudo chmod +x /usr/local/bin/cleaner.sh

We do this to indicate to the system that our script is executable. After that, we add the script to the cron to execute. In my case, the server has a control panel BrainyCP where in the cron menu I add the following

Thus, the script will run at 3 am and 6 pm, and an execution log will be added to /var/log/check_cache.log where, if necessary, we can see whether the script is executing correctly.

A small addition - if your password contains special characters such as # or $, you need to change the password escape character from double quotes to single quotes, so instead of DB_PASS="your_database_pass" there will be DB_PASS='your_database_pass'.