1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

MySQL Key Buffer Usage greater than total Index size?

Discussion in 'MySQL' started by tflight, Nov 11, 2006.

  1. #1
    I've been trying to adjust the Key Buffer size in my MySQL server to make it as efficient as possible. So I totaled up the total size of all indexes across all databases on my server. They totaled about 41 Mb. Giving myself a little room to grow I set my Key Buffer size at 64 Mb.

    So if I understand all this correctly, since my Key Buffer size is greater than the total size of all indexes in all tables across all databases, the Key Buffer usage should never fill, right? Once all indexes are used my key buffer usage would hold at 41 Mb out of 64 Mb, right?

    I restart the MySQL server and the key buffer usage is initially 0. As the databases are queried the usage starts to grow as indexes are added to the Key Buffer. How is it possible for the Key Buffer usage to go above the total size of all of my indexes?

    I've got 41 Mb of indexes, 64 Mb allocated to the Key Buffer, yet after a day or so of running my Key Buffer fills the total 64 Mb I've allocated to it. How is that possible?
     
    tflight, Nov 11, 2006 IP
  2. Scolls

    Scolls Guest

    Messages:
    70
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Have you tried using explain on your more complicated queries? I know you're thinking "What? What's my queries got to do with anything?"

    It's this: If you have queries that are using temporary, then indexes will be created for these temporary disk tables too, and there's what's probably using up that "extra" key buffer. ;)

    Use:
    explain my_select_query

    Then have a look in the "extra" column at the end, and see if it says "using temporary". If you have some really large result sets, you might use really large temporaries too. Add a high load to it, and maybe that's where your problem lies.

    Hope this helps you!
     
    Scolls, Dec 3, 2006 IP
    tflight likes this.
  3. tflight

    tflight Peon

    Messages:
    617
    Likes Received:
    38
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Ahhhh! Yes, quite a few of the queries use temporary. I never considered those temp tables and their indexes. I'll go check those out. Thanks!
     
    tflight, Dec 3, 2006 IP
  4. Scolls

    Scolls Guest

    Messages:
    70
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Pleasure! Let us know how it goes. :)
     
    Scolls, Dec 4, 2006 IP