Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id E53462E0064 for ; Tue, 25 Mar 2008 12:11:56 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 80246-08 for ; Tue, 25 Mar 2008 12:11:49 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.233]) by postgresql.org (Postfix) with ESMTP id D11D12E003B for ; Tue, 25 Mar 2008 12:11:48 -0300 (ADT) Received: by wx-out-0506.google.com with SMTP id r21so3079888wxc.28 for ; Tue, 25 Mar 2008 08:11:48 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=yYSGBZvia+0wxT+OwT9caLJtbaX1FL0iCfL89EqO2oE=; b=Yik6J2EHzyAlNplXcalbQgZfaZWDOaKHL3NgqYm/nIfqgF+DauUu6qUiqxeSb/47qjN4Qs1Q1ALD53u0LrV7ONWIDktLgIiy6zzngWZ9OlWWB5/AcHvuCWE3+OAKTLa87SEJ/B70KdesKe5aJ4ies5Bd1ZTeP/wfashiOZ4WDMI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=oIwvOn1kUkY5+EjZ6jvrXTESMMTUZ326gOaiFXpgzLrMITkqKT3IEkRL/P8dVNfmIyAuSuXWeh/Jt9tI2KBG5mWL0NS5rWzojTTIgLWeOgNKrqh5di5c8kddl28m20MyKg1Mtf5o/PynOftEO32/osrSQGd1Z4/Z7g4hj9F7pj4= Received: by 10.100.33.11 with SMTP id g11mr22007687ang.62.1206457907628; Tue, 25 Mar 2008 08:11:47 -0700 (PDT) Received: by 10.100.105.20 with HTTP; Tue, 25 Mar 2008 08:11:47 -0700 (PDT) Message-ID: Date: Tue, 25 Mar 2008 20:41:47 +0530 From: "sathiya psql" To: "Luke Lonergan" Subject: Re: postgresql is slow with larger table even it is in RAM Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3924_25885252.1206457907622" References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200803/479 X-Sequence-Number: 29433 ------=_Part_3924_25885252.1206457907622 Content-Type: text/plain; charset=WINDOWS-1252 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline > > > 1st: you should not use a ramdisk for this, it will slow things down as > compared to simply having the table on disk. Scanning it the first time > when on disk will load it into the OS IO cache, after which you will get > memory speed. > absolutely.... after getting some replies, i dropped the table from ramdisk, and started to have that in the disk itself.. > > 2nd: you should expect the "SELECT COUNT(*)" to run at a maximum of about > 350 =96 600 MB/s (depending on PG version and CPU speed). It is CPU spee= d > limited to that rate of counting rows no matter how fast your IO is. > am using 8.1 pentium duo core > > So, for your 700 MB table, you should expect a COUNT(*) to run in about > 1-2 seconds best case. This will approximate the speed at which other > queries can run against the table. > ok count(*) per say, but other queries is taking much time... ok i ll do more experimentations and i ll be back.... Very great thanks for all of your replies GUYZ..... > > - Luke > > > > On 3/25/08 1:35 AM, "sathiya psql" wrote: > > Dear Friends, > I have a table with 32 lakh record in it. Table size is nearly 700 > MB, and my machine had a 1 GB + 256 MB RAM, i had created the table space= in > RAM, and then created this table in this RAM. > > So now everything is in RAM, if i do a count(*) on this table it > returns 327600 in 3 seconds, why it is taking 3 seconds ????? because am > sure that no Disk I/O is happening. ( using vmstat i had confirmed, no di= sk > I/O is happening, swap is also not used ) > > Any Idea on this ??? > > I searched a lot in newsgroups ... can't find relevant things.... ( > because everywhere they are speaking about disk access speed, here i don'= t > want to worry about disk access ) > > If required i will give more information on this. > > > > ------=_Part_3924_25885252.1206457907622 Content-Type: text/html; charset=WINDOWS-1252 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline

1st:  you should not use a ramdisk for this, it will slow things down = as compared to simply having the table on disk.  Scanning it the first= time when on disk will load it into the OS IO cache, after which you will = get memory speed.
absolutely....

after ge= tting  some replies, i dropped the table from ramdisk,
and started = to have that in the disk itself..

2nd: you should expect the "SELECT COUNT(*)" to run at a maximum of about 3= 50 =96 600 MB/s (depending on PG version and CPU speed).  It is CPU sp= eed limited to that rate of counting rows no matter how fast your IO is.
am using 8.1
pentium duo co= re

So, for your 700 MB table, you should expect a COUNT(*) to run in about 1-2= seconds best case.  This will approximate the speed at which other qu= eries can run against the table.
ok count(*) per say, bu= t other queries is taking much time...

ok i ll do more experimentati= ons and i ll be back....


Very great thanks for all of your repli= es GUYZ.....

- Luke



On 3/25/08 1:35 AM, "sathiya psql" <sathiya.psql@gmail.com> wrote:

Dear Friends,
     I have a table with 32 lakh record in it. Tab= le size is nearly 700 MB, and my machine had a 1 GB + 256 MB RAM, i had cre= ated the table space in RAM, and then created this table in this RAM.

    So now everything is in RAM, if i do a count(*) on = this table it returns 327600 in 3 seconds, why it is taking 3 seconds ?????= because am sure that no Disk I/O is happening. ( using vmstat i had confir= med, no disk I/O is happening, swap is also not used )

Any Idea on this ???

I searched a lot in newsgroups ... can't find relevant things.... ( bec= ause everywhere they are speaking about disk access speed, here i don't= want to worry about disk access )

If required i will give more information on this.




------=_Part_3924_25885252.1206457907622--