| Option Sets Variable | Yes, group_concat_max_len | ||||||||
| Variable Name | group_concat_max_len | ||||||||
| Variable Scope | Both | ||||||||
| Dynamic Variable | Yes | ||||||||
| Value Set |
| ||||||||
| Value Set |
|
The maximum allowed result length in bytes for the GROUP_CONCAT() function. The default is 1024.
2. join_buffer_size
| Option Sets Variable | Yes, join_buffer_size | ||||||||
| Variable Name | join_buffer_size | ||||||||
| Variable Scope | Both | ||||||||
| Dynamic Variable | Yes | ||||||||
| Value Set |
|
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. Normally, the best way to get fast joins is to add indexes. Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible. One join buffer is allocated for each full join between two tables. For a complex join between several tables for which indexes are not used, multiple join buffers might be necessary.
The maximum allowable setting for join_buffer_size is 4GB.
| Option Sets Variable | Yes, | ||||||
| Variable Name |
| ||||||
| Variable Scope | Global | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set |
|
Index blocks for MyISAM tables are buffered and are shared by all threads. key_buffer_size is the size of the buffer used for index blocks. The key buffer is also known as the key cache.
The maximum allowable setting for key_buffer_size is 4GB on 32-bit platforms. As of MySQL 5.0.52, values larger than 4GB are allowed for 64-bit platforms (except 64-bit Windows, for which large values are truncated to 4GB with a warning). The effective maximum size might be less, depending on your available physical RAM and per-process RAM limits imposed by your operating system or hardware platform. The value of this variable indicates the amount of memory requested. Internally, the server allocates as much memory as possible up to this amount, but the actual allocation might be less.
Increase the value to get better index handling (for all reads and multiple writes) to as much as you can afford. Using a value that is 25% of total memory on a machine that mainly runs MySQL is quite common. However, if you make the value too large (for example, more than 50% of your total memory) your system might start to page and become extremely slow. MySQL relies on the operating system to perform file system caching for data reads, so you must leave some room for the file system cache. Consider also the memory requirements of other storage engines.
For even more speed when writing many rows at the same time, use LOCK TABLES. See Section 7.2.19, “Speed of INSERT Statements”.
You can check the performance of the key buffer by issuing a SHOW STATUS statement and examining the Key_read_requests, Key_reads, Key_write_requests, and Key_writes status variables. (See Section 12.5.5, “SHOW Syntax”.) The Key_reads/Key_read_requests ratio should normally be less than 0.01. The Key_writes/Key_write_requests ratio is usually near 1 if you are 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 are using the DELAY_KEY_WRITE table option.
The fraction of the key buffer in use can be determined using key_buffer_size in conjunction with the Key_blocks_unused status variable and the buffer block size, which is available from the key_cache_block_size system variable:
1 - ((Key_blocks_unused × key_cache_block_size) / key_buffer_size) This value is an approximation because some space in the key buffer may be allocated internally for administrative structures.
It is possible to create multiple MyISAM key caches. The size limit of 4GB applies to each cache individually, not as a group. See Section 7.4.6, “The MyISAM Key Cache”.
| Option Sets Variable | Yes, max_allowed_packet | ||||||
| Variable Name | max_allowed_packet | ||||||
| Variable Scope | Both | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set |
|
The maximum size of one packet or any generated/intermediate string.
The packet message buffer is initialized to net_buffer_length bytes, but can grow up to max_allowed_packet bytes when needed. This value by default is small, to catch large (possibly incorrect) packets.
You must increase this value if you are using large BLOB columns or long strings. It should be as big as the largest BLOB you want to use. The protocol limit for max_allowed_packet is 1GB. The value should be a multiple of 1024; non-multiples are rounded down to the nearest multiple.
| Option Sets Variable | Yes, | ||||
| Variable Name |
| ||||
| Variable Scope | Global | ||||
| Dynamic Variable | Yes | ||||
| Value Set |
|
The number of simultaneous client connections allowed. By default, this is 100. See Section B.1.2.7, “Too many connections”, for more information.
| Option Sets Variable | Yes, max_join_size | ||||||
| Variable Name | max_join_size | ||||||
| Variable Scope | Both | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set |
|
Do not allow SELECT statements that probably need to examine more than max_join_size rows (for single-table statements) or row combinations (for multiple-table statements) or that are likely to do more than max_join_size disk seeks. By setting this value, you can catch SELECT statements where keys are not used properly and that would probably take a long time. Set it if your users tend to perform joins that lack a WHERE clause, that take a long time, or that return millions of rows.
Setting this variable to a value other than DEFAULT resets the value of sql_big_selects to 0. If you set the sql_big_selects value again, the max_join_size variable is ignored.
If a query result is in the query cache, no result size check is performed, because the result has previously been computed and it does not burden the server to send it to the client.
This variable previously was named sql_max_join_size.
| Option Sets Variable | Yes, | ||||||
| Variable Name |
| ||||||
| Variable Scope | Both | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set |
|
The cutoff on the size of index values that determines which filesort algorithm to use. See Section 7.2.13, “ORDER BY Optimization”.
| Option Sets Variable | Yes, max_user_connections | ||||
| Variable Name | max_user_connections | ||||
| Variable Scope | Both | ||||
| Dynamic Variable | Yes | ||||
| Value Set |
|
The maximum number of simultaneous connections allowed to any given MySQL account. A value of 0 means “no limit.”
Before MySQL 5.0.3, this variable has only global scope. Beginning with MySQL 5.0.3, it also has a read-only session scope. The session variable has the same value as the global variable unless the current account has a non-zero MAX_USER_CONNECTIONS resource limit. In that case, the session value reflects the account limit.
| Option Sets Variable | Yes, query_alloc_block_size | ||||||||
| Variable Name | query_alloc_block_size | ||||||||
| Variable Scope | Both | ||||||||
| Dynamic Variable | Yes | ||||||||
| Value Set |
| ||||||||
| Value Set |
|
The allocation size of memory blocks that are allocated for objects created during statement parsing and execution. If you have problems with memory fragmentation, it might help to increase this a bit.
| Option Sets Variable | Yes, query_cache_limit | ||||||||
| Variable Name | query_cache_limit | ||||||||
| Variable Scope | Global | ||||||||
| Dynamic Variable | Yes | ||||||||
| Value Set |
| ||||||||
| Value Set |
|
Don't cache results that are larger than this number of bytes. The default value is 1MB.
| Option Sets Variable | Yes, | ||||||||
| Variable Name |
| ||||||||
| Variable Scope | Global | ||||||||
| Dynamic Variable | Yes | ||||||||
| Value Set |
| ||||||||
| Value Set |
|
The amount of memory allocated for caching query results. The default value is 0, which disables the query cache. The allowable values are multiples of 1024; other values are rounded down to the nearest multiple. Note that query_cache_size bytes of memory are allocated even if query_cache_type is set to 0. See Section 7.5.4.3, “Query Cache Configuration”, for more information.
The query cache needs a minimum size of about 40KB to allocate its structures. (The exact size depends on system architecture.) If you set the value of query_cache_size too small, you'll get a warning, as described in Section 7.5.4.3, “Query Cache Configuration”.
| Option Sets Variable | Yes, | ||||||||
| Variable Name |
| ||||||||
| Variable Scope | Both | ||||||||
| Dynamic Variable | Yes | ||||||||
| Value Set |
| ||||||||
| Value Set |
|
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. See Section B.1.4.4, “Where MySQL Stores Temporary Files”.
| Option Sets Variable | Yes, | ||||||
| Variable Name |
| ||||||
| Variable Scope | Global | ||||||
| Dynamic Variable | Yes | ||||||
| Deprecated | 5.1.3, by | ||||||
| Value Set |
|
The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. You can check whether you need to increase the table cache by checking the Opened_tables status variable. See Section 5.1.6, “Server Status Variables”. If the value of Opened_tables is large and you don't do FLUSH TABLES often (which just forces all tables to be closed and reopened), then you should increase the value of the table_cache variable. For more information about the table cache, see Section 7.4.8, “How MySQL Opens and Closes Tables”.
| Version Introduced | 5.0.10 | ||||||
| Option Sets Variable | Yes, table_lock_wait_timeout | ||||||
| Variable Name | table_lock_wait_timeout | ||||||
| Variable Scope | Global | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set |
|
Specifies a wait timeout for table-level locks, in seconds. The default timeout is 50 seconds. The timeout is active only if the connection has open cursors. This variable can also be set globally at runtime (you need the SUPER privilege to do this). It's available as of MySQL 5.0.10.
| Option Sets Variable | Yes, tmp_table_size | ||||||
| Variable Name | tmp_table_size | ||||||
| Variable Scope | Both | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set |
|
The maximum size of internal in-memory temporary tables. (The actual limit is determined as the smaller of max_heap_table_size and tmp_table_size.) If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table. Increase the value of tmp_table_size (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory. This variable does not apply to user-created MEMORY tables.
Additionally, the formula should probably be extended:
total =
innodb_buffer_pool_size +
key_buffer_size +
innodb_additional_mem_pool_size +
innodb_log_buffer_size +
max_connections *
(sort_buffer_size +
read_buffer_size +
binlog_cache_size +
maximum_thread_stack_size);
