pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Memory 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