Heya
before you leap on the reply button, please read through my entire post.
Let start off by saying that the problem started following an upgrade to version 3.23.58 - previous versions simply did not display any of the symptoms I'll describe. unfortunately for me, I'm locked into this version for various stupid (i.e, administrative) reasons.
I run a 1000 unique-visits-a-sec website, moderate in size, this website runs quite a few background services with cron and has many features - content, forums, leagues, a clans system and other features. All home brewed (so I have no one else to blame).
The problem (finally), is that at random intervals, a query locks up a table (any table, any query) and connections begin stacking up - i.e, mysql litterally chokes up on it's queries and blocks all access, returning a "too many connections" error.
at first, I figured this is due to load - nope, the server doesn't even pass the 1.0 load mark and the problem manifested itself also at 5:00AM - my site's low point in activity.
next, I thought I have a rogue query going insane, however, since mysql is choked, I can't even connect to perform show processlist (remember, this is 3.23 ... no SUPER users). once, I was lucky enough to see this phenomenon happening (as joyfull as watching a tornado destroying your house).
The interesting thing is that it looks like a weird dead lock. the process that locks the table that time was an esspecially light query that drew static forums information from our forum table (a 20 rows table ...) and had an inner join to another small-medium sized table (20k rows or so). The query's been running for more than a few hundred seconds and was in status "TABLES LOCKED". Many other queries were stacked below this one with the LOCKED status. till finally, the site choked.
I immediately split up this query, assuming that I've found my "rogue query", but of course, it came back.
Following my previous observations, the word "dead lock" immediately popped into my head - needless to say, all my searches after known issues or bugs or problems lead me no where. Apparently I'm the only miserable bastard to encounter this pink elephant.
Currently, I've had another idea - that somehow my site's security has been compromised and someone is injecting a "lock tables" command into my application, but as of yet, I've not figured out how, and of course I've added quite a few layers of defense to the site's security (both in the system and application level).
if anyone has *any ideas*, please share'em with me.
before you leap on the reply button, please read through my entire post.
Let start off by saying that the problem started following an upgrade to version 3.23.58 - previous versions simply did not display any of the symptoms I'll describe. unfortunately for me, I'm locked into this version for various stupid (i.e, administrative) reasons.
I run a 1000 unique-visits-a-sec website, moderate in size, this website runs quite a few background services with cron and has many features - content, forums, leagues, a clans system and other features. All home brewed (so I have no one else to blame).
The problem (finally), is that at random intervals, a query locks up a table (any table, any query) and connections begin stacking up - i.e, mysql litterally chokes up on it's queries and blocks all access, returning a "too many connections" error.
at first, I figured this is due to load - nope, the server doesn't even pass the 1.0 load mark and the problem manifested itself also at 5:00AM - my site's low point in activity.
next, I thought I have a rogue query going insane, however, since mysql is choked, I can't even connect to perform show processlist (remember, this is 3.23 ... no SUPER users). once, I was lucky enough to see this phenomenon happening (as joyfull as watching a tornado destroying your house).
The interesting thing is that it looks like a weird dead lock. the process that locks the table that time was an esspecially light query that drew static forums information from our forum table (a 20 rows table ...) and had an inner join to another small-medium sized table (20k rows or so). The query's been running for more than a few hundred seconds and was in status "TABLES LOCKED". Many other queries were stacked below this one with the LOCKED status. till finally, the site choked.
I immediately split up this query, assuming that I've found my "rogue query", but of course, it came back.
Following my previous observations, the word "dead lock" immediately popped into my head - needless to say, all my searches after known issues or bugs or problems lead me no where. Apparently I'm the only miserable bastard to encounter this pink elephant.
Currently, I've had another idea - that somehow my site's security has been compromised and someone is injecting a "lock tables" command into my application, but as of yet, I've not figured out how, and of course I've added quite a few layers of defense to the site's security (both in the system and application level).
if anyone has *any ideas*, please share'em with me.
