table_name.ibd file size is increasing ( MySQL 5.7.7 Enterprise) ( Innodb Engines) 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
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 &amp; 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