MySQL my.cnf parameter Configuration optimization detailed

http://leeyin.iteye.com/blog/1459274

[b] 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. 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.  [/b] # 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] # BAC  The K_log is the number of connections that the operating system can hold in the listening queue, and 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 add the value here.  # Check your operating system documentation to get the maximum value for this variable.  # If the Back_log is set to a higher value than your operating system, it will have no effect Back_log = 300 # does not listen on the TCP/IP port.  # If all the processes are connected to the local mysqld on the same server, # This will be an enhanced security method # All MYSQLD connections are made through UNIX sockets or named pipes.  Note Under Windows If you do not open the Named pipes option and 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 permissions 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 link time will 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 for Mysqld # so you need to confirm that the "open-files-limit" variable in [Mysqld_safe] setting Open file number allows at least 4096 Table_cache = 4096 # Allow external A file-level lock.  Opening a file lock can negatively affect performance # So use this option only if you are running multiple DB instances on the same file (note that there are still 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 packet that the service can handle and the maximum request size that the service can handle (when working with a large blob field) # Each connection is independent of the large  Small. Dynamic increase in size 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, 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 at the first update state of each connected transaction 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 # The sort buffer is used to process the sort caused by 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 # to view "  Sort_merge_passes "state variable. # assigned by each thread when sorting occurs sort_buffer_size = 16M # This buffer is madeUsed to optimize federation (full JOINs without index union).  # Similar unions have very poor performance in most cases, but setting this value to large can mitigate the performance impact. # View the number of full unions through the "Select_full_join" state variable # when full federation occurs, allocate join_buffer_size in each thread = 16M # We keep how many threads in the cache are used for reuse # When a client disconnects, as  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 # (Generally speaking, 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 that are running.  # This value is only meaningful for systems that support the thread_concurrency () function (for example, Sun Solaris). # you can try using [CPU number]* (2..4) as the value of thread_concurrency thread_concurrency = 8 # The query buffer is often used to buffer the results of a SELECT and no longer executes the direct return node 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 situations 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 less than this setting are buffered # This setting is used to protect query buffering, preventing a large result set from overwriting all other query results.  Query_cache_limit = 4M # is the smallest word length indexed by the full-text index.  # You may want to reduce it if you need to search for shorter words. # Note 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 let the running MySQL in memory height tightData is locked in memory and prevents it from being swapping out # This option is useful for performance #memlock # When creating a new table 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 function that requires a lot of heap # or your operating system needs more heap 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 transact  Ion_isolation = repeatable-read # internal (in-memory) the maximum size of the 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 # Opens the binary logging feature.  # in Replication (replication) configuration, this key must be opened as 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 the update that was re-made on the thread, # and writes it to the binary log from the server. #log_slave_updates # Open the full query log. All queries received by the server (even queries for an 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 problems with 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 open, queries that do not use the index are also logged. # If you frequently add new queries to existing systems.  In general this is a good idea, Log_slow_queries # all use more queries 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 make the queries that are not using the index also be 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 disk-based sorting, as in 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 them on separate disks. # you can use ";"  To place multiple paths # They will be polled in accordance with the Roud-robin method.  #tmpdir =/TMP # * * * Master-slave replication-related settings # Unique service identification number, values are located 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 of slave (remove comments from MASTER segment to make it effective) # # In order to configure this host as a replicated slave server, you can choose between two methods: # # 1) using the Change MASTER to command (in our  In the 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). # # # # # # # # # # # change MASTER to Master_host= ' 125.564.12.1′, Master_port=3306, # master_user= ' Joe ', master_password= ' secret '; # # # # # 2) set the following variables. Anyway, in case you choose this method, and then first start the copy (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 close the slave service, delete the 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 (view above) instead of # # # The unique ID number required is between 2 and 2^32–1 # (and unlike MASTER) # if master-  The host is set. The default value is 2 # but if omitted, it will not take effect #server-id = 2 # # master– in the replication structure must be #master-host = # # when connecting to master slave the user name used for authentication-  # optional – The 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 accidentally modifies the data on slave instead of on master #read_only #*** MyISAM related options # keyword buffer size, commonly used to buffer the index blocks of MyISAM tables.  # do not set it greater than 30% of the available memory, # because part of the memory is also used by the OS to buffer the 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 # The buffer size used to do the MyISAM table full table scan. # When a full table scan is required, the corresponding lineAssigned in the process.  Read_buffer_size = 8M # When a row is read from an already 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. # when needed by each thread assigned read_rnd_buffer_size = 64M # MyISAM uses a special tree-like cache to make a burst insert # (these insertions are, insert ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA # INFILE) are 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 is created (slower) with the myisam_max_sort_file_size = 10G # The key-value buffering method is used if the temporary file used to create the index using a faster index is greater than the established value.  # 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 # automatic check and fix 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.  The memory pool is 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 the server physical memory size of 80% # 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 may be limited to 2-3.5g user-level memory limits on each process, # 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 relevant content 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 hardcoded under UNIX to 4, 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 non-0 value may help you to export your table.  # Start from 1 and increase this value to know you can successfully export the table.  #innodb_force_recovery = 1 # 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 flushes (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 two and reduce the disk I/O caused by the transaction log # 0 to represent the log only about every second written to the log file and the log files are 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: This value is recommended to be set to 2 if it is a game server, and if it is a very high data security requirement, it is recommended to set to 1 and set to 0 for the highest performance, but in the event of a failure, the data may be at risk of loss!) 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. ) # accelerates 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 # every day in a log groupThe size of the log file.  # 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 The log file is in the same location.  The default is MySQL's datadir.  # You can assign it to a standalone hard disk or to 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 = # InnoDB The method used to refresh the log.  # tablespace always uses 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 occur and InnoDB cannot notice.  # This timeout value in this case 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. 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 = 32MWrite_buffer = 32M [Myisamchk] Key_buffer = 2048M Sort_buffer_size = 2048M Read_buffer = 32M Write_buffer = 32M  [Mysqlhotcopy] interactive-timeout [Mysqld_safe] # increases 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 that this value be set to large Open-files-limit = 8192

　　

My.ini (Linux system is my.cnf), when the MySQL server starts it will read this file, set the relevant operating environment parameters.

The My.ini is divided into two blocks: the Client section and the server section.
The client section is used to configure MySQL clients parameters.
To view configuration parameters, you can use the following command:

Show variables like '%innodb% '; # view INNODB related configuration parameters show status like '%innodb% '; # view InnoDB Related runtime parameters (such as the number of tables currently open, the number of tables currently open) show global status like ' Open%tables '; # View the Global runtime parameters, plus global is the statistics for all DB instances running in the current MySQL server. Without global, only the current DB instance is counted.

1. Client section
[Client]
Port = 3306 # Sets the default ports used by the MySQL client when connecting to the server
[MySQL]
Default-character-set=utf8 # setting MySQL client default character set

2. Server section

[Mysqld]
port=3306 # MySQL server-side default listener (listen on) TCP/IP port

Basedir= "C:/Program Files/mysql/mysql Server 5.5/" # datum path, other paths are relative to this path

Datadir= "C:/Program files/mysql/mysql Server 5.5/data" # MySQL Database file directory

Character-set-server=latin1 # The character set used by the service side defaults to a 8-bit encoded latin1 character set

DEFAULT-STORAGE-ENGINE=INNODB # Default storage engine that will be used when creating a new table

Sql-mode= "strict_trans_tables,no_auto_create_user,no_engine_substitution" # SQL mode is STRICT mode

MAX_CONNECTIONS=100 # The maximum number of concurrent connections supported by the MySQL server (number of users). However, one of the connections is always reserved for administrators to log on with super privileges, even if the number of connections reaches the maximum limit. If the settings are too small and the user is more, the "Too many connections" error will often occur.

Query_cache_size=0 # Query cache size, used to cache select query results. If you have many select queries that return the same query results, and you rarely change the table, you can set query_cache_size greater than 0, which can greatly improve query efficiency. And if the table data changes frequently, do not use this, it will backfire

TABLE_CACHE=256 # This parameter is called Table_open_cache in the version after 5.1.3 to set the number of table caches. Because each client connection accesses at least one table, the value of this parameter is related to Max_connections. When a connection accesses a table, MySQL checks the number of tables that are currently cached. If the table is already open in the cache, direct access to the tables in the cache speeds up the query, and if the table is not cached, the current table is added to the cache and queried. Before performing a cache operation, Table_cache is used to limit the maximum number of cache tables: If the currently cached table does not reach Table_cache, the new table will be added, and if this value is reached, MySQL will release the previous cache based on rules such as the last query time of the cache table, query rate, and so on.

tmp_table_size=34m # The maximum size allowed for each temporary table in memory. If the temporary table size exceeds this value, the temporary table is automatically converted to a disk Based table.

Thread_cache_size=8 # The maximum number of threads cached. When the client connection is broken, the thread that handles the client task is put back into the cache if the total number of client connections is less than that value. In high concurrency situations, if the value is set too small, many threads are created frequently, the overhead of thread creation becomes larger, and the query efficiency decreases. In general, if there is good multithreading on the application side, this parameter will not improve the performance much.

# MyISAM related parameters
myisam_max_sort_file_size=100g # mysql The maximum size allowed for a temporary file when rebuilding an index

Myisam_sort_ buffer_size=68m

key_buffer_size=54m # key buffer size, used to cache the index block of the MyISAM table. Determines the speed of database index processing (especially indexed reads)

read_buffer_size=64k # is used when using a full table scan of a MyISAM table. Allocated for each thread (provided a full table scan is performed). When you sort a query, MySQL scans the buffer first to avoid disk searches, improve query speed, and, if you need to sort large amounts of data, raise the value appropriately. However, MySQL will issue this buffer space for each client connection, so you should set this value as appropriately as possible to avoid excessive memory overhead.

read_rnd_buffer_size=256k

sort_buffer_size=256k # Connection-level parameters (configured for each thread), 500 threads will consume 500*256k Sort_ Buffer_size.

# InnoDB Related parameters
INNODB_ADDITIONAL_MEM_POOL_SIZE=3M # InnoDB Memory pool size for storing metadata information, generally no modification required

Innodb_flush_log_at_trx_commit = 1 # Transaction-related parameters, if the value is 1, InnoDB writes the transaction log to disk at each commit (disk IO consumption is large), which guarantees full acid performance. If set to 0, the transaction log writes to the memory log and the memory log writes to the disk at a frequency of 1 times per second. If set to 2, the transaction log is written to memory log at each commit, but the memory log is written to disk 1 times per second.

INNODB_LOG_BUFFER_SIZE=2M # InnoDB The log data buffer size, and if the buffer is full, the log data in the buffer is written to disk (flush). Since the disk is usually written at least 1 seconds, there is no need to set it too large, even for a long transaction.

innodb_buffer_pool_size=105m # InnoDB uses a buffer pool to cache index and row data. The larger the value is set, the less disk IO. This value is typically set to 80% of physical memory.

innodb_log_file_size=53m # The size of each INNODB transaction log. Generally set to 25% to 100% of Innodb_buffer_pool_size

Innodb_thread_concurrency=9 # InnoDB The maximum number of concurrent threads in the kernel.

In the Apache, PHP, MySQL architecture, MySQL has the greatest impact on performance and is a key core. For the discuz! Forum program is also the case, MySQL settings are reasonable optimization, directly affect the speed and capacity of the forum! At the same time, MySQL is also the most difficult part of optimization, not only need to understand some of the MySQL expertise, but also need to observe statistics for a long time and based on experience to judge, and then set reasonable parameters. Let's look at some of the MySQL optimization basics, MySQL optimization I divided into two parts, one is the optimization of the server physical hardware, and the second is the MySQL itself (my.cnf) optimization.
first, the impact of server hardware on MySQL performance
① disk seek capability (disk I/O), with the current high-speed SCSI hard drive (7200 RPM) For example, the hard drive theoretically seeks 7,200 times per second, which is determined by the physical characteristics, there is no way to change. MySQL every second in a large number of complex query operations, the amount of read and write disk can be imagined. Therefore, the disk I/O is generally considered to be one of the biggest constraints on MySQL performance, for the average daily access to more than 1 million PV discuz! forum, due to disk I/O constraints, MySQL performance will be very low! To address this constraint, consider the following solutions: Using the raid-0+1 disk array, it is important not to try to use Raid-5,mysql on the RAID-5 disk array as quickly as you might expect.
②CPU for MySQL applications, it is recommended to use S.M.P. Architecture's multi-symmetric CPU, for example: two Intel Xeon 3.6GHz CPUs can be used, now I would rather recommend 4U servers dedicated to database server, not just for MySQL.
③ physical memory for a database server using MySQL, the server memory is recommended not less than 2GB, recommended to use more than 4GB of physical memory, but memory for the current server can be said to be a negligible problem, At work encountered high-end servers, basically more than 16G of memory.

Second, MySQL self-factor when the above server hardware constraints are resolved, let's look at how MySQL's own optimizations are being manipulated. The optimization of MySQL itself is mainly to optimize and adjust the parameters in the configuration file MY.CNF. Let's introduce some parameters that have a greater impact on performance. Since the optimization settings for the my.cnf file are closely related to the server hardware configuration, we specify an imaginary server hardware environment: Cpu:2 Intel Xeon 2.4GHz Memory: 4GB DDR HDD: SCSI 73GB (very common 2U server).

Below, we are based on the above hardware configuration with an optimizedmy.cnf to explain:
#vim/ETC/MY.CNF The following lists only the contents of the [MYSQLD] paragraph in the my.cnf file, and the other paragraphs have little impact on MySQL performance, so ignore them.
[Mysqld]
Port = 3306
ServerID = 1
Socket =/tmp/mysql.sock
Skip-locking
#避免MySQL的外部锁定, reducing the chance of error increases stability.
Skip-name-resolve
#禁止MySQL对外部连接进行DNS解析, this option can be used to eliminate the time for DNS resolution for MySQL. However, it is important to note that if this option is turned on, all remote host connection authorizations will use IP address mode, otherwise MySQL will not be able to handle the connection request properly!
Back_log = 384
#back_log参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中. If the system has many connections in a short period of time, you need to increase the value of this parameter, which specifies the size of the listening queue for incoming TCP/IP connections. Different operating systems have its own limitations on this queue size. Attempting to set a limit of back_log above your operating system will be invalid. The default value is 50. For Linux systems, the recommended setting is an integer less than 512.
Key_buffer_size = 256M
#key_buffer_size指定用于索引的缓冲区大小, increase it to get better index processing performance. The parameter can be set to 256M or 384M for a server that has around 4GB. Note: This parameter value setting is too large to be the overall efficiency of the server down!
Max_allowed_packet = 4M
Thread_stack = 256K
Table_cache = 128K
Sort_buffer_size = 6M
#查询排序时所能使用的缓冲区大小. Note: The allocated memory for this parameter is per-connection exclusive, and if there are 100 connections, then the actual allocated total sort buffer size is 100x6 = 600MB. Therefore, the recommended setting for a server that has around 4GB is 6-8m.
Read_buffer_size = 4M
#读查询操作所能使用的缓冲区大小. As with Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection.
Join_buffer_size = 8M
#联合查询操作所能使用的缓冲区大小, like Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection.
Myisam_sort_buffer_size = 64M
Table_cache = 512
Thread_cache_size = 64
Query_cache_size = 64M
#指定MySQL查询缓冲区的大小. It can be observed in the MySQL console that if the value of the qcache_lowmem_prunes is very large, there is often insufficient buffering, and if the value of qcache_hits is very large, the query buffer is used very frequently, and if the value is small it can affect efficiency. Then consider not querying the buffer; qcache_free_blocks, if the value is very large, it indicates that there is a lot of fragmentation in the buffer.
Tmp_table_size = 256M
Max_connections = 768
#指定MySQL允许的最大连接进程数. If the too many connections error is frequently encountered when accessing the forum, you need to increase the parameter value.
Max_connect_errors = 10000000
Wait_timeout = 10
#指定一个请求的最大连接时间, the server with about 4GB of memory can be set to 5-10.
Thread_concurrency = 8
#该参数取值为服务器逻辑CPU数量 * *, in this case, the server has 2 physical CPUs, and each physical CPU supports H.T Hyper-threading, so the actual value is 4*2=8
Skip-networking
#开启该选项可以彻底关闭MySQL的TCP/IP connection, do not turn on this option if the Web server accesses the MySQL database server as a remote connection! Otherwise it will not connect properly!
table_cache=1024
#物理内存越大, the larger the setting. Default is 2402, set to 512-1024 best
innodb_additional_mem_pool_size=4m
#默认为2M
Innodb_flush_log_at_trx_commit=1
#设置为0就是等到innodb_log_buffer_size列队满后再统一储存, default is 1
Innodb_log_buffer_size=2m
#默认为1M
Innodb_thread_concurrency=8
#你的服务器CPU有几个就设置为几, the recommended default is typically 8
key_buffer_size=256m
#默认为218, tune to 128 best
tmp_table_size=64m
#默认为16M, tune to 64-256 most hanging
read_buffer_size=4m
#默认为64K
read_rnd_buffer_size=16m
#默认为256K
sort_buffer_size=32m
#默认为256K
thread_cache_size=120
#默认为60
query_cache_size=32m

※ It is worth noting that:
many situations require specific analysis of specific circumstances
First, if the key_reads is too large, it should be my.cnf in the key_buffer_size to become larger, keep key_reads/key_read_requests at least 1/100, the smaller the better.
Second, if the qcache_lowmem_prunes is large, it is necessary to increase the value of query_cache_size.

MySQL my.cnf parameter Configuration optimization detailed