table_name.ibd file size is increasing ( MySQL 5.7.7 Enterprise) ( Innodb Engines)

Question

I am using MySQL 5.7.17 Enterprise edition in RHEL with configuration innodb_file_per_table=ON .

So now here i have 2 question :

1. Instead of ibd file per table ,i can see ibdata1,ib_logfile1,ib_logfile0 in data directory which is getting updated regularly .However these files are not big in size but collectively , these 3 files are consuming 15-20 GB space . So , instead of having ibd file per table "ON" , do these files still exist or if they exist what they contain which is making it updated regularly ?

2. I have devoted 393 GB to the data directory . and one database folder is occupying 293 GB alone. I have table_name.ibd files of size 84 GB-100 GB. Is there any way that i can check if any memory is getting waste ? and if it is getting waste how can i reclaim it ?

I ran optimize tables for some small tables only ( it helped) because if i run this command for big tables , i need more space before using optimize command as space required is table_size*2 .

I have checked table sizes as well . Size of table and size of respective table's ibd file , vary with 2 -3 GB difference .

Can anyone help me , what can i do here ?

Should i point MySQL data directory to new path ? if it is possible , how can i do this ?

Size of file (in GB) in Descending order : 93 33 19 17 16 14 13 11 11 9.2

Answer 1

Now that you have posted the top tablespace file sizes, here is my attempt to answer your questions:

1. Yes, regardless of what value you have for innodb_file_per_table the files ibdata & ib_logfile will be there. There can be multiple ibdata files depending on how you configure innodb_data_file_path. ib_logfile(s) can also be multiple depending on the value you use for innodb_log_files_in_group. To calculate how much size for log files is good for you, have a look at How to calculate a good innodb log file size

If your ib_logfiles are GBs, it will best to recalculate the size you really need (see link above) and set the size accordingly. ibdata* files are difficult to resize (the only way I know is to mysqldump the whole DB and reimport in a new installation).

2. You can calculate free tablespace that can be compacted by using this query:

   SELECT table_name, (data_free)/power(1024,2) free_space_mb FROM information_schema.tables WHERE table_schema='[yourdb]' ORDER BY free_space_mb DESC;

If you find any tables that have GBs worth of free space, then running OPTIMIZE on such tables should reduce the on-disk tablespace size.

3. If you have bigger disk/volume available that you want to take your datafiles to, use the following as a guide:

   • Stop MySQL service
   • Copy contents of your current data directory (usually /var/lib/mysql on CentOS/RHEL) to the new location
   • Make sure all folders and files on the new location where mysql files have been copied to, have owner AND group set to "mysql".
   • Edit /etc/my.cnf, set datadir to the new location where you copied mysql files to
   • Restart mysql service

Answer 2

thatsaru covered most issues.

It is unwise to let the disk free space to drop below the size of the largest table. That way, you should have the ability to ALTER or OPTIMIZE any table. (You apparently have gone far beyond that safe point.)

ibdata1 will continue to be modified, even with innodb_file_per_table = ON. There is a variety of stuff other than data and indexes that is stored there. The file may occasionally grow in size, but it will never shrink. The only way to shrink it is to do a full dump and reload; not pleasant.

iblog* files (usually 2 of them) are necessary for InnoDB. There are some loose guidelines on how big they should be. Clearly you do not have room to increase their size. Unless you have a really new version of MySQL, it is a pain to shrink their sizes. But it is possible, so this may be a desparation measure. The downside is some (perhaps small) performance loss.

The "Free" space in a table is notorously inaccurate. It can be found from SHOW TABLE STATUS or information_schema.TABLES. PARTITIONed tables have a lot more "Free" space than non-partitioned.

You have been using OPTIMIZE TABLE starting with the smallest? And now you can't do the next one? Well, you are out of luck. Drop some table(s). Get a bigger disk (and migrate the data). Or other painful cures.

OTOH, let's look at the schema for the next largest table. Maybe it has some indexes that can be DROPped. Maybe it uses BIGINT when INT would suffice (8 bytes -> 4 bytes). Etc. If we can find enough cleanups like this, then perhaps ALTER TABLE t MODIFY COLUMN ..., DROP INDEX ...; would successfully run.

ALTER and OPTIMIZE copy the table over and rebuild the indexes. In doing so, there is a chance that the disk footprint will happen to be larger than it had been. These tend to cause a table to grow: Inserts with a PRIMARY KEY less than the current max; Updates; Deletes.