12 个比较有用的 MySQL 配置参数

不错的一个文章,有时间慢慢翻译。

  • Altering Index Buffer Size (key_buffer)

This variable controls the size of the buffer used when handling table indices (both read and write operations). The MySQL manual recommends that this variable be increased to as much as you can afford to ensure you get the best performance on indexed tables, and recommends a value equivalent to about 25 percent of the total system memory. This is one of the more important MySQL configuration variables and if you’re interested in optimizing and improving performance, trying different values for the key_buffer_size variable is a good place to start.

  • Altering Table Buffer Size (read_buffer_size)

When a query requires a table to be scanned sequentially, MySQL allocates a memory buffer to this query. The read_buffer_size variable controls the size of this buffer. If you find that sequential scans are proceeding slowly, you can improve performance by increasing this value, and hence the size of the memory buffer.

  • Setting the Number of Maximum Open Tables (table_cache)

This variable controls the maximum number of tables MySQL can have open at any one time, and thus controls the server’s ability to respond to incoming requests. This variable is closely related to the max_connections variables – increasing this value allows MySQL to keep a larger number of tables open, just as increasing max_connections increases the number of allowed connections. Consider altering this value if you have a high-volume server which receives queries on multiple different databases and tables.

  • Activating the Query Cache (query_cache_type)

MySQL 4.x includes a query cache which can improve query response time by caching the results of frequently-used queries and returning the cached data on subsequent calls to the same query. Whether or not the query cache is used is controlled by the query_cache_type variable, which may be set to ON, OFF or DEMAND. It’s generally a good idea to turn this on, especially if you have large numbers of identical SELECT queries being executed repeatedly on the server.
Tip: You can also use the query_cache_size variable to control the amount of memory allocated to the MySQL query cache. Increase this value for high-volume servers.

  • Setting the Maximum Size Of A Join (max_join_size)

This variable sets the maximum number of rows that a SELECT query should scan when performing a table join. This is useful to catch badly-written queries that might end up scanning millions of rows, thus decreasing the server’s ability to satisfy other requests. Because this variable impacts all queries and users, it should be set only after careful consideration of what constitutes a legal query on your specific system.

  • Setting the Maximum Number of Permitted Connections (max_connections)

This variable controls the maximum number of incoming client connections MySQL can deal with at any one time. If your application is likely to experience large numbers of independent client connections simultaneously, it’s a good idea to increase this value to avoid the “Too many connections” error.

  • Deciding a Time Limit for Long Queries (long_query_time)

MySQL comes with a so-called slow query log, which automatically logs all queries that do not end within a particular time limit. This log is useful to track inefficient or misbehaving queries, and to find targets for optimization algorithms. The long_query_time variable controls this maximum time limit, in seconds.
Tip: To avoid false positives, set this to a relatively higher value on systems that experience heavy load.

  • Altering the Transaction Isolation Level (tx_isolation)

This variable controls the transaction isolation level, or the extent to which concurrent transactions and the changes they make to a table are “visible” to each other. There are four transaction levels in an ACID-compliant transactional RDBMS like MySQL, and you can set MySQL to use any one of them with this variable.
Note: It’s important to remember that this variable controls the global, default transaction level. Individual clients can, of course, override this value on a per-transaction basis if needed.

  • Activating the Binary Log (log_bin)

MySQL’s binary log keeps track of all queries that alter the data in the database. It’s mostly used to efficiently and accurately perform replication operations, and to restore the system to a stable snapshot in the event of a failure or transaction interruption. The log_bin variable enables this log.

  • Auto-Executing SQL Code on Client Connection (init_connect)

This variable can be used to run SQL commands on the server for every client that successfully opens a connection. This variable is usually set to one or more SQL commands, which are executed by the server to perform client-specific initialization.

  • Setting a Timeout for Interactive Connections (interactive_timeout)

This is a particularly useful variable to set if your MySQL server receives numerous requests for interactive use. This variable controls how long the server should wait for activity on an interactive client connection before terminating the connection. Set this to a reasonable value—five minutes is usually fair, although you will want to reduce that if your server has a lot of clients requesting interactive connection.
Tip: You can use the connect_timeout variable to control how long MySQL waits for a client connection to be consummated before terminating it with an error.

  • Setting the Time Zone (system_time_zone)

This variable controls the time zone MySQL uses for all its date/time bookkeeping. It’s important to ensure that this variable is correctly set, as using the wrong time zone can affect the accuracy of values entered into DATE, TIME and TIMESTAMP fields, as well as flaw date arithmetic operations.