Re: memory - Mailing list pgsql-novice

Richard Broersma Jr wrote:
>> I've a relatively small machine (512MB) that I am setting up as a small area
>> database server.  And I was trying to get the memory balanced out for this
>> machine.  I don't plan on running anything other than postgresql and whatever
>> might be required to operate sanely on the network.
>> So I was changing my shared buffers and found I couldn't really get over 3500
>> before SHMMAX started complaining.
>> That being done, I'm running some jobs now on this server and have noticed that
>> postgres uses only a few percentage points of the available memory according to top.
>> So, I'm trying to understand why I don't have more memory being used up by these
>> SQL jobs.  I was assuming that running 100 SQL statements/second would suck up a
>> lot of memory.
>> Right now all it seems to burn in CPU cycles more than RAM.
>> Maybe I don't understand much about how postgres will appear to operate...
>> But is the memory limited by the shared_buffers * max_connections?
>
> Don't forget that if you database is significantly smaller than you memory, it could reside
> entirely in Kernel memory cache.  The shared_buffer is used (IIRC) to allocate memory specifically
> for preforming complicated data transformations required by your issued SQL statement.  The larger
> larger the data set your transforming or the more complication your sql statements are,
> performance can benefit from increased shared_buffers.  However, I believe that this can reduce
> the amount of memory available for caching the rest of your database in memory.
>
> But to verify what I've mentioned please see the following:
>
> http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html
> http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
> http://www.postgresql.org/files/documentation/books/aw_pgsql/hw_performance/
>
> Regards,
>
> Richard Broersma Jr.
>

Lots to learn.

I changed the shmmax to ~442MB and changed the shared_buffers from 3000 to 52000.
The database is MUCH faster, less load on the cpu, but takes 50% of the RAM.
I don't know how much of the data is cached per se -- but it's an improvement.

Now I probably have to worry about using too much memory...

Lots to learn.