Announcement

Announcement Module
Collapse
No announcement yet.

The best key_buffer size

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • The best key_buffer size

    I've got a db-server, dual quad-core, MySQL 5, with 8Gb of memory (64bits CentOS). My db is arround 70Gb and some tables have indexes around 2Gb.

    I am playing arround with the mysql config-values, using the tuning-script, because performance is going down, for example it's using all the swap memory of the OS and the load will jump up and the whole server will become unresponsive at some times. I guess it has to do with the key_buffer size, which is setup pretty high right now.

    What is the best size for the key_buffer in this scenario? Should it be big enough to hold more then one index or doesn't it work that way?

    Thanks!

    P.S.: A quick summary of the config-values:

    key_buffer = 5020Mmax_allowed_packet = 1024Mtable_cache = 4096sort_buffer_size = 512Mread_buffer_size = 512Mmyisam_sort_buffer_size = 512Mthread_cache = 32query_cache_limit = 16Mquery_cache_size = 256Mthread_concurrency = 8net_read_timeout = 3600net_write_timeout = 3600group_concat_max_len = 10485760max_heap_table_size = 128Mmax_connections = 250tmp_table_size = 512Mjoin_buffer_size = 512Mopen_files_limit = 25000

  • #2
    So I assume you're only using MyISAM tables. If you're using InnoDB, you'll definitely need to use some of that memory for the InnoDB buffer pool.

    The key buffer is used to cache index pages. You can tell if its being effectively used by checking the ratio of Key_reads to Key_read_requests from the SHOW GLOBAL STATUS command. The popular rule of thumb is that this ratio should be less than 0.01. When Key_reads is much lower than Key_read_requests, it means you're going to disk to fetch index pages less often, which is good.

    The problem with having a very large key buffer is that you're not just accessing indexes, you need to read the actual rows that the indexes point to. MySQL doesn't cache these data pages in memory for a MyISAM table, instead it relies on the OS file system cache to speed things up. This cache just uses memory not in use by other processes, so by decreasing MySQL's memory footprint, you'll make more memory available for caching data.

    So if your key_reads:key_read_requests is very small, you can try lowering the key buffer size until you get to the 0.01 ratio. But keep in mind the first goal here should be to stop the system from swapping heavily. You need to find out why its happening and stop it.

    Also, your max_allowed_packet seems huge, though I don't think that could cause that much of a problem.

    Really, your configuration depends on your workload. A tuning script can give you a good starting point, buts its no replacement for really understanding the configuration options and using your unique knowledge of your application needs to make intelligent configuration decisions.

    Comment


    • #3
      Quote:

      So I assume you're only using MyISAM tables. If you're using InnoDB, you'll definitely need to use some of that memory for the InnoDB buffer pool.
      Most of them are MyISAM tables, we got a few innoDB tables, but they aren't very big.

      Quote:

      The key buffer is used to cache index pages. You can tell if its being effectively used by checking the ratio of Key_reads to Key_read_requests from the SHOW GLOBAL STATUS command. The popular rule of thumb is that this ratio should be less than 0.01. When Key_reads is much lower than Key_read_requests, it means you're going to disk to fetch index pages less often, which is good.
      Didn't know that, I've checked those values and they look like this:
      Key_read_requests 23474418144
      Key_reads 4997410

      Quote:

      So if your key_reads:key_read_requests is very small, you can try lowering the key buffer size until you get to the 0.01 ratio. But keep in mind the first goal here should be to stop the system from swapping heavily. You need to find out why its happening and stop it.
      Whenever I look at top I see MySQl is taking all the swap memory in some cases. No other big processes run on the server, only MySQL. But I'm guessing lowering the key_buffer is worth a try?!

      Quote:

      Also, your max_allowed_packet seems huge, though I don't think that could cause that much of a problem.
      True, but I tried to lower that, but it did not help. The reason this is that high is because we are running two slaves which complained about this value being too low.

      Comment


      • #4
        It looks like you've got some room to lower the key buffer size and free up some memory. Other interesting things to look at regarding the key buffer are Key_blocks_used and Key_blocks_unused, though be aware that just because you can use the entire key buffer, doesn't mean you need to.

        That being said, I overlooked what your real problem probably is:


        sort_buffer_size = 512Mread_buffer_size = 512Mjoin_buffer_size = 512M


        Quoting the manual:

        sort_buffer_size: Each thread that needs to do a sort allocates a buffer of this size. Increase this value for faster ORDER BY or GROUP BY operations.

        read_buffer_size: Each thread that does a sequential scan allocates a buffer of this size (in bytes) for each table it scans.

        join_buffer_size: The size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans.


        Definitely turn these guys down, they are used as needed by each connection.

        Comment


        • #5
          vgatto wrote on Fri, 13 February 2009 17:16



          That being said, I overlooked what your real problem probably is:


          sort_buffer_size = 512Mread_buffer_size = 512Mjoin_buffer_size = 512M


          Quoting the manual:

          sort_buffer_size: Each thread that needs to do a sort allocates a buffer of this size. Increase this value for faster ORDER BY or GROUP BY operations.

          read_buffer_size: Each thread that does a sequential scan allocates a buffer of this size (in bytes) for each table it scans.

          join_buffer_size: The size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans.


          Definitely turn these guys down, they are used as needed by each connection.

          True, I am gonna play with these values. Weird thing is, these configuration settings worked perfectly on the old server with MySQL4. Not sure why the other guy set them so high though..

          Comment


          • #6
            You might benefit from reducing your key_buffer size if your "hot" data is only a few GB in size. This would free-up RAM to keep the hot data in the OS's filesystem cache. Anything over 25% or so of your RAM as a key_buffer is likely pushing hot data out of the filesystem cache. Try setting it to 2G and see how that affects performance.

            I'd seriously look at increasing the RAM in your system. 2GB chips are cheap, and if you have 8 slots it's well worth the money to upgrade.

            Comment

            Working...
            X