MySQL Forums
Forum List  »  InnoDB

Free up space after dropping indexes
Posted by: Alexandru Badulescu
Date: August 06, 2019 02:53AM

Hi,

I am using 5.7.21-20-57-log (it's actually Percona XtraDB Cluster (GPL), Release rel20, Revision 1702aea, WSREP version 29.26, wsrep_29.26, but it's not configured as a cluster).
We have some daily tables where we insert data, and we kept adding indexes. As we had more daily data to insert, the tables grew kind of big (15-20 GB), and we noticed the indexes use almost half the space. Once we finish all current processing we keep older data for a while, so we wanted to drop the indexes on those tables in order to free up space. The problem is we can't seem to be able to accomplish this for some reason.
For example I have a 12G table. I dropped some big indexes from this table and after that I ran optimize table on it. The .idb file changed as it actually rebuilt the table, but it has the exact same size as before.
I created a new table based on it's current structure (create table2 as table1) and ran an insert into table2 select * from table1.
The size of table2 5.6G.

Is there something I am missing here? I would like to avoid having to create new tables and running the same process for each daily table.



Thank you,
Alex

Options: ReplyQuote


Subject
Views
Written By
Posted
Free up space after dropping indexes
80
August 06, 2019 02:53AM


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.