Skip site navigation (1) Skip section navigation (2)

Re: cached memory

From: "Campbell, Lance" <lance(at)uiuc(dot)edu>
To: "dx k9" <bitsandbytes88(at)hotmail(dot)com>,"Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
Cc: "posgres support" <pgsql-admin(at)postgresql(dot)org>
Subject: Re: cached memory
Date: 2007-11-15 15:37:47
Message-ID: B10E6810AC2A2F4EA7550D072CDE8760197E06@SAB-FENWICK.sab.uiuc.edu (view raw or flat)
Thread:
Lists: pgsql-admin
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> 

In response to

pgsql-admin by date

Next:From: Andrew SullivanDate: 2007-11-15 15:38:28
Subject: Re: trigger ddl actions in a table
Previous:From: König, MonikaDate: 2007-11-15 15:33:34
Subject: functions pg_get...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group