List:General Discussion« Previous MessageNext Message »
From:David Ashley Date:September 8 2008 12:56pm
Subject:Re: How many key buffer size should be allocate?
View as plain text  
On Mon, Sep 8, 2008 at 12:47 AM, Dan Nelson <dnelson@stripped> wrote:

> In the last episode (Sep 08), Yi Wang said:
> > According to the mysql manual, I changed key_buffer_size from 8M to
> > 512M. But In mysql administrator, key buffer hitrate seems stay
> > unchanged. And key buffer usage always below 4M.
> >
> > The total db size is about 200M. And the system's load is slight. key
> > buffer size is 8M before changing. And the hitrate is about 100%
> > intermittently.
>
> If your database is only 200M, then your indexes are probably less than
> half that (if you total up all of your .MYI files, that's the total
> size of all your indexes).  Setting key_buffer_size larger than your
> total index size doesn't do any good.
>
The other thing that struck me about this particular application is that
with typical RAM size for a modern computer, and with a lightly loaded
server, the ENTIRE database will effectively be cached:

http://en.wikipedia.org/wiki/Page_cache

Because of the small database size, I'm not confident that using the key
cache features of MySQL will lead to a significant performance increase.

There will be some performance increase, naturally, because it is cheaper to
find index values locally in MySQL rather than make the fseek() calls or
whatever through the OS.  But I also have to consider that the OS calls
usually involve an open file handle and primarily integer operations, and
are highly optimized.  I'm just not sure what kind of a performance increase
would result from using the key cache features of MySQL for such a small
database.

The OP should definitely explore these features, for (a) academic value, (b)
future expansion of the database, (c) other databases, (d) in case he is
running the database on an older server, and (e) in case the server is not
lightly loaded.  But in general for a 200M database ... I'm not sure of the
benefit.

I've never actually tried any of this, so I could be totally wrong about the
performance implications with a small database ...

Dave.

Thread
How many key buffer size should be allocate?Yi Wang8 Sep
  • Re: How many key buffer size should be allocate?Dan Nelson8 Sep
    • Re: How many key buffer size should be allocate?Yi Wang8 Sep
    • Re: How many key buffer size should be allocate?David Ashley8 Sep