Check database size

From Zarafa wiki

Jump to: navigation, search

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"
Personal tools