MySQL Optimization & Odd Connectivity Issues

[Jacklyn]

I apologize in advance if this is slightly confusing or lacking information.  It seems to be a complicated situation and after what has been far too long I am at the end of my rope in need of some assistance to try and get the problem sorted out.  Before I get started, I have dedicated web hosting where I do not have root access to know the exact values of the settings in MySQL's config.  I am, however, running the latest stable version of everything (PHP, MySQL, Apache, etc.).

 

First, a little back story.  I operate a virtual horse racing simulation game using PHP and MySQL (http://www.equination.net).  Several months ago my users and I started to experience a great deal of slowness with the game.  I rewrote the entire website a couple of times to help improve and optimize my queries and did so with success.  After a few more months and some tweaks made by my host to cache sizes, things were running fantastically.

 

July 1st marked the first day of a "New Year" and the server load increased significantly.  From that day on, the website has been at an absolute crawl with numerous errors popping up all over the place.  My host has continued to try tweaking the MySQL config, trying a big query cache, which provided some improvements for a short period of time but then tailed off into more slowness.  They are currently testing a small query cache along with other tweaks to see how it goes.  The queries seem to be failing to utilize the MySQL query cache efficiently.  I also believe they have tried disabling query cache and running with just memcached to see how the performance would be.

 

Presently, the general speed of the website is still relatively slow, though not quite as bad.  However, the major issue I am running into right now seems to be an odd connectivity issue.  My pages typically work by first loading a login script, which connects to the database and verifies login information.  It then includes the header of the page (which successfully displays the user information), followed by the body of the page and then the footer.  The body of the page, however, is continuously providing me with these types of errors:

 

Warning: mysql_numrows() expects parameter 1 to be resource, boolean given in...

 

A couple of refreshes to the page, and the query will then successfully grab the information it's supposed to.  In the meantime, I can open PHPMyAdmin and successfully perform the same query without any problems.

 

I am quite the newbie when it comes to this type of thing and I'm not really sure where to go from here.  I'm sure it will be a matter of testing new things here and there, but I'm hoping I might be able to get some magical answer as to what may be wrong here so I can resolve the problem as soon as possible.  My customers and I are very unhappy with how things have been and I'm simply at the end of my rope with dealing with this situation.

[PFMaBiSmAd]

The php Warning: error you are getting (most likely) means that a query has failed due to an error (possibly a mysql connection problem, i.e. too many connections or connection has gone away...) and your code (most likely) has no error checking, error reporting/logging, and error recovery logic in it to get it to test for errors, report/log the errors that are occurring in the application, and take an appropriate action (don't execute follow-on code that is dependent on the previous step executing without errors.)

 

You could also have an error in your logic that is causing the problem. However, without any error checking, error reporting/logging logic in your code to get your code to tell you what problems are occurring and where they are occurring at, the actual cause is just a guess.

 

All applications should always test each mysql_connect, mysql_select_db, and mysql_query statement for errors and log the mysql_error information and in the case of a mysql_query, log the actual query statement.

 

If you use trigger_error as part of the error reporting/logging logic, php can (assuming that you have error_reporting set to E_ALL, log_errors set to ON, and error_log is setup with a log file name to write to) write the information to a log file, which includes the file and line number (of the trigger_error statement) where the error is occurring at.

 

Typical logic for a SELECT/SHOW query -

 

	$query = "SELECT * FROM your_table WHERE your_where_conditions...";
if(!$result = mysql_query($query)){
	// query failed, handle the error here...
	$errors[] = "A fatal error occurred and this page is non-functional at this time!"; // setup a user error message
	trigger_error("Query failed: $query<br /> Due to: " . mysql_error()); // generate an application error message
} else {
	// query worked, check if it matched any rows
	if(!mysql_num_rows($result)){
		// no matching rows
		$main_content .= "There is no matching information...(for whatever the query was trying to do!)"; // use your own appropriate wording ...
	} else {
		// query matched at least one row, use the results from the query here...
		$row = mysql_fetch_assoc($result); // fetch one row or use a loop to iterate over all the rows
		// use the data that the query matched here...
	}
}
// code having nothing to do with the success or failure of the above query goes here...

 

If you set the error_reporting/log_errors/error_log settings as indicated, the trigger_error statement will log the $query and the mysql_error that is occurring. If you set display_errors to ON, you will get more immediate feedback because the information will also be output to the browser.

[Jacklyn]

Thank you for the information.  I will see what I can do about error logging, though the only error I have been receiving thus far is that warning and nothing related to the mysql_connect or select_server itself.

 

The thing that baffles me is that the individual page connects to the database initially to ensure the user is logged in and then grabs the users information from the database.  If there was a connectivity issue, it would arise there, but there doesn't appear to be a connection issue.  The page will successfully pull the user's initial information from the database after verifying their login information.  However, later in the body of the page suddenly it seems to be having issues, and there is no change in the connection to the database.

 

The query itself works fine, it just seems like it is being temperamental about when it decides to work.  Is it possible for the connection to randomly die part-way through a page loading? 

 

I'm also wondering why I never seem to receive any errors with the initial part of the page at all with the amount of problems the server seems to be having lately.  I would assume that if, 9 times out of ten I'm receiving the warnings and the queries are failing with MySQL that the login portion itself would be failing from time to time, too.

 

It just seems incredibly odd that I'm suddenly having this problem.  This is probably way out there, as I'm not 100% familiar with how this aspect works, but could the cache be drawing the errors somehow and then continuously putting those errors out since they are simply what's in the cache?

[Jacklyn]

Well, don't I feel like a complete idiot!  Thank you very much for your assistance, as it made me pay a lot closer attention to what was happening on my page with the connection issues.  It appears I was requesting a second MySQL connection on the same page, which was then failing and causing the query fails.  Removing the second mysql_select_db() has resolved the problem entirely. 

[Jacklyn]

And for my millionth reply in a row, apparently it resolved the issue for myself for a short while but has since started doing the same thing again even with the second database connection function removed. 

 

I'm completely baffled as to why part of the page seems to experience errors on the exact same connection that works for the other part of the page just fine.