I have a problem with OPTIMIZE TABLE query with one of my InnoDB database tables. I have a database that contains several different tables and data is constantly accumulating in these tables. However I have my own automatic services that I use for deleting older rows from these tables.
After deleting old rows with my service I also use optimize table command to free up disk space. (innodb_file_per_table = 1)
Everything has been working well (I have used these services successfully with other tables (size 100-200MB) but now I have faced a problem with one larger table (size 900-1000MB).
Deleting data with that larger table is working fine, but when I try to use OPTIMIZE TABLE query I get error:
Error on mysql optimizing log_events table: Fatal error encountered during command execution. at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() at CleanTablesService.Service1.siivoaTaulut(Object& yhteys) in c:\users\green automation\documents\visual studio 2015\Projects\CleanTablesService\CleanTablesService\Service1.vb:line 127
I assume that this has something to do with some buffer_size or something like that? I'm able to execute this exactly same query successfully with MySQL Workbench but when executing query on my service it throws this error.
So is there some value in my.ini file that I need to adjust so that I can make it work or is there some other method that I should use for optimizing table size (releasing disk space after I have deleted old rows)?
EDIT: I added some more logging to my service and I noticed that this Fatal error is raised 30s after executing OPTIMIZE TABLE. In MySQL Workbench execution of this same operation takes 67.110s so probably problem is that for some reason MySQL is killing execution in the middle of the process after 30s? However I cannot find any parameter from my MySQL Settings file my.ini that would affect this 30s timeout.
OPTIMIZErarely does enough good to be worth it. Let's say you do the delete daily. Measure the disk space after two days. Then measure again, but skip the Optimize after the first day.