The Cache: Technology Expert's Forum
 
*
Welcome, Guest. Please login or register. September 17, 2019, 05:55:24 PM

Login with username, password and session length


Pages: [1]
  Print  
Author Topic: Database Optimization  (Read 4435 times)
poschi
n00b
*
Offline Offline

Posts: 3


View Profile
« on: May 13, 2007, 04:42:27 AM »

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
Logged

No links in signatures please
thedarkness
Lifer
*****
Offline Offline

Posts: 585



View Profile
« Reply #1 on: May 13, 2007, 05:03:59 AM »

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

Oh, and welcome to the cache.

Cheers,
td
Logged

"I want to be the guy my dog thinks I am."
 - Unknown
poschi
n00b
*
Offline Offline

Posts: 3


View Profile
« Reply #2 on: May 13, 2007, 05:08:36 AM »

thanks for the welcome  Wink

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);
Logged

No links in signatures please
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #3 on: May 13, 2007, 07:52:26 AM »

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.

Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
poschi
n00b
*
Offline Offline

Posts: 3


View Profile
« Reply #4 on: May 13, 2007, 10:14:17 AM »

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?
Logged

No links in signatures please
m0nkeymafia
Expert
****
Offline Offline

Posts: 240


Check it!


View Profile
« Reply #5 on: May 13, 2007, 10:17:31 AM »

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.
Logged

I am Tyler Durden
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #6 on: May 13, 2007, 10:24:45 AM »

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

I could eat a bowl of Alphabet Soup and shit a better argument than that.
m0nkeymafia
Expert
****
Offline Offline

Posts: 240


Check it!


View Profile
« Reply #7 on: May 13, 2007, 01:22:00 PM »

yeah sorry im talking mysql
Logged

I am Tyler Durden
thedarkness
Lifer
*****
Offline Offline

Posts: 585



View Profile
« Reply #8 on: May 13, 2007, 06:25:49 PM »

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
Logged

"I want to be the guy my dog thinks I am."
 - Unknown
Pages: [1]
  Print  
 
Jump to:  

Perkiset's Place Home   Best of The Cache   phpMyIDE: MySQL Stored Procedures, Functions & Triggers
Politics @ Perkiset's   Pinkhat's Perspective   
cache
mart
coder
programmers
ajax
php
javascript
Powered by MySQL Powered by PHP Powered by SMF 1.1.2 | SMF © 2006-2007, Simple Machines LLC
Seo4Smf v0.2 © Webmaster's Talks


Valid XHTML 1.0! Valid CSS!