0

I have a table with about 600 million rows. I ran the following commands and noticed

select table_schema "DB name (table_schema)",
    -> sum((data_length+index_length)/1024/1024/1024) AS "DB size in GB" from
    -> information_schema.tables group by table_schema;
+------------------------+-------------------+
| DB name (table_schema) | DB size in GB     |
+------------------------+-------------------+
| my_db                  | 4423.946937561025 |
+------------------------+-------------------+

The whole db is 4.4GB

The table with 600 million rows is:

+------------------------------------+-----------+
| Table                              | Size (MB) |
+------------------------------------+-----------+
| my_table                           |   3729084 |

So this table is 3.7TB in size.

I went to the file system and issued:

sudo du -hs * | sort -rh | head -5
11T my_table.ibd

My table is taking up 11TB in storage.

I ran the following command on the database:

SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.01 sec)

So is it normal for an innodb file to be that large? If not, is there something that can be done to optimize its storage on disk?

I am running a master slave database: sudo mysql --version mysql Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using EditLine wrapper

I am running this on an Ubuntu instance; lsb_release -a No LSB modules are available. Distributor ID: Ubuntu Description: Ubuntu 16.04.5 LTS Release: 16.04 Codename: xenial

Be gentle with your answers as managing mysql is new to me. I usually just write programs to read from mysql and haul the data to something like Elasticsearch or HBase for big data analytics.

Thank you...Amro

9
  • Do you exoerience any problems or why do you check the size?
    – nbk
    Apr 3, 2020 at 20:07
  • Are a number of columns in this table TEXT or BLOB fields, they don't always fall under the category of clustered index in the data_length calculation. Was there a significant purge at data at some point? idb file don't reduce is size however the space will eventually get reused.
    – danblack
    Apr 3, 2020 at 21:04
  • @nbk: Good question, my disk is 16TB so I am getting close to capacity and I am trying to see what I can do to shrink the DB size. Looking into Sharding the contents, but that is a bigger project Apr 3, 2020 at 21:36
  • @danblack Thats a good point. I have 10 columns, 4 are INT, 2 are BIGINT, 2 are VARCHAR (32), and 1 is LONGTEXT, and 1 is DATETIME. I wrote a program to compress the contents of the LONGTEXT column which contains a json by reducing the each key in the json from a descriptive key to an abbreviated key with no more than two letters. Apr 3, 2020 at 21:38
  • at this point i would knock at the doors by amazon, google or ms evntually Alibaba and ask for how much it costs to host a larger database or increase the raid size
    – nbk
    Apr 3, 2020 at 21:44

1 Answer 1

1

Of course, it never does.

In your case, you have about 66.67% fragmentation

I have wickedly good script you can run to calculate the fragmentation based on the difference between the .ibd filesize and the (data_length+index_length) of the any table:

MYSQL_AUTH="-h... -u... -p..."

DATADIR=`mysql -ANe"SELECT @@global.datadir"`

TAG_LIST_FILE=/tmp/list_of_tables.txt
TAG_LIST_ERRS=/tmp/list_of_tables.err
DAT_LIST_FILE=/tmp/list_of_databases.txt
FULL_RPT_FILE=Full_FragReport.txt

MB=${1}

if [ "${MB}" == "" ] ; then exit ; fi

NUM_OK=0
if [ "${MB}" ==  "128" ] ; then NUM_OK=1 ; fi
if [ "${MB}" ==  "256" ] ; then NUM_OK=1 ; fi
if [ "${MB}" ==  "512" ] ; then NUM_OK=1 ; fi
if [ "${MB}" == "1024" ] ; then NUM_OK=1 ; fi
if [ "${MB}" == "2048" ] ; then NUM_OK=1 ; fi
if [ ${NUM_OK} -eq 0 ] ; then exit ; fi

MINIMUM_SIZE="(1024 * 1024 * ${MB})"
(( MIN_SIZE = MB * 1048576 ))

SQL="SELECT CONCAT(table_schema,':',table_name,':',data_length+index_length,':',data_free) FROM information_schema.tables"
SQL="${SQL} WHERE table_schema NOT IN ('information_schema','performance_schema','mysql') AND engine IN ('InnoDB','MyISAM')"
SQL="${SQL} AND data_length+index_length > ${MIN_SIZE}"
mysql ${MYSQL_AUTH} -ANe"${SQL}" >${TAG_LIST_FILE} 2>${TAG_LIST_ERRS}
TAG_FILESIZE=`ls -l ${TAG_LIST_FILE} | awk '{print $5}'`
ERR_FILESIZE=`ls -l ${TAG_LIST_ERRS} | awk '{print $5}'`
if [ ${ERR_FILESIZE} -gt 0 ]
then
        echo "Errors"
        echo "======"
        cat ${ERR_FILESIZE}
        echo
        exit
fi
if [ ${TAG_FILESIZE} -eq 0 ]
then
        echo "No Databases with Tables > ${MB}M (${MIN_SIZE})"
        exit
fi

SPC=""
TAGS=""
for TAG in `cat ${TAG_LIST_FILE}`
do
        NEWTAG=${TAG}
        DB=`echo ${TAG} | sed 's/:/ /g' | awk '{print $1}'`
        TB=`echo ${TAG} | sed 's/:/ /g' | awk '{print $2}'`
        IS=`echo ${TAG} | sed 's/:/ /g' | awk '{print $3}'`
        FR=`echo ${TAG} | sed 's/:/ /g' | awk '{print $4}'`
        IBD=${DATADIR}/${DB}/${TB}.ibd
        MYD=${DATADIR}/${DB}/${TB}.MYD
        MYI=${DATADIR}/${DB}/${TB}.MYI
        if [ -f ${IBD} ]
        then
                (( N1 = IS + FR ))
                N2=`ls -l ${IBD} | awk '{print $5}'`
                if [ ${N1} -lt ${N2} ]
                then
                        (( DF = N2 - N1 ))
                        NEWTAG="${DB}:${TB}:${IS}:${DF}"
                fi
        else
                ND=`ls -l ${MYD} | awk '{print $5}'`
                NI=`ls -l ${MYI} | awk '{print $5}'`
                (( N1 = IS + FR ))
                (( N2 = ND + NI ))
                if [ ${N1} -lt ${N2} ]
                then
                        (( DF = N2 - N1 ))
                        NEWTAG="${DB}:${TB}:${IS}:${DF}"
                fi
        fi
        TAGS="${TAGS}${SPC}${NEWTAG}"
        SPC=" "
done

# Collect List of DBs
DBLIST=""
SPC=""
for DB in `cat ${TAG_LIST_FILE} | sed 's/:/ /' | awk '{print $1}' | sort -u`
do
        DBLIST="${DBLIST}${SPC}${DB}"
        SPC=" "
done

for D2 in ${DBLIST}
do
        SUM=0
        SUM_DF=0
        SQL=""
        SELECT="SELECT"
        for TAG in ${TAGS}
        do
                DB=`echo ${TAG} | sed 's/:/ /g' | awk '{print $1}'`
                if [ "${DB}" == "${D2}" ]
                then
                        TB=`echo ${TAG} | sed 's/:/ /g' | awk '{print $2}'`
                        IS=`echo ${TAG} | sed 's/:/ /g' | awk '{print $3}'`
                        FR=`echo ${TAG} | sed 's/:/ /g' | awk '{print $4}'`
                        (( OS = IS + FR ))
                        SQL="${SQL} ${SELECT} '${TB}' TableName,LPAD(FORMAT(${OS},0),18,' ') FileSize,LPAD(FORMAT(${OS}/POWER(1024,3),2),10,' ') FileSizeGB,LPAD(FORMAT(${IS},0),18,' ') TableSize,LPAD(FORMAT(${IS}/POWER(1024,3),2),11,' ') TableSizeGB,LPAD(FORMAT(${FR},0),18,' ') Difference,LPAD(FORMAT(${FR}/POWER(1024,3),2),12,' ') DifferenceGB,LPAD(FORMAT(${FR}*100.0/${OS},2),7,' ') FragPct"
                        SELECT="UNION SELECT"
                        (( SUM    += OS ))
                        (( SUM_DF += FR ))
                fi
        done
        FRAG_RPT=FragReport_${D2}.txt
        DT=`date +"%Y-%m-%d %H:%M:%S"`
        echo "Fragmentation for Database ${D2} (${DT})" > ${FRAG_RPT}
        echo "Minimum Table Size (${MB} MB = ${MIN_SIZE})" >> ${FRAG_RPT}
        echo >> ${FRAG_RPT}
        BIGSQL="SELECT * FROM (${SQL}) A ORDER BY Difference DESC;"
        BIGSQL="${BIGSQL} SELECT FORMAT(${SUM}/POWER(1024,3),2) DiskSpaceUsed,FORMAT(${SUM_DF}/POWER(1024,3),2) SumDiff;"
        mysql ${MYSQL_AUTH} --table -Ae"${BIGSQL}" 2>&1 >> ${FRAG_RPT}
done

rm -f ${FULL_RPT_FILE}
for TXT in `ls FragReport*.txt`
do
        if [ -f ${FULL_RPT_FILE} ]
        then
                echo -e "\n================================================================================\n" >> ${FULL_RPT_FILE}
        fi
        cat ${TXT} >> ${FULL_RPT_FILE}
done
less ${FULL_RPT_FILE}

You will have too run this script as the OS root user or another OS that has rights to read the datadir

GIVE IT A TRY !!!

I wrote How to monitor mysql percentage disk utilization? like 6 years ago based on the same principles

6
  • CAVEAT on this script. This script does not take into account Partitioned Tables. I actually wrote a separate script for that months back. It's been a while, so I have to dig it up. Apr 3, 2020 at 21:55
  • I agree with you that it is fragmented, even without running the script, for example, I went to a staging database I use for testing, using the query to measure the same table type, and its 15177MB or 15.1GB, and on disk the IBD file is 16G so it correlates with size. So my question is what is the best practice of de-fragmenting the file? Apr 3, 2020 at 22:06
  • Use pt-online-schemna-change --alter "ENGINE=InnoDB" (this is known as a NULL ALTER TABLE. It copies the data into a temp table, run ANALYZE TABLE on the new temp table, and then drops old and renamed temp to the original. Temp table will end of 3.7TB or a little less). Please refer to the docs on this as you will needs to use options that watch for max-load and critical-load scenarios : percona.com/doc/percona-toolkit/3.0/… Apr 4, 2020 at 12:23
  • Thank you very much. At the moment I have mySQL. I'll look into seeing if I can install the percona toolkit without having to setup a percona server. Also, I will need to free up some disk space to make room for the temp database. Apr 4, 2020 at 13:10
  • Temp Table should at least be 3.7TB. I would make sure to free up 5TB Apr 4, 2020 at 15:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.