OPTIMIZE TABLE innodb file per table while system is running

Şükrü Özdemir

2/14/23, 1:44 PM

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.

171

0 + 0

mysql

innodb

moodle

NiKiZe

2/14/23, 4:58 PM

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.

Score:1

Server

Rick James

2/14/23, 5:34 PM

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.

0 + 0

Şükrü Özdemir

2/15/23, 6:02 AM

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?

Rick James

2/15/23, 3:18 PM

@ŞükrüÖzdemir - No and no. The performance difference will be minor. Running `OPTIMIZE` table will _probably_ get in the way of users.

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: OPTIMIZE TABLE innodb file per table while system is running

TH: OPTIMIZE TABLE ไฟล์ innodb ต่อตารางในขณะที่ระบบกำลังทำงาน

RO: OPTIMIZE TABLE fișier innodb per tabel în timp ce sistemul rulează

RU: OPTIMIZE TABLE файл innodb для каждой таблицы во время работы системы

VI: TỐI ƯU HÓA BẢNG tệp innodb trên mỗi bảng trong khi hệ thống đang chạy

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.