MySQL my.cnf configuration file detailed

This profile is for Dell R710, dual Xeon E5620, and 16G memory hardware configurations. CentOS 5.6 64-bit system, MySQL 5.5.x stable version. For sites with daily IP 50-100W,PV 100-300w, use the InnoDB storage engine primarily. Other application environment please set the optimization according to the actual situation.

# The following options will be read by the MySQL client app.
Note Only the client application included with MySQL guarantees that this content can be read.
# If you want your own MySQL application get these values.
# These options need to be specified when the MySQL client library is initialized.

#
[Client]
#password = [Your_password]
Port = @[email Protected]
Socket = @[email Protected]

# * * * Application Customization Options * * *

#
# MySQL Server
#
[Mysqld]

# General configuration Options
Port = @[email Protected]
Socket = @[email Protected]

# Back_log is the number of connections that the operating system can hold in the listening queue.
# The queue holds the connection before the MySQL Connection Manager thread is processed.
# If you have a very high connection rate and the "connection refused" error occurs,
# You should increase 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, it will not be effective
Back_log = 300

# Do not listen on TCP/IP ports.
# If all the processes are connected to the local mysqld on the same server,
# This setting will be a security-enhancing method
# All MYSQLD connections are made through UNIX sockets or named pipes.
# Note Under Windows if the Named Pipes option is not turned on, just use this item
# (via the "enable-named-pipe" option) will cause the MySQL service to have no effect!
#skip-networking

# The maximum number of simultaneous sessions allowed by the MySQL service
# One of the connections will be logged in as an administrator with Super privileges reserved.
# even if the maximum number of connections has been reached.
Max_connections = 3000
# The maximum number of errors per client connection is allowed if this limit is reached.
# This client will be blocked by the MySQL service until the "FLUSH HOSTS" is executed or the service restarts
# illegal passwords and other errors at the time of linking increase this value.
# View the "aborted_connects" status to get the global counters.
Max_connect_errors = 30

# The number of tables opened by all threads.
# Increasing this value increases the number of file descriptors required by the MYSQLD
# so you need to make sure that the "open-files-limit" variable in [Mysqld_safe] sets the number of open files allowed at least 4096
Table_cache = 4096

# Allow external file-level locks. Opening a file lock can adversely affect performance
# so use this option only if you are running multiple DB instances on the same file (note that there will still be other constraints!)
# or you use some other software dependencies on the file level to lock the MyISAM table
#external-locking

# The maximum size of the request package that the service can handle and the maximum requested size that the service can handle (when working with a large blob field is quite necessary)
# Each connection is independent of size. Dynamic Increase in size
Max_allowed_packet = 32M

# Binlog in a transaction in order to record the cache size held by the SQL state
# If you often use large, multi-declarative transactions, you can increase this value to get greater performance.
# All the status of the service will be buffered in the Binlog buffer and then written to Binlog once committed
# If the transaction is larger than this value, the temporary file on disk will be used instead.
# This buffer is created when the status of each connected transaction is updated the first time
Binlog_cache_size = 4M

# The maximum capacity allowed for a separate memory table.
# This option prevents accidental creation of a large memory table resulting in all memory resources being exhausted.
Max_heap_table_size = 128M

# sort buffers are used to handle orders that are similar to the order by and the group by queue
# If the sorted data cannot be placed in the sort buffer,
# An alternative disk-based merge classification will be used
# View the "sort_merge_passes" status variable.
# assigned by each thread when sorting occurs
Sort_buffer_size = 16M

# This buffer is used to optimize federation (full JOINs without index union).
# Similar unions have very poor performance in most cases,
# However, setting this value to large can mitigate the performance impact.
# See the total number of unions through the "Select_full_join" state variable
# When full federation occurs, each thread is assigned
Join_buffer_size = 16M

# How many threads we keep in the cache for reuse
# When a client disconnects, if the thread in the cache is 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 lot of new connections
# (in general, if you have a good threading model, this will not have a noticeable performance boost.)
Thread_cache_size = 16

# This allows the application to give the thread system a hint at the same time to give the desired number of threads 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 number]* (2..4) as the value of thread_concurrency
Thread_concurrency = 8

# query buffering is often used to buffer the results of a SELECT and no longer executes the direct return result at the next same query.
# Open query buffering can greatly improve server speed if you have a large number of identical queries and rarely modify tables.
# Check the "qcache_lowmem_prunes" status variable to see if the current value is high enough for your load.
Note: In cases where your table is constantly changing, or if your query text is different every time,
# query buffering may cause performance degradation rather than performance gains.
Query_cache_size = 128M

# only results that are smaller than this setting will be buffered
# This setting is used to protect query buffering, preventing a large result set from overwriting all other query results.
Query_cache_limit = 4M

# The smallest word length indexed by the 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 your fulltext index
Ft_min_word_len = 8

# If your system supports the Memlock () function, you may want to turn this option on to keep your data locked in memory and prevent it from being swapping out when the running MySQL is in high memory
# This option is good for performance
#memlock

# When a new table is created, it is used as the default table type.
# This value will be used if the create represents no special execution table type
Default_table_type = MYISAM

# The heap size used by the thread. The memory for this capacity is reserved on each connection.
# MySQL itself often does not need more than 64K of memory
# If you use your own UDF functions that require a lot of heap
# or your operating system requires more heap for some operations,
# You may need to set it up a bit higher.
Thread_stack = 512K

# Set the default transaction isolation level. The available levels are as follows:
# read-uncommitted, read-committed, Repeatable-read, SERIALIZABLE
Transaction_isolation = Repeatable-read

# The maximum size of the internal (in-memory) temporary table
# If a table grows larger than this value, it will be automatically converted to a disk-based table.
# This limit is for a single table, not a sum.
Tmp_table_size = 128M

# turn on the binary logging feature.
# in the Replication (replication) configuration, this must be turned on as the master master server
# If you need to do a point-in-time recovery 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 item on Server B.
# This option opens the log of updates that have been re-made on the thread.
# and write it to the binary log from the server.
#log_slave_updates

# Open the full query log. All queries received by the server (even for a query of the error syntax)
# will be recorded. This is useful for debugging, which is often turned off in a production environment.
#log

# Print out the warning to the error log file. If you have any questions about MySQL
# You should open the warning log and review the error log carefully to isolate possible causes.
#log_warnings

# Record slow queries. A slow query is a query that consumes more time than the definition of "long_query_time".
# If Log_long_format is opened, queries that do not use the index are also logged.
# If you frequently add new queries to existing systems. Generally speaking, this is a good idea,
Log_slow_queries

# All queries that use more than this time (in seconds) will be considered slow queries.
# do not use "1″" here, otherwise it will cause all queries, even very fast query pages to be recorded (due to the accuracy of MySQL current time can only reach the second level).
Long_query_time = 6

# record more information in the slow log.
# generally this is best opened.
# Opening this entry will record that queries that do not use the index are also attached to the slow log as a slow query
Log_long_format

# This directory is used by MySQL to save temporary files. For example,
# It is used to handle large-scale disk-based sorting, like internal sorting.
# and a simple temporary table.
# If you don't create very large temporary files, it might be better to put them on the Swapfs/tmpfs file system
# Another 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.
#tmpdir =/tmp

# * * * Master copy-related settings

# The unique service identification number, the value is between 1 and 2^32-1.
# 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 will not take effect as master.
Server-id = 1

# Copy the slave (remove the comment from the master paragraph to make it effective)
#
# in order to configure this host as a replicated slave server, you can choose between two methods:
#
# 1) Use the Change MASTER to command (full description in our manual) –
# 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 to use master (default 3306).
#
Example
#
# change MASTER to Master_host= ' 125.564.12.1′, master_port=3306,
# master_user= ' Joe ', master_password= ' secret ';
#
# or
#
# 2) Set the following variables. However, in the case where you choose this method, and then first start the replication (even if it is unsuccessful,
# For example if you enter the wrong password in the Master-password field and slave cannot connect),
# Slave will create a master.info file, and any subsequent changes to the parameters contained within this file will be ignored
# and overwritten by content within the Master.info file, unless you close the slave service, remove master.info and restart the slave service.
# for this reason, you may not want to touch the configuration (commented out) and use change MASTER to (see above) instead
#
# The unique ID number required is between 2 and 2^32–1
# (and unlike master)
# If the master-host is set. The default value is 2
# but if omitted, it will not take effect
#server-id = 2
#
# master– in a copy structure must
#master-host =
#
# username used for authentication when connected to master – must be slave
#master-user =
#
# When connected to master, the slave is used to authenticate the password – must be
#master-password =
#
# Master listens on the port.
# optional – Default is 3306
#master-port =

# makes slave read only. Only the user has super privileges and the slave thread above can modify the data.
# You can use this to ensure that no application will accidentally modify the data on the slave instead of the master
#read_only

#*** MyISAM Related options

# The size of the keyword buffer, generally used to buffer the index block of the MyISAM table.
# do not set it greater than 30% of your available memory,
# because part of the memory is also used by the OS to buffer row data
# Even if you do not use the MyISAM table, you also need to still set up 8-64m memory because it will also be used by the internal temporary disk table.
Key_buffer_size = 128M

# used to do MyISAM table full table scan buffer size.
# When a full table scan is required, it is allocated in the corresponding thread.
Read_buffer_size = 8M

# when the rows are read from a sorted sequence after sorting, the row data is read from the buffer to prevent the disk from seeking the path.
# If you increase this value, you can improve the performance of many order by.
# assigned by each thread when needed
Read_rnd_buffer_size = 64M

# MyISAM uses a special tree-like cache to make a burst insert
# (These inserts are, insert ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA
# INFILE) faster. This variable limits the number of bytes in the buffer tree per process.
# set to 0 turns off this optimization.
# for optimization do not set this value greater than "Key_buffer_size".
# This buffer will be allocated when burst insertion is detected.
Bulk_insert_buffer_size = 256M

# This buffer is assigned when MySQL needs to be allocated in REPAIR, OPTIMIZE, ALTER, and LOAD DATA INFILE to an empty table causing the rebuild index.
# This is assigned in each thread. So be careful when setting large values.
Myisam_sort_buffer_size = 256M

# The size of the maximum temporary file allowed when MySQL rebuilds the index (when REPAIR, ALTER TABLE or LOAD DATA INFILE).
# If the file size is larger than this value, the index will be created by the key buffer (slower)
Myisam_max_sort_file_size = 10G

# Use the key-value buffering method if the temporary file used to create the index using a faster index is greater than the value that is set.
# This is primarily used to force the long string key in large tables to use a slow key-value buffer method to create an index.
Myisam_max_extra_sort_file_size = 10G

# If a table has more than one index, MyISAM can use more than one thread to fix them by using parallel sorting.
# This is a good choice for users with multiple CPUs and a lot of memory.
Myisam_repair_threads = 1

# automatically check and fix the MyISAM table without proper shutdown.
Myisam_recover

# Default off 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 speed things up.
Skip-bdb

# * * * * INNODB Related options * * *

# If your MySQL service contains INNODB support but is not intended to be used,
# using this option saves memory and disk space, and accelerates some parts
#skip-innodb

# Additional memory pools are innodb used to hold 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.
The # show INNODB STATUS command displays the number of yourselves used.
Innodb_additional_mem_pool_size = 64M

# InnoDB uses a buffer pool to hold indexes and raw data, unlike MyISAM.
# The larger you set up, 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 80% of the server's physical memory size
# do not set too large, otherwise, due to the competition of physical memory may cause the operating system to break the page.
# Note on 32-bit systems you each process 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 a tablespace.
# If you only have a single logical drive to save your data, a single self-increment file is good enough.
# in other cases. A file Per device is generally a good choice.
# You can also configure InnoDB to use bare disk partitioning – please refer to the manual for more information
Innodb_data_file_path = Ibdata1:10m:autoextend

# Set this option if you want the InnoDB tablespace file to be saved in another partition.
# is saved in MySQL DataDir by default.
#innodb_data_home_dir =

# The number of IO threads used to synchronize IO operations. This value is
# This value is hardcoded to 4 under UNIX, but the Windows disk I/O may perform better under a large value.
Innodb_file_io_threads = 4

# If you find that the InnoDB tablespace is corrupted, setting this value to a value other than 0 may help you export your table.
# Start from 1 and increase this value to know you can successfully export the table.
#innodb_force_recovery =1

# The number of allowed threads within the InnoDB core.
# The optimal value depends on how the application, hardware, and operating system are scheduled.
# High values can cause mutually exclusive bumps on the thread.
Innodb_thread_concurrency = 16

# If set to 1, InnoDB will flush (fsync) The transaction log to disk after each commit.
# This provides complete acid behavior.
# If you are willing to compromise on transactional security and you are running a small food, you can set this value to 0 or both to reduce disk I/O caused by the transaction log
# 0 means that the log is written only about every second and the log file is flushed to disk.
# 2 means that log files are written to the log file after each commit, but the log files are flushed to disk only about once per second.
Innodb_flush_log_at_trx_commit = 2
(Note: If it is a game server, this value is recommended to be set to 2, if it is a very high data security application, it is recommended to set to 1; set to 0 performance is the highest, but if there is a failure, the data can be lost in danger!) The default value of 1 means that every single transaction commit or out-of-transaction instruction requires the log to be written to (flush) the hard disk, which is time consuming. Especially when using the battery-powered cache (Battery backed up cache). Set to 2 for many applications, especially from the MyISAM table is possible, it means to write to the system cache instead of writing to the hard disk. The log will still flush to the hard drive every second, so you will generally not lose more than 1-2 seconds of updates. Set to 0 will be faster, but the security side is poor, even if MySQL hangs may also lose the transaction data. A value of 2 will only lose data if the entire operating system is hung. ）

# speed up the InnoDB shutdown. This prevents InnoDB from doing a full purge and inserting a buffer merge when it is closed.
# This can greatly increase the shutdown time, but instead the InnoDB may do so at the next boot.
#innodb_fast_shutdown

# The size of the buffer used to buffer the log data.
# When this value is nearly full, INNODB will have to flush the data to disk.
# because it's basically refreshed every second, there's no need to set this value too large (even for long transactions)

Innodb_log_buffer_size = 16M

# The size of each log file in the log group.
# You should set the total size of the log file to the size of your buffer pool 25%~100%
# to avoid unnecessary buffer pool refresh behavior in log file overwrite.
# Regardless, note that a large log file size increases the time that is required for the recovery process.
Innodb_log_file_size = 512M

# The total number of files in the log group.
# usually it's better to have one or both.
Innodb_log_files_in_group = 3

# InnoDB log files are in the same location. The default is MySQL's datadir.
# You can assign it to a standalone hard drive or a RAID1 volume to improve its performance
#innodb_log_group_home_dir

# The scale 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 and is not guaranteed to be absolutely enforced.
innodb_max_dirty_pages_pct = 90

# 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

# How long a InnoDB transaction should wait for a lock to be approved before being rolled back.
# InnoDB automatically detects the transaction deadlock in its own lock table and rolls back the transaction.
# If you use the LOCK TABLES directive, or use a storage engine other than InnoDB for transaction security in the same transaction
# Then a deadlock can happen and InnoDB can't notice.
# This timeout value in this case is very helpful for solving this problem.
Innodb_lock_wait_timeout = 120

[Mysqldump]
# do not cache the entire result in memory before it is written to disk. This is required when exporting very large tables
Quick

Max_allowed_packet = 32M

[MySQL]
No-auto-rehash

# only UPDATEs and deletes with key values are allowed.
#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]
# Increase the number of open files per process.
# Warning: Make sure you have set the system limit high enough!
# Opening a large number of tables requires setting this value to large
Open-files-limit = 8192

Note: This article is reproduced, if there is infringement, please contact me: [Email protected]

Detailed MySQL my.cnf configuration file