MYSQL Server my. cnf Configuration documentation

This configuration was used by the old boy on the production line. During the training, he gave it. Here, I added Chinese characters to the parameters, indicating that the configuration has been optimized, if your mysql has no special circumstances, you can directly use the configuration parameter MYSQL server. detailed description of cnf configuration documents hardware: 16 GB memory [client] port3306

This configuration was used by the old boy on the production line. During the training, he gave it. Here, I added Chinese characters to the parameters, indicating that the configuration has been optimized, if your mysql has no special circumstances, you can directly use the configuration parameter MYSQL server. detailed description of cnf configuration documents hardware: Memory 16 GB [client] port = 3306

This configuration was used by the old boy on the production line. During the training, he gave it. Here, I added a Chinese description of each parameter.
This configuration has been optimized. If your mysql has no special circumstances, you can directly use this configuration parameter.

MYSQL Server my. cnf Configuration documentation
Hardware: 16 GB memory

[Client]
Port = 3306
Socket =/data/3306/mysql. sock

[Mysql]
No-auto-rehash

[Mysqld]
User = mysql
Port = 3306
Socket =/data/3306/mysql. sock
Basedir =/usr/local/mysql
Data =/data/3306/data
Open_files_limit = 10240

Back_log = 600
# Before MYSQL temporarily stops responding to a new request, how many requests can be stored in the stack in a short period of time. If the system has many connections in a short time, you need to increase the value of this parameter, which specifies the size of the listener queue for the incoming TCP/IP connection. The default value is 50.

Max_connections = 3000
# MySQL allows the maximum number of process Connections. If the Too worker Connections error message is frequently displayed, you need to increase the value.

Max_connect_errorrs = 6000
# Set the maximum number of abnormal interruptions of connection requests for each host. When this limit is exceeded, the MYSQL server will disable host connection requests, until the mysql server is restarted or the host information is cleared using the flush hosts command.

Table_cache = 614
# Indicates adjusting the buffer size in the table. # The table_cache parameter sets the table cache quantity. Each connection will open at least one table cache. # Therefore, the size of table_cache should be related to the settings of max_connections. For example, for 200 # concurrent connections, the table cache should be at least 200 × N. Here N is the maximum number of tables in a join where the application can execute the query. In addition, additional file descriptors must be reserved for temporary tables and files.
# When Mysql accesses a table, if the table has been opened in the cache, it can directly access the cache. If # Is Not cached yet, but there is still space in the Mysql table buffer, this table will be opened and placed in the table's slow # dashboard. If the table cache is full, the unused table will be released according to certain rules, or temporarily expand the table cache for storage. The advantage of using the table cache is that you can access the table content more quickly. Execute flush tables to clear the cached content. In general, you can check the status values Open_tables # And Opened_tables of the database peak time to determine whether to increase the value of table_cache (where open_tables is the number of tables opened before, opened_tables indicates the number of opened tables ). That is, if open_tables is close to table_cache and the value of Opened_tables increases gradually, you need to increase the size of this # value. In addition, when Table_locks_waited is relatively high, you also need to add table_cache.


External-locking = FALSE
# Use the-skip-external-locking MySQL option to avoid external locking. This option is enabled by default.

Max_allowed_packet = 32 M
# Set the maximum number of messages transmitted during network transmission. The default value is 1 MB, and the maximum value is 1 GB. You must set a multiple of 1024.

Sort_buffer_size = 2 M
# Sort_Buffer_Size is a connection-level parameter. When this buffer is required for each connection (session) for the first time, the configured memory is allocated at one time.
# Sort_Buffer_Size is not as large as possible. Because it is a connection-level parameter, excessive settings + high concurrency may exhaust system memory resources. For example, 500 connections consume 500 * sort_buffer_size (8 M) = 4G memory.
# When Sort_Buffer_Size exceeds 2 kb, mmap () instead of malloc () will be used for memory allocation, resulting in reduced efficiency.
# Technical guidance http://blog.webshuo.com/2011/02/16/mysql-sort_buffer_size/
# Dev-doc: http://dev.mysql.com/doc/refman/5.5/en/server-parameters.html
# Explain select * from table where order limit; filesort appears
# Key optimization parameters

Join_buffer_size = 2 M
# Used for the size of the associated cache between tables. Like sort_buffer_size, the allocated memory corresponding to this parameter is also exclusive to each connection.

Thread _ cache_size = 300
# The value cached by the server thread indicates that the number of threads stored in the cache can be reused. If there is space in the cache when the connection is disconnected, the client thread will be placed in the cache, if the thread is re-requested, the request will be read from the cache. If the cache is empty or a new request, the thread will be re-created. If there are many new threads, adding this value can improve the system performance. by comparing variables in Connections and Threads_created states, you can see the role of this variable. The setting rules are as follows: 1 GB memory is configured as 8, 2 GB is configured as 16, 3 GB is configured as 32, 4 GB or higher, and can be configured as larger.

Thread_concurrency = 8
# Setting the value of thread_concurrency is correct or not, which has a great impact on mysql performance. When multiple CPUs (or multiple cores) are used, the value of thread_concurrency is incorrectly set, as a result, mysql cannot make full use of multiple CPUs (or multiple cores), and only one cpu (or core) can work at the same time. Thread_concurrency should be set to 2 times the number of CPU cores. For example, if there is a dual-core CPU, thread_concurrency should be 4; if there are two dual-core CPUs, the value of thread_concurrency should be 8
# Key optimization parameters

Query_cache_size = 64 M
# For MySQL users, this variable is certainly not unfamiliar to everyone. During MyISAM engine optimization in the past few years, this parameter is also an important optimization parameter. But with the development, this parameter also exposes some problems. Machine memory is getting bigger and bigger, and people tend to allocate more and more useful parameters. This parameter is also caused by a series of problems. First, let's analyze the working principle of query_cache_size: After a SELECT query works in the DB, the DB caches the statement. When the same SQL statement is called in the DB again, the DB returns the result from the cache to the Client without changing the table. Here is a reference point, that is, when DB uses Query_cache to work, it is required that the table involved in this statement not be changed during this period. What if the data in Query_cache is processed when the table is changed? First, set all the Query_cache statements related to the table to invalid, and then write the updates. If Query_cache is very large, the query structure of the table is large, and the query statement becomes invalid, an update or Insert operation will be slow, in this way, we can see how the Update or Insert operation is so slow. This parameter is not suitable for systems with a large number of database writes or updates. In addition, for systems with high concurrency and large write volumes, we recommend that you disable this function.
# Key optimization parameters (primary database addition, deletion, modification, and MyISAM)

Query_cache_limit = 4 M
# Specify the buffer size that can be used by a single query. The default value is 1 MB.

Query_cache_min_res_unit = 2 k
# The default value is 4 kb. Setting a large value is good for big data queries. However, if you query small data queries, memory fragmentation and waste may occur.
# Query cache fragmentation rate = Qcache_free_blocks/Qcache_total_blocks * 100%
# If the query cache fragmentation rate exceeds 20%, you can use flush query cache to sort out the CACHE fragmentation, or try to reduce query_cache_min_res_unit, if your QUERY involves a small amount of data.
# Query cache utilization = (query_cache_size-Qcache_free_memory)/query_cache_size * 100%
# If the query cache utilization is below 25%, it indicates that the query_cache_size setting is too large and can be appropriately reduced. If the query cache utilization is above 80% and Qcache_lowmem_prunes> 50, it indicates that query_cache_size may be small or too many fragments.
# Query cache hit rate = (Qcache_hits-Qcache_inserts)/Qcache_hits * 100%

Default-storage-engine = MyISAM
# Default_table_type = InnoDB

Thread_stack = 192 K
# Set the stack size of each MYSQL thread. The default value is large enough to meet normal operations. The value range is kb to 4 GB. The default value is kb.

Transaction_isolation = READ-COMMITTED
# Set the default transaction isolation level. The available level is as follows:
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
#1. read uncommitted-read uncommitted 2. read committe-READ committed 3. repeatable read-repeatable read 4. SERIALIZABLE-serial

Tmp_table_size = 256 M
# The default size of tmp_table_size is 32 MB. If a temporary table exceeds this size, MySQL generates an error in The table tbl_name is full format. If you do many advanced group by queries, increase The tmp_table_size value. If this value is exceeded, the temporary table is written to the disk.
Max_heap_table_size = 256 M
Long_query_time = 2
Log_long_format
Log-slow-queries =/data/3306/slow-log.log
# Log-bin =/data/3306/mysql-bin
Log-bin
Binlog_cache_size = 4 M
Max_binlog_cache_size = 8 M
Max_binlog_size = 512 M

Expire_logs_days = 7
Key_buffer_size = 2048 M
# Batch set the buffer size used for indexing. Increasing the size can improve the index processing performance. For servers with around 4 GB of data, this parameter can be set to 384 MB or MB.

Read_buffer_size = 1 M
# Size of the buffer read from MySql. Requests that perform sequential scans on the table will be allocated with a read buffer, and MySql will allocate it with a memory buffer. The read_buffer_size variable controls the size of the buffer. If you want to scan the table in a very frequent order and think that frequent scanning is too slow, you can increase the performance by increasing the variable value and memory buffer size. Like sort_buffer_size, the allocated memory corresponding to this parameter is also exclusive to each connection.

Read_rnd_buffer_size = 16 M
# MySql random read (query operation) buffer size. When a row is read in any order (for example, in the sorting order), a random read cache is allocated. During sorting query, MySql first scans the buffer to avoid disk search and increase the query speed. If you need to sort a large amount of data, you can increase the value accordingly. However, MySql will issue this buffer space for each client connection. Therefore, set this value as much as possible to avoid excessive memory overhead.

Bulk_insert_buffer_size = 64 M
# Batch insert data cache size, which can effectively improve insertion efficiency. The default value is 8 Mb.

Myisam_sort_buffer_size = 128 M
# Cache required for re-sorting when the MyISAM table changes

Myisam_max_sort_file_size = 10G
# The maximum size of temporary files allowed when MySQL re-creates an index (when REPAIR, alter table, or load data infile ).
# If the file size is greater than this value, the index will be created through the key-value buffer (slower)

Myisam_max_extra_sort_file_size = 10G
Myisam_repair_threads = 1
# If a table has more than one index, MyISAM can use more than one thread to fix them through parallel sorting.
# This is a good choice for users with multiple CPUs and a large amount of memory.

Myisam_recover
# Automatic check and repair of MyISAM tables that are not properly closed
Skip-name-resolve
Lower_case_table_names = 1

Server-id = 1

Innodb_additional_mem_pool_size = 16 M
# This parameter is used to set the data directory information stored in InnoDB and the memory pool size of other internal data structures, similar to the Oracle library cache. This is not a mandatory parameter and can be broken through.

Innodb_buffer_pool_size = 2048 M
# This is very important for Innodb tables. Compared with MyISAM tables, Innodb is more sensitive to buffering. MyISAM can run in the default key_buffer_size setting. However, Innodb is similar to snail bait in the default innodb_buffer_pool_size setting. Because Innodb caches data and indexes, there is no need to leave too much memory for the operating system. Therefore, if you only need Innodb, you can set it to up to 70-80% of available memory. Some rules apply to key_buffer: If your data volume is small and does not increase rapidly, you do not need to set innodb_buffer_pool_size too large.

Innodb_data_file_path = ibdata1: 1024 M: autoextend
# Important data of tablespace files

Innodb_file_io_threads = 4
# Number of file I/O threads, which is generally 4, but can be set to a large value in Windows.

Innodb_thread_concurrency = 8
# Set the number of CPUs on the server. We recommend that you use the default setting, which is generally 8.

Innodb_flush_log_at_trx_commit = 2
# If this parameter is set to 1, logs are written to the disk after each transaction is committed. To provide performance, it can be set to 0 or 2, but it must bear the risk of data loss in the event of a fault. 0 indicates that the transaction log is written to the log file, and the log file is refreshed to the disk once per second. 2 indicates that the transaction log is written at the time of submission, but the log file is refreshed to the disk every time.

Innodb_log_buffer_size = 16 M
# This parameter determines the memory size used by some log files, in MB. A larger buffer zone can improve performance, but unexpected faults will cause data loss. We recommend that you set this parameter to 1-8 MB for MySQL developers.

Innodb_log_file_size = 128 M
# This parameter determines the size of the data log file, in MB. Larger settings can improve performance, but also increase the time required to recover the faulty database.

Innodb_log_files_in_group = 3
# To improve performance, MySQL can write log files to multiple files cyclically. Recommended to 3 M

Innodb_max_dirty_pages_pct = 90

# The number of Dirty_Page in Buffer_Pool directly affects the InnoDB close time. The innodb_max_dirty_pages_pct parameter can directly control the proportion of Dirty_Page in Buffer_Pool. Fortunately, innodb_max_dirty_pages_pct can be dynamically changed. Therefore, reduce innodb_max_dirty_pages_pct before disabling InnoDB, and force the data block to Flush for a period of time, which can greatly shorten the MySQL shutdown time.

Innodb_lock_wait_timeout = 120
# InnoDB has its built-in Deadlock Detection Mechanism, which can cause unfinished transaction rollback. However, if InnoDB is used with the lock tables Statement of MyISAM or a third-party transaction engine, InnoDB cannot identify the deadlock. To eliminate this possibility, you can set innodb_lock_wait_timeout to an integer indicating how long MySQL will wait before allowing other transactions to modify the data that is eventually rolled back by the transaction (in seconds)

Innodb_file_per_table = 0
# Exclusive tablespace (off)

[Mysqldump]
Quick
Max_allowed_packet = 32 M

[Mysqld_safe]
Log-error =/data/3306/mysql_oldboy.err
Pid-file =/data/3306/mysqld. pid



# Supplement
# Wait_timeout = 10
# Specify the maximum connection time of a request. For memory servers of about 4 GB, you can set it to 5-10.

# Interactive_timeout = 10
Parameter description: The number of seconds before the server closes the interactive connection. Interactive clients are defined as clients that use the CLIENT_INTERACTIVE option in mysql_real_connect.
Default Value of the parameter: 28800 seconds (8 hours)

# Skip_networking
# Enabling this option can completely disable the MYSQL TCP/IP connection mode. If the WEB server accesses the MYSQL database server remotely, do not enable this option, otherwise, the connection will fail.

# Log-queries-not-using-indexes
Record queries that do not use Indexes

Linux system O & M:
Http://www.linuxyw.com/a/shujuku/20130506/216.html