New Technologies

  • Java
  • Javascript
  • DTML
  • Dot Net
  • ASP .Net
  • C# .Net
  • PHP
Your Ad Here

Friday, February 27, 2009

How to Best Make use of Mysql Memory Variables

1.  group_concat_max_len

Option Sets Variable

Yes, group_concat_max_len

Variable Name

group_concat_max_len

Variable Scope

Both

Dynamic Variable

Yes

Value Set

Platform Bit Size

32

Type

numeric

Default

1024

Range

4-4294967295

Value Set

Platform Bit Size

64

Type

numeric

Default

1024

Range

4-18446744073709547520

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

Platform Bit Size

64

Type

numeric

Default

131072

Range

8200-18446744073709547520

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.

3.  key_buffer_size

Option Sets Variable

Yes, key_buffer_size

Variable Name

key_buffer_size

Variable Scope

Global

Dynamic Variable

Yes

Value Set

Type

numeric

Default

8388608

Range

8-4294967295

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”.

 

 

4.  max_allowed_packet

Option Sets Variable

Yes, max_allowed_packet

Variable Name

max_allowed_packet

Variable Scope

Both

Dynamic Variable

Yes

Value Set

Type

numeric

Default

1048576

Range

1024-1073741824

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.

 

 

5.  max_connections

Option Sets Variable

Yes, max_connections

Variable Name

max_connections

Variable Scope

Global

Dynamic Variable

Yes

Value Set

Type

numeric

Default

100

The number of simultaneous client connections allowed. By default, this is 100. See Section B.1.2.7, “Too many connections, for more information.

6.    max_join_size

Option Sets Variable

Yes, max_join_size

Variable Name

max_join_size

Variable Scope

Both

Dynamic Variable

Yes

Value Set

Type

numeric

Default

4294967295

Range

1-4294967295

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.

7.    max_length_for_sort_data

Option Sets Variable

Yes, max_length_for_sort_data

Variable Name

max_length_for_sort_data

Variable Scope

Both

Dynamic Variable

Yes

Value Set

Type

numeric

Default

1024

Range

4-8388608

The cutoff on the size of index values that determines which filesort algorithm to use. See Section 7.2.13, “ORDER BY Optimization”.

8.     max_user_connections

Option Sets Variable

Yes, max_user_connections

Variable Name

max_user_connections

Variable Scope

Both

Dynamic Variable

Yes

Value Set

Type

numeric

Range

1-4294967295

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.

9.    query_alloc_block_size

Option Sets Variable

Yes, query_alloc_block_size

Variable Name

query_alloc_block_size

Variable Scope

Both

Dynamic Variable

Yes

Value Set

Platform Bit Size

32

Type

numeric

Default

8192

Range

1024-4294967295

Value Set

Platform Bit Size

64

Type

numeric

Default

8192

Range

1024-18446744073709547520

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.

10.                      query_cache_limit

Option Sets Variable

Yes, query_cache_limit

Variable Name

query_cache_limit

Variable Scope

Global

Dynamic Variable

Yes

Value Set

Platform Bit Size

32

Type

numeric

Default

1048576

Range

0-4294967295

Value Set

Platform Bit Size

64

Type

numeric

Default

1048576

Range

0-18446744073709547520

Don't cache results that are larger than this number of bytes. The default value is 1MB.

11.                      query_cache_size

Option Sets Variable

Yes, query_cache_size

Variable Name

query_cache_size

Variable Scope

Global

Dynamic Variable

Yes

Value Set

Platform Bit Size

32

Type

numeric

Default

0

Range

0-4294967295

Value Set

Platform Bit Size

64

Type

numeric

Default

0

Range

0-18446744073709547520

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”.

12.                      sort_buffer_size

Option Sets Variable

Yes, sort_buffer_size

Variable Name

sort_buffer_size

Variable Scope

Both

Dynamic Variable

Yes

Value Set

Platform Bit Size

32

Type

numeric

Default

2097144

Max Value

4294967295

Value Set

Platform Bit Size

64

Type

numeric

Default

2097144

Max Value

18446744073709547520

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”.

13.                      table_cache

Option Sets Variable

Yes, table_cache

Variable Name

table_cache

Variable Scope

Global

Dynamic Variable

Yes

Deprecated

5.1.3, by table_open_cache

Value Set

Type

numeric

Default

64

Range

1-524288

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”.

14.                      table_lock_wait_timeout

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

Type

numeric

Default

50

Range

1-1073741824

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.

15.                      tmp_table_size

Option Sets Variable

Yes, tmp_table_size

Variable Name

tmp_table_size

Variable Scope

Both

Dynamic Variable

Yes

Value Set

Type

numeric

Default

system dependent

Range

1024-4294967295

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);

 

No comments:

Your Ad Here