Thread: PostgreSQL Performance on OpenBSD
http://www.postgresql.org/docs/aw_pgsql_book/hw_performance/0.html I was taking a look at the above document, and found it very interesting. Essentially, it talks about two very important configuration features being the Cache and Sort sizes. Now, I was wondering how accurate it is when looking at this. Essentially, the author recommends using 25% of the available RAM for the cache and 2-4% for the sort buffer. Since units are available in 8K units, the numbers seem to come across as a bit high for a machine with 512Megs, dedicated to the PostgreSQL server. For example a conversion of Megabytes to Kilobytes then to 8K units: 128 x 1024 / 8 = 16384 (Cache) 20 x 1024 / 8 = 2560 (Sort) My database has a lot of information that is being queried over and over due to the fact that it's running a web based application. So for me, a large cache makes sense as it will allow information to be queried without the associated expensive I/O. However are the above numbers realistic? This would require some fairly customized kernels for OpenBSD to allow for numbers that high as well? And what about the Write Ahead Log (WAL) would a number close to 20-64 megs seem as realistic and proper? Martin Foster Creator/Designer Ethereal Realms martin@ethereal-realms.org
I'm pretty sure sort buffer is measured in k, not 8k units. i.e. 16384 sort mem is 16 Megs. Or are you referring to something other than sort_mem? On Thu, 15 May 2003, Martin Foster wrote: > http://www.postgresql.org/docs/aw_pgsql_book/hw_performance/0.html > > I was taking a look at the above document, and found it very > interesting. Essentially, it talks about two very important > configuration features being the Cache and Sort sizes. > > Now, I was wondering how accurate it is when looking at this. > Essentially, the author recommends using 25% of the available RAM for > the cache and 2-4% for the sort buffer. Since units are available in > 8K units, the numbers seem to come across as a bit high for a machine > with 512Megs, dedicated to the PostgreSQL server. > > For example a conversion of Megabytes to Kilobytes then to 8K units: > 128 x 1024 / 8 = 16384 (Cache) > 20 x 1024 / 8 = 2560 (Sort) > > My database has a lot of information that is being queried over and over > due to the fact that it's running a web based application. So for me, > a large cache makes sense as it will allow information to be queried > without the associated expensive I/O. > > However are the above numbers realistic? This would require some > fairly customized kernels for OpenBSD to allow for numbers that high as > well? And what about the Write Ahead Log (WAL) would a number close to > 20-64 megs seem as realistic and proper? > > Martin Foster > Creator/Designer Ethereal Realms > martin@ethereal-realms.org > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html >
On Mon, 19 May 2003, Martin Foster wrote: > scott.marlowe wrote: > > I'm pretty sure sort buffer is measured in k, not 8k units. i.e. > > > > 16384 sort mem is 16 Megs. > > > > Or are you referring to something other than sort_mem? > > > > It seems that you are correct. Just took a look at the configuration > file and while shared_buffers are in 8K units, or seem to be the > sort_mem is in K units meaning that I am allocating less then expected. > > 128 x 1024 / 8 = 16384 (shared_buffers) > 20 x 1024 = 20480 (sort_mem) > > Are these numbers normal? Since they start you out at considerably > higher then the default/minimum values that PostgreSQL advertises in > it's configuration file. Yeah, those numbers are fairly typical. Unless you have truly huge sorts going on, 16 megs or so is a good compromise number. note that you can change sort_mem for the current session with set sort_mem=nnnn; so if you need a big sort mem every now and then, you can do it for just that one query, and leave it smaller. Since shared_buffers are fixed and don't change during operation, they don't have the same danger that sort_mem does of dynamically running the machine into the ground should too much memory gets allocated to sorts. sort_mem is a limit per sort, so theorectically, a complex query could generate more than one sort, and a handful of clients running large sorts could run the machine of out RAM and into a swap storm as tries to service all the backend process sorts. So, while having sort_mem too small costs a little in performance, having it set too high can result in your server coming to a crawl under load, which is definitely worse. On servers with lots of memory, the real limit to shared_buffers is that postgresql becomes less efficient at handling its cache as shared_buffers gets to be a pretty big chunk of memory. On my machine with 1.5 gig ram, I allocate 256 Megs ram to shared_buffers, but only 16 Meg for sort_mem.
Martin Foster writes: > However are the above numbers realistic? This would require some > fairly customized kernels for OpenBSD to allow for numbers > that high as well? What kernel parameter would need to be changed besides BUFCACHEPERCENT (which defaults to 5%)? That's one small customization.
On Mon, May 19, 2003 at 02:13:58PM -0600, scott.marlowe wrote: > Since shared_buffers are fixed and don't change during operation, they > don't have the same danger that sort_mem does of dynamically running the > machine into the ground should too much memory gets allocated to sorts. > sort_mem is a limit per sort, so theorectically, a complex query could > generate more than one sort, and a handful of clients running large sorts > could run the machine of out RAM and into a swap storm as tries to service > all the backend process sorts. So, while having sort_mem too small costs > a little in performance, having it set too high can result in your server > coming to a crawl under load, which is definitely worse. Hmm... this could explain some of the memory alloc errors people have been posting about. It would be very useful if pgsql could limit the amount of memory used by a connection, or better yet, used across all connections. This way you could ensure that you never start swapping. -- Jim C. Nasby (aka Decibel!) jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
"Jim C. Nasby" <jim@nasby.net> writes: > Hmm... this could explain some of the memory alloc errors people have > been posting about. It would be very useful if pgsql could limit the > amount of memory used by a connection, or better yet, used across all > connections. This way you could ensure that you never start swapping. Per-connection is easy: 'man ulimit' Across all connections is possible if your system supports per-user limits (in addition to per-process) but otherwise all the bookkeeping would have to be done in the server, kept in shared memory and managed with a semaphore. Somehow I doubt you'd get that patch accepted. :) -Doug
"Jim C. Nasby" <jim@nasby.net> writes: > On Mon, May 19, 2003 at 08:50:31PM -0400, Doug McNaught wrote: > > Per-connection is easy: 'man ulimit' > > > > Across all connections is possible if your system supports per-user > > limits (in addition to per-process) but otherwise all the bookkeeping > > would have to be done in the server, kept in shared memory and managed > > with a semaphore. Somehow I doubt you'd get that patch accepted. :) > > Where would you do the ulimit? In the pgsql ~/.profile? In whatever script starts the postmaster. > More importantly, what happens when this limit is hit? I'm guessing the > engine would fail, which isn't very helpful. If the server tracked > resource usage on it's own, it would be able to throttle back things > like sort memory when things started getting tight. That's a lot of overhead for a feature that's not needed for most installations. It's already not that hard to put a crude limit on memory usage: set your shared buffers and sort_mem appropriately, taking into account the number of connections expected and the memory in the system. I fail to see how more detailed resource accounting would be a win, given that the application doesn't know what other apps are on the system and how close it is to the swap limit at any given time. -Doug
On Mon, May 19, 2003 at 08:50:31PM -0400, Doug McNaught wrote: > "Jim C. Nasby" <jim@nasby.net> writes: > > > Hmm... this could explain some of the memory alloc errors people have > > been posting about. It would be very useful if pgsql could limit the > > amount of memory used by a connection, or better yet, used across all > > connections. This way you could ensure that you never start swapping. > > Per-connection is easy: 'man ulimit' > > Across all connections is possible if your system supports per-user > limits (in addition to per-process) but otherwise all the bookkeeping > would have to be done in the server, kept in shared memory and managed > with a semaphore. Somehow I doubt you'd get that patch accepted. :) Where would you do the ulimit? In the pgsql ~/.profile? More importantly, what happens when this limit is hit? I'm guessing the engine would fail, which isn't very helpful. If the server tracked resource usage on it's own, it would be able to throttle back things like sort memory when things started getting tight. -- Jim C. Nasby (aka Decibel!) jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
Chris Palmer wrote:
> Martin Foster writes:
>
>
>>However are the above numbers realistic? This would require some
>>fairly customized kernels for OpenBSD to allow for numbers
>>that high as well?
>
>
> What kernel parameter would need to be changed besides BUFCACHEPERCENT
> (which defaults to 5%)? That's one small customization.
>
>
# PostGRE SQL specific
options SYSVSHM
options SHMMAXPGS=4096
options SHMSEG=256
options SYSVSEM
options SEMMNI=512
options SEMMNS=1024
options SEMMNU=512
options SEMMAP=512
All of these options are required in order to start raising the values
of shared_buffers and most probably sort_mem. Seems that PostgreSQL
uses Posix extensions which are not very well represented in most BSD
kernels.
As a result, you need to manually raise the values above in order to get
higher values. If memory serves, the above was necessary to have 256
connections with 512 shared_buffers.
Martin Foster
Creator/Designer Ethereal Realms
martin@ethereal-realms.org
scott.marlowe wrote:
> I'm pretty sure sort buffer is measured in k, not 8k units. i.e.
>
> 16384 sort mem is 16 Megs.
>
> Or are you referring to something other than sort_mem?
>
It seems that you are correct. Just took a look at the configuration
file and while shared_buffers are in 8K units, or seem to be the
sort_mem is in K units meaning that I am allocating less then expected.
128 x 1024 / 8 = 16384 (shared_buffers)
20 x 1024 = 20480 (sort_mem)
Are these numbers normal? Since they start you out at considerably
higher then the default/minimum values that PostgreSQL advertises in
it's configuration file.
Martin Foster
Creator/Designer Ethereal Realms
martin@ethereal-realms.org
Are these different from what is already in our docs? --------------------------------------------------------------------------- Martin Foster wrote: > Chris Palmer wrote: > > Martin Foster writes: > > > > > >>However are the above numbers realistic? This would require some > >>fairly customized kernels for OpenBSD to allow for numbers > >>that high as well? > > > > > > What kernel parameter would need to be changed besides BUFCACHEPERCENT > > (which defaults to 5%)? That's one small customization. > > > > > > # PostGRE SQL specific > options SYSVSHM > options SHMMAXPGS=4096 > options SHMSEG=256 > > options SYSVSEM > options SEMMNI=512 > options SEMMNS=1024 > options SEMMNU=512 > options SEMMAP=512 > > All of these options are required in order to start raising the values > of shared_buffers and most probably sort_mem. Seems that PostgreSQL > uses Posix extensions which are not very well represented in most BSD > kernels. > > As a result, you need to manually raise the values above in order to get > higher values. If memory serves, the above was necessary to have 256 > connections with 512 shared_buffers. > > Martin Foster > Creator/Designer Ethereal Realms > martin@ethereal-realms.org > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
>>>>> "BM" == Bruce Momjian <pgman@candle.pha.pa.us> writes: BM> Are these different from what is already in our docs? FWIW, the docs for FreeBSD had numbers which were overkill for semaphores when I did the setup. My current values are these: options SYSVSHM #SYSV-style shared memory # Maximum number of shared memory pages system wide. options SHMALL=262144 # Maximum size, in pages (4k), of a single System V shared memory region. options SHMMAXPGS=262144 # only need semaphores for PostgreSQL options SYSVSEM #SYSV-style semaphores # Maximum number of System V semaphores that can be used on the system at # one time. options SEMMNI=32 # Total number of semaphores system wide options SEMMNS=512 # Maximum number of entries in a semaphore map. options SEMMAP=256 My SHM settings allow for up to 1GB of SHM, but I don't use that much anymore after several rounds of tuning discussions on this list. My only concern now is that my buffer cache isn't as big as I think it should be, but perhaps I underestimate the locality of my data ;-)