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

A brief introduction

InnoDB provides MySQL with a transaction secure (ACID compatible) storage engine with commit, rollback and crash recovery capabilities. InnoDB locks at the row level and also provides an Oracle-consistent unlocked read in the SELECT statement. These features increase multi-user deployment and performance. There is no need to expand locking in InnoDB, because row-level locking in InnoDB is suitable for very small spaces. InnoDB also supports FOREIGN KEY enforcement. In SQL queries, you are free to mix InnoDB tables with other MySQL table types, even in the same query.

two。 The reason why innodb is chosen as the storage engine

Currently, InnoDB is the maximum performance design for Mysql when dealing with large amounts of data. Its CPU efficiency may be unmatched by any other disk-based relational database engine. Innodb is very popular in websites or applications with a large amount of data.

On the other hand, Innodb also plays a certain role in ensuring the consistency of master and slave data in the database replication operation.

3. The following is a record of the optimization analysis of the configuration of the online mysql5.6 version of the database:

1) memory utilization:

Innodb_buffer_pool_size

This is the most important parameter for Innodb, which is similar to but different from MyISAM's key_buffer_size.

This parameter mainly caches the index of the innodb table, the data, and the buffer when inserting the data.

The principle for allocating memory for this parameter:

This parameter is assigned only 8m by default, which can be said to be a very small value.

If it is a dedicated DB server, it can account for 70% and 80% of the memory.

This parameter cannot be changed dynamically, so allocation needs to be considered. If the allocation is too large, the Swap will be occupied too much, causing the query of Mysql to be very slow.

If your data is small, then your data size is + 10% or so as the value of this parameter.

For example, if the data size is 50m, then assign innodb_buffer_pool_size=64M to this value

Set the method in the my.cnf file:

Innodb_buffer_pool_size=4G

-

Note:

Prior to the Mysql5.7 version, resizing the innodb_buffer_pool_size had to be modified in the my.cnf configuration before restarting the mysql process would take effect.

Now in the Mysql5.7 version, you can adjust this parameter directly and dynamically, which is much more convenient.

Especially after the server memory increases, the operation and maintenance staff should not be careless and remember to increase the parameter Innodb_Buffer_Pool_size.

After configuring the database, you should check whether the parameter Innodb_Buffer_Pool_size is set properly.

Points to pay attention to:

During the adjustment of innodb_buffer_pool_size, the user's request will block until the adjustment is complete, so do not adjust during the day, during the trough of 3: 00 a.m. to 4: 00 a.m.

When adjusting, the data page is moved internally to a new location, in blocks. If you want to increase the speed of movement, you need to resize the innodb_buffer_pool_chunk_size parameter, which defaults to 128m.

The operation record in Mysql5.7 that dynamically adjusts this parameter (for example, from 128m to 384m):

134217728amp 1024m 1024m 128m

Mysql > SELECT @ @ innodb_buffer_pool_size

+-+

| | @ @ innodb_buffer_pool_size |

+-+

| | 134217728 |

+-+

1 row in set (0.00 sec)

Mysql > SELECT @ @ innodb_buffer_pool_chunk_size

+-+

| | @ @ innodb_buffer_pool_chunk_size |

+-+

| | 134217728 |

+-+

1 row in set (0.00 sec)

Mysql > SET GLOBAL innodb_buffer_pool_size=402653184

Query OK, 0 rows affected (0.01 sec)

Mysql > SELECT @ @ innodb_buffer_pool_size

+-+

| | @ @ innodb_buffer_pool_size |

+-+

| | 402653184 |

+-+

1 row in set (0.00 sec)

The size of innodb_buffer_pool_chunk_size. The formula is innodb_buffer_pool_size/innodb_buffer_pool_instances.

For example, if innodb_buffer_pool_size is initialized to 2G and the instance is set to 1G, then innodb_buffer_pool_chunk_size 1G will be automatically adjusted to 512m.

Example:

. / mysqld-innodb_buffer_pool_size=2147483648-innodb_buffer_pool_instances=4

-- innodb_buffer_pool_chunk_size=1073741824

Mysql > SELECT @ @ innodb_buffer_pool_size

+-+

| | @ @ innodb_buffer_pool_size |

+-+

| | 2147483648 |

+-+

1 row in set (0.00 sec)

Mysql > SELECT @ @ innodb_buffer_pool_instances

+-+

| | @ @ innodb_buffer_pool_instances |

+-+

| | 4 |

+-+

1 row in set (0.00 sec)

# Chunk size was set to 1GB (1073741824 bytes) on startup but was

# truncated to innodb_buffer_pool_size / innodb_buffer_pool_instances

Mysql > SELECT @ @ innodb_buffer_pool_chunk_size

+-+

| | @ @ innodb_buffer_pool_chunk_size |

+-+

| | 536870912 |

+-+

1 row in set (0.00 sec)

Monitor the Buffer Pool adjustment process

Mysql > SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status'

+-+

| | Variable_name | Value |

+-+

| Innodb_buffer_pool_resize_status | Resizing also other hash tables. | |

+-+

1 row in set (0.00 sec)

View the error log:

(increase)

[Note] InnoDB: Resizing buffer pool from 134217728 to 4294967296. (unit=134217728)

[Note] InnoDB: disabled adaptive hash index.

[Note] InnoDB: buffer pool 0: 31 chunks (253952 blocks) was added.

[Note] InnoDB: buffer pool 0: hash tables were resized.

[Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.

[Note] InnoDB: completed to resize buffer pool from 134217728 to 4294967296.

[Note] InnoDB: re-enabled adaptive hash index.

(decrease)

[Note] InnoDB: Resizing buffer pool from 4294967296 to 134217728. (unit=134217728)

[Note] InnoDB: disabled adaptive hash index.

[Note] InnoDB: buffer pool 0: start to withdraw the last 253952 blocks.

[Note] InnoDB: buffer pool 0: withdrew 253952 blocks from free list. Tried to relocate 0 pages. (253952gamble 253952)

[Note] InnoDB: buffer pool 0: withdrawn target 253952 blocks.

[Note] InnoDB: buffer pool 0: 31 chunks (253952 blocks) was freed.

[Note] InnoDB: buffer pool 0: hash tables were resized.

[Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.

[Note] InnoDB: completed to resize buffer pool from 4294967296 to 134217728.

[Note] InnoDB: re-enabled adaptive hash index.

-

Innodb_additional_mem_pool_size

Used to store the internal directory of Innodb, this value does not need to be allocated too large, the system can automatically adjust. Usually setting 16m is enough, if there are more tables, it can be increased appropriately.

Set the method in the my.cnf file:

Innodb_additional_mem_pool_size = 16m

2) with regard to logs:

Innodb_log_file_size

Purpose: specify the size of each log in a log group.

Combined with innodb_buffer_pool_size to set its size, 25% 100%. Avoid unnecessary refreshes.

Note: the size allocated by this value has a lot to do with the write speed of the database, the transaction size, and the recovery after an abnormal restart. Generally, 256m can strike a balance between performance and recovery speed.

Allocation principle: several daily value members add up to about the same size as your innodb_buffer_pool_size. The upper limit is 4G per day. Generally controlled in several Log files, it is better to increase the size within 2G. The specific situation also depends on your transaction size and data size.

Description: the size allocated by this value has a lot to do with the write speed of the database, the transaction size, and the recovery after an abnormal restart.

Setting method: in the my.cnf file:

Innodb_log_file_size = 256m

Innodb_log_files_in_group

Purpose: specify how many daily groups you have.

Distribution principle: generally we can use 2-3 daily value groups. The default is two.

Setting method: in the my.cnf file:

Innodb_log_files_in_group=3

Innodb_log_buffer_size:

Function: the buffer of transactions in memory, that is, the size of the log buffer, can be set by default, and those with a large number of transactions can be set to 16m.

If this value grows too fast, you can increase innodb_log_buffer_size appropriately.

In addition, if you need to deal with the TEXT or BLOB field of Dali, consider increasing the value of this parameter.

Setting method: in the my.cnf file:

Innodb_log_buffer_size=3M

Innodb_flush_logs_at_trx_commit

Function: controls how transactions are committed, that is, how log is flushed to disk.

Allocation principle: this parameter has only 3 values (0, 1, 2). The default is 1, and higher performance can be set to 0 or 2, which can appropriately reduce disk IO (but lose transactions for one second. It is recommended that the MySQL of the game library be set to 0. Please do not change the main library.

Where:

Data in 0:log buffer will be written to log file at a rate of once per second, and file system-to-disk synchronization will occur at the same time, but the commit of each transaction will not trigger any log buffer-to-log file refresh or file system-to-disk refresh

1: (default is 1) data from logbuffer is written to log file each time the transaction is committed, and synchronization of the file system to disk is triggered.

2: transaction commit triggers log buffer-to-log file refresh, but does not trigger disk file system-to-disk synchronization. In addition, there is a file system-to-disk synchronization operation every second.

Description:

The setting of this parameter has a great impact on the performance of Innodb, so I'll explain it here.

When this value is 1: innodb's transaction LOG writes the daily value file after each commit and flushes the daily value to disk. This can be done without losing a single transaction.

When this value is 2: at each commit, the log buffer is written to the file, but the log file is not refreshed on disk, and the refresh of the log file occurs once per second when the refresh value is 2. However, it should be noted that due to problems with process calls, 100% occurrence per second is not guaranteed. Thus it is the fastest in terms of performance. However, the last second transaction will be deleted only if the operating system crashes or power goes down.

When this value is 0: the log buffer is written to the log file once a second, and the log file is refreshed to disk, but nothing is done in a transaction commit. The crash of the mysqld process deletes the last second transaction before the crash.

From the above analysis, when this value is not 1, you can achieve better performance, but there will be losses when you encounter an exception, so you need to measure it according to your own situation.

Setting method: in the my.cnf file:

Innodb_flush_logs_at_trx_commit=1

3) File IO allocation, space occupation

Innodb_file_per_table

Function: make each Innodb table have its own independent tablespace. For example, you can reclaim that part of the space after deleting the file. It is off by default. It is recommended to turn it on (innodb_file_per_table=1)

Distribution principle: only whether to use it or not. But DB also needs to have a common tablespace.

Setting method: in the my.cnf file:

Innodb_file_per_table=1

Innodb_file_io_threads

Function: the file reads and writes the number of IO. This parameter only works on Windows. It will only be equal to 4 on Linux, by default!

Setting method: in the my.cnf file:

Innodb_file_io_threads=4

Innodb_open_files

Purpose: limit the data of tables that Innodb can open.

Allocation principle: this value defaults to 300. If there are a lot of tables in the library, you can increase it to 1000. The size of innodb_open_files has little effect on the efficiency of InnoDB. However, in the case of InnoDBcrash, a small setting of innodb_open_files will affect the efficiency of recovery. So it is more appropriate to enlarge the innodb_open_files when using InnoDB.

Setting method: in the my.cnf file:

Innodb_open_files=800

Innodb_data_file_path

Specifies the space for table data and index storage, which can be one or more files. The last data file must be automatically expanded, and only the last file allows automatic expansion. In this way, when the space is used up, the auto-extension data file will automatically grow (in 8MB) to accommodate the additional data.

For example: innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:50M:autoextend two data files on different disks. The data is first placed in ibdata1, and when it reaches 900m, the data is placed in ibdata2.

Set the method in the my.cnf file:

Innodb_data_file_path = ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:1G:autoextend

Innodb_data_home_dir

The directory where the tablespace data is placed, by default, is in the mysql data directory, and setting to a different partition from the MySQL installation file can improve performance.

Setting method, in the my.cnf file: (for example, the data directory of mysql is / data/mysql/data, which can be set to the impassable partition / home/mysql)

Innodb_data_home_dir = / home/mysql

4) other related parameters (increase table_cache appropriately)

Here is a more important parameter:

Innodb_flush_method

Role: an IO model for Innodb to deal with systems

Principles of distribution:

Windows does not need to be set.

Linux can choose: O_DIRECT

Write directly to disk, disable system Cache

Setting method: in the my.cnf file:

Innodb_flush_method=O_DIRECT

Innodb_max_dirty_pages_pct

Function: in the buffer pool buffer, the percentage of dirty pages allowed for Innodb is in the range of 1-100. the default is 90. It is recommended to keep it by default.

Another use of this parameter: when the memory allocation of Innodb is too large, resulting in serious Swap footprint, you can appropriately reduce and adjust this value to free up Swap space. Jianyi: the maximum value is 90% and the minimum is 15%. Too large, each update in the cache needs to change too many data pages, too small, too small, and the update operation is too slow.

Setting method: in the my.cnf file:

Innodb_max_dirty_pages_pct=90

Dynamic changes require administrator privileges:

Set global innodb_max_dirty_pages_pct=50

Innodb_thread_concurrency

Number of threads processed in the Innodb kernel at the same time. Default values are recommended.

Set the method in the my.cnf file:

Innodb_thread_concurrency = 16

5) Common parameter tuning

Skip-external-locking

The MyISAM storage engine also uses this parameter, which is enabled by default after MySQL4.0.

The function is to avoid the external locking of MySQL (the old version of MySQL this parameter is called skip-locking), reduce the probability of error and enhance stability. Default values are recommended.

Set the method in the my.cnf file:

Skip-external-locking

Skip-name-resolve

Disable DNS resolution of external connections by MySQL (this setting is turned off by default, that is, DNS resolution by default). Use this option to eliminate the time it takes for MySQL to resolve 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! If desired, you can set this item.

Setting method, in the my.cnf file: (I opened this setting in my online mysql database)

Skip-name-resolve

Max_connections

Set the maximum number of connections (users). Each user connected to MySQL is counted as a connection, and the default value for max_connections is 100. This value needs to be set according to the peak number of connections.

Set the method in the my.cnf file:

Max_connections = 3000

Query_cache_size

Query cache size, if the table changes frequently, or if each query is different, the results of the query cache will slow down system performance. Can be set to 0.

Set the method in the my.cnf file:

Query_cache_size = 512m

Sort_buffer_size

Connection-level parameters, sort cache size. It is generally set to 2-4MB.

Set the method in the my.cnf file:

Sort_buffer_size = 1024m

Read_buffer_size

Parameters at the connection level. It is generally set to 2-4MB.

Set the method in the my.cnf file:

Read_buffer_size = 1024m

Max_allowed_packet

The size of the network packet. To avoid large network packet errors, it is recommended to set it to 16m.

Set the method in the my.cnf file:

Max_allowed_packet = 16m

Table_open_cache

When a connection accesses a table, MySQL checks the current number of cached tables. If the table is already open in the cache, the table in the cache is accessed directly to speed up the query; if the table is not cached, the current table is added to the cache and queried.

By checking the state values of peak time Open_tables and Opened_tables, you can decide whether you need to increase the value of table_open_cache.

If you find that open_tables is equal to table_open_cache and opened_tables is growing, you need to increase the value of table_open_cache; set to 512 to meet the demand.

Set the method in the my.cnf file:

Table_open_cache = 512

Myisam_sort_buffer_size

In fact, this myisam_sort_buffer_size parameter does not make much sense, it is a literal parameter, it is used for the memory required by ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE and other commands. The default value is fine.

Set the method in the my.cnf file:

Myisam_sort_buffer_size = 8m

Thread_cache_size

Thread cache, if a client disconnects, the thread will be put into the thread_cache_size (buffer pool is not full), SHOW STATUS LIKE 'threads%'; if the Threads_created continues to grow, then the current value should be changed to the Threads_connected value or so. (usually, this value does not improve performance very much.) the default is 8.

Set the method in the my.cnf file:

Thread_cache_size = 8

Innodb_thread_concurrency

Number of concurrency threads. It is recommended to set the number of CPU cores to * 2

Set the method in the my.cnf file:

Innodb_thread_concurrency = 8

Key_buffer_size

Acts only on the MyISAM storage engine to set the size of the memory area used to cache index files in the MyISAM storage engine. If we have enough memory, this cache area is better able to hold all the indexes of all our MyISAM engine tables to maximize performance. Do not set more than 30% of the available memory. Even if you do not use the MyISAM table, set the value 8-64m for temporary tables.

Set the method in the my.cnf file:

Key_buffer_size = 8m

-

The following is the my.cnf configuration reference for online mysql (innodb):

[client]

Port = 3306

Socket = / usr/local/mysql/var/mysql.sock

[mysqld]

Port = 3306

Socket = / usr/local/mysql/var/mysql.sock

Basedir = / usr/local/mysql/

Datadir = / data/mysql/data

Pid-file = / data/mysql/data/mysql.pid

User = mysql

Bind-address = 0.0.0.0

Server-id = 1

Sync_binlog=1

Log_bin = mysql-bin

Skip-name-resolve

Back_log = 600,

Max_connections = 3000

Max_connect_errors = 3000

Table_open_cache = 512

Max_allowed_packet = 16m

Binlog_cache_size = 16m

Max_heap_table_size = 16m

Tmp_table_size = 256m

Read_buffer_size = 1024m

Read_rnd_buffer_size = 1024m

Sort_buffer_size = 1024m

Join_buffer_size = 1024m

Key_buffer_size = 8192 M

Thread_cache_size = 8

Query_cache_size = 512m

Query_cache_limit = 1024m

Ft_min_word_len = 4

Binlog_format = mixed

Expire_logs_days = 30

Log_error = / data/mysql/data/mysql-error.log

Slow_query_log = 1

Long_query_time = 1

Slow_query_log_file = / data/mysql/data/mysql-slow.log

Performance_schema = 0

Explicit_defaults_for_timestamp

Skip-external-locking

Default_storage_engine = InnoDB

Innodb_file_per_table = 1

Innodb_open_files = 500,

Innodb_buffer_pool_size = 1024m

Innodb_write_io_threads = 1000

Innodb_read_io_threads = 1000

Innodb_thread_concurrency = 8

Innodb_purge_threads = 1

Innodb_flush_log_at_trx_commit = 2

Innodb_log_buffer_size = 4m

Innodb_log_file_size = 32m

Innodb_log_files_in_group = 3

Innodb_max_dirty_pages_pct = 90

Innodb_lock_wait_timeout = 120

Bulk_insert_buffer_size = 8m

Myisam_sort_buffer_size = 8m

Myisam_max_sort_file_size = 10G

Myisam_repair_threads = 1

Interactive_timeout = 28800

Wait_timeout = 28800

[mysqldump]

Quick

Max_allowed_packet = 16m

[myisamchk]

Key_buffer_size = 8m

Sort_buffer_size = 8m

Read_buffer = 4m

Write_buffer = 4m

Sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Port = 3306

- -

Here is an optimized configuration of my.cnf under mysql5.6, which can greatly improve the performance of mysql:

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

# * * DO NOT EDIT THIS FILE. It's a template which will be copied to the

# * * default location during install, and will be replaced if you

# * * upgrade to a newer version of MySQL.

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128m

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

# These are commonly set, remove the # and set as required.

# basedir =.

# datadir =.

# port =.

# server_id =.

# socket =.

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# # #

# innodb

User=mysql

Innodb_buffer_pool_size=6G

Innodb_log_file_size=4G

Innodb_log_buffer_size = 8m

Innodb_flush_log_at_trx_commit=2

Innodb_file_per_table=1

Innodb_file_io_threads=4

Innodb_flush_method=O_DIRECT

Innodb_io_capacity=2000

Innodb_io_capacity_max=6000

Innodb_lru_scan_depth=2000

Innodb_thread_concurrency = 0

Innodb_additional_mem_pool_size=16M

Innodb_autoinc_lock_mode = 2

# # #

# Binary log/replication

Log-bin

Sync_binlog=1

Sync_relay_log=1

Relay-log-info-repository=TABLE

Master-info-repository=TABLE

Expire_logs_days=7

Binlog_format=ROW

Transaction-isolation=READ-COMMITTED

# # #

# cache

Tmp_table_size=512M

Character-set-server=utf8

Collation-server=utf8_general_ci

Skip-external-locking

Back_log=1024

Key_buffer_size=1024M

Thread_stack=256k

Read_buffer_size=8M

Thread_cache_size=64

Query_cache_size=128M

Max_heap_table_size=256M

Query_cache_type=1

Binlog_cache_size = 2m

Table_open_cache=128

Thread_cache=1024

Thread_concurrency=8

Wait_timeout=30

Join_buffer_size = 1024m

Sort_buffer_size = 8m

Read_rnd_buffer_size = 8m

# # #

# connect

Max-connect-errors=100000

Max-connections=1000

# # #

Explicit_defaults_for_timestamp=true

Sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# # #

Parameter explanation:

# Binary log/replication (here is mainly the replication function, that is, the master-slave configuration, which is configured in advance, and the master-slave configuration later)

# binary log

Log-bin

# in order to ensure the persistence and consistency of replicated InnoDB transactions on the maximum program

Sync_binlog=1

Sync_relay_log=1

# enable these two items, which can be used to ensure binary and slave server security in the event of a crash

Relay-log-info-repository=TABLE

Master-info-repository=TABLE

# set the time to clear logs

Expire_logs_days=7

# Line copy

Binlog_format=ROW

# there are four mysql database transaction isolation levels (READ UNCOMMITTED,READ COMMITTED,REPEATABLE READ,SERIALIZABLE)

Transaction-isolation=READ-COMMITTED

# cache

# maximum value of internal memory temporary table

Tmp_table_size=512M

Character-set-server=utf8

Collation-server=utf8_general_ci

# that is, skip external locking

Skip-external-locking

# number of connections that can be temporarily stored by MySQL (based on actual settings)

Back_log=1024

# specify the size of the index buffer, which only works on the MyISAM table. It doesn't matter if you write it here

Key_buffer_size=1024M

# this instruction limits the stack size for each database thread

Thread_stack=256k

# when a query constantly scans a table, MySQL allocates a memory buffer to it

Read_buffer_size=8M

# Thread caching

Thread_cache_size=64

# query cache size

Query_cache_size=128M

# the maximum value of the temporary table of internal memory, which should be allocated by each thread

Max_heap_table_size=256M

# put the query results in the query cache

Query_cache_type=1

# represents the cache size that holds binary log SQL statements during a transaction

Binlog_cache_size = 2m

# it is also the cache table size

Table_open_cache=128

# caching thread

Thread_cache=1024

# it is recommended to set it to 2 times the number of server CPU cores

Thread_concurrency=8

Wait_timeout=30

# size of buffer for table and table join

Join_buffer_size = 1024m

# is a connection-level parameter. When each connection needs to use this buffer for the first time, it allocates the set memory at once.

Sort_buffer_size=8M

# Random read data buffer using memory

Read_rnd_buffer_size = 8m

# connect

# is a security-related counter value in MySQL, which is responsible for preventing too many failed clients from breaking passwords violently

Max-connect-errors=100000

# number of connections

Max-connections=1000

# enable query caching

Explicit_defaults_for_timestamp=true

# mysql server can work in different modes and apply these modes in different ways for different clients

Sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

The main variables that have a great impact on performance optimization are listed below, mainly divided into connection request variables and buffer variables.

1. Variables for connection requests:

1) max_connections

The maximum number of connections for MySQL, increasing this value increases the number of file descriptors required by mysqld. If the server has a large number of concurrent connection requests, it is recommended to increase this value to increase the number of parallel connections. Of course, this is based on the case that the machine can support, because if the number of connections is more, between MySQL will provide a connection buffer for each connection, the more memory will be spent, so adjust this value appropriately, not blindly increase the setting value.

ERROR 1040: Too many connections errors often occur when the value is too small. You can check the number of connections in the current state through the 'conn%' wildcard to determine the size of this value.

Maximum number of connections in show variables like 'max_connections'

Number of connections to show status like 'max_used_connections' response

As follows:

Mysql > show variables like 'max_connections'

+-+

| | Variable_name | Value |

+-+

| | max_connections | 256 |

+-+

Mysql > show status like 'max%connections'

+-+

| | Variable_name | Value |

+-+

| | max_used_connections | 256 |

+-+

Max_used_connections / max_connections * 100% (ideal value ≈ 85%)

If the max_used_connections is the same as max_connections, then the max_connections setting is too low or exceeds the server load limit, and less than 10% is too large.

2) back_log

The number of connections that MySQL can hold temporarily. This works when the main MySQL thread gets very many connection requests in a very short period of time. If the connection data of MySQL reaches max_connections, the new request will be stored in the stack to wait for a connection to release resources, the number of the stack is back_log, if the number of waiting connections exceeds back_log, connection resources will not be granted.

The back_ log value indicates how many requests can be stored on the stack in a short period of time before MySQL temporarily stops answering new requests. Only if you expect to have many connections in a short period of time, you need to increase it, in other words, this value is the size of the listening queue for incoming TCP/IP connections.

When looking at the list of your host processes (mysql > show full processlist), you can find a large number of 264084 | unauthenticated user | xxx.xxx.xxx.xxx | Connect | NULL | login | NULL processes to be connected, you should increase the value of back_log.

The default value is 50, can be tuned to 128, and the system setting range is an integer less than 512.

3) interactive_timeout

The number of seconds an interactive connection waits for action before being shut down by the server. An interactive customer is defined as a customer who uses the CLIENT_INTERACTIVE option for mysql_real_connect ().

The default value is 28800 and can be tuned to 7200.

two。 Buffer variable

Global buffering:

4) key_buffer_size

Key_buffer_size specifies the size of the index buffer, which determines the speed of index processing, especially the speed of index reads. By checking the status values Key_read_requests and Key_reads, you can see whether the key_buffer_size setting is reasonable. The key_reads / key_read_requests ratio should be as low as possible, at least 1 SHOW STATUS LIKE 100 and 1 SHOW STATUS LIKE 1000 is better. (the above state values can be obtained using the state 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. You can use the check status value created_tmp_disk_tables to learn more.

Examples are as follows:

Mysql > show variables like 'key_buffer_size'

+-+

| | Variable_name | Value |

+-+

| | key_buffer_size | 536870912 | |

+-+-+

Key_buffer_size is 512MB. Let's take a look at the usage of key_buffer_size:

Mysql > show global status like 'key_read%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Key_read_requests | 27813678764 |

| | Key_reads | 6798830 | |

+-+ +

There are 27813678764 index read requests, of which 6798830 are not found in memory to read the index directly from the hard disk. Calculate the probability that the index misses the cache:

Key_cache_miss_rate = Key_reads / Key_read_requests * 100%, it is better to set it to around 1max 1000.

The default configuration value is 8388600 (8m). The host has 4GB memory and can be tuned to 268435456 (256MB).

5) query_cache_size

Using query buffering, MySQL stores the query results in a buffer, and in the future, for the same SELECT statement (case sensitive), the results will be read directly from the buffer.

You can see whether the query_cache_size setting is reasonable by checking the status value Qcache_*, (the above status values can be obtained using SHOW STATUS LIKE 'Qcache%'). If the value of Qcache_lowmem_prunes is very large, it means that there is often insufficient buffering; if the value of Qcache_hits is also very large, it means that query buffering is used very frequently, and you need to increase the buffer size; if the value of Qcache_hits is not large, it means that your query repetition rate is very low, in this case, using query buffering will affect the efficiency, then you can consider not using query buffering. In addition, adding SQL_NO_CACHE to the SELECT statement makes it clear that query buffering is not used.

Other parameters related to query buffering are query_cache_type, query_cache_limit, and query_cache_min_res_unit.

Query_cache_type specifies whether to use query buffering, which can be set to 0, 1, 2, which is a SESSION-level variable.

Query_cache_limit specifies the size of the buffer that can be used by a single query, which defaults to 1m.

Query_cache_min_res_unit, introduced after version 4.1, specifies the minimum unit of buffer space allocated, which defaults to 4K. Check the status value Qcache_free_blocks, which, if very large, indicates that there is a lot of fragmentation in the buffer, which indicates that the query results are relatively small, and you need to reduce query_cache_min_res_unit at this point.

Examples are as follows:

Mysql > show global status like 'qcache%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Qcache_free_blocks | 22756 | |

| | Qcache_free_memory | 76764704 | |

| | Qcache_hits | 213028692 | |

| | Qcache_inserts | 208894227 | |

| | Qcache_lowmem_prunes | 4010916 | |

| | Qcache_not_cached | 13385031 | |

| | Qcache_queries_in_cache | 43560 | |

| | Qcache_total_blocks | 111212 | |

+-+ +

Mysql > show variables like 'query_cache%'

+-+ +

| | Variable_name | Value |

+-+

| | query_cache_limit | 2097152 | |

| | query_cache_min_res_unit | 4096 | |

| | query_cache_size | 203423744 | |

| | query_cache_type | ON |

| | query_cache_wlock_invalidate | OFF |

+-+ +

Query cache fragmentation rate = Qcache_free_blocks / Qcache_total_blocks * 100%

If the query cache fragmentation rate exceeds 20%, you can use FLUSH QUERY CACHE to defragment the cache, or try to reduce query_cache_min_res_unit, if your query is a small amount of data.

Query cache utilization = (query_cache_size-Qcache_free_memory) / query_cache_size * 100%

If the query cache utilization is below 25%, it means that the query_cache_size setting is too large and can be reduced appropriately; if the query cache utilization is above 80% and the Qcache_lowmem_prunes is more than 50, the query_cache_size may be a little small, or there may be too many fragments.

Query cache hit ratio = (Qcache_hits-Qcache_inserts) / Qcache_hits * 100%

Example server query cache fragmentation rate = 20.46%, query cache utilization = 62.26%, query cache hit rate = 1.94%, the hit rate is very poor, maybe write operations are more frequent, and there may be some fragments.

Buffer per connection

6) record_buffer_size

Each thread that performs a sequential scan allocates a buffer of this size to each table it scans. If you do a lot of sequential scans, you may want to increase this value.

The default value is 131072 (128K), which can be changed to 16773120 (16m)

7) read_rnd_buffer_size

Random read buffer size. When rows are read in any order (for example, in sort order), a random read cache is allocated. When sorting a query, MySQL will first scan the buffer to avoid disk search and improve the query speed. If you need to sort a large amount of data, you can increase the value appropriately. However, MySQL allocates this buffer space for each customer connection, so you should try to set this value appropriately to avoid excessive memory overhang.

It can generally be set to 16m

8) sort_buffer_size

Each thread that needs to sort allocates a buffer of that size. Increase this value to speed up ORDER BY or GROUP BY operations.

The default value is 2097144 (2m), which can be changed to 16777208 (16m).

9) join_buffer_size

Buffer size that can be used by federated query operations

Record_buffer_size,read_rnd_buffer_size,sort_buffer_size,join_buffer_size is exclusive to each thread, that is, if there are 100 thread connections, the occupancy is 16M*100

10) table_cache

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. By checking the state values of peak time Open_tables and Opened_tables, you can decide whether you need to increase the value of table_cache. If you find that open_tables equals table_cache and opened_tables is growing, you need to increase the value of table_cache (the above state values can be obtained using SHOW STATUS LIKE 'Open%tables'). Note that table_cache cannot be set to a large value blindly. If set too high, it may result in insufficient file descriptors, resulting in unstable performance or connection failure.

One gigabyte memory machine, the recommended value is 128mur256. Servers with memory around 4GB this parameter can be set to 256m or 384m.

11) max_heap_table_size

The size of the memory table (memory table) that the user can create. This value is used to calculate the maximum row value of the memory table. This variable supports dynamic change, i.e. set @ max_heap_table_size=#

This variable, along with tmp_table_size, limits the size of internal memory tables. If the size of an internal heap (stacking) table exceeds tmp_table_size,MySQL, you can automatically change the in-memory heap table to a hard disk-based MyISAM table as needed.

12) tmp_table_size

Increase the size of a temporary table by setting the tmp_table_size option, such as a temporary table generated by advanced GROUP BY operations. If you increase this value, MySQL will also increase the size of the heap table, which can improve the speed of the join query. It is recommended to optimize the query as far as possible to ensure that the temporary table generated during the query process is in memory, so as to avoid the generation of MyISAM table based on hard disk due to the temporary table being too large.

Mysql > show global status like 'created_tmp%'

+-+

| | Variable_name | Value |

+-+

| | Created_tmp_disk_tables | 21197 | |

| | Created_tmp_files | 58 | |

| | Created_tmp_tables | 1771587 | |

+-+

Each time a temporary table is created, Created_tmp_tables increases. If the temporary table size exceeds tmp_table_size, a temporary table is created on disk, and Created_tmp_disk_tables is also increased. Created_tmp_files represents the number of temporary files created by the MySQL service. The ideal configuration is:

Created_tmp_disk_tables / Created_tmp_tables * 100% show variables

1. Slow query

Mysql > show variables like'% slow%'

+-+ +

| | Variable_name | Value |

+-+ +

| | log_slow_queries | ON |

| | slow_launch_time | 2 | |

+-+ +

Mysql > show global status like'% slow%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Slow_launch_threads | 0 | |

| | Slow_queries | 4148 | |

+-+ +

Slow query is opened in the configuration. If the execution time is more than 2 seconds, it is slow query. The system shows that there are 4148 slow queries. You can analyze the slow query log and find out the problematic SQL statements. Slow query time should not be set too long, otherwise it is not meaningful, preferably within 5 seconds. If you need microsecond-level slow query, you can consider patching MySQL: http://www.percona.com/docs/wiki/release:start. Remember to find the corresponding version.

Opening the slow log may have a slight impact on system performance. If your MySQL is a master-slave structure, consider opening the slow log of one of the slave servers, so that you can monitor slow queries with little impact on system performance.

Second, the number of connections

We often encounter the situation of "MySQL: ERROR 1040: Too manyconnections". One is that the traffic is really high and the MySQL server cannot resist it. At this time, it is necessary to consider increasing the decentralized reading pressure from the server, and the other is that the max_connections value in the MySQL configuration file is too small:

Mysql > show variables like 'max_connections'

+-+ +

| | Variable_name | Value |

+-+ +

| | max_connections | 256 |

+-+ +

The maximum number of connections for this MySQL server is 256, and then query the maximum number of connections that the server responded to:

Mysql > show global status like 'Max_used_connections'

+-+ +

| | Variable_name | Value |

+-+ +

| | Max_used_connections | 245 |

+-+ +

The maximum number of connections to the MySQL server in the past is 245.It does not reach the server connection limit of 256. there should be no 1040 error. The ideal setting is:

Max_used_connections / max_connections * 100% ≈ 85%

The maximum number of connections accounts for about 85% of the upper limit. If the proportion is found to be less than 10%, the maximum number of MySQL server connections is set too high.

III. Key_buffer_size

Key_buffer_size is a parameter that has the greatest impact on the performance of the MyISAM table. The following is a configuration of a server with MyISAM as the main storage engine:

Mysql > show variables like 'key_buffer_size'

+-+ +

| | Variable_name | Value |

+-+ +

| | key_buffer_size | 536870912 | |

+-+ +

After allocating 512MB memory to key_buffer_size, let's take a look at key_buffer_size usage:

Mysql > show global status like 'key_read%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Key_read_requests | 27813678764 |

| | Key_reads | 6798830 | |

+-+ +

There are 27813678764 index read requests, of which 6798830 are not found in memory to read the index directly from the hard disk. Calculate the probability that the index misses the cache:

Key_cache_miss_rate = Key_reads / Key_read_requests * 100%

Compared with the above data, the key_cache_miss_rate is 0.0244% per 4,000 index read requests before there is one direct read hard disk, which is already very BT. The key_cache_miss_rate below 0.1% is good (there is one direct read hard disk for every 1000 requests). If the key_cache_miss_rate is less than 0.01%, the key_buffer_size allocation is too much, which can be reduced appropriately.

The MySQL server also provides key_blocks_* parameters:

Mysql > show global status like 'key_blocks_u%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Key_blocks_unused | 0 | |

| | Key_blocks_used | 413543 | |

+-+ +

Key_blocks_unused represents the number of unused cache clusters (blocks), and Key_blocks_used indicates the maximum number of blocks ever used, such as this server, where all the cache is used, either adding key_buffer_size or transitional indexes, filling up the cache. Ideal settings:

Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%

IV. Temporary watch

Mysql > show global status like 'created_tmp%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Created_tmp_disk_tables | 21197 | |

| | Created_tmp_files | 58 | |

| | Created_tmp_tables | 1771587 | |

+-+ +

Each time a temporary table is created, Created_tmp_tables increases. If a temporary table is created on disk, Created_tmp_disk_tables also increases. Created_tmp_files represents the number of temporary files created by the MySQL service. The ideal configuration is:

Created_tmp_disk_tables / Created_tmp_tables * 100% show variables where Variable_name in ('tmp_table_size',' max_heap_table_size')

+-+ +

| | Variable_name | Value |

+-+ +

| | max_heap_table_size | 268435456 | |

| | tmp_table_size | 536870912 | |

+-+ +

Only temporary tables below 256MB can be stored in memory, and hard disk temporary tables will be used if they are exceeded.

V. Open Table situation

Mysql > show global status like 'open%tables%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Open_tables | 919 | |

| | Opened_tables | 1951 | |

+-+ +

Open_tables indicates the number of open tables, and Opened_tables indicates the number of opened tables. If the number of Opened_tables is too large, the value of table_cache (called table_open_cache after 5.1.3) in the configuration may be too small. Let's query the server table_ cache value:

Mysql > show variables like 'table_cache'

+-+ +

| | Variable_name | Value |

+-+ +

| | table_cache | 2048 | |

+-+ +

The more appropriate values are:

Open_tables / Opened_tables * 100% > = 85%

Open_tables / table_cache * 100% show global status like 'Thread%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Threads_cached | 46 | |

| | Threads_connected | 2 | |

| | Threads_created | 570 |

| | Threads_running | 1 | |

+-+ +

If we set thread_cache_size in the MySQL server configuration file, when the client is disconnected, the thread that the server processes this client will be cached to respond to the next client instead of destroying it (provided that the cache number does not reach the limit). Threads_created indicates the number of threads created. If the Threads_created value is too high, it means that the MySQL server has been creating threads, which is also resource-consuming. You can appropriately increase the value of thread_cache_ size in the configuration file to query the server thread_cache_size configuration:

Mysql > show variables like 'thread_cache_size'

+-+ +

| | Variable_name | Value |

+-+ +

| | thread_cache_size | 64 | |

+-+ +

The server in the example is quite healthy.

7. Query cache (query cache)

Mysql > show global status like 'qcache%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Qcache_free_blocks | 22756 | |

| | Qcache_free_memory | 76764704 | |

| | Qcache_hits | 213028692 | |

| | Qcache_inserts | 208894227 | |

| | Qcache_lowmem_prunes | 4010916 | |

| | Qcache_not_cached | 13385031 | |

| | Qcache_queries_in_cache | 43560 | |

| | Qcache_total_blocks | 111212 | |

+-+ +

MySQL query cache variable explanation:

Qcache_free_blocks: the number of adjacent memory blocks in the cache. A large number means there may be fragments. FLUSH QUERY CACHE will defragment the cache to get a free block.

Qcache_free_memory: free memory in the cache.

Qcache_hits: increments every time a query hits in the cache

Qcache_inserts: increments each time a query is inserted. The miss ratio is the number of hits divided by the number of inserts.

Qcache_lowmem_prunes: the number of times the cache runs out of memory and must be cleaned to provide space for more queries. This number is best seen over a long period of time; if it is growing, it means that the fragmentation may be very serious, or there may be very little memory. (the free_blocks and free_memory above can tell you which case it is.)

Qcache_not_cached: the number of queries that are not suitable for caching, usually because they are not SELECT statements or use functions such as now ().

Qcache_queries_in_cache: the number of queries (and responses) currently cached.

Qcache_total_blocks: the number of blocks in the cache.

Let's check the server's configuration for query_cache again:

Mysql > show variables like 'query_cache%'

+-+ +

| | Variable_name | Value |

+-+ +

| | query_cache_limit | 2097152 | |

| | query_cache_min_res_unit | 4096 | |

| | query_cache_size | 203423744 | |

| | query_cache_type | ON |

| | query_cache_wlock_invalidate | OFF |

+-+ +

Explanation of each field:

Query_cache_limit: queries larger than this size will not be cached

Query_cache_min_res_unit: minimum size of the cache block

Query_cache_size: query cache size

Query_cache_type: cache type, which determines what kind of query to cache. In the example, select sql_no_cache query is not cached.

Query_cache_wlock_invalidate: when another client is writing to the MyISAM table, if the query is in query cache, whether to return the cache result or wait for the write operation to complete and then read the table to get the result.

Query_cache_min_res_unit configuration is a "double-edged sword", the default is 4KB, set a large value for big data query is good, but if your queries are small data queries, it is easy to cause memory fragmentation and waste.

Query cache fragmentation rate = Qcache_free_blocks / Qcache_total_blocks * 100%

If the query cache fragmentation rate exceeds 20%, you can use FLUSH QUERY CACHE to defragment the cache, or try to reduce query_cache_min_res_unit, if your query is a small amount of data.

Query cache utilization = (query_cache_size-Qcache_free_memory) / query_cache_size * 100%

If the query cache utilization is below 25%, it means that the query_cache_size setting is too large and can be reduced appropriately; if the query cache utilization is above 80% and the Qcache_lowmem_prunes is more than 50, the query_cache_size may be a little small, or there may be too many fragments.

Query cache hit ratio = (Qcache_hits-Qcache_inserts) / Qcache_hits * 100%

Example server query cache fragmentation rate = 20.46%, query cache utilization = 62.26%, query cache hit rate = 1.94%, the hit rate is very poor, maybe write operations are more frequent, and there may be some fragments.

VIII. Sorting usage

Mysql > show global status like 'sort%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Sort_merge_passes | 29 | |

| | Sort_range | 37432840 | |

| | Sort_rows | 9178691532 | |

| | Sort_scan | 1860569 | |

+-+ +

Sort_merge_passes consists of two steps. MySQL will first try to sort in memory, and the memory size used is determined by the system variable Sort_buffer_size. If it is not big enough to read all the records into memory, MySQL will save the results of each sort in memory to a temporary file, and wait for MySQL to find all the records, and then sort the records in the temporary file again. This sort again increases the Sort_merge_passes. In fact, MySQL uses another temporary file to store the re-sorted results, so it is common to see that Sort_merge_passes increases by twice the number of temporary files created. Because temporary files are used, the speed may be slow, and increasing Sort_buffer_size reduces the number of times Sort_merge_passes and temporary files are created. But blindly increasing Sort_buffer_size does not necessarily improve speed.

In addition, increasing the value of read_rnd_buffer_size (3.2.3 is record_rnd_buffer_size) also has a little benefit for sorting operations.

IX. Number of document openings (open_files)

Mysql > show global status like 'open_files'

+-+ +

| | Variable_name | Value |

+-+ +

| | Open_files | 1410 | |

+-+ +

Mysql > show variables like 'open_files_limit'

+-+ +

| | Variable_name | Value |

+-+ +

| | open_files_limit | 4590 | |

+-+ +

More appropriate setting: Open_files / open_files_limit * 100% show global status like 'table_locks%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Table_locks_immediate | 490206328 | |

| | Table_locks_waited | 2084912 | |

+-+ +

Table_locks_immediate indicates the number of table locks to be released immediately, and Table_locks_waited indicates the number of table locks to wait. If Table_locks_immediate / Table_locks_waited > 5000, it is best to use InnoDB engine. Because InnoDB is a row lock and MyISAM is a table lock, InnoDB works better for applications with high concurrent writes. The server in the example, Table_locks_immediate / Table_locks_waited = 235 ~ MyISAM, is sufficient.

11. Table scanning

Mysql > show global status like 'handler_read%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Handler_read_first | 5803750 | |

| | Handler_read_key | 6049319850 | |

| | Handler_read_next | 94440908210 | |

| | Handler_read_prev | 34822001724 | |

| | Handler_read_rnd | 405482605 | |

| | Handler_read_rnd_next | 18912877839 |

+-+ +

For explanation of each field, see the number of query requests completed by the call-up server:

Mysql > show global status like 'com_select'

+-+ +

| | Variable_name | Value |

+-+ +

| | Com_select | 222693559 | |

+-+ +

Calculate the table scan rate:

Table scan rate = Handler_read_rnd_next / Com_select

If the table scan rate exceeds 4000, there are too many table scans, and it is very likely that the index is not well built. Increasing the read_buffer_size value has some benefits, but it is best not to exceed 8MB.

To check the deadlock, you need show engine innodb status\ G

In the MySQL5.6 version, in the my.cnf configuration file, add

Innodb_print_all_deadlocks = 1

You can print the deadlock information to the error log.

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.