0

I am using MySql Engine=InnoDB for my application. There is a table which collects Search data from different location and save it for processing. After a particular interval of time we remove this data using scheduler. Problem happens when we delete large number of rows, it doesn't free up the disk space. We have already set innodb_file_per_table ON. One solution is to run a query:

Optimize Table <TableName>

But this cause other Select operation fails on that particular table, telling:

Caused by: java.sql.SQLException: Table definition has changed, please retry transaction at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:545) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:115) at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1983) at com.mysql.cj.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1826) at com.mysql.cj.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1923) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:353) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60) ... 154 common frames omitted

I need a solution to reclaim/reuse table space. I am using Spring-boot, so this solution should be handled from the application. Even if partition needs to be used, it should be configurable from application.

Narendra Chamoli
  • 221
  • 2
  • 7
  • There is a topic which seems like this but doesn't have satisfactory answer as it was suggesting innodb_file_per_table which I already have but still the issue exist. – Narendra Chamoli Mar 06 '19 at 06:46
  • Big `DELETEs` are problematic. Here are several workarounds: http://mysql.rjweb.org/doc.php/deletebig – Rick James Mar 15 '19 at 22:03

1 Answers1

0

A couple of thoughts:

  1. While the disk space is not being released, it is not being lost. As you insert new rows into that table, the previously used space will be reused. In other words, if your table grows to a similar size each time before you do your deletes, there is no problem.
  2. You should seriously consider using partitioning on that table. If each batch goes into a different partition, you can drop the partition rather than deleting. This would be much faster and in fact would release the storage back to the operating system. You may only need at most two partitions at any given time.
Rob
  • 6,247
  • 2
  • 25
  • 33
  • Be careful about recommending partitioning. The requirement that the partition key must be part of every unique key of the table often makes it inappropriate for the use case. For example, if the OP wants to delete old data by a timestamp column, then the partition deletion would be useful only if the timestamp column were part of the primary key. That's seldom true. – Bill Karwin Mar 05 '19 at 15:34
  • Correct @BillKarwin, if I use partition I need to delete them as per timestamp. Also, those partition needs to be dynamic from application. That's something I am concerned about. Any help in that direction would be appreciated. – Narendra Chamoli Mar 06 '19 at 06:42
  • The application does not need to be aware of partitioning - it just references the table and the partitioning his handled by the database. There are lots of use cases where partitioning's unique key limitations can be accommodated. – Rob Mar 06 '19 at 14:36