next »

[poschi]

hello,

i need help with speeding up my sql access.
when i do "status" in sql it shows: "Threads: 30  Questions: 719529  Slow queries: 45962  Opens: 32734  Flush tables: 1  Open tables: 64  Queries per second avg: 18.701"

i think thats not too much, but my serverload is at 25-50 when i start mysql, if mysql isnt running, the load is below 1, so the problem seems to be mysql. my sitegenerator uses 3 queries to the database, one to get the config for the site, like keyword, mainkeyword, show_ads..., and the other 2 queries are used to get 10-20 random links. therefor i first determine how much links are in the linktable ("SELECT COUNT(*) AS c FROM $linkdb") and then generate 10-20 random ids wich i select from the linkdatabase. all databases have an index over id.
i thought about caching the sql commands with memcache, but since the links should always be random on each request, it would'nt make much sense to cache the request.

i hope anyone can help me, cause im running out of ideas how to speed up my system and my server is fucking slow, even if its an amd64-3700 with 1gb ram.

-p

[thedarkness]

What query are you using to get your random links out of the db, specifically?

Oh, and welcome to the cache.

Cheers,
td

[poschi]

thanks for the welcome 

the 2 queries im using is:

$q = "SELECT COUNT(*) AS c FROM $linkdb";
$r = mysql_query($q);
$c = mysql_fetch_assoc($r);
$numsites = $c[c];

$query = "SELECT * FROM $linkdb where id = ";
for ($i=1; $i<rand(10,20); $i++) {
    $rnd = rand(1, $numsites);
    $query .= $rnd." or id = ";
}
$rnd = rand(1, $numsites);
$query .= $rnd." or id = $rnd;";

$result  = mysql_query($query);
while ($row = mysql_fetch_object($result)) {
    $mylinks .= "<li> $row->link </li>";
}
mysql_free_result($result);

[nutballs]

i know enough about mysql to translate and move stuff to and from msSQL, so take this with a grain.
you could just do it in 1 shot by using rand() in mysql. im not sure about the efficiency of it however, but i assume its gotta be better than a count(*). eg: select * from $linkdb order by rand() limit 10
OR
you might want to change to just a count(ID). count(*) has more overhead, even though your just counting, it would make sense for it to just count rows without caring whats in them, but oh no, its gotta load the whole enchilada... i think.

[poschi]

i know enough about mysql to translate and move stuff to and from msSQL, so take this with a grain.
you could just do it in 1 shot by using rand() in mysql. im not sure about the efficiency of it however, but i assume its gotta be better than a count(*). eg: select * from $linkdb order by rand() limit 10

the reason im using it the way i do is, that i've tested your way before and it was REALLY slow like this:
mysql> select id from links1 order by rand() limit 10;
.....
10 rows in set (11 min 41.71 sec)
 
the other select-statement is executed  in ~0.00 seconds, on a table with 10641557 entries and an index over id.

is there anything else i can improve, or should i live with that and just buy new servers? then again i think the box should be able to handle the load since it isnt that much.
can anyone comment on this from his own experience? how much req's/second can your mysqld's handle?

[m0nkeymafia]

Yes using rand on big datasets is very slow
I had the exact same problem

Try doing a count on all the rows
Just
SELECT COUNT(table_id) AS rowCount FROM table

Grab the info
use php to do a random number like so:

$finalRowId = 0
while ($finalRowId == 0)
{
//rand(0,$rowMax);
//grab the row / data
//if the row exists then set the row id to $finalRowId
}

The loop is there so IF it tries to pull a row that doesnt exist itll try again
Hope that makes sense, i missed out a bit of code but should be easy to peace together and importantly v fast.

[nutballs]

ah ok. didnt know if that was the case with MySQL. msSQL has little to no issue. so nevermind.

[m0nkeymafia]

yeah sorry im talking mysql

[thedarkness]

I had a very similar problem with a very slow query, what we did was this;

Just select the highest ID to give you the count,
then get a random number between 0 and that figure with a PHP function or whatever
then "SELECT * from Blah where id > random_number limit 10"
as all queries are on the ID filed they are all numeric and therefore orders of magnitude faster.

NOTE: your count number would have to be minus 10 of course to allow for the 10 rows you want to pull out.

HTH,
td