Re: Maximum Performance Follow-up Question

From: "Peter T(dot) Brown" <peter(at)memeticsystems(dot)com>
To: "'Luis Amigo'" <lamigo(at)atc(dot)unican(dot)es>, "'Jean Huveneers'" <j(dot)huveneers(at)farcourier(dot)com>
Cc: <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Maximum Performance Follow-up Question
Date: 2002-01-24 18:05:10
Message-ID: 003301c1a501$aa69df20$7d00000a@PETER
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)postgresql(dot)org
[mailto:pgsql-admin-owner(at)postgresql(dot)org]On Behalf Of Luis Amigo
Sent: Thursday, January 24, 2002 12:27 AM
To: Jean Huveneers
Cc: pgsql-admin(at)postgresql(dot)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

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Luis Amigo 2002-01-24 18:23:30 Re: Maximum Performance Follow-up Question
Previous Message Zhang, Anna 2002-01-24 17:30:35 Re: ERROR: cannot read block