Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
    
Please run the following : 1) SHOW VARIABLES LIKE 'innodb_file_per_table'; 2) SELECT COUNT(1) InnoDBTableCount FROM information_schema.tables WHERE engine='InnoDB'; –  RolandoMySQLDBA Jan 17 at 16:33
    
Please run SELECT COUNT(1) InnoDBTableCount FROM information_schema.tables WHERE engine='InnoDB'; –  RolandoMySQLDBA Jan 17 at 16:36
    
Deleted the answer by @Mahesh - code was lifted directly from this answer by another user, but also referenced this post. –  Aaron Bertrand Apr 18 at 20:24
add comment

1 Answer 1

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.
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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