MySQL optimization, my. ini configuration instructions

[Mysqld]
Port = 3306
Serverid = 1
Socket =/tmp/MySQL. Sock

Skip-name-resolve # prohibit MySQL from performing DNS resolution on external connections
Skip-grant-tables
# Prohibit MySQL from performing DNS resolution on external connections. Using this option can eliminate the time for MySQL to perform DNS resolution. However, if this option is enabled, IP addresses are required for all remote host connection authorizations. Otherwise, MySQL cannot process connection requests normally! Note: If you use winform to connect to MySQL, adding this sentence will greatly improve the speed.

Skip-locking
# Avoid external locks of MySQL to reduce the chance of errors and enhance stability.

Back_log = 384
Specify the number of possible MySQL connections. When the MySQL main thread receives many connection requests within a short period of time, this parameter takes effect. The main thread takes a short time to check the connection and start a new thread. The value of the back_log parameter indicates how many requests can be stored in the stack within a short time before MySQL temporarily stops responding to a new request. 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 listener queue for the incoming TCP/IP connection. Different operating systems have their own limits on the queue size. Trying to set back_log to be higher than your operating system limit will be invalid. The default value is 50. We recommend that you set the value to an integer smaller than 512 in Linux.

Key_buffer_size = 32 m
# Key_buffer_size is very important for MyISAM tables. If you only use the MyISAM table, you can set it to 30-40% of the available memory. A reasonable value depends on the index size, data volume, and load. Remember, the MyISAM Table uses the operating system cache to cache data. Therefore, you need to leave some memory for them, in many cases, the data is much larger than the index. However, it is always necessary to check whether all key_buffer files are used -- The. myi file only has 1 GB, while the key_buffer is set to 4 GB. This is a waste. If you seldom use the MyISAM table, keep the key_buffer_size smaller than 16-32 MB to meet the requirements for temporary table indexes on the disk.

Innodb_buffer_pool_size = 2.4g
# This is very important for InnoDB tables. Compared with MyISAM tables, InnoDB is more sensitive to buffering. MyISAM can run in the default key_buffer_size setting. However, InnoDB is similar to snail bait in the default innodb_buffer_pool_size setting. Because InnoDB caches data and indexes, there is no need to leave too much memory for the operating system. Therefore, if you only need InnoDB, you can set it to up to 70-80% of available memory. -- If your data volume is small and does not increase rapidly, you do not need to set innodb_buffer_pool_size too large.

Innodb_additional_pool_size = 20 m
# This option does not affect performance much, at least on an operating system with almost enough memory to allocate. However, if you still want to set it to 20 MB (or larger), you need to check the other InnoDB memory to be allocated.

Innodb_log_file_size = 512 m
# It is important to have a high write load, especially a large dataset. The larger the value, the higher the performance, but note that the recovery time may increase. I often set it to 64-512 MB, depending on the server size.

Innodb_log_buffer_size = 16 m
# By default, the server performance is acceptable when the write load is moderate and the transaction is short. If there is a peak update operation or a large load, you should consider increasing the value. If its value is set too high, memory may be wasted-it will refresh once every second, so you do not need to set the memory space more than 1 second. Usually 8-16 Mb is enough. The smaller the system, the smaller its value.

Innodb_flush_logs_at_trx_commit = 2
# Is it because InnoDB is 1000 times slower than MyISAM? Maybe you forgot to modify this parameter. The default value is 1, which means that each commit of the update transaction (or a statement other than each transaction) will be refreshed to the disk, which is quite resource-consuming, especially when there is no battery backup cache. Many applicationsProgram, Especially those from MyISAM, set its value to 2, that is, do not refresh the log to the disk, instead, it is only refreshed to the operating system cache. Logs are still refreshed to the disk every second, so the consumption of 1-2 updates per second is usually not lost. If it is set to 0, it will be much faster, but it is relatively insecure-some transactions will be lost when the MySQL server crashes. Set to 2 to direct the part of the transaction that is lost and refreshed to the operating system cache.

Max_allowed_packet = 4 m
Thread_stack = 256 K
Table_cache = 128 K
Sort_buffer_size = 6 m
# The buffer size that can be used for sorting. Note: The allocated memory corresponding to this parameter is exclusive to each connection! If there are 100 connections, the total size of the actually allocated sort buffer is 100 × 6 = 600 mb. Therefore, we recommend that you set the size of a server with around 4 GB to 6-8 Mb.

Read_buffer_size = 4 m
# The buffer size that can be used by the read query operation. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection!

Join_buffer_size = 8 m
# The buffer size that can be used by the Joint query operation. The same as sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection!

Myisam_sort_buffer_size = 64 m
Table_cache = 512
# Opening a table may have a high overhead. For example, MyISAM marks the myi file header that the table is in use. You certainly do not want this operation to be too frequent. Therefore, you usually need to increase the number of caches so that the opened tables can be cached to the maximum extent. It requires the resources and memory of the operating system, which is of course not a problem for the current hardware configuration. If you have more than 200 tables, it may be appropriate to set it to 1024 (each thread needs to open the Table). If the number of connections is large, it will increase its value. I have seen a 100,000 error.

Thread_cache_size = 64
# The overhead of thread creation and destruction may be large because connection/disconnection is required for each thread. I usually set at least 16. If the application has a large number of skip concurrent connections and the value of threads_created is large, I will increase the value. It does not need to create a new thread in common operations.

Query_cache_size = 64 m
# Specify the size of the MySQL Query Buffer. You can run the following command on the MySQL console:

#> Show variables like '% query_cache % ';
#> Show status like 'qcache % ';
# If the qcache_lowmem_prunes value is very large, it indicates that the buffer is often insufficient. If the qcache_hits value is very large, it indicates that the query buffer is frequently used, if this value is small, it will affect the efficiency, you can consider not to query the buffer; qcache_free_blocks, if this value is very large, it indicates that there are many fragments in the buffer.

Tmp_table_size = 256 m
Max_connections = 768
# Specify the maximum number of connection processes allowed by MySQL. If the too connector connections error is frequently reported during Forum access, you need to increase the value of this parameter.

Max_connect_errorrs = 10000000
Wait_timeout = 10
# Specify the maximum connection time of a request. For servers with around 4 GB of memory, you can set it to 5-10.

Thread_concurrency = 8
# The value of this parameter is the number of server logical CPUs × 2. In this example, the server has two physical CPUs, and each physical CPU supports h.t hyper-threading, therefore, the actual value is 4x2 = 8.

Skip-networking
# Enabling this option can completely disable the MySQL TCP/IP connection mode. If the Web server accesses the MySQL database server remotely, do not enable this option! Otherwise, the connection will fail!

Show status command
Meaning:
Number of times the aborted_clients client has been disconnected illegally
Aborted_connects connection failure count
The number of times the com_xxx XXX command is executed. There are many
Number of connections from connections to MySQL
Created_tmp_disk_tables temporary table created on the disk
Created_tmp_tables temporary table created in memory
Created_tmp_files temporary files
Key_read_requests the number of requests to read a key block from the cache
Key_reads the number of physical reads of a key block from disk
Number of connections simultaneously used by max_used_connections
Open_tables open tables
Open_files files
Opened_tables Open Table
Number of queries submitted by questions to the server
Sort_merge_passes if the value is large, you should increase the sort_buffer value in my. CNF.
The number of seconds that the uptime server has been working on.

suggestions for improving performance:
1. if opened_tables is too large. table_cache in CNF becomes larger
2. if key_reads is too large. in CNF, key_buffer_size increases. you can use key_reads/key_read_requests to calculate the cache failure rate
3. if handler_read_rnd is too large, many of the SQL statements you write will scan the entire table without using the index key.
4. if threads_created is too large, add my. the value of thread_cache_size in CNF. you can use threads_created/connections to calculate the cache hit rate
5. if created_tmp_disk_tables is too large, add my. the value of tmp_table_size in CNF, which is replaced by a memory-based temporary table