From owner-freebsd-smp@FreeBSD.ORG Wed Apr 5 10:44:20 2006 Return-Path: X-Original-To: freebsd-smp@freebsd.org Delivered-To: freebsd-smp@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 3A73016A400 for ; Wed, 5 Apr 2006 10:44:20 +0000 (UTC) (envelope-from markir@paradise.net.nz) Received: from linda-2.paradise.net.nz (bm-2a.paradise.net.nz [203.96.152.181]) by mx1.FreeBSD.org (Postfix) with ESMTP id ADBAC43D4C for ; Wed, 5 Apr 2006 10:44:19 +0000 (GMT) (envelope-from markir@paradise.net.nz) Received: from smtp-3.paradise.net.nz (tclsnelb1-src-1.paradise.net.nz [203.96.152.172]) by linda-2.paradise.net.nz (Paradise.net.nz) with ESMTP id <0IX8001LUWHT3C@linda-2.paradise.net.nz> for freebsd-smp@freebsd.org; Wed, 05 Apr 2006 22:44:18 +1200 (NZST) Received: from [192.168.1.11] (218-101-29-132.dsl.clear.net.nz [218.101.29.132]) by smtp-3.paradise.net.nz (Postfix) with ESMTP id 572B11B271E; Wed, 05 Apr 2006 22:44:17 +1200 (NZST) Date: Wed, 05 Apr 2006 22:44:16 +1200 From: Mark Kirkwood In-reply-to: <000a01c6588e$c3201da0$65fd24c0@Eric> To: Eric Message-id: <44339F80.3040704@paradise.net.nz> MIME-version: 1.0 Content-type: text/plain; format=flowed; charset=ISO-8859-1 Content-transfer-encoding: 7bit X-Accept-Language: en-us, en User-Agent: Mozilla Thunderbird 1.0.6 (X11/20051106) 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> Cc: Lucas Holt , freebsd-smp@freebsd.org Subject: Re: Low perf with smp X-BeenThere: freebsd-smp@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: FreeBSD SMP implementation group List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Wed, 05 Apr 2006 10:44:20 -0000 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