Detailed description of mysql configuration parameters and mysql Parameters

Detailed description of mysql configuration parameters and mysql Parameters

[Client] port = 3306 socket =/var/lib/mysql. sock [mysql] # This configuration section sets the conditions for starting the MySQL service. In this case, no-auto-rehash ensures that the service starts faster. No-auto-rehash [mysqld] user = mysql port = 3306 socket =/var/lib/mysql. sock basedir =/usr/local/mysql datadir =/data/mysql/data/open_files_limit = 10240back_log = 600 # Before MYSQL temporarily stops responding to new requests, the number of requests in a short period of time can be stored in the stack. 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 80. Max_connections = 3000 # maximum number of processes allowed by MySQL. If the Too worker Connections error message is frequently displayed, you need to increase the value. The default value is 151max_connect_errors = 6000 # sets the maximum number of abnormal connection requests of 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. The default value is 100external-locking = FALSE # use the-skip-external-locking MySQL option to avoid external locking. This option enables max_allowed_packet = 32 M by default # sets the maximum number of messages transmitted during network transmission. The default value is 4 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 each connection (session) needs to use this buffer for the first time, the configured memory is allocated at a 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, this reduces the efficiency. The default value is 2 MB. You can use the default value # join_buffer_size = 2 M # to associate the cache size between tables. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection. The default value is 2 MB. You can use the default value thread_cache_size = 300 # default value 38 # The value cached by the server thread indicates that the number of threads saved in the cache can be reused, when the connection is disconnected, if there is space in the cache, the client thread will be put into the cache. If the thread is requested again, the request will be read from the cache, if the cache is empty or a new request, this thread will be re-created. If there are many new threads, increasing this value can improve 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 # The default value is 10. Use 10 to check whether the value of thread_concurrency is set correctly or not, which has a great impact on mysql performance) if the value of thread_concurrency is set incorrectly, mysql may not 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, the thread_concurrency should be 4; if there are two dual-core CPUs, The thread_concurrency value should be 8query_cache_size = 64 M # during MyISAM engine optimization, this parameter is also an important optimization parameter. But some problems are also exposed. The machine memory is getting bigger and bigger, and it is customary to allocate more and more 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. Query_cache_limit = 4 M # specify the buffer size that a single query can use. The default value is 1Mquery_cache_min_res_unit = 2 k # The default value is 4 kb. Setting a large value is good for big data queries, however, if all your queries are small data queries, it will easily cause 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 the flush query cache to sort out CACHE fragments, or try to reduce query_cache_min_res_unit if your queries contain small amounts of data. # Query cache utilization = (query_cache_size-Qcache_free_memory)/query_cache_size * 100% # If the query cache utilization is lower than 25%, the query_cache_size setting is too large and can be appropriately reduced; if the query Cache Usage is above 80% and Qcache_lowmem_prunes> 50, the query_cache_size may be small, or there may be too many fragments. # Query cache hit rate = (Qcache_hits-Qcache_inserts) /Qcache_hits * 100% # default-storage-engine = MyISAM # default_table_type = InnoDB # failed to enable # thread_stack = 192 K # Set the stack size of each MYSQL thread. The default value is large enough, common operations are supported. The value range is kb to 4 GB. The default value is KB. Use the default value transaction_isolation = READ-COMMITTED # To set the default transaction isolation level. the available level is as follows: read uncommitted-read uncommitted read committe-READ committed repeatable read-repeatable serializable-serial tmp_table_size = 256 M # tmp_table_size the default size is 32 M. 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 = 256Mexpire_logs_days = 7 key_buffer_size = 2048 M # batch set the buffer size 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 # default 128 K # MySql read buffer size. 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 8Mmyisam_sort_buffer_size = 128 M # buffer required for re-sorting when the MyISAM table changes. The default value is 8Mmyisam_max_sort_file_size = 10G # maximum size of temporary files allowed when the MySQL index is re-Indexing, 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 5.6 this value is not set # myisam_repair_threads = 1 default value: 1 # If a table has more than one index, MyISAM can use more than one thread to fix them through parallel sorting. # This is good for users with multiple CPUs and a large amount of memory. Myisam_recover # automatically checks and fixes the MyISAM Table skip-name-resolve lower_case_table_names = 1 server-id = login = 16 M # this parameter is used to set the data directory information and memory pool size of other internal data structures, similar to 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 setting too large, leading to an error, the default value is 12 Mb. # The important data in the tablespace file # innodb_file_io_threads = 4 is not clear. The default value is used. # The number of file I/O threads is generally 4, but it 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 use the default 8 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. it is recommended that MySQL developers set it between 1-8 M # innodb_log_file_size = 128 M use the default 48 M # This parameter determines the size of the Data Log File, in MB. Larger settings can improve performance, however, it will also increase the time required to recover the faulty database # innodb_log_files_in_group = 3 use the default 2 # to improve performance, MySQL can write log files to multiple files in a circular manner. It is recommended to set to 3 M # innodb_max_dirty_pages_pct = 90 use default 75 observation # It is recommended to read the number of Dirty_Page in the http://www.taobaodba.com/html/221_innodb_max_dirty_pages_pct_checkpoint.html# Buffer_Pool, directly affecting 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 # The default value is 50 seconds # 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 # default: No # exclusive tablespace (off) [mysqldump] quick # max_allowed_packet = 32 M [mysqld_safe] log-error =/data/mysql/mysql_oldboy.err pid-file =/data/mysql/mysqld. pidsql_mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES