Shulou(Shulou.com)06/01 Report--

Recently, I was working on a report system, which was not very responsible, but the amount of data was relatively large, writing tens of thousands of data at a time, plus in the same transaction, after being deployed to the linux server, I found that it was particularly slow, and later found that there was no optimized configuration file after mysql installation, resulting in a particularly slow write. Server mysql version:

[root@localhost ~] # mysql-Vmysql

Ver 14.14 Distrib 5.6.25, for linux-glibc2.5 (x86 / 64) using EditLine wrappermysql

Default path of configuration file: vim / etc/my.cnf

Restart after configuration: service mysql restart

If the restart reports an error, the wrong configuration is added to the configuration file

Shutting down MySQL.... SUCCESS! Starting MySQL.... ERROR! The server quit without updating PID file (/ data/mysql/dbdata/report.pid).

For the current simple configuration information, it takes at least 20 times faster than the previous time for batch processing (see the successful time submitted on the page here). Here is the configuration information. For each description, please refer to Baidu:

Lower_case_table_names=1wait_timeout=2880000

Interactive_timeout = 2880000

Max_allowed_packet = 200

Mmax_connections = 700,

Open-files-limit = 10240 # 20180607 if error delete this##

Key_buffer_size=256

Mback_log = 512

Table_open_cache=2048

Innodb_file_per_table= 1

Innodb_buffer_pool_size = 2048m

Innodb_log_file_size = 1024m

Innodb_log_buffer_size = 16m

Innodb_flush_log_at_trx_commit = 2

Innodb_flush_method = 'Olympus direct'

Innodb_max_dirty_pages_pct = 90 #

Use ulimit-an or ulimit-n to view the maximum number of open files currently set by linux. If you want the value of ulimits to take effect permanently, you must modify the configuration file / etc/security/limits.conf to add * soft nofile 65535 * hard nofile 65535 to the configuration file. Restart discovery will take effect.

Https://www.cnblogs.com/pangguoping/p/5791432.html

To query the parameters, please refer to:

Https://www.2cto.com/database/201711/695306.html

The following is the description of the mysql parameters written by the online blog

Https://www.cnblogs.com/musings/p/5913157.html

Let's take a look at some of the basics of MySQL optimization. I have two parts to optimize MySQL.

One is the optimization of the physical hardware of the server, and the other is the optimization of MySQL itself (my.cnf).

I. the influence of server hardware on the performance of MySQL

① disk seek capability (disk Imango O), take the current high-speed SCSI hard disk (7200 rpm / s) as an example, this kind of hard disk theoretically seeks 7200 times per second, which is determined by physical characteristics and cannot be changed. MySQL does a lot of complex query operations every second, so you can imagine the amount of reading and writing to the disk. Therefore, it is generally considered that disk IMySQL O is one of the biggest factors restricting the performance of Discuz, for which the average daily traffic is more than 1 million Discuz! Forum, due to the restriction of disk Imax O, the performance of MySQL will be very poor! To address this constraint, consider the following solutions: use RAID-0+1 disk arrays and be careful not to try to use RAID-5,MySQL. The efficiency on RAID-5 disk arrays will not be as fast as you might expect.

For ② CPU, S.M.P is recommended for MySQL applications. Multi-channel symmetric CPU of the architecture, for example, you can use two Intel Xeon 3.6GHz CPU, now I recommend using a 4U server specifically as a database server, not just for mysql.

③ physical memory for a Database Server using MySQL, it is recommended that the server memory is not less than 2GB, and it is recommended to use physical memory above 4GB. However, memory is a negligible problem for current servers. When you encounter a high-end server, the memory is basically more than 16 GB.

Second, MySQL's own factors after solving the above server hardware constraints, let's take a look at how the optimization of MySQL itself operates. The optimization of MySQL itself is mainly to optimize and adjust the parameters in its configuration file my.cnf. Let's introduce some parameters that have a great impact on performance. Because the optimized settings of the my.cnf file are closely related to the server hardware configuration, we specify a hypothetical server hardware environment:

Next, we will explain the above hardware configuration combined with an optimized my.cnf:

# vim / etc/my.cnf the following lists only the contents of the [mysqld] paragraph in the my.cnf file, and the contents of other paragraphs have little impact on the performance of MySQL, so ignore it for the time being.

[mysqld]

Port = 3306serverid = 1

Socket = / tmp/mysql.sockskip-locking# avoids the external locking of MySQL, reduces the probability of error and enhances stability.

Skip-name-resolve# prohibits MySQL from DNS parsing external connections, and using this option eliminates the time it takes for MySQL to parse DNS. It should be noted, however, that if this option is turned on, all remote host connection authorizations must use IP addresses, otherwise MySQL will not be able to handle connection requests properly! The value of the back_log = 384#back_log parameter indicates how many requests can be stored on the stack in a short period of time before MySQL temporarily stops responding to new requests. 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 their own limits on the queue size. Trying to set the limit that back_log is higher than your operating system will not be valid. The default value is 50. It is recommended to set an integer less than 512 for Linux systems. Key_buffer_size = 256M#key_buffer_size specifies the size of the buffer used for indexing, and increasing it results in better index processing performance. For servers with memory around 4GB, this parameter can be set to 256m or 384m. Note: the excessive setting of this parameter value will reduce the overall efficiency of the server! Max_allowed_packet = 4m

Thread_stack = 256K

Table_cache = 128K sort_buffer_size = the size of the buffer that can be used when sorting 6M# queries. Note: the allocated memory for this parameter is exclusive per connection. If there are 100 connections, the actual total sort buffer size allocated is 100x6 = 600MB. Therefore, it is recommended to set it to 6-8m for servers with about 4GB memory. Read_buffer_size = the buffer size that can be used by 4M# read query operations. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection. Join_buffer_size = the size of the buffer that can be used by the 8M# federated query operation. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive per connection. Myisam_sort_buffer_size = 64m

Table_cache = 512

Thread_cache_size = 64

Query_cache_size = 64M# specifies the size of the MySQL query buffer. You can observe in the MySQL console that if the value of Qcache_lowmem_prunes is very large, it indicates that the buffer is often insufficient; if the value of Qcache_hits is very large, query buffering is used very frequently, and if the value is small, it will affect the efficiency, so you can consider not using query buffering; Qcache_free_blocks, if the value is very large, it indicates that there are a lot of fragments in the buffer. Tmp_table_size = 256m max_connections = 76connections specifies the maximum number of connected processes allowed by MySQL. If there are frequent Too Many Connections errors when visiting the forum, you need to increase the value of this parameter.

Max_connect_errors = 10000000

Wait_timeout = 1 requests specifies the maximum connection time for a request, which can be set to 5-10 for servers with memory around 4GB.

Thread_concurrency = "this parameter is the number of logical CPU of the server * 2. In this example, the server has two physical CPU, and each physical CPU supports H.T hyperthreading, so the actual value is 4*2=8skip-networking#. Enable this option to completely disable the TCP/IP connection mode of MySQL. Do not enable this option if the WEB server accesses the MySQL database server by remote connection!" Otherwise, you will not be able to connect properly! The larger the table_cache=1024# physical memory, the larger the setting. The default is 2402, and the best innodb_additional_mem_pool_size=4M# is 2m when adjusted to 512-1024.

If innodb_flush_log_at_trx_commit=1# is set to 0, it will be stored uniformly after the innodb_log_buffer_size queue is full. Default is 1 innodb_log_buffer_size=2M#. Default is 1m.

Innodb_thread_concurrency=8# your server CPU can be set to several. It is recommended that the default is 8.

Key_buffer_size=256M# is 218 by default, and 128 is the best setting.

Tmp_table_size=64M# defaults to 16m, and it is best to set it to 64-256m.

Read_buffer_size=4M# defaults to 64K

Read_rnd_buffer_size=16M# defaults to 256k

Sort_buffer_size=32M# defaults to 256k

Thread_cache_size=120# defaults to 60

If query_cache_size=32M starts from the database platform application, I will still choose myisam. PS: some people may say that your myisam cannot resist too many writes, but I can make up for it through the architecture. Let me talk about the capacity of the database platform I currently use: the total amount of master and slave data is more than a few hundred tons, more than one billion pv of dynamic pages per day, and several large projects are called through data interfaces without counting the total number of pv. (this includes a large project that did not deploy memcached initially, resulting in a single database handling 90 million queries per day.) On the other hand, the average load of my overall database server is about 0.5-1.

MyISAM and InnoDB optimization:

Key_buffer_size-this is very important for MyISAM tables. If you just use the MyISAM table, you can set it to 30-40% of the available memory. Reasonable values depend on index size, data volume, and load-remember that MyISAM tables use the operating system's cache to cache data, so you need to set aside some memory for them, and in many cases the data is much larger than the index. However, you need to always check to see if all key_buffer is being utilized-it is rare that .MYI files only have 1GB and key_buffer is set to 4GB. What a waste to do this. If you rarely use MyISAM tables, keep key_buffer_size below 16-32MB to accommodate temporary table indexes given to disk.

Innodb_buffer_pool_size-this is very important for Innodb tables. Innodb is more sensitive to buffering than MyISAM tables. MyISAM can run in the default key_buffer_size setting, while Innodb is like a snail in the default innodb_buffer_pool_size setting. Because Innodb caches both data and indexes, it does not need to leave too much memory to the operating system, so you can set up up to 70-80% of its available memory if you only need to use Innodb. Some of the rules that apply to key_buffer are-if you don't have a large amount of data and don't burst, you don't need to set the innodb_buffer_pool_size too large.

Innodb_additional_pool_size-this option does not have much impact on performance, at least on operating systems that have almost enough memory to allocate. But if you still want to set it to 20MB (or more), you need to see how much other memory Innodb needs to allocate.

Innodb_log_file_size is important in the case of high write loads, especially large datasets. The higher this value, the higher the performance, but be aware that recovery time may be increased. I often set it to 64-512MB, depending on the server size. The default setting of innodb_log_buffer_size can also provide server performance in the case of medium write loads and short transactions. If there is a peak update operation or a high load, you should consider increasing its value. If its value is set too high, memory may be wasted-it is refreshed every second, so there is no need to set the memory space required for more than 1 second. Usually 8-16MB is enough. The smaller the system, the smaller the value.

Does innodb_flush_logs_at_trx_commit mean that Innodb is 1000 times slower and its head is bigger than MyISAM? Maybe you forgot to change this parameter. The default value is 1, which means that each committed update transaction (or statements other than each transaction) is flushed to disk, which is quite resource-intensive, especially if there is no battery spare cache. Many applications, especially those transitioned from MyISAM, can simply set its value to 2, that is, instead of flushing logs to disk, they will only flush them to the operating system's cache. Logs are still flushed to disk every second, so you don't usually lose the cost of 1-2 updates per second. Setting it to 0 is much faster, but it's also relatively insecure-some transactions are lost when the MySQL server crashes. Set to 2 to direct the part of the transaction that is lost and flushed to the operating system cache.

Table_cache-opening a table can be expensive. For example, MyISAM marks the MYI file header as the table is in use. You certainly don't want this to happen too often, so you usually need to increase the number of caches to maximize the cache of open tables. It requires operating system resources and memory, which is certainly 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), and increase its value if the number of connections is large. I've seen it set to 100000.

Thread_cache-Thread creation and destruction can be costly because each thread needs to be connected / disconnected. I usually set it to at least 16. If there are a large number of jump concurrent connections in the application and the value of Threads_Created is large, then I will increase its value. Its purpose is that there is no need to create new threads in normal operations.

Query_cache-this is useful if your application has a lot of reads and no application-level caching. Don't set it too big, because it takes a lot of overhead to maintain it, which can cause MySQL to slow down. The common setting is 32-512Mb. After setting up, it is best to track it for a period of time to see if it is working well. Under a certain load pressure, if the cache hit ratio is too low, enable it. Sort_buffer_size-if you only have a few simple queries, you don't need to increase its value, even though you have 64GB memory. It may degrade performance.

Transferred from: https://blog.csdn.net/cuker919/article/details/80610314

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.