Re: Maximum Performance Follow-up Question - Mailing list pgsql-admin

But how can Postgres be 'forced' to keep a table in memory? I've noticed
that on our Dual Pentium4, 1GB RAM machine, the size of the individual
postgres threads is very small. Top reports it as like 5K or 20K (I believe
that's what it means). Shouldn't this number be 100's of MB if postgres is
properly moving my tables to RAM? I do notice that the system cache is very
very large... Is there any way to specify that a certain table should have
priority for being transferred into RAM? Should I reduce the system cache
size so that postgres has more room to play with?

Thanks for any help!


here is a top dump:

89 processes: 88 sleeping, 1 running, 0 zombie, 0 stopped
CPU0 states: 10.0% user,  2.3% system,  0.0% nice, 87.0% idle
CPU1 states:  0.0% user,  0.5% system,  0.0% nice, 99.0% idle
Mem:  1035688K av, 1023896K used,   11792K free,  190424K shrd,  213836K
buff
Swap: 1036184K av,    2520K used, 1033664K free                  554284K
cached

  PID USER     PRI  NI  SIZE  RSS SHARE LC STAT %CPU %MEM   TIME COMMAND
 5861 postgres  17   0 12172  11M 11156  1 S     9.7  1.1   0:01 postmaster
25544 postgres   0   0  3280 3280  3124  0 S     0.0  0.3   1:11 postmaster
31682 postgres   0   0  5844 5844  4904  1 S     0.0  0.5   0:00 postmaster
31886 postgres   0   0  5600 5600  4712  0 S     0.0  0.5   0:00 postmaster
31903 postgres   0   0  5596 5596  4704  0 S     0.0  0.5   0:00 postmaster
31925 postgres   0   0  5600 5600  4712  1 S     0.0  0.5   0:00 postmaster
31929 postgres   0   0  5600 5600  4708  1 S     0.0  0.5   0:00 postmaster
 5441 postgres   0   0  5916 5916  4992  0 S     0.0  0.5   0:00 postmaster
 5551 postgres   0   0  5744 5744  4868  1 S     0.0  0.5   0:00 postmaster
 5552 postgres   0   0  5888 5888  4988  1 S     0.0  0.5   0:00 postmaster
 5553 postgres   0   0  5528 5528  4652  0 S     0.0  0.5   0:00 postmaster
 5554 postgres   0   0  5764 5764  4888  0 S     0.0  0.5   0:00 postmaster
 5555 postgres   0   0  5768 5768  4892  0 S     0.0  0.5   0:00 postmaster
 5556 postgres   0   0  5724 5724  4848  1 S     0.0  0.5   0:00 postmaster
 5563 postgres   0   0  5520 5520  4644  1 S     0.0  0.5   0:00 postmaster
 5564 postgres   0   0  5684 5684  4784  1 S     0.0  0.5   0:00 postmaster
 5565 postgres   0   0  5516 5516  4640  0 S     0.0  0.5   0:00 postmaster
 5570 postgres   0   0  5548 5548  4668  1 S     0.0  0.5   0:00 postmaster
 5572 postgres   0   0  5540 5540  4660  0 S     0.0  0.5   0:00 postmaster
 5573 postgres   0   0  5516 5516  4636  1 S     0.0  0.5   0:00 postmaster
 5709 postgres   0   0  5524 5524  4648  1 S     0.0  0.5   0:00 postmaster
 5710 postgres   0   0  5752 5752  4876  1 S     0.0  0.5   0:00 postmaster
 5711 postgres   0   0  5508 5508  4628  1 S     0.0  0.5   0:00 postmaster
 5712 postgres   0   0  5756 5756  4876  0 S     0.0  0.5   0:00 postmaster
 5713 postgres   0   0  5516 5516  4628  1 S     0.0  0.5   0:00 postmaster
 5715 postgres   0   0  5504 5504  4600  1 S     0.0  0.5   0:00 postmaster
 5781 postgres   0   0 13660  13M 12560  0 S     0.0  1.3   0:04 postmaster
 5803 postgres   0   0  5516 5516  4608  0 S     0.0  0.5   0:00 postmaster
 5826 postgres   0   0  6920 6920  5868  1 S     0.0  0.6   0:00 postmaster
 5833 postgres   0   0  7360 7360  6244  1 S     0.0  0.7   0:00 postmaster
 5835 postgres   0   0 12424  12M 11288  0 S     0.0  1.1   0:01 postmaster
 5860 postgres   0   0  7596 7596  6460  1 S     0.0  0.7   0:00 postmaster

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Luis Amigo
Sent: Thursday, January 24, 2002 12:27 AM
To: Jean Huveneers
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Maximum Performance


Jean Huveneers wrote:

> Hi,
>
> Within an couple of weeks we will start using PostgreSQL on Mandrake 8.1
> in real business (we have been testing, for over a half year).
>
> In future we will have some tables with 100.000+ records an the system
> has te work very fast.
>
> I know that speed of querries depend much on the amount of availible RAM
> to PostgreSQL, the server will only run the databases. What amount is
> RAM is usefull (I meen, does Postgres use the RAM, if availible, up to 2
> GB)?
>
> I'm can chose for a Dual Athlon MP on a Tyan Thunder K7 motherbord, but
> does PostgreSQL use the 2 processors?
>
> Regards,
>
> Jean Huveneers
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

On my own experience I will tell you that if you're able to force postgres
to keep all database in memory it will be very fast, so memory only depends
on your
database size.
Each backend may run on a different processor, so the more processors u
have the more backends u can run at once
hope it helps