Hacker News Reader

Comment by toast0 | Hacker News Reader

Unless MySQL changed it since I last looked, the one large innodb file is a big PITA, but otherwise you can do OPTIMIZE TABLE to compact tables; apparently this can sometimes be done in place, but I'm used to it writing to a new file so you'd need room for old and new; and at least for MyISAM locks the table for the whole time, so I hope you've got a way to manage that too, but that's about the same as VACUUM FULL.

View on HN

Innodb can be split into individual per table files and has had this ability for years.

Yeah, but the default used to be one file for everything, and it's hard to split things after you started with that. Especially if your data is sizable.

The default innodb_file_per_table changed to ON in 5.6, released nearly 9 years ago.

It's not really that hard to change if you started with OFF, it just requires a logical dump and reload (typically on a replica, followed by some re-cloning and promotion). Companies with sizable data tend to have database engineers who can handle this fairly easily.

In any case, it's not an urgent maintenance operation like a VACUUM problem...

It's not nearly the problem now it was ~6 years ago when I was stuck with 8TB in a single file at a small startup.

I look around now and you a right. It's really no big deal to move a few terabytes of data around but it sure was a mess back then and I'm once bitten, twice shy.

Having been in a similar situation 11 years ago, from my POV it was waaay easier 6 years ago than it was back then ;)

I was fortunate that the startup was willing to devote sufficient time and resources to help me get our DB problems under control -- including getting expert consulting services from Percona, attending conferences, etc. However I've definitely seen some other startups that didn't take DB tech debt seriously enough, and just kicked the can down the road until a major disaster occurred.

Anyway though, even aside from improvements in hardware and tooling, managing large DBs just gets less stressful once you've spent some time on the problem. Assuming you successfully solved this 6 years ago, why shy away from it in the future? You almost certainly learned a lot in the process, and the good news is it only gets easier from there!

Reply on news.ycombinator.com