2

Database DB1 is estimated to be 48 KB, while the .sql file I dumped to is 4.7998 KB - almost exactly 10% the size. Regardless of the size drop, note that the Database size was estimated without the index, so that doesn't seem to explain it.

I tried several different methods of size estimation and they report the same. 

MariaDB [information_schema]> SELECT table_schema
    -> AS "Database",
    -> SUM(data_length + index_length) AS "Size (Bytes)",
    -> ROUND(SUM(data_length + index_length) / 1024, 5) AS "Size (KB)",
    -> ROUND(SUM(data_length + index_length) / 1024 / 1024, 5) AS "Size (MB)",
    -> ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 5) AS "Size (GB)"
    -> FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+--------------+-----------+-----------+-----------+
| Database           | Size (Bytes) | Size (KB) | Size (MB) | Size (GB) |
+--------------------+--------------+-----------+-----------+-----------+
| DB1                |        65536 |  64.00000 |   0.06250 |   0.00006 |
| information_schema |       180224 | 176.00000 |   0.17188 |   0.00017 |
| mysql              |       746169 | 728.68066 |   0.71160 |   0.00069 |
| performance_schema |            0 |   0.00000 |   0.00000 |   0.00000 |
+--------------------+--------------+-----------+-----------+-----------+
MariaDB [(none)]> SELECT
    -> table_schema AS "Database",
    -> SUM(data_length) AS "Size (Bytes)",
    -> ROUND(SUM(data_length) / 1024, 5) AS "Size (KB)",
    -> ROUND(SUM(data_length) / 1024 / 1024, 5) AS "Size (MB)",
    -> ROUND(SUM(data_length) / 1024 / 1024 / 1024, 5) AS "Size (GB)"
    -> FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+--------------+-----------+-----------+-----------+
| Database           | Size (Bytes) | Size (KB) | Size (MB) | Size (GB) |
+--------------------+--------------+-----------+-----------+-----------+
| DB1                |        49152 |  48.00000 |   0.04688 |   0.00005 |
| information_schema |        90112 |  88.00000 |   0.08594 |   0.00008 |
| mysql              |       576185 | 562.68066 |   0.54949 |   0.00054 |
| performance_schema |            0 |   0.00000 |   0.00000 |   0.00000 |
+--------------------+--------------+-----------+-----------+-----------+

...

MariaDB [information_schema]> SELECT
    -> table_schema AS "Database",
    -> SUM(data_length) AS "Size (Bytes)",
    -> SUM(data_length) / POWER(1024,1) AS "Size (KB)",
    -> SUM(data_length) / POWER(1024,2) AS "Size (MB)",
    -> SUM(data_length) / POWER(1024,3) AS "Size (GB)"
    -> FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+--------------+----------------+--------------------+-----------------------+
| Database           | Size (Bytes) | Size (KB)      | Size (MB)          | Size (GB)             |
+--------------------+--------------+----------------+--------------------+-----------------------+
| DB1                |        49152 |             48 |           0.046875 |    0.0000457763671875 |
| information_schema |        90112 |             88 |          0.0859375 |   0.00008392333984375 |
| mysql              |       576185 | 562.6806640625 | 0.5494928359985352 | 0.0005366140976548195 |
| performance_schema |            0 |              0 |                  0 |                     0 |
+--------------------+--------------+----------------+--------------------+-----------------------+

me@MyMint:~$ mysqldump -u root -p --databases DB1 > DB1_dataBU_$(date +%Y%m%d).sql
Enter password:
me@MyMint:~$ du -sb DB1_dataBU_20190506.sql
4915    DB1_dataBU_20190506.sql
me@MyMint:~$ ls -lh DB1_dataBU_20190506.sql;
-rw-rw-r-- 1 jason jason 4.8K May  6 12:22 DB1_dataBU_20190506.sql

Either the estimation is off, the dump is missing 90% of it's content/data, or I need to be put straight on how mysqldump works.

JayRugMan
  • 148
  • 7
  • 1
    I don't think these sizes are comparable. The dump file is a text encoding of the database, the table data is binary. If you have `CHAR` columns with trailing whitespace, those spaces are in the DB, but won't be in the dump file. – Barmar May 06 '19 at 19:04
  • 1
    Small numbers will also take less space in the dump file. `INT` is 4 bytes in the database, but small values will take 1-3 bytes in the dump file (plus a byte for the comma delimiter). – Barmar May 06 '19 at 19:12
  • Have you run `OPTIMIZE TABLE` before using `mysqldump`? What table storage engine are you using? What is the actual data you are saving? – Progman May 06 '19 at 19:51
  • @Barmar - I believe you're right. mysql commands I used were found in other articles about checking the database size for backup size estimates, but your comments make a lot of sense. Is there a reliable way to at least estimate how big the .sql file will be when using mysqldump? – JayRugMan May 06 '19 at 22:19
  • @Progman - no, I didn't run OPTIMIZE TABLE first. How would I find out the table storage engine being used? As for the actual data, only three tables, one with no rows, one with 4 rows, and one with 1 row. Only 8 or 9 columns on each table too. varchar not that large, but Barmar's comments makes sense – JayRugMan May 06 '19 at 22:20
  • @JayRugMan See https://stackoverflow.com/questions/213543/how-can-i-check-mysql-engine-type-for-a-specific-table for the used storage engine. – Progman May 07 '19 at 17:14

0 Answers0