Mysql Configuration | mysql configuration optimization | my. cnf Optimization

Mysql configuration optimization my. cnf optimization explanation MySQLPHP [client] default-character-setutf8port3306socketoptmysqlmysql.sock [mysqld] character-set-serverutf8usermysqlport3306socketoptmysqlmysql.sockbasediroptmysqldatadiropt

Mysql configuration optimization my. cnf optimization details MySQL PHP [client] default-character-set = utf8port = 3306 socket =/opt/mysql. sock [mysqld] character-set-server = utf8user = mysqlport = 3306 socket =/opt/mysql. sockbasedir =/opt/mysql/datadir =/opt/

Mysql Configuration Optimization
My. cnf optimization details MySQL PHP

[Client] default-character-set = utf8port = 3306 socket =/opt/mysql. sock [mysqld] character-set-server = utf8user = mysqlport = 3306 socket =/opt/mysql. sockbasedir =/opt/mysql/datadir =/opt/mysql/datalog-error =/opt/mysql/log/mysql_error.logpid-file =/opt/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 = 10240table_cache = 512 # non-dynamic variable. 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 # maximum number of connections allowed by MySQL max_connections = 5000 # maximum number of errors allowed to connect to max_connect_errors = 6000 # use the-skip-external-locking MySQL option to avoid external locking. This option enables external-locking = FALSE by default # sets the maximum package, limits the size of the packets received by the server, and avoids excessive SQL Execution problems. The default value is 16 Mb, when the MySQL client or mysqld server receives an information packet greater than the value of max_allowed_packet, the "information packet is too large" error is sent and the connection is 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.htmlmax_allowed_packet = 32 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 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, this reduces the 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; the filesort # key optimization Parameter sort_buffer_size = 8 M # join_buffer_size = 1 M # The server thread cache value indicates that the number of threads stored 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 the Connections and Threads_created states, we can see that the role of this variable is thread_cache_size = 300 # whether the value of thread_concurrency is set correctly has a great impact on mysql performance, when multiple CPUs (or multiple cores) are used, thread_concurr is set incorrectly. However, mysql cannot fully utilize 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 # The key optimization Parameter thread_concurrency = 8 # For MySQL users, you will not be unfamiliar with this variable. 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 available for a single query. The default value is 1Mquery_cache_limit = 2 M # The default value is 4KB, setting a large value is good for big data queries, but if your queries are all small data queries, it is easy to 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 are all small data volumes. # 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% query_cache_min_res_unit = 2kdefault-storage-engine = MyISAM # limit the stack size used for each database thread. The default setting is sufficient for most applications with thread_stack = 192 K # The default transaction isolation level is set. available level: # READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE #1. read uncommitted-READ not submitted 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 = 246Mmax_heap_table_size = 246 M # index cache size: it determines the database index processing speed, especially the index read Speed key_buffer_size = 512 M # 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. 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 8Mbulk_insert_buffer_size = 64 M # buffer needed for re-sorting when the MyISAM table changes. myisam_sort_buffer_size = 128 M # maximum temporary file size allowed when MySQL re-indexing (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 # automatically check and fix the MyISAM Table myisam _ which is not properly closed _ Recoverinteractive_timeout = 120wait_timeout = 120innodb_data_home_dir =/opt/mysql/data # 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, innodb_buffer_pool_size is not set too large. innodb_buffer_pool_size = 512 M # Number of file IO threads, generally 4, however, in Windows, you can set a large value. 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. it is recommended that MySQL developers set innodb_log_buffer_size = 16 M between 1 and 8 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 for restoring the faulty database. innodb_log_file_size = 128 M # to improve performance, MySQL can write log files to multiple files cyclically. Recommended Value: 3Minnodb_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 a built-in Deadlock Detection mechanism that can roll back unfinished transactions. 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 =/opt/mysql/log/slow. logslow_query_loglong_query_time = 1replicate-ignore-db = mysqlreplicate-ignore-db = testreplicate-ignore-db = information_schema # configure whether update operations on the slave database write binary files, this parameter is required for other slave databases. This parameter must be used with-logs-bin to synchronize ROW logs. log-slave-updateslog-bin =/opt/mysql/log/binlogbinlog_cache_size = 4 M # STATEMENT, ROW, MIXED # SQL statement-based replication (statement-based replication, SBR), row-based replication (RBR), and hybrid replication (mixed-based replication, MBR ). Correspondingly, there are three binlog formats: STATEMENT, ROW, and MIXED. Binlog_format = plugin = 64Mmax_binlog_size = 1Grelay-log-index =/opt/mysql/log/relaylogrelay-log-info-file =/opt/mysql/log/relaylogrelay-log =/opt/mysql/log /relaylogexpire_logs_days = 30skip-name-resolve # master-connect-retry = 10slave-skip-errors = 1032,1062,, 1114, 1146,1048, 1396server-id = 1 [mysqldump] bytes = 32 M [myisamchk] key_buffer_size = bytes = 256Mread_buffer = 2Mwrite_buffer = 2 M [mysqlhotcopy] interactive-timeout