Mysql – DB disk space requirements comparision between MySQL versions

disk-spaceinnodbMySQLperformance

I have 2 labs where MySQL is installed. DataBases are created and deleted dynamically by certain internal processes. InnoDB is the Engine used.

In one lab, MySQL version 5.0.77 is installed.
The DB directory for each DB is around 230 KB, on average.

    select engine from   information_schema.tables where  table_schema = 'test_1389772840'   and table_name = 'test';
    +--------+
    | engine |
    +--------+
    | InnoDB |
    +--------+
    1 row in set **(0.82 sec)**

In the other lab, MySQL version 5.6.11 is installed.
The DB directory for each DB is around 500 MB, on average.

    select engine from   information_schema.tables where  table_schema = 'test_1389586231'   and table_name = 'test';
    +--------+
    | engine |
    +--------+
    | InnoDB |
    +--------+
    1 row in set **(0.00 sec)**

So in summary, both labs have similar DataBases and tables, and use same engine, but older version is slow, and disk space requirement is less, while newer version is fast, and disk space is requirement huge.

Is this expected ? Why the slowness ? Why the huge space requirement ? How can query speed and disk usage be brought to good levels ?

I checked MySQL documentation, but it did not help. It suggested optimize table ( here http://dev.mysql.com/doc/refman/5.6/en/optimize-table.html ) but there was no change in disk usage.

I found some compression parameters ( here https://blogs.oracle.com/mysqlinnodb/entry/innodb_compression_improvements_in_mysql ) which I tried configuring:

On 5.0.77:

    show variables like '%compr%' ;
    +---------------------------+-------+
    | Variable_name             | Value |
    +---------------------------+-------+
    | have_compress             | YES   |
    | slave_compressed_protocol | OFF   |
    +---------------------------+-------+

BEFORE change of values on 5.6.11:

    show variables like '%compr%' ;
    +------------------------------------------+-------+
    | Variable_name                            | Value |
    +------------------------------------------+-------+
    | have_compress                            | YES   |
    | innodb_compression_failure_threshold_pct | 5     |
    | innodb_compression_level                 | 6     |
    | innodb_compression_pad_pct_max           | 50    |
    | innodb_log_compressed_pages              | ON    |
    | slave_compressed_protocol                | OFF   |
    +------------------------------------------+-------+

AFTER change of values on 5.6.11:

    show variables like '%compr%' ;
    +------------------------------------------+-------+
    | Variable_name                            | Value |
    +------------------------------------------+-------+
    | have_compress                            | YES   |
    | innodb_compression_failure_threshold_pct | 0     |
    | innodb_compression_level                 | 9     |
    | innodb_compression_pad_pct_max           | 0     |
    | innodb_log_compressed_pages              | OFF   |
    | slave_compressed_protocol                | OFF   |
    +------------------------------------------+-------+

I will be monitoring the average size over next few days.

Best Answer

One thing must be noted that you probably never realized: The setting of the option innodb_file_per_table will determine how much space is inside each database folder.

First, take a look at the InnoDB Architecture

InnoDB Architecture

Note the system tablespace (which was know is named ibdata1 in the OS).

  • In MySQL 5.0, the default value for innodb_file_per_table is 0 or OFF
  • In MySQL 5.6, the default value for innodb_file_per_table is 1 or ON
  • With innodb_file_per_table disabled (default in 5.0), the data and index pages for all InnoDB tables are stored inside ibdata1. When you create the table mydb.mytable, you get the following:
    • /var/lib/mysql/mydb/mytables.frm
    • Data and index pages for mydb.mytable are inside ibdata1
  • With innodb_file_per_table enabled (default in 5.6), the data and index pages for all InnoDB tables are outside ibdata1. When you create the table mydb.mytable, you get the following:
    • /var/lib/mysql/mydb/mytables.frm
    • Data and index pages for mydb.mytable are stored in /var/lib/mysql/mydb/mytables.ibd

SUMMARY

Without setting innodb_file_per_table and just storing data

  • Databases under MySQL 5.0 will have nothing but .frm files for its InnoDB tables. Thus, you only have kilobytes of files in each database.
  • Databases under MySQL 5.6 will have .frm and .ibd files for its InnoDB tables. Thus, you will have megabytes or gigabytes of files in each database.
Related Question