Thread: memory
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?
Tom Allison <tallison@tacocat.net> writes:
> 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.
Well, that's only about 28MB. A lot of systems have unreasonably small
SHMMAX settings (historical leftover); you might try increasing yours.
If you're running something older than PG 8.1, it's not necessarily
worth your trouble to increase shared_buffers beyond that, but in 8.1
I'd encourage you to try going higher.
> 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.
Not necessarily. How much data do they touch?
regards, tom lane
Tom Allison <tallison@tacocat.net> writes: > How would I change that memory setting? Depends on what OS you're running, see advice at http://www.postgresql.org/docs/8.1/static/kernel-resources.html regards, tom lane
> 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.
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.
> 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... If you want to have large shared_buffers but not use too much memory, just reduce max_connections to a reasonable number. Regards, Richard Broersma Jr.
There is a contrib module (not sure now if it is already a pgfoundry project, tho) that will help you while trying to figure out the buffer hits. Best whishes, g.- On 11/9/06, Tom Allison <tallison@tacocat.net> wrote: > 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. > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > -- Guido Barosio ----------------------- http://www.globant.com guido.barosio@globant.com