pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMemory and performance
8+ messages / 6 participants
[nested] [flat]
* Memory and performance
@ 2001-04-04 17:15 edipoelder@ig.com.br
2001-04-04 20:34 ` Re: Memory and performance Richard Huxton <dev@archonet.com>
2001-04-05 01:53 ` Re: Memory and performance Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 8+ messages in thread
From: edipoelder@ig.com.br @ 2001-04-04 17:15 UTC (permalink / raw)
To: pgsql-sql
Hi all,
I have noted that Postgresql don't make a good memory handle. I have
made the tables/procedure (in attached file) and run it as "select bench(10,
5000)". This will give a 50000 records inserts (5 x 10000). (well, I run it
on a P200+64MB of RAM, under Linux, and Postgres 7.0.2. In a more powerfull
machine, you can try other values).
I get as result, the following times:
id | objname | benchtime
----+---------+-----------
1 | group 1 | 00:00:32
2 | group 2 | 00:00:47
3 | group 3 | 00:01:13
4 | group 4 | 00:01:41
5 | group 5 | 00:02:08
(5 rows)
Note that, with memory increse, the system becomes slow, even if the
system has free memory to alocate (yes, 64MB is enough to this test). I
didn't see the source code (yet), but I think that the data estructure used
to keep the changed records is a kind of chained list; and to insert a new
item, you have to walk to the end of this list. Can it be otimized?
The system that I'm developing, I have about 25000 (persons) x 8 (exams)
x 15 (answers per exam) = 3000000 records to process and it is VERY SLOW.
thanks,
Edipo Elder
[edipoelder@ig.com.br]
_________________________________________________________
Oi! Você quer um iG-mail gratuito?
Então clique aqui: http://www.ig.com.br/paginas/assineigmail.html
Attachments:
teste.zip (application/octet-stream, 620B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Memory and performance
2001-04-04 17:15 Memory and performance edipoelder@ig.com.br
@ 2001-04-04 20:34 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 8+ messages in thread
From: Richard Huxton @ 2001-04-04 20:34 UTC (permalink / raw)
To: edipoelder@ig.com.br; +Cc: pgsql-sql
edipoelder@ig.com.br wrote:
>
> Hi all,
>
> I have noted that Postgresql don't make a good memory handle. I have
> made the tables/procedure (in attached file) and run it as "select bench(10,
> 5000)". This will give a 50000 records inserts (5 x 10000). (well, I run it
> on a P200+64MB of RAM, under Linux, and Postgres 7.0.2. In a more powerfull
> machine, you can try other values).
That's 50,000 inserts in one transaction - have you tried 50
transactions of 1000 inserts?
> I get as result, the following times:
> 5 | group 5 | 00:02:08
>
> Note that, with memory increse, the system becomes slow, even if the
> system has free memory to alocate (yes, 64MB is enough to this test). I
> didn't see the source code (yet), but I think that the data estructure used
> to keep the changed records is a kind of chained list; and to insert a new
> item, you have to walk to the end of this list. Can it be otimized?
I don't fancy your chances before 7.1 ;-)
> The system that I'm developing, I have about 25000 (persons) x 8 (exams)
> x 15 (answers per exam) = 3000000 records to process and it is VERY SLOW.
If you need to import large quantities of data, look at the copy
command, that tends to be faster.
- Richard Huxton
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Memory and performance
2001-04-04 17:15 Memory and performance edipoelder@ig.com.br
@ 2001-04-05 01:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-04-05 12:52 ` Re: Memory and performance Tim Perdue <tim@perdue.net>
1 sibling, 1 reply; 8+ messages in thread
From: Tom Lane @ 2001-04-05 01:53 UTC (permalink / raw)
To: edipoelder@ig.com.br; +Cc: pgsql-sql
edipoelder@ig.com.br writes:
> I have noted that Postgresql don't make a good memory handle. I have
> made the tables/procedure (in attached file) and run it as "select bench(10,
> 5000)". This will give a 50000 records inserts (5 x 10000). (well, I run it
> on a P200+64MB of RAM, under Linux, and Postgres 7.0.2. In a more powerfull
> machine, you can try other values).
> I get as result, the following times:
> id | objname | benchtime
> ----+---------+-----------
> 1 | group 1 | 00:00:32
> 2 | group 2 | 00:00:47
> 3 | group 3 | 00:01:13
> 4 | group 4 | 00:01:41
> 5 | group 5 | 00:02:08
> (5 rows)
This is an inefficiency in handling of foreign-key triggers. It's fixed
for 7.1 --- in current sources I get
id | objname | benchtime
----+----------+-----------
1 | group 1 | 00:00:03
2 | group 2 | 00:00:03
3 | group 3 | 00:00:03
4 | group 4 | 00:00:03
5 | group 5 | 00:00:03
6 | group 6 | 00:00:03
7 | group 7 | 00:00:03
8 | group 8 | 00:00:03
9 | group 9 | 00:00:03
10 | group 10 | 00:00:03
(10 rows)
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Memory and performance
2001-04-04 17:15 Memory and performance edipoelder@ig.com.br
2001-04-05 01:53 ` Re: Memory and performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-04-05 12:52 ` Tim Perdue <tim@perdue.net>
2001-04-05 14:46 ` Re: Memory and performance Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Tim Perdue @ 2001-04-05 12:52 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
I thought this was an interesting thread because we're running into
problems with IO under 7.1 during vacuum and a couple of scheduled aggregation
jobs.
Our database is about 1GB in total size, the machine has 4GB, but the entire
system is only using 1.2 GB, even during vacuum or a daily, extremely large
query that requires a lot of grouping and sorting.
Any suggestions as to how we can make more use of our RAM, paricularly during
vacuum, which is when we get killed? We've actually considered mounting the
database on a RAM drive in a halfway serious way.
Tim
--
Founder - PHPBuilder.com / Geocrawler.com
Lead Developer - SourceForge
VA Linux Systems
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Memory and performance
2001-04-04 17:15 Memory and performance edipoelder@ig.com.br
2001-04-05 01:53 ` Re: Memory and performance Tom Lane <tgl@sss.pgh.pa.us>
2001-04-05 12:52 ` Re: Memory and performance Tim Perdue <tim@perdue.net>
@ 2001-04-05 14:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-04-05 14:57 ` Re: Memory and performance Tim Perdue <tim@perdue.net>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2001-04-05 14:46 UTC (permalink / raw)
To: Tim Perdue <tim@perdue.net>; +Cc: pgsql-sql
Tim Perdue <tim@perdue.net> writes:
> Our database is about 1GB in total size, the machine has 4GB, but the entire
> system is only using 1.2 GB, even during vacuum or a daily, extremely large
> query that requires a lot of grouping and sorting.
What have you got the SortMem parameter (backend -S switch) set to?
That's about the only use I can think of for RAM beyond what's needed to
cache the whole database ...
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Memory and performance
2001-04-04 17:15 Memory and performance edipoelder@ig.com.br
2001-04-05 01:53 ` Re: Memory and performance Tom Lane <tgl@sss.pgh.pa.us>
2001-04-05 12:52 ` Re: Memory and performance Tim Perdue <tim@perdue.net>
2001-04-05 14:46 ` Re: Memory and performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-04-05 14:57 ` Tim Perdue <tim@perdue.net>
0 siblings, 0 replies; 8+ messages in thread
From: Tim Perdue @ 2001-04-05 14:57 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On Thu, Apr 05, 2001 at 10:46:07AM -0400, Tom Lane wrote:
> Tim Perdue <tim@perdue.net> writes:
> > Our database is about 1GB in total size, the machine has 4GB, but the entire
> > system is only using 1.2 GB, even during vacuum or a daily, extremely large
> > query that requires a lot of grouping and sorting.
>
> What have you got the SortMem parameter (backend -S switch) set to?
> That's about the only use I can think of for RAM beyond what's needed to
> cache the whole database ...
-i -o -F -S 32768 -B 2048 is our startup line.
It appears to me that when you're vacuuming, it's physically moving all the
bits around the disk. It doesn't read in the table, clean out the cruft, the
flush it out. So I think this is where we're getting IO bound. We only have a
5x36 RAID - must not be sufficient.
Tim
--
Founder - PHPBuilder.com / Geocrawler.com
Lead Developer - SourceForge
VA Linux Systems
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Memory and performance
@ 2001-04-04 22:15 Edipo Elder Fernandes de Melo <edipoelder@ig.com.br>
0 siblings, 0 replies; 8+ messages in thread
From: Edipo Elder Fernandes de Melo @ 2001-04-04 22:15 UTC (permalink / raw)
To: pgsql-sql
Em 05 Apr 2001, Cedar Cox escreveu:
>To this I say, remember that you are using a database! I would split this
>into 3 tables (people, exams, answers). Then only the 'answers' table
>would contain 3M records. Should be a bit faster. You don't want to have
>to store the <person> and <exam> with each <answer>.
>
>(If this is what you are doing then ignore me. I don't take you for an
>idiot :)
Yeah... I'm doing this... (I'm not a idiot! :)))) I'm just asking if
exist any way to optimize PostgreSQL to handle a great quantity of tuples.
(I will try the 7.1. By the way... in the URI
http://www.postgresql.org/ftpsite/dev/ we found a RC2 version, but in the
URI ftp://ftp.postgresql.org/pub/dev/ don't!)
Hugs,
Edipo Elder
[edipoelder@ig.com.br]
_________________________________________________________
Oi! Você quer um iG-mail gratuito?
Então clique aqui: http://www.ig.com.br/paginas/assineigmail.html
^ permalink raw reply [nested|flat] 8+ messages in thread
* Memory And Performance
@ 2001-04-07 03:31 Mark Kirkwood <markir@i4free.co.nz>
0 siblings, 0 replies; 8+ messages in thread
From: Mark Kirkwood @ 2001-04-07 03:31 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; edipoelder@ig.com.br; +Cc: pgsql-sql
> > The system that I'm developing, I have about 25000 (persons) x 8
>>(exams)
>> x 15 (answers per exam) = 3000000 records to process and it is VERY SLOW.
>
>f you need to import large quantities of data, look at the copy
>command, that tends to be faster.
By way of example for the level of improvement COPY gives:
a 3000000 row table ( 350Mb dump file -> 450Mb table ) can by loaded via copy
in 7 minutes. To insert each row (say using a perl prog to read the file and
DBD-Pg to insert, committing every 10000 rows ) takes about 75minutes. I used
a PII 266Mhz/192Mb and Postgresql 7.1b5 for these results. Postgresql 7.0.2
is slower ( 20-30% or so...), but should still display a similar level of
improvement with copy.
Good loading
Mark
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2001-04-07 03:31 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-04-04 17:15 Memory and performance edipoelder@ig.com.br
2001-04-04 20:34 ` Richard Huxton <dev@archonet.com>
2001-04-05 01:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-04-05 12:52 ` Tim Perdue <tim@perdue.net>
2001-04-05 14:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-04-05 14:57 ` Tim Perdue <tim@perdue.net>
2001-04-04 22:15 Re: Memory and performance Edipo Elder Fernandes de Melo <edipoelder@ig.com.br>
2001-04-07 03:31 Memory And Performance Mark Kirkwood <markir@i4free.co.nz>
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