-1

We have 2 MySQL databases:

  1. A MySQL 5.6 database, managed by our cloud provider (source).
  2. A MySQL 5.7 database, managed by us (target).

We can download a mysqldump from the source database and then import it on the target database.

mysqldump source_database --user=username --host=hostname \
    --protocol=tcp --set-gtid-purged=OFF --single-transaction \
    > dump.sql

mysql target_database < dump.sql

I have an SQL query that shows me the sizes of the tables inside the database:

SELECT
  table_name   AS                                        `Table`,
  ROUND(((data_length + index_length) / 1024 / 1024), 2) `Size (MiB)`,
  ROUND(((index_length) / 1024 / 1024), 2)               `Size index (MiB)`,
  ROUND(((data_length) / 1024 / 1024), 2)                `Size data (MiB)`
FROM information_schema.TABLES
WHERE `table_schema` = 'database_name'
ORDER BY (data_length + index_length) DESC;

When I look at the sizes of the database tables and indexes, I see a huge difference for some tables.

Source database

+-------+------------+------------------+-----------------+
| Table | Size (MiB) | Size index (MiB) | Size data (MiB) |
+-------+------------+------------------+-----------------+
| t1    |   11670.22 |            81.22 |        11589.00 |
| t2    |   12123.59 |          1279.59 |        10844.00 |
+-------+------------+------------------+-----------------+

Target database

+-------+------------+------------------+-----------------+
| Table | Size (MiB) | Size index (MiB) | Size data (MiB) |
+-------+------------+------------------+-----------------+
| t1    |   56582.55 |           260.55 |        56322.00 |
| t2    |   26442.00 |          4649.00 |        21793.00 |
+-------+------------+------------------+-----------------+

Other tables also show increased sizes on the target database. The schema for t1 is as follows:

create table t1
(
  t1_id     int auto_increment primary key,
  t1_type   varchar(255)                                  not null,
  t1_string longtext                                      not null,
  t1_rtype  enum ('a', 'b', 'c', 'd', 'e')    default 'a' not null,
  t1_rid    int                                           not null,
  t1_s      tinyint(1) default '0'                        not null,
  t1_t      int(2) default '0'                            not null,
  t1_sa     datetime                                      null,
  t1_lr     longtext                                      null,
  t1_c      datetime                                      null,
  t1_u      datetime                                      null
);

create index t1_i1
  on t1 (t1_s);

create index t1_i2
  on t1 (t1_rid, t1_rtype, t1_type);

What could be causing these huge differences in data and index size? Is it because of the different database versions? Is there any my.cnf setting that we might have to adjust?

migrated from serverfault.com Sep 18 '18 at 10:27

This question came from our site for system and network administrators.

  • I'd try to import the mysqldump into a fresh 5.6 instance for a fair comparision. Could also use an accurate table row count (SELECT COUNT(*) FROM tbl) rather than the information_schema estimate. – danblack Sep 18 '18 at 9:14
  • Have you tried defragmenting the source table? – Jon of All Trades Sep 18 '18 at 14:09
  • Defragmentation increased the size to 60 GiB, so I think that doesn't help. – david Sep 26 '18 at 7:58
  • What is the schema for t2? – Rick James Oct 7 '18 at 17:32
1

Smells like the source was ENGINE=MyISAM, but the target was ENGINE=InnoDB. Please provide output from SHOW CREATE TABLE from each machine.

Off-record

The storage format for LONGTEXT (and other 'long' things) changed with DYNAMIC. (Can't tell what format the source had.) Maybe the following query will get some insight into whether this might be an avenue to pursue:

SELECT  COUNT(*),
        SUM(LENGTH(t1_string) < 768) AS str_ct,
        AVG(LENGTH(t1_string)) AS str_avg,
        SUM(LENGTH(t1_lr) < 768) AS lr_ct,
        AVG(LENGTH(t1_lr)) AS lr_avg
    FROM  t1;

And likewise for t2.

more

You have a bunch of "large" strings that would be handled differently due to the change in ROW_FORMAT from COMPACT to DYNAMIC.

Consider writing a bug report at bugs.mysql.com -- This seems like a significant 'regression' that they should address.

You could switch back to ROW_FORMAT=COMPACT to see if that shrinks the data back. The best process might be something like:

CREATE TABLE new LIKE t1;
ALTER TABLE new ROW_FORMAT=COMPACT;
INSERT INTO new SELECT * FROM t1;
RENAME TABLE t1 TO old, new TO t1;
DROP TABLE old;
  • They were both InnoDB tables, and the schema is the same for both. – david Dec 14 '18 at 14:30
  • Can you get SELECT * FROM information_schema.INNODB_SYS_TABLES WHERE name = 'dbname/tablename' on both machines? – Rick James Dec 14 '18 at 17:15
  • On the target machine: | TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | +----------+----------------------------------+------+--------+-------+-------------+------------+---------------+------------+ | 842 | xxx | 33 | 14 | 830 | Barracuda | Dynamic | 0 | Single | – david Jan 8 at 10:44
  • The source machine was deleted already, so I can't show that anymore. :/ – david Jan 8 at 10:45
  • @david - Seem my addition. Maybe we can figure something out. – Rick James Jan 8 at 21:33
0

Unless there was a problem during the dump or load of the table, both tables should have the same number of rows. But that doesn't automatically mean the actual size on disk will be the same on both servers.

You did not mention which storage engine is being used. I will assume it is innodb since it is the default for MySQL.

I would suggest that you first check what is the ROW_FORMAT and the FILE_FORMAT for the tables on both servers. The actual size of the table on disk will change based on these values. You can check the innodb table settings with:

    SELECT NAME,FILE_FORMAT,ROW_FORMAT,PAGE_SIZE
    FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES
    where NAME='database_name/t1' or NAME='database_name/t2';

The available ROW_FORMAT settings are described on MySQL Docs: https://dev.mysql.com/doc/refman/5.6/en/innodb-row-format-specification.html

On MySQL 5.7 the default FILE_FORMAT was switched to Barracuda (before it was Antelope) and the ROW_FORMAT is now DYNAMIC (changed from COMPACT).

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.