Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

MySQL PHP Query Delays

Hello,

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
0
compsol1993
Asked:
compsol1993
1 Solution
 
maeltarCommented:
I would suggest just restarting the mysql process rather than a full reboot..

However, that is not a fix, far from it, I would be inclined to suggest that you look at the web server first, as you said that you just had a blank page returned/served this would indicate that the server did not respond.

Check the error logs both for mysql and apache, and post if you can around the relative times the issue occured.
0
 
wolfgang_93Commented:
It sounds like you have MyISAM tables. In MyISAM, table locking is primitive in that tables get fully locked even if a single row is fetched. Look at converting to InnoDB to get row-level locking and only rows locked that are being updated -- not entire tables.
0
 
compsol1993Author Commented:
After posting, my research led me down the same road, and I ended up making the conversion this weekend.  Everything is working great now, thanks for the advice.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now