Thread: Memory and performance
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
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
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
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
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
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
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