Mysql performance optimization (my. cnf), mysqlmy. cnf

Mysql performance optimization (my. cnf), mysqlmy. cnf

Introduction:

Mysql Parameter Optimization

I. Mysql source code compilation Parameters

shell > yum -y install gcc gcc-c++ make cmake ncurses-devel zlib-devel bisonshell > cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \-DMYSQL_DATADIR=/usr/local/mysql/data \-DTMPDIR=/usr/local/mysql/data \-DMYSQL_UNIX_ADDR=/usr/local/mysql/data/mysql.sock \-DSYSCONFDIR=/etc/my.cnf \-DWITH_MYISAM_STORAGE_ENGINE=1 \-DWITH_INNOBASE_STORAGE_ENGINE=1 \-DWITH_FEDERATED_STORAGE_ENGINE=1 \-DDEFAULT_CHARSET=utf8 \-DEXTRA_CHARSETS=all \-DDEFAULT_COLLATION=utf8_general_ci

#-DCMAKE_INSTALL_PREFIX =/usr/local/mysql specifies the Mysql installation path
#-DMYSQL_DATADIR =/usr/local/mysql/data specify the Mysql data DIRECTORY
#-DTMPDIR =/usr/local/mysql/data specify the Mysql temporary directory
#-DMYSQL_UNIX_ADDR specify Mysql socket
#-DSYSCONFDIR =/etc/my. cnf specifies the Mysql configuration file
#-DWITH_MYISAM_STORAGE_ENGINE = 1 install the MyISAM storage engine
#-DWITH_INNOBASE_STORAGE_ENGINE = 1 install the Innodb Storage Engine
#-DWITH_FEDERATED_STORAGE_ENGINE = 1 install the Federated storage engine
#-DDEFAULT_CHARSET = utf8 specify the default character set as utf8
#-DEXTRA_CHARSETS = all install the extended Character Set
#-DDEFAULT_COLLATION = utf8_general_ci default sorting rule utf8_general_ci

Cmake parameter: http://dev.mysql.com/doc/refman/5.6/en/source-configuration-options.html

Bytes ---------------------------------------------------------------------------------------------

Ii. my. cnf configuration file

# Data blocks retrieved from data files cached by global_buffers in the memory can greatly improve the performance of data query and update ## formula: bytes + bytes + cached + key_buffer_size + query_cache_size # per_thread_buffers thread exclusive memory size # calculation formula (read_buffer_size + bytes + sort_buffer_size + thread_stack + buffers) * max_connections # Note: global_buffers + per_thread_buffers cannot be greater than the actual thing Memory Management. If the concurrency is large, memory overflow and system crashes! [Client] port = 3306 socket =/usr/local/mysql/data/mysql. sock # define client connection information, port number, and socket storage location [mysqld] port = 3306 socket =/usr/local/mysql/data/mysql. sockbasedir =/usr/local/mysqldatadir =/usr/local/mysql/datatemdir =/usr/local/mysql/data # basic Mysql information, port Number, socket, installation directory, data storage directory, temporary directory # skip-name-resolve # prohibit Mysql from performing DNS resolution on external connections to speed up the connection. After it is enabled, all remote connection hosts can only use the IP address skip_external_locking # Avoid Mysql External Locking, reduce error probability, enhance stability local-infile = 0 # prohibit SQL from reading local files character-set-server = utf8 # default character set utf8default-storage-engine = innodb # default storage engine # general_log = on ## enable log query, generally, if this option is disabled, the query log is detailed, which increases the disk IO overhead and affects performance # general_log_file =/usr/local/mysql/data/mysql. log # query the log storage location and File Name log-error =/usr/local/mysql/data/error. log # Error log location and file name # slow_query_log = on # enable the slow query day Log. After enabling, the SQL statement with the execution time exceeding the value of long_query_time is recorded (generally, it can be temporarily enabled) # long_query_time = 2 # define how many seconds the execution time has exceeded to slow queries, the default value is 10 s # slow_query_log_file =/usr/local/mysql/data/slow. log # define the location where slow query logs are stored # server-id = 1 # unique id of Mysql Server, used for master-slave synchronization (enabled when the master-slave node is enabled) log-bin = mysql-bin # enable binlog (Binary), mainly used for Incremental backup and master-slave synchronization binlog_format = mixed # Mysql binlog log format, statement, ROW and Mixed (Mixed Mode) binlog_cache_size = 2 M # binary log buffer size, this parameter is for each Session Separately allocated. When a thread starts a transaction, Mysql will allocate a binlog cache for this Session. When this transaction is committed, data in binlog cache is written into the binlog file # Use show status like 'binlog _ cache % '; to view the number of times binlog cache is used and the number of times the disk is used sync_binlog = 0 # this parameter is very important for the Mysql system, not only affects the performance loss caused by binlog on Mysql, it also affects the data integrity in Mysql. # When a table transaction is committed with a value of 0, Mysql will not refresh the information in binlog_cache to the disk using Disk Synchronization commands such as fsync, and Filesystem will decide when to synchronize the data, or the disk is synchronized after the cache is full. # After the table is submitted for n times, Mysql will execute a Disk Synchronization command such as fsync to forcibly write data in binlog_cache to the disk. # By default, this parameter is set to 0, that is, no mandatory disk refresh command is performed. This parameter has the best performance but has the highest risk. When the system crashes, all binlog information in binlog_cache will be lost. # When it is set to 1, it is the safest but the biggest loss of performance. When the system crashes, at most one unfinished transaction in binlog_cache is lost, which has no substantial impact on the actual data. Expire_logs_days = 30 # retain 30 days of binlog, when the system restarts, The binlog back_log = 500 # Number of Mysql connection request queues is deleted when the size of the flush logs or binlog files reaches the upper limit. When there are too many client connection requests at a certain time point, the maximum number of connections that Mysql Server stores when it cannot establish a connection is 65535 (the OS-level network listening queue limit needs to be adjusted when it increases) max_connections = 1000 # the maximum number of connections of Mysql, it directly affects the concurrent processing capability of Mysql applications (500 ~ 1000 is a suitable value. Note that each established connection will occupy a certain amount of memory space until the connection is closed) wait_timeout = 100interactive_timeout = 100 # number of seconds waiting for activity before the server closes the non-exchange connection. The default value is 28800 seconds (Note: In my. modify this parameter in cnf with interactive_timeout (timeout interval), otherwise it will not take effect) # (the time for Mysql to wait for release after processing a connection. If the concurrency is high, the default setting causes the maximum connection to be full and the "too many connections" ERROR occurs.) # (if this value is low, for example, 5, ERROR 2006 (HY000) may occur) mySQL server has gone away "error. This error may be caused by an excessively small value in max_allowed_packet) # real case max_connect _ Errors = 1000 # maximum number of connection failures, which has little to do with performance and is mainly related to security. (when this limit is reached, it will be blocked from connecting to the database unconditionally. The default value is 100) connect_timeout = 20 # connection Timeout: 20 seconds max_allowed_packet = 16 M # maximum number of messages transmitted over the network. The default value is 4 M, it must be set to an integer multiple of 1024 table_open_cache = 2000 # The number of opened file descriptor caches to prevent the system from wasting resources due to frequent opening or closing of the descriptor (performance impact, default: 2000) read_buffer_size = 512 K # size of the Mysql read buffer. A read buffer is allocated for requests that perform sequential scans on the table. Mysql allocates a buffer to the table. The default value is 128 kb, this parameter is allocated per Thread) read_rnd_buffer_size = 512 K # large Mysql random Query Buffer Small. When a row is read in any order, a random read buffer is allocated. For example, during sorting query, Mysql first scans the buffer to avoid disk search and increase the query speed (256 kb by default, which is also allocated to each thread) sort_buffer_size = 512 K # buffer used for data sorting in the system. If the sorting in the system is large and the memory is sufficient and the concurrency is low, this value can be appropriately increased (256 kb by default, which assigns an independent buffer for each thread) join_buffer_size = 512 K # buffer used when join is set to ALL, index, rang, or index_merge (256 K by default, each Thread creates its own independent buffer) thread_stack = 256 K # parameter table showing the stack size of each Thread thread_cache_size = 64 # Number of connection threads in the Thread Cache pool (the threads in this pool are not created when the service is started, but with the creation and use of the connection thread, Gradually store the used threads in it. When this value is reached, the connection thread will no longer be cached.) # formula for calculating the cache hit rate: Thread_Cache_Hit = (Connections-Thread_created) /Connections * 100% # after the system runs for a period of time, the Thread Cache hit rate should be kept above 90%. explicit_defaults_for_timestamp = 1 ## if this parameter is not enabled, error_log has warning information query_cache_type = 1 # Whether to enable query_cache. 0 is not used. (To disable query_cache, set query_cache_size and query_cache_limit to 0 at the same time) query_cache_size = 32 M # query buffer size, which is obtained directly from this buffer when the queried table is changed. Will be invalid (it is recommended to disable this buffer in environments with frequent writes, updates, and high concurrency) query_cache_limit = 1 M # buffer size ft_min_word_len = 1 # use full text index minimum length transaction_isolation = REPEATABLE-READ # transaction isolation level, to effectively ensure the correctness of concurrent reads (Repeatables Read by default is Repeatables Read) # Innodb has four isolation levels: Read Uncommitted (Uncommitted Read) read Committed, Repeatable Read, and Serializable) tmp_table_size = 32 M # temporary table size key_buffer_size = 32 M # index used to cache the MyISAM storage engine (8 MB by default, if Innodb Storage is used Engine, this value is set to 64 M or smaller) # calculation formula: key_reads/key_read_requests * 100% is less than 0.1% # Innodb Storage engine parameters innodb_file_per_table = 0 # disable exclusive tablespace and use the shared tablespace innodb_buffer_pool_size = 256 M # core Innodb Storage engine parameters, used to cache indexes and data of Innodb tables (128 MB by default, Innodb Storage engine is used separately and it is recommended to set it to 70%-80% of physical memory for a single Mysql Service) # You can use show status like 'innodb _ buffer_pool _ % '; to obtain the real-time status information of the innodb buffer pool # Total pages of Innodb_buffer_pool_pages_total (all in the innodb Storage engine) The minimum unit of data storage is page. The default value of each page is 16 KB) # Innodb_buffer_pool_pages_free idle pages # Innodb_buffer_pool_pages_data pages with data # bytes total read requests # Innodb_buffer_pool_reads reads data from the physical disk, that is: in the buffer pool, the # Innodb_buffer_pool_wait_free wait_free generated due to insufficient buffer space # Number of pre-reads produced by random reads # Innodb_buffer_pool_read_ahead_seq records generated during continuous reads preread count ## Innodb_buffer_pool_size usage = Bytes/bytes * 100% # Innodb_buffer_pool_read hit rate = (bytes-innodb_buffer_pool_reads)/bytes * 100% innodb_use_sys_malloc = 1 # Use the built-in memory distributor, replace innodb_additional_mem_pool_size parameter innodb_data_file_path = ibdata1: 512 M: autoextend # specify a 512 M, extensible ibdata1 data file inno Db_read_io_threads = 4innodb_write_io_threads = 4 # Innodb uses background threads to process I/O requests on the data page. The default value is 4innodb_thread_concurrency = 0 # Innodb thread concurrency. 0 is unlimited, the default value is 0innodb_flush_log_at_trx_commit = 2 # Innodb Transaction log refresh mode. 0 indicates that every second, log thread writes data in log buffer to the file and notifies the file system to perform the flush operation for file synchronization, in extreme cases, data will be lost for one second #1. log thread is triggered at the end of each transaction to write data in log buffer to a file and notify the file system to synchronize files, data is the safest and will not lose any committed data #2. After each transaction ends, log thread writes data to the transaction log, The file write operation of the file system is called and is not synchronized to the physical disk, because the file system has a cache mechanism, different file systems have different cache refresh mechanisms # It is the most secure when set to 1, but the performance is also the worst. The value 0 indicates that synchronization is performed once per second, and the performance is relatively high. The best performance is set to 2, but the most data is lost after the fault (OS and host hardware, power supply is safe enough to choose from, or a small amount of data loss is acceptable ). Innodb_log_buffer_size = 8 M # buffer used by transaction logs. When Innodb writes transaction logs, information is first written to Innodb Log Buffer to improve the I/O performance of Log writing. When Innodb_flush_log_trx_commit parameter or the Log Buffer is full, then synchronize the logs to the disk. # The default value is 8 MB, which is generally set to 16 ~ 64 mb. You can run the show status like 'innodb _ log % 'command to view the innodb_buffer_pool_dump_at_shutdown = 1 ## dump hot data to the local disk when closing the database. Innodb_buffer_pool_dump_now = 1 # manually dump hot data to the local disk. Innodb_buffer_pool_load_at_startup = 1 # Load hot data to the memory at startup. Innodb_buffer_pool_load_now = 1 # manually load hot data to the memory. # The above four parameters will quickly warm up the Buffer_pool buffer pool. when the machine is restarted normally, hot data will still be stored in the memory, so as to avoid the crash of the machine due to an instant full number of connections. [Mysqldump] quickmax_allowed_packet = 4 M # When you use the mysqldump tool to back up a database, an error is returned when a table is too large. You need to increase the value to a value greater than the table size)