MySQL optimizer: Settings for MySQL service run parameters

can be set with administrator login under the mysql> command line, the settings are temporary.

If you want to take effect permanently, modify the relevant parameters in the [mysqld] in the /etc/my.cnf file:

#vim/ect/my.cnf

[Mysqld]

......

Here is a detailed explanation of the settings for the relevant operating parameters:

1. number of concurrent connections settings max_connections

set formula for maximum concurrent connections: The maximum number of connections that have ever been/the maximum number of connections to be set *100% approximately equal to 85% ,15% to cope with burst traffic

Mysql> Show variables like "max_connections";

Show global status Like "Max_used_connections"; View the maximum number of connections that have ever been made

Flush status;// Clear the maximum number of connections that have ever been made, re-count

Temporary settings:

mysql> set global max_connections=300;

Permanent settings:

Vim/etc/my.cnf

[Mysqld]

max_connections=300

2. Connection Database server time-out setting connect_timeout wait_timeout

connect_timeout=10 ( seconds ) timeout to wait for the handshake when getting a connection, only valid at logon

wait_timeout=28800(seconds) The number of seconds the server waits for action to close a connection, and the default 28800 seconds to disconnect the wait time

query:showvariables like "Connect_timeout"

Temporary settings:setglobal connect_timeout=7;// long will consume system resources, too short will frequently respond to requests will also consume system resources, generally use the default value

query:showvariables like "Wait_timeout";

Temporary settings:set wait_timeout=3600;// cannot be too short, otherwise the client data will be disconnected if not finished reading

3. set the number of threads that can be reused to save in the cache thread_cache_size

Thread_cache_size// cache threads can speed up access without waiting for the user to access the temporary open thread. If the amount of traffic is small, the number of threads will be wasted resources, the number of high-traffic threads small access is slow, you should set the appropriate number of threads.

query:showvariables like "thread_cache_size" default:9

Temporary settings:setglobal thread_cache_size=8

Show Global status like "thread%";

+-------------------+-------+

| variable_name | Value |

+-------------------+-------+

| threads_cached |   1 | the value of the threads_cacheed_size

| threads_connected |   1 | number of connections already

| threads_created |   2 | number of threads created

| threads_running |   1 | number of connections that are running

+-------------------+-------+

The gap between the threads_connected and threads_created values is too large to indicate that the Thread_cache_size value setting is too small.

4.  Set the number of open tables for all threads    Table_open_cache default: five

view:mysql>show variables like "Table_open_cache";

Temporary settings:mysql>set global table_open_cache=50;

Mysql> Show global status Like "open%table%";

+--------------------------+-------+

| variable_name | Value |

+--------------------------+-------+

| open_table_definitions | 3 |

| Open_tables |   3 | Number of open tables

| opened_table_definitions | 73 |

| Opened_tables | 73 | The number of open tables, this value is too large,the Table_open_cache value may be too small

+--------------------------+-------+

Table_open_cache Value setting:open_tables/table_open_cache*100%<=95% is reasonable

5.  to set the size of an index buffer   key_buffer_size default: 8388608 byte =8m

view:mysql>show variables like "key_buffer_size";

Mysql> Show global status Like "key_read%";

+-------------------+-------+

| variable_name | Value |

+-------------------+-------+

| key_read_requests |   8 | Total number of index read requests

| Key_reads |   4 | The number of indexes read directly from the hard disk is not found in the index buffer

+-------------------+-------+

Key_buffer_size Value setting: key_reads/key_read_requests *100% The smaller the ratio the better

6. Each thread that needs to be sorted is assigned to a buffer of size, which increases this value to accelerate the order by and group by Operation sort_buffer_size

view:mysql>show variables like "sort_buffer_size"; Default:256K

7. The length of the buffer reserved for read operations that read data from the data table order    read_buffer_size Default:128K

Mysql> Show variables like "read_buffer_size";

8. in a particular order (for example, using the ORDER by clause of the query) output query results

read_rnd_buffer_size Default:512K

SELECT * from Mysql.usertab order by age;

Mysql>show variables like "read_rnd_buffer_size";

This article is from the IT Technology Learning blog, so be sure to keep this source http://learningit.blog.51cto.com/9666723/1790376

MySQL optimizer: Settings for MySQL service run parameters