DJK,
I used Oracle for quite a while myself. I
found I had to think outside the box when looking at PostgreSQL. I maintain a
very heavily used PostgreSQL database now. The key I found was for PostgreSQL
8.2.4:
1) Make sure to set effective_cache_size to how much memory PostgreSQL
will have in order to cache your database files in memory.
2) If you have a really nice server for your database then set
random_page_cost close to 1.0. This will make sure to use indexes.
3) Make sure to set shared_buffers to a number that is not too large
but not to small. The max I would use would be 1 GB in most cases. It is
important to remember that PostgreSQL caches the blocks in the shared_buffers
area. Don’t double buffer. The operating system has already put your database
file blocks in memory since it is accessing them regularly. They are cached.
4) It is important to set work_mem to an appropriate amount in order
to handle sorting.
If you have any questions I would be more
than happy to answer them. It was really a challenge for me to think in a
different direction from that of Oracle. Feel free to contact me by phone.
It is very important to give some general
information when posting to the listserv:
1) PostgreSQL version
2) Amount of memory
Honestly I wish they would split this
listserv up based on version number of PostgreSQL. Then maybe some people
would be more responsive to upgrading.
Thanks,
Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu
From: pgsql-admin-owner@xxxxxxxxxxxxxx
[mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] On
Behalf Of dx k9
Sent: Thursday, November 15, 2007
8:07 AM
To: Scott Marlowe
Cc: posgres support
Subject: Re: cached memory
Hi Scott,
Thanks for the reply.
Top is showing 10157008 / 15897160 in kernel cache, so postgres is
using 37% right now, following what you are saying. I realize the
load isn't peaking right now, but wouldn't it be nice to have some of the
indexes cached in memory?
In your case 1868064 / 2000000 or 7 % of your memory is being used by
postgres. That sort of proves my point. Shouldn't postgres use more
than 7% of the memory. Doesn't that seem like 93% isn't being used?
~DjK
top - 08:59:38 up 277 days, 23:03, 1 user, load average: 0.63,
0.51, 0.40
Tasks: 101 total, 1 running, 100 sleeping, 0
stopped, 0 zombie
Cpu0 : 0.0% us, 1.0% sy, 0.0% ni, 99.0% id, 0.0%
wa, 0.0% hi, 0.0% si
Cpu1 : 0.0% us, 0.0% sy, 0.0% ni, 99.7% id, 0.3%
wa, 0.0% hi, 0.0% si
Cpu2 : 0.7% us, 0.7% sy, 0.0% ni, 98.3% id, 0.0%
wa, 0.0% hi, 0.3% si
Cpu3 : 0.0% us, 0.0% sy, 0.0% ni, 99.3% id, 0.7%
wa, 0.0% hi, 0.0% si
Mem: 15897160k total, 10477104k used, 5420056k free,
169780k buffers
Swap: 16779768k total, 78912k used, 16700856k free, 10157008k
cached
PID USER PR NI VIRT
RES SHR S %CPU %MEM TIME+ COMMAND
1975 postgres 15 0 33412 7932 1388 S 1.0
0.0 1211:09 postgres: stats collector process
1971 postgres 15 0 1085m 14m 14m S
0.3 0.1 2323:28 /postgres
1 root 16
0 640 80 48 S 0.0
0.0 0:11.55 init [3]
2 root RT
0 0 0 0 S
0.0 0.0 0:02.30 [migration/0]
3 root 34
19 0 0 0 S
0.0 0.0 0:06.99 [ksoftirqd/0]
4 root RT
0 0 0 0 S
0.0 0.0 0:00.82 [migration/1]
5 root 34
19 0 0 0 S
0.0 0.0 0:56.60 [ksoftirqd/1]
6 root RT
0 0 0 0 S
0.0 0.0 0:10.71 [migration/2]
7 root 34 19
0 0 0
> Date: Wed, 14 Nov 2007 15:20:53 -0600
> From: scott.marlowe@xxxxxxxxx
> To: bitsandbytes88@xxxxxxxxxxx
> Subject: Re: cached memory
> CC: pgsql-admin@xxxxxxxxxxxxxx
>
> On Nov 14, 2007 3:13 PM, dx k9 <bitsandbytes88@xxxxxxxxxxx> wrote:
> >
> > In looking at some cacti memory usage graphs, the Oracle servers show
> > only 6 of a total of16 GB of RAM as 'Total Available'. Whereas, our
> > Postgres version 8.24 servers show all 16 GB of RAM totally available
or
> > free. Some people are asking why Postgres doesn't take that memory
and
> > lock into it, so you can't see less 'total available' memory. We use
a lot
> > of B-tree indexes. This may or may not be related, but it there a
good way
> > to make sure those stay in memory?
>
> Not sure what you mean by totally available. Is the OS using it to
> cache? If so, why should postgresql do what the OS already does so
> well.
>
> Oracle was written back when OSes were barely more than program
> loaders and it had to do everything, from having its own file systems
> to buffering / caching to memory management to job schedulers.
>
> PostgreSQL was written as Unix was maturing (mostly) and takes
> advantage of all the cool things a modern unix comes with, and one of
> those things is kernel level caching of disk files.
>
> So, what does free / top have to say about your memory? And how hard
> have these servers been working. For instance, my RHEL4 reporting
> server, with only 2 Gigs in it shows 1868064 used as kernel cache.
> The rest is mostly pgsql processes
Help yourself to FREE treats served up daily at the
Messenger Café. Stop by today!
