MySQL profile ES1en. cnf parameter optimization and Details in Chinese

Mysql parameter optimization for beginners, is a difficult thing, actually this parameter optimization, is a very complicated thing, for different web sites, and online content, traffic, the number of posts, network, hardware configuration and the machine have relations, optimizing impossible once completed, requires constant observation and debugging, is likely to get the best results.

[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql
pid-file = /data/mysql/mysql.pid
user = mysql
bind-address = 0.0.0.0
server-id = 1 # Represents the serial number of the machine is 1,1 In general master The meaning of 
skip-name-resolve
#  ban MySQL For external connections DNS Parse, use this 1 Options can be eliminated MySQL for DNS Parsing time. But be aware that if you turn this option on, 
#  All remote host connection authorization is used IP Address mode, otherwise MySQL The connection request will not be processed properly 
#skip-networking
back_log = 600
# MySQL The number of connections available. When the main MySQL The thread in 1 A very large number of connection requests in a very short period of time, and that works, 
#  Then the main thread takes some time ( Although is very short ) Check the connection and start 1 I have a new thread. back_log Value is pointed out that in MySQL How many requests can be stored on the stack in the short time before you temporarily stop answering new requests. 
#  If the expectation is 1 There are many connections in a short time and you need to add them. In other words, if MySQL Of the connection data reached max_connections , the incoming request will be stored in the stack, 
#  To wait for a 1 The connection frees the resource, and the number of stacks is back_log , if the number of pending connections exceeds back_log , the connection resource will not be granted. 
#  In addition, this value ( back_log ) is limited to the arrival of your operating system pairs TCP/IP The size of the listener queue for the connection. 
#  Your operating system has its own limits on the size of this queue (check your own OS The document finds the maximum value of this variable back_log Any higher than your operating system limit will be invalid. 
max_connections = 1000
# MySQL If the number of concurrent connection requests from the server is large, it is recommended to increase this value to increase the number of parallel connections. Of course, this is based on the condition that the machine can support, because if the number of connections is larger, it is between MySQL The connection buffer is provided for each connection, and the more memory overhead is incurred, so adjust this value appropriately and do not blindly increase the set value. They can live in 'conn%' The wildcard looks at the number of connections in the current state to determine the size of the value. 
max_connect_errors = 6000
#  For the same 1 Host, if there is an interrupt error connection beyond the number of values of this parameter, the host will be disconnected. If you need to unban the host, execute: FLUSH HOST . 
open_files_limit = 65535
# MySQL Open file descriptor limit, minimum by default 1024; when open_files_limit When not configured, compare max_connections*5 and ulimit -n Student: the value of theta, which is the big one, 
#  when open_file_limit When configured, compare open_files_limit and max_connections*5 The value of theta, which is the big one. 
table_open_cache = 128
# MySQL Every open 1 I'm going to read in every table 1 Some data to table_open_cache Cache, when MySQL When no information is found in the cache, it is read on disk. The default value 64
#  Suppose that the system has 200 Set this parameter to 200*N(N Number of file descriptors required for each connection ) ; 
#  When the table_open_cache Set to large, if the system cannot handle that many file descriptors, the client will fail and fail to connect 
max_allowed_packet = 4M
#  Packet size received; Increases the value of the variable 10 Split security, because extra memory is allocated only when needed. For example, only if you issue a long query or MySQLd Must return a large result row MySQLd To allocate more memory. 
#  The smaller default value for this variable is 1 A precaution to catch error packets between the client and server and to ensure that memory is not overflowed by accidentally using large packets. 
binlog_cache_size = 1M
# 1 A log of a transaction that was not committed Cache ; When the transaction commit needs to commit, the log is persisted to disk. The default binlog_cache_size The size of the 32K
max_heap_table_size = 8M
#  Defines the memory tables that users can create (memory table) The size of the. This value is used to calculate the maximum row value of the memory table. This variable supports dynamic change 
tmp_table_size = 16M
# MySQL the heap Table buffer size. All associations are in 1 a DML Within instructions, and most unions can be done without even a temporary table. 
#  Most temporary tables are memory based (HEAP) Table. A temporary table with a large record length  ( Sum of the lengths of all the columns ) Or contain BLOB The table of columns is stored on the hard disk. 
#  If one of the interior heap (Stacking) table size over tmp_table_size . MySQL Can be automatically placed in memory as needed heap The table is changed to be hardware-based MyISAM Table. You can also go through Settings tmp_table_size Option to increase the size of the temporary table. In other words, if I turn it up, MySQL At the same time it will increase heap Table size to increase the speed of join queries 
read_buffer_size = 2M
# MySQL Read in the buffer size. Requests for sequential scans of tables are allocated 1 Read in buffer, MySQL It's going to be allocated 1 Segment memory buffer. read_buffer_size Variable control here 1 The size of the buffer. 
#  If the sequential scan requests to the table are very frequent, and you think the frequent scans are going too slowly, you can improve performance by increasing the variable value and the memory buffer size 
read_rnd_buffer_size = 8M
# MySQL The random read buffer size of. When rows are read in any order ( For example, by sort order ) To assign 1 A random read cache. When you do a sort query, 
# MySQL It scans first 1 This buffer is traversed to avoid disk searches, improve query speed, and can be appropriately adjusted if a large amount of data needs to be sorted. but MySQL This buffer space is issued for each client connection, so try to set it appropriately to avoid excessive memory overhead 
sort_buffer_size = 8M
# MySQL The buffer size used to perform the sort. If I want to increase ORDER BY First of all, let's see if we can let MySQL Use the index instead of the extra sort phase. 
#  If not, try increasing it sort_buffer_size Size of variable 
join_buffer_size = 8M
#  The buffer size that can be used by the federated query operation, and sort_buffer_size1 The allocated memory corresponding to this parameter is also reserved per connection 
thread_cache_size = 8
#  This value (default 8 ) represents the number of threads that can be reused in the cache. If there is room in the cache when the connection is disconnected, the client thread will be put into the cache. 
#  If the thread is requested again, the request is read from the cache , If the cache is empty or a new request is made, the thread is recreated , If you have a lot of new threads, 
#  Increasing this value can improve system performance . By comparing the Connections and Threads_created The state variable, and you can see what that variable does. ( � > Represents the value to adjust )
#  Set the rules according to physical memory as follows: 
# 1G   - > 8
# 2G   - > 16
# 3G   - > 32
#  Is greater than 3G   - > 64
query_cache_size = 8M
#MySQL The query buffer size (from 4.0.1 To start, MySQL Provides a query buffer mechanism.) Using query buffer, MySQL will SELECT Statements and query results are stored in the buffer, 
#  The same thing in the future SELECT Statement, which will read the results directly from the buffer. According to the MySQL User manual, using the query buffer can be reached at most 238% The efficiency. 
#  By checking the status value 'Qcache_%' You can see that query_cache_size Reasonable Settings: If Qcache_lowmem_prunes Is very large, indicating that insufficient buffering often occurs, 
#  if Qcache_hits Is also very large, indicating that the query buffer is used very frequently and needs to be increased; if Qcache_hits A small value indicates that your query repetition rate is low, 
#  In this case, using query buffering can actually affect efficiency, so consider not using query buffering. In addition, in SELECT Add to statement SQL_NO_CACHE You can explicitly state that query buffering is not used 
query_cache_limit = 2M
# Specifies the buffer size that a single query can use, by default 1M
key_buffer_size = 4M
# Specifies the size of the buffer used for the index, increasing it to better handle the index ( For all reads and multiple overwrites ) As much as you can afford. If you make it too big, 
#  The system will start changing pages and really slow down. For the existence 4GB Left and right server this parameter can be set to 384M or 512M . By checking the status value Key_read_requests and Key_reads . 
#  You can know key_buffer_size Is the setup reasonable? The proportion key_reads/key_read_requests It should be as low as possible, 
#  At least, 1:100 . 1:1000 better ( The above status values can be used SHOW STATUS LIKE 'key_read%' To obtain ) . Note: setting this parameter too high will reduce the overall efficiency of the server 
ft_min_word_len = 4
#  Participle vocabulary minimum length, default 4
transaction_isolation = REPEATABLE-READ
# MySQL support 4 The transaction isolation levels are: 
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.
#  If not specified, MySQL The default is REPEATABLE-READ . ORACLE The default is READ-COMMITTED
log_bin = mysql-bin
binlog_format = mixed
expire_logs_days = 30 # More than 30 Days of binlog delete 
log_error = /data/mysql/mysql-error.log # Error log path 
slow_query_log = 1
long_query_time = 1 # Slow query time   More than 1 Seconds is a slow query 
slow_query_log_file = /data/mysql/mysql-slow.log
performance_schema = 0
explicit_defaults_for_timestamp
#lower_case_table_names = 1 # Case insensitive 
skip-external-locking #MySQL Option to avoid external locking. This option is enabled by default 
default-storage-engine = InnoDB # Default storage engine 
innodb_file_per_table = 1
# InnoDB For the standalone tablespace schema, each table in each database is generated 1 Data space 
#  Advantages of independent table space: 
# 1 . Each table has its own separate table space. 
# 2 . Each table's data and indexes are stored in its own table space. 
# 3 . You can move a single table between different databases. 
# 4 . Space can be recycled (except drop table Operation, table empty cannot be recovered by itself) 
#  Disadvantages: 
#  Too large an increase in a table, as in excess 100G
#  Conclusion: 
#  The Shared table space is at Insert There are few advantages in operation. Nothing else performs as well as a standalone tablespace. When independent tablespaces are enabled, adjust accordingly: innodb_open_files
innodb_open_files = 500
#  limit Innodb If there are too many tables in the library, please add this. The default value is 300
innodb_buffer_pool_size = 64M
# InnoDB use 1 A buffer pool to hold the index and raw data ,  Don't like MyISAM.
#  The bigger you set it up here , The disk you need to access the data in the table I/O The less .
#  in 1 On a separate database server , You can set this variable to the server physical memory size 80%
#  Don't make it too big , Otherwise, , Due to the physical memory competition may cause the operating system page change bump .
#  Note that in 32 On a bit system each of your processes may be limited to  2-3.5G  User-level memory limits ,
#  So don't set it too high .
innodb_write_io_threads = 4
innodb_read_io_threads = 4
# innodb Use background threads to process reads and writes on data pages  I/O( Input and output ) request , According to your  CPU  Check to change , The default is 4
#  note : These two parameters do not support dynamic change , You need to add this parameter to the my.cnf After the modification, restart MySQL service , The allowed values range from  1-64
innodb_thread_concurrency = 0
#  The default setting is  0, Does not limit the number of concurrences 0 To better play CPU Multi - core processing capacity, improve concurrency 
innodb_purge_threads = 1
# InnoDB The cleanup operation is 1 Class that periodically collects unwanted data. In previous versions, the cleanup operation was the main thread 1 In part, this means that at run time it may clog up other database operations. 
#  from MySQL5.5.X Version starts, and the operation runs in a separate thread , And support more concurrency. The user can pass the setting innodb_purge_threads Configure parameters to choose whether the cleanup operation will use a single 
#  Alone the thread , By default, the parameter is set to 0( No separate threads are used ), Set to  1  "Indicates the use of a separate clear thread. Suggestions for 1
innodb_flush_log_at_trx_commit = 2
# 0 If: innodb_flush_log_at_trx_commit The value of 0,log buffer Log files are swiped to disk every second, and no action is taken when the transaction is committed mysql the master thread It's done by threads. 
#  The redo log buffer is written to disk every second in the main thread (REDO LOG) In the. Whether or not the transaction has been committed) the default log file is ib_logfile0,ib_logfile1
# 1 : When set to default 1 Every time a transaction is committed, it will be log buffer Swipe to the log. 
# 2 : If set to 2, Each commit transaction is logged, but no brush operation is performed. The log file will be swiped at regular intervals per second. Note that there is no guarantee 100% In a second 1 It all goes to disk, depending on the schedule of the process. 
#  Each time a transaction commits, the data is written to the transaction log, and the write is simply a call to the file system, which does   Cached, so this write does not guarantee that the data has been written to the physical disk 
#  The default value 1 In order to keep it intact ACID . Of course, you can set this configuration item to 1 Extra value for higher performance, but in the event of a system crash, you will lose it 1 Seconds of data. 
#  Set to 0 Words, mysqld When a process crashes, the end is lost 1 Transactions in seconds. Set to 2, It is only lost when the operating system crashes or loses power 1 Seconds of data. InnoDB This value is ignored when doing the recovery. 
#  conclusion 
#  Set to 1 Certainly the safest, but the performance page is the worst (relative to the other two parameters, but not unacceptable). If you look at the data 1 The requirements for completeness and completeness are not high, and can be set as 2 , if only for the best performance, such as a high-concurrency log server, set to 0 To get higher performance 
innodb_log_buffer_size = 2M
#  This parameter determines the amount of memory used for some log files M As the unit. Larger buffers can improve performance, but unexpected failures can lose data. MySQL The developers suggest setting to 1 - 8M between 
innodb_log_file_size = 32M
#  This parameter determines the size of the data log file, and larger Settings can improve performance but also increase the time required to recover the failed database 
innodb_log_files_in_group = 3
#  To improve performance, MySQL Log files can be written to multiple files in a circular fashion. Recommend setting to 3
innodb_max_dirty_pages_pct = 90
# innodb The main thread flushes the data in the cache pool to make the proportion of dirty data less than 90%
innodb_lock_wait_timeout = 120 
# InnoDB Transactions can wait before being rolled back 1 Number of lock timeout seconds. InnoDB Automatically detects transaction deadlocks in its own lock table and rolls back transactions. InnoDB with LOCK TABLES Statement notices the lock setting. The default value is 50 seconds 
bulk_insert_buffer_size = 8M
#  Batch insert cache size,   This parameter is for theta MyISAM The storage engine. Applicable to 1 Sexual insert 100-1000+ When the bar is recorded,   Increase efficiency. The default value is 8M . You can double it for the size of the data. 
myisam_sort_buffer_size = 8M
# MyISAM Sets the size of the buffer used when restoring the table, when in REPAIR TABLE Or use the CREATE INDEX Create an index or ALTER TABLE In-process sorting  MyISAM Index allocated buffer 
myisam_max_sort_file_size = 10G
#  If temporary files become larger than the index, do not use the quicksort index method to create them 1 An index. Note: This parameter is given in bytes 
myisam_repair_threads = 1
#  If the value is greater than 1 In the Repair by sorting Parallel creation in the process MyISAM Table index ( Each index is in its own thread )  
interactive_timeout = 28800
#  The number of seconds that the server waits for activity before shutting down the interactive connection. The interactive client is defined in mysql_real_connect() The use of CLIENT_INTERACTIVE Option client. Default value: 28800 Seconds ( 8 Hours) 
wait_timeout = 28800
#  The number of seconds that the server waits to be active before shutting down a non-interactive connection. At thread startup, according to the global wait_timeout Value or global interactive_timeout Value to initialize the session wait_timeout Value, 
#  Depends on the client type ( by mysql_real_connect() Connection options for CLIENT_INTERACTIVE define ) . Default values of parameters: 28800 Seconds ( 8 Hours) 
# MySQL There is an upper limit to the number of connections supported by the server because each connection is created consumes memory, so we want clients to connect to the MySQL Server After processing the corresponding operation, 
#  The connection should be disconnected and the occupied memory freed. If you have a MySQL Server There are a lot of idle connections, they will not only waste memory, but also if the connection 1 It keeps on adding up, 
#  Eventually it will MySQL Server The maximum number of connections that will be reported 'too many connections' The error. for wait_timeout Should be determined based on the operation of the system. 
#  Run in the system 1 After some time, you can pass show processlist Command to view the connection status of the current system if a large number is found sleep State of the connection process, indicates that this parameter is set too large, 
#  It can be adjusted to be smaller. I'm gonna set it at the same time interactive_timeout and wait_timeout It takes effect. 
[mysqldump]
quick
max_allowed_packet = 16M # The maximum packet length that the server sends and receives 
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M