0

I had an 800 GB Moodle mdl_logstore_standard_log.ibd database file. I deleted around 900 million rows in 10-15 days. The .idb file was around 800GB and still the same size after deletion. I want to optimize the table so I can fix this.

Is it safe to run OPTIMIZE TABLE while users continue to use the moodle webpage? Or should I stop users from accessing and run OPTIMIZE TABLE?

If I stop access, I must restore access within 48 hours. In this process, if the optimization process does not finish, if I have to interrupt it, will I have a problem?

Thank you.

Şükrü Özdemir
  • 1
  • 1
    It is safe to do it from a data perspective, but you will probably get metadata locks, any writes to the table would probably fail for a while. What happens is that a copy is created and then switch over, in worst case you restore from backup. – NiKiZe Oct 14 '21 at 16:58

1 Answers1

1

Before you need to do the next big delete, read the following blog and plan on doing the delete a more efficient way: http://mysql.rjweb.org/doc.php/deletebig

Meanwhile, users can continue to use the table, even though there is a lot of wasted space on disk. That is, do nothing.

If disk space is a concern, _and the table was created while innodb_file_per_table was ON, the OPTIMIZE TABLE will shrink the disk footprint, at some cost of user access. You may have noticed that the Delete had an impact on user access. (My blog shows ways to avoid such impact.)

If you do nothing, the 800GB file will gradually fill with newly Inserted rows. This may be fine.

Rick James
  • 2,058
  • 5
  • 11
  • Hello I did the deletion by increasing the php timestamp by 100k seconds until there were 180 days of logs from the end to the top. The code is like this: `DELETE FROM mdl_logstore_standard_log WHERE timecreated<1619000000` Actually, it's not because I need space, of course, it would be nice to have free space. My main concern is will random filling of these blanks cause slower reading and writing in this table? And What about to run OPTIMIZE TABLE while users can reach the site? – Şükrü Özdemir Oct 15 '21 at 06:02
  • @ŞükrüÖzdemir - No and no. The performance difference will be minor. Running `OPTIMIZE` table will _probably_ get in the way of users. – Rick James Oct 15 '21 at 15:18