• This topic is for my.cnf Configuration to do a detailed description of the parameters
  • The sample configuration is as follows :
  • #cat my.cnf
  • # MySQL client library initialization.
  • [client]
  • port
  • = 3306
  • socket = /tmp/mysql.sock
  • character-set-server = utf8
  • # The MySQL server
  • [mysqld]
  • init_connect = 'set names utf8'// Set connection mysql Database utf8 code , In order to let mysql Database to utf8 function
  • >show variables like'character%'\G; Can query mysql Character related
  • init_connect = 'SET autocommit=0' // Turn off auto submit mode , Think that users always operate in a transactional way
  • character-set-server = utf8
  • port = 3306
  • socket = /tmp/mysql.sock
  • basedir = /opt/justone/mysqldM/mysql
  • datadir = /opt/justone/mysqldM/mysql/data
  • skip-locking // avoid MySQL External locking of , Reduce the chance of error and increase stability .
  • #skip-networking// Turn this option on to turn it off completely MySQL Of TCP/IP How to connect , If WEB The server is accessed remotely MySQL Do not turn on the database server ! Otherwise, you will not be able to connect properly !
  • skip_name_resolve// prohibit MySQL For external connections DNS analysis , Use this option to eliminate MySQL Conduct DNS Time of resolution . If this option is turned on , Then all remote host connection authorizations must use IP Address , otherwise MySQL Connection requests will not be processed properly !
  • #back_log = 384 // The number of connections that can be held in the listening queue is saved in MySQL The connection manager thread handles previous connections .
  • back_log The value of the parameter is indicated in MySQL How many requests can be stored in the stack in a short time before the response to a new request is temporarily stopped .
  • If there are many connections in a short time , You need to increase the value of this parameter , To designate the coming TCP/IP The size of the listening queue for the connection .
  • Different operating systems have their own limits on the size of this queue . Try to set back_log Higher than the limit of your operating system will be invalid .
  • The default value is 50. about Linux The system recommended setting is less than 512 The integer of . The system value is determined by ulimit-a see .
  • max_connections = 2000 // The number of customers allowed at the same time . Increasing this value increases mysqld Number of file descriptors required .
  • notes : The value is too small , Clients often have Too many connections error .
  • wait_timeout=10 // Specify the maximum connection time for a request , about 4GB Left and right memory servers can be set to 5-10.
  • max_connect_errors = 500 // The same host has the number of broken connection errors exceeding the value of this parameter , Then the host will be forbidden to connect .
  • If the host needs to be released , perform :FLUSH HOST;
  • table_open_cache = 2048 // Number of open tables for all threads
  • max_allowed_packet = 16M// The maximum size of a query statement package , The allowable size of packets used in information exchange ( Such as : The import table )
  • max_heap_table_size = 256M //HEAP Data sheet ( Memory tables ) Maximum length of ( The default setting is 16M);
  • Over this length HEAP The data table will be stored in a temporary file instead of staying in memory .
  • sort_buffer_size = 512K // The size of the buffer that can be used when querying the sort .
  • The allocated memory corresponding to this parameter is exclusive per connection , if there be 100 A connection , The actual allocated sort buffer size is 100×512K=50MB.
  • therefore , For memory in 4GB The recommended settings for the left and right servers are 6-8M.
  • join_buffer_size = 1M // The size of the buffer that can be used by the federated query operation , The allocated memory corresponding to this parameter is also exclusive for each connection .
  • thread_cache_size = 8 // The number of threads that can be reused and stored in the thread cache . Memory G*8 namely 2G Set to 16
  • Each connection to the database uses its own thread . Thread creation takes time , So if the thread doesn't need to be closed when the connection is closed , The server keeps it in its own thread cache , For the next connection to use .
  • thread_concurrency = 8 // The value of this parameter is server logic CPU Number ×2
  • bulk_insert_buffer_size = 8M // Appoint MyISAM Type tables use special tree structured caching .
  • Use the block approach (bulk) Can speed up the insertion operation ( INSERT … SELECT, INSERT …VALUES (…), (…), …, and LOAD DATA INFILE) Speed and efficiency . This parameter limits the size of the tree cache used by each thread , If set to 0 The cache acceleration function is disabled . Be careful : The cache operation corresponding to this parameter can only be sent to non empty users Insert operations in data tables ! The default value is 8MB.
  • query_cache_size = 64M // Appoint MySQL Query buffer size .
  • It can be done by MySQL The console performs the following commands to observe :
  • > SHOW VARIABLES LIKE'%query_cache%';
  • > SHOW STATUS LIKE'Qcache%';
  • Qcache_lowmem_prunes It's worth a lot , It indicates that insufficient buffer often occurs ;
  • Qcache_hits It's worth a lot , It shows that query buffer is used very frequently , If the value is small, it will affect the efficiency , So we can consider not to query buffer ;
  • Qcache_free_blocks, If the value is very large , It indicates that there are many fragments in the buffer .
  • default-storage-engine = InnoDB // The default data table type for the new data table The default setting is MyISAM
  • lower_case_table_names = 1 //MySQL Implementation is case insensitive
  • transaction_isolation = REPEATABLE-READ // Set the default transaction isolation level for all connections
  • tmp_table_size = 256M // temporary HEAP The maximum length of the data table
  • The default setting is 32M; Temporary tables over this length will be converted to MyISAM Data table and save it in a temporary file .
  • slow_query_log = 1
  • log = /opt/justone/mysqldM/mysql/logs/mysql.log
  • long_query_time = 2
  • log-slow-queries =/opt/justone/mysqldM/mysql/logs/slowquery.log
  • # Replication related settings
  • server-id = 1 // Set to master
  • log-bin=mysql-bin // Produced log With mysql-bin start
  • binlog_cache_size = 8M // by binary log Specifies that during query request processing SQL The size of the cache used by the query statement .
  • If it is frequently applied to a large number of 、 complex SQL Expression processing , The value of this parameter should be increased for performance improvement .
  • binlog_format=mixed // Log format , You can also customize .
  • InnoDB and MyISAM Type difference
  • InnoDB and MyISAM It's used by a lot of people MySQL , the two most commonly used table types , Each of these table types has its advantages and disadvantages , It depends on the application .
  • The basic difference is :MyISAM Types do not support advanced processing such as transaction processing , and InnoDB Type of support .
  • MyISAM Type tables emphasize performance , The ratio of execution InnoDB The type is faster , Transaction support is not provided , and InnoDB Provides advanced database functions such as transaction support and external keys .
  • InnoDB What to pay attention to :
  • 1.InnoDB I won't support it FULLTEXT Index of type .
  • 2.InnoDB The specific number of rows of the table is not saved in , in other words , perform select count(*) fromtable when ,InnoDB Scan the entire table to see how many rows there are , however MyISAM Simply read out the number of saved lines . Pay attention to is , When count(*) language Sentence contains where When the conditions , The operation of the two kinds of tables is the same .
  • 3. about AUTO_INCREMENT Type field ,InnoDB Must contain only the index of this field , But in MyISAM In the table , You can create a union index with other fields .
  • 4.DELETE FROMtable when ,InnoDB The table will not be recreated , It's line by line deletion .
  • 5.LOAD TABLE FROMMASTER Operation of InnoDB It doesn't work , The solution is to put InnoDB Change the watch to MyISAM surface , Import the data and change it to InnoDB surface , But for the extra InnoDB characteristic ( For example, foreign key ) The table of does not apply .
  • 6.InnoDB The row lock of the table is not absolute either , If you are executing a SQL When the sentence is MySQL Can't determine the range to scan ,InnoDB The watch also locks the whole watch , for example updatetable set num=1 where name like “?a%”
  • # MyISAM Specific options
  • key_buffer_size = 32M // Buffer size of index block , Add it to get better indexes ( For all reading and rewriting )
  • The index block is buffered and shared by all threads . If you make it too big , The system will start to change pages and really slow down .
  • The default value is 8388600(8M),MySQL The main engine has 2GB Memory , May be set as 402649088(400MB).
  • Be careful : If the parameter value is set too large, the overall efficiency of the server will be reduced !
  • read_buffer_size = 256K // The size of the buffer that can be used for read query operations , The allocated memory corresponding to this parameter is also exclusive for each connection .
  • read_rnd_buffer_size = 256K // In a certain order ( Such as ORDERBY Clause ) Output query results ( Default 256K)
  • Speed up reading data after sorting operation , Speed up reading classified lines .
  • myisam_sort_buffer_size = 128M //myisam Engine sort_buffer_size
  • myisam_max_sort_file_size = 10G // It's similar to
  • myisam_recover // Auto check and repair doesn't shut down properly MyISAM surface
  • #skip-innodb // Get rid of innodb Support
  • #skip-bdb // Get rid of bdb Transactional tables support
  • # INNODB Specific options ***
  • innodb_additional_mem_pool_size = 16M//InnoDB Memory pool size used to store data dictionaries and other internal data structures .
  • The more tables in an application, the more memory should be allocated , If innodb If you run out of memory, you will ask for the system memory .
  • And write the warning log , according to MySQL manual , about 2G Memory machine , Recommended value is 20M.
  • The default value is 1M. Usually not too big , As long as it's enough , It has to do with the complexity of the table structure .
  • #innodb_buffer_pool_size = 6G // Specify the size of memory to buffer data and indexes .
  • For the individual MySQL database server , The maximum value can be set to physical memory 80%.
  • according to MySQL manual , about 2G Memory machine , Recommended value is 1G(50%)
  • innodb_buffer_pool_size = 512M
  • innodb_data_file_path = ibdata1:10M:autoextend// To hold InnoDB Is the table space of the data table :
  • More than one document may be involved ; The maximum length of each table space file must be in bytes (B)、 Megabytes (MB) Or gigabytes (GB) Given in units ;
  • The names of the table space files must be separated by semicolons ; The last table space file can also carry a autoextend Property and a maximum length (max:n).
  • for example ,ibdata1:1G;ibdata2:1G:autoextend:max:2G It means :
  • Table space files ibdata1 The maximum length of is 1GB,ibdata2 And the maximum length of it is also 1G, But allow it to expand to 2GB.
  • except Outside the file name , You can also define the table space with the setting name of the hard disk partition , At this point, you must add... To the maximum initial length of the table space newraw Keywords do suffixes , Add... To the maximum extended length of the table space raw Keywords do suffixes ( for example /dev/hdb1:20Gnewraw or /dev/hdb1:20Graw);MySQL 4.0 The default settings for version 1 and later are ibdata1:10M:autoextend.
  • innodb_file_io_threads = 4 //IO operation ( Hard disk write operations ) The maximum number of threads ( The default setting is 4).
  • innodb_file_per_table = 1// Create a table space file for each new table instead of saving all tables in a central table space
  • If there are not many tables in the system , And there's no big watch , Use this parameter to make the , Maintenance is relatively independent .
  • innodb_thread_concurrency = 16//InnoDB The maximum number of threads that the driver can use at the same time ( The default setting is 8).
  • innodb_flush_log_at_trx_commit = 1 //InnoDB How to log .
  • If set to 1, When each transaction is committed ,MySQL Will write the transaction log to disk .
  • If set to 0 perhaps 2, The log is written to disk approximately once per second .
  • Actual test findings , This value has a great influence on the speed of inserting data
  • Set to 2 Insert when 10000 Records only need 2 second , Set to 0 When it comes to 1 second , And set to 1 When you need 229 second .
  • It is recommended to merge the insert operations into one transaction as much as possible , This can greatly speed up .
  • In the presence of the risk of losing the most recent part of the transaction , You can set this value to 0.
  • innodb_log_buffer_size = 8M // The size of the log cache
  • The default setting is in the case of medium write load and short transactions , Generally, it can meet the performance requirements of the server .
  • If the update operation peak or load is large, you should increase this value .8-16M that will do .
  • innodb_log_file_size = 256M // The size of each log file in the log group is very important in the case of high write load, especially in the case of large data sets .
  • The higher the value, the higher the performance , But it takes longer to recover . The default is 5M.Javaeye recommend innodb_log_file_size = 64M
  • It should be noted that After modification, we need to STOP The service then removes the original log ib_logfile0 and ib_logfile1, And then start the service .
  • Overall performance analysis report > show engine innodbstatus\G;
  • innodb_log_files_in_group = 3 // Number of log files in log group , Recommended 3
  • innodb_max_dirty_pages_pct = 90 // Percentage of maximum dirty pages
  • When the percentage of dirty pages in the system exceeds this value ,INNODB A write operation is performed to write the updated data in the page to the disk file .
  • innodb_flush_method = O_DIRECT//InnoDB Log file synchronization method ( Only applicable to UNIX/Linux System ).
  • O_DIRECT Skipping the file system of the operating system DiskCache, Give Way MySQL Direct read write disk .
  • innodb_lock_wait_timeout = 120 // Transaction get resource timeout , Default 50s
  • If a transaction is waiting n second (s) We haven't got the resources we need yet , Just use ROLLBACK Order to abort the transaction .
  • This setting is not available for discovery and processing InnoDB The deadlock condition identified by the data table driver is of great significance .
  • But it can't automatically monitor the deadlock caused by table lock .
  • [mysqldump]
  • quick // Do not buffer queries , Export directly to stdout; Use mysql_use_result() Do it .
  • Don't cache the entire result in memory before writing it to disk . This is required when exporting very large tables
  • max_allowed_packet = 16M
  • [mysql]
  • no-auto-rehash // Do not auto fill command , Set up auto-rehash For automatic completion . Default TAB A filling
  • #safe-updates // Only key values are allowed UPDATE and DELETE
  • [myisamchk] //MyISAM Table maintenance is a very practical tool .
  • have access to myisamchk Utility to get information about database tables or check 、 Repair 、 Optimize them .
  • myisamchk apply MyISAM surface ( Corresponding .MYI and .MYD A list of documents ).
  • key_buffer_size = 512M
  • sort_buffer_size = 512M
  • read_buffer = 8M
  • write_buffer = 8M
  • [mysqlhotcopy]
  • interactive-timeout // Set the maximum amount of time for a data transfer to the default 28800 second
  • [mysqld_safe]
  • open-files-limit = 8192 // Number of openable files per process .

MySql More articles on performance tuning strategies

  1. MySQL performance tuning —— Lock mechanism and lock optimization analysis

    Concurrent access for multithreading , Any database has its own locking mechanism , Its advantages and disadvantages are directly related to the consistency and integrity of data and the high concurrent processing performance of database system . Locking mechanism has become one of the core technologies of various databases . Different database storage engines have different locking mechanisms , Ben ...

  2. MySQL Performance optimization summary ___ This article is 《MySQL Performance tuning and architecture design 》 Reading notes !

    One .MySQL The main application scenarios of 1.Web Website system 2. Logging system 3. Data warehouse system 4. Embedded system Two .MySQL Architecture diagram : 3、 ... and .MySQL Storage engine overview 1)MyISAM Storage engine MyISAM Storage engine ...

  3. MySQL Storage engine for performance tuning

    original text :http://bbs.landingbj.com/t-0-246222-1.html        http://bbs.landingbj.com/t-0-245851-1.html MySQ ...

  4. MySQL Performance tuning 10 A way - mysql Database column

    Abstract : https://edu.aliyun.com/a/29036?spm=5176.11182482.related_article.1.hbeZbF Abstract : MYSQL It should be the most popular WEB ...

  5. MySql( 11、 ... and ):MySQL performance tuning —— Common storage engine optimization

    One . Preface MySQL Provide a very rich variety of storage engines for you to choose , It's good to have multiple choices , But the knowledge that we need to understand will also increase a lot . This chapter will introduce the two most commonly used storage engines for targeted optimization suggestions . Two .MyISAM Storage ...

  6. MySQL Performance tuning and architecture design —— The first 18 Chapter High availability design MySQL monitor

    The first 18 Chapter High availability design  MySQL monitor Preface : A highly available and scalable design MySQL Database cluster , If there is not a monitoring system that is fine enough and powerful enough , It may also make the previous efforts in high availability design fall short . One ...

  7. MySQL Performance tuning and architecture design —— The first 17 Chapter Idea and scheme of high availability design

    The first 17 Chapter Idea and scheme of high availability design Preface : Database system is the core part of an application system , To ensure the overall availability of the system , The database system can't have any problems . For an enterprise class system , The availability of database system is very important . database ...

  8. MySQL Performance tuning and architecture design —— The first 16 Chapter MySQL Cluster

    The first 16 Chapter MySQL Cluster Preface : MySQL Cluster It's based on NDB Cluster Storage engine complete distributed database system . It's not just high availability , And it can automatically segment data , Redundant data, etc ...

  9. MySQL Performance tuning and architecture design —— The first 15 Chapter Extensibility design Cache And Search The use of

    The first 15 Chapter Extensibility design Cache And Search The use of Preface : The extensible architecture scheme analyzed in the previous section , Basically, it's all around the database itself , Whether this will affect our thinking in seeking the road of expansibility “ Imprisonment ”, Can't be more ...

Random recommendation

  1. Java SE Basics : Common keywords

    Java SE Basics : Common keywords Common keyword list

  2. Lucene 4.X Inverted index principle and implementation : (3) Term Dictionary and Index file (FST Detailed analysis )

    Let's look at the most complex part , Namely Term Dictionary and Term Index file ,Term Dictionary The suffix of the file is tim,Term Index The suffix of the file is tip, The format is shown in the figure . Ter ...

  3. LeetCode Binary Search Tree Iterator

    The link to the original question is here :https://leetcode.com/problems/binary-search-tree-iterator/ Implement an iterator over a bina ...

  4. CSS 3D rotate hover After setting transform It's relative to the normal position

    <!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8&quo ...

  5. ado.net Basics ( One )

    // C# The basis of operating a database 1 Reference two namespace using data:using date.sqlclient;2 Create a connection to the database : sqlconnection a = new sqlconn ...

  6. Oracle 11g service

    Successful installation Oracle 11g after , share 7 A service , These seven services mean : 1. Oracle ORCL VSS Writer Service: Oracle Volume mapping copy write service ,VSS(Volume S ...

  7. cmd The command line sets the environment variables

    http://blog.sciencenet.cn/blog-51026-566742.html 1. View all currently available environment variables : Input set You can view it . 2. Look at an environment variable : Input “set Variable name ...

  8. SELECT TOP column FROM table [ORDER BY column [DESC]]

    If you want to go back to a subset of the rows in a table , Only a specific number of records need to be returned , And no matter how many lines are eligible . To return the top value , There are two options : Specify a fixed number of rows , Or specify a percentage of the total number of rows .SQL Server No analysis of the data , common ...

  9. string matching &mdash;KMP Expand KMP Manacher

    kuangbin String portal --http://acm.hust.edu.cn/vjudge/contest/view.action?cid=70325#overview Algorithm template : KMP: ; ...

  10. linux shell Execute commands concurrently

    Usually we are in linux Last eleven shell Batch execution of commands , In general use for perhaps while Cycle operation , But there is a problem ,for perhaps while It's essentially serial , Does not , If a command takes a long time to execute , ...