CODEWITHSUNDEEP
mysqldatabaseindexinginnodb
Himanshu Goel
Himanshu Goel

Reputation: 594

How to reduce index size of a table in mysql using innodb engine?

I am facing a performance issue in mysql due to large index size on my table. Index size has grown to 6GB and my instance is running on 32GB memory. Majority of rows is not required in that table after a few hours and can be removed selectively. But removing them is a time consuming solution and doesn't reduce index size.

Please suggest some solution to manage this index.

Upvotes: 2

Views: 4319

Answers (2)

Rick James
Rick James

Reputation: 142298

If you can remove all the rows older than X hours, then PARTITIONing is the way to go. PARTITION BY RANGE on the hour and use DROP PARTITION to remove an old hour and REORGANIZE PARTITION to create a new hour. You should have X+2 partitions. More details.

If the deletes are more complex, please provide more details; perhaps we can come up with another solution that deals with the question about index size. Please include SHOW CREATE TABLE.

Even if you cannot use partitions for purging, it may be useful to have partitions for OPTIMIZE. Do not use OPTIMIZE PARTITION; it optimizes the entire table. Instead, use REORGANIZE PARTITION if you see you need to shrink the index.

How big is the table?

How big is innodb_buffer_pool_size?

(6GB index does not seem that bad, especially since you have 32GB of RAM.)

Upvotes: 0

Zafar Malik
Zafar Malik

Reputation: 6844

You can optimize your table to rebuild index and get back space if not getting even after deletion-

optimize table table_name;

But as your table is bulky so it will lock during optimze table and also you are facing issue how can remove old data even you don't need few hours old data. So you can do as per below-

Step1: during night hours or when there is less traffic on your db, first rename your main table and create a new table with same name. Now insert few hours data from old table to new table.

By this you can remove unwanted data and also new table will be optimzed.

Step2: In future to avoid this issue, you can create a stored procedure. Which will will execute in night hours only 1 time per day and either delete till previous day (as per your requirement) data from this table or will move data to any historical table.

Step3: As now your table always keep only sigle day data then you can execute optimize table statement to rebuild and claim space back on this table easily.

Note: delete statement will not rebuild index and will not free space on server. For this you need to do optimize your table. It can be by various ways like by alter statement or by optimize statement etc.

Upvotes: 3

Related Questions