MySQL configuration file my. cnf optimization (mysql5.5) _ MySQL

This article describes how to optimize MySQL configuration file my. cnf. For more information, see MySQL 5.5.13.

Parameter description:

[Client]

Character-set-server = utf8

Port = 3306

Socket =/data/mysql/3306/mysql. sock

[Mysqld]

Character-set-server = utf8

User = mysql

Port = 3306

Socket =/data/mysql/3306/mysql. sock

Basedir =/usr/local/webserver/mysql

Data =/data/mysql/3306/data

Log-error =/data/mysql/3306/mysql_error.log

Pid-file =/data/mysql/3306/mysql. pid

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

Open_files_limit = 10240

Table_cache = 512

# Non-dynamic variables, need to restart the service

# Specify the number of possible MySQL connections. When the MySQL main thread receives many connection requests within a short period of time, this parameter takes effect. the main thread takes a short time to check the connection and start a new thread. The value of the back_log parameter indicates how many requests can be stored in the stack within a short 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 listener queue for the incoming TCP/IP connection. Different operating systems have their own limits on the queue size. Trying to set back_log to be higher than your operating system limit will be invalid. The default value is 50. We recommend that you set the value to an integer smaller than 512 in Linux.

Back_log = 600

# Max connections allowed by MySQL

Max_connections = 5000

# How many error connections can be allowed

Max_connect_errorrs = 6000

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

External-locking = FALSE

# Set the maximum package to limit the size of data packets received by the server to avoid excessive SQL execution. the default value is 16 MB. when the MySQL client or mysqld server receives an information package larger than max_allowed_packet, the "information package is too large" error will be sent and the connection will be closed. For some clients, if the communication information package is too large, a "lost connection to MySQL server" error may occur during query. The default value is 16 MB.

# Dev-doc: http://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html

Max_allowed_packet = 32 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

Sort_buffer_size = 8 M

# Used to associate the cache size between tables

Join_buffer_size = 1 M

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

Thread _ cache_size = 300

# 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

Thread_concurrency = 8

# 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. This function is disabled for systems with high concurrency and large write volumes.

# Key optimization parameters (primary database addition, deletion, modification, and MyISAM)

Query_cache_size = 512 M

# Specify the buffer size that can be used by a single query. the default value is 1 MB.

Query_cache_limit = 2 M

# 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%

Query_cache_min_res_unit = 2 k

Default-storage-engine = MyISAM

# Limit the stack size used for each database thread. The default setting is sufficient for most applications.

Thread_stack = 192 K

# 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

Transaction_isolation = READ-COMMITTED

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

Tmp_table_size = 246 M

Max_heap_table_size = 246 M

# Index cache size: it determines the database index processing speed, especially the index reading speed.

Key_buffer_size = 512 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.

Read_buffer_size = 4 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.

Read_rnd_buffer_size = 16 M

# Batch insert data cache size, which can effectively improve insertion efficiency. the default value is 8 MB.

Bulk_insert_buffer_size = 64 M

# Cache required for re-sorting when the MyISAM table changes

Myisam_sort_buffer_size = 128 M

# 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_sort_file_size = 10G

# 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_repair_threads = 1

# Automatic check and repair of MyISAM tables that are not properly closed

Myisam_recover

Interactive_timeout = 120

Wait_timeout = 120

Innodb_data_home_dir =/data/mysql/3306/data

# Important data of tablespace files

Innodb_data_file_path = ibdata1: 2000 M; ibdata2: 10 M: autoextend

# 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_additional_mem_pool_size = 16 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_buffer_pool_size = 512 M

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

Innodb_file_io_threads = 4

# Number of threads allowed in the InnoDb core.

# The optimal value depends on the scheduling methods of applications, hardware, and operating systems.

# A high value may cause mutually exclusive thread bumps.

Innodb_thread_concurrency = 8

# 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_flush_log_at_trx_commit = 2

# 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_buffer_size = 16 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_file_size = 128 M

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

Innodb_log_files_in_group = 3

# Recommended read http://www.taobaodba.com/html/221_innodb_max_dirty_pages_pct_checkpoint.html

# 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_max_dirty_pages_pct = 90

# 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_lock_wait_timeout = 120

# Exclusive tablespace (off)

Innodb_file_per_table = 0

# Start mysqld with-slow-query-log-file =/data/mysql/3306/slow. log

Slow_query_log

Long_query_time = 1

Replicate-ignore-db = mysql

Replicate-ignore-db = test

Replicate-ignore-db = information_schema

# Configure whether to write binary files for update operations on the Slave Database. if this slave database needs to be used as the master database of another slave database, this parameter is required, so that the slave database of the slave database can synchronize logs. this parameter must be used with-logs-bin.

Log-slave-updates

Log-bin =/data/mysql/3306/binlog

Binlog_cache_size = 4 M

# STATEMENT, ROW, MIXED

# SQL statement-based replication, row-based replication, and RBR ). Correspondingly, there are three binlog formats: STATEMENT, ROW, and MIXED.

Binlog_format = MIXED

Max_binlog_cache_size = 64 M

Max_binlog_size = 1G

Relay-log-index =/data/mysql/3306/relaylog

Relay-log-info-file =/data/mysql/3306/relaylog

Relay-log =/data/mysql/3306/relaylog

Expire_logs_days = 30

Skip-name-resolve

# Master-connect-retry = 10

Slave-skip-errors = 1396

Server-id = 1

[Mysqldump]

Quick

Max_allowed_packet = 32 M

[Myisamchk]

Key_buffer_size = 256 M

Sort_buffer_size = 256 M

Read_buffer = 2 M

Write_buffer = 2 M

[Mysqlhotcopy]

Interactive-timeout