Link to the original text :http://www.cnblogs.com/kevingrace/p/6133818.html Thank you

1. Brief introduction

InnoDB to MySQL Provided with commit , Transaction security of rollback and crash recovery capability (ACID compatible ) Storage engine .InnoDB Locked at line level and also at SELECT Statement provides a Oracle Style consistent non lock reading . These features increase multi-user deployment and performance . Not in the InnoDB The need to expand locking in , Because in InnoDB Middle row locking is suitable for very small spaces .InnoDB Also support FOREIGN KEY mandatory . stay SQL Querying , You are free to put InnoDB Type of table and other MySQL The types of the tables are mixed up , Even in the same query can be mixed .

2. Why choose innodb As a storage engine
For the moment ,InnoDB Is for Mysql Design for maximum performance when dealing with huge amounts of data . its CPU Efficiency may be unmatched by any other disk based relational database engine . In websites or applications with large amount of data Innodb It's very popular .
On the other hand , In the database replication operation Innodb It can also be guaranteed master and slave Data consistency has a role .

3. Here's the alignment mysql5.6 Version of the database configuration optimization analysis records :
1) Memory utilization :
innodb_buffer_pool_size
This is Innodb The most important parameter , and MyISAM Of key_buffer_size There are similarities , But there are differences .
This parameter is mainly cached innodb Index of tables , data , Buffer when inserting data .
The principle of allocating memory for this parameter :
This parameter is assigned only by default 8M, It's a very small number, so to speak .
If it's a dedicated DB The server , So he can take up the memory 70%-80%.
This parameter cannot be changed dynamically , So there's a lot to think about . Distribution is too large , Can make Swap Take up too much , the Mysql Is extremely slow .
If your data is small , So allocable is your data size +10% Left and right as the value of this parameter .
for example : Data size is 50M, So assign this value innodb_buffer_pool_size=64M
Setup method , stay my.cnf In the document :
innodb_buffer_pool_size=4G
----------------------------------------------------------------------------------------------------------
Be careful :
stay Mysql5.7 Before the release , adjustment innodb_buffer_pool_size The size has to be in my.cnf Change in configuration , And then restart mysql The process can take effect .
Now it is Mysql5.7 edition , You can dynamically adjust this parameter directly , A lot of convenience .

Especially after the server memory increases , Operation and maintenance personnel should not be careless , Remember to turn it up Innodb_Buffer_Pool_size This parameter .
After the database is configured , Pay attention to check Innodb_Buffer_Pool_size Is the setting of this parameter reasonable

What to pay attention to :
Adjusting innodb_buffer_pool_size period , The user's request will block , Until the adjustment is completed , So don't adjust during the day , In the morning 3-4 Point low peak period adjustment .
When adjusting , Internally move the data page to a new location , The unit is block . If you want to increase the speed of movement , Need to adjust innodb_buffer_pool_chunk_size The size of the parameter , The default is 128M.

Mysql5.7 The operation record of dynamically adjusting this parameter in ( For example, by 128M Increase to 384M):
134217728/1024*1024=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)

innodb_buffer_pool_chunk_size Size , The formula is innodb_buffer_pool_size/innodb_buffer_pool_instances

For example, now initialize innodb_buffer_pool_size by 2G,innodb_buffer_pool_instances Example is 4,innodb_buffer_pool_chunk_size Set to 1G, Then it will automatically innodb_buffer_pool_chunk_size
1G Adjusted for 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 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)

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

( Reduce )

[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. (253952/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
For storage Innodb Internal directory , This value doesn't have to be assigned too much , The system can automatically adjust . Usually set 16M Will be enough , If there are more tables , It can be increased properly .
Setup method , stay my.cnf In the document :
innodb_additional_mem_pool_size = 16M

2) About logs :
innodb_log_file_size
effect : Specify in a log group , Every log Size .
combination innodb_buffer_pool_size Set its size ,25%-100%. Avoid unnecessary refresh .
Be careful : The size of this value allocation and the write speed of the database , Transaction size , Recovery after abnormal restart has a lot to do with . Usually take 256M It can balance performance and recovery The speed of .
The principle of distribution : The size of a few daily members is about the same as yours innodb_buffer_pool_size equal . The upper limit is per day, and the upper limit is 4G. Generally controlled in a few Log The total size of the file is 2G Better within . It depends on the size of your transaction , Based on the size of the data .
explain : The size of this value allocation and the write speed of the database , Transaction size , Recovery after abnormal restart has a lot to do with .
Setup method : stay my.cnf In the document :
innodb_log_file_size = 256M

innodb_log_files_in_group
effect : Specify how many day value groups you have .
The principle of distribution : Generally, we can use 2-3 A daily value group . The default is two .
Setup method : stay my.cnf In the document :
innodb_log_files_in_group=3

innodb_log_buffer_size:
effect : Buffering of transactions in memory , That is, the size of the log buffer , The default setting is fine , With a large number of transactions, consider setting to 16M.
If this value grows too fast , It can be increased appropriately innodb_log_buffer_size
In addition, if you need to deal with Dali's TEXT, or BLOB Field , The value of this parameter can be increased .
Setup method : stay my.cnf In the document :
innodb_log_buffer_size=3M

innodb_flush_logs_at_trx_commit
effect : Control how transactions are committed , Control log How to refresh to disk .
The principle of distribution : This parameter is only 3 It's worth (0,1,2). The default is 1, Higher performance can be set to 0 or 2, This can reduce the number of disks appropriately IO( But one second of transactions will be lost .), Game library's MySQL Recommended setting is 0. Please don't change the main library .
among :
0:log buffer The data in will be written to... Once per second log file in , The files are synchronized to the operating system at the same time , But for every transaction commit It doesn't trigger any log buffer To log file Or file system to disk ;
1:( The default is 1) At each transaction commit time logbuffer All data in is written to log file, It also triggers file system to disk synchronization ;
2: Transaction commit triggers log buffer To log file Refresh of , But it doesn't trigger disk file system to disk synchronization . Besides , There will be one file system to disk synchronization per second .
explain :
The setting of this parameter is very important for Innodb Has a big impact on the performance of , So here's a little more explanation .
When the value is 1 when :innodb The business of LOG Write the daily value file after each commit , And refresh the daily value to disk . This can be done without losing any transaction .
When the value is 2 when : In each submission , The log buffer is written to the file , But do not refresh the log file by disk operation , In the refresh of the log file, the value is 2 It happens every second . But it should be noted that , Due to problems with process calls , There's no guarantee that every second 100% Happen . So it's the fastest in terms of performance . But the last second transaction will be deleted only when the operating system crashes or loses power .
When the value is 0 when : The log buffer is written to the log file once a second , And refresh the log file with disk operation , But in a transaction commit do nothing .mysqld Process crash will delete the last second transaction before crash .
From the above analysis , When this value is not 1 when , Can achieve better performance , But there's a loss when it comes to anomalies , So we need to measure it according to our own situation .
Setup method : stay my.cnf In the document :
innodb_flush_logs_at_trx_commit=1

3) file IO Distribute , In terms of space occupation
innodb_file_per_table
effect : Make each Innodb Table of , Has its own independent table space . If you delete a file, you can recycle that space . The default is off , Suggest opening (innodb_file_per_table=1)
The principle of distribution : Only use not use . but DB There also needs to be a public table space .
Setup method : stay my.cnf In the document :
innodb_file_per_table=1

innodb_file_io_threads
effect : File read and write IO Count , This parameter is only in Windows It works . stay Linux It will only be equal to 4, The default can be !
Setup method : stay my.cnf In the document :
innodb_file_io_threads=4

innodb_open_files
effect : Limit Innodb Data of tables that can be opened .
The principle of distribution : The default value is 300. If there are too many watches in the library , It can be increased to 1000.innodb_open_files The size is right InnoDB The impact of efficiency is relatively small . But in InnoDBcrash Under the circumstances ,innodb_open_files Setting too small will affect recovery The efficiency of . So use InnoDB I still put innodb_open_files It's better to zoom in .
Setup method : stay my.cnf In the document :
innodb_open_files=800

innodb_data_file_path
Specify the storage space of table data and index , It can be one or more files . The last data file must be automatically expanded , Only the last file can be expanded automatically . such , When the space is used up , Automatically expanding data files will automatically grow ( With 8MB In units of ) To accommodate 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 in , When reach 900M in the future , The data is in ibdata2 in .
Setup method , stay my.cnf In the document :
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 table space data is placed , Default in mysql Data directory for , Set to and MySQL Different partitions of installation files can improve performance .
Setup method , stay my.cnf In the document :( such as mysql The data directory for is /data/mysql/data, You can set it to a different partition /home/mysql Next )
innodb_data_home_dir = /home/mysql

4) Other related parameters ( Appropriate increase table_cache)
Here is an important parameter :
innodb_flush_method
effect :Innodb One that deals with systems IO Model
The principle of distribution :
Windows No settings .
linux You can choose :O_DIRECT
Write directly to disk , Disable the system Cache 了
Setup method : stay my.cnf In the document :
innodb_flush_method=O_DIRECT

innodb_max_dirty_pages_pct
effect : stay buffer pool Buffering , allow Innodb The percentage of dirty pages , Value in range 1-100, The default is 90, It is recommended to keep the default .
Another use of this parameter : When Innodb The memory allocation of is too large , the Swap When occupation is serious , You can adjust this value by reducing it , To achieve Swap Free up space . Jianyi : The maximum value is 90%, The least is 15%. Too big , There are too many data pages to be paged for each update in the cache , Too small , The data page is too small , Update operation is too slow .
Setup method : stay my.cnf In the document :
innodb_max_dirty_pages_pct=90
Dynamic changes require administrator privileges :
set global innodb_max_dirty_pages_pct=50;

innodb_thread_concurrency
At the same time Innodb The number of threads processed in the kernel . The default value is recommended .
Setup method , stay my.cnf In the document :
innodb_thread_concurrency = 16

5) Common parameter tuning
skip-external-locking
MyISAM The storage engine also uses this parameter ,MySQL4.0 after , This value is on by default .
The effect is to avoid MySQL External locking of ( The old version of MySQL This parameter is called skip-locking), Reduce the chance of error and increase stability . The default value is recommended .
Setup method , stay my.cnf In the document :
skip-external-locking

skip-name-resolve
prohibit MySQL For external connections DNS analysis ( This setting is turned off by default , That is, default parsing DNS), Use this option to eliminate MySQL Conduct DNS Time of resolution .
But we need to pay attention , If this option is turned on , Then all remote host connection authorizations must use IP Address , otherwise MySQL Connection requests will not be processed properly ! if necessary , You can set this .
Setup method , stay my.cnf In the document :( I'm on the line mysql This setting is turned on in the database )
skip-name-resolve

max_connections
Set the maximum connection ( user ) Count , Each connection MySQL All users count as a connection ,max_connections The default value is 100. This value needs to be set according to the specific peak number of connections .
Setup method , stay my.cnf In the document :
max_connections = 3000

query_cache_size
Query cache size , If the table changes very frequently , Or each query is different , The results of query caching can slow down system performance . It can be set to 0.
Setup method , stay my.cnf In the document :
query_cache_size = 512M

sort_buffer_size
connection Parameters of the level , Sort cache size . Generally set as 2-4MB that will do .
Setup method , stay my.cnf In the document :
sort_buffer_size = 1024M

read_buffer_size
connection Parameters of the level . Generally set as 2-4MB that will do .
Setup method , stay my.cnf In the document :
read_buffer_size = 1024M

max_allowed_packet
The size of the network packet , To avoid large network packet errors , Recommended setting is 16M
Setup method , stay my.cnf In the document :
max_allowed_packet = 16M

table_open_cache
When a connection accesses a table ,MySQL The current number of cached tables is checked . If the table is already open in the cache , The table in the cache is accessed directly , To speed up queries ; If the table is not cached , The current table will be added to the cache and queried .
By checking the state value of the peak time Open_tables and Opened_tables, You can decide whether you need to add table_open_cache Value .
If you find that open_tables be equal to table_open_cache, also opened_tables It's growing , Then we need to increase table_open_cache Value ; Set to 512 To meet the needs .
Setup method , stay my.cnf In the document :
table_open_cache = 512

myisam_sort_buffer_size
Actually this myisam_sort_buffer_size The parameter is of little significance , This is a literal parameter , It is used for ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE Memory needed to wait for a command . The default value is .
Setup method , stay my.cnf In the document :
myisam_sort_buffer_size = 8M

thread_cache_size
Thread cache , If a client is disconnected , This thread will be put into thread_cache_size in ( The buffer pool is not full ),SHOW
STATUS LIKE 'threads%'; If Threads_created Growing , Then the current value should be increased , Change to
Threads_connected It's worth about .( Usually , This value does not improve performance very much ), Default 8 that will do
Setup method , stay my.cnf In the document :
thread_cache_size = 8

innodb_thread_concurrency
Thread concurrency , Recommended setting is CPU Number of cores *2
Setup method , stay my.cnf In the document :
innodb_thread_concurrency = 8

key_buffer_size
Only for
MyISAM Storage engine , Used to set for caching
MyISAM The memory area size of the index file in the storage engine . If we have enough memory , It's better to store all our data in this cache area MyISAM
All indexes of engine tables , To maximize performance . Don't set more than the available memory 30%. Even if not MyISAM surface , Also set the value 8-64M, For a temporary watch .
Setup method , stay my.cnf In the document :
key_buffer_size = 8M

-----------------------------------------------------------------------------------------------------------------------------------------------
Here's the online one mysql(innodb) Of my.cnf Configure the reference :
[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 = 8192M

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

1. Connection request variables :
1) max_connections
MySQL Is the maximum number of connections , Increase this value by mysqld

Number of file descriptors required . 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 fact that the machine can support , Because if there are more connections ,
Be situated between MySQL A connection buffer is provided for each connection , It's going to cost more memory , So adjust the value properly , Do not blindly increase the set value .
Too small a number often appears ERROR 1040: Too many connections error , You can go through ’conn%’ Wildcards view the number of connections in the current state , To determine the size of the value .
show variables like ‘max_connections’ maximum connection
show status like ‘max_used_connections’ Number of connections responding
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 max_used_connections Follow max_connections identical So that is max_connections The setting is too low or the server load limit is exceeded , lower than 10% The setting is too large .
2) back_log
MySQL Number of connections that can be staged . Be the main MySQL Threads get a lot of connection requests in a short time , It works . If MySQL The connection data of
max_connections when , New requests will be stored in the stack , To wait for a connection to release resources , The number of stacks is back_log, If the number of waiting connections exceeds
back_log, Will not be granted connection resources .
back_log The value is in MySQL How many requests can be stored in the stack in a short time before you temporarily stop answering new requests . Only if you expect a lot of connections in a short time , You need to add it , let me put it another way , It's worth the coming TCP/IP The size of the listening queue for the connection .
When looking at your host process list (mysql>
show full processlist), Find a lot 264084 | unauthenticated user |
xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL
To connect to the process , It's going to increase back_log The value of the .
The default value is 50, Tunable to 128, Set the range for the system to be less than 512 The integer of .
3) interactive_timeout
The number of seconds an interactive connection waits for action before being shut down by the server . An interactive client is defined as being right mysql_real_connect() Use CLIENT_INTERACTIVE Option customers .
The default value is 28800, Tunable to 7200.
5) query_cache_size
Use query buffer ,MySQL Store query results in a buffer , In the future for the same SELECT sentence ( Case sensitive ), The result will be read directly from the buffer .
By checking the status values Qcache_*, You can know query_cache_size Whether the setting is reasonable or not ( The above status values can be used SHOW
STATUS LIKE
‘Qcache%’ get ). If Qcache_lowmem_prunes It's worth a lot , It indicates that insufficient buffer often occurs , If Qcache_hits The value of is also

A very large , It shows that query buffer is used very frequently , You need to increase the buffer size ; If Qcache_hits It's not worth much , It shows that your query repetition rate is very low , In this case, the query buffer is used
And it affects efficiency , So we can consider not to query buffer . Besides , stay SELECT Add to statement SQL_NO_CACHE It can be made clear that query buffering is not used .

There are also parameters related to query buffering query_cache_type、query_cache_limit、query_cache_min_res_unit.
query_cache_type Specifies whether to use query buffer , It can be set to 0、1、2, The variable is SESSION Level variables .
query_cache_limit Specify the buffer size that a single query can use , Default is 1M.
query_cache_min_res_unit Is in 4.1 Version later , It specifies the minimum unit of buffer space allocated , Default is 4K. Check the status value
Qcache_free_blocks, If the value is very large , It indicates that there are many fragments in the buffer , This shows that the query results are relatively small , This needs to be reduced
query_cache_min_res_unit.
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%, It can be used FLUSH QUERY CACHE Defragment cache , Or try reducing query_cache_min_res_unit, If your queries are small data .
Query cache utilization = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%
Query cache utilization is 25% Here's a description query_cache_size Set too big , It can be reduced properly ; Query cache utilization is 80% Above and Qcache_lowmem_prunes > 50 The words of query_cache_size It may be a little small , Or there are too many pieces .
Query cache hit rate = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%
Sample 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 I write more frequently , And there may be some pieces .
Buffer per connection
6) record_buffer_size
Each thread that performs a sequential scan allocates a buffer of this size for each table it scans . If you do a lot of sequential scanning , You may want to increase the value .
The default value is 131072(128K), Can be changed to 16773120 (16M)
7) read_rnd_buffer_size
Random read buffer size . When reading rows in any order ( for example , In sort order ), A random read buffer will be allocated . When sorting queries ,MySQL The buffer will be scanned first , To avoid

Disk free search , Improve query speed , If you need to sort a lot of data , The value can be increased properly . but MySQL This buffer will be issued for each customer connection , Therefore, the value should be set as appropriate as possible , To avoid the memory being turned on
Excessive sales .
It can be set to 16M
8) sort_buffer_size
Each thread that needs to be sorted allocates a buffer of that size . Increase this value to accelerate ORDER BY or GROUP BY operation .
The default value is 2097144(2M), Can be changed to 16777208 (16M).
9) join_buffer_size
The size of the buffer that can be used by the federated query operation
record_buffer_size,read_rnd_buffer_size,sort_buffer_size,join_buffer_size Exclusive for each thread , in other words , If there is 100 Thread connections , The occupation is 16M*100
10) table_cache
Table cache size . whenever MySQL When accessing a table , If there is still space in the table buffer , The watch is opened and put into it , This allows faster access to table content . By checking the state value of the peak time Open_tables and Opened_tables, You can decide whether you need to add table_cache Value . Such as

If you find out open_tables be equal to table_cache, also opened_tables It's growing , Then you need to add table_cache The value of the
( The above status values can be used SHOW STATUS LIKE
‘Open%tables’ get ). Be careful , Don't blindly put table_cache Set to a large value . If it's set too high , May cause insufficient file descriptors , This results in performance
Unstable or connection failed .
1G Memory machine , Recommended value is 128-256. In memory 4GB This parameter can be set to 256M or 384M.
11) max_heap_table_size
Memory tables that users can create (memory table) Size . This value is used to calculate the maximum row value of a memory table . This variable supports dynamic changes , namely set @max_heap_table_size=#
This variable and tmp_table_size Together, it limits the size of the internal memory table . If something inside heap( Pile up ) The size of the watch is larger than tmp_table_size,MySQL You can automatically set the heap The table is based on the hard disk MyISAM surface .
12) tmp_table_size
By setting tmp_table_size Option to increase the size of a temporary table , For example, do advanced GROUP

BY Operation generated temporary table . If you raise the value ,MySQL At the same time, it will increase heap The size of the table , It can achieve the effect of improving the speed of connection query , It is recommended to optimize the query as much as possible , Make sure that the temporary tables generated during the query are in memory , Avoid temporary tables that are too large to generate hard disk based MyISAM surface .
mysql> show global status like ‘created_tmp%‘;
+——————————–+———+
| Variable_name | Value |
+———————————-+———+
| Created_tmp_disk_tables | 21197 |
| Created_tmp_files | 58 |
| Created_tmp_tables | 1771587 |
+——————————–+———–+
Every time you create a temporary table ,Created_tmp_tables increase , If the size of the temporary table exceeds tmp_table_size, Is to create a temporary

surface ,Created_tmp_disk_tables Also increase ,Created_tmp_files Express MySQL Number of temporary files created by the service , Ideal match
It's :
Created_tmp_disk_tables / Created_tmp_tables * 100% <=
25% For example, the server above Created_tmp_disk_tables / Created_tmp_tables * 100%
=1.20%, It should be pretty good
The default is 16M, Adjustable to 64-256 The best , Thread exclusive , Too big maybe not enough memory I/O jam
13) thread_cache_size
The number of threads that can be reused in . If there is , The new thread gets... From the cache , When disconnected, if there is space , The customer's line is in the cache . If there are many new threads , In order to improve performance, you can use this variable value .
By comparison Connections and Threads_created Variables of state , You can see the effect of this variable .
The default value is 110, Tunable to 80.
14) thread_concurrency
The recommended setting is server CPU Nuclear 2 times , For example, dual core CPU, that thread_concurrency Of should be 4;2 Two core cpu, thread_concurrency The value should be 8. The default is 8
15) wait_timeout
Specify the maximum connection time for a request , about 4GB Left and right memory servers can be set to 5-10.
One 、 The 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 |
+---------------------+-------+
Log slow query is turned on in the configuration , Execution time exceeded 2 Second is slow query , The system shows 4148 Slow query , You can analyze slow query logs , Find out what's wrong SQL sentence , Slow query time should not be set too long , Otherwise it doesn't make much sense , Best in 5 Within seconds , If you need microsecond level slow queries , Consider giving MySQL patch up :http://www.percona.com/docs/wiki/release:start, Remember to find the corresponding version .
Turning on slow query logs may have a little impact on system performance , If your MySQL It is the Lord. - From the structure , Consider opening the slow query log of one of the slave servers , In this way, slow queries can be monitored , It has little effect on system performance .
Two 、 The number of connections
I often meet ”MySQL: ERROR 1040: Too manyconnections” The situation of , One is that the traffic is really high ,MySQL Server can't resist , At this time, we should consider increasing the pressure of decentralized reading from the server , The other is MySQL In profile max_connections It's too small :
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 256 |
+-----------------+-------+
The machine MySQL The maximum number of server connections is 256, Then query the maximum number of connections that the server responds to :
mysql> show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 245 |
+----------------------+-------+
MySQL The maximum number of connections to the server in the past was 245, The maximum number of server connections has not been reached 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... Of the maximum number of connections 85% about , If it turns out that the ratio is 10% following ,MySQL The maximum number of server connections is set too high .
3、 ... and 、Key_buffer_size
key_buffer_size It's right MyISAM Table performance has the biggest impact on one of the parameters , The next one is for MyISAM Configuration for the primary storage engine server :
mysql> show variables like 'key_buffer_size';
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| key_buffer_size | 536870912 |
+-----------------+------------+
Allocated 512MB Memory for key_buffer_size, Let's see key_buffer_size Usage situation :
mysql> show global status like 'key_read%';
+------------------------+-------------+
| Variable_name | Value |
+------------------------+-------------+
| Key_read_requests | 27813678764 |
| Key_reads | 6798830 |
+------------------------+-------------+
Altogether 27813678764 Index read requests , Yes 6798830 Requests were not found in memory to read the index directly from the hard disk , Calculate the probability of index Miss cache :
key_cache_miss_rate = Key_reads / Key_read_requests * 100%
Than
Like the data above ,key_cache_miss_rate by 0.0244%,4000 Only one index read request has one direct read hard disk , It's already very BT
了 ,key_cache_miss_rate stay 0.1% The following are all very good ( Every time 1000 One request has a direct read hard disk ), If key_cache_miss_rate stay
0.01% The following words ,key_buffer_size Too much distribution , Can be reduced appropriately .
MySQL The 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 an unused cache cluster (blocks) Count ,Key_blocks_used That's the largest that's ever been used blocks Count , Like this server , All the caches are used , or
increase key_buffer_size, It's either a transitional index , The cache is full . Ideal setup :
Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%
Four 、 A temporary table
mysql> show global status like 'created_tmp%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Created_tmp_disk_tables | 21197 |
| Created_tmp_files | 58 |
| Created_tmp_tables | 1771587 |
+-------------------------+---------+
Every time you create a temporary table ,Created_tmp_tables increase , If you are creating a temporary table on disk ,Created_tmp_disk_tables Also increase ,Created_tmp_files Express MySQL Number of temporary files created by the service , The ideal configuration is :
Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%
For example, the server above Created_tmp_disk_tables / Created_tmp_tables * 100% = 1.20%, It should be pretty good . Let's see MySQL Server configuration of temporary tables :
mysql> 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 256MB Only the following temporary tables can be stored in memory , If you exceed the limit, you will use the hard disk temporary table .
5、 ... and 、Open Table situation
mysql> show global status like 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 919 |
| Opened_tables | 1951 |
+---------------+-------+
Open_tables
Represents the number of open tables ,Opened_tables Represents the number of tables that have been opened , If Opened_tables Too many , Description configuration
table_cache(5.1.3 And then this value is called table_open_cache) The value may be too small , Let's check the server table_cache value :
mysql> show variables like 'table_cache';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| table_cache | 2048 |

+---------------+-------+
The more suitable value is :
Open_tables / Opened_tables * 100% >= 85%
Open_tables / table_cache * 100% <= 95%

6、 ... and 、 Process usage
mysql> show global status like 'Thread%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 46 |
| Threads_connected | 2 |
| Threads_created | 570 |
| Threads_running | 1 |
+-------------------+-------+
Such as

If we are MySQL The server configuration file has thread_cache_size, When the client is disconnected , The threads that the server processes for this client will be cached in response to the next client
Instead of destroying ( If the number of caches is not up to the maximum ).Threads_created Represents the number of threads created , If you find that Threads_created If it's too much , indicate
MySQL The server has been creating threads , It's also quite resource intensive , You can add thread_cache_size value , Query server
thread_cache_size To configure :
mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 64 |
+-------------------+-------+
The server in the example is quite healthy .
7、 ... and 、 The 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 interpretation :
Qcache_free_blocks: The number of adjacent memory blocks in the cache . A large number means there may be debris .FLUSH QUERY CACHE Defragments the cache , To get a free block .
Qcache_free_memory: Free memory in the cache .
Qcache_hits: Every time a query hits in the cache, it grows
Qcache_inserts: Increases 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 ran out of memory and had to be cleaned up to provide space for more queries . It's better to look at this number for a long time ; If this number is growing , It means that the debris may be very serious , Or memory
Very few .( above free_blocks and free_memory Can tell you what kind of situation )
Qcache_not_cached: The number of queries that are not suitable for caching , Usually because these queries are not SELECT Sentence or used now() Functions like that .
Qcache_queries_in_cache: Queries currently cached ( And response ) The number of .
Qcache_total_blocks: The number of blocks in the cache .
Let's check the server again about query_cache Configuration of :
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: The minimum size of the cache block
query_cache_size: Query cache size
query_cache_type: Cache type , Decide what queries to cache , In the example, it means no cache select sql_no_cache Inquire about
query_cache_wlock_invalidate: When other clients are working on MyISAM When a table is written , If the query is in query cache in , Whether to return cache The result is to wait for the write operation to complete and then read the table to get the result .
query_cache_min_res_unit The configuration of is a ” Double-edged sword ”, The default is 4KB, Large settings are good for big data query , But if your queries are all 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%, It can be used FLUSH QUERY CACHE Defragment cache , Or try reducing query_cache_min_res_unit, If your queries are small data .
Query cache utilization = (query_cache_size - Qcache_free_memory) / query_cache_size * 100%
Query cache utilization is 25% Here's a description query_cache_size Set too big , It can be reduced properly ; Query cache utilization is 80% Above and Qcache_lowmem_prunes > 50 The words of query_cache_size It may be a little small , Or there are too many pieces .
Query cache hit rate = (Qcache_hits - Qcache_inserts) / Qcache_hits * 100%
Sample 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 I write more frequently , And there may be some pieces .
8、 ... and 、 Sort 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
There are two steps .MySQL First try sorting in memory , The amount of memory used is determined by the system variable Sort_buffer_size
decision , If it's not big enough, read all the records into memory ,MySQL The result of each sort in memory will be saved in a temporary file , etc. MySQL
After finding all the records , Sort the records in the temporary file again . This sort again will increase
Sort_merge_passes. actually ,MySQL Another temporary file will be used to store the result of reordering , So you usually see
Sort_merge_passes The increase is twice the number of temporary files created . Because temporary files are used , So the speed may be slow , increase
Sort_buffer_size Will be reduced Sort_merge_passes and The number of times a temporary file was created . But blindly increasing
Sort_buffer_size It doesn't necessarily increase speed ,
in addition , increase read_rnd_buffer_size(3.2.3 yes record_rnd_buffer_size) The value of is also a little good for sorting operations ,
Nine 、 Number of open files (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 settings :Open_files / open_files_limit * 100% <= 75%
Ten 、 Watch lock condition
mysql> 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 released immediately ,Table_locks_waited Indicates the number of table locks to wait for , If Table_locks_immediate /
Table_locks_waited
>5000, It's better to use InnoDB engine , because InnoDB It's the line lock and MyISAM It's a watch lock , For high concurrency write applications InnoDB It will be better . The service in the example
device Table_locks_immediate / Table_locks_waited = 235,MyISAM That's enough .
11、 ... and 、 Table scan
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 the explanation of each field, please refer to , The number of query requests completed by the call out 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, Too many table scans , It's very likely that the index isn't built , increase read_buffer_size Value has some benefits , But it's better not to exceed 8MB.

To see deadlocks , Do you want to show engine innodb status\G;
stay MySQL5.6 edition , stay my.cnf In the configuration file , Join in
innodb_print_all_deadlocks = 1
You can print the deadlock information to the error log

Mysql Optimization series --Innodb Under the engine mysql Self configuration optimization - More related articles

  1. Mysql Optimization series (1)--Innodb Under the engine mysql Self configuration optimization

    1. Brief introduction InnoDB to MySQL Provided with commit , Transaction security of rollback and crash recovery capability (ACID compatible ) Storage engine .InnoDB Locked at line level and also at SELECT Statement provides a Oracle Style consistent non lock reading . These characteristics ...

  2. Innodb Under the engine mysql Self configuration optimization

    1. Brief introduction InnoDB to MySQL Provided with commit , Transaction security of rollback and crash recovery capability (ACID compatible ) Storage engine .InnoDB Locked at line level and also at SELECT Statement provides a Oracle Style consistent non lock reading . These characteristics ...

  3. MySQL database InnoDB Server power failure data recovery under the engine

    explain : One on the line MySQL The database server suddenly lost power , Cause system failure, unable to start , After reinstalling the system , Find the previous MySQL Database folder . problem : By copying files to the previous MySQL Database recovery , It is found that when the program is called ...

  4. Mysql InnoDB Under the engine The isolation level of the transaction

    mysql InnoDB Learning things under the engine Build table user CREATE TABLE `user` ( `uid` bigint(20) unsigned NOT NULL AUTO_INCREMENT, ...

  5. MySql Lock and InnoDB engine

    MySql Lock and InnoDB engine mysql The lock is a frequent question in the interview , It's also something we often encounter in our daily development, but we don't notice . I share my understanding of the lock through this blog , Because locks need to be understood in conjunction with transactions , This article only introduces ...

  6. MySQL Database Series ( 3、 ... and )- MySQL Common engines MyISAM and InnoDB Detailed explanation

    summary InnoDB: stay MySQL 5.5 And later ,InnoDB yes MySQL The default transactional engine , It's also the most important and widely used storage engine . It's designed to be a lot of short-term business , Short term transactions are normally committed in most cases , Rarely returned ...

  7. MySQL innodb Under the engine, according to .frm and .ibd File recovery table structure and data

    The record passed .frm and .ibd File recovery data to local .frm file : The metadata of each table is saved , Including the definition of table structure, etc : .ibd file :InnoDB The engine turns on the independent table space (my.ini Middle configuration innodb_file_per ...

  8. Mysql stay InnoDB The case of index invalidation row level lock changing to table lock under the engine

    Get ready first , establish InnoDB Engine data table , And added the corresponding index DROP TABLE IF EXISTS `innodb_lock`; CREATE TABLE `innodb_lock` ( `a` ) ...

  9. MySQL Document and Innodb Engine files

    constitute MySQL Database and InnoDB Various types of files that store engine tables , The group should have the following files Parameter file : tell MySQL Where can I find... When the instance starts , Database files , And specify some initialization parameters , These parameters define the size of some memory structure and other settings ...

Random recommendation

  1. Divide and conquer:Garland(POJ 1759)

      Hang up the lights The main idea of the topic : It's just that when you're going to set up a stadium, you need to hang up colored lights , The height of the lantern can meet : H1 = A Hi = (Hi-1 + Hi+1)/2 - 1, for all 1 < i < N HN = B Hi ...

  2. spirng Configuration and use of thread pool

    1. stay xml Configure thread pool in <!-- Configure thread pool --> <bean id="taskExecutor" class="org.springframewo ...

  3. Data warehouse automation ETL Research

    however , In the process of implementing data integration , Because the data provided by different users may come from different channels , Its data content . Data format and data quality are very different , Sometimes we even encounter some thorny problems, such as the data format can not be converted or the information lost after the data format conversion , Seriously hindered the data in all sectors ...

  4. 【Java Basics 】 Explore the execution order of code blocks and construction methods in inheritance

    This article talks about static code blocks for a class , Building blocks of code , The execution flow of construction method . Let's start with an example /** * Created by lili on 15/10/19. */ class Person{ stati ...

  5. Nginx+PostgreSQL+Django+UWSGI build

    Recently, due to the needs of the project, it began to use a lot nginx, So I also want to take advantage of the opportunity to django+apache Change the architecture of django+nginx. common django webapp The deployment mode is FCGI or WSGI ...

  6. use C++ Do simple documentation I/O operation - from VC The knowledge base

    Please refer to  http://www.vckbase.com/index.php/wv/1158 introduction I have published articles on file input and output , Now I think it's necessary to write a little more . file I/O stay C++ It's much easier than baking a cake . Here ...

  7. Fiddler Capture, grab App End packet

    Recent project design to App Grab the bag , So using Fiddler Tools to capture APP Data packets , however fiddler Yes, some app It's impossible to capture packets , Here's how I deal with it : 1. My default proxy port uses a custom port instead of the default ...

  8. xxl-job Installation tutorial

    xxl-job Is an open source distributed scheduling framework , Other similar frameworks include airflow,oozie wait , There needs to be a comparison https://github.com/xuxueli/xxl-job 1. First git clon ...

  9. MSys2 install QT5

    1. MSYS2 shell # pacman –Syuu 2. Reopen MSYS2 # pacman –Syuu 3. Add domestic sources msys64\etc\pacman.d There are three files in the directory 1. ...

  10. SimpleDateFormat Conversion time ,12,24 Time format [ turn ]

    SimpleDateFormat Conversion time ,12,24 Time format come from :http://blog.csdn.net/dongguang1082/article/details/4387165 In the use of Simp ...