Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 5AE9F2E0052 for ; Tue, 25 Mar 2008 11:47:17 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 09460-06 for ; Tue, 25 Mar 2008 11:47:16 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from exprod6og104.obsmtp.com (exprod6og104.obsmtp.com [64.18.1.187]) by postgresql.org (Postfix) with SMTP id 481892E0051 for ; Tue, 25 Mar 2008 11:47:16 -0300 (ADT) Received: from source ([63.240.6.3]) (using TLSv1) by exprod6ob104.postini.com ([64.18.5.12]) with SMTP; Tue, 25 Mar 2008 07:47:13 PDT Received: from d01smtp03.Mi8.com ([172.16.1.24]) by Outbound02.Mi8.com with Microsoft SMTPSVC(6.0.3790.1830); Tue, 25 Mar 2008 10:49:31 -0400 Received: from MI8NYCMAIL08.Mi8.com ([172.16.1.164]) by d01smtp03.Mi8.com with Microsoft SMTPSVC(6.0.3790.1830); Tue, 25 Mar 2008 10:46:23 -0400 Received: from 69.181.101.67 ([69.181.101.67]) by MI8NYCMAIL08.Mi8.com ([172.16.1.190]) via Exchange Front-End Server mi8owa.mi8.com ([172.16.1.134]) with Microsoft Exchange Server HTTP-DAV ; Tue, 25 Mar 2008 14:48:20 +0000 User-Agent: Microsoft-Entourage/12.1.0.080305 Date: Tue, 25 Mar 2008 07:46:02 -0700 Subject: Re: postgresql is slow with larger table even it is in RAM From: Luke Lonergan To: sathiya psql , Message-ID: Thread-Topic: [PERFORM] postgresql is slow with larger table even it is in RAM Thread-Index: AciOU5eJIqxrL2BSRwu2sS6VmKN/4QAM1qmi In-Reply-To: Mime-version: 1.0 Content-type: multipart/alternative; boundary="B_3289275962_1709692" X-OriginalArrivalTime: 25 Mar 2008 14:46:23.0418 (UTC) FILETIME=[FEF351A0:01C88E86] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/478 X-Sequence-Number: 29432 > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3289275962_1709692 Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable Hello Sathiya, 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. 2nd: you should expect the =B3SELECT COUNT(*)=B2 to run at a maximum of about 350 =AD 600 MB/s (depending on PG version and CPU speed). It is CPU speed limited to that rate of counting rows no matter how fast your IO is. 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. - 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 M= B, > 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. >=20 > So now everything is in RAM, if i do a count(*) on this table it retu= rns > 327600 in 3 seconds, why it is taking 3 seconds ????? because am sure tha= t no > Disk I/O is happening. ( using vmstat i had confirmed, no disk I/O is > happening, swap is also not used ) >=20 > Any Idea on this ??? >=20 > I searched a lot in newsgroups ... can't find relevant things.... ( becau= se > everywhere they are speaking about disk access speed, here i don't want t= o > worry about disk access ) >=20 > If required i will give more information on this. >=20 >=20 >=20 --B_3289275962_1709692 Content-type: text/html; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable Re: [PERFORM] postgresql is slow with larger table even it is in RAM= Hello Sathiya,

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 ge= t memory speed.

2nd: you should expect the “SELECT COUNT(*)” to run at a maximu= m of about 350 – 600 MB/s (depending on PG version and CPU speed). &nb= sp;It is CPU speed limited to that rate of counting rows no matter how fast = your IO is.

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 que= ries can run against the table.

- 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 crea= ted 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 confirme= d, 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.... ( 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.



--B_3289275962_1709692--