I’m new to mysql. In /var/lib/mysql directory I’m creating databases. I’m using InnoDB storage engine. In this directory three files namely ibdata1,ib_logfile0,ib_logfile1 created automatically. What is the use of these files and it contains any information? If I deleted these files what happens?
Any one answer for this doubt.
Dont delete the files. Those are the files where the actual data, indexes and the innodb log are stored for all innodb databases
If we insert records in the db, will the size of these files increase?.
Will these files affects the execution speed.
If databases r deleted will the size of these files decrease?
Yes when the ibdata1 file is to small for the amount of data that you are trying store in it. It will grow automatically.
The ib_logfile files will continue to be the same size always, they do not grow.
The question isn’t if the files will affect the execution speed.
The quesiton is if more data in the database will affect execution speed, and the answer is, yes more data in the database will affect execution speed.
But if you have proper indexes in your tables you will not notice it that much.
No, unfortunately not.
But generally when you have a database you seldom have the situation that you need to shrink the table space.
Because often the size of a database is either growing or pretty stable in size.
If you’re using the innodb_file_per_table option, then each InnoDB table gets its own .ibd file, so you can recover freed-up space in a table by rebuilding just that one table. The easiest way to rebuild an InnoDB table is to run the command, “ALTER TABLE tablename ENGINE=InnoDB;”. However, this will build the new copy of the table on disk before dropping the old one, so you’ll need to have enough free disk space to keep the entire second copy of the table. If you’re tight on disk space on the database server (which is probably why you want to free up the space trapped in the InnoDB tablespace in the first place), then you can mysqldump the table to another machine with more free space and then re-import it back into the database server. It’s still a pain to have to do that, but it’s a lot better than having to dump ALL of your tables.