Low perf with smp

Mark Kirkwood markir at paradise.net.nz
Wed Apr 5 10:44:20 UTC 2006


Eric wrote:
> Main problem is that query is done in a database of more than 2Go and it 
> will be difficult to mail it. ;=)
> I try with several versions of mysql and php from mysql 4.1.18 to last 
> 5.x and php 4.4.0 to 5.x and don't find significant change in performance.
> If it can help here is the loop.
> 
> for($debut=$deb;$debut < $fin;$debut +=  $range){
>    $end = $debut + $range;
>    $query = "select avg(total)   from `histo_perfs_x`  where id1 = 
> '".$id1."' and id2 = '".addslashes($id2)."' and date > '".$debut."' and 
> date < '".$end."'";
>    $result = mysql_query($query) or die("La requete :$query a echouee.");
>    if(!$result)$databar_total[$j][$i]= 0;
>    else {
>      $ligne = mysql_fetch_array($result);
>      if($ligne[0] != "")$databar_total[$j][$i]=$ligne[0]; else 
> $databar_total[$j][$i]= "-";
>    }
> }
> 

Excellent! Well yes, 2G of data is too big to mail! However, generating 
2G of synthetic test data is no problem at all.

However to do this, a little more information is needed:

1/ A create statement for the table histo_perfs_x, along with its 
indexes, explicitly specifying if using an engine other than myisam.

2/ An estimate of proportion of the histo_perfs_x table a query like:

select avg(total) from histo_perfs_x
where id1 = ? and id2 = ? and date > ? and date < ?;

is likely to scan (for typical values of the 4 bind variables).

3/ (If possible) the output of explain of the above query so we can 
duplicate the access plan.

4/ The number of iterations you were using in the php loop for your test.

Cheers

Mark




More information about the freebsd-smp mailing list