Re: cached memory

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(at)postgresql(dot)org [mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of dx k9
Sent: Thursday, November 15, 2007 8:07 AM
To: Scott Marlowe
Cc: posgres support
Subject: Re: [ADMIN] 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(dot)marlowe(at)gmail(dot)com
> To: bitsandbytes88(at)hotmail(dot)com
> Subject: Re: [ADMIN] cached memory
> CC: pgsql-admin(at)postgresql(dot)org
>
> On Nov 14, 2007 3:13 PM, dx k9 <bitsandbytes88(at)hotmail(dot)com> 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! <http://www.cafemessenger.com/info/info_sweetstuff2.html?ocid=TXT_TAGLM_OctWLtagline>