MySQL 服务器性能调优 Making sure your MySQL server flies

HOW MYSQL USES MEMORY MySQL uses memory for a variety of internal buffers and caches that influence how often it must access files that reside on disk. The more often it has to wait for a disk to respond, the slower it will be. As fast as

HOW MYSQL USES MEMORY

MySQL uses memory for a variety of internal buffers and caches that influence how often it must access files that reside on disk. The more often it has to wait for a disk to respond, the slower it will be. As fast as modern disk drives are, they're still an order of magnitude (or more) slower than RAM. And given the recent drops in memory prices, odds are pretty good that you can easily afford to add memory to a server if it will speed things up. Upgrading to faster disks should be a last resort.

MySQL's buffers and caches come in two flavors, global and per-thread:

GLOBAL: As its name suggests, these memory areas are allocated once and are shared among all of MySQL's threads. Two of the ones we'll look at are the key buffer and the table cache. Because these are shared buffers, the goal is to make them as large as possible (without unnecessarily taxing our resources).

PER-THREAD: These buffers allocate memory individually to queries as they need to perform particular operations, such as sorting or grouping. Incidentally, most of MySQL's buffers are allocated on this per-thread basis. The per-thread buffers we'll be looking at are the record buffer and the sort buffer.

Let's first examine what function each of the buffers serves and how to set and inspect their values. Then we'll look at how to examine MySQL's performance counters and judge whether or not changes you make are having any significant impact.

KEY BUFFER

The key buffer is where MySQL caches index blocks for MyISAM tables. Anytime a query uses an index, MySQL will first check to see if the relevant index is in memory or not. The key_buffer parameter in your my.cnf file controls how large the buffer is allowed to get. Once the buffer is full, MySQL will make room for new data by replacing older data that hasn't been used recently. (See the Using a my.cnf File sidebar if you're not familiar with MySQL's configuration file.)

The size of the key buffer appears as key_buffer_ size in the output of SHOW VARIABLES. With a 384 MB key buffer, you'd see:

| key_buffer_size         | 402649088

As a general recommendation, on a dedicated MySQL server, you should allocate somewhere between 20 percent and 50 percent of your RAM for MySQL's key buffer. If you have a gigabyte of memory, start with something like:

set-variable = key_buffer=128M

or even:

set-variable = key_buffer=256M

in your my.cnf file and see if you notice a difference. Odds are that you will.

If you were only allowed to adjust one parameter on your MySQL server, the key buffer would be the one to try. Indexes are so important to the overall performance of any database server that it's hard to go wrong with making more room in memory for them.

If you do not specify a size for the key buffer, MySQL will use its default size, which is in the neighborhood of 8 MB. Of course, it makes little sense to set the value for your key buffer too high. Doing so could potentially starve the operating system of memory that it needs for disk buffering and other tasks.

It might also be helpful to look at how much index data you have on disk. Simply find the size of all the .MYI files under MySQL's data directory:

$ du -sh */*.MYI

Knowing how much index data you have, you can better judge how much benefit you are likely to see from increasing the size of the key buffer beyond a certain point. If some of your index files belong to tables that are infrequently used, there is little point in making room for them. But it's clear that any large or medium-sized database will normally benefit from a larger key buffer.