mysql-5.5---configuration file parameter description

Example configuration:

[Client]

Character-set-server = UTF8//setting MySQL client default character set

Port = 3306//Set the default ports to be used by the MySQL client when connecting to the server

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

[Mysqld]

Character-set-server = UTF8//default character Set

user = MySQL//mysql users

Port = 3306//mysql Server default listener port

Socket =/data/mysql/3306/mysql.sock//Connection Database socket path

Basedir =/usr/local/webserver/mysql//mysql installation directory

DataDir =/data/mysql/3306/data//mysql The directory where the database files are located

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

Pid-file =/data/mysql/3306/mysql.pid//Specify a file that holds the process ID for the MYSQLD program

Open_files_limit = 10240//Set the number of open files allowed at least

Table_cache = 512//number of data tables open at the same time

Back_log =//back_log is the number of connections that the operating system can hold in the listening queue

Max_connections = maximum number of database connections processed concurrently by the//MYSQL server (default setting is 100)

Max_connect_errors = 6000//Per client connection maximum number of errors allowed, if this limit is reached, this client will be The MySQL service blocks until "flush hosts" is executed or User Service restart

external-locking = FALSE//Allow external file-level locks, opening file locks can adversely affect performance, so only if you This option is only used when running multiple DB instances on the same file (note still There will be other constraints! ), or you use some other software dependencies on the file level to lock the MyISAM table

Max_allowed_packet = 32M//maximum request size that the service can handle

Sort_buffer_size = 8M//The length of the buffer allocated for the sort operation (the default setting is 2M)

Join_buffer_size = 1M

Thread_cache_size = 300

Thread_concurrency = 8

Query_cache_size = 512M

Query_cache_limit = 2M

Query_cache_min_res_unit = 2k

Default-storage-engine = MyISAM

Thread_stack = 192K

Transaction_isolation = read-committed

Tmp_table_size = 246M

Max_heap_table_size = 246M

Key_buffer_size = 512M

Read_buffer_size = 4M

Read_rnd_buffer_size = 16M

Bulk_insert_buffer_size = 64M

Myisam_sort_buffer_size = 128M

Myisam_max_sort_file_size = 10G

Myisam_repair_threads = 1

Myisam_recover

Interactive_timeout = 120

Wait_timeout = 120

Innodb_data_home_dir =/data/mysql/3306/data

Innodb_data_file_path = Ibdata1:2000m;ibdata2:10m:autoextend

Innodb_additional_mem_pool_size = 16M

Innodb_buffer_pool_size = 512M

Innodb_file_io_threads = 4

Innodb_thread_concurrency = 8

Innodb_flush_log_at_trx_commit = 2

Innodb_log_buffer_size = 16M

Innodb_log_file_size = 128M

Innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

Innodb_lock_wait_timeout = 120

innodb_file_per_table = 0

Slow-query-log-file=/data/mysql/3306/slow.log

Long_query_time = 1

replicate-ignore-db = MySQL

REPLICATE-IGNORE-DB = Test

Replicate-ignore-db = Information_schema

Log-slave-updates

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

Binlog_cache_size = 4M

Binlog_format = MIXED

Max_binlog_cache_size = 64M

Max_binlog_size = 1G

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

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

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

Expire_logs_days = 30

Skip-name-resolve

#master-connect-retry = 10

Slave-skip-errors = 1032,1062,126,1114,1146,1048,1396

Server-id = 1

[Mysqldump]

Quick

Max_allowed_packet = 32M

[Myisamchk]

Key_buffer_size = 256M

Sort_buffer_size = 256M

Read_buffer = 2M

Write_buffer = 2M

[Mysqlhotcopy]

Interactive-timeout

Parameter description:

The Table_cache 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. Executing flush tables empties the cached content. In general, you can determine whether you need to increase the value of Table_cache (where Open_tables is the number of currently open tables) by looking at the status values Open_tables and Opened_tables of the database running peak time, opened_tables is the number of tables that have been opened). That is, if open_tables approach Table_cache, and opened_tables this value is gradually increasing, then consider increasing the size of this value. There is table_locks_waited higher than the time, also need to increase the table_cache.

Back_log non-dynamic variable, need to restart service. Specifies the number of possible connections for MySQL. When the MySQL main thread receives very many connection requests in a very short time, this parameter takes effect and 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 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. The default value is 50. For Linux systems, the recommended setting is an integer less than 512.

Max_connections MySQL allows the maximum number of connections.

Max_connect_errors How many error connections can be allowed.

Max_allowed_packet sets the maximum package, limits the size of packets accepted by the server, avoids the problem of long SQL execution with a default value of 16M, when a MySQL client or mysqld server receives packets larger than Max_allowed_packet bytes , a "packet is too large" error is issued and the connection is closed. For some clients, if the communication packet is too large, you may encounter a "missing connection to the MySQL server" error during query execution. The default value is 16M. See also: dev-doc:http://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html

Sort_buffer_size is a connection-level parameter that allocates the set of memory once for the first time each connection (session) needs to use this Buffer. Sort_buffer_size is not the bigger the better, because it is a connection-level parameter, too large settings + high concurrency may drain the system memory resources. For example: 500 connections will consume 500*sort_buffer_size (8M) of =4g memory. When Sort_buffer_size exceeds 2KB, memory allocations are made using mmap () rather than malloc (), resulting in reduced efficiency. Official Reference Document: Http://dev.mysql.com/doc/refman/5.5/en/server-parameters.html

Sort_buffer_size is a key optimization parameter.

Join_buffer_size is used to size the associated cache between tables.

Thread_cache_size Server thread Cache This value indicates 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, and if the thread is requested again, the request will be read from the cache. If the cache is empty or a new request, then the thread will be recreated, and if there are many new threads, adding this value can improve system performance. You can see the effect of this variable by comparing the variables of the Connections and threads_created states.

Thread_concurrency set the correct value of thread_concurrency, the performance of MySQL is very significant, in the case of multiple CPUs (or multicore), the error set the value of Thread_concurrency, Will cause MySQL to not take full advantage of multiple CPUs (or multicore) that occur at the same moment only one CPU (or core) is working. The thread_concurrency should be set to twice times the number of CPU cores. For example, there is a dual-core CPU, then the thread_concurrency should be 4; 2 Dual-core CPUs, the value of thread_concurrency should be 8 (is a key optimization parameter)

Query_cache_size for users who use MySQL, you will not be unfamiliar with this variable. In previous years of MyISAM engine optimization, this parameter was also an important optimization parameter. But with the development, this parameter also reveals some problems. The memory of the machine is getting bigger and larger, and people are accustomed to allocating the previously useful parameters more and more. This increase in parameters also raises a number of questions. Let's start by analyzing how Query_cache_size works: When a select query works in DB, the DB caches the statement, and when the same SQL comes back to the DB, the DB returns the result from the cache to the client if the table does not change. There is a shut-down point, that is, when DB is working with Query_cache, it requires that the table involved in the statement not be changed during this time period. So what happens to the data in Query_cache if the table is changed? The first thing to do is to invalidate the Query_cache and the table-related statements, and then write the update. So if the query_cache is very large, the query structure of the table is more, the query statement invalidation is slow, an update or insert will be very slow, so see is update or insert how slow. Therefore, in the database write volume or update volume is also relatively large system, this parameter is not suitable for allocation too large. And in the high concurrency, write a large-scale system, built to disable the function. Key optimization parameters (main library additions and deletions changed-myisam)

QUERY_CACHE_LIMIT Specifies the buffer size that can be used by a single query, which defaults to 1M.

Query_cache_min_res_unit default is 4KB, setting a large value for big data query is good, but if your query is small data query, it is easy to create memory fragmentation and waste.

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 defragment the cache, or try to reduce query_cache_min_res_unit if your queries are small data volumes.

Query Cache utilization = (query_cache_size–qcache_free_memory)/query_cache_size * 100%

Query cache utilization below 25% indicates that the query_cache_size setting is too large to be appropriately reduced; query cache utilization is above 80% and Qcache_lowmem_prunes > 50 query_cache_ Size may be a little bit small, or too much fragmentation.

Query Cache Hit Ratio = (qcache_hits–qcache_inserts)/qcache_hits * 100%

Thread_stack limits the stack size for each database thread. The default settings are sufficient for most applications.

Transaction_isolation sets the default transaction isolation level. Available levels are: 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

The default size of Tmp_table_size is 32M. If a temporary table exceeds that size, MySQL produces an error in the form of the table tbl_name is, and if you do many advanced GROUP by queries, increase the tmp_table_size value.

Key_buffer_size Index Cache Size: It determines the speed of database index processing, especially the speed of index reads.

Read_buffer_size MySQL read-in 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 MySQL's random read (query operation) buffer size. 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.

Bulk_insert_buffer_size BULK INSERT data cache size, can effectively improve the efficiency of insertion, the default is 8M.

Myisam_sort_buffer_size the buffer required for reordering when the MyISAM table changes.

Myisam_max_sort_file_size the size of the maximum temporary file allowed when MySQL rebuilds the index (when REPAIR, ALTER TABLE, or LOAD DATA INFILE) ... If the file size is larger than this value, the index is created (slower) by a key-valued buffer

Myisam_repair_threads 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 large memory situation.

Myisam_recover automatic check and repair of MyISAM table without proper shutdown

Innodb_data_file_path table space File important data

Innodb_additional_mem_pool_size This parameter is used to set the memory pool size of InnoDB stored data directory information and other internal data structures, similar to Oracle's library cache. This is not a mandatory parameter and can be breached.

Innodb_buffer_pool_size This is very important for the InnoDB table. InnoDB is more sensitive to buffering than the MyISAM table. MyISAM can be run under the default Key_buffer_size settings, but InnoDB is snail-like under the default Innodb_buffer_pool_size settings. Since InnoDB caches both data and indexes, there is no need to leave too much memory on the operating system, so if you only need to use InnoDB, you can set it up to 70-80% of usable memory. Some rules apply to Key_buffer-if your data volume is small and does not increase, then you do not need to set the innodb_buffer_pool_size too large

The number of threads for the innodb_file_io_threads file IO, typically 4, but under Windows, can be set larger.

Innodb_thread_concurrency the number of allowed threads within the InnoDB core. Optimal values depend on how the application, hardware, and operating system are scheduled. A value that is too high may cause a mutually exclusive bump on the thread.

Innodb_flush_log_at_trx_commit If this parameter is set to 1, the log will be written to disk after each commit transaction. to * * * can, can be set to 0 or 2, but bear 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.

Innodb_log_buffer_size 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 recommends setting the 1-8m between

Innodb_log_file_size This parameter determines the size of the data log file, in m, larger settings can improve performance, but also increases the time required to recover the failed database

Innodb_log_files_in_group to improve performance, MySQL can write log files to multiple files in a circular fashion. Recommended setting is 3, recommended reading http://www.taobaodba.com/html/221_innodb_max_dirty_pages_pct_checkpoint.html

The amount of dirty_page in the innodb_max_dirty_pages_pct 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.

The Innodb_lock_wait_timeout 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.

Innodb_file_per_table Exclusive Table space (0 for off)

innodb_file_per_table = 0

Slow_query_log enable the slow query log, or enable the –slow-query-log-file=/data/mysql/3306/slow.log parameter when MySQL starts.

Log-slave-updates Configure the update operation from the library whether to write binary files, if this from the library, but also to do other from the library's main library, then you need to hit this parameter, so that the library from the library to be able to log synchronization this parameter to be used with the-logs-bin

Binlog_format SQL statement-based replication (statement-based replication, SBR), row-based replication (row-based replication, RBR), mixed-mode replication (mixed-based Replication, MBR). Accordingly, there are three types of Binlog: statement,row,mixed.

mysql-5.5---configuration file parameter description