LGF Technical Detour: DB Thrashing
Charles Johnson
Tue Jun 5, 2007 at 5:53 pm PDT • Views: 162
This post is going to be totally geeked out, so feel free to tune out if you wish.
I ran into something odd in the last few days while experimenting with MySQL’s query cache, and I’m wondering if any of our DB ninjas might know the reason; Google has been worthless on this one, and none of my books are helping either. When I enable the query cache, our InnoDB session management table keeps going into a heavy thrashing mode, with SELECT queries that pile up and choke the whole system, apparently when the session manager goes into garbage collection. If you’ve noticed LGF suddenly getting unresponsive for short intervals, that’s why.
The query cache is off for now to prevent these hangups, but if anyone can give me a clue why this is happening (or has ideas for things to check) I’d like to know, because the query cache did seem to significantly improve performance in other areas.
And just to throw some geek kudos where deserved, one of the tools I used to observe this problem is the ultimate MySQL lava lamp (shows queries in real time), mytop. According to mytop, we’re processing between 300 to 700 queries per second, and our key efficiency (at last check) is 99.4%.
Frank says: