My-innodb-heavy-4g.cnf1 detailed

Http://bbs.51cto.com/thread-1166608-1.html on the Internet to find an article

The following is a welcome discussion of the translation of self-study information

1, Back_log = 50

#指定MySQL可能的连接数量. When the MySQL main thread gets very many connection requests in a short period of time, the parameter works, and the main thread takes some time (albeit very short) to check the connection and start a new thread.
The value of the Back_log parameter indicates how many requests can be present in the stack for a short period of time before MySQL temporarily stops responding to a new request. If the system has many connections in a short period of time, you need to increase the value of this parameter, which specifies the size of the listening queue for incoming TCP/IP connections. Different operating systems have its own limitations on this queue size. Attempting to set a limit of back_log above your operating system will be invalid.
When observing the MySQL process list, find a lot of 264084 | Unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | Login | The Back_log value is increased when the NULL pending process is to be connected. The default value for Back_log is 50.

max_connections = 100
The parameter is used to set the maximum number of connections (users). Each user connected to MySQL counts as a connection, and the default value for Max_connections is 100

2, max_connect_errors = 10

#当此值设置为10时 means that if a client attempts to connect to this MySQL server but fails (such as a password error, and so on) 10 times, MySQL will unconditionally force this client connection to be blocked.
If you want to reset the value of this counter, you must restart the MySQL server or perform
Command.
When this client successfully connects to a MySQL server, the max_connect_errors for this client are zeroed out.
Impact and wrong form
If the setting of the max_connect_errors is too small, the Web page may prompt that the database server cannot be connected, and the MySQL command to SSH to the database will return
ERROR 1129 (00000): Host ' Gateway ' is blocked because of many connection errors; Unblock with ' mysqladmin flush-hosts '

Table_open_cache = 2048
Parameter sets the number of table caches. Each connection comes in with at least one table cache open. Therefore, the size of the Table_cache should be related to the Max_connections setting. For example, for 200 parallel-running connections, the table should have at least 200xN of cache, where N is the maximum number of tables in a join of a query that the application can execute. In addition, some additional file descriptors need to be reserved for temporary tables and files.
When Mysql accesses a table, if the table is already open in the cache, it can access the cache directly, and if it is not yet cached, but there is room in the MySQL table buffer, then the table is opened and placed in the table buffer, and if the table cache is full, the currently unused table is released according to certain rules. or temporarily expand the table cache to hold, the advantage of using table caching is that you can access the contents of the table more quickly.
Caching mechanism
When Mysql accesses a table, if the table is already open in the cache, it can access the cache directly, and if it is not yet cached, but there is room in the MySQL table buffer, then the table is opened and placed in the table buffer, and if the table cache is full, the currently unused table is released according to certain rules. or temporarily expand the table cache to hold, the advantage of using table caching is that you can access the contents of the table more quickly.
Perform

MySQL >flush tables;

#命令将会清空当前所有缓存的表.

3, Max_allowed_packet = 16M

#指代mysql服务器端和客户端在一次传送数据包的过程当中数据包的大小
This is the size of the packet that defines the MySQL server side and the client during a single transfer of the packet
Defined too large, such as max_allowed_packet=8092, it is possible that the server is too busy, too late to receive, or the network is too poor, it will easily cause loss of packets
Defined too small, because the client may not be able to quickly receive server-side packets sent over, the general recommendation is 4096

Http://bbs.chinaunix.net/thread-4178672-1-1.html

4, binlog_cache_size = 1M
#为每个session allocated memory, which is used to store the cache of binary logs during a transaction. Improve the efficiency of recording Bin-log

Max_heap_table_size = 64M
It specifies the maximum value of the internal memory temporary table, which is allocated for each thread. (The actual limiting effect is the minimum value of Tmp_table_size and Max_heap_table_size.) If the memory temp table exceeds the limit, MySQL automatically translates it into a disk-based MyISAM table, stored in the specified Tmpdir directory, by default:
Mysql> Show variables like "Tmpdir";

5, read_buffer_size = 2M

#是MySQL读入缓冲区大小. A request to sequentially scan a table allocates a read-in buffer, and MySQL allocates a memory buffer for it. The read_buffer_size variable controls the size of this buffer. If the sequential scan requests for a table are frequent, and you think that frequent scans are going too slowly, you can improve their performance by increasing the value of the variable and the size of the memory buffer.
Read_rnd_buffer_size = 16M
is the random read buffer size of MySQL. When rows are read in any order (for example, in sort order), a random read buffer is allocated. When you sort a query, MySQL scans the buffer first to avoid disk searches, improve query speed, and, if you need to sort large amounts of data, raise the value appropriately. However, MySQL will issue this buffer space for each client connection, so you should set this value as appropriately as possible to avoid excessive memory overhead.

6, sort_buffer_size = 8M

#是MySQL执行排序使用的缓冲大小. If you want to increase the speed of the order by, first see if you can let MySQL use the index instead of the extra sort stage. If not, you can try increasing the size of the sort_buffer_size variable.
Join_buffer_size = 8M
The application often has some operational requirements for two tables (or multiple tables) join, and when MySQL completes certain join requirements (All/index join), in order to reduce the number of reads of the "driven table" participating in the join to improve performance, it is necessary to use the join Buffer To assist in completing the join operation. When the join buffer is too small, MySQL does not save the buffer to the disk file, but rather first joins the result set in the join buffer with the table that needs the join, then empties the data in the join buffer and continues to write the remaining result set to this Buf In the Fer, so reciprocating. This is bound to cause the driver table to be read multiple times, multiply IO access, reduce efficiency
Thread_cache_size = 8
If we set the thread_cache_size in the MySQL server configuration file, when the client disconnects,
The server processing this customer's thread will be cached in response to the next customer instead of destroying it (provided the cache count is not up to the limit). thread_cache_size function in the MySQL database configuration file is a very important feature, if the Thread_cache_size optimization is done well we can let the server run very fast, the settings are not good will find a very small amount of access to the card Oh.
7, thread_concurrency = 8

#是一个编程术语, which means "thread concurrency", simply means that a program creates multiple threads and performs some tasks in parallel.

Query_cache_size = 64M
The query cache saves the full result returned by the query. When the query hits the cache, the results are returned immediately, skipping the parsing, optimization, and execution phases.
The query cache tracks each table involved in the query, and if the write table changes, all caches associated with the table will be invalidated.
But with the power of the server, query caching can also become a resource contention point for the entire server.
The cache is stored in a reference table, referenced by a hash value, which includes the query itself, the database, the version of the client protocol, and so on, any character, such as a space, which causes the cache to miss.
When there are some uncertain data in the query, it is not cached, such as now (), Current_date (), custom functions, stored functions, user variables, word queries, etc. So the query will not hit the cache, but also to detect the cache, because the query cache before parsing SQL, so MySQL does not know whether the query contains the class function, but does not cache, nature will not hit.

8, Query_cache_limit = 2M

#指定单个查询能够使用的缓冲区大小, default is 1M

Ft_min_word_len = 4
Full-text indexing has been supported since MySQL 4.0, but the default minimum index length for MySQL is 4. If the English default value is reasonable, but the majority of Chinese words are 2 characters, which results in less than 4 words can not be indexed, full-text indexing function is a fake
General database Search is a SQL like statement, like statements can not take advantage of the index, each query is from the first one to traverse to the last, query efficiency is extremely low. General data is more than 100,000 or too many people online, like queries can cause the database to crash. That's why many programs provide only a title search, because if you search for content, it's even slower, and tens of thousands of of the data is not moving.

Mysql Full-text indexing is specifically designed to solve fuzzy queries provided by the entire article in advance according to the word index, the search efficiency is high, can support millions data retrieval.

9, Default-storage-engine = MYISAM
#默认的MyISAM存储引擎

10, Thread_stack = 192K
#每个连接被创建的时候, MySQL allocates the memory to it. This value is generally considered to be applied to most scenarios by default, unless it is necessary to not move it.
Transaction_isolation = Repeatable-read

11, tmp_table_size = 64M
#它规定了内部内存临时表的最大值, each thread is assigned. (The actual limiting effect is the minimum value of Tmp_table_size and Max_heap_table_size.) If the memory temp table exceeds the limit, MySQL automatically translates it into a disk-based MyISAM table, stored in the specified Tmpdir directory

Log-bin=mysql-bin
MYSQL-BIN.000001, mysql-bin.000002 files, such as the database operation log, such as update a table, or delete some data, even if the statement does not have matching data, the command will be stored in the log file, also includes the time of execution of each statement, also recorded in.

Binlog_format=mixed
There are three main ways of MySQL replication: SQL statement-based replication (statement-based replication, SBR), row-based replication (row-based replication, RBR), mixed-mode replication (mixed-based Replication, MBR). The corresponding, Binlog format also has three kinds: statement,row,mixed.
①statement Mode (SBR)

Each SQL statement that modifies the data is recorded in the Binlog. The advantage is that you do not need to record every SQL statement and each row of data changes, reduce the Binlog log volume, save IO, improve performance. The disadvantage is that in some cases the data in Master-slave is inconsistent (such as the Sleep () function, last_insert_id (), and user-defined Functions (UDF), and so on)

②row Mode (RBR)

Do not log the context information for each SQL statement, just record which data has been modified and what is changed. There are no stored procedures, or function, or trigger calls and triggering problems that cannot be replicated correctly in certain situations. The disadvantage is that a large number of logs are generated, especially when ALTER TABLE causes the log to skyrocket.

③mixed Mode (MBR)

Mixed use of the above two modes, general replication using statement mode to save the Binlog, for statement mode cannot copy operation using row mode save Binlog,mysql will choose the way to save the log according to the SQL statement executed
Mixed description

For a time function that contains now () in the executed SQL statement, the corresponding unix_timestamp () *1000 time string is generated in the log, slave when the synchronization is completed, the time taken for the sqlevent to be used to ensure the accuracy of the data. In addition, for some functional functions slave can complete the corresponding data synchronization, and for some of the above specified similar to the UDF function, resulting in slave can not be known, the row format will be stored in these binlog, to ensure that the resulting binlog can be slave to complete the data synchronization.
Http://www.111cn.net/database/mysql/71702.htm
12, Slow_query_log

#顾名思义, the slow query log is recorded in a long execution of the query, which is what we often call slow query, by setting--log-slow-queries[=file_name] to open the function and set the record location and file name, The default file name is Hostname-slow.log, and the default directory is the data directory.
The slow query log is a simple text format that allows you to view the content in a variety of text editors. It records the moment of execution, the time spent executing, executing the user, connecting the host and other related information. MySQL also provides a tool program mysqlslowdump for analyzing full query logs to help database managers resolve possible performance issues.
Http://www.cnblogs.com/Richardzhu/p/3230221.html

13, Long_query_time = 2

#MySQL慢查询支持毫秒的设置MySQL慢查询本身不支持ms级别 (need to be patched), but for mysql5.21+ version, the Long_query_time minimum is 0 (5.2.1 before version minimum is 1s), the unit is S, if you specify MS, The MS part is ignored; in fact, this is already in disguise to support the millisecond level, such as query time greater than 100ms will be remembered
Http://www.dedecms.com/knowledge/data-base/mysql/2012/0819/7319.html

14, Server-id = 1
The data in #1, MySQL synchronization contains Server-id, which identifies which server the statement was originally written from, so Server-id must have

#2, each synchronization slave a master thread on master, which is identified by the slave Server-id, and each slave has at most one master thread on the master side, If the two slave have the same server-id, the last one will be kicked off when the connection succeeds. There's at least one consideration here.
Slave active Connection Master, if the slave above performs a slave stop, the connection is broken, but the corresponding thread on master does not exit, and when slave start, master can no longer create a thread and retain the original thread. There may be problems with that synchronization;

#3, in MySQL master synchronization, a number of primary needs to form a ring, but when synchronizing to ensure that a data does not fall into the dead loop, here is the Server-id to achieve
14, key_buffer_size = 32M

#key_buffer_size指定索引缓冲区的大小, it 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 if the key_buffer_size settings are reasonable. The proportional key_reads/key_read_requests should be as low as possible, at least 1:100,1:1000 better (the above status values can be obtained using the show status like ' key_read% ').

Key_buffer_size only works on MyISAM tables. Even if you do not use the MyISAM table, the internal temporary disk table is the MyISAM table and this value is used. You can use the Check status value created_tmp_disk_tables to learn more.
Http://blog.chinaunix.net/uid-24145780-id-125159.html

15, bulk_insert_buffer_size = 64M

#和key_buffer_size一样, this parameter is also used only for the use of the MyISAM storage engine to cache the temporary cache write data when bulk data is inserted. This memory area is used to cache bulk structure data to help bulk write data files when we write to statements using several of the following data:
Http://www.jb51.net/article/48422.htm

16, myisam_sort_buffer_size = 128M
The sort index is cached when #当对MyISAM表执行repair table or when the index is created; Myisam_sort_buffer_size is too small may be encountered when setting too much hours

17, Myisam_max_sort_file_size = 10G

#当对MyISAM表重建索引时 (repair/alter table/load data infile), the maximum number of temporary files allowed to be used, and if the index creation is more than this limit, use the key cache, at which time show Processlist will show that the thread is in "repair with Keycache" instead of "repair by sorting", which creates an index record by article, and a similar situation when the specified Tmpdir directory space is not sufficient;

18, Myisam_repair_threads = 1

# If a table has more than one index, MyISAM can use more than one thread to fix them by using parallel sorting.
# This is a good choice for users with multiple CPUs and a lot of memory.
Myisam_recover
#自动检查和修复没有适当关闭的 MyISAM Table
19, innodb_additional_mem_pool_size = 16M

#这个参数用来设置 the memory pool size of the data directory information and other internal data structures stored by the InnoDB, similar to Oracle's library cache. This is not a mandatory parameter and can be breached.
Innodb_buffer_pool_size = 2G
When we use the InnoDB storage engine, the Innodb_buffer_pool_size parameter is probably the most critical parameter that affects our performance, which is used to set the size of the memory area used to cache InnoDB indexes and blocks, similar to the MyISAM storage engine's The Key_buffer_size parameter, of course, may be more like Oracle's db_cache_size. Simply put, when we manipulate a InnoDB table, all the data returned or any index block used in the data process will go through this memory area.

    and Key_buffer_size for the MyISAM engine, Innodb_buffer_pool_size sets the size of the largest chunk of memory required by the InnoDB storage engine, directly related to InnoDB the performance of the storage engine, so if we have enough memory, we can set this parameter to enough to hit, and put as many as possible InnoDB indexes and data into the cache area, until all.

    We can pass (innodb_buffer_pool_read_requests-innodb_buffer_pool_reads)/Innodb_buffer_pool_read _requests * 100% calculates the cache hit ratio and adjusts the innodb_buffer_pool_size parameter size based on the hit ratio to optimize.
Innodb_data_file_path = ibdata1:10m:autoextend
#表空间文件 Important Data
20, Innodb_write_io_threads = 8
#其它对IO有影响的参数 (whichever is 5.6)
innodb_adaptive_flushing default
Innodb_change_buffer_max_size If it is a Japanese value class service, consider adding this value to the
Innodb_ change_buffering default is
Innodb_flush_neighors is open, this must be open, make full use of sequential IO to write data.
Innodb_lru_scan_depth: This parameter can be more professional by default.
Innodb_max_purge_lag is not enabled by default, and if both writes and reads are large, you can ensure that read takes precedence and you can consider using this feature.
Innodb_random_read_ahead is not turned on by default, belongs to a more active parameter, if you want to use it must be tested a bit more. For Passport class applications you can consider using the
Innodb_read_ahead_threshold default on: 56 read-ahead mechanism can be processed according to the business, if it is Passprot can consider shutting down. If you use Innodb_random_read_ahead, it is recommended to turn this feature off
Innodb_read_io_threads defaults to: 4 You can consider 8
innodb_write_io_threads default is: 4 You can consider 8
Sync_binlog Default: 0
innodb_rollback_segments default: +

Another 5.6 of the undo log can be configured independently, and it is recommended to configure it separately.
Http://www.mysqlsupport.cn/innodb-io-optimize-conf

21, Innodb_read_io_threads = 8
#文件IO的线程数, typically 4, but under Windows, you can set it larger.
22, innodb_thread_concurrency = 16
#服务器有几个CPU就设置为几, the default setting, typically 8, is recommended.
23, Innodb_flush_log_at_trx_commit = 1
# If this parameter is set to 1, the log will be written to disk after each commit transaction. To provide performance, you can set to 0 or 2, but assume the risk of losing data in the event of a failure. A setting of 0 indicates that the transaction log is written to the log file, and the log file is flushed to disk once per second. A setting of 2 indicates that the transaction log will be written to the log at commit time, but the log file is flushed to disk one at a time.
24, innodb_log_buffer_size = 8M
#这是 the buffer used by the transaction log of the InnoDB storage engine. Similar to Binlog Buffer,innodb when writing transaction logs, in order to improve performance, the information is first written to the INNOFB log Buffer, when it satisfies the Innodb_flush_log_trx_commit The log is written to a file (or to a disk) after the corresponding condition set by the parameter (or the log buffer is full). You can set the maximum memory space that can be used by the Innodb_log_buffer_size parameter.
Note: The Innodb_flush_log_trx_commit parameter has a very critical impact on the write performance of InnoDB log. This parameter can be set to 0,1,2, as explained below:

The data in the 0:log buffer is written to log file at a frequency of once per second, and the file system-to-disk synchronization is performed, but the commit of each transaction does not trigger any flush of log buffer to log file or file system-to-disk refresh operations;
1: The data in log buffer will be written to log file each time the transaction commits, and the file system to disk synchronization will also be triggered;
2: Transaction commit triggers a flush of log buffer to log file, but does not trigger a disk file system-to-disk synchronization. In addition, there is a file system-to-disk synchronization operation every second.

In addition, the MySQL documentation mentions that the mechanism of synchronizing each second in these settings may not completely ensure that a very accurate synchronization occurs every second and depends on the process scheduling problem. In fact, whether InnoDB can really meet the value set by this parameter means normal Recovery is still under the limitations of the file system and the disk itself in the different OS, there may be times when the disk synchronization is not actually complete and will tell Mysqld that the disk synchronization has been completed.
25, Innodb_log_file_size = 256M
#此参数确定数据日志文件的大小, in M, larger settings can improve performance, but also increase the time it takes to recover a failed database
26, Innodb_log_files_in_group = 3

#为提高性能, MySQL can write log files to multiple files in a circular fashion. Recommended set to 3M
27, innodb_max_dirty_pages_pct = 90

# The amount of dirty_page in Buffer_pool directly affects the closing time of the InnoDB. The parameter innodb_max_dirty_pages_pct can directly control the ratio of dirty_page in Buffer_pool, and fortunately innodb_max_dirty_pages_pct can be changed dynamically. Therefore, the innodb_max_dirty_pages_pct is reduced before closing the InnoDB, forcing the data block to flush for a period of time, which can greatly shorten the time of MySQL shutdown.
Http://www.taobaodba.com/html/221_innodb_max_dirty_pages_pct_checkpoint.html

28, Innodb_lock_wait_timeout = 120
# InnoDB has its built-in deadlock detection mechanism that can cause incomplete transactions to be rolled back. However, if you combine InnoDB with a MYISAM lock tables statement or a third-party transaction engine, the INNODB does not recognize the deadlock. To eliminate this possibility, you can set Innodb_lock_wait_timeout to an integer value that indicates how long (in seconds) the MySQL waits before allowing other transactions to modify data that is eventually rolled back by the transaction.
[Mysqldump]
Quick
= 16M
Refers to the size of the packet in the case of MySQL server and client during a packet transfer
This is the size of the packet that defines the MySQL server side and the client during a single transfer of the packet
Defined too large, such as max_allowed_packet=8092, it is possible that the server is too busy, too late to receive, or the network is too poor, it will easily cause loss of packets
Defined too small, because the client may not be able to quickly receive server-side packets sent over, the general recommendation is 4096
[MySQL]
No-auto-rehash
Is the automatic completion of the meaning, as we enter the command line in the Linux command, the use of the TAB key function is the same

[Myisamchk]
29, Key_buffer_size = 512M

#如果key_buffer_size设置太大, the system will change pages frequently, reducing system performance. Because MySQL caches data using the operating system's cache, we have to leave enough memory for the system, and in many cases the data is much larger than the index.
Http://www.cnblogs.com/sunss/archive/2011/03/11/1981373.html

30, sort_buffer_size = 512M
#1 Sort_buffer_size is a connection-level parameter that allocates the set of memory once each connection needs to use this Buffer for the first time.
#2 Sort_buffer_size is not as big as possible, because it is a connection-level parameter, too large a setting + high concurrency may deplete system memory resources.
#3 document says "on Linux, there is thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation"
Http://bbs.chinaunix.net/thread-1805254-1-1.html

31, Read_buffer = 8M
#主要用于表顺序扫描的缓存, the role of this cache is not to query the same table multiple times without going to the disk to fetch data, read directly from the cache (here is a question, if the data has changed the cache will be invalidated?). ), or just as a cache for a full table scan, the cache space is released directly after the query, and is not used for the next query.
Http://bbs.csdn.net/topics/390415865?page=1

32, Write_buffer = 8M
#cache和write Buffer is a small piece of high-speed memory built into the CPU, which holds memory data that has been used by the CPU in the last time, while write buffer is used to handle memory writes. The data that was originally written to memory is temporarily written to the write buffer, and the data is slowly moved into memory until the CPU is idle.
Http://blog.chinaunix.net/uid-20662820-id-3917558.html

[Mysqlhotcopy]
Interactive-timeout
Interactive_timeout is the number of seconds that MySQL waits before waiting for an active connection to close the connection
http://blog.itpub.net/26855487/viewspace-751504

[Mysqld_safe]
Open-files-limit = 8192
The MySQL variables open_files_limit,table_open_cache and max_connections are interrelated. If some variables are set and some variables are not set, MySQL calculates the rest according to a certain calculation formula, which of course sometimes triggers some MySQL warnings. The specific calculation process and how to derive the final file descriptor is somewhat complex
Http://www.sudops.com/mysql-open-file-limit-max_connections-table-open-cache.html

This article is from the "Ys" blog, be sure to keep this source http://yangson.blog.51cto.com/10828336/1726577

My-innodb-heavy-4g.cnf1 detailed