Query limit per ip

Philwn · September 16, 2011

Im wondering if someone can shed some light on possibly the best way to do this.

I currently have a website which recieves 26000-37000 visits a month. The host got on my case about to many MySql connections and with help from someone here decided a cache system was best. the cache system is now in place and only updates if the cached file is older than 5 days. I am still recieving high connections to my database and wonder if it is spam as i have a search box. the search box once filled in redirects to a page which queries the database for the keyword and displays results and because it was just selecting and not inserting into database i didnt go for the captcha. this is why i suspect spam, I have thought about limiting the amount of search queries per time limit by ip but I am unsure of how to tackle this and if it will help against spam bots?

a google search has only found an answer which stores every visitors ip in a database and keeps count there which I cant see the benefit as it is creating more work for the DB. I have thought maybe creating a session variable that increments with every search and when searching checks this value but I am not to sure if this would help as it would be down to cookie settings.

can anyone shed some light on a possible solution to this.

I am not expecting you to write this for me but for now just to help with the best way to tackle the problem.

Thank you for your time.

WebStyles · September 16, 2011

you could store the IPS in a text file with a time stamp. something like

XXX.XXX.XXX.XXX - 123456789

on each line, and check against that. then set a cron job to run every night that will remove all expired lines from the file.

or

you could set a delay using javascript from the time the search box gets focus till the time it's allowed to be submitted (robots will submit much faster than it takes a user to type in stuff)

WebStyles · September 16, 2011

do you store the queries made on your site? (by the keywords entered you can probably figure out if it's real users or bots.)

Philwn · September 16, 2011

no i dont currently. I think I might set up a text file for it to dump all keywords into and i will be able to find out. I cant understand how it could be users tho with a cache system.

Philwn · September 16, 2011

ok I have set up a system so all searched terms are dumped in a text file. I will leave this set up for a few days and see if it is spam causing the connections.

WebStyles · September 16, 2011

Cool. I just thought it was something worth checking.... let me know what come up.

xyph · September 16, 2011

You host was on your case for MySQL connections, or slow MySQL queries?

40k hits a month is only an average of 55 hits an hour - let's say 500 hits/hour at peak time. That's still only around 8 hits/min.

If you have different stats, let me know. You aren't averaging anywhere near 1 hit/second which means it's more than likely some sort of inefficiency in your code. Considering each request should only open at most 1 MySQL connection, you either have several AJAX requests opening many SQL connection per hit, your script opens multiple SQL connections, or your host has things set up wrong and connections aren't expiring and being wiped when they should.

Please clear this up for me

Pikachu2000 · September 16, 2011

Or queries running in loops . . .

xyph · September 16, 2011

Well, that falls into the inefficient code bit I mentioned. He said the host was on his case about MySQL connections numbers, not on slow or too many queries. Queries in a loop shouldn't cause too many connections.

Correct me if I'm wrong

Pikachu2000 · September 16, 2011

Just trying to cover all the bases, and queries in loops is one of the most common missteps we see around here. Usually preceded by "my host shut down my website, but I don't know why!!!!!"

Philwn · September 21, 2011

Ok it wasnt spam, they have shut it down again today and they say it is because of range.php and too many processes not connections, code pasted below:

<?php session_start();
   $cat = $_GET["cat"];
   $sub_cat = $_GET["sub"];
   $language = $_GET["lang"];
$cachefile = "cache/".$cat."-".$sub_cat."-".$language.".html";


      $cachetime = 60 * 60 * 96; // 5 days


      // Serve from the cache if it is younger than $cachetime

      if (file_exists($cachefile) && (time() - $cachetime
         < filemtime($cachefile))) 
      {

         include($cachefile);


         echo "<!-- Cached ".date('jS F Y H:i', filemtime($cachefile))." -->";


         exit;

      }

      ob_start(); // start the output buffer
include("./includes/db_con.inc.php");
$page_title = $cat . " - " . $sub_cat;
$sql_meta = mysql_query("SELECT * FROM sub_cat WHERE name='$sub_cat'");
$row_meta = mysql_fetch_assoc($sql_meta);
$meta_desc = $row_meta['META_DESC'];
$meta_key = $row_meta['META_KEY'];
include("./includes/head.php"); 
include("./includes/header.php");
  ?>

<div id="main-page-header">
<?php 
$desc_sql = mysql_query("SELECT * FROM sub_cat WHERE name='$sub_cat'");
$row_desc = mysql_fetch_assoc($desc_sql);
$description = $row_desc['description'];
echo "<img src='$img_loc/product-pages/sub-cat-head/".str_replace(" ", "-", $row_desc['name'])."-sub-cat-head.jpg' width='940' height='310' />";

?>
</div>

  <div id="page-content">
  
    <div id="main-page-text">
    
    <h1><?php echo $sub_cat; ?></h1>
<h2 class="crumbs"><a href="<?php echo "$url/Products/$lang/"; ?>">Products</a> > <a href="<?php echo "$url/Category/$lang/".urlencode($cat)."/"; ?>"><?php echo $cat; ?></a> > <?php echo $sub_cat; ?></h2>
<p>
<?php echo $description; ?>
</p>

  <script src="http://connect.facebook.net/en_US/all.js#xfbml=1"></script><fb:like layout="button_count" show_faces="false" width="170" font="verdana"></fb:like> 
  <div id="related-wrap">
  
  <h2><?php echo $sub_cat; ?> Products</h2>
    
    <?php
$range_products = mysql_query("SELECT * FROM $table WHERE CATEGORY='$cat' AND SUB_CATEGORY='$sub_cat' AND display='1' ORDER BY sortID ASC");
while($row_range = mysql_fetch_array($range_products))
    { 
$prodid = str_replace("/", "-", $row_range['PRODID']);
$cat_link = urlencode($row_range['CATEGORY']);
$sub_cat_link = urlencode($row_range['SUB_CATEGORY']);
echo "<div class='related-products'>\n<a class='related-img' href='$url/Product/$lang/$cat_link/$sub_cat_link/$prodid/'>\n<img src='$img_loc/product-pages/Range-thmb/$prodid.jpg' width='120' height='120' /></a>\n";
    echo "<h5>" . $row_range['PROD_TITLE'] . "</h5>\n";
    echo "</div>\n";
}
?>

    </div>    
    
     <div id="FAQ-wrap"><h2>Frequently Asked Questions</h2>
  <?php
  $QA_table = $lang . "_qanda";
  if ($id=="") {
      $list_QA = mysql_query("SELECT * FROM $QA_table WHERE SUB_CATEGORY='$sub_cat' AND DISPLAY='1'");
  }
  else {
  $list_QA = mysql_query("SELECT * FROM $QA_table WHERE PRODID='$id' AND DISPLAY='1'");
	}
  while($row_QA = mysql_fetch_array($list_QA))
{ 
  echo "<div id='FAQ-QA'>";
  echo "<h2 class='FAQ-question'>Q: ".$row_QA['QUESTION']."</h2>";
     echo "<p class='FAQ-question'>A: ".$row_QA['ANSWER']."</p></div>";
}
  ?> 
    <div id="FAQ-question">
        <h2 class="white">Got a question about the 
        
        <span class="got-question-product"><?php echo $sub_cat; ?></span> 
        
        </h2>    
      </div>
      
      </div>

    </div>    
    <div id="totem-menu-container"><?php include("./includes/search.php") ?>
    
    <?php include("./includes/totem.php") ?>
    </div>
  </div>
  </div>  
<?php include("./footer.php") ?>

<?php
       // open the cache file for writing
       $fp = fopen($cachefile, 'w'); 


       // save the contents of output buffer to the file
    fwrite($fp, ob_get_contents());

	// close the file

        fclose($fp); 

	// Send the output to the browser
        ob_end_flush(); 
?>

Philwn · September 21, 2011

i have now added mysql_free_result after each query has finished will this help?

WebStyles · September 21, 2011

A few comments...

line 30

$sql_meta = mysql_query("SELECT * FROM sub_cat WHERE name='$sub_cat'");

is the same as line 40. So you're basically pulling out the same information twice (unnecessarily).

select * is almost always a bad idea.

How are you connecting to the mysql server? are you using persistent connections?

consider opening the connection, retrieving data and then closing it...

do you also have connections in head, header, totem and footer? (all the included files)

Philwn · September 21, 2011

the database connection is opened at the start of eveyr page, the connection is then closed with mysql_close($con) in the included footer.php

JonnoTheDev · September 21, 2011

I would also suggest moving away from using MySQL to search, using LIKE, MATCH, etc, bad, bad, bad if you have a large database. I would recommend a proper search index such as Lucene or Sphinx.

If you have a website with that sort of traffic, do you make any revenue? If so I would invest in a dedicated server and tell your host where to go. If your code is inefficient at least you can fix it without someone pulling your service.

Philwn · September 21, 2011

on this page the sub cat has a possible 65 results (65 records in database) and another page has 365 records in a database so I would not of thought these are massive?

The website is generally a catalogue of products for a manufacturer it does not earn any revenue.

JonnoTheDev · September 21, 2011

Ok that's fine. I thought that you were talking thousands of records.

xyph · September 21, 2011

Too many processes? Is this shared hosting? Sounds like the host has MySQL timeouts set too high. This really doesn't sound like something you've done, unless you're using persistent connections.

At max, how many hits are you getting per minute?

Even with 20 queries, the number of processes should be the same, they'll just momentarily take up more resources. All of these queries seem simple enough (millisecond execution with < 1000 rows), but without seeing the includes I can't tell if you have something bogging down .

See if your host will set up a slow queries log to help you see if it's your application or their set up that's really bogging things down.

Worse comes to worse, move hosts.

Philwn · September 22, 2011

Thanks for taking the time to read the code. I have pasted below each of the includes if you would like to see them but they are similar queries to the main page content.

the error log on the host is returning alot of problems with my cache system saying "supplied argument is not a valid stream resource" for the fwrite and fclose functions. However the cache system works? would these be leaving processes open as they say?

head.php

<?php
include_once $_SERVER[ 'DOCUMENT_ROOT' ].'/twatch/api/LogRequest.php';
twatchLogRequest();
  $temp_url = (!empty($_SERVER['HTTPS'])) ? "https://".$_SERVER['SERVER_NAME'].$_SERVER['REQUEST_URI'] : "http://".$_SERVER['SERVER_NAME'].$_SERVER['REQUEST_URI'];
  $url = "http://www.myurl.co.uk";
$img_loc = $url . "/img";
  $lang = $_GET["lang"];
  if ($lang=="") {
  $lang = "En";
  }
  if ($lang=="En")
  $table = "english";
  elseif ($lang=="Fr")
  $table = "french";
  elseif ($lang=="Du")
  $table = "dutch";
  else
  $table = "english";
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">

<head>

<meta http-equiv="content-type" content="text/html; charset=UTF-8" />
<meta http-equiv="imagetoolbar" content="no" />
<title><?php echo $page_title; ?> My Site</title>
<meta name="descripton" content="<?php echo $meta_desc; ?>" />
<meta name="keywords" content="<?php echo $meta_key; ?>" />    

<link rel="apple-touch-icon" href="iphone-icon.png"/>
<link rel="image_src" href="http://www.myurl.co.uk/image.jpg" />
<link rel="shortcut icon" type="image/x-icon" href="/favicon.ico"/>
<link rel="stylesheet" href="<?php echo $url; ?>/css/screen.css" type="text/css" media="screen" />
<link rel="stylesheet" href="<?php echo $url; ?>/css/print.css" type="text/css" media="print" />
<link rel="stylesheet" href="assets/stylesheet.css" type="text/css" charset="utf-8" />
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>

<meta property="fb:admins" content="152042004842362" />

<script>
	!window.jQuery && document.write('<script src="jquery-1.4.3.min.js"><\/script>');
</script>
<script type="text/javascript" src="<?php echo $url; ?>/fancybox/jquery.mousewheel-3.0.4.pack.js"></script>
<script type="text/javascript" src="<?php echo $url; ?>/fancybox/jquery.fancybox-1.3.4.pack.js"></script>
<link rel="stylesheet" type="text/css" href="<?php echo $url; ?>/fancybox/jquery.fancybox-1.3.4.css" media="screen" />
<script type="text/javascript">
	$(document).ready(function() {

$("a.youtube").click(function() {
$.fancybox({
'padding' : 0, //optional
'autoScale' : false,
'transitionIn': 'none',
'transitionOut': 'none',
'title': this.title,
'width': 680, //or whatever
'height': 495, //or whatever
'href': this.href.replace(new RegExp("watch\\?v=", "i"), 'v/'),
'type': 'swf',
'swf': {
'wmode': 'opaque',
'allowfullscreen' : 'true'
}
});
return false;
});


		$("a.fuglybox").fancybox({
			'titlePosition'		: 'outside',
			'overlayColor'		: '#000',
			'overlayOpacity'	: 0.9
		});


		$("a[rel=example_group]").fancybox({
			'transitionIn'		: 'none',
			'transitionOut'		: 'none',
			'titlePosition' 	: 'over',
			'titleFormat'		: function(title, currentArray, currentIndex, currentOpts) {
				return '<span id="fancybox-title-over">Image ' + (currentIndex + 1) + ' / ' + currentArray.length + (title.length ? '   ' + title : '') + '</span>';
			}
		});


		/*
		*   Examples - various
		*/

		$("#various1").fancybox({
			'titlePosition'		: 'inside',
			'transitionIn'		: 'none',
			'transitionOut'		: 'none'
		});

		$("#various2").fancybox();

		$(".iframe").fancybox({
			'width'				: 480,
			'height'			: 700,
			'autoScale'			: false,
			'transitionIn'		: 'fade',
			'transitionOut'		: 'fade',
			'type'				: 'iframe'

		});



	});



</script>
<script src="<?php echo $url; ?>/js/gen_validatorv4.js" type="text/javascript"></script>


</head>

<body>


<div class="wrapper"> <!-- Begin WRAPPER DIV -->

header.php

<div id="head-container">

</div>
    
<div id="menu">

  <div class="nav-logo"><a href="<?php echo $url . "/Home/$lang/"; ?>"><img src="<?php echo $img_loc; ?>/nav-img/Logo-218x73.png" width="218" height="73" /></a></div>

  <div id="lang-select"></div>
  
				<div id="menuh-container">
                    		<div id="menu1-wrap">
                            	<ul class="menu1">
                            	<li><a href="<?php echo $url . "/Home/$lang/"; ?>" class="top_parent"><b>Home</b></a></li>
                            	<li><a href="<?php echo $url . "/About/$lang/"; ?>"><b>About us</b></a></li>
                            	<li><a href="<?php echo $url . "/News/$lang/1/"; ?>"><b>News</b></a></li>
                            	
                            	
                                <li><a href="<?php echo $url . "/Articles/$lang/1/"; ?>"><b>Articles</b></a></li>
                                <li><a href="<?php echo $url . "/Products/$lang/"; ?>"><b>Products</b></a></li>
                                <li><a href="<?php echo $url . "/Dealers/$lang/"; ?>"><b>Dealers</b></a></li>
                                <li><a href="<?php echo $url . "/Contact/$lang/"; ?>"><b>Contact Us</b></a></li>
                                <li><a href="<?php echo $url . "/Videos/$lang/1/"; ?>"><b>Videos</b></a></li>
                                </ul>
                                
                                                               
                                
                            </div> 
                                <div id="nave-search">
                  <form method="get" action="<?php echo $url; ?>/Search_Results.php">
          <input class="navIn" type="text" id="search" name="search" />
           <input class="navGo" name="submit" type="submit" value="GO"  /> 
		</form>
                   </div>      
				</div> 	 
                
                </div>
                
                  <div id="menu2-wrap">
                                   <div class="menu2">
                                   <ul class="menu2">             
					 <?php 
						   $sub_menu_head = mysql_query("SELECT * FROM category WHERE DISPLAY='1' ORDER BY display_order ASC");
						   		while($sub_menu_row = mysql_fetch_array($sub_menu_head))
								{
								$link = $url . "/Category/" . $lang . "/" . urlencode($sub_menu_row['CATEGORY']) . "/";
								 echo "<li><a href='$link'><b>" . $sub_menu_row['CATEGORY'] . "</b></a></li>\n";
								}
							mysql_free_result($sub_menu_head);
						   ?>
                               
                  				</ul>
                  </div>
                  
    

</div>

Totem.php

      <div id="totem-banner-ads">
<?php
$ad_sql = mysql_query("SELECT * FROM totem_ads ORDER BY RAND() LIMIT 1");
while($row_advert = mysql_fetch_array($ad_sql))
{
echo "<a href='$url/" . $row_advert['link'] . "'><img src='$img_loc/banner-ads/" . $row_advert['image'] . "' alt='" . $alt_text['image'] . "'></a>";
}
?>  
</div>

  <div class="totem-button"><a href="<?php echo $url . "/Home/$lang/"; ?>"><img src="<?php echo $img_loc; ?>/main-pages/totem/1-button.jpg" alt="Find out about our products" width="285" height="45" /></a></div>
<div class="totem-button"><a href="<?php echo $url . "/News/$lang/1/"; ?>"><img src="<?php echo $img_loc; ?>/main-pages/totem/2-news.jpg" alt="The latest news and products" width="285" height="45" /></a></div>
    <div class="totem-button"><a href="<?php echo $url . "/Dealers/$lang/"; ?>"><img src="<?php echo $img_loc; ?>/main-pages/totem/3-Dealers.jpg" alt="Find a Stockist near you" width="285" height="45" /></a></div>
    <div class="totem-button"><a href="<?php echo $url . "/Videos/$lang/1/"; ?>"><img src="<?php echo $img_loc; ?>/main-pages/totem/4-videos.jpg" alt="All the action and videos from our archives" width="285" height="45" /></a></div>
    <!-- <div class="totem-button"><a href="#"><img src="<?php echo $img_loc; ?>/main-pages/totem/5-Venues.jpg" alt="" width="285" height="45" /></a></div> -->
    <div class="totem-button"><a href="<?php echo $url . "/Articles/$lang/1/"; ?>"><img src="<?php echo $img_loc; ?>/main-pages/totem/6-Press-articles.jpg" alt="Read the Press articles" width="285" height="45" /></a></div>
    <div class="totem-button"><a href="<?php echo $url . "/Anglers/$lang/"; ?>"><img src="<?php echo $img_loc; ?>/main-pages/totem/7-meet.jpg" alt="Meet" width="285" height="45" /></a></div>
    <div class="totem-button"><a href="http://www.facebook.com/mysite" target="_blank"><img src="<?php echo $img_loc; ?>/main-pages/totem/8-facebook.jpg" alt="Find Us on Facebook" width="285" height="45" /></a></div>
    <div class="totem-button"><a target="_blank" href="http://www.affiliate.com/" target="_blank"><img src="<?php echo $img_loc; ?>/main-pages/totem/9-affiliate.jpg" alt="Link to affiliate" width="285" height="45" /></a></div>

</div>

and footer.php is just html and javascript other than mysql_close($con)

xyph · September 22, 2011

How big is the 'totem_ads' table?

Anything more than ~100 rows and ORDER BY RAND() will start slowing down.

All handles opened by fopen() should be closed when the script ends execution, so I don't see how that could cause a MySQL process to stay open.

Could we see your db_con file? Perhaps it's somehow being included multiple times.

You should set up your own server with a clone of your web site and see if there are processes being held open.

Philwn · September 26, 2011

thank you again,

the totem ads table is never more than 20 rows(adverts).

db_con.inc.php:

<?php

$DBName = "database";
$DBUser = "username";
$DBPassword = "pass";
$DBHost = "localhost";
$con = mysql_connect($DBHost,$DBUser,$DBPassword);
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db($DBName, $con);

?>

setting up a server to test is a good idea I shall try this.

Philwn · September 30, 2011

Ok so I set up a Wamp on my PC and found sever errors of unidentified variables which have now been solved, would these of caused the too many processes?

JonnoTheDev · September 30, 2011

no

xyph · September 30, 2011

Most likely not.

Sign In

Query limit per ip

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information