MySQL 5.5.49 large memory optimization profile optimization details

1. Configuration file description

my-small.cnf
my-medium.cnf
my-large.cnf
my-huge.cnf
my-innodb-heavy-4G.cnf

2. The explanation my - innodb - heavy - 4 G. cnf
3. Profile optimization
Note: environment, CentO5. 5 x86_64 + MySQL 5.5.32 related software download: http: / / yunpan cn/QtaCuLHLRKzRq

1. Configuration file description

Mysql - 5.5.49 is Mysql5. The final version 1 in 5 series, is also the last one of the configuration file version, why say so, 5.6 bloggers know that used in mysql5. 6 has not provide configuration file selection, only one of the default configuration file, ok, we said today is 5.5.49 this version, at enmity with you said 5.6, let's say 1, the specific mysql5. 5.49, provides several optional configuration file,
my-small.cnf
my-medium.cnf
my-large.cnf
my-huge.cnf
my-innodb-heavy-4G.cnf

Now let's take a look at ^_^...
1.my-small.cnf

[root@mysql support-files]# vim my-small.cnf
# Example MySQL config file for small systems.
# This is for a system with little memory (<= 64M) where MySQL is only used 
# from time to time and it's important that the mysqld daemon 
# doesn't use much resources.

my-small.cnf is a small database system with memory less than or equal to 64M, so as not to take up more system resources!
2.my-medium.cnf

[root@mysql support-files]# vim my-medium.cnf
# Example MySQL config file for medium systems.
# This is for a system with little memory (32M - 64M) where MySQL plays 
# an important part, or systems up to 128M where MySQL is used together with 
# other programs (such as a web server)

This configuration file is used for mid-sized data systems. It has 128M in it!
3.my-large.cnf

[root@mysql support-files]# vim my-large.cnf
# Example MySQL config file for large systems. 
# This is for a large system with memory = 512M where the system runs mainly 
# MySQL.

This configuration file is used for large database system, memory around 512M!
4.my-huge.cnf

[root@mysql support-files]# vim my-huge.cnf
# Example MySQL config file for very large systems. 
# This is for a large system with memory of 1G-2G where the system runs mainly 
# MySQL.

This configuration file is used by a huge database system. It contains around 1G-2G!
5.my-innodb-heavy-4G.cnf

[root@mysql support-files]# vim my-innodb-heavy-4G.cnf
#BEGIN CONFIG INFO 
#DESCR: 4GB RAM, InnoDB only, ACID, few connections, heavy queries 
#TYPE: SYSTEM 
#END CONFIG INFO
# This is a MySQL example config file for systems with 4GB of memory 
# running mostly MySQL using InnoDB only tables and performing complex 
# queries with few connections.

The main purpose of this configuration file is to support 4G memory, InnoDB engine, ACID transactions and other features used!
ACID, the abbreviation of the four essential elements for the correct execution of database transactions. Includes: atomicity (Atomicity), one-induced (Consistency), isolation (Isolation), persistence (Durability)!
6. Summary
From the above we can out, basic is by memory size to choose mysql configuration file, it have the bloggers will say, now the server is prone to 32 G 64 G memory, memory, or even larger memory, you are the largest configuration file only support 4 G memory is a bit small, confirm that there will be such problem, from mysql5. 6, for greater play mysql performance, has gone to in addition to the configuration file options, only one of the default configuration file, there was only 1 some basic configuration, All Settings administrators can be set according to their own actual needs, well said so much, we say 1, in the enterprise with the most my-innodb-heavy-4G.cnf configuration file!
2. The explanation my - innodb - heavy - 4 G. cnf
1. Be specific
Note: the following is my - innodb - heavy - 4 G. cnf default configuration I didn't do any changes, we have detailed below 1 said!

[root@mysql support-files]# vim my-innodb-heavy-4G.cnf
#BEGIN CONFIG INFO 
#DESCR: 4GB RAM, InnoDB only, ACID, few connections, heavy queries 
#TYPE: SYSTEM 
#END CONFIG INFO
# 
# This is a MySQL example config file for systems with 4GB of memory 
# running mostly MySQL using InnoDB only tables and performing complex 
# queries with few connections. 
# 
# MySQL programs look for option files in a set of 
# locations which depend on the deployment platform. 
# You can copy this option file to one of those 
# locations. For information about these locations, see: 
# http://dev.mysql.com/doc/mysql/en/option-files.html
# 
# In this file, you can use all long options that a program supports. 
# If you want to know which options a program supports, run the program 
# with the "--help" option. 
# 
# More detailed information about the individual options can also be 
# found in the manual. 
#
# 
# The following options will be read by MySQL client applications. 
# Note that only client applications shipped by MySQL are guaranteed 
# to read this section. If you want your own MySQL client program to 
# honor these values, you need to specify it as an option during the 
# MySQL client library initialization. 
#
#  The following options will be MySQL The client application reads ,  Note that only MySQL This content is guaranteed to be read by the accompanying client application , If you want your own MySQL The application gets these values , Need to be in MySQL These options are specified when the client library is initialized 
[client] 
#password  = [your_password] #mysql Client connection mysql At the time of the password  
port    = 3306 #mysql The default port when the client connects  
socket    = /tmp/mysql.sock # with mysql Used by the server for local communication socket The file path 
# *** Application-specific options follow here ***
# 
# The MySQL server 
# 
[mysqld]
# generic configuration options #1 General configuration options  
port    = 3306 #mysql The default port on which the server listens  
socket    = /tmp/mysql.sock #socket Local communication file path 
# back_log is the number of connections the operating system can keep in 
# the listen queue, before the MySQL connection manager thread has 
# processed them. If you have a very high connection rate and experience 
# "connection refused" errors, you might need to increase this value. 
# Check your OS documentation for the maximum value of this parameter. 
# Attempting to set back_log higher than your operating system limit 
# will have no effect.
# back_log  Is the number of connections that the operating system can hold in a listening queue , 
#  The queue is saved in MySQL The connection manager thread processes the previous connection . 
#  If you have a very high connection rate and you have a" connection refused "An error , 
#  You should increase the value here . 
#  Check the number of files your operating system can open to get the maximum value of this variable . 
#  If you have back_log Set to a value higher than your operating system limit , There will be no effect 
back_log = 50
# Don't listen on a TCP/IP port at all. This can be a security 
# enhancement, if all processes that need to connect to mysqld run 
# on the same host. All interaction with mysqld must be made via Unix 
# sockets or named pipes. 
# Note that using this option without enabling named pipes on Windows 
# (via the "enable-named-pipe" option) will render mysqld useless!
#  Not in TCP/IP Listen on port . 
#  If all processes are in the same 1 The server is connected to the local mysqld, 
#  This setting would be a way to enhance security  
#  all mysqld The connections are all through Unix sockets  Or a named pipe . 
#  Note that in windows If the named pipe option is not turned on and this is only used  
# ( through   " enable-named-pipe "   options )  Will lead to mysql The service doesn't do anything !
#skip-networking # The default is not enabled 
# The maximum amount of concurrent sessions the MySQL server will 
# allow. One of these connections will be reserved for a user with 
# SUPER privileges to allow the administrator to login even if the 
# connection limit has been reached.
# MySQL  An upper limit on the number of simultaneous sessions allowed by the server  
#  Among them 1 The connection will be SUPER Permissions remain logged in as administrator . 
#  Even though we've reached the limit of connections . 
max_connections = 100
# Maximum amount of errors allowed per host. If this limit is reached, 
# the host will be blocked from connecting to the MySQL server until 
# "FLUSH HOSTS" has been run or the server was restarted. Invalid 
# passwords and other errors during the connect phase result in 
# increasing this value. See the "Aborted_connects" status variable for 
# global counter.
#  The maximum number of errors allowed per client connection , If this limit is reached . 
#  The client will be MySQL Service block until executed" FLUSH HOSTS "   Or a service restart  
#  Illegal passwords and other errors during linking can increase this value . 
#  To view   " Aborted_connects "   State to get the global counter . 
max_connect_errors = 10
# The number of open tables for all threads. Increasing this value 
# increases the number of file descriptors that mysqld requires. 
# Therefore you have to make sure to set the amount of open files 
# allowed to at least 4096 in the variable "open-files-limit" in 
# section [mysqld_safe]
#  The number of tables open by all threads . 
#  This value increases when you increase it mysqld Number of file descriptors required  
#  So you need to make sure that [mysqld_safe] In the   " open-files-limit "   Variable to set the number of open files allowed at least 2048
table_open_cache = 2048
# Enable external file level locking. Enabled file locking will have a 
# negative impact on performance, so only use it in case you have 
# multiple database instances running on the same files (note some 
# restrictions still apply!) or if you use other software relying on 
# locking MyISAM tables on file level.
#  Allows external file-level locking .  Opening a file lock can negatively impact performance  
#  Use this option only if you are running multiple instances of the database on the same file ( Note that there are still other constraints !) 
#  Or you use something else at the file level 1 Some software relies on locking MyISAM table 
#external-locking # The default is not enabled 
# The maximum size of a query packet the server can handle as well as 
# maximum query size server can process (Important when working with 
# large BLOBs). enlarged dynamically, for each connection.
#  The maximum size of request packets that the service can handle and the maximum size of requests that the service can handle ( When with the big one BLOB field 1 It is quite necessary to start work ) 
#  Each connection is independent of its size . Dynamic size increase  
max_allowed_packet = 16M
# The size of the cache to hold the SQL statements for the binary log 
# during a transaction. If you often use big, multi-statement 
# transactions you can increase this value to get more performance. All 
# statements from transactions are buffered in the binary log cache and 
# are being written to the binary log at once after the COMMIT. If the 
# transaction is larger than this value, temporary file on disk is used 
# instead. This buffer is allocated per connection on first update 
# statement in transaction
#  in 1 A transaction binlog In order to record SQL Is held by the state cache The size of the  
#  If you use the big one a lot , Multiple declared transactions , You can increase this value for greater performance . 
#  All state from the transaction will be buffered in binlog In the buffer and then after the submission 1 The secondary writes to binlog In the  
#  If the transaction is larger than this value ,  Temporary files on disk will be used instead . 
#  This buffer is in the transaction of each connection 1 Created on the next update status  
binlog_cache_size = 1M
# Maximum allowed size for a single HEAP (in memory) table. This option 
# is a protection against the accidential creation of a very large HEAP 
# table which could otherwise use up all memory resources.
#  The maximum capacity allowed for a separate memory table . 
#  This option is to prevent accidental creation 1 A very large memory table causes all memory resources to be exhausted forever . 
max_heap_table_size = 64M
# Size of the buffer used for doing full table scans. 
# Allocated per thread, if a full scan is needed.
#MySql Read in the buffer size. Requests for a sequential scan of the table are allocated 1 The read buffer, MySql Points for it # with 1 Segment memory buffer. read_buffer_size Variable control here 1 Size of buffer. If the sequential scan of the table is requested very frequently, # If you think frequent scans are too slow, you can improve performance by increasing the value of this variable and the size of the memory buffer.  
read_buffer_size = 2M
# When reading rows in sorted order after a sort, the rows are read 
# through this buffer to avoid disk seeks. You can improve ORDER BY 
# performance a lot, if set this to a high value. 
# Allocated per thread, when needed.
# is MySql Size of the random read buffer. When the rows are read in any order ( For example, by sort order ) To assign 1 There are random read caches. When you do a sort query, MySql It will scan first 1 Traverse the buffer to avoid disk searches and improve query speed if needed # To sort large amounts of data, increase this value appropriately. but MySql This buffer space is allocated for each customer connection, so try to set this value appropriately to avoid excessive memory overhead.  
read_rnd_buffer_size = 16M
# Sort buffer is used to perform sorts for some ORDER BY and GROUP BY 
# queries. If sorted data does not fit into the sort buffer, a disk 
# based merge sort is used instead - See the "Sort_merge_passes" 
# status variable. Allocated per thread if sort is needed.
#  Sort buffers are used to handle things like this ORDER BY As well as GROUP BY The sort caused by the queue  
#  If the sorted data cannot be placed in the sort buffer , 
# 1 An alternative disk-based merge classification will be used  
#  To view   " Sort_merge_passes "   State variables . 
#  Assigned by each thread when the sort occurs  
sort_buffer_size = 8M
# This buffer is used for the optimization of full JOINs (JOINs without 
# indexes). Such JOINs are very bad for performance in most cases 
# anyway, but setting this variable to a large value reduces the 
# performance impact. See the "Select_full_join" status variable for a 
# count of full JOINs. Allocated per thread if full join is found
#  This buffer is used to optimize full union (full JOINs  Union without index ). 
#  Similar unions have very poor performance in the vast majority of cases , 
#  However, setting this value high can mitigate the performance impact . 
#  through   " Select_full_join "   The state variable looks at the number of full unions  
#  When total union occurs , Allocate in each thread  
join_buffer_size = 8M
# How many threads we should keep in a cache for reuse. When a client 
# disconnects, the client's threads are put in the cache if there aren't 
# more than thread_cache_size threads from before. This greatly reduces 
# the amount of thread creations needed if you have a lot of new 
# connections. (Normally this doesn't give a notable performance 
# improvement if you have a good thread implementation.)
#  We are in cache How many threads are reserved for reuse in  
#  when 1 After the client disconnects , if cache There are fewer threads in the thread_cache_size, 
#  The client thread is put in cache In the . 
#  This can greatly reduce the overhead of thread creation when you need a lot of new connections  
# (1 In general, if you have a good threading model , There is no significant performance improvement .) 
thread_cache_size = 8
# This permits the application to give the threads system a hint for the 
# desired number of threads that should be run at the same time. This 
# value only makes sense on systems that support the thread_concurrency() 
# function call (Sun Solaris, for example). 
# You should try [number of CPUs]*(2..4) for thread_concurrency
#  This allows applications to give threading systems 1 A hint in the same 1 Time gives the number of threads that are eager to be run . 
#  This value is for support only  thread_concurrency()  The system of functions makes sense (  For example, Sun Solaris). 
#  You can try it  [CPU The number of ]*(2..4)  As a thread_concurrency The value of the  
thread_concurrency = 8
# Query cache is used to cache SELECT results and later return them 
# without actual executing the same query once again. Having the query 
# cache enabled may result in significant speed improvements, if your 
# have a lot of identical queries and rarely changing tables. See the 
# "Qcache_lowmem_prunes" status variable to check if the current value 
# is high enough for your load. 
# Note: In case your tables change very often or if your queries are 
# textually different every time, the query cache may result in a 
# slowdown instead of a performance improvement.
#  Query buffers are often used for buffering  SELECT  The results are also below 1 The next time the same query is executed, the result is no longer returned directly . 
#  Turning on the query buffer can greatly speed up the server ,  If you have a large number of identical queries and rarely modify the table . 
#  To view   " Qcache_lowmem_prunes "   State variables to check whether the current value is high enough for your load . 
#  Pay attention to :  In cases where your table changes frequently or if your query text is different each time , 
#  Query buffering may cause performance degradation rather than performance improvement . 
query_cache_size = 64M
# Only cache result sets that are smaller than this limit. This is to 
# protect the query cache of a very large result set overwriting all 
# other query results.
#  Only results less than this set point will be buffered  
#  This setting is used to protect the query buffer , To prevent 1 All other query results are overridden by a maximum result set of six .
query_cache_limit = 2M
# Minimum word length to be indexed by the full text search index. 
# You might wish to decrease it if you need to search for shorter words. 
# Note that you need to rebuild your FULLTEXT index, after you have 
# modified this value.
#  The smallest word length to be 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 rebuild yours  FULLTEXT  The index  
ft_min_word_len = 4
# If your system supports the memlock() function call, you might want to 
# enable this option while running MySQL to keep it locked in memory and 
# to avoid potential swapping out in case of high memory pressure. Good 
# for performance.
#  If your system supports  memlock()  function , You may want to enable this option to enable running mysql In times of high memory stress , The data is kept locked in memory and protected from possible loss swapping out 
#  This option is good for performance  
#memlock
# Table type which is used by default when creating new tables, if not 
# specified differently during the CREATE TABLE statement.
#  The default table type to use when creating a new table , 
#  If there is no special execution table type in the creation , This value will be used  
default-storage-engine = MYISAM
# Thread stack size to use. This amount of memory is always reserved at 
# connection time. MySQL itself usually needs no more than 64K of 
# memory, while if you use your own stack hungry UDF functions or your 
# OS requires more stack for some operations, you might need to set this 
# to a higher value.
#  Heap size used by the thread .  Memory of this capacity is reserved for each connection . 
# MySQL  It often does not need to exceed itself 64K The memory of  
#  If you use your own you need a lot of heaps UDF function  
#  Or maybe your operating system needs more heap for certain operations , 
#  You might want to set it higher 1 point . 
thread_stack = 192K
# Set the default transaction isolation level. Levels available are:
#  Set the default transaction isolation level . The levels available are as follows : 
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE 
transaction_isolation = REPEATABLE-READ
# Maximum size for internal (in-memory) temporary tables. If a table 
# grows larger than this value, it is automatically converted to disk 
# based table This limitation is for a single table. There can be many 
# of them.
#  internal ( In the memory ) Maximum size of temporary table  
#  if 1 The table grows to a value greater than this , Will be automatically converted to a disk-based table . 
#  This restriction is for a single table , Not the sum . 
tmp_table_size = 64M
# Enable binary logging. This is required for acting as a MASTER in a 
# replication configuration. You also need the binary log if you need 
# the ability to do point in time recovery from your latest backup.
#  Open the 2 The base log function . 
#  The replication (replication) In the configuration , As a MASTER This must be enabled on the master server  
#  If you need to do a point-based recovery from your last backup , You need it too 2 Log into the system . 
log-bin=mysql-bin
# binary logging format - mixed recommended
# Set a record 2 The format of the base log, there is 3 Various formats, based on statements  statement ,   Based on the line  row ,   Mixed way  mixed 
binlog_format=mixed
# If you're using replication with chained slaves (A->B->C), you need to 
# enable this option on server B. It enables logging of updates done by 
# the slave thread into the slave's binary log.
#  If you are using a chained slave server structure copy mode  (A->B->C), 
#  You need to be on the server B Open this item on . 
#  This option turns on the log of updates that have been redone on the slave thread , 
#  And writes it to the slave server's 2 Log into the system . 
#log_slave_updates
# Enable the full query log. Every query (even ones with incorrect 
# syntax) that the server receives will be logged. This is useful for 
# debugging, it is usually disabled in production use.
#  Open the query log .  All queries received by the server  ( Even for 1 A query with incorrect syntax ) 
#  It's going to be recorded .  This is useful for debugging ,  This is often turned off in production environments . 
#log # This is not enabled by default and affects server performance 
# Print warnings to the error log file. If you have any problem with 
# MySQL you should enable logging of warnings and examine the error log 
# for possible explanations.
#  Prints the warning to an error log file .  If you're interested in MySQL Any questions  
#  You should turn on the warning log And carefully review the error log , Find out the probable cause . 
#log_warnings
# Log slow queries. Slow queries are queries which take more than the 
# amount of time defined in "long_query_time" or which do not use 
# indexes well, if log_short_format is not enabled. It is normally good idea 
# to have this turned on if you frequently add new queries to the 
# system.
#  Log slow queries .  Slow queries are those that consume more than   " long_query_time "   Defines more time for queries . 
#  if  log_long_format  To be opened , Queries that do not use indexes are also logged . 
#  If you are constantly adding new queries to an existing system . 1 Generally speaking, this is 1 A good idea  
slow_query_log
# All queries taking more than this amount of time (in seconds) will be 
# trated as slow. Do not use "1" as a value here, as this will result in 
# even very fast queries being logged from time to time (as MySQL 
# currently measures time with second accuracy only). 
#  All took longer than this time ( In seconds ) More queries are considered slow queries . 
#  Don't use it here." 1 " ,  Otherwise it will result in all queries , Even very fast query pages are logged ( Due to the MySQL  The accuracy of time is currently only on the order of seconds ).
long_query_time = 2
# *** Replication related settings # ***  The master-slave copy the relevant Settings 
# Unique server identification number between 1 and 2^32-1. This value 
# is required for both master and slave hosts. It defaults to 1 if 
# "master-host" is not set, but will MySQL will not function as a master 
# if it is omitted.
#  only 1 Of the service identification number , Value is  1  to  2^32-1 between . 
#  This value is in master and slave All need to be set . 
#  if   " master-host "   Not set , The default for 1,  But if you ignore this option ,MySQL Not as master To take effect . 
server-id = 1
# Replication Slave (comment out master section to use this) # Copy of the Slave ( To get rid of master Segment comments to make it effective ) 
# 
# To configure this host as a replication slave, you can choose between 
# two methods : # To configure this host as replicated slave The server , You can do it two ways : 
# 
# 1) Use the CHANGE MASTER TO command (fully described in our manual) - 
#  the syntax is: # use  CHANGE MASTER TO  The command  ( It's fully described in our handbook ) - 
#  Syntax is as follows :
# 
#  CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>, 
#  MASTER_USER=<user>, MASTER_PASSWORD=<password> ; 
# 
#  where you replace <host>, <user>, <password> by quoted strings and 
#  <port> by the master's port number (3306 by default). 
#   You need to replace it  , ,  Fields surrounded by Angle brackets and their use master Replace the port number of  ( The default 3306). 
#  Example:  case  
# 
#  CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, 
#  MASTER_USER='joe', MASTER_PASSWORD='secret'; 
# 
# OR  or  
# 
# 2) Set the variables below. However, in case you choose this method, then 
#  start replication for the first time (even unsuccessfully, for example 
#  if you mistyped the password in master-password and the slave fails to 
#  connect), the slave will create a master.info file, and any later 
#  changes in this file to the variable values below will be ignored and 
#  overridden by the content of the master.info file, unless you shutdown 
#  the slave server, delete master.info and restart the slaver server. 
#  For that reason, you may want to leave the lines below untouched 
#  (commented) and instead use CHANGE MASTER TO (see above) 
#
# Set the following variables .  No matter how ,  In the case that you choose this method ,  Then the first 1 Secondary start replication ( Even if it doesn't work out , 
#  For example, if you enter the wrong password in master-password Field and slave Unable to connect ), 
# slave Will create 1 a  master.info  file , And any subsequent changes to parameters contained in the file are ignored  
#  And by the  master.info  The contents of the file are overwritten ,  Unless you shut it down slave service ,  delete  master.info  And restart slave  service . 
#  For this reason , You may not want to touch it 1 The configuration of the ( Commented out )  And the use of  CHANGE MASTER TO ( See the above )  To take the place of 
# required unique id between 2 and 2^32 - 1 
# (and different from the master) 
# defaults to 2 if master-host is set 
# but will not function as a slave if omitted
#  All that is needed 1id No. In  2  and  2^32  �  1 between  
# ( and master different ) 
#  if master-host Has been set up . The default value is 2 
#  But if you omit , It doesn't take effect  
#server-id = 2 
# 
# The replication master for this slave  �  required
#  In a replication structure master  �   Must be  
#master-host = <hostname> 
# 
# The username the slave will use for authentication when connecting 
# to the master  �  required
#  When the connection to the master when slave The user name used to authenticate   �   Must be 
#master-user = <username> 
# 
# The password the slave will authenticate with when connecting to
# the master  �  required
#  When the connection to the master when slave The password used for authentication   �   Must be 
#master-password = <password> 
# 
# The port the master is listening on. 
# optional - defaults to 3306
# master Listening port . 
#  optional   �   The default is 3306 
#master-port = <port>
# Make the slave read-only. Only users with the SUPER privilege and the 
# replication slave thread will be able to modify data on it. You can 
# use this to ensure that no applications will accidently modify data on 
# the slave instead of the master
#  make slave read-only . User Owned Only SUPER Permissions and above slave Threads can modify data . 
#  You can use this to ensure that no application changes unexpectedly slave Rather than master The data on the 
#read_only
#*** MyISAM Specific options
#*** MyISAM  options  
# Size of the Key Buffer, used to cache index blocks for MyISAM tables. 
# Do not set it larger than 30% of your available memory, as some memory 
# is also required by the OS to cache rows. Even if you're not using 
# MyISAM tables, you should still set it to 8-64M as it will also be 
# used for internal temporary disk tables.
#  Keyword buffer size , 1 As a buffer MyISAM The index block of the table . 
#  Do not set it larger than your available memory 30%, 
#  because 1 Part of the memory is also OS Used to buffer row data  
#  Even if you don't use it MyISAM  Table case ,  You also need to still set it  8-64M  Memory because it will also be used by internal temporary disk tables . 
key_buffer_size = 32M
# MyISAM uses special tree-like cache to make bulk inserts (that is, 
# INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA 
# INFILE) faster. This variable limits the size of the cache tree in 
# bytes per thread. Setting it to 0 will disable this optimisation. Do 
# not set it larger than "key_buffer_size" for optimal performance. 
# This buffer is allocated when a bulk insert is detected.
# MyISAM  Use special tree-like ones cache To make a burst insert  
# ( These inserts are ,INSERT  ...  SELECT, INSERT  ...  VALUES ( ... ), ( ... ),  ... ,  As well as  LOAD DATA 
# INFILE)  faster .  This variable limits the number of bytes of the buffer tree per process . 
#  Set to  0  This optimization is turned off . 
#  Do not set this value greater than for optimization   " key_buffer_size " . 
#  This buffer is allocated when a burst insert is detected . 
bulk_insert_buffer_size = 64M
# This buffer is allocated when MySQL needs to rebuild the index in 
# REPAIR, OPTIMIZE, ALTER table statements as well as in LOAD DATA INFILE 
# into an empty table. It is allocated per thread so be careful with 
# large settings.
#  The buffer when MySQL Need to be in  REPAIR, OPTIMIZE, ALTER  As well as  LOAD DATA INFILE  to 1 Is allocated when an index is rebuilt in an empty table . 
#  This is allocated in each thread . So you need to be careful when setting large values . 
myisam_sort_buffer_size = 128M
# The maximum size of the temporary file MySQL is allowed to use while 
# recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE. 
# If the file-size would be bigger than this, the index will be created 
# through the key cache (which is slower).
# MySQL The maximum size of a temporary file that will be allowed when the index is rebuilt  ( when  REPAIR, ALTER TABLE  or  LOAD DATA INFILE). 
#  If the file size is larger than this value , Indexes are created using key-value buffers ( More slowly ) 
myisam_max_sort_file_size = 10G
# If a table has more than one index, MyISAM can use more than one 
# thread to repair them by sorting in parallel. This makes sense if you 
# have multiple CPUs and plenty of memory.
#  if 1 A table has more than one 1 index , MyISAM  Can be used by parallel sort over 1 Thread to fix them . 
#  This is good for having more than one CPU As well as large memory cases of users , is 1 It's a good choice . 
myisam_repair_threads = 1
# Automatically check and repair not properly closed MyISAM tables.
#  Automatically checks and fixes not properly closed  MyISAM  table . 
myisam_recover
             
# *** INNODB Specific options ***
# *** INNODB  options  ***
# Use this option if you have a MySQL server with InnoDB support enabled 
# but you do not plan to use it. This will save memory and disk space 
# and speed up some things.
#  If you have a MySQL Services include InnoDB Supported but not intended to be used , 
#  Using this option saves memory and disk space , And accelerate something  
#skip-innodb
# Additional memory pool that is used by InnoDB to store metadata 
# information. If InnoDB requires more memory for this purpose it will 
# start to allocate it from the OS. As this is fast enough on most 
# recent operating systems, you normally do not need to change this 
# value. SHOW INNODB STATUS will display the current amount used.
#  The additional memory pool is InnoDB Used to store  metadata  information  
#  if InnoDB More memory is required for this purpose , It's going to start from OS I'm going to apply memory here . 
#  Because this operation is fast enough on most modern operating systems ,  you 1 There is no need to modify this value . 
# SHOW INNODB STATUS  The command displays the number used first . 
innodb_additional_mem_pool_size = 16M
# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and 
# row data. The bigger you set this the less disk I/O is needed to 
# access data in tables. On a dedicated database server you may set this 
# parameter up to 80% of the machine physical memory size. Do not set it 
# too large, though, because competition of the physical memory may 
# cause paging in the operating system. Note that on 32bit systems you 
# might be limited to 2-3.5G of user level memory per process, so do not 
# set it too high. 
# InnoDB use 1 Buffer pools to hold indexes and raw data ,  Don't like  MyISAM. 
#  The bigger you set it here , The disk that you need to access the data in the table I/O The less . 
#  in 1 It is used on a separate database server , You can set this variable to the server physical memory size 80% 
#  Don't make it too big , Otherwise, , Paging bumps in the operating system can occur due to physical memory competition . 
#  Note that in 32 On a bit system each of your processes may be limited to  2-3.5G  User level memory limits , 
# 所以不要设置 the 太高.
innodb_buffer_pool_size = 2G
# InnoDB stores data in one or more data files forming the tablespace. 
# If you have a single logical drive for your data, a single 
# autoextending file would be good enough. In other cases, a single file 
# per device is often a good choice. You can configure InnoDB to use raw 
# disk partitions as well - please refer to the manual for more info 
# about this.
# InnoDB  Save the data in 1 One or more data files become tablespaces . 
#  If you only have a single logical driver to hold your data ,1 A single auto-increment file is good enough . 
#  In other cases . Each device 1 A file 1 It's always a good choice . 
#  You can also configure it InnoDB To use a bare disk partition   �   Please refer to the manual for more information  
innodb_data_file_path = ibdata1:10M:autoextend
# Set this option if you would like the InnoDB tablespace files to be 
# stored in another location. By default this is the MySQL datadir.
#  Set this option if you wish InnoDB The tablespace files are saved in other partitions . 
#  By default, save in MySQL the datadir In the . 
#innodb_data_home_dir = <directory>
# Number of IO threads to use for async IO operations. This value is 
# hardcoded to 8 on Unix, but on Windows disk I/O may benefit from a 
# larger number.
#  Used to synchronize IO Operation of the IO Number of threads . This value is 
#  This value is in Unix Is hardcoded as 8, But in the Windows disk I/O May be in 1 They perform better at larger numbers . 
innodb_write_io_threads = 8 
innodb_read_io_threads = 8
# If you run into InnoDB tablespace corruption, setting this to a nonzero 
# value will likely help you to dump your tables. Start from value 1 and 
# increase it until you're able to dump the table successfully.
#  If you find that InnoDB Tablespace corruption ,  Set this value to 1 A non-zero value may help you export your table . 
#  from 1 Start and increase this value until you can successfully export the table . 
#innodb_force_recovery=1
# Number of threads allowed inside the InnoDB kernel. The optimal value 
# depends highly on the application, hardware as well as the OS 
# scheduler properties. A too high value may lead to thread thrashing.
#  in InnoDb Number of threads allowed in the core . 
#  The optimal value depends on the application , Hardware and operating system scheduling . 
#  Too high a value can cause mutually exclusive bumping of threads . 
innodb_thread_concurrency = 16
# If set to 1, InnoDB will flush (fsync) the transaction logs to the 
# disk at each commit, which offers full ACID behavior. If you are 
# willing to compromise this safety, and you are running small 
# transactions, you may set this to 0 or 2 to reduce disk I/O to the 
# logs. Value 0 means that the log is only written to the log file and 
# the log file flushed to disk approximately once per second. Value 2 
# means the log is written to the log file at each commit, but the log 
# file is only flushed to disk approximately once per second.
#  If you set it to 1 ,InnoDB It will refresh after each submission (fsync) Transaction logging to disk , 
#  This provides the complete ACID behavior . 
#  If you are willing to compromise on transaction security ,  And you're running 1 A small food ,  You can set this value to 0 or 2 To reduce the number of disks caused by transaction logging I/O 
# 0 The log is written to the log file approximately every second and the log file is flushed to disk . 
# 2 Writes the log file on behalf of the log after each commit , But log files are flushed to disk only about every second . 
innodb_flush_log_at_trx_commit = 1
# Speed up InnoDB shutdown. This will disable InnoDB to do a full purge 
# and insert buffer merge on shutdown. It may increase shutdown time a 
# lot, but InnoDB will have to do it on the next startup instead.
#  To speed up InnoDB The closing of the .  This will prevent InnoDB Do full purge and insert buffer merge on close . 
#  This can significantly increase shutdown time ,  But instead InnoDB You might do this the next time you boot . 
#innodb_fast_shutdown
# The size of the buffer InnoDB uses for buffering log data. As soon as 
# it is full, InnoDB will have to flush it to disk. As it is flushed 
# once per second anyway, it does not make sense to have it very large 
# (even with long transactions).
#  The size of the buffer used to buffer log data . 
#  When this value is nearly full , InnoDB You will have to flush the data to disk . 
#  Because it's basically refreshing every second 1 time , So there is no need to set this value too high ( Even for long transactions ) 
innodb_log_buffer_size = 8M
# Size of each log file in a log group. You should set the combined size 
# of log files to about 25%-100% of your buffer pool size to avoid 
# unneeded buffer pool flush activity on log file overwrite. However, 
# note that a larger logfile size will increase the time needed for the 
# recovery process.
#  The size of each log file in the log group . 
#  You should set the total log file size to the size of your buffer pool 25%~100% 
#  To avoid overwriting the log file with unnecessary buffer pool refresh behavior . 
#  No matter how ,  Please note that 1 A large log file size increases the time required for the recovery process . 
innodb_log_file_size = 256M
# Total number of files in the log group. A value of 2-3 is usually good 
# enough.
#  The total number of files in the log group . 
#  Generally speaking 2~3 It's better . 
innodb_log_files_in_group = 3
# Location of the InnoDB log files. Default is the MySQL datadir. You 
# may wish to point it to a dedicated hard drive or a RAID1 volume for 
# improved performance
# InnoDB The location of the log file .  The default is MySQL the datadir. 
#  You can specify it to 1 On a separate hard drive or 1 a RAID1 Roll up to improve its performance  
#innodb_log_group_home_dir
# Maximum allowed percentage of dirty pages in the InnoDB buffer pool. 
# If it is reached, InnoDB will start flushing them out agressively to 
# not run out of clean pages at all. This is a soft limit, not 
# guaranteed to be held.
#  in InnoDB The percentage of the maximum permissible dirty pages in the buffer pool . 
#  If the limit is reached , InnoDB Start refreshing them to prevent them from getting in the way of clean data pages . 
#  This is a 1 A soft limit , Absolute enforcement is not guaranteed . 
innodb_max_dirty_pages_pct = 90
# The flush method InnoDB will use for Log. The tablespace always uses 
# doublewrite flush logic. The default value is "fdatasync", another 
# option is "O_DSYNC".
# InnoDB The method used to flush the log . 
#  A table space always uses the dual write flush method  
#  The default value is   " fdatasync " ,  On the other 1 One is   " O_DSYNC " . 
#innodb_flush_method=O_DSYNC
# How long an InnoDB transaction should wait for a lock to be granted 
# before being rolled back. InnoDB automatically detects transaction 
# deadlocks in its own lock table and rolls back the transaction. If you 
# use the LOCK TABLES command, or other transaction-safe storage engines 
# than InnoDB in the same transaction, then a deadlock may arise which 
# InnoDB cannot notice. In cases like this the timeout is useful to 
# resolve the situation.
#  Before being rolled back ,1 a InnoDB The transaction should wait 1 How long a lock is approved . 
# InnoDB Automatically detects transaction deadlocks in its own lock table and rolls back transactions . 
#  If you use  LOCK TABLES  instruction ,  Or in the same transaction except InnoDB Other transaction-safe storage engines  
#  then 1 A deadlock may occur while InnoDB Fail to notice . 
#  In this case this timeout Values can be very helpful in solving such problems . 
innodb_lock_wait_timeout = 120
[mysqldump] 
# Do not buffer the whole result set in memory before writing it to 
# file. Required for dumping very large tables
#  Do not cache the entire result in memory before writing it to disk .  This is required when exporting very large tables  
quick
max_allowed_packet = 16M
[mysql] 
no-auto-rehash
# Only allow UPDATEs and DELETEs that use keys.
#  Only the use of key values is allowed  UPDATEs  and  DELETEs . 
#safe-updates
[myisamchk] 
key_buffer_size = 512M 
sort_buffer_size = 512M 
read_buffer = 8M 
write_buffer = 8M
[mysqlhotcopy] 
interactive-timeout
[mysqld_safe] 
# Increase the amount of open files allowed per process. Warning: Make 
# sure you have set the global system limit high enough! The high value 
# is required for a large number of opened tables
#  Increase the number of open files per process . 
#  warning :  Make sure you set the system-wide limit high enough ! 
#  To open a large number of tables, you need to make this value large  
open-files-limit = 8192

3. Profile optimization (based on the actual situation)
, in this paper I am my - innodb - heavy - 4 G. cnf default all options are illustrated, below I was optimized according to the actual situation of our company!
1. Running environment of the server
Hardware server: Dell R710, Dual XE E5620 CPU, 16G memory, 6*500G hard disk
Operating system: CentOS5.5 X86_64 system
Mysql version: MySQL 5.5.32
Suitable for: day IP 100-200W, day PV 200-500W sites
2. The specific optimization configuration is as follows

[client] 
port    = 3306 
socket    = /tmp/mysql.sock
default-character-set = utf8 # Sets the character encoding of the client 
[mysqld]
# generic configuration options 
port    = 3306 
socket    = /tmp/mysql.sock
#*** char set ***
character-set-server = utf8 # Sets the character encoding on the server side 
            
#*** network ***
back_log = 512
#skip-networking # It's not on by default 
max_connections = 3000
max_connect_errors = 30
table_open_cache = 4096
#external-locking # It's not on by default 
max_allowed_packet = 32M
max_heap_table_size = 128M
            
# *** global cache ***
read_buffer_size = 8M
read_rnd_buffer_size = 64M
sort_buffer_size = 16M
join_buffer_size = 16M
            
# *** thread ***
thread_cache_size = 16
thread_concurrency = 8
thread_stack = 512K
            
# *** query cache ***
query_cache_size = 128M
query_cache_limit = 4M
            
# *** index ***
ft_min_word_len = 8
            
#memlock # It's not on by default 
default-storage-engine = INNODB
transaction_isolation = REPEATABLE-READ
            
# *** tmp table ***
tmp_table_size = 64M
            
# *** bin log ***
log-bin=mysql-bin
binlog_cache_size = 4M
binlog_format=mixed
#log_slave_updates # It's not on by default 
#log # This is not enabled by default. This is the query log. This will affect server performance 
log_warnings # Open the warning log 
            
# *** slow query log ***
slow_query_log
long_query_time = 10
# *** Replication related settings
server-id = 1
#server-id = 2 
#master-host = <hostname> 
#master-user = <username> 
#master-password = <password> 
#master-port = <port>
#read_only
#*** MyISAM Specific options
key_buffer_size = 128M
bulk_insert_buffer_size = 256M
myisam_sort_buffer_size = 256M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
            
# *** INNODB Specific options ***
#skip-innodb # It's not on by default 
innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size = 6G # Note that in 32 On a bit system each of your processes may be limited to  2-3.5G  User level memory limits ,  So don't set it too high .
innodb_data_file_path = ibdata1:10M:autoextend
#innodb_data_home_dir = <directory>
innodb_write_io_threads = 8 
innodb_read_io_threads = 8
#innodb_force_recovery=1
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
# Description: innodb_flush_log_at_trx_commit = 2  If it is a game server, it is recommended that this value be set to 2 ; If it is an application with high data security requirements, the recommended setting is 1 ; Set to 0 Highest performance, but there is a risk of data loss if something goes wrong! The default value 1 "Means every 1 Subtransaction commits or out-of-transaction instructions require the log to be written ( flush ) Hard disk, which is time consuming. In particular, use a battery-powered cache ( Battery backed up cache ). set 2 For many applications, especially from MyISAM The table is not written to the disk but to the system cache. The logs will still be logged every second flush Go to the hard drive, so you 1 Like will not lose more than 1-2 Updates in seconds. set 0 Will be faster 1 Dot, but the security aspect is relatively poor, even if MySQL A hang can also lose transaction data. The value of 2 Data loss is only possible when the entire operating system is down. 
#innodb_fast_shutdown
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
#innodb_log_group_home_dir
innodb_max_dirty_pages_pct = 90
#innodb_flush_method=O_DSYNC
innodb_lock_wait_timeout = 120
[mysqldump] 
quick
max_allowed_packet = 32M
[mysql] 
no-auto-rehash
[myisamchk] 
key_buffer_size = 2048M 
sort_buffer_size = 2048M 
read_buffer = 32M 
write_buffer = 32M
[mysqlhotcopy] 
interactive-timeout
[mysqld_safe] 
open-files-limit = 10240

3. Summary
MySQL configuration file optimization is based on the actual needs of the online environment optimization, can not be based on the optimization, writing this blog is to blog friends 1 some reference!
4. Common commands for MySQL status check

mysql> show status; # Display status information  
mysql> show variables; # Display system variables  
mysql> show engines; # View all engines 
mysql> show engine innodb status; # According to InnoDB The state of the storage engine 

This article is from "Share your knowledge..." blog

After this site this site test, such efficiency is very good, directly brought can be used.