server load extremely high

jackson992 · #1 (**permalink**) 06-29-2004, 03:23 PM

Can anyone tell me why this would cause the server load to be so high?

Copy of script------------------------------------

<html>
<head>

<style type="text/css">

</style>

<title>Buy Fresh Flowers From Jack's Retail</title>

<meta name="description" content="We offer the finest fresh flowers at 1 Stop Florists">

<meta name="keywords" content="fresh flowers,1 Stop Florists">

</head>

<body topmargin="0" leftmargin="0">

<?php include ('./right.txt'); ?>

<h1 align=center>Buy Fresh Flowers From Jack's Retail</h1>

<?
$user="";
$host="";
$password="";
$database="";

$connection=mysql_connect($host,$user,$password)
or die ("couldn't connect to server");
$db=mysql_select_db($database,$connection);

$limit=24;
$query_count="SELECT distinct ProductName FROM 1stopflorists";
$result_count=mysql_query($query_count);
$totalrows=mysql_num_rows($result_count);
$page = $_GET['page'];

if(empty($page))
{
$page = 1;
}
$limitvalue = ($page * $limit) - $limit;
$query = "SELECT distinct ProductName,ProductImageURL FROM 1stopflorists order by ProductName LIMIT $limitvalue,$limit";
$result = mysql_query($query) or die("Error: " . mysql_error());

?>

<table width="100%" border="0" align="center" cellpadding="15" cellspacing="0">
<tr>

<?php
$prod_columns=4; // Alter this to change number of columns
$loop_count = 1;
while($row=mysql_fetch_array($result)){
?>

<td width="<?php print 100/$prod_columns; ?>%" valign="top">

<?
// extract($row);
$newvar=urlencode($row["ProductName"]);
?>

<center>

<a href="http://www.jacksretail.com/1stopflorists.php?interest=<? print $newvar ?>">[img]<?php print $row[ProductImageURL];?>[/img]
<? print $row[ProductName]; ?></a>
</center>
</td>

<?
if($loop_count == $prod_columns){
?>

</tr>
</table>
<hr>
<table width="100%" border="0" align="center" cellpadding="15" cellspacing="0">
<tr>

<?php
$loop_count = 1;
}else{
$loop_count++;
}
$count++; // Just for making the while statement work
}

?>

</tr>
</table>

<?
$limit=24;
if($page != 1){
$pageprev = $page-1;
}

echo("<a href=\"1stopfloristscategories.php?page=$pageprev\ ">Fresh Flowers Previous Page</a> | "); //removed $ in front of page

$numofpages = $totalrows / $limit;
for($i = 1; $i <= $numofpages; $i++){
if($i == $page)
echo(" ");
else
echo(""); //removed $ in front of page
}

if(($totalrows % $limit) != 0){
if($i == $page)
echo(" ");
else
echo("");
}

if(($totalrows - ($limit * $page)) > 0){
$pagenext = $page+ 1;
echo("<a href=\"1stopfloristscategories.php?page=$pagenext\ ">Fresh Flowers Next Page</a> "); //removed +1 after page
}else
echo(" NEXT PAGE".$limit);
mysql_free_result($result);
?>

<hr>

<p align="center"><?php include ('./bottom.php'); ?> </p>

<hr>

</body>
</html>

cyanide · #2 (**permalink**) 06-29-2004, 03:28 PM

How high is the server load? And, what are you running?
The only thing I spot is the mysql connect... if you are making that connection on every page multiply # of visitors...

jackson992 · #3 (**permalink**) 06-29-2004, 06:00 PM

Hi cyanide:

My host shut my site off so the server load must have at least been in the 30's. Iuse Php and Mysql thrugh phpadmin

I use this same script for all my datafeed pages with currently around 133,000 indexed in Google right now

cyanide · #4 (**permalink**) 06-29-2004, 08:24 PM

Quote:

Originally Posted by jackson992

My host shut my site off so the server load must have at least been in the 30's. Iuse Php and Mysql thrugh phpadmin

I use this same script for all my datafeed pages with currently around 133,000 indexed in Google right now

I would have shut you down too LOL
By the way, what domain are you runninng this script?

Basically, here's what's happening...
On every page you are making a connection to the database. Apache has a limit to the number of connections you can make server-wide. Each connection you open, uses up precious ram.

So, every time a page is accessed a connection is opened, but when the user leaves the page that connection is left open and idle, meanwhile a new connection is being opened on the next page.

Here's a couple things to try:
1. At the end of the script, put

Code:

mysql_close()

This will close the connection
2. In this code

Code:

$connection=mysql_pconnect($host,$user,$password)

The only difference is I added the p
In this case, you are using a persistent connection. On subsequent pages, the server will look for an existing connection before opening up a new one.
Before you try this second one, you'll have to see if your host supports persistant connections
You can contact them or create a new file and put

Code:

<? phpinfo() ?>

Save the page as phpinfo.php and upload to your root, now navigate to yourdomain.com/phpinfo.php
Do a search for mysql.allow_persistent - to the right of it should say on or off

mushroom · #5 (**permalink**) 06-29-2004, 10:07 PM

An other posible cause is your table stucture..... but with out seeing the whole picture can't tell.

At times it is nessary to rethink and rebuild your tables to improve the speed of your queries.

jackson992 · #6 (**permalink**) 06-30-2004, 03:41 PM

Hi all:

I s there a way to show you my table structure?

cyanide · #7 (**permalink**) 06-30-2004, 03:46 PM

Quote:

Originally Posted by mushroom

An other posible cause is your table stucture..... but with out seeing the whole picture can't tell.

At times it is nessary to rethink and rebuild your tables to improve the speed of your queries.

Well, it's possible..

I don't think it would cause this high of a server load though...

in any case, can we see a URL ?

I spent a considerable amount of time writing one of my posts as a possible solution... did you even bother to investigate that as a possiblity?

jackson992 · #8 (**permalink**) 06-30-2004, 05:59 PM

Hi Cyanide:

I did indeed and mentioned it to my host. However he says he doesn't have the persistant connection.

Here is a site that uses one of my scripts.

http://automotiveparts-accessories.c...categories.php

HardCoded · #9 (**permalink**) 07-03-2004, 06:52 PM

IMO, the problem, if there is one, is almost certainly do do with the length of time it takes the MySQL queries to run.

I'm not sure why you need the DISTINCT keyword in there, but since you have it, I think you need to make sure that there is an index on ProductName. If you don't need DISTINCT, you should get rid of it.

Also, instead of this:

Code:

$query_count="SELECT distinct ProductName FROM 1stopflorists"; 
$result_count=mysql_query($query_count); 
$totalrows=mysql_num_rows($result_count);

This is probably more efficient:

Code:

$query_count="SELECT COUNT(distinct ProductName) AS cnt FROM 1stopflorists"; 
$result_count=mysql_query($query_count);
$row = mysql_fetch_array($result_count, MYSQL_ASSOC);
$totalrows=$row['cnt'];

The reason this is better is that you don't have your whole table sitting (and not even used) in a resource.

And instead of this:

Code:

SELECT distinct ProductName,ProductImageURL FROM 1stopflorists order by ProductName LIMIT $limitvalue,$limit

try this:

Code:

SELECT ProductName,ProductImageURL FROM 1stopflorists GROUP BY ProductName ORDER BY ProductName LIMIT $limitvalue,$limit

Again, if you don't really need the DISTINCT or the GROUP BY then get rid of it, but if you really do, then make sure ProductName is indexed.

Database connections are closed automatically at the end of the script, so no difference with mysql_close(). However, you should probably still do it in case the script sometimes doesn't exit properly ...doubtful in this case because most of the hard work is done by the time you'd want to close it. And if $host is localhost, then mysql_pconnect won't matter much either, since the connection overhead is extremely small.

So how large ARE the tables that this runs on? Can you show us the table structure?

ronniethedodger · #10 (**permalink**) 07-09-2004, 05:39 PM

In order to analyze this effectively, rather than posting snippets of tables, he should provide us with a the schema of his database.

He mentioned he is using phpMyAdmin. Go into that and log into your database, then use the Export tab to dump the Schema for your database. Do not dump the Data too (Schema only).

Take that file and upload it to one of your Servers that is still up. We will be able to look at the database structure, key indices, and data types from that.

If that is not too revealing or it appears to be somewhat optimally layed out, then the next step is to provide us with the actual php files in a zip file. But first things first.

compusolver · #11 (**permalink**) 07-10-2004, 12:09 PM

I don't think that closing the connection will help because that is done automatically.

Have you tried mysql_pconnect()?
What is your max_connections setting?
Is one of your connection statements within a loop?

Quote:

(From manual:)
mysql_pconnect() acts very much like mysql_connect() with two major differences.

First, when connecting, the function would first try to find a (persistent) link that's already open with the same host, username and password. If one is found, an identifier for it will be returned instead of opening a new connection.

Second, the connection to the SQL server will not be closed when the execution of the script ends. Instead, the link will remain open for future use (mysql_close() will not close links established by mysql_pconnect()).

HardCoded · #12 (**permalink**) 07-13-2004, 02:33 AM

The difference between pconnect and connect is immeasurably small when the the db server is on the same machine as the webserver. I have tested this thoroughly. It's a reasonable thing to do, usually, when the db server is in a separate machine, but not otherwise. Even that is not a given. If you're on a shared server with lots of domains and lots of different databases being connected to, it can harm you rather than help or be neutral.

jackson992 · #13 (**permalink**) 07-13-2004, 11:41 PM

Hi all:

First of all I had no idea anyone replied since my last post because I didn't get an email like I usually do.

Question on showing you the tables. Do you need a user/pass to be able to see them? I can easily show you the url to the phpadmin page or I can try what Ron suggested.

# phpMyAdmin SQL Dump
# version 2.5.6
# http://www.phpmyadmin.net
#
# Host: localhost
# Generation Time: Jul 13, 2004 at 10:27 PM
# Server version: 4.0.18
# PHP Version: 4.2.3
#
# Database : `jacksret_wwwjackretail`
#

# --------------------------------------------------------

#
# Table structure for table `compusa`
#

CREATE TABLE `compusa` (
`ProductName` text NOT NULL,
`Description` text NOT NULL,
`Price` text NOT NULL,
`ProductURL` text NOT NULL,
`ProductImageURL` text NOT NULL,
`PrimaryCategory` text NOT NULL
) TYPE=MyISAM;

The reason I use Distinct is because I only want each thing to show up once. For instance if there are several products with the same product name but differnt sizes etc. I'm not familiar with the Group By command. What does adding that do? Not sure what is meant by creating an index

One final note. The schema I posted above is for a table with 8925 records. My max is usually around 60,000 products

ronniethedodger · #14 (**permalink**) 07-14-2004, 02:39 AM

That is why I wanted you to post the schema.

There are no indexed fields that I can see and all your data types are text. I can see a an integer and a currency data type that should be using, instead of the text data type -- the category and price fields. You also do not have a key index field for this table.

Another thing I noticed is that you have not put any limits on the the text field lengths. I am not sure what the default length is on those, but if you don't need the extra space for something like your description then you should limit the length in the database.

For every SQL statement you are running, you are doing so against 8000+ records using unindexed fields in the query and to top it off they are text comparisons that are being made. This would definitely have an effect on the server, and they were probably right for shutting it down.

Doing queries on straight text values is more server intensive than integer/currency values, plus they take up less storage space. Indexes are used to quickly find values that match the query (hence the name index -- quick lookup). One field I noticed missing from your schema was the product_id field. This is standard for uniquely identifying each row in your table with a Unique Value (usually set to autonumber).

I think you might want to read up on database structuring and at least get a decent grasp of how to build one. Search for some tutorials on what you are trying to accomplish and lift some code for it. I would start at MySQL's website which can provide you with that information and then cruise over to a place like Zend for some decent background on programming your code (or find a script on the web somewhere).

HardCoded · #15 (**permalink**) 07-14-2004, 08:33 AM

With the possible exception of Description, none of those fields needs to be text type.

For each field, run this query:
SELECT MAX(LENGTH(field_name)) FROM compusa

Call the result n.

Then change the field type to VARCHAR(n).

More importantly, get an index on ProductName. In PHPMyAdmin, in the table structure view, you'll see a column for indexes. Click the one next to ProductName.

Finally, take my advice above on how to get the table count.

jackson992 · #16 (**permalink**) 07-14-2004, 04:44 PM

Hi all:

First off thanks to everyone for their help. I went in today and tried what you guys suggested. Here is what I have for a schema now:

# phpMyAdmin SQL Dump
# version 2.5.6
# http://www.phpmyadmin.net
#
# Host: localhost
# Generation Time: Jul 14, 2004 at 03:29 PM
# Server version: 4.0.20
# PHP Version: 4.2.3
#
# Database : `jacksret_wwwjackretail`
#

# --------------------------------------------------------

#
# Table structure for table `fogdog`
#

CREATE TABLE `fogdog` (
`ProductName` varchar(107) NOT NULL default '',
`Description` text NOT NULL,
`ProductImageURL` varchar(77) NOT NULL default '',
`ProductURL` varchar(180) NOT NULL default '',
`PrimaryCategory` varchar(84) NOT NULL default '',
KEY `ProductName` (`ProductName`)
) TYPE=MyISAM;

I still have to look up how that COUNT works. I did create an index on the product name. The only thing I am not sure about is the id as I need all the info from the table not just the id?

ronniethedodger · #17 (**permalink**) 07-15-2004, 04:03 AM

Jack - You are going to want to create one more column. Call it product_id and make it an autonumber field. Make this field your primary key, and the ProductName just an index. I think (not sure) you will have to "drop" the key on the ProductName field first before you can add it to the product_id.

Xcalabers · #18 (**permalink**) 07-15-2004, 04:58 AM

Your host. You script looks OK to me. Yea you could add a primary key to the table which would improve query preformance but with only 8,000 and some records it wont really matter. While the DB is doing text compares again with only 8000 records it doesn't matter.

Relational database systems( Read mysql) is designed to handle large amounts of records and 8000 is nothing for it to query.

If it was my host giving me stress for such a small amount of CPU usage I would find a new host. I mean "HELLO" your paying these people for their CPU usage.

You might also try something like system("ps -uax") to see how much CPU power is being used

jackson992 · #19 (**permalink**) 07-15-2004, 04:29 PM

Hi Ron:

I've got that successfully added but am not sure how I do the query now. Do I just add the ProductID to the query as in:

$query = "SELECT distinct ProductName,ProductImageURL,ProductID FROM 1stopflorists order by ProductName LIMIT $limitvalue,$limit";

ronniethedodger · #20 (**permalink**) 07-15-2004, 06:37 PM

Yes. Or use the GROUP BY query as Hard Coded suggested. It just may work this time round.

You see by having an integral value being used as primary key for your tables, it enables any database engine to access rows in your tables quicker. That in combination with the reduction of your database size is going to be a drastic change on the load now.

Go for it!!! Crank that puppy up and take it for a test drive.

Another advantage to having the product_id field, is you can pass that as a parameter now in lieu of passing the ProductName which may contain offensive characters and cause your app to choke.