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

ibdatainnodbMySQLmysql-5.5mysqladmin

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 :

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 ?
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

Best Answer

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

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).

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.

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

Related Solutions

MySQL InnoDB logfiles resized – review thesql-err.log for potential problems

It is sort of yes and no. Why would I say both?

There are still some transactional data embedded in the old ib_logfiles in conjunction with the ibdata1 file. (See pictorial representation)

What you should have done is this:

mysql -u... -p... -ANe"SET GLOBAL innodb_fast_shutdown = 0"
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

If you did not know of disabling innodb_fast_shutdown, ther you steps should have been

/etc/init.d/mysql.server stop
/etc/init.d/mysql.server start
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

Doing either of these coordinate the purging of all uncommitted writes to tables and indexes. Just looking at the error log, I think you dodged a bullet in this instance. In future, please follow this protocol.

If you are not sure either way, you could put back the old log files, set the old size of innodb_log_file_size, restart mysql. Then, start the process over again as I prescribed above.

MySQL InnoDB Optimization – Dynamic Table Optimization Without ‘File Per Table’

Before answering the question, please click here to see the InnoDB Infrastructure Map

Based on innodb_file_per_table being disabled, let's go through your questions:

Q 1: How much fragmentation is allowed before it affects performance?

The system tablespace can grow to the limit of the disk volume.

EXAMPLE: I just answered a question about what do to when the system tablespace reaches the limit of an ext3 disk : How to solve "The table ... is full" with "innodb_file_per_table"?

There may still be some wiggle room inside the system tablespace. However, when the wiggle room dwindles to the point that all 1023 undo logs inside the system tablespace are completely filled and can no longer extend, then you must add a new system tablespace file.

Please note that when I say wiggle room, I am referring to the free space within the system tablespace that must accommodate the following:

Data Dictionary
Double Write Buffer (can be disabled but not recommended)
Insert Buffer (Cached Index Changes in System Tablespace instead of the OS)
Rollback Segments (1023 slots)
Undo Logs (referenced from the Rollback Segments)
Please refer Back to the InnoDB Infrastructure Map

Q 2: Should InnoDB tables even be optimized (some say yes others say no)?

If you run OPTIMIZE TABLE, you basically make the data and index pages contiguous in the system tablespace. This defragments the table and accesses all data and indexes quicker until the fragmentation reappears over time in production use. This can introduce new areas of fragmentation. Again, all that fragmentation can fill up with data and indexes. This will endanger the wiggle room I mentioned before.

Q 3: How do you test for InnoDB fragmentation if the server does not use the “file per table” option?

Back on Aug 27, 2012, I answered this post : How To Optimize and Repair InnoDB tables? ALTER and OPTIMIZE table failed

I explained there how to get the fragmentation. In essence, you do this:

Goto the OS and run

cd /var/lib/mysql
ls -l ibdata1 | awk '{print $5}'

This gets you the size of ibdata1 in bytes

SELECT (data_length+index_length) InnoDBDataIndexBytes
FROM information_schema.tables WHERE engine='InnoDB';

This gets you the sum total of data and index pages in bytes

Subtract the sum total from ibdata1's total bytes. The difference represents the wiggle room. This space causes fragmentation, but is constantly in use until ibdata1 gets filled.

CAVEAT : When innodb_file_per_table is enabled, I explain how to get the fragmentation of an individual table: Innodb table with many deletes and inserts - is there any disk space wasted?

Q 4: Is fragmentation the only reason to run “optimize table”?

Yes. It is far more beneficial for MyISAM and for InnoDB tables (innodb_file_per_table being enabled). Do this with innodb_file_per_table off and you will just make the system tablespace grow faster. See my post How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?

Q 5: If I do need to run “optimize table” on an InnoDB table should I run ALTER TABLE mydb.mytable ENGINE=InnoDB; and not ANALYZE TABLE

Running ALTER TABLE mydb.mytable ENGINE=InnoDB; would indeed shrink when innodb_file_per_table is enabled. Again, it is not worth when innodb_file_per_table is disabled.

Q 6: Can you selectively tell which innodb tables needs optimizing if the server does not use the "file per table" option?

No you cannot. Why? The INFORMATION_SCHEMA becomes totally useless because all the tables are inside one file. I wrote a script to find to uptime_time (last time an InnoDB table was written) of all InnoDB tables. Is there a way to find the least recently used tables in a schema? script only works for innodb_file_per_table. This shows that you cannot ascentain that fragmentation with ease. You could resort to more aggressive techniques like dumping the tablespace map and located segments with unused space : See this blog post : http://www.markleith.co.uk/2009/01/19/innodb-table-and-tablespace-monitors/. This is way too much firepower to deal with. You could just run the OPTIMIZE TABLE to eliminate segment fragmentation, but this brings us back full circle to getting everything out of ibdata1.

SUGGESTION

If you want to remove all data and index pages from ibdata1 and shrink ibdata1 permanently, please read my Oct 29, 2010 StackOverflow post Howto: Clean a mysql InnoDB storage engine?

As you can see, this subject is not new to me

Jan 13, 2013 : How to check growth of database in mysql?
Sep 23, 2012 : MySQL / InnoDB: Does daily optimization improve performance?
Apr 15, 2012 : What happens when InnoDB hits its tablespace autoextend max?
Apr 11, 2012 : How do you remove fragmentation from InnoDB tables?
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Feb 03, 2012 : Scheduled optimization of tables in MySQL InnoDB

EPILOGUE

Running OPTIMIZE TABLE is not the biggest reason that ibdata1 grows quickly. Please see this post from mysqlperformanceblog and learn about the other contributing factors.

Please remember that most who run OPTIMIZE TABLE do so sequentially. You could probably script many of them in parallel. Of course, you need to convert to innodb_file_per_table like I mentioned before.