Log in

No account? Create an account
entries friends calendar profile my webpage Previous Previous Next Next
Server maintenance again... - Tina Marie's Ramblings
Red hair and black leather, my favorite colour scheme...
Server maintenance again...
...otherwise known as, "Wow! Tina's an idiot! Again!".

So I've always had this weird issue that pops up once a year or so - my load averages spike way up for a week or so. It used to just happen, then go away, but after the last round, I installed monit, which sends me an email when it happens. Then I can log on, look at what's happening, and still be puzzled.

It started again on Friday. This time, it was mySql - it was holding the box consistently at around 1.5 load average. I immediately suspected the mimeDefangLog table, because I log every single piece of mail that comes though the server - on a good day, that's 20K inserts, on a bad day, it can be 200K. I use that data to generate some tables to show me things like the proportion of spam to real mail, viruses, and so on. That table's been around since I switched to this machine, about 3 years ago.

The plan was to keep two weeks of data, but the code that purges it happens to be in the same php file that generates the graphs. I only look at the graphs when there's a problem, so sometimes that table goes a long time without a purge. I've thrown out all the older-then-two weeks data a few times in the last week, but today, mySQL was still running at over 1 LA. I took out some unneeded code from mimeDefang which saved me 2 queries (not inserts, just queries) per mail, but that didn't seem to help much.

So I decided to throw the purge into a cron job, so it happens every night, and the table doesn't get so big. Then I thought, "Hm. Massive numbers of inserts and deletes. I wonder if mySQL databases need compacted?". The answer "Of course they do. They're a database. Duh." - so I ran 'optimize table' against the offending table. MySQL immediately settled down to the bottom of my 'top' display, and has stayed there for an hour. Then I added it to the nightly purge job, so it keeps it in in better shape.

Now I'm back down to a shiny, happy .15 load average again. For another year, at least.

Current Mood: geeky geeky

1 comment or Leave a comment
greeneyedsadie From: greeneyedsadie Date: December 3rd, 2008 09:07 pm (UTC) (Link)
You're so geeky. And hot.
1 comment or Leave a comment