This section covers the server variables that are discussed in the study guide. There are many more system variables that are not shown here. For a full list, see the MySQL Reference Manual. Also note that the exam might not be limited to the variables discussed here.
back_log:
The number of outstanding connection requests MySQL can have. This comes into play when the main MySQL thread gets very many connection requests in a very short time. You need to increase this only if you expect a large number of connections in a short period of time.
datadir:
The location of the data directory.
innodb_buffer_pool_size:
The size of the memory buffer InnoDB uses to cache data and indexes of its tables. The larger you set this, the less disk I/O is needed to access data in tables. On a dedicated database server, you may set this parameter up to 80% of the machine physical memory size. Do not set it too large, though, because competition for physical memory might cause paging in the operating system.
innodb_flush_log_at_trx_commit:
Normally you set this to 1, meaning that at a transaction commit, the log is flushed to disk and the modifications made by the transaction become permanent, and survive a database crash. If you're willing to compromise this safety, and you're running small transactions, you may set this to 0 or 2 to reduce disk I/O to the logs. A value of 0 means that the log is only written to the log file and the log file flushed to disk approximately once per second. A value of 2 means the log is written to the log file at each commit, but the log file is only flushed to disk approximately once per second. The default value is 1 starting from MySQL 4.0.13; previously it was 0.
innodb_log_file_size:
Size of each log file in a log group in megabytes. Sensible values range from 1MB to 1/n-th of the size of the buffer pool (see earlier discussion), where n is the number of log files in the group. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. But larger log files also mean that recovery will be slower in case of a crash. The combined size of log files must be less than 4GB on 32-bit computers. The default value is 5MB.
join_buffer_size:
The size of the buffer that is used for joins that do not use indexes. The buffer is allocated one time for each such join between two tables. Increase this value to get a faster join when adding indexes is not possible. (Normally, the best way to get fast joins is to add indexes.)
key_buffer_size:
Index blocks are buffered and are shared by all threads. key_buffer_size is the size of the buffer used for index blocks.
Increase the value of key_buffer_size to as much as you can afford to get better index handling. 64MB on a 256MB machine that mainly runs MySQL is quite common. However, if you make this too large (for example, more than 50% of your total memory), your system might start to page and become extremely slow. Remember that because MySQL does not cache data reads, you'll have to leave some room for the operating system filesystem cache.
You can check the performance of the key buffer by issuing a SHOW STATUS statement and examining the variables Key_read_requests, Key_reads, Key_write_requests, and Key_writes. The Key_reads/Key_read_request ratio should normally be less than 0.01. The Key_write/Key_write_requests ratio is usually near 1 if you're using mostly updates and deletes, but might be much smaller if you tend to do updates that affect many rows at the same time, or if you're using DELAY_KEY_WRITE.
long_query_time:
How long a query can take in seconds before it is considered slow. Slow queries are written to the slow query log.
max_allowed_packet:
The maximum size of one packet. The message buffer is initialized to net_buffer_length bytes, but can grow up to max_allowed_packet bytes when needed. This value is small by default to catch big (possibly wrong) packets. You must increase this value if you're using big BLOB columns. It should be as big as the biggest BLOB you want to use. The protocol limit for max_allowed_packet is 1GB.
max_connections:
The number of simultaneous clients allowed. Increasing this value increases the number of file descriptors that mysqld requires.
max_heap_table_size:
Doesn't allow the creation of HEAP tables that are bigger than the value of this variable.
query_cache_limit:
The maximum size of individual query results that can be cached.
query_cache_size:
The memory allocated to store results from previously issued queries. If this is 0 (default), the query cache is disabled.
query_cache_type:
This may be set to one of the following values:
0 or OFF:
Don't cache or retrieve results
1 or ON:
Cache all results except SELECT SQL_NO_CACHE … queries
2 or DEMAND:
Cache only SELECT SQL_CACHE … queries
read_buffer_size:
Each thread that does a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential scans, you might want to increase this value.
read_rnd_buffer_size:
When reading rows in sorted order after a sort, the rows are read through this buffer to avoid disk seeks. It can improve ORDER BY by a lot if set to a high value. This is a thread-specific variable, so you should not set the value large globally, but just change it when running some specific large queries.
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.
table_cache:
The maximum number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires.
You can find out whether you need to increase the table cache by checking the Opened_tables variable. See SHOW VARIABLES (section A.1.45, "SHOW VARIABLES"). If this variable is big and you don't issue FLUSH TABLES a lot (which just forces all tables to be closed and reopened), you should increase the value of this variable.
For more information about the table cache, see the MySQL Reference Manual.
table_type:
The default table type.
tmp_table_size:
If an in-memory temporary table exceeds the value of this variable, MySQL will automatically convert it to an on-disk MyISAM table. Increase the value of tmp_table_size if you do many advanced GROUP BY queries and you have lots of memory.
