Thread: Memory and performance

Memory and performance

From
edipoelder@ig.com.br
Date:
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

Re: Memory and performance

From
Edipo Elder Fernandes de Melo
Date:
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



Re: Memory and performance

From
Tom Lane
Date:
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:035 | 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:0310| group 10 | 00:00:03
 
(10 rows)
        regards, tom lane


Re: Memory and performance

From
Richard Huxton
Date:
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


Re: Memory and performance

From
Tim Perdue
Date:
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


Re: Memory and performance

From
Tom Lane
Date:
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


Re: Memory and performance

From
Tim Perdue
Date:
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