Key Parameter Optimization Configuration of my. cnf (my. ini), my. cnfmy. ini

Key Parameter Optimization Configuration of my. cnf (my. ini), my. cnfmy. ini

MyISAM storage engine

MyISAM storage engine is suitable for systems with high read performance requirements due to multiple reads and writes.

Official documents: http://dev.mysql.com/doc/refman/5.6/en/myisam-storage-engine.html

Key_buffer_size, which can be set to around 30%-40% of the memory. Use show variables like '% key_buffer_size % ';

Use show global status like '% key_blocks_unused %' to check whether there is any surplus. If there are many remaining parts, you do not need to increase the key_buffer_size.
If you do not need MyISAM, we recommend that you set 16 m to 32 m.

Query_cache if the application has a large number of reads and there is no cache at the application level, it will be useful to set this option, but it should not be too large. The maintenance overhead is large, and mysql will slow down. We recommend that you increase the length from 32 MB to 512 MB.

Sort_buffer_size is used for complex queries. 8 MB to 16 Mb is recommended.

Query_cache_size caches the select query results. If a large number of identical queries exist, you can increase the value.

Bulk_insert_buffer_size used for batch insert, must be smaller than key_buffer_size

Read_rnd_buffer_size when an SQL statement has an order by statement and is used for the second query, it records the order and reads it directly from the memory.

How many threads are retained in Thread_cache_size cache. If the thread is retained, it will not be destroyed if it is disconnected. Wait for a new link. Reduces the overhead of thread creation.

Parameter official reference document: http://dev.mysql.com/doc/refman/5.6/en/optimizing-myisam.html

Innodb Storage Engine

Innodb Storage Engine 1

Number of concurrent threads: Innodb_thread_concurrency = 0 [Default]. It does not mean no concurrency, but unlimited concurrency without concurrent check. Internal Control of innodb
Value Range: 0 to 1000

Suggestion:

Number of CPUs + number of disks x 2. If there is a RAID Master/Slave, NO 2 is taken because there is a backup disk.

Innodb Storage Engine 2

The default value of Innodb_io_capacity is 200. I personally think it indicates the disk I/O throughput. the maximum number of data pages for innodb background processes to process IO operations per second

Innodb_io_capacity_max defaults to 2000 and sets IO to go online.

Source code: Search for srv_io_capacity at the innodb Storage engine layer (mainly in the srv0srv. c file)

When SSD is used, you can increase the Disk Throughput until it meets the disk I/O throughput.

Innodb Storage engine 3

Innodb_max_dirty_pages_pct ratio of dirty pages refreshed by innodb buffer: 15%-80%

Source code: Search for srv_max_buf_pool_modified_pct at the innodb Storage engine layer (mainly in the srv0srv. c file)

Innodb Storage engine 4 [important]

Innodb_flush_method (O_DSYNC, O_DIRECT)

O_DSYNC: InnoDB uses the O_SYNC mode to open and update log files, and uses the fsync () function to update data files.

O_DIRECT: InnoDB uses O_DIRECT mode to open data files and uses the fsync () function to update logs and data files.

On raid devices, to avoid data being cached by innodb_buffer and raid multiple times, the O_DIRECT mode is used. That is to say, you can directly open the data file without opening the log file.

Source code: Search for srv_unix_file_flush_method at the innodb Storage engine layer (mainly in the log0log. c and os0file. c files)

Innodb Storage engine 5 [important]

Innodb_buffer_pool_size

Innodb will follow the lru and load the data to innodb_buffer_pool_size based on the data input. If you do not need to search for data files during data operations, you can find them directly from the memory.

Generally, about 80% of the memory size is set, but the total amount of data files must be considered. Buffer_pool_size + capacity occupied by data volume + memory used by the operating system = memory size. Set as much as possible.

Source code: Search for srv_buf_pool_size at the innodb Storage engine layer (in the srv0srv. c and srv0start. c files ).

Innodb Storage Engine 6

Innodb_buffer_pool_instances must be set when multiple instances exist.

Source code: Search for srv_buf_pool_instances at the innodb Storage engine layer (mainly concentrated on the buf0buf. c file)

Innodb Storage Engine 7

Innodb_log_file_size Log File Size

Innodb_log_buffer_size log cache size

Innodb_log_buffer is written first. buffer is full or transaction committed. Data is refreshed. large transactions are frequently written. innodb_log_buffer_size is increased. The default value is 16 Mb.

Source code: Search for srv_log_buffer_size at the innodb Storage engine layer (mainly in the log0log. c file)

Innodb Storage engine 8 [important]

Innodb_file_per_table

When Innodb_file_per_table is set to 1, it is enabled, that is, all tables are set to independent tablespaces, one table is a storage data file. You must also set

Innodb_open_files (number of files opened at the same time ). Because each table corresponds to a data file, you need to set the number of files opened at the same time to ensure that multiple tables are queried, the shared tablespace cannot be migrated when you want to remove a table from another disk, because all tables use the shared tablespace.

By default, all tables are placed in the shared space. That is, OFF

Innodb Storage Engine 9

Core parameters of Innodb_flush_log_at_trx_commit:

0: writes transaction logs to the log buffer every second and refreshes them to the disk.

1: After each transaction is committed, write the content of the log buffer to the transaction log and write it to the data disk.

2: each transaction is committed. The log buffer content is written to the transaction log, but no data is flushed to the disk.

Sync_binlog

Dual-consistency mode: innodb_flush_log_at_trx_commit = 1; sync_binlog = 1; the master-slave data is consistent and no data is lost.

Official parameter description: http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html

System Parameter Optimization

NUMA (for dual instances, each instance can be placed under the node separately controlled by numa)

When the OS-layer numa is disabled, enabling numa on the bios layer will affect performance, and the QPS will decrease by about 15-30%;

When numa is disabled at the bios level, performance is not affected regardless of whether numa is enabled at the OS level.

System Optimization jemalloc

Nic optimization: RPS + RFS

Malloc

1) download the jemalloc source code package
Wget http://www.canonware.com/download/jemalloc/jemalloc-3.6.0.tar.bz2
Tar-xjf jemalloc-3.6.0.tar.bz2

2) Compile and install
Cd jemalloc-3.6.0;./configure; make & make install

3) Configure MySQL

[Mysqld_safe]
Malloc-lib = $ PATH/libjemalloc. so

4), reference documents: http://blog.chinaunix.net/uid-29957450-id-4547818.html

My. cnf configuration file reference

# The following options will be read by the MySQL client application. # Note that only client applications attached to MySQL can read this section. # If you want your MySQL application to obtain these values. # You need to specify these options when initializing the MySQL client database. [Client] port = 3306 socket =/usr/local/mysql. sock # MySQL Server [mysqld] # default storage engine INNODBdefault-storage-engine = INNODB # GROUP_CONCAT length group_concat_max_len = 99999 # port number port = 3306 # socket location socket =/usr/local/mysql /mysql. sock # pid where the file is written pid-file =/usr/local/mysql/mysqld. pid # Database File Location datadir =/home/data/mysql/datauser = mysql # SQL mode for details SQL _mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES # When the external lock (external-lo Cking), if each process needs to access the data table, # must wait for the previous process to complete the operation and unlock. Because the server often needs to wait for unlocking to access the data table, # external locking in a single server environment will degrade MySQL performance. # In many Linux distributions, skip-external-locking is used by default in the MySQL configuration file to avoid external locking. Skip-external-locking # skip DNS reverse resolution skip-name-resolve # disable the default value of TIMESTAMP type: explicit_defaults_for_timestamp # not affected by the client character set, ensure sever character set skip-character-SET-client-handshake # initial connection character set UTF8init-connect = 'set NAMES utf8' # default database character set character-SET-server = utf8 # query cache, 2, representing off, on, And demandquery_cache_type = 1 # Unit: seconds. The handshake time exceeds connect_timeout, the connection request will be rejected. connect_timeout = 20 # The number of seconds after the system does not receive the Binary Logs events from the master database. After the Slave database considers the network time-out, the Slave IO thread will reconnect to the master database. # The default value of this parameter is 3600 s. However, a long time may cause database delay or an exception in the direct connection between the master and slave databases. # Setting slave_net_timeout to a short value will cause heavy time-frequency connection when the Master node does not have data updates. Generally, it is set to 5 s slave_net_timeout = 30 # this parameter is used to configure whether to write updates from the slave server to binary logs. This option is disabled by default. # However, if the slave server B is the slave server of server A and serves as the master server of server C, you need to develop this option, # in this way, its slave server C can obtain its binary log for synchronization operation log-slave-updates = 1 # used for slave server, the io thread writes the event with the same server id to the log, conflict with the log-slave-updates option replicate-same-server-id = 0 # correct to generate a unique server_id I thought of, all IP addresses are unique, such as 10.112.87.91, remove the DoT number and add the serial number 01, 02, or 03 to the end. (The addition of the serial number 2 is a fear of a physical machine located on the bank, master-slave replication requires the server-id to identify # Use 10112879101 as the server_id. Server_id = 10112879101 # enable the binary log function. # In the replication configuration, this item must be enabled as the MASTER server # If you need to recover from your last backup based on the time point, you also need binary log-bin =/home/data/mysql/binlog/mysql-bin.log # relay-log = mysql-relay-bin # master-info-repository and enable relay-log-info-repository to enable the crash-safe binary log/slave server function (store information in the transaction table rather than in the flat file) master-info-repository = TABLErelay-log-info-repository = TABLE # Database binlog-ignore-db = mysql # No sync datab Asesbinlog-ignore-db = test # No sync databasesbinlog-ignore-db = information_schema # No sync databasesbinlog-ignore-db = cece_schema # No sync databases # Write database binlog- do-db = business_dbbinlog-do-db = user_dbbinlog-do-db = plocc_system #15 scroll clean binlogexpire-logs-days = 15max_binlog_size = 1073741824 # Bin logs size (1G) # synchronize the binlog with the hard disk after every 1000 binlog writes. sync_binlog = 1000 # specify which database to copy only data replicate-do-db = Business_dbreplicate-do-db = user_dbreplicate-do-db = plocc_system # enable Event scheduler Event Schedulerevent_scheduler = 1 # Number of connections that MySQL can save. This takes effect when the main MySQL thread receives many connection requests in a short time. # If MySQL connection data reaches max_connections, new requests will be stored in the stack to wait for a connection to release resources. # The number of stacks is back_log, if the number of connections waiting exceeds back_log, the connection resource back_log = 500 # the maximum number of connections to the entire database (User) will not be granted) max_connections = 6000 # maximum number of connections of a user max_user_connection = 3000 # maximum number of errors allowed for each client connection. If this limit is reached. # This client will be blocked by the MySQL service until "flush hosts" is executed or the service is restarted # This value will be added for invalid passwords and other connection errors. # view the "Aborted_connects" status to obtain the Global Counter max_connect_errors = 1844674407370954751 # Table descriptor cache size, which can reduce the number of times the file is opened/closed table_open_cach E = 2048 # maximum size of the request packet that the service can process and the maximum request size that the service can process (required when working with a large BLOB field) # The size of each connection. dynamically increase the size of max_allowed_packet = 64 M # In a transaction, binlog records the cache size held by the SQL status # If you often use large, multi-statement transactions, you can add this value to obtain greater performance. # All the statuses from the transaction will be buffered in the binlog buffer and then written to the binlog at one time after submission # if the transaction is larger than this value, it will be replaced by temporary files on the disk. # This buffer is created when the first update status of each connected transaction is binlog_cache_size = 1 M # maximum capacity allowed by the independent memory table. # This option is used up to prevent accidental creation of a large memory table. max_heap_table_size = 1342177280 # Sort buffer is used to process ORDER And sorting caused BY the group by queue # If the sorted data cannot be placed in the SORT buffer, # A disk-based merged category will be used # view the "Sort_merge_passes" status variable. # When sorting occurs, sort_buffer_size = 8 M is allocated by each thread # This buffer is used to optimize full Union (full JOINs union without indexes ). # Similar joins have very bad performance in most cases. # setting this value to a large value can reduce the performance impact. # use the "Select_full_join" status variable to view the total number of conjoins # When the full Union occurs, allocate join_buffer_size = 8 M in each thread # The number of threads we keep in the cache for reuse # If a client is disconnected, if the number of threads in the cache is less than thread_cache_size, # The client thread is put into the cache. # This can greatly reduce threads when you need a large number of new connections Overhead of creation # (generally, if you have a good thread model, this does not significantly improve performance .) thread_cache_size = 128 # This allows the application to give the thread system a prompt to give the desired number of threads at the same time. # This value is only valid for systems that support the thread_concurrency () function (for example, Sun Solaris ). # You can try to use [number of CPUs] * (2 .. 4) as the value of thread_concurrency = 8 # The Query Buffer is often used to buffer the SELECT results and will not directly return results during the next same query. # enabling the Query Buffer can greatly improve the server speed. If you have a large number of identical queries and rarely modify tables. # Check the "Qcache_lowmem_prunes" status variable to check whether the current value is high enough for your load. # Note: When your table changes frequently or if your query The original text is different each time. # query buffering may cause performance degradation rather than performance improvement. query_cache_size = 64 M # The result is buffered only when the value is smaller than this setting # This setting is used to protect the Query Buffer, prevent a large result set from overwriting all other query results with query_cache_limit = 2 M # the smallest word length indexed by full-text search. # You may want to reduce it if you need to search for shorter words. # note that after you modify this value, # You need to recreate your FULLTEXT index ft_min_word_len = 4 # The heap size used by the thread. this capacity is reserved for each connection. # MySQL itself usually does not require 64 KB of memory # If you use a large number of heap UDF functions # Or your operating system requires more heap for some operations, # You may need to set it to a higher level. thread_stack = 192 K # Set the default transaction isolation level. available levels: # READ-UNCOM MITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLEtransaction_isolation = READ-COMMITTED # maximum size of a temporary table inside (in memory) # If a table grows to be greater than this value, it is automatically converted to a disk-based table. # This restriction applies to a single table, not the sum. tmp_table_size = 1342177280 # binlog type -- Hybrid binlog_format = mixed # enable slow query log slow_query_log # FILE format log_output = FILE # All usage time (in seconds) more queries are considered as slow queries. # Do not use "0" here. Otherwise, all queries or even very fast query pages will be recorded (because MySQL currently has a precision of only seconds ). long_query_time = 0.5 # Slow query log location slo W_query_log_file =/usr/local/mysql/mysqld_slow.log # specify the size of the index buffer, which determines the index processing speed, especially the speed of index reading ********** * ******************** # keyword buffer size, it is generally used to Buffer Index blocks of the MyISAM table. # Do not set it to 30% larger than your available memory # because some of the memory is also used by the OS to buffer row data # Even if you do not use the MyISAM table, you also need to set up the 8-64 M memory because it will also be used by the internal temporary disk table. key_buffer_size = 32 M # buffer size used for full table scan in the MyISAM table. # allocate it to the corresponding thread when full table scan is required. read_buffer_size = 2 M # When the row is read from a sorted sequence, the row data will be read from this buffer. Read to prevent disk seek. # If you increase this value, you can improve the performance of many order by statements. # When necessary, each thread allocates read_rnd_buffer_size = 8 M # MyISAM uses a special cache similar to a tree to enable burst inserts # (these inserts are, INSERT... SELECT, INSERT... VALUES (...), (...), ..., And load data # INFILE) faster. this variable limits the number of bytes of the buffer tree in each process. # setting 0 will disable this optimization. # For optimization, do not set this value to be greater than "key_buffer_size ". # When burst inserts are detected, the buffer will be allocated. bulk_insert_buffer_size = 16 M # This buffer is allocated when MySQL needs to re-index a REPAIR, OPTIMIZE, ALTER, and load data infile to an empty table. # This is allocated in each thread. therefore, be careful when setting the token. myisam_sort_buffer_size = 128 M # maximum temporary file size (when REPAIR, alter table, or load data infile) allowed during index reconstruction by MySQL ). # If the file size is greater than this value, the index will be created through the key-value buffer (slower) myisam_max_sort_fil E_size = 1G # 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 that is not properly closed. myisam_recover ******************** * # If your MySQL service contains InnoDB support but is not intended to be used, # using this option will save memory and disk space, and accelerate some parts # skip-innodb ###### [key items] # InnoDB uses a buffer pool to store indexes and raw data, unlike MyISAM. # The larger you set, the less disk I/O you need to access the data in the table. # On an independently used database server, you can set this variable to the physical server. 80% of the memory size # Do not set too large. Otherwise, the competition in physical memory may lead to page bumps in the operating system. # note that on a 32-bit system, each process may be limited to 2-3.5 GB of user memory. # Do not set it too high. innodb_buffer_pool_size = 700 m #1G # InnoDB saves data in one or more data files as tablespaces. # If you only have a single logic driver to save your data, a single auto-increment file is enough. # In other cases. A file on each device is generally a good choice. # You can also configure InnoDB to use the raw disk partition.-please refer to the Manual for more information about innodb_data_file_path = IBdata1: 1024 M; IBdata2: 1024 M: autoextend # Set this option if you want InnoDB tablespace files to be saved in other partitions. # It is stored in MySQL datadir by default. # innodb_data_home _ Dir = # Number of IO threads used to synchronize IO operations. this value is # This value is hard-coded to 4 in Unix, But Windows disk I/O may perform better in 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 = 16 ###### [key items] # if it is set to 1, InnoDB will refresh the transaction log (fsync) to the disk after each commit, # This provides complete ACID behavior. # If you are willing to compromise transaction security and you are running a small transaction, you can set this value to 0 or 2 to reduce disk I/O #0 caused by transaction logs. It means that logs are only written to log files every second and the log files are refreshed to the disk. #2 indicates that after each commit, the log file The disk is refreshed only every second. # -------------------- # (Note: if it is a game server, we recommend that you set this value to 2; if it is an application with extremely high data security requirements, we recommend that you set it to 1; # Set it to 0 with the highest performance, however, if a fault occurs, data may be lost! # The default value 1 indicates that logs need to be written to the hard disk (flush) for each transaction commit or non-transactional command. This is time-consuming. # Especially when Battery backed up cache is used. Set to 2 is applicable to a lot of applications, especially the conversion from the MyISAM table. # It means writing data not to the hard disk but to the system cache. Logs are flushed to the hard disk every second, so you will not lose updates that exceed 1-2 seconds. # Setting it to 0 is faster, but the security is poor. Even if MySQL crashes, transaction data may be lost. While value 2 may only lose data when the entire operating system is down.) innodb_flush_log_at_trx_commit = 2 # buffer size used to buffer log data. # When the value is full, InnoDB must refresh the data to the disk. # Because the value is refreshed every second, it is unnecessary to set this value too large (or even for long transactions) innodb_log_buffer_size = 16 M # size of each log file in the log group. # You should set the total size of log files to 25% ~ of the size of your buffer pool ~ 100% # To avoid overwriting unnecessary buffer pool refresh behaviors in log files. # In any case, please note that the size of a large log file will increase the time required for restoring the process. innodb_log_file_size = 1024 M # Total number of files in the log group. # generally, 2 ~ 3 is better. innodb_log_files_in_group = 3 # location of the InnoDB log file. the default value is MySQL datadir. # You can specify it to an independent hard disk or a RAID1 volume to improve its performance # innodb_log_group_home_dir # maximum allowable proportion of dirty pages in the InnoDB buffer pool. # If the quota is reached, InnoDB will start refreshing them to prevent them from interfering with the clean data page. # This is a soft limit and is not guaranteed to be executed absolutely. innodb_max_dirty_pages_pct = 90 # How InnoDB refreshes logs. # The tablespace always uses the double write refresh method # The default value is "fdatasync", and the other is "O_DSYNC ". innodb_flush_method = O_DSYNC # Before rollback, how long should an InnoDB Transaction wait for a lock to be approved. # InnoDB in its own lock table Dynamically detects the transaction deadlock and rolls back the transaction. # If you use the lock tables command or use a transaction-safe storage engine other than InnoDB in the same transaction # A deadlock may occur and InnoDB cannot notice it. # In this case, the timeout value is very helpful for solving this problem. innodb_lock_wait_timeout = 30 [mysqldump] # Do not cache the entire result in the memory before writing it to the disk. when exporting a very large table, you need to export this item to quickmax_allowed_packet = 64 M [mysql] no-auto-rehash [myisamchk] key_buffer_size = bytes = 512Mread_buffer = 8Mwrite_buffer = 8 M [mysqlhotcopy [mysqld_safe] # add each The number of files that can be opened by the process. # warning: Make sure you have set a high enough limit for the entire system! # To open a large number of tables, set this value to "open-files-limit = 8192log-error =/usr/local/mysql/mysqld. logpid-file =/usr/local/mysql/mysqld. pid

The performance optimization of MYSQL is not just about this, but many other MYSQL performance optimization solutions can be searched on our website.