1

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

  • Can you post the top 10 file sizes (table_name.ibd)? You can optimize one table at a time and in that case you should have enough space available? Optimize is only going to help if you've had lots of deleted rows in a table so that space can be reclaimed. – thatsaru Mar 30 '17 at 12:14
  • Size of top 10 file (table_name.ibd) posted. As per my understanding Optimize doesn't decrease file size always . It will , only when some space needs to be reclaimed . M i right :) ?? – Ankit Kapoor Mar 30 '17 at 12:53
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).

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

  1. 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
  • current value of Innodb_data_file_path is ibdata1:12M:autoextend which is by default. current value of Innodb_log_filesin_group is 2 which is by default. current value of innodb_log_file_size is 2 GB. i have calculated log file size. Difference of log sequence number is coming as 0 MB. So what should be the size of innodb_log_file_size and should i change this parameter in CNF now ? Will it not impact MySQL data 2. I have calculated size using above query . None of the table free space is in GB . Max free space of a table is 26 MB – Ankit Kapoor Apr 4 '17 at 9:48
  • user21546 - from your comment, it seems your tables are not fragmented much and the table spaces contain valid data i.e. you have a large dataset that does need the disk space it is using. So your options in such a case are limited. The only I can think of is to move your datadir to a bigger volume (see my original answer as a guide on how to do this). You can also gain perhaps a GB by reducing the innodb_log_file_size but in the current scheme of things it doesn't sound much. – thatsaru Apr 4 '17 at 10:55
  • After reducing innodb_log_file_size to 100 MB and giving a restart . Will it impact data any how ? Or it will be safe ? Yes , i am trying to move tables from one current mount point to other. – Ankit Kapoor Apr 4 '17 at 11:31
  • Use set global innodb_fast_shutdown=0; when logged into MySQL before shutting the MySQL server. Then you can restart after changing the log file size. MySQL will then create new log files using the size you use. – thatsaru Apr 4 '17 at 12:15
  • Sure will let you know soon – Ankit Kapoor Apr 4 '17 at 13:03
0

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.

  • i have started optimizing with smallest one . Yes i have dropped some unwanted tables . Now the disk space has been reduced to 88%. – Ankit Kapoor Apr 4 '17 at 9:52
  • You should work on getting more disk space and/or shrinking the tables. 12% is dangerously low. – Rick James Apr 4 '17 at 16:09
  • Shrinking tables will involve taking mysql dump and re-storing it ? – Ankit Kapoor Apr 6 '17 at 11:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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