pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpostgresql is slow with larger table even it is in RAM
8+ messages / 4 participants
[nested] [flat]
* postgresql is slow with larger table even it is in RAM
@ 2008-03-25 08:36 sathiya psql <sathiya.psql@gmail.com>
2008-03-25 08:41 ` Re: postgresql is slow with larger table even it is in RAM CZUCZY Gergely <gergely.czuczy@harmless.hu>
2008-03-25 08:42 ` Re: postgresql is slow with larger table even it is in RAM Uwe C. Schroeder <uwe@oss4u.com>
0 siblings, 2 replies; 8+ messages in thread
From: sathiya psql @ 2008-03-25 08:36 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: postgresql is slow with larger table even it is in RAM
2008-03-25 08:36 postgresql is slow with larger table even it is in RAM sathiya psql <sathiya.psql@gmail.com>
@ 2008-03-25 08:41 ` CZUCZY Gergely <gergely.czuczy@harmless.hu>
2008-03-25 08:48 ` Re: postgresql is slow with larger table even it is in RAM sathiya psql <sathiya.psql@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: CZUCZY Gergely @ 2008-03-25 08:41 UTC (permalink / raw)
To: sathiya psql <sathiya.psql@gmail.com>; +Cc: pgsql-admin
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
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: postgresql is slow with larger table even it is in RAM
2008-03-25 08:36 postgresql is slow with larger table even it is in RAM sathiya psql <sathiya.psql@gmail.com>
2008-03-25 08:41 ` Re: postgresql is slow with larger table even it is in RAM CZUCZY Gergely <gergely.czuczy@harmless.hu>
@ 2008-03-25 08:48 ` sathiya psql <sathiya.psql@gmail.com>
2008-03-25 17:55 ` Re: postgresql is slow with larger table even it is in RAM Uwe C. Schroeder <uwe@oss4u.com>
2008-03-27 10:35 ` Re: postgresql is slow with larger table even it is in RAM Elvis Henríquez <henriquez.elvis@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: sathiya psql @ 2008-03-25 08:48 UTC (permalink / raw)
To: CZUCZY Gergely <gergely.czuczy@harmless.hu>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: postgresql is slow with larger table even it is in RAM
2008-03-25 08:36 postgresql is slow with larger table even it is in RAM sathiya psql <sathiya.psql@gmail.com>
2008-03-25 08:41 ` Re: postgresql is slow with larger table even it is in RAM CZUCZY Gergely <gergely.czuczy@harmless.hu>
2008-03-25 08:48 ` Re: postgresql is slow with larger table even it is in RAM sathiya psql <sathiya.psql@gmail.com>
@ 2008-03-25 17:55 ` Uwe C. Schroeder <uwe@oss4u.com>
1 sibling, 0 replies; 8+ messages in thread
From: Uwe C. Schroeder @ 2008-03-25 17:55 UTC (permalink / raw)
To: pgsql-admin; +Cc: sathiya psql <sathiya.psql@gmail.com>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: postgresql is slow with larger table even it is in RAM
2008-03-25 08:36 postgresql is slow with larger table even it is in RAM sathiya psql <sathiya.psql@gmail.com>
2008-03-25 08:41 ` Re: postgresql is slow with larger table even it is in RAM CZUCZY Gergely <gergely.czuczy@harmless.hu>
2008-03-25 08:48 ` Re: postgresql is slow with larger table even it is in RAM sathiya psql <sathiya.psql@gmail.com>
@ 2008-03-27 10:35 ` Elvis Henríquez <henriquez.elvis@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Elvis Henríquez @ 2008-03-27 10:35 UTC (permalink / raw)
To: sathiya psql <sathiya.psql@gmail.com>; +Cc: pgsql-admin
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:
>
>
> 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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: postgresql is slow with larger table even it is in RAM
2008-03-25 08:36 postgresql is slow with larger table even it is in RAM sathiya psql <sathiya.psql@gmail.com>
@ 2008-03-25 08:42 ` Uwe C. Schroeder <uwe@oss4u.com>
2008-03-25 08:50 ` Re: postgresql is slow with larger table even it is in RAM sathiya psql <sathiya.psql@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Uwe C. Schroeder @ 2008-03-25 08:42 UTC (permalink / raw)
To: pgsql-admin; +Cc: sathiya psql <sathiya.psql@gmail.com>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: postgresql is slow with larger table even it is in RAM
2008-03-25 08:36 postgresql is slow with larger table even it is in RAM sathiya psql <sathiya.psql@gmail.com>
2008-03-25 08:42 ` Re: postgresql is slow with larger table even it is in RAM Uwe C. Schroeder <uwe@oss4u.com>
@ 2008-03-25 08:50 ` sathiya psql <sathiya.psql@gmail.com>
2008-03-25 09:27 ` Re: postgresql is slow with larger table even it is in RAM sathiya psql <sathiya.psql@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: sathiya psql @ 2008-03-25 08:50 UTC (permalink / raw)
To: Uwe C. Schroeder <uwe@oss4u.com>; +Cc: pgsql-admin
>
> 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: postgresql is slow with larger table even it is in RAM
2008-03-25 08:36 postgresql is slow with larger table even it is in RAM sathiya psql <sathiya.psql@gmail.com>
2008-03-25 08:42 ` Re: postgresql is slow with larger table even it is in RAM Uwe C. Schroeder <uwe@oss4u.com>
2008-03-25 08:50 ` Re: postgresql is slow with larger table even it is in RAM sathiya psql <sathiya.psql@gmail.com>
@ 2008-03-25 09:27 ` sathiya psql <sathiya.psql@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: sathiya psql @ 2008-03-25 09:27 UTC (permalink / raw)
To: Uwe C. Schroeder <uwe@oss4u.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2008-03-27 10:35 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-03-25 08:36 postgresql is slow with larger table even it is in RAM sathiya psql <sathiya.psql@gmail.com>
2008-03-25 08:41 ` CZUCZY Gergely <gergely.czuczy@harmless.hu>
2008-03-25 08:48 ` sathiya psql <sathiya.psql@gmail.com>
2008-03-25 17:55 ` Uwe C. Schroeder <uwe@oss4u.com>
2008-03-27 10:35 ` Elvis Henríquez <henriquez.elvis@gmail.com>
2008-03-25 08:42 ` Uwe C. Schroeder <uwe@oss4u.com>
2008-03-25 08:50 ` sathiya psql <sathiya.psql@gmail.com>
2008-03-25 09:27 ` sathiya psql <sathiya.psql@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox