* Curt Sampson <cjs@cynic.net> [060201 17:11] wrote:
> On Wed, 1 Feb 2006, Alfred Perlstein wrote:
> 
> >Basically, FreeBSD has a setting 'kern.ipc.shm_use_phys' under sysctl
> >that will tell the kernel not to make SysV shared memory pageable.  This
> >is VERY handy when you are going to use very large shared segments.
> 
> Sure, but assuming your buffer cache works reasonably well, you ought
> not be devoting more than about 20% of your memory to PostgreSQL shared
> memory buffers, so they're hardly likely to get swapped anyway.
> 
> Keep in mind that those shared memory buffers are essentially
> "double-buffered;" any block in a shared memory buffer is copied from
> a block in the buffer cache, and it's used often enough, if it's in a
> buffer, that it's likely to stay in the buffer cache as well. So by
> increasing your shared memory buffers, you are in most circumstances
> actually decreasing the amount of total cache you have available.
> 
> Some will argue that you should try and make the shared buffers huge
> in order to minimize the amount of file system buffering, but because
> you don't generally have much control over other memory usage (system
> processes, other postgresql processes, sort memory, maintenance memory
> for vacuum, etc.) you're highly likely to create truly tragic situations
> such as your sort memory being swapped out to disk that will cause your
> performance to plummet.

Database caching algorithms will offer a signifigant advantage over
a simple filesystem LRU.

Additionally, a larger shared segment reduces the amount of copying
required.  For instance, if you have 1 gig available, but only a
256meg buffer, you will constantly be copying data using read/write
even though it could have been cached.

Additionally, a larger shared segment particularly when non-pageable,
will protect the database's cache against errant jobs that blow
through the kernel's cache.  Imagine the effect of untarring a large
file suddenly.

In practice, when I ran a very database intensive site, the higher
I cranked the shared memory buffers, the better the performance up
until a point, but that point was about 1gig (maybe 768megs) if I
recall correctly.

> >You are very correct here.  MySQL is not a "real database", it's close,
> 
> I was using the term DBMS, or "Data Base Managment System"; a "database"
> is what it manages. MySQL is not even close to a DBMS. But let's not get
> into that. :-)

:D

-- 
- Alfred Perlstein