MySQL configuration file mysql.ini parameters, MySQL performance optimization

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 configuration file mysql.ini parameters, MySQL performance optimization