Thread: postgresql is slow with larger table even it is in RAM
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 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.
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 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.
On Tuesday 25 March 2008, 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 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. I can't really say anything about your RAM issue, but count(*) is always a bad idea if it can be avoided. Assuming you have a primary key on the table, do a count(pk). In my experience that alone cuts down up to 30% of execution time. Uwe
On Tue, Mar 25, 2008 at 2:11 PM, CZUCZY Gergely <gergely.czuczy@harmless.hu> wrote:
yes, i have both the index and primary key on a column "id",May I ask, do you have any indeces or a primary key on that table?
and am doing the
SELECT count(id) from TABLE;
this is taking 3 seconds
I can't really say anything about your RAM issue, but count(*) is always a bad
idea if it can be avoided. Assuming you have a primary key on the table, do a
count(pk). In my experience that alone cuts down up to 30% of execution time.
i want exact count so am doing this...
But as you are saying it is not doing so,
at first without primary key i had a column "id", it took the same 3 seconds to show the count, after reading some tips i created primary key on that "id", and i did select count(id) from table, and now also it is taking the same 3 seconds.
But as you are saying it is not doing so,
at first without primary key i had a column "id", it took the same 3 seconds to show the count, after reading some tips i created primary key on that "id", and i did select count(id) from table, and now also it is taking the same 3 seconds.
some more info....
/proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Celeron(R) CPU 2.66GHz
stepping : 1
cpu MHz : 2667.031
cache size : 256 KB
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm constant_tsc up pni monitor ds_cpl tm2 cid cx16 xtpr
bogomips : 5340.26
Is there any specifications, a table should not contain more than N number of records......
This count(id) am doing for experimentations, not only this query all the queries are taking much time...
Any idea on this ??? how to make queries to execute faster when the number of rows are nearly 50 lakh
/proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Celeron(R) CPU 2.66GHz
stepping : 1
cpu MHz : 2667.031
cache size : 256 KB
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm constant_tsc up pni monitor ds_cpl tm2 cid cx16 xtpr
bogomips : 5340.26
Is there any specifications, a table should not contain more than N number of records......
This count(id) am doing for experimentations, not only this query all the queries are taking much time...
Any idea on this ??? how to make queries to execute faster when the number of rows are nearly 50 lakh
Can you provide the output of explain analyze select count(id) from table; ? Uwe On Tuesday 25 March 2008, sathiya psql wrote: > On Tue, Mar 25, 2008 at 2:11 PM, CZUCZY Gergely > <gergely.czuczy@harmless.hu> > > wrote: > > May I ask, do you have any indeces or a primary key on that table? > > yes, i have both the index and primary key on a column "id", > and am doing the > > SELECT count(id) from TABLE; > > this is taking 3 seconds
May I ask, do you have any indeces or a primary key on that table? On Tue, 25 Mar 2008 14:06:25 +0530 "sathiya psql" <sathiya.psql@gmail.com> 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 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. -- Üdvölettel, Czuczy Gergely Harmless Digital Bt mailto: gergely.czuczy@harmless.hu Tel: +36-30-9702963
Attachment
Re: postgresql is slow with larger table even it is in RAM
From
"=?ISO-8859-1?Q?Elvis_Henr=EDquez?="
Date:
We could notice your processor is an Intel Celeron. They have a small cache, as you can see for yourself in your output.
But, what about postgresql.conf ? How many memory resources are being assigned in your configuration?
If you only have 1.25 GB of RAM and you're creating a tablespace in RAM which holds a table about 0.8 GB in size, and have created indexes in the same tablespace in RAM, you could be getting out of RAM. vmstat says you're not using swap, but if you leave the postgresql process only ~100 MB of RAM, it surely will have a bad performance.
Once the server is up and running, which is the output of free ?
Which tool are you using to retrieve data ? psql is pretty much faster than phpPgAdmin and this one is faster than pgAdmin.
Elvis E. Henríquez A.
On Wed, Mar 26, 2008 at 4:18 AM, sathiya psql <sathiya.psql@gmail.com> wrote:
yes, i have both the index and primary key on a column "id",On Tue, Mar 25, 2008 at 2:11 PM, CZUCZY Gergely <gergely.czuczy@harmless.hu> wrote:May I ask, do you have any indeces or a primary key on that table?
and am doing the
SELECT count(id) from TABLE;
this is taking 3 seconds