Shulou(Shulou.com)06/02 Report--

Mysql- server-extended configuration

Extended configuration

Max_connections

The maximum number of connections allowed, default 100, maximum 16384. This is adjusted according to performance. If a 3000 connection will result in insufficient resources for mysql, give it to 3000. Because if you give more, it will cause the resources of other connections to be preempted.

Recommendations:

According to the demand, the general 2-core 4G machine should fill in 1000JI 16-core 64G and fill in 5000.

After the test runs, query the current number of connections and server load. If the number of connections is full, but the load is not very large, you can increase the number of connections.

Query configuration: statusThreads is the number of connections

Online configuration:

Profile parameter: max_connections=5000

Connect_timeout

The timeout for establishing a three-way handshake may be caused by a link timeout (in seconds) caused by network problems on the client and server.

Query configuration:

Online configuration:

Profile parameter: connect_timeout=10

Interactive_timeout | wait_timeout

Parameters that control the maximum idle time of the connection. The default is 28800, that is, 8 hours, in seconds.

Wait_timeout controls non-interactions, such as links to java programs, and interactive_timeout controls interactions, such as operations performed by mysql commands.

Recommendations:

Usually 300 seconds is enough to prevent some links from faking death and taking up links without making an action order.

Query: show global variables like'% timeout%'

Online configuration: set global wait_timeout=300; | set global interactive_timeout=300

Configuration file: interactive_timeout = 300 | wait_timeout = 300

Net_retry_count

If a read or write communication port is interrupted, the number of times mysql attempts to connect before abandoning. This value should be set high on FreeBSD systems because FreeBSD internal interrupts are sent to all threads.

Query configuration:

Online configuration:

Profile parameter: net_retry_count = 100

Thread_concurrency

This variable is for Solaris systems and is set to twice the number of kernels.

If you set this variable, mysqld calls thr_setconcurrency (). This function causes the application to provide the desired number of threads to the threaded system running at the same time.

Query configuration:

Online configuration:

Profile parameter: thread_concurrency = 8

Thread_cache_size | thread_stack

Each client connection has a corresponding connection thread. A Thread Cache pool is implemented in MySQL to store idle connection threads instead of destroying them after completing the request.

In this way, when there is a new connection request, MySQL first checks to see if there is a free connection thread in the Thread Cache, takes it out and uses it directly, and creates a new connection thread if there is no free connection thread. It is better to set 64 3G memory.

The amount of memory allocated to each connection thread by MySQL when it is created. When MySQL creates a new connection thread, it needs to allocate a certain amount of memory stack space to store the Query of the client request and its own state and processing information. Thread_stack controls this value. 16G/32G machine is set to 512K, too small there will be Thread stack overrun error.

You can use the SQL statement show global status like 'Thread%'; to view the parameters

+-+

| | Variable_name | Value |

+-+

| | Threads_cached | 1 | |

| | Threads_connected | 1 | |

| | Threads_created | 2 | |

| | Threads_running | 1 | |

+-+

Threads_cached, if too large, proves that new threads are being created all the time, and thread_cache_size can be zoomed up.

Query configuration: show variables like 'thread_%'

Online configuration:

Configuration file parameter: thread_cache_size = 64 | thread_stack = 1m

Open_files_limit

The maximum number of files that can be opened by mysql cannot exceed the number seen by ulimt-n

Query configuration:

Online configuration:

Profile parameter: open_files_limit = 65535

Max_connect_errors

Experimental reference

When the client connection delay exceeds the time defined by connect_timeout, it will be recorded in the host_ cache table under the performance_schema database.

You can use use performance_schema;select * from host_cache\ G; to see that the SUM_CONNECT_ERRORS field will increase.

When the number of times exceeded is equal to the number of times defined by max_connect_errors, an error will be reported as follows:

ERROR 1129 (HY000): Host '10.10.10.101' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

Try to improve the network environment, or increase the max_connect_errors value

Query configuration: show variables like'% max_connect_error%'

Online configuration: set global max_connect_errors=3000

Profile parameter: max_connect_errors = 3000

Back_log

When the number of links in MySQL reaches max_connections, new requests cannot be processed currently and will be stored in the stack to wait for a connection to release resources. The number of back_log in the stack will not be granted if the number of connections waiting for exceeds back_log.

The back_log value cannot exceed the size of the listening queue for TCP/IP connections. If it is invalid, check the size command of the listening queue of the TCP/IP connection of the current system.

Cat / proc/sys/net/ipv4/tcp_max_syn_backlog

Recommendations:

Recommended setting to 350

Query: show variables like 'back_log'

Online configuration:

Configuration file: back_log= 350

Max_allowed_packet

Mysql limits the size of packets received by server based on the configuration file.

Sometimes large inserts and updates are limited by the max_allowed_packet parameter, resulting in failure.

Recommendations:

In most cases, 4m is enough, if it is not enough to add slowly.

Query: show VARIABLES like'% max_allowed_packet%'

Online configuration: set global max_allowed_packet = 410241024 *

Configuration file: max_allowed_packet = 4m

Ft_min_word_len

Full-text indexing is enabled and off by default. Open it according to the demand, do not open it if full-text indexing is not used.

Query:

Online configuration:

Configuration file: ft_min_word_len = 1

Auto_increment_increment | auto_increment_offset

These two parameters are generally used in master-master synchronization to stagger self-increment and prevent key-value conflicts.

Query: show variables like 'auto_inc%'

Online configuration:

Configuration file: auto_increment_increment = 1 | auto_increment_offset = 1

Log_bin_trust_function_creators

If master-slave replication is enabled, it should be set to 0 to prevent users from creating functions and triggers. Because the storage function may lead to inconsistent data between the master and the slave.

If only Binlog is enabled and there is no master and slave, it is set to 1.

Query:

Online configuration:

Configuration file: log_bin_trust_function_creators = 1

Read_buffer_size

MySQL read buffer size. A request for a sequential scan of the table allocates a read buffer and MySQL allocates a memory buffer for it. The read_buffer_size variable controls the size of this buffer.

If sequential scan requests for tables are very frequent, and you think frequent scans are too slow, you can improve its performance by increasing the value of the variable and the memory buffer size

Recommendations:

8G machines can set this parameter to 1m

Query:

Online configuration:

Configuration file: read_buffer_sizes = 4m

Performance_schema

It is enabled by default after version 5.5, which is used to collect performance parameters. There will also be a library with the corresponding name in the instance.

Query:

Online configuration:

Configuration file: performance_schema = 1

Skip-locking | skip-external-locking

Avoid external locking of MySQL, reduce the probability of error and enhance stability.

5 previous version skip-locking, new version skip-external-locking

When external locking (external-locking) works, each process must wait for the previous process to complete the operation and unlock if it wants to access the data table. Because servers often need to wait for unlocking when accessing data tables, external locking can degrade MySQL performance in a single server environment.

Query:

Online configuration:

Configuration file: skip-locking | skip-external-locking

Skip-name-resolve

Disable DNS parsing of external connections by MySQL, which eliminates the time it takes for MySQL to parse DNS. It should be noted, however, that if this option is turned on, all remote host connection authorizations must use IP addresses, otherwise MySQL will not be able to handle connection requests properly!

Query:

Online configuration:

Configuration file: skip-name-resolve

Table_cache

Its function is to cache table file descriptors to reduce the frequency of opening and closing tables.

Mysql has only one global lock to control the opening and closing of tables, that is, no matter how many threads are executing in parallel, only one thread can open or close tables, so there will be a lot of deadlocks, and other threads will wait for that global lock.

Accordingly, the consumption of cpu is increased, the time for other link threads to execute sql is prolonged, and the system performance is reduced. So while ensuring that the table_cache is adequate, try to keep the table_cache small enough

Query:

Online configuration:

Configuration file: table_cache = 128K

Init_connect

Init_connect means that after the user logs in to the database, the contents are executed by default, similar to the / etc/profile of the Linux system. Before the user operates, you can set the character set or initialize something.

However, there is a problem with the syntax in the content, which will cause the user to exit from mysql. Init_connect is not valid for users with super privileges.

Query:

Online configuration: set global init_connect=set autocommit=0; set names gbk;'

Configuration file: init_connect='set autocommit=0; set names gbk;'

Explicit_defaults_for_timestamp

Reference experiment

Clear timestamp default null mode. If it is higher than version 5.5.6, create the following

Create table mytime (

Id int

Atime timestamp not null

Ctime timestamp not null

);

The following error occurs. Just change the variable to true.

ERROR 1067 (42000): Invalid default value for 'ctime'

When false, "initialize" according to the following rules:

TIMESTAMP columns that are not explicitly declared as NULL attributes are assigned as NOT NULL attributes. (columns of other data types are allowed null values if they are not explicitly declared as NOT NULL. Set this column to NULL and set it to the current timestamp

The first TIMESTAMP column in the table (if not declared as a NULL attribute or an explicit DEFAULT or ON UPDATE clause) automatically assigns the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes.

The TIMESTAMP column after the first (if not declared as a NULL attribute or an explicit DEFAULT clause) is automatically assigned a DEFAULT'0000-00-0000: 00 DEFAULT 00' ("zero" timestamp). For insert rows that do not specify an explicit value for this column, the column is assigned "0000-00-0000: 00:00" and no warning occurs.

When true, "initialize" according to the following rules:

Null values are allowed for TIMESTAMP columns that are not explicitly declared NOT NULL. Set this column to NULL and set it to NULL instead of the current timestamp.

No TIMESTAMP column automatically assigns DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP attributes. These attributes must be specified explicitly.

TIMESTAMP columns that are declared as NOT NULL and have no explicit DEFAULT clause are considered to have no default values. For insert rows that do not specify an explicit value for this column, the result depends on the SQL mode. If strict SQL mode is enabled, an error occurs. If strict SQL mode is not enabled, the column is assigned an implicit default value of "0000-00-0000: 00:00" and a warning is issued. This is similar to how MySQL handles other time types, such as DATETIME.

Query:

Online configuration:

Configuration file: explicit_defaults_for_timestamp=false

Transaction-isolation

Modify transaction isolation level

Optional parameters are: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE. Default REPEATABLE-READ

Query:

Online configuration: set global transaction isolation level read uncommitted

Configuration file: transaction-isolation = REPEATABLE-READ

Key_buffer_size

Specifies the size of the index buffer, which determines the speed of index processing, especially the speed of index reading.

For a key_reads/key_read_requests obtained with the show global status like 'key_read%'; command, it would be better to have a ratio of at least 1 key_reads/key_read_requests 100 and 1 key_reads/key_read_requests 1000. If the scale is too small, you can increase the key_buffer_ size value.

Key_buffer_size only works on MyISAM tables. Use this value even if you do not use the MyISAM table, but the internal temporary disk table is the MyISAM table.

For machines with 1G memory, if the MyISAM table is not used, the recommended value is 16m (8-64m)

For machines with 64 memory, 256m is recommended.

Query: SHOW VARIABLES LIKE'% key_buffer_size%'

Online configuration:

Configuration file: key_buffer_size = 16m

Table_open_cache

Specifies the size of the table cache. Whenever MySQL accesses a table, if there is space in the table buffer, the table is opened and placed in it, allowing faster access to the table contents.

You can use the SQL statement SHOW variables LIKE'% table_open_cache%'; to get the table_open_cache parameter, which is the cached table.

The open_tables parameter is obtained with SHOW GLOBAL STATUS LIKE 'Open%tables';. This is the open watch.

If open_tables equals table_open_cache, and opened_tables is growing, then you need to increase the value of table_open_cache. Because mysql is releasing the cached table to accommodate the new table.

Recommendations:

Open_tables / Opened_tables > = 0.85

Open_tables / table_open_cache

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.