Apparently, one table had a read lock on it, which made the prepared statement wait a bit too long and it decided to flood the database connections till it would got an answer. Only the table was still locked and the database came to a screeching halt.
The last thing I saw on my screen was a bunch of the same prepared statement on my process list screen, completely taking up all the slots and then.... I lost my connection to see the process list.
I don't really understand how the perl script and php page that ran the prepared statement could be so aggressive or where was it defined to continue opening new connections till they got an answer.
This is definatly a first for me and afterwards I changed some of the tables to be more transactional, so that they wont lock.
Conclusion... well there is no conclusion. I'm not going to rule out using prepared statements, but I am going to look more into their behavior (once I find out where they are).
Hi!
ReplyDeleteDid you turn them on purpose? Most of the drivers have them disabled (for the reason you mention).
Cheers,
-Brian
From your post, It doesn't seem to be a problem with prepared statements as much as it does with the perl/php code that was opening connections. If the code was performing the same table lock the prepared statement was, the result could very well have been the same.
ReplyDeleteAlthough, I guess the developers might not have attempted to open all of the connections, if they had a little more transparency as to what was going on in the prepared statements.
You really think prepared statements killed your database? ...really?
ReplyDeleteYou might want to set the --max-connections to something lower, so you make sure scripts don't run amok.
ReplyDeleteAlso, make sure that no client application uses SUPER privilege, so you can always connect to the MySQL server when max_connections -1 has been reached.