...

View Full Version : Resolved Script draining Disk I/O?



cosmicsea
11-06-2010, 02:22 PM
I have this function I am working on right here to show the last 5 indexed pages on my search engine and every time I implement it, My Disk I/O Usage goes up to 99%. Is it this code that is doing this? Is there something making it drain usage so much? I thought maybe it is looping or something.


<?php

$class = "evrow";
echo "
<table width='60%' align='center'>

</tr>
";
$num = '1';
$result=mysql_query("select title, url from ".$mysql_table_prefix."links order by Link_id");
if ($debug > '0') echo mysql_error();
while (($row=mysql_fetch_row($result)) && ($num <= '5')) {
if ($class =="evrow")
$word = $row[0];
$url = $row[1];
echo "<tr class='$class'>
<td><a href='".($url)."' target='rel' title='View search results in new window'>".($word)."</a></td>
</tr>
";
$num++;
echo "

";
}
?>

Fou-Lu
11-06-2010, 05:11 PM
I think the issue here might fall against your MysQL itself. Nothing here PHP wise should be causing an issue.
You are looping, but all you are doing is printing out the 5 most recent searches. I really doubt that either title or url are so large that its doing anything to cause issues. Looking at the query though is where I would put my money. What you are doing is selecting ALL records from the database, and then iterating just the 5 most recent. If the query result exceeds the size of memory allocated to mysql, I believe it then starts writing the results to files which would explain your high IO usage.
What you can try is skipping the use of the $num variables completely, and using a LIMIT 5 at the end of your query. MySQL supports the LIMIT keyword, and will only retrieve the most recent records. Link_ID sounds like a Primary Key to me, so you probably don't need to double check any type of key usage. If the IO is still a problem, check the structure of the table to make sure that link_id is at least an indexed field.

cosmicsea
11-07-2010, 06:43 AM
I think the issue here might fall against your MysQL itself. Nothing here PHP wise should be causing an issue.
You are looping, but all you are doing is printing out the 5 most recent searches. I really doubt that either title or url are so large that its doing anything to cause issues. Looking at the query though is where I would put my money. What you are doing is selecting ALL records from the database, and then iterating just the 5 most recent. If the query result exceeds the size of memory allocated to mysql, I believe it then starts writing the results to files which would explain your high IO usage.
What you can try is skipping the use of the $num variables completely, and using a LIMIT 5 at the end of your query. MySQL supports the LIMIT keyword, and will only retrieve the most recent records. Link_ID sounds like a Primary Key to me, so you probably don't need to double check any type of key usage. If the IO is still a problem, check the structure of the table to make sure that link_id is at least an indexed field.
Thank you, And sorry for such a late reply. I never knew about the LIMIT feature of mysql. I am using it right now and I changed my script and it seems to be doing the same thing "running at 99%". I don't know if it is my script or just because i have a database size of over 100,000 rows and I am grabbing the last 5 indexed pages which would show the page title and url. The page titles are generally kind of long like any page title would be. If I was to index the page on coding forum I am on it would show
CodingForums.com - Reply to Topic and be a clickable link to that url. Maybe doing this is hitting my db kinda hard but I do not understand how it could hit the db that hard when i can do search queries of 10,000 or more results several times and not be at 99%
Here is my updated script.


<?php

echo "
<table width='60%' align='center'>
</tr>";
$result=mysql_query("select title, url from ".$mysql_table_prefix."links order by Link_id LIMIT 5");
while ($row=mysql_fetch_row($result))
echo "<tr class='class'>
<td><a href='".($row[1])."' target='rel' title='View search results in new window'>".($row[0])."</a></td>
</tr>";
?>


Is this what you meant to do? If so I am not very sure where to go from here. I have tried several variations to get it to work correctly. Maybe I am not querying right or displaying rows right?

cosmicsea
11-07-2010, 06:48 AM
I just checked my usage and it is now at 13% now. Maybe the script is working now. I would still like to know if what I did was what you were talking about doing though, and I am also going to sit and watch for awhile and see if this acts up again.

cosmicsea
11-07-2010, 01:08 PM
actually the script doesnt even seem to update anything when using the second code.

cosmicsea
11-07-2010, 01:20 PM
I see that I just need to change my query a bit. This seemed to solve my problems.


<?php

echo "
<table width='60%' align='center'>
</tr>";
$result=mysql_query("select title, url from ".$mysql_table_prefix."links ORDER BY Link_id DESC LIMIT 5");
while ($row=mysql_fetch_row($result))
echo "<tr class='class'>
<td><a href='".($row[1])."' target='rel' title='View search results in new window'>".($row[0])."</a></td>
</tr>";
?>

cosmicsea
11-07-2010, 01:22 PM
Thanks for your help Fou-Lu.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum