Page 1 of 1
Databases size not updated.
Posted: Sun Jan 01, 2017 8:55 pm
by Trentor
Hello,
I've VestaCP installed on a CentOS 7 server.
I realized that, since the last Vesta update, the size of databases is always zero, Vesta seems not update the sizes.
Anyone else with this problem?
Thanks.
Re: Databases size not updated.
Posted: Tue Mar 07, 2017 10:29 am
by skurudo
Try to "Update counters"
Re: Databases size not updated.
Posted: Tue Mar 07, 2017 9:18 pm
by Trentor
skurudo wrote:Try to "Update counters"
Hi skurudo,
Yes, I've tried that in the past, with no success.
The problem remains and I've also the problem in other two servers.
Re: Databases size not updated.
Posted: Wed Mar 08, 2017 6:34 am
by plutocrat
Not a solution, but an alternative ... from the mysql command line you can get a database's size with
SELECT table_schema 'Database Name', SUM( data_length + index_length)/1024/1024 'Database Size (MB)' FROM information_schema.TABLES where table_schema = 'name_of_your_database';"
Assuming you don't need to pass user, password, host information etc to mysql, you could use that in a script like this
Code: Select all
#!/bin/bash
DBNAME=your_db_name
mysql -e "SELECT table_schema 'Database Name', SUM( data_length + index_length)/1024/1024 'Database Size (MB)' FROM information_schema.TABLES where table_schema = '$DBNAME';"
Re: Databases size not updated.
Posted: Wed Mar 08, 2017 6:44 am
by plutocrat
OK, slightly better script which takes db name as an input. :-) Slow day at work ...
Code: Select all
#!/bin/bash
if [ -z "$1" ]
then
echo "No database name supplied. Here is a list of them:"
mysql -sN -e "SHOW DATABASES;"
read -p "Please enter a Database Name: " DBNAME
else
DBNAME=$1
fi
DBSIZE=$(mysql -sN -e "SELECT SUM( data_length + index_length)/1024/1024 FROM information_schema.TABLES where table_schema = '$DBNAME';")
echo $DBNAME is $DBSIZE Mb
Re: Databases size not updated.
Posted: Wed Mar 08, 2017 7:37 am
by plutocrat
Went hunting in the code and found a function in $VESTA/func/db.sh
Code: Select all
get_mysql_disk_usage() {
mysql_connect $HOST
query="SELECT SUM( data_length + index_length ) / 1024 / 1024 \"Size\"
FROM information_schema.TABLES WHERE table_schema='$database'"
usage=$(mysql_query "$query" |tail -n1)
if [ "$usage" == '' ] || [ "$usage" == 'NULL' ] || [ "${usage:0:1}" -eq '0' ]; then
usage=1
fi
export LC_ALL=C
usage=$(printf "%0.f\n" $usage)
}
Seems that swapping the double quotes around Size to single quotes allows the database size calculation to work. i.e. \"Size\" -> \'Size\'
Re: Databases size not updated.
Posted: Wed Mar 08, 2017 10:37 am
by Trentor
plutocrat wrote:Not a solution, but an alternative ... from the mysql command line you can get a database's size with
Thanks @plutocrat!!