How to clear the ibdata1 file and how it may affect the performance?
database, innodb, mysql
Solution
Once space is consumed by your ibdata file it never goes away. The only way to 'shink' the data file is dump your dbs, drop them all, delete the ibdata file, restart mysql and reload the dump.
If you frequently drop entire tables, you can config innodb to use one data file per table. In this case, when you drop a table, you can delete that table specific ibdata file.
Problem
I looked into the ibdata file and it was 128 GB. I took out size of each table and a table was having 5 Million of record and it was showing 90 GB. I truncated that table since it was not necessary and I viewed the ibdata1 size. Still it is showing 128 GB. Then I took out sizes of each table and sum of then is just 35-38 GB. How can I get back the 90 GB of space on my Db server. I am using mysql with php on red-hat Linux server. I guess- if we delete anything on DB it just removes the replication but space allotted to it sill not be deallocated till we do it manually. Is it the way.