MySQL设置优化[转载]

Key buffer size

The the most important option if you are using lots of MyISAM tables is key_buffer. You should set it up to be 25% to 50% of available memory. You have to leave memory for OS cache as well because MyISAM uses OS cache to cache table data. Even if you are not using MyISAM tables, still you would be better of allocating 32M to 64M of memory to the key buffer, because internally MySQL uses MyISAM tables for chores such as creating temporary tables.

Thread cache

The thread cache holds threads that are not being used by any connection, but are available to be used. A thread is created for each connection to the MySQL server, and of course thread creation takes time and resources. The way MySQL works is, when a new connection is requested MySQL first looks into the thread cache to see if there is any ideal thread, if it finds one it uses that one for the connection, if it doesn’t find any it has to create the thread. Obviously MySQL response would be much faster if its just using threads from the cache and not creating them.
The number of threads in the cache is dictated by the thread_cache_size variable. This variable should have an adequate value, according to the connections requests per second that your server is receiving.
A good approach is to look at the Threads Created and make sure that the number of threads created per second does not exceed 1.
Each thread in a thread cache typically uses 128KB of memory, so its not really memory intensive and high numbers here won’t hurt you. But don’t keep it at more than 1000 because some OS don’t handle large number of threads well.

Table cache

Just like creating threads can be expensive, similarly opening tables is expensive too. Of course if the the table definition and data is already in the cache, then it can be queried much faster, as compared to opening the table file for each query. Keep an eye on the Opened Tables status variable and if you see the number large, increase the value of your table cache. Also keep in mind that every connection to the MySQL server uses its own slot from the table cache, so generally a good value is calculated by multiplying the number of tables by a factor of 10.
The table cache doesn’t consume much memory typically 256KB per table cache slot. The configuration variable for table cache size is table_cache

Query cache size

Query cache is a very important functionality for read-intensive applications. Query cache stores complete result-sets of SELECT queries, enabling very fast retrievals on query cache-hits. The query cache is stored completely in memory, which of course is the reason for very fast retrievals from query cache. The configuration variable that governs the size of the query cache is query_cache_size. A value between 64M to 1024M is sufficient for most of the use cases.

Sort buffer size

Sort buffer size controls who big the buffer should be for filesorts. Sort buffer size is one of those configuration setting that will make the performance of MySQL server suffer, if set very high. And there are reasons for this. A sort buffer is all at once by the MySQL server although it many not be needed to be entirely filled (say if you have a sort buffer of 1MB and you only need 100KB for sorting, still MySQL will use the whole 1M and hence waste memory). Besides sort buffer is one of those settings that is used per connection, meaning if you have 100 connections open and each performing a sort query and the size of your sort buffer is 1MB, then the amount of memory being utilized for sorting would be 100 x 1MB = 100MB.
Hence, its best to keep this value small. The best method is to keep it small between 128KB to 1M and increase it only during the length of the connection if needed.

Read buffer size

Read buffer size is another of those variables whose value you should keep as small as possible. MySQL doesn’t allocate any memory from this buffer until a query needs it, but when it does it allocates the entire chunk of memory even if the entire chunk is needed or not. Hence large values here can really be a waste here.
Similar to the sort buffer size, the optimal way is to keep it small initially 128KB to 1MB, and then increase it only for the connection that needs more buffer memory.

Having gone through this article, I hope you will understand that allocating large amounts of memory is not always the right solution. You have to work on a case by case basis.
One more thing to keep in mind is that the best way to start changing the configuration is to change one or two values, and then run a benchmark test to see the effect of the change, this way you have a total grasp of how the configuration changes are effecting the performance of your server.

 

 

InnoDB buffer pool

InnoDB buffer pool has to be the most important part of any tuning activity. InnoDB relies heavily on the buffer pool, it uses it to cache indexes, row data, the adaptive hash index, the insert buffer and many other data structures used internally by InnoDB. The buffer pool is also used to buffer changes to data, so that write operations are delayed and so that they don’t have to be performed immediately on disk, thus improving the performance of write operations. Thus buffer pool is an integral part of InnoDB and its size has to be adjusted accordingly. Following is how you would set the size for different kinds of servers.

• Set the buffer pool size to 80% or more of available RAM for a dedicated InnoDB only machine.
• Set the size to upto 50% of available RAM on a dedicated MySQL machine if you have a mix of MyISAM and InnoDB tables.
• Set the size to 50% of RAM you have allocated to MySQL for a machine that is not dedicated to MySQL only.

As you can guess the more memory that you can allocate to the buffer pool, the more the performance gains.

The configuration variable that deals with the size of buffer pool is innodb_buffer_pool_size.

InnoDB log file size

Choosing the right size for the InnoDB log file size, is important both for the write operations and for having a decent recovery time after server crash. First let’s see how the log file size matters for write operations.
Let’s first consider what goes inside the log actually. When records are modified the change is not written back to the tablespace on the disk immediately, instead the change is recorded at the end of the log file on disk and the page is marked as dirty. Hence, what InnoDB is actually doing is it is using its log to convert the random disk I/O into sequential I/O.
Now when the log is full, the dirty page is written out to the tablespace in log order so that space can be freed up in the log file. Now suppose in the middle of some transactions the server crashes, obviously the write operations had only been recorded in the log file. So before the server can come back live again, it goes through a recovery phase, where the changes recorded in the log file are replayed. The more the entries in the log file, the longer its going to take for the server to recover.
Hence, you see how the log file size affects both the recovery time as well as the write performance. So there has to be some balance between the recovery time and write performance when choosing the right number for the log file size. Typically anything between 128M and 512M is a good value.

The configuration variable that deals with log file size is innodb_log_file_size.

InnoDB thread concurrency

InnoDB has been designed for high concurrency and works very efficiently for applications having high concurrency. But still you have to configure the amount of concurrency that you are going to allow.

The variable that deals with concurrency is innodb_thread_concurrency which limits how many threads can enter the kernel at once.

A value of 0 means there is no limit. Theoretically speaking a high value would mean high resource utilization, but as of MySQL 5.1, you are better off limiting the concurrency to somewhere between 4 and 8, because InnoDB does have issue with higher numbers. Although the latest version of InnoDB shipping with 5.5 is said to have fixed the issues. Following is a formula you could use to calculate the value of thread concurrency you should allow:

concurrency = 2 * (Number of CPUs + Number of Disks)

When to flush the InnoDB log buffer?

When any change is made to the data, the change is not written to the disk immediately, instead its recorded in a log buffer, which is a portion of memory that InnoDB allocates to buffer changes that have to be recorded in the log file. Of course the size of the log buffer can be changed through configuration variables, but that is not the point here. The more important thing that we are interested in (and that can have an impact on write performance) is when should the changes recorded in the log buffer be actually recorded in the log file on disk. InnoDB flushes the buffer to the log file on disk when a transaction is committed, or when the buffer gets full, or once per second – whichever event happens first. The buffer will be flushed when its full, or once per second, and you can’t change this behavior. But what you can change is “when a transaction is committed” part.

The configuration variable that controls this is innodb_flush_log_at_trx_commit.
Possible values are:

• 0: Do not flush the log buffer on transaction commit.
• 1: Flush the log buffer to the log file on disk, on every transaction commit. This is the default setting.
• 2: Flush the log buffer to OS cache, but not to the log file on disk.

The safest value out of the above is the value of 1 (flush on commit), which enforces ACID compliance and ensures the maximum data safety because every transaction commit is recorded on disk. But that also means that there is a overhead whenever a write operation is committed, remember that these write operations also include updates to data outside of a transaction, for example a simple UPDATE query. This can be an overkill in most of situations, because as I have mentioned above, InnoDB flushes the log every second anyway. So even if you set it 0 (no flush) or 2 (flush to OS cache), the only a possibility of any data loss is of loosing a transaction from last 1 second during a hardware failure or a MySQL server failure.

Hence if you really need full ACID compliance (say for a financial application), then choose 1, otherwise choose 0 or 2. More so if you have InnoDB tables as drop-in-place replacements for MyISAM tables then select a value of 0 or 2.

 

 

转自

http://www.ovaistariq.net/358/tuning-mysql-server-settings/

http://www.ovaistariq.net/496/tuning-innodb-configuration/