pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpostgresql is slow with larger table even it is in RAM
13+ messages / 8 participants
[nested] [flat]
* postgresql is slow with larger table even it is in RAM
@ 2008-03-25 08:35 sathiya psql <sathiya.psql@gmail.com>
0 siblings, 6 replies; 13+ messages in thread
From: sathiya psql @ 2008-03-25 08:35 UTC (permalink / raw)
To: pgsql-performance
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] 13+ messages in thread
* Re: postgresql is slow with larger table even it is in RAM
@ 2008-03-25 08:42 sathiya psql <sathiya.psql@gmail.com>
parent: sathiya psql <sathiya.psql@gmail.com>
5 siblings, 1 reply; 13+ messages in thread
From: sathiya psql @ 2008-03-25 08:42 UTC (permalink / raw)
To: jose javier parra sanchez <jojapasa@gmail.com>; +Cc: pgsql-performance
On Tue, Mar 25, 2008 at 2:09 PM, jose javier parra sanchez <
jojapasa@gmail.com> wrote:
> It's been said zillions of times on the maillist. Using a select
> count(*) in postgres is slow, and probably will be slow for a long
> time. So that function is not a good way to measure perfomance.
>
Yes, but if the data is in HDD then we can say this...
but now the data is in RAM
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgresql is slow with larger table even it is in RAM
@ 2008-03-25 09:08 hubert depesz lubaczewski <depesz@depesz.com>
parent: sathiya psql <sathiya.psql@gmail.com>
5 siblings, 1 reply; 13+ messages in thread
From: hubert depesz lubaczewski @ 2008-03-25 09:08 UTC (permalink / raw)
To: sathiya psql <sathiya.psql@gmail.com>; +Cc: pgsql-performance
On Tue, Mar 25, 2008 at 02:05:20PM +0530, sathiya psql wrote:
> Any Idea on this ???
yes. dont use count(*).
if you want whole-table row count, use triggers to store the count.
it will be slow. regeardless of whether it's in ram or on hdd.
depesz
--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgresql is slow with larger table even it is in RAM
@ 2008-03-25 09:34 Craig Ringer <craig@postnewspapers.com.au>
parent: hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 0 replies; 13+ messages in thread
From: Craig Ringer @ 2008-03-25 09:34 UTC (permalink / raw)
To: depesz@depesz.com; +Cc: sathiya psql <sathiya.psql@gmail.com>; pgsql-performance
hubert depesz lubaczewski wrote:
> On Tue, Mar 25, 2008 at 02:05:20PM +0530, sathiya psql wrote:
>> Any Idea on this ???
>
> yes. dont use count(*).
>
> if you want whole-table row count, use triggers to store the count.
>
> it will be slow. regeardless of whether it's in ram or on hdd.
In other words, if you're having performance problems please provide
EXPLAIN ANALYZE output from a more useful query that does real work,
rather than something like count(*).
COUNT(*) can be slow due to some MVCC limitations; it's been discussed
frequently here so you should search the archives for information.
--
Craig Ringer
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgresql is slow with larger table even it is in RAM
@ 2008-03-25 11:59 Alvaro Herrera <alvherre@commandprompt.com>
parent: sathiya psql <sathiya.psql@gmail.com>
5 siblings, 0 replies; 13+ messages in thread
From: Alvaro Herrera @ 2008-03-25 11:59 UTC (permalink / raw)
To: sathiya psql <sathiya.psql@gmail.com>; +Cc: pgsql-performance
sathiya psql escribió:
> 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.
It has to scan every page and examine visibility for every record. Even
if there's no I/O involved, there's a lot of work to do. I am not sure
if with your hardware it is expected for it to take 3 seconds though.
Do you see high CPU usage during that period?
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgresql is slow with larger table even it is in RAM
@ 2008-03-25 13:10 Bill Moran <wmoran@collaborativefusion.com>
parent: sathiya psql <sathiya.psql@gmail.com>
5 siblings, 1 reply; 13+ messages in thread
From: Bill Moran @ 2008-03-25 13:10 UTC (permalink / raw)
To: sathiya psql <sathiya.psql@gmail.com>; +Cc: pgsql-performance
In response to "sathiya psql" <sathiya.psql@gmail.com>:
> 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 ???
Yes. It takes your hardware about 3 seconds to read through 700M of ram.
Keep in mind that you're not just reading RAM. You're pushing system
requests through the VFS layer of your operating system, which is treating
the RAM like a disk (with cylinder groups and inodes and blocks, etc) so
you have all that processing overhead as well. What filesystem did you
format the RAM disk with?
Why are you doing this? If you have enough RAM to store the table, why
not just allocate it to shared buffers?
--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/
wmoran@collaborativefusion.com
Phone: 412-422-3463x4023
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgresql is slow with larger table even it is in RAM
@ 2008-03-25 13:27 sathiya psql <sathiya.psql@gmail.com>
parent: Bill Moran <wmoran@collaborativefusion.com>
0 siblings, 1 reply; 13+ messages in thread
From: sathiya psql @ 2008-03-25 13:27 UTC (permalink / raw)
To: Bill Moran <wmoran@collaborativefusion.com>; +Cc: pgsql-performance
>
> Yes. It takes your hardware about 3 seconds to read through 700M of ram.
>
>
> Keep in mind that you're not just reading RAM. You're pushing system
> requests through the VFS layer of your operating system, which is treating
> the RAM like a disk (with cylinder groups and inodes and blocks, etc) so
> you have all that processing overhead as well. What filesystem did you
> format the RAM disk with?
>
tmpfs
>
> Why are you doing this? If you have enough RAM to store the table, why
> not just allocate it to shared buffers?
just allocating will make read from hdd to RAM at first time, to eliminate
that
>
>
are you saying it will take 3 seconds surely if i have 50 lakh record
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgresql is slow with larger table even it is in RAM
@ 2008-03-25 14:03 Bill Moran <wmoran@collaborativefusion.com>
parent: sathiya psql <sathiya.psql@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Bill Moran @ 2008-03-25 14:03 UTC (permalink / raw)
To: sathiya psql <sathiya.psql@gmail.com>; +Cc: pgsql-performance
In response to "sathiya psql" <sathiya.psql@gmail.com>:
> >
> > Yes. It takes your hardware about 3 seconds to read through 700M of ram.
> >
> >
> > Keep in mind that you're not just reading RAM. You're pushing system
> > requests through the VFS layer of your operating system, which is treating
> > the RAM like a disk (with cylinder groups and inodes and blocks, etc) so
> > you have all that processing overhead as well. What filesystem did you
> > format the RAM disk with?
>
> tmpfs
I'm not an expert, but according to wikipedia:
"tmpfs (previously known as shmfs) distinguishes itself from the Linux ramdisk device by allocating memory dynamically and by allowing less-used pages to be moved onto swap space."
Both dynamically allocating and swapping are potential problems, but I
don't know how to tell you to determine if they're issues or not.
> > Why are you doing this? If you have enough RAM to store the table, why
> > not just allocate it to shared buffers?
>
> just allocating will make read from hdd to RAM at first time, to eliminate
> that
PostgreSQL is still going to copy the data from your RAM disk into shared
buffers before working with it, so you still have that overhead. All
you're escaping is the time involved in physical disk activity, which is
what shared_buffers are designed to avoid.
> are you saying it will take 3 seconds surely if i have 50 lakh record
No. That is dependent on your hardware and other factors.
You are trying to use the system in a non-standard configuration. If it
doesn't work that way, don't be surprised.
Also, what are you expectations? Honestly, I don't see any problems with
the results you're getting, they're about what I would expect. Are you
trying to compare PostgreSQL to MySQL/MyISAM? More directly, what is
your purpose in starting this email conversation? What are you hoping
to accomplish?
--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/
wmoran@collaborativefusion.com
Phone: 412-422-3463x4023
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgresql is slow with larger table even it is in RAM
@ 2008-03-25 14:46 Luke Lonergan <llonergan@greenplum.com>
parent: sathiya psql <sathiya.psql@gmail.com>
5 siblings, 1 reply; 13+ messages in thread
From: Luke Lonergan @ 2008-03-25 14:46 UTC (permalink / raw)
To: sathiya psql <sathiya.psql@gmail.com>; pgsql-performance
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 ³SELECT COUNT(*)² to run at a maximum of about
350 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" <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.
>
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgresql is slow with larger table even it is in RAM
@ 2008-03-25 15:02 Chris Browne <cbbrowne@acm.org>
parent: sathiya psql <sathiya.psql@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Chris Browne @ 2008-03-25 15:02 UTC (permalink / raw)
To: pgsql-performance
sathiya.psql@gmail.com ("sathiya psql") writes:
> On Tue, Mar 25, 2008 at 2:09 PM, jose javier parra sanchez <jojapasa@gmail.com> wrote:
>
>
> It's been said zillions of times on the maillist. Using a select
> count(*) in postgres is slow, and probably will be slow for a long
> time. So that function is not a good way to measure perfomance.
>
>
>
> Yes, but if the data is in HDD then we can say this...
> but now the data is in RAM
Even if the data all is in RAM, it will still take some definitely
non-zero time to examine all of the pages, looking for tuples, and
then to determine which of those tuples are visible from the
perspective of your DB connection.
If 500MB of relevant data is sitting on disk, then it will take
whatever time it takes to pull it from disk; if it is in memory, there
is still work to be done...
--
select 'cbbrowne' || '@' || 'cbbrowne.com';
http://linuxdatabases.info/info/finances.html
Rules of the Evil Overlord #76. "If the hero runs up to my roof, I
will not run up after him and struggle with him in an attempt to push
him over the edge. I will also not engage him at the edge of a
cliff. (In the middle of a rope-bridge over a river of molten lava is
not even worth considering.)" <http://www.eviloverlord.com/;
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgresql is slow with larger table even it is in RAM
@ 2008-03-25 15:11 sathiya psql <sathiya.psql@gmail.com>
parent: Luke Lonergan <llonergan@greenplum.com>
0 siblings, 0 replies; 13+ messages in thread
From: sathiya psql @ 2008-03-25 15:11 UTC (permalink / raw)
To: Luke Lonergan <llonergan@greenplum.com>; +Cc: pgsql-performance
>
>
> 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 – 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.
>
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" <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.
>
>
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgresql is slow with larger table even it is in RAM
@ 2008-03-26 23:48 Peter Koczan <pjkoczan@gmail.com>
parent: sathiya psql <sathiya.psql@gmail.com>
5 siblings, 1 reply; 13+ messages in thread
From: Peter Koczan @ 2008-03-26 23:48 UTC (permalink / raw)
To: sathiya psql <sathiya.psql@gmail.com>; +Cc: pgsql-performance
On Tue, Mar 25, 2008 at 3:35 AM, 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.
Two things:
- Are you VACUUM'ing regularly? It could be that you have a lot of
dead rows and the table is spread out over a lot of pages of mostly
dead space. That would cause *very* slow seq scans.
- What is your shared_buffers set to? If it's really low then postgres
could be constantly swapping from ram-disk to memory. Not much would
be cached, and performance would suffer.
FWIW, I did a select count(*) on a table with just over 300000 rows,
and it only took 0.28 sec.
Peter
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgresql is slow with larger table even it is in RAM
@ 2008-03-27 17:32 Luke Lonergan <llonergan@greenplum.com>
parent: Peter Koczan <pjkoczan@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Luke Lonergan @ 2008-03-27 17:32 UTC (permalink / raw)
To: Peter Koczan <pjkoczan@gmail.com>; sathiya psql <sathiya.psql@gmail.com>; +Cc: pgsql-performance
So your table is about 80 MB in size, or perhaps 120 MB if it fits in
shared_buffers. You can check it using ³SELECT
pg_size_pretty(pg_relation_size(Œmytable¹))²
- Luke
On 3/26/08 4:48 PM, "Peter Koczan" <pjkoczan@gmail.com> wrote:
> FWIW, I did a select count(*) on a table with just over 300000 rows,
> and it only took 0.28 sec.
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2008-03-27 17:32 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-03-25 08:35 postgresql is slow with larger table even it is in RAM sathiya psql <sathiya.psql@gmail.com>
2008-03-25 08:42 ` sathiya psql <sathiya.psql@gmail.com>
2008-03-25 15:02 ` Chris Browne <cbbrowne@acm.org>
2008-03-25 09:08 ` hubert depesz lubaczewski <depesz@depesz.com>
2008-03-25 09:34 ` Craig Ringer <craig@postnewspapers.com.au>
2008-03-25 11:59 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-03-25 13:10 ` Bill Moran <wmoran@collaborativefusion.com>
2008-03-25 13:27 ` sathiya psql <sathiya.psql@gmail.com>
2008-03-25 14:03 ` Bill Moran <wmoran@collaborativefusion.com>
2008-03-25 14:46 ` Luke Lonergan <llonergan@greenplum.com>
2008-03-25 15:11 ` sathiya psql <sathiya.psql@gmail.com>
2008-03-26 23:48 ` Peter Koczan <pjkoczan@gmail.com>
2008-03-27 17:32 ` Luke Lonergan <llonergan@greenplum.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