Introduction to MySQL configuration optimization in Linux system

Learn MySQL for some time, in MySQL configuration optimization to do a summary, MySQL optimization to take into account the manifold, multi-factor. is closely related to the server's hardware and the currently running environment. Here's a look at MySQL configuration optimizations:

[client]port = 3306socket = /tmp/mysql.sock   [mysqld]port =  3306socket = /tmp/mysql.sock  basedir = /usr/local/mysqldatadir = / data/mysqlpid-file = /data/mysql/mysql.piduser = mysqlbind-address =  0.0.0.0server-id = 1  #表示是本机的序号为1, generally speaking is the meaning of master   skip-name-resolve#  Disable MySQL for DNS resolution of external connections, and Use this option to eliminate the time for DNS resolution for MySQL. However, it is important to note that if this option is turned on,#  all remote host connection authorizations will use IP address, otherwise MySQL will not be able to handle connection requests    #skip-networking  back_log The number of connections that  = 600# mysql can have. When the primary MySQL thread gets very many connection requests in a very short period of time, this works,#  then the main thread takes some time (albeit very short) to check the connection and start a new thread. The Back_log value indicates how many requests can be present in the stack for a short period of time before MySQL temporarily stops answering a new request. #  If you expect to have a lot of connections in a short period of time, you need to add it. That is, if the MySQL connection data reaches max_connections, the new request will be present in the stack,#  to wait for a connection to release the resource, the number of that stack is back_log, if the number of waiting connections exceeds back_log, The connection resource will not be granted. #  Additionally, this value (Back_log) is limited to the size of your operating system's listening queue for incoming TCP/IP connections. #  your operating system has its own limit on this queue size (you can check your OS documentation to find out the maximum value of this variable), trying to set back_log higher than your operating system limit will be invalid. &nbSp; max_connections = 1000# mysql the maximum number of connections, if the server's concurrent connection request volume is large, it is recommended to increase this value to enhance the number of parallel connections, of course, this is based on the machine can support the case, Because if the number of connections, in MySQL will provide a connection buffer for each connection, it will cost more memory, so to adjust the value appropriately, you can not blindly increase the value set. The ' conn% ' wildcard character can be used to view the number of connections in the current state to decide the size of the value.   max_connect_errors = 6000#  for the same host, if there is an interrupt error connection that exceeds the number of values for this parameter, the host will be blocked from connecting. If you need to unblock the host, execute: flush host.   open_files_limit = 65535# mysql Open File descriptor limit, default minimum 1024, when Open_files_limit is not configured, compare Max_ The values of connections*5 and ulimit -n, which are large with which,#  when Open_file_limit is configured, compare open_files_limit and max_connections*5 values, Which is big with which.   table_open_cache = 128# mysql every time a table is opened, some data is read into the Table_open_cache cache, When MySQL cannot find the appropriate information in this cache, it will not be read on the disk. The default value 64#  assumes that the system has 200 concurrent connections, you need to set this parameter to 200*n (N is the number of file descriptors required for each connection);#  when the Table_open_cache is set to large, if the system does not handle so many file descriptors, Then the client fails, the packet size is not connected   max_allowed_packet = 4M#  accepted, and the value of the variable is increased very safely, because additional memory is allocated only when needed. For example, MYSQLD will allocate more memory only if you issue a long query or if mysqld must return a large result row. #  This variable takes a smaller default value is a precaution to capture the error message packets between the client and the server, and to ensure that memory overflows are not caused by accidental use of large packets.   binlog_cache_size = 1m#  a transaction that, when not committed, logs the resulting log to the cache, and persists the log to disk when the transaction submission needs to be committed. The default binlog_cache_size size 32k  max_heap_table_size = 8m#  defines the memory tables that users can create (memory table) The size. This value is used to calculate the maximum row value for the memory table. This variable supports dynamically changing the heap (stack) table buffer size of the   tmp_table_size = 16m# mysql. All unions are completed within a DML instruction, and most unions can be done without a temporary table. #  Most temporary tables are memory-based (HEAP) tables. Temporary tables with large record lengths   (the length of all columns) or tables containing BLOB columns are stored on the hard disk. #  If an internal heap (stacked) table is larger than Tmp_table_size,mysql, you can automatically change the in-memory heap table to a hard disk-based MyISAM table as needed. You can also increase the size of the staging table by setting the Tmp_table_size option. In other words, if the value is raised, MySQL will increase the size of the heap, which can increase the speed of the join query   read_buffer_size = 2m# mysql read the buffer size. 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 = 8m# The random read buffer size of the  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   sorT_buffer_size = 8m# mysql the buffer size used to perform the sort. If you want to increase the speed of 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 buffer size that can be used by the union query operation. As with Sort_buffer_size, the allocated memory for this parameter is also the exclusive   thread_cache_size = 8#  per connection This value (default 8) means that the number of threads stored in the cache can be re-used, and if there is room in the cache when disconnected, the client's thread will be placed in the cache,#  if the thread is requested again, then the request will be read from the cache, if the cache is empty or a new request, Then this thread will be recreated, and if there are many new threads,#  increase this value to improve system performance. You can see the effect of this variable by comparing the variables of the connections and threads_created states. (–> represents the value to adjust) #  according to the physical memory setting rules are as follows: # 1g  -> 8# 2g  -> 16# 3g   -> 32#  Greater than 3g  -> 64  query_cache_size = 8m# MySQL's query buffer size (starting with 4.0.1, MySQL provides a query buffering mechanism) uses query buffering, and MySQL stores the SELECT statement and query results in a buffer,#  future for the same SELECT statement (case sensitive), The result is read directly from the buffer. Depending on the MySQL user manual, the use of query buffering can be up to 238% efficient. #  by checking the status value ' qcache_% ', you can know whether the query_cache_size setting is reasonable: If the value of Qcache_lowmem_prunes is very large, it indicates that there is often insufficient buffering,#  if Qcache The value of the _hits is also very large, indicating that the query buffer is used very frequently, which requires an increase in buffer sizeSmall; If the value of qcache_hits is not large, it indicates that your query repetition rate is very low,#  in this case the use of query buffering will affect efficiency, then you can consider not to query buffering. In addition, adding sql_no_cache in the SELECT statement can make it clear that you do not use a query buffer   query_cache_limit = 2m# Specify the buffer size that a single query can use, and the default 1M   key_buffer_size = 4m# specifies the buffer size to use for the index, increasing the index (for all reads and multiple writes) that can be better processed, to the extent that you can afford it. If you make it too big,#  the system will start to change pages and really slows down. The parameter can be set to 384M or 512M for a server that has around 4GB. Check the status values key_read_requests and key_reads,#  to see if the Key_buffer_size setting is 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 used show status like  ' Key_ read% ' obtained). Note: This parameter value setting is too large to be the overall efficiency of the server reduced   ft_min_word_len = 4#  Word segmentation minimum length, default 4  transaction_ The ISOLATION = REPEATABLE-READ# MYSQL supports 4 types of transaction isolation levels, namely: # read-uncommitted, read-committed , repeatable-read, serializable.#  if not specified, MySQL defaults to repeatable-read,oracle default is read-committed   log_bin = mysql-binbinlog_format = mixedexpire_logs_days = 30 # Binlog Delete   log_error = /for more than 30 daysdata/mysql/mysql-error.log  #错误日志路径slow_query_log  = 1long_query_time = 1  #慢查询时间   More than 1 seconds for slow query slow_query_log_file = /data/mysql/mysql-slow.log  performance_schema =  0explicit_defaults_for_timestamp   #lower_case_table_names  = 1  #不区分大小写    skip-external-locking  #MySQL选项以避免外部锁定. This option opens   default-storage-engine = InnoDB  #默认存储引擎   innodb_file_per_table by default  = 1# innodb is a standalone tablespace pattern, and each table in each database generates a data space #  the advantages of a standalone tablespace: # 1. Each table has a self-contained table space. # 2. The data and indexes for each table will exist in the table space themselves. # 3. You can implement a single table to move through different databases. # 4. Space can be recycled (in addition to drop table operations, table air cannot recover) #  disadvantage:#  single table increased too large, such as more than 100g#  conclusion:#  shared table space has little advantage in insert operation. Others do not have a separate table space to perform well. When you enable a stand-alone table space, make reasonable adjustments to the data for the table that the:innodb_open_files  innodb_open_files = 500#  restricts innodb to open, If there are more tables in the library, please add this. This value defaults to 300  INNODB_BUFFER_POOL_SIZE = 64M# INNODB using a buffer pool to hold indexes and raw data,  unlike myisam.#   Here you set the larger you are in the accessThe less disk I/O required for the data in the table .#  on a standalone database server, you can set this variable to the server physical memory size of the 80%#  do not set too large, otherwise, due to the competition of physical memory may cause the operating system to change the page bumps. #   Note that on 32-bit systems you may be limited to each process on the  2-3.5G  user level memory limit,#  so do not set too high.   innodb_write_io_threads  = 4INNODB_READ_IO_THREADS = 4# INNODB uses a background thread to process read-write  i/o (input and output) requests on the data page, depending on your  cpu   Audit number 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 modification, restart the MySQL service, allowed values range from  1-64  innodb_ The default setting of thread_concurrency = 0#  is  0, which means no limit on concurrency, it is recommended to set to 0, better to play CPU multi-core processing capacity, increase concurrency   innodb_ The purge operation in Purge_threads = 1# innodb is a class of operations that periodically reclaim useless data. In previous releases, the purge operation was part of the main thread, which meant that it could clog other database operations at runtime. #  starting with the mysql5.5.x version, the operation runs on a separate thread and supports more concurrent numbers. The user can set the innodb_purge_threads configuration parameter to select whether the purge operation uses a single #  thread, the parameter is set to 0 by default (without using a separate thread), and the set to  1  indicates the use of a separate purge thread. Recommended for 1  innodb_flush_log_at_trx_commit = 2# 0: If the value of Innodb_flush_log_at_trx_commit is 0,log  buffer writes log files to disk every second, and does nothing when committing a transaction (execution is performed by MySQL's Master thread thread). #  the redo log buffers are written to disk per second in the main thread of theRedo log files (redo log). The default log file, regardless of whether the transaction has been committed, is ib_logfile0,ib_logfile1# 1: When set to the default value of 1, the Log buffer brush is written to the log every time the transaction is committed. # 2: If set to 2, each commit transaction will write a log, but does not perform the brush operation. The log file is brushed on a per-second schedule. Note that there is no guarantee that 100% per second will be brushed to disk, depending on the scheduling of the process. #  writes data to the transaction log each time a transaction is committed, where the write is only called the file system's write operation, and the file system is   cached, so this write does not guarantee that the data has been written to the physical disk #  the default value of 1 is to ensure complete acid. Of course, you can set this configuration to a value other than 1 in exchange for higher performance, but you will lose 1 seconds of data when the system crashes. When the #  is set to 0, the mysqld process crashes, losing the last 1 seconds of the transaction. Set to 2, the last 1 seconds of data will be lost only if the operating system crashes or loses power. InnoDB will ignore this value when doing a recovery. #  Summary #  set to 1 is certainly the safest, but the performance page is the worst (as opposed to the other two parameters, but not unacceptable). If the data consistency and integrity requirements are not high, can be set to 2, if only the most performance, such as high concurrent write log server, set to zero for higher performance   innodb_log_buffer_size = 2m#   This parameter determines the amount of memory used by the log files, in M. Larger buffers can improve performance, but unexpected failures will cause data loss. MySQL developer recommended setting to 1-8m between   innodb_log_file_size = 32M#  This parameter determines the size of the data log file, larger settings can improve performance, But it also increases the time it takes to recover the failed database   innodb_log_files_in_group = 3#  to improve performance, MySQL can write log files to multiple files in a circular fashion. The recommended setting is to flush the data in the cache pool to the 3  INNODB_MAX_DIRTY_PAGES_PCT = 90# INNODB main thread, so that the dirty data scale is less than 90%   Innodb_lock_The WAIT_TIMEOUT = 120 # INNODB transaction can wait for a locked timeout number of seconds before being rolled back. InnoDB automatically detects the transaction deadlock in its own locking table and rolls back the transaction. InnoDB uses the Lock tables statement to notice the lock setting. The default value is 50 seconds   bulk_insert_buffer_size = 8M#  BULK Insert cache size,  This parameter is for the MyISAM storage engine. Suitable for,  increase efficiency when inserting 100-1000+ records at once. The default value is 8M. Can be doubled to the size of the data volume.   myisam_sort_buffer_size = 8m# myisam set the size of the buffer used when restoring the table when in repair table or using the Create  index creates an index or alter table the buffer during the  myisam index allocation   myisam_max_sort_file_size = 10g #  if the temporary file becomes more than the index, do not use the Quick Sort index method to create an index. Note: This parameter is given as a byte   myisam_repair_threads = 1#  if the value is greater than 1, the repair by  MyISAM table indexes are created in parallel in the sorting process (each index is in its own line range)     interactive_timeout = 28800#  The number of seconds that the server waits for activity before closing the interactive connection. The interactive client is defined as a client that uses the Client_interactive option in Mysql_real_connect (). Default value: 28,800 seconds (8 hours)   wait_timeout = 28800#  the number of seconds that the server waits for activity before closing a non-interactive connection. When a thread starts, the session Wait_timeout value is initialized based on the global Wait_timeout value or global interactive_timeout value,#  depends on the type of client (Mysql_rEal_connect () connection options client_interactive defined). Parameter default value: 28,800 seconds (8 hours) The maximum number of connections supported by the # mysql server is capped, because each connection is built to consume memory, so we want the client to be connected to Mysql server after the corresponding operation has been processed,#  You should disconnect and release the memory that is occupied. If your mysql server have a large number of idle connections, they will not only consume memory in vain, but if the connection keeps accumulating and,#  will eventually reach the Mysql server connection limit, it will be reported ' too  Many connections ' 's error. For the value setting of wait_timeout, it should be judged according to the operation condition of the system. #  after the system has been running for a period of time, you can view the current system connection status through the Show processlist command, if you find a large number of sleep status of the connection process, then the parameter setting is too large,#  can be appropriately adjusted smaller. To set Interactive_timeout and wait_timeout at the same time will not take effect.   [mysqldump]quickmax_allowed_packet = 16M  #服务器发送和接受的最大包长度   [myisamchk]key_ buffer_size = 8msort_buffer_size = 8mread_buffer = 4mwrite_buffer = 4m

I am also in the study, hope for beginners a little help.

This article from "A Cup of coffee" blog, declined reprint!

Introduction to MySQL configuration optimization in Linux system