MySQL 5.5.x my.cnf parameter configuration optimization detailed _mysql

Have been heard MySQL5.5 performance is very NB, so the recent plan to test, convenient time to upgrade the bbs.kaoyan.com to this version of the database. Today I just saw a summary of MY.CNF optimization, although it has not been my own practice test, but from the content of the article has been written in detail (of course, in fact, the following article a lot of places just translated my.cnf original configuration file description, hehe), so specially reproduced a collection, Everyone in the MySQL server to optimize the time can be used as a reference, and according to the actual situation of some of these parameters to adjust. (Special note: Some parameters in the following text are in fact not applicable to mysql5.5, do not know whether the original author has been tested, such as log-slow-queries should be written slow-query-log, and Log_long_ Format this simply does not support the direct, I would like to reorganize a point of the parameters of the argument bar

The following is a detailed interpretation of MY.CNF by the original author:

PS: This profile is for Dell R710, dual Xeon E5620, 16G memory hardware configuration. CentOS 5.6 64-bit system, MySQL 5.5.x stable version. Applies to the day IP 50-100W,PV 100-300w site, mainly uses the InnoDB storage engine. For other application environments, set the optimization according to the actual situation.

# The following options will be read by the MySQL client application.
# Note Only the client application included with MySQL guarantees that this content can be read.
# If you want to get these values from your own MySQL application.

# These options need to be specified when the MySQL client library is initialized. # [Client] #password = [Your_password] port = @MYSQL_TCP_PORT @ socket = @MYSQL_UNIX_ADDR @ * * * Apply CUSTOM Options * * * * * * MYSQL dress Service end # [MYSQLD] # general configuration Options port = @MYSQL_TCP_PORT @ socket = @MYSQL_UNIX_ADDR @ # Back_log is the number of connections the operating system can hold in the listening queue, # queues are saved in MYSQ
 L The Connection Manager thread handles the previous connection. # If you have a very high connection rate and there is a ' connection refused ' error, # You should add the value here.
 # Check your operating system documentation to get the maximum value for this variable.
 # If you set the Back_log to a higher value than your operating system, there will be no effect Back_log = 300 # does not listen on TCP/IP ports.
 # If all processes are connected to the local mysqld on the same server, # This setting will be the enhanced security method # All MYSQLD connections are made through UNIX sockets or named pipes.
 # Note that if you do not open the Named pipe option in Windows and only use this item # (through the "enable-named-pipe" option), the MySQL service will have no effect!
 #skip-networking # The maximum number of simultaneous sessions allowed by the MySQL service # One of the connections will be reserved by super privileges as an administrator.
# even if the number of connections has reached the upper limit.
 Max_connections = 3000 # The maximum number of error allowed per client connection, if this limit is reached.
 # This client will be blocked by the MySQL service until the "FLUSH HOSTS" or service restart # illegal password and other errors in the link increase this value.
# View the "aborted_connects" status to get the global counter.
 Max_connect_errors = 30 # Number of tables opened by all threads. # Adding this value increases theNumber of file descriptors required for Mysqld # so you need to make sure that the number of open files in the [Mysqld_safe] open-files-limit variable setting allows at least 4096 Table_cache = 4096 # to allow external file-level locks.
 Opening file locks negatively affects performance # so use this option only if you are running multiple database instances on the same file (note that there are still other constraints!) # or you use other software dependencies at the file level to lock the maximum size of the request package that the MyISAM table #external-locking # Service can handle and the maximum request size the service can handle (when working with large BLOB fields is necessary)
 The individual size of each connection. Dynamic increase max_allowed_packet = 32M # in a transaction binlog in order to record the cache size held by the SQL State # If you often use large, multiple-declaration transactions, you can add this value to achieve greater performance.
 # All engaged states will be buffered in the Binlog buffer and then written to the Binlog once committed. # If transactions are larger than this value, temporary files on disk are used instead.
 # This buffer is created when each connected transaction is first updated state binlog_cache_size = 4M # The maximum allowable capacity of a separate memory table. # This option to prevent accidental creation of an oversized memory table causes all memory resources to be exhausted. Max_heap_table_size = 128M # sort buffers are used to handle sort orders by and group by queue # If the sorted data cannot be placed in the sort buffer, # A disk-based merge classification used for substitution is used # view ' sort
 _merge_passes the state variable.
 # allocated by each thread when the sort occurs sort_buffer_size = 16M # This buffer is used to optimize the total union (full joins union without indexes).
 # Similar unions have very poor performance in most cases, # but setting this value can mitigate the impact of performance. # View the number of total unions through the ' Select_full_join ' state variable # When a full union occurs, allocate join_buffer_size = 16M in each thread # We keep the number of threads in cache for reuse # When a client disconnects, if Cach The threads in e are less than thread_cache_size, # The client thread is placed in the cache. # This can greatly reduce the overhead of thread creation when you need a large number of new connections # (generally, this will not have a noticeable performance boost if you have a good threading model.) Thread_cache_size = 16 #
 This allows the application to give the thread system a hint at the same time to give the number of threads that are eager to be run.
 # This value is only meaningful for systems that support the thread_concurrency () function (for example, Sun Solaris). 
 # You can try to use [CPU quantity]* (2..4) as thread_concurrency value thread_concurrency = 8 # query buffering is often used to buffer the results of a SELECT and to no longer perform direct return results at the next same query.
 # Open query buffering can greatly improve server speed, if you have a large number of the same query and rarely modify the table.
 # Check the "qcache_lowmem_prunes" state variable to see if the current value is high enough for your load.
# Note: When 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 = 128M # Only results that are less than this setting are buffered # This setting protects the query buffer against a large result set that overwrites all other query results.
 Query_cache_limit = 4M # is the smallest word length retrieved for full-text indexing. # 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 your fulltext index Ft_min_word_len = 8 # If your system supports the Memlock () function, you may want to open this option to allow running MySQL to be in memory when the memory is highly tense.

Keep locked and prevent possible swapping out # This option is good for performance #memlock # When you create a new table as the default table type, # This value will be used when creating representations that do not specifically perform table types Default_table_type = MYISAM # The heap size used by the thread.
 Memory for this capacity is reserved for each connection. # MySQL itself often does not require more than 64K of memory # If you use your own UDF function that requires a lot of heaps, or your operating system needs more heaps for some operations, # You may need to set it up a bit higher. Thread_stack = 512K # Sets the default transaction isolation level. The available levels are as follows: # read-uncommitted, read-committed, Repeatable-read, SERIALIZABLE transactio
 N_isolation = repeatable-read # internal (in-memory) temporary table maximum size # If a table grows larger than this, it will automatically be converted to a disk-based table.
# This limit is for a single table, not a sum.
 Tmp_table_size = 128M # turns on binary logging.
# In a replication (replication) configuration, you must open this key as Master Master # If you need to do a point-in-time restore from your last backup, you also need a binary log.
 Log-bin=mysql-bin # If you are using a chained copy mode from the server structure (A->B->C), # You need to open this on server B.
 # This option opens the log of the update that was redo from the thread and writes it to the binary log from the server. #log_slave_updates # Opens the full query log. All queries received by the server (even for a query with a bad syntax) will be recorded.
 This is useful for debugging, which is often turned off in a production environment. #log # Prints the warning to the error log file.
 If you have any questions about MySQL # You should turn on the warning log and review the error log carefully to find out the probable cause. #log_warnings # Record slow queries.
 A slow query refers to a query that consumes more time than the "long_query_time" definition.
 # If Log_long_format is opened, queries that do not use the index are also logged. # If you often add new queries to existing systems.
This is generally a good idea.
 Log_slow_queries # All the queries that use more than this time (in seconds) are considered slow queries.
# do not use "1″" here, otherwise it will cause all queries, even very fast query pages to be recorded (since MySQL's current time accuracy can only reach the second level). Long_query_time = 6 # More information is logged in the slow log.
 # generally this item is best opened. # opening this item will record that queries that do not use the index are also attached to the slow log as a slow-speed query Log_loNg_format # This directory is used by MySQL to save temporary files. For example, # It is used to handle large disks based sorting, as well as internal sorting.
 # and a simple temporary table.
 # If you don't create a very large temporary file, it might be nice to put it on the Swapfs/tmpfs file system. The other option is that you can also place it on a separate disk. # you can use '; '
 To place multiple paths # They will be polled in accordance with the Roud-robin method.
 # This value needs to be set on both master and slave.
# if "Master-host" is not set, the default is 1, but if this option is omitted, MySQL does not take effect as master. Server-id = 1 # copied slave (remove the comment for MASTER section to make it effective) # # To configure this host as a replicated slave server, you can choose between two ways: # 1 ' Using the Change MASTER to command (in our manual
 Have a complete description) –# syntax is as follows: # change MASTER to Master_host=, master_port=, # master_user=, master_password=;
 # You need to replace, wait for the field surrounded by angle brackets and the port number that uses master (default 3306). # # Example: # change MASTER to Master_host= ' 125.56.12.1′, master_port=3306, # master_user= ' Joe ', Master_password= ' secret
 '; # # or # # 2 set the following variables. Anyway, in case you choose this method, and then start the replication for the first time (even if unsuccessful, # for example if you enter the wrong password in the Master-password field and slave cannot connect), # Slave creates a master.info file, and then
 Any changes to the parameters contained within this file will be ignored # and overwritten by the contents of the Master.info file, unless you turn off the slave service, delete the master.info, and restart the slave service. # byFor this reason, you may not want to touch the configuration (commented out) and use change MASTER to (view above) instead of # # The unique ID number that is required is between 2 and 2^32–1 # (and unlike MASTER) # if Master-host is set The default value is 2 # but if omitted, it will not take effect #server-id = 2 # # The master– in the replication structure must #master-host = # # The user name that slave is used to authenticate when connecting to master-must be #maste
 R-user = # # when connected to master slave the password to authenticate-must #master the port that-password = # Master listens on.
 # optional – Default is 3306 #master-port = # makes slave read-only. Only the user has the Super permission and the slave thread above can modify the data.
 # You can use this to ensure that no application will accidentally modify the slave instead of the data on Master #read_only #*** MyISAM the size of the keyword buffer, which is generally used to buffer the index block of the MyISAM table. # don't set it larger than 30% of your available memory # because part of the memory is also used by the OS to buffer row data # Even if you don't use the MyISAM table, you need to still set up 8-64m memory because it will also be used by the internal temporary disk table.
 Key_buffer_size = 128M # is used to make MyISAM table full table scan buffer size.
# When the full table scan is needed, it is allocated in the corresponding thread.
 Read_buffer_size = 8M # When the row is read from a sorted sequence after sorting, the row data is read from this buffer to prevent disk seek.
 # If you increase this value, you can improve the performance of many order by. # read_rnd_buffer_size = 64M # MyISAM use special tree-like cache to make burst inserts when needed (these inserts are, insert ...) SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA # INFILE) are faster.
 This variable limits the number of bytes in the buffer tree in each process.
 # setting to 0 will turn off this optimization. # to optimize not to set this value greater than ' Key_bUffer_size ".
# This buffer will be allocated when burst inserts are detected.
 Bulk_insert_buffer_size = 256M # This buffer is assigned when MySQL needs to cause the rebuild index in REPAIR, OPTIMIZE, ALTER, and LOAD DATA INFILE to an empty table.
# This is assigned in each thread, so be careful when setting large values.
 myisam_sort_buffer_size = 256M # mysql The maximum size of temporary files allowed when the index is rebuilt (when REPAIR, ALTER TABLE, or LOAD DATA INFILE).
 # If the file size is larger than this, the index will be created (slower) by the key-value buffer myisam_max_sort_file_size = 10G # If the temporary file used for indexing the index to be used faster is greater than the established value, then the key-value buffering method is used.
# This is primarily used to force long string keys in large tables to use slow key-value buffering methods to create indexes.
 Myisam_max_extra_sort_file_size = 10G # If a table has more than one index, MyISAM can fix them by using more than one thread in parallel sorting. # This is a good choice for users with multiple CPUs and large amounts of memory.

myisam_repair_threads = 1 # Auto check and fix MyISAM table without proper shutdown. Myisam_recover # default Shutdown Federated skip-federated # * * BDB Related options * * * # Use this option if you are running a MySQL service that has BDB support but you are not ready to use it.
This saves memory and can accelerate something. SKIP-BDB # * * * INNODB Related OPTIONS * * * * If your MySQL service contains INNODB support but is not intended to be used, # Use this option to save memory and disk space, and to speed up some parts #skip-innodb # Additional memory pools are in
 Nodb is used to save metadata information # If INNODB needs more memory for this purpose, it will begin to request memory from the OS.
 # Since this operation is fast enough on most modern operating systems, you generally do not need to modify this value.
# show INNODB The STATUS command displays the number of the in-use. Innodb_additional_mem_pool_size = 64M # InnoDB uses a buffer pool to hold indexes and raw data, unlike MyISAM.
 # The larger you set here, the less disk I/O you need to access the data in the table.
 # on a standalone database server, you can set this variable to the size of the server's physical memory 80% # do not set too large, otherwise, due to the competition of physical memory may cause the operating system to change page bumps.
# Note that each process on a 32-bit system may be limited to 2-3.5g user-level memory limits, so don't set it too high.
 Innodb_buffer_pool_size = 6G # InnoDB saves data in one or more data files as tablespaces.
 # If you only have a single logical drive to save your data, a single self-add file is good enough.
 # in other cases. A single file for each device is generally a good choice.
 # You can also configure InnoDB to use bare disk partitions-please refer to the manual for more relevant content Innodb_data_file_path = ibdata1:10m:autoextend # Set this option if you want InnoDB tablespace files to be saved in other partitions.
 # The default is saved in the MySQL DataDir.
 #innodb_data_home_dir = # number of IO threads used to synchronize IO operations.
# This value is hard-coded to 4 under UNIX, but may perform better in a large number of Windows disk I/O.
 Innodb_file_io_threads = 4 # If you find that the InnoDB table space is corrupted, setting this value to a non-0 value may help you to export your table.
 # Start with 1 and add this value to know that you can successfully export the table.
 #innodb_force_recovery = 1 # Number of allowed threads in the InnoDB core. # The optimal value depends on the application, hardware, and operating system scheduling.
# too high a value can cause a thread to be mutually exclusive bumps.
 innodb_thread_concurrency = 16 # If set to 1, InnoDB will refresh (fsync) The transaction log to disk after each commit, # This provides complete acid behavior.
 # If you're willing to compromise on the security of a transaction and you're running a small food, you can set this value to 0 or two to reduce disk I/O caused by the transaction log the log file is written approximately per second and the log files are flushed to disk. # 2 represents the log write log file after each commit, but only about every second of the log file is refreshedto disk. Innodb_flush_log_at_trx_commit = 2 (Note: If it is a game server, it is recommended that this value be set to 2; if it is a highly demanding application for data security, the recommended setting is 1; set to 0 performance is highest, but if there is a failure, the data may be in danger of loss! The default value of 1 means that every instruction outside the transaction commit or transaction needs to write the log to the (flush) hard disk, which is time consuming. Especially when using battery-powered caching (Battery backed up cache). Set to 2 for many applications, especially from the MyISAM table, it means that the system cache is written without writing to the hard disk. The log still flush to the hard drive every second, so you won't normally lose more than 1-2 seconds of updates. Set to 0 will be faster, but the security aspect is poor, even if MySQL hangs may lose the transaction data. A value of 2 only loses data when the entire operating system hangs. ) # accelerates the InnoDB closure.
 This prevents InnoDB from doing full cleanup and inserting a buffer merge at shutdown.
 # This can greatly increase the shutdown time, but instead the InnoDB may do so at the next startup.
 #innodb_fast_shutdown # The size of the buffer used to buffer log data.
 # When this value is full, InnoDB will have to refresh the data to disk.
 # because it refreshes almost every second, there is no need to set this value too large (even for long transactions) innodb_log_buffer_size = 16M # in the log group per log file size.
 # You should set the aggregate size of the log file to the size of your buffer pool # to avoid unnecessary buffer pool refreshes in the log file 25%~100%. # However, note that a large log file size increases the time required for the recovery process.
 Innodb_log_file_size = 512M # Total number of files in the log group.
# Generally speaking, 2~3 is better. Innodb_log_files_in_group = 3 # InnoDB The location of the log file.
 The default is MySQL DataDir.
 # You can assign it to a standalone hard disk or a RAID1 volume to improve its performance #innodb_log_group_home_dir # The ratio of the maximum allowable dirty pages in the InnoDB buffer pool.
 # If the limit is reached, InnoDB will start refreshing them to prevent them from interfering with the Clean data page.
# This is a soft limit that is not guaranteed to be absolutely executed. Innodb_max_dirty_pages_pct = # InnoDB The method used to refresh the log.
 # table spaces always use the double write Refresh method # The default value is ' Fdatasync ' and the other is ' O_dsync '.
 #innodb_flush_method =o_dsync # before being rolled back, a INNODB transaction should wait for a lock to be approved for how long.
 # InnoDB automatically detects a transaction deadlock and rolls back the transaction in its own lock table.
 # If you use the LOCK TABLES command, or if you use a storage engine with a transaction security other than InnoDB in the same transaction # then a deadlock can occur and InnoDB cannot notice it.
# In this case this timeout value is very helpful for solving this problem. Innodb_lock_wait_timeout = [Mysqldump] # do not cache the entire result in memory before it is written to disk.
 This is required when exporting very large tables The quick Max_allowed_packet = 32M [MySQL] no-auto-rehash # only allows UPDATEs and deletes with key values. 
 #safe-updates [isamchk] Key_buffer = 2048M Sort_buffer_size = 2048M Read_buffer = 32M Write_buffer = 32M [Myisamchk] 

Key_buffer = 2048M Sort_buffer_size = 2048M Read_buffer = 32M Write_buffer = 32M [Mysqlhotcopy] Interactive-timeout
 [Mysqld_safe] # to increase the number of open files per process.
 # Warning: Make sure you have set the system limit high enough!
 # Open a large number of tables you need to set this value to large Open-files-limit = 8192

From:http://www.ha97.com/4110.html