MySQL perf. probs on SIMPLE stuff

Jaxson

Hey all. I was previously a cold-fusion developer, and now I am brand new to PHP. This is also the first time I set up the the web server myself. I am running Apache, MySQL, and PHP off a new Win2000Pro machine (due to company politics).

This is my problem: Anything written in html or non-database interactive php loads instantly, as expected. This means any static HTML page, or a php-imbedded page that catches variables from a form and displays them. These are lighting fast- just as soon as I hit the submit button the next page loads.

HOWEVER, any page that interacts with the mySQL database takes MUCH LONGER. I mean, I hit submit and wait like 2 or 3 seconds before the results page to load. I'm not talking about complicated joins or huge inserts. Just the MOST BASIC, simple queries like looking for a single record based on ID number, or updating a single record. An example of my query is below:

<?php
mysql_connect (localhost, webuser, webpassx);
mysql_select_db (test);

$selectresult = mysql_query ("
SELECT * FROM JUNKIT
WHERE FNAME LIKE '$_POST[fname]'

");

What am I doing wrong? Did I put the database in a non-optimal location? Am I not connecting to the database correctly? Please help.

Thanks-
J

Sxooter

Are you indexing those fields? If you've got a large data set and the field you're ordering by or whereing by isn't indexed the database has to sequentially scan every row, which can be quite slow.

Jaxson

I'm not indexing the fields- but this is just a dummy database table. it has only a tiny dataset in it - only has a handful of fake records and 5 fields. it seems to me that ANY query on that table should be ultra fast.

I can't figure out why my queries run sooo slow. 2-3 seconds running from the local machine.

Sxooter

Originally posted by Jaxson
I'm not indexing the fields- but this is just a dummy database table. it has only a tiny dataset in it - only has a handful of fake records and 5 fields. it seems to me that ANY query on that table should be ultra fast.

I can't figure out why my queries run sooo slow. 2-3 seconds running from the local machine.

Hmmm. Maybe you should post a bit of code. Are you doing something like connecting / disconnecting to the database on every query or using a lot of dbselect statements or something?

Maybe it's just MySQL taking a while to setup a connection each time or something.

Have you tried profiling your code to see where the big slowdown is?

Jaxson

I timed the page below. From the instant I hit 'submit,' the results page takes a full FIVE SECONDS to appear.

Here is the full code of my test page:

<?php
mysql_connect (localhost, webuser, webpassx);
mysql_select_db (test);
$selectresult = mysql_query ("
SELECT * FROM JUNKIT
WHERE FNAME = '$_POST[fname]'");
?>

<?php $recordcount = mysql_numrows($selectresult); ?>

Your search found <?php echo "$recordcount" ?> results.

As you can see, I have it making a connection right before the query, but still- this seems to be taking too long regardless. Again- the database table only contains a few test records with very few fields- it's not searching anything extensive. Again- any static HTML pages or PHP pages that receive form posts and display them work ultra fast - this is all on the local machine. I don't understand why hitting the database causes so much slowdown.

Sxooter

Try adding this to your code:

function mtime(){
$a = microtime();
$a = split(" ",$a);
$time = $a[1]+$a[0];
return $time;
}

Then stick in a line like this between each mysql statement:

$a[] = mtime();

Then print out at the end:

print implode(" : ",$a);

And see where the longest wait is.

Jaxson

Sxooter- I tried adding your code. Here is what my resulting script looks like:

<?php

function mtime(){
$a = microtime();
$a = split(" ",$a);
$time = $a[1]+$a[0];
return $time;
}

mysql_connect (localhost, webuser, webpassx);
$a[] = mtime();

mysql_select_db (test);
$a[] = mtime();

$selectresult = mysql_query ("
SELECT * FROM JUNKIT
WHERE FNAME = '$_POST[fname]'
");
$a[] = mtime();

$recordcount = mysql_numrows($selectresult);
$a[] = mtime();

print implode(" : ",$a);

?>

<P>Your search found <?php echo "$recordcount" ?> results.

The page outputs this:

1039129074.74 : 1039129074.74 : 1039129074.74 : 1039129074.74

Your search found 2 results.

Did I implement your code incorrectly? It just seems to be repeating the same time 4 times, instead of showing the time at each step.

Sxooter

Originally posted by Jaxson
Sxooter- I tried adding your code. Here is what my resulting script looks like:

-------------------------------------------------

<?php

function mtime(){
$a = microtime();
$a = split(" ",$a);
$time = $a[1]+$a[0];
return $time;
}

mysql_connect (localhost, webuser, webpassx);
$a[] = mtime();

Try putting one of the $a[]=mtime() calls before the mysql_connect to see how long that takes. It may be the connect time is what is taking so long.

Jaxson

thanks for your help so far- I added the function call before the mysql_connect. now the output is this:

1039134779.53 : 1039134780.72 : 1039134780.72 : 1039134780.72 : 1039134780.72 : 1039134780.72

So it looks like it is taking about 1.2 seconds to connect to the database. Is that normal?

But the page still takes a full 5+ seconds before appearing. There's absolutely nothing on the page except a recordcount. What's going on the other 3.8 seconds??

nemonoman

FWIW, I have apache, PHP and MySQL loaded on my 166khz Dell Laptop, which is where I do my developing. It can take 4-5 seconds to manage even a very simple form->PHP->DB->PHP->HTML page sequence.

I have very bad phone line and connect to the internet @+/- 36kbps. The same query used using my server's DB, PHP etc., will go 2x or 3x times faster. Sometimes better.

FWIW as well: The first time I use a form, particularly after writing new PHP code, it takes 2-3x longer to work than the next time it's used. I think there is probably some compiling and caching being done in the bkgd. probably with both MySQL and PHP.

But I'd love to hear from someone with knowledge, and not only opinions.

Sxooter

Originally posted by Jaxson
thanks for your help so far- I added the function call before the mysql_connect. now the output is this:

1039134779.53 : 1039134780.72 : 1039134780.72 : 1039134780.72 : 1039134780.72 : 1039134780.72

So it looks like it is taking about 1.2 seconds to connect to the database. Is that normal?

But the page still takes a full 5+ seconds before appearing. There's absolutely nothing on the page except a recordcount. What's going on the other 3.8 seconds??

Not a clue bro. I run Linux/Apache/PHP/Postgresql (LAPP) and my response times are much better.

It may be something to do with how it's running on Windows, versus how it runs on Unix. Got a spare P233 sitting around you can throw Linux on when nobody's looking?

After all, it's easier to ask forgiveness than to ask permission.

Jaxson

yeah- I know apache isn't quite as fast running on top of windows, but I'm pretty sure it's not an apache thing. all my static html pages load instantly. the machine is a dell 1.8ghz, which seems plenty fast. it's just when php hits the mysql database that I get this 5 second delay.

has anyone else running apache/php/mysql on top of Win2000Pro had performance problems? I COULD try to get a linux server going, but iti would be better if I could just fix a configuration issue with my current setup.

thanks-
J

Sxooter

What I'm saying is that the problem may be a performance issue caused by running MySQL on top of Windows, and it may or may not be fixable.

Way back when I first started running apache on NT before switching to Linux, I had lots of little issues like this pop up, and every time I tried to figure out what was causing them, it seemed to come down to the fact that Windows just doesn't seem fast unless it's running software made for it's multi-thread / single process design.

I.e. it may not be a config issue, it may just be windows.

But, if you set it up for testing on linux and get the same performance problem, then that helps you prove that it is a configuration issue.

sem_db

I have the same problem but on my development machine. I am running Windows 2000 with php and mysql and I have one query with a large WHERE clause and this select statement causes everthing to hang for several minutes. It disturbing because there is no error messages. I can only guess its something wrong with my table structure! oh no.

J.