buffer cache size

[Thomas Hurst]

* michael luch (michaelluch at hotmail.com) wrote:

> I think that the buffer cache on my system is not growing sufficiently
> and wonder if I can I tune it.
>
> Here's a snippet from the output of top on my system:
>
> Mem: 646M Active, 2905M Inact, 174M Wired, 121M Cache, 112M Buf, 5460K Free
> Swap: 512M Total, 192K Used, 512M Free

Looks fine to me -- Buf and Cache aren't supposed to use all available
free memory, since the majority of disk cache lives in active and
inactive pages along with everything else, ala:

Mem: 2333M Active, 1152M Inact, 177M Wired, 163M Cache, 112M Buf, 5432K Free

This system's doing >300 queries/second on a pretty sizable database,
with MySQL using around 450MB; FreeBSD's happily using the rest to
cache indexes and data.  "Cache" and "Buf" are different beasts for
things like filesystem metadata, VM magic, etc. You can tweak them with
sysctl's under the vfs and vm trees, but I wouldn't recommend it without
knowing what you're doing (which appears to involve reading a lot of
kernel source code).

Don't forget to come back and teach the rest of us when you do ;)

> and expected to see subsequent SELECTs cause mysqld become CPU bound,
> as data from the database should now be in the buffer cache. However,
> this is not the case. I have 2GB of inactive memory, but a small
> buffer cache and a disk bound mysqld :(

Are you sure it's disk bound and not just slow?  Are you using InnoDB
and O_DIRECT (which supposedly bypasses most disk caching)?  Have you
tried an EXPLAIN on your select to see if it's effecient?  MySQL will
write temp tables out to disk to do things like filesorts if your result
sets get very large, and would seem the most likely problem if you're
still disk bound on reads with that much memory.

> Are there some tunables I should look at to allow the buffer cache to
> grow?  All the searches I did suggested that the vm system should look
> after this itself.

Yup; I'd focus your tuning efforts on making sure you have the right
indexes set up, and that they're being used as you expect, as well as
looking at what memory MySQL itself is using for sorts, temp tables,
etc.

-- 
Thomas 'Freaky' Hurst
    http://hur.st/