Anyway to stop "exceeded the 'max_questions' resource&q

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Dale
Forum Contributor
Posts: 466
Joined: Fri Jun 21, 2002 5:57 pm
Location: Atherstone, Warks

Anyway to stop "exceeded the 'max_questions' resource&q

Post by Dale »

I am starting to recently recieve this error on one of my sites:
User 'dalehay1' has exceeded the 'max_questions' resource (current value: 50000)
I'm just wondering though how it can be avoided as I believe it means that more than 50,000 "actions" where sent to the database within an hour? (Correct me if i'm wrong).

I don't have access over changing that limit amount, so i'm just wondering is there anyway of now making it so heavy on the database?

I'm using the bog-standard stuff like:

Code: Select all

<?php
$conn = mysql_connect("","","");
?>

Code: Select all

<?php
$sql = "SELECT * FROM blah WHERE foo = '$boo' LIMIT 10";
$result = mysql_query($sql, $conn) or die(mysql_error());
?>
etc.. etc.. Maybe I need to change the way I do that type of stuff?? Any ideas?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Dale wrote:(current value: 50000)
3600 seconds per hours -> ~13/14 querries per second, every second.
Any idea which of your scripts might hit the database that hard?

You can replace all occurences of mysql_query() by mysql_my_query() and add

Code: Select all

function mysql_my_query($query, $link=null) {
  static $query_counter = 0;
  
  if ( is_null($query) ) {
    return $query_counter;
  }
  
  $query_counter += 1;
  if ( is_null($link) ) {
    return mysql_query($query);
  }
  else {
    return mysql_query($query, $link);
  }
}

function print_mysql_query_counter() {
	$n = mysql_my_query(null, null);
	$filename = dirname($_SERVER['SCRIPT_FILENAME']).'/mysql_debug.txt';
	$text = date('H:i:s Y-m-d') . ': '.$n.' querries '.' in '.$_SERVER['SCRIPT_FILENAME'];
	
	// echo '<div>Debug: ', $text, "</div>\n";
	error_log($text. "\n", 3, $filename);
}

register_shutdown_function('print_mysql_query_counter', null, null);
This will put a file mysql_debug.txt in the directory of the called script containing something like
09:35:46 2007-03-10: 0 querries in /path/to/file.php
09:35:47 2007-03-10: 10 querries in /path/to/file2.php
09:35:51 2007-03-10: 5 querries in /path/to/file1.php
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

did you even try googling this.

1. Your host has put in place a limit on the amount of resources

Your options

1. rewrite your application to issue less queries
2. Ask your host to increase this number
3. Move host
4. Do nothing and live with it
Dale
Forum Contributor
Posts: 466
Joined: Fri Jun 21, 2002 5:57 pm
Location: Atherstone, Warks

Post by Dale »

mikeq wrote:did you even try googling this.
Yes.
mikeq wrote:1. rewrite your application to issue less queries
Thank you Captain Obvious, but I was looking for some guidance on what to change to something else.


Cheers volka for your input. :) Also would using mysql_close() help at all?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

No, it's about the amount of querries. Not the number of concurrend connections.
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

Your welcome Seargeant Sarcastic.

But unfortunately the answers are that obvious, those are your 4 options. Unless someone has another way around the settings your host has imposed.

What other kind of guidance can you expect, we dont know what queries your application uses, how many there are, can they be cut down, only you can do that. Or take one of the other options
Post Reply