MySQL Forums
Forum List  »  InnoDB

Re: ibdata1 keeps growing forever....
Posted by: Jesús Uzcanga
Date: May 01, 2018 01:39PM

Ok, Fanos I can help you.

With innodb storage engine, all data (from all tables and all databases) is located by default in ibdata1 so the first thing is.

Why is innodb growing?

To answer your question you have to look the data, so run the following script to see how are those 40 GB distributed and then you can take actions to stop it.

# Show each table size in GB sort by table sizes
SELECT TABLE_SCHEMA,TABLE_NAME,Round(Sum(data_length + index_length) / 1024 / 1024 / 1024, 2) 'TABLEsizeGB' FROM information_schema.tables
GROUP BY TABLE_SCHEMA,TABLE_NAME
ORDER BY TABLEsizeGB DESC;


Also can run the following script to see the size of every DB.

# Show each DB size in GB sort by DB sizes
SELECT TABLE_SCHEMA,Round(Sum(data_length + index_length) / 1024 / 1024 / 1024, 2) 'DBsizeGB' FROM information_schema.tables
GROUP BY TABLE_SCHEMA
ORDER BY DBsizeGB DESC;

If you find where is your data booster you can stop it and delete unused data.


The second thing is how to shrink ibdata1.

If you delete some data to recover space your ibdata file will remain the same size so the only way to reclaim that space is to set innodb_file_per_table option.

To do so:



STEP 01) MySQLDump all databases into a SQL text file (call it SQLData.sql)

STEP 02) Drop all databases (except mysql, information_schema and performance_schema schemas)

STEP 03) Shutdown mysql

STEP 04) Add the following lines to /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT


STEP 05) Delete ibdata1, ib_logfile0 and ib_logfile1 (see update below before deleting!)
At this point, there should only be the mysql schema in /var/lib/mysql

STEP 06) Restart mysql
This will recreate ibdata1 at 10MB (do not configure the option) , ib_logfile0 and ib_logfile1

STEP 07) Reload SQLData.sql into mysql
ibdata1 shoul remain just 10 MB.

Each InnoDB table will exist outside of ibdata1

Suppose you have an InnoDB table named mydb.mytable. If you go into /var/lib/mysql/mydb, you will see two files representing the table

mytable.frm (Storage Engine Header)
mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)
ibdata1 will never contain InnoDB data and Indexes anymore.

___________________________
Ing. Jesús Alfredo Uzcanga
Twitter: @JesusUzcanga

We learn the 20% of what we HEAR,
the 50% of what we SEE,
the 80% of what we DO and
the 95% of what we TEACH.

____________________________________________________________
https://www.linkedin.com/in/jauzcanga/

Options: ReplyQuote


Subject
Views
Written By
Posted
113
April 30, 2018 07:39AM
Re: ibdata1 keeps growing forever....
75
May 01, 2018 01:39PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.