Thread: Memory Tuning
I am looking for information on tuning memory usage for Postgres on Linux (2.2 kernel). In particular I have a lot of memory relative to the size of my database and am looking to reduce latency in queries. Searching goegle turned up a few other cases of people asking about memory tuning, but I didn't see any answers. I have tried increasing the memory allowed for in memory sorts and the estimate of available buffer caching. I haven't tried raising the buffer space per connection as the documenation didn't seem to indicate that that would help. This seems to have helped a little, but it is hard to tell since the current delay is about 1 second and the queries seem to happen faster when they are repeated after a short amount of time. For some idea of the specific problem, I am setting up a replacement web server for my hobby server. The new box has a 1GHz Tbird with 500MB of memory and a 20G 7200rpm IDE disk. The database is effectively static, with rows for 300 games, 4000 people and 11000 people/game ratings and a couple of other miscelaneous tables with a handful of rows each. I am mostly interested in reducing the latency of requests while maintaining the flexibility of using the database. Eventually there will be online data entry so I don't want to switch to writing static files for the most of the different possible reports.
If you could post the schema of your tables that you do the query against and an EXPLAIN of the queries you're doing, perhaps we could further tune your queries in addition to beefing up the memory usage of the backend.. Check this link out too. http://postgresql.readysetnet.com/devel-corner/docs/user/performance-tips.ht ml -Mitch Software development : You can have it cheap, fast or working. Choose two. ----- Original Message ----- From: "Bruno Wolff III" <bruno@wolff.to> To: <pgsql-general@postgresql.org> Sent: Friday, March 30, 2001 9:45 AM Subject: Memory Tuning > I am looking for information on tuning memory usage for Postgres on Linux > (2.2 kernel). In particular I have a lot of memory relative to the size > of my database and am looking to reduce latency in queries. > > Searching goegle turned up a few other cases of people asking about memory > tuning, but I didn't see any answers. > > I have tried increasing the memory allowed for in memory sorts and the > estimate of available buffer caching. I haven't tried raising the buffer > space per connection as the documenation didn't seem to indicate > that that would help. This seems to have helped a little, but it is > hard to tell since the current delay is about 1 second and the queries > seem to happen faster when they are repeated after a short amount of > time. > > For some idea of the specific problem, I am setting up a replacement > web server for my hobby server. The new box has a 1GHz Tbird with 500MB > of memory and a 20G 7200rpm IDE disk. The database is effectively static, > with rows for 300 games, 4000 people and 11000 people/game ratings and > a couple of other miscelaneous tables with a handful of rows each. > > I am mostly interested in reducing the latency of requests while maintaining > the flexibility of using the database. Eventually there will be online data > entry so I don't want to switch to writing static files for the most of the > different possible reports. > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl >
On Fri, Mar 30, 2001 at 10:18:56AM -0500, Mitch Vincent <mitch@venux.net> wrote: > If you could post the schema of your tables that you do the query against > and an EXPLAIN of the queries you're doing, perhaps we could further tune > your queries in addition to beefing up the memory usage of the backend.. This is a bit more than I was expecting. People who do this kind of thing generally paid lots of money. However, if you really want, all of the information on queries and schema is available at http://wolff.to/area/ . That is the old box which has a lot less memory and a much slower processor. The database schema build script is available as well as the source to the perl scripts that handle the queries. The especially slow (about 20 seconds before rows are returned - reduced to about 1 second on the new box) queries are the full lists of people sorted by name or ID (the ID sort isn't as slow). Almost all of the data is available. However the people data is accessed through a view and there is one person whose name is anonymized. At this point I wasn't as worried about inefficiencies in the queries themselves, but rather how to tell the database server and/or linux to best use the memory. The data in the database should easily fit into memory. > > Check this link out too. > http://postgresql.readysetnet.com/devel-corner/docs/user/performance-tips.ht > ml I will look through that site again. I looked at that previously, but not specifically looking for efficient use of memory.
If you have a specific query that you're having trouble with, post it and the table schema and an EXPLAIN of the query when you run it, generally someone will have some immediate pointers on how to speed things up.. I don't have time to go through your site looking for the database schema and such but if you include some specific information in an email to the list I'd be happy to take a quick look (and I'm sure others would too).. Good luck! -Mitch Software development : You can have it cheap, fast or working. Choose two. ----- Original Message ----- From: "Bruno Wolff III" <bruno@wolff.to> To: "Mitch Vincent" <mitch@venux.net> Cc: <pgsql-general@postgresql.org> Sent: Friday, March 30, 2001 2:01 PM Subject: Re: Memory Tuning > On Fri, Mar 30, 2001 at 10:18:56AM -0500, > Mitch Vincent <mitch@venux.net> wrote: > > If you could post the schema of your tables that you do the query against > > and an EXPLAIN of the queries you're doing, perhaps we could further tune > > your queries in addition to beefing up the memory usage of the backend.. > > This is a bit more than I was expecting. People who do this kind of thing > generally paid lots of money. > > However, if you really want, all of the information on queries and schema > is available at http://wolff.to/area/ . That is the old box which has a > lot less memory and a much slower processor. The database schema build > script is available as well as the source to the perl scripts that handle > the queries. The especially slow (about 20 seconds before rows are returned > - reduced to about 1 second on the new box) queries are the full lists of > people sorted by name or ID (the ID sort isn't as slow). > > Almost all of the data is available. However the people data is accessed > through a view and there is one person whose name is anonymized. > > At this point I wasn't as worried about inefficiencies in the queries > themselves, but rather how to tell the database server and/or linux to > best use the memory. The data in the database should easily fit into memory. > > > > > Check this link out too. > > http://postgresql.readysetnet.com/devel-corner/docs/user/performance-tips.ht > > ml > > I will look through that site again. I looked at that previously, but not > specifically looking for efficient use of memory. >
On Fri, Mar 30, 2001 at 01:52:42PM -0500, Mitch Vincent <mitch@venux.net> wrote: > If you have a specific query that you're having trouble with, post it and > the table schema and an EXPLAIN of the query when you run it, generally > someone will have some immediate pointers on how to speed things up.. I I am currently interested in the general issue of how to use memory efficiently rather than speeding up specific queries. I may revisit that later to see if controlling join order would help. Right now I am interested in such things such as: Should I leave postgres tuning alone and let Linux use all of the memory for buffer caching? Is there any good reason to increase the number of buffers per backend over the default of 2? If I don't anticipate a lot of simultaneous queries, what fraction of memory should I let in core sorts take up? To set the estimate for bucher caching, should I just look at /proc/meminfo to see how much memory is being used for caching or should I get this estimate some other way? Why I am not seeing consitant wall clock times for queries? Presumably there is some caching going on, but I am not sure if it is in postgres or in the OS. > don't have time to go through your site looking for the database schema and > such but if you include some specific information in an email to the list Including the pointers to the site was a semi joke. I don't expect people to go and give me specific answers. And I am not as interested in that as I am in general ideas in how to figure things out. This might include useful rules of thumb for (for my current issue) memory tuning, where to read about performance measuring tools. > I'd be happy to take a quick look (and I'm sure others would too).. I appreciate the help, but I think so far you are offering to answer a different question than I have at this point.
> I am currently interested in the general issue of how to use memory > efficiently rather than speeding up specific queries. I may revisit > that later to see if controlling join order would help. > > Right now I am interested in such things such as: > > Should I leave postgres tuning alone and let Linux use all of the memory > for buffer caching? We have a server with 512 MB of RAM, and I've tried pretty hard to get PostgreSQL to use it all - but it just doesn't need it. My startup line goes like this: su postgres -c '/usr/local/pgsql/bin/postmaster -i -S -B 8192 -N 64 -d 3 -D/usr/local/pgsql/data -o "-S 65536"' Now I know that I could set those numbers higher, but doing so does me no good - PostgreSQL simply doesn't need that much memory for what we do. They used to be set at about half of their current levels (buffers, etc.), and I doubled them just because I could, but saw no further performance increases or memory usage increases. The machine generally runs with around 275 megs doing nothing but disk cache, even under moderate usage. The limiting factor is the CPU speed, now. Having watched the lights on the disks, I feel that's enough cache - the lights only give a quick blink occasionally, when we do an insert/update. Other than that, everything runs out of cache. steve
--- Bruno Wolff III <bruno@wolff.to> wrote: > I am looking for information on tuning memory usage for > Postgres on Linux > (2.2 kernel). In particular I have a lot of memory relative to > the size > of my database and am looking to reduce latency in queries. <snip> > For some idea of the specific problem, I am setting up a > replacement > web server for my hobby server. The new box has a 1GHz Tbird > with 500MB > of memory and a 20G 7200rpm IDE disk. The database is > effectively static, > with rows for 300 games, 4000 people and 11000 people/game > ratings and > a couple of other miscelaneous tables with a handful of rows > each. Your problem may not be the fault of poor memory management. By default most (if not all) Linux distributions set IDE drives to the safest and slowest settings possible. By tweaking the settings using the hdparm utility you can reduce CPU utilization and increase disk speed at the same time. Read this article for some more information on the subject, http://www.oreillynet.com/lpt/a/272 . Note: SCSI hard drives often get excellent performance without any of the aforementioned tuning. Brent __________________________________________________ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/?.refer=text
Bruno Wolff III <bruno@wolff.to> writes:
> Should I leave postgres tuning alone and let Linux use all of the memory
> for buffer caching?
You shouldn't try to make Postgres consume *all* of memory for buffers;
for one thing, if the shmem region is too large then (at least on some
Unixen, not sure about Linux) the kernel might decide to swap out parts
of it. That'd be counterproductive. However:
> Is there any good reason to increase the number of buffers per backend over
> the default of 2?
Yes. Particularly so under 7.1 --- WAL likes to have a lot of buffers
so that it doesn't have to write data to data files too often. I'd
recommend perhaps a few thousand buffers depending on how much RAM you
have (maybe 10-20% of your physical RAM would be a reasonable upper
limit on how much space for buffers).
> Why I am not seeing consitant wall clock times for queries? Presumably
> there is some caching going on, but I am not sure if it is in postgres
> or in the OS.
Both: we have our buffer area, and then the kernel has its own buffers.
We can make good use of kernel-level buffering as well as our shared-memory
buffers, so there's no reason to try to starve the kernel of buffer space.
regards, tom lane
> Bruno Wolff III <bruno@wolff.to> writes: > > Should I leave postgres tuning alone and let Linux use all of the memory > > for buffer caching? > > You shouldn't try to make Postgres consume *all* of memory for buffers; > for one thing, if the shmem region is too large then (at least on some > Unixen, not sure about Linux) the kernel might decide to swap out parts > of it. That'd be counterproductive. However: If it pages out, it also maps that shared virtual memory into every forked backend. That can be lots of page tables. > > > Is there any good reason to increase the number of buffers per backend over > > the default of 2? > > Yes. Particularly so under 7.1 --- WAL likes to have a lot of buffers > so that it doesn't have to write data to data files too often. I'd > recommend perhaps a few thousand buffers depending on how much RAM you > have (maybe 10-20% of your physical RAM would be a reasonable upper > limit on how much space for buffers). > > > Why I am not seeing consitant wall clock times for queries? Presumably > > there is some caching going on, but I am not sure if it is in postgres > > or in the OS. > > Both: we have our buffer area, and then the kernel has its own buffers. > We can make good use of kernel-level buffering as well as our shared-memory > buffers, so there's no reason to try to starve the kernel of buffer space. Our shared buffers allow us to modify them without copying them in/out of the kernel. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruno Wolff III wrote:
> I am looking for information on tuning memory usage for Postgres on Linux
What about
http://www.postgresql.org/devel-corner/docs/postgres/kernel-resources.html?
--
_
/ \ Leandro Guimarães Faria Corsetti Dutra +55 (11) 3040 8913
\ / Amdocs at Bell Canada +1 (514) 786 87 47
X Support Center, São Paulo, Brazil mailto:adbaamd@bell.ca
/ \ http://terravista.pt./Enseada/1989/ mailto:leandrod@amdocs.com