Date: Wed, 05 Apr 2006 22:44:16 +1200 From: Mark Kirkwood <markir@paradise.net.nz> To: Eric <ericd@free.fr> Cc: Lucas Holt <luke@foolishgames.com>, freebsd-smp@freebsd.org Subject: Re: Low perf with smp Message-ID: <44339F80.3040704@paradise.net.nz> In-Reply-To: <000a01c6588e$c3201da0$65fd24c0@Eric> References: <009301c65418$cc029f30$65fd24c0@Eric> <64659004-E643-4044-AAED-076E0CC91977@foolishgames.com> <01a901c65493$77ad2740$65fd24c0@Eric> <442DC9B6.2090200@paradise.net.nz> <442EAA0A.8030704@he.iki.fi> <442F0E1B.9090505@paradise.net.nz> <027f01c657f4$0d0a66a0$65fd24c0@Eric> <4432858F.90701@he.iki.fi> <008001c65825$f3c27700$65fd24c0@Eric> <44331024.7090807@paradise.net.nz> <000a01c6588e$c3201da0$65fd24c0@Eric>
next in thread | previous in thread | raw e-mail | index | archive | help
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
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?44339F80.3040704>