On Mon, Sep 8, 2008 at 12:47 AM, Dan Nelson <[EMAIL PROTECTED]> 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.
