0

My database size (with indexs): 70GB
My ibdata1 size: 211GB
How can it possible with inno_db_per_file=1 setting?

SELECT FILE_NAME,DATA_FREE, (DATA_FREE / 1024 / 1024 / 1024) AS GB FROM information_schema.files WHERE file_id = 0

Result is:
FILENAME | DATE_FREE | GB
./ibdata1 | 212448837632 | 197

my settings

innodb_file_per_table=1    
innodb_log_file_size=512M   
innodb_buffer_pool_size=128G  
innodb_buffer_pool_instance=128   
join_buffer_size=64G
3
  • I have 518G /var/lib/mysql, and only 25G ibdata1. I guess your db is badly designed and could be greatly optimized
    – the_nuts
    Aug 11, 2021 at 14:17
  • Also read this: bugs.mysql.com/bug.php?id=1341 and notice the bug has "Verified" status (i.e. not fixed). Aug 11, 2021 at 14:23
  • What version of MySQL?
    – Rick James
    Aug 12, 2021 at 19:23

1 Answer 1

1

Possible causes of huge ibdata1:

  • It was huge before setting innodb_file_per_table = 1
  • A huge UPDATE or DELETE wad run, leading to lots of "undo" data that may have been stored (temporarily) in ibdata1.
  • Maybe a very complex SELECT needed it for temp space?
  • other(?)

Alas, there is no simple way to shrink a big ibdata1. (There are lots of Q&A about it, probably on this forum.)

1
  • Thank you for comment, i deleted my db and ibdata files with export db. My new ibdata1 size 9 Gb after import, i will watch this file Aug 17, 2021 at 14:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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