MariaDB/MySQL Table Repair Eating TONS of disk space
-
I have a MariaDB database that is of moderate size (~48 GB) and two of the tables within this database have corruption. One of the offending tables is reportedly ~25 GB in size. When running REPAIR TABLE, the process eats up all the remaining disk space (which admittedly wasn't a lot, 14 GB).
I started growing the disk and gave it an additional 50 GB. The repair process gobbled it up. Gave it 50 GB more, repair process gobbled it up. Gave it 100 GB more (for a total of 200 GB additional disk), and the repair process still gobbled it all up. I'm a little baffled as to why and am wondering if anyone here can shed some light as to why a 25GB table would need over 200GB of working space for a repair?
The database is using MyISAM storage (I know, I'm working on it) and the table in question stores data (document images) in longblob format. From what I understand, MyISAM table repairs basically involve reconstructing the table, so I wouldn't be surprised at needing at least an equal amount of free space to the size of the table. This behavior has me a little confused.
-
@anthonyh said in MariaDB/MySQL Table Repair Eating TONS of disk space:
I have a MariaDB database that is of moderate size (~48 GB) and two of the tables within this database have corruption. One of the offending tables is reportedly ~25 GB in size. When running REPAIR TABLE, the process eats up all the remaining disk space (which admittedly wasn't a lot, 14 GB).
I started growing the disk and gave it an additional 50 GB. The repair process gobbled it up. Gave it 50 GB more, repair process gobbled it up. Gave it 100 GB more (for a total of 200 GB additional disk), and the repair process still gobbled it all up. I'm a little baffled as to why and am wondering if anyone here can shed some light as to why a 25GB table would need over 200GB of working space for a repair?
The database is using MyISAM storage (I know, I'm working on it) and the table in question stores data (document images) in longblob format. From what I understand, MyISAM table repairs basically involve reconstructing the table, so I wouldn't be surprised at needing at least an equal amount of free space to the size of the table. This behavior has me a little confused.
I assume there are no database clients trying to access the database during your repair.
I don't know the answer to your question but have you had a look at the different settings for repair table?
https://dev.mysql.com/doc/refman/5.7/en/repair-table-optimization.htmlCheck
myisam_max_sort_file_sizefor instance. If it's larger than your disk then it could outgrow the available space. -
@pete-s said in MariaDB/MySQL Table Repair Eating TONS of disk space:
@anthonyh said in MariaDB/MySQL Table Repair Eating TONS of disk space:
I have a MariaDB database that is of moderate size (~48 GB) and two of the tables within this database have corruption. One of the offending tables is reportedly ~25 GB in size. When running REPAIR TABLE, the process eats up all the remaining disk space (which admittedly wasn't a lot, 14 GB).
I started growing the disk and gave it an additional 50 GB. The repair process gobbled it up. Gave it 50 GB more, repair process gobbled it up. Gave it 100 GB more (for a total of 200 GB additional disk), and the repair process still gobbled it all up. I'm a little baffled as to why and am wondering if anyone here can shed some light as to why a 25GB table would need over 200GB of working space for a repair?
The database is using MyISAM storage (I know, I'm working on it) and the table in question stores data (document images) in longblob format. From what I understand, MyISAM table repairs basically involve reconstructing the table, so I wouldn't be surprised at needing at least an equal amount of free space to the size of the table. This behavior has me a little confused.
I assume there are no database clients trying to access the database during your repair.
I don't know the answer to your question but have you had a look at the different settings for repair table?
https://dev.mysql.com/doc/refman/5.7/en/repair-table-optimization.htmlCheck
myisam_max_sort_file_sizefor instance. If it's larger than your disk then it could outgrow the available space.You are correct. No clients are accessing the database. I'm doing the repair on a clone of the actual host.
I will investigate myisam_max_sort_file_size and report back.
