MySQL PHP Query Delays
Posted on 2011-09-23
I have a MySQL server running, serving a large amount of numeric data to a website written in PHP (running on the same physical server).
The site has been running fine for years, with no significant code changes to the primary functions.
The site is setup with a frame set, a left menu and a main body.
On occasion, when a link is clicked in the left that opens a specific report, the right page will simply go to white screen, and never load. In my code, if the MySQL connection fails, it should report "Couldn't connect to MySQL". This never happens.
I go into MySQL admin, and I see several processes that are all locked. All of these are various SELECT statements into my table, all of which are valid based on different reports that have been opened.
As the data was needed quickly, I performed a server reboot and everything was accessible again.
My question is, what steps can I take now to hopefully prevent this from happening again. Worse case, what should I do next time this happens, as the data is needed quickly by the users, and I won't have long before I'll have to reboot.
Thanks for any input