Solved

MySQL PHP Query Delays

Posted on 2011-09-23
3
313 Views
Last Modified: 2012-05-12
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
Comment
Question by:compsol1993
3 Comments
 
LVL 11

Expert Comment

by:maeltar
ID: 36587365
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
 
LVL 8

Accepted Solution

by:
wolfgang_93 earned 500 total points
ID: 36590150
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
 

Author Closing Comment

by:compsol1993
ID: 36599140
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
updating table data with inner join 9 24
datetime in sql 6 23
WP_Query multiple custom taxonomies 5 24
PHP get array item with custom id 4 21
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now