Check database size

From Zarafa wiki

The following script allows you to check the database size for all tables within the zarafa database.

And also displays the size of the database itself.

Usage: ./mysql-size.sh -u username -p password -h hostname -d database

For example:

# ./mysql-size.sh -u username -p password -h localhost -d zarafa
+-------------------+------+-------------+--------------+------------+
| Table             | Rows | Data Length | Index Length | Size in MB |
+-------------------+------+-------------+--------------+------------+
| abchanges         |   14 |       16384 |        32768 |       0.05 |
| acl               |    0 |       16384 |            0 |       0.02 |
| changes           |  753 |      131072 |       196608 |       0.31 |
| hierarchy         |  366 |       16384 |        16384 |       0.03 |
| indexedproperties |  736 |       65536 |        49152 |       0.11 |
| lob               |    0 |       16384 |            0 |       0.02 |
| mvproperties      |  262 |       16384 |            0 |       0.02 |
| names             |   98 |       16384 |        65536 |       0.08 |
| object            |    1 |       16384 |        32768 |       0.05 |
| objectmvproperty  |    0 |       16384 |            0 |       0.02 |
| objectproperty    |    6 |       16384 |            0 |       0.02 |
| objectrelation    |    0 |       16384 |            0 |       0.02 |
| outgoingqueue     |    0 |       16384 |            0 |       0.02 |
| properties        | 4765 |     2555904 |       196608 |       2.63 |
| receivefolder     |   16 |       16384 |        16384 |       0.03 |
| searchresults     |    0 |       16384 |            0 |       0.02 |
| settings          |    4 |       16384 |            0 |       0.02 |
| singleinstances   |    0 |       16384 |        16384 |       0.03 |
| stores            |    6 |       16384 |        16384 |       0.03 |
| syncedmessages    |   16 |       16384 |        16384 |       0.03 |
| syncs             |   83 |       16384 |        32768 |       0.05 |
| usergroup_acl     |    2 |       16384 |            0 |       0.02 |
| users             |    4 |       16384 |        16384 |       0.03 |
| versions          |    8 |       16384 |            0 |       0.02 |
+-------------------+------+-------------+--------------+------------+

+----------+------------+
| Database | Size in MB |
+----------+------------+
| zarafa   | 3.62500000 |
+----------+------------+

Script

Copy and paste this script into a new file on your server.

For example: mysql-size.sh.

#!/bin/bash

USAGE="Usage: ./mysql-size.sh -u username -p password -h hostname -d database"

if [ "$#" == "0" ]; then
        echo "$USAGE"
        exit 1
fi

while [ ! -z $1 ]; do
  case $1 in
        "-h")
        HOST=$2
        ;;
        "-u")
        USER=$2
        ;;
        "-p")
        PASS=$2
        ;;
        "-d")
        DB=$2
        ;;
  esac
  shift
  shift
done

CMD="SELECT table_name 'Table', table_rows 'Rows', data_length 'Data Length', index_length 'Index Length', round(((data_length + index_length) / 1024 / 1024),2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema = '$DB';"
CMD2="SELECT table_schema 'Database', sum( data_length + index_length) / 1024 / 1024 'Size in MB' FROM information_schema.TABLES WHERE table_schema = '$DB';"

mysql -u$USER -h$HOST -p"$PASS" $DB -e"$CMD"
echo
mysql -u$USER -h$HOST -p"$PASS" $DB -e"$CMD2"