Determine size of mysqldump prior to dumping

Question

This question is a followup on this previous question.

Q: How can I determine or estimate the size of the SQL dump file prior to using something like mysqldump?

I have tried using the most upvoted answer but it doesn't seem to be working in my case. I am actually trying to determine a specific database's size prior to dumping, so I am using this query

SELECT
    Data_BB / POWER(1024,1) Data_KB,
    Data_BB / POWER(1024,2) Data_MB,
    Data_BB / POWER(1024,3) Data_GB
FROM (SELECT SUM(data_length) Data_BB FROM information_schema.tables
WHERE table_schema ='db_name') A;

I am getting a filesize of ~4.5mb whereas my (uncompressed) dump takes less than 2mb. I have also tried excluding VIEWs from my dump as such:

SELECT
    Data_BB / POWER(1024,1) Data_KB,
    Data_BB / POWER(1024,2) Data_MB,
    Data_BB / POWER(1024,3) Data_GB
FROM (SELECT SUM(data_length) Data_BB FROM information_schema.tables
WHERE table_schema ='db_name' AND engine IS NOT NULL) A;

but this made no difference. Am I doing something wrong here?

(Most of my tables are InnoDB except for two who are MyISAM but contain almost no data)

Answer 1

Actually, the numbers you have indicate something special: Your table data are fragmented.

Why such fragmentation ? InnoDB data and index pages are 16K. Row data will never completely fill up 16K.

I am sure if you reload the dump into another mysql instance, the data will take up less space, no fragmentation.

What you could also do is shrink all the InnoDB tables. How ???

To shrink mydb.mytable run

OPTIMIZE TABLE mydb.mytable;

or

ANALYZE TABLE mydb.mytable;

You could probably script i as follows:

SQLSTMT="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') FROM information_schema.tables WHERE engine = 'InnoDB' AND table_schema NOT IN ('information_schema','mysql','performance_schema','sys')"
INNODB_CONV_SCRIPT=Shrunk_InnoDB.sql
echo "SET SQL_LOG_BIN = 0;" > ${INNODB_CONV_SCRIPT}
mysql -h... -u... -p... -ANe"${SQL}" >> ${INNODB_CONV_SCRIPT}
mysql -h... -u... -p... < ${INNODB_CONV_SCRIPT}

After running this all your tables will be smaller.

To answer your actual question, no, you are not doing anything wrong.

Views take up no actual diskspace except for the View Definition.

Answer 2

Or, you data could be inefficiently stored.

If you have a BIGINT with the number 1 in it, it takes 8 bytes to store in the database, but 2 in dump (including the comma): ,1

There are other situations where the dump will be be bigger than the table: TINYINT (1 bytes) with 127 (4 bytes in dump). Or strings that need quotes and a comma in the dump. Etc. Plus there is overhead to say

INSERT INTO `your_long_table_name` (`your_long_column_name`) VALUES (1);