At my last job I mainly did database work and very little coding. This was unusual since I’m primarily a code monkey, but I was supposed to be starting with a 2-month project adding some features to a reporting application and unfortunately it turned into the job from hell. I lasted 4 months – the job market in Boston is way too hot right now to put up with a crap job like that.
The database had worked fine for about a year with limited data, but around the time I started we added a very large data feed and brought the system to its knees in about 3 weeks (reports that had finished in under an hour now took 1-2 days). In retrospect I think the simple fix would have been to aggressively normalize the database – that would have made adding indexes and other standard performance fixes more applicable – but we were so busy fighting fires we didn’t really have time to fix things properly. Classic catch-22.
I’d only been there about a month and a half when a data load failed with the error “the table is full”. Ugh. I started poking around and the table in question was only 4GB. I know MySQL can handle huge databases – so WTF? I found this excellent writeup by Jeremy Zawodny.
We were running version 4.1, and hadn’t done much at all to the configuration; it was pretty much running a default install, using MyISAM tables. So the fix was pretty simple:
alter table foo max_rows=1000000000;
Jeremy was certainly right – it took a long time (on a dual Xeon PowerEdge with 8GB ram):
Query OK, 12770057 rows affected (7 hours 31 min 23.87 sec)
Records: 12770057 Duplicates: 0 Warnings: 0
Looking occasionally at “show processlist;” and the final file times
Dec 11 15:26 foo.frm
Dec 11 18:19 foo.MYD
Dec 11 22:57 foo.MYI
it took about 3 hours to copy the data into the new temporary table but 4 1/2 hours to build the indexes (“Repair with keycache”).
So that fixed that table, but we were running several identical databases, one for each data feed, so all tables in all databases that might fill up had to be fixed, and that took another few days. Good times.