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

Some thoughts on High concurrency Optimization configuration of mysql

There are many high concurrency optimization configuration solutions for mysql, but few are suitable for you. Our optimization of the database is nothing more than to deal with the high concurrency of mysql. With the arrival of big data's era and the increase in the number of network users, many enterprises may deal with millions, tens of millions, or even hundreds of millions of PVs every day, which is already more than the ordinary configuration of mysql, so to deal with the increasing amount of pv, we need to make corresponding countermeasures to mysql, further optimize mysql, achieve our desired results, and prevent mysql downtime caused by high concurrency. By debugging and optimizing mysql, we can effectively deal with these situations.

Let's talk about some optimization schemes about mysql. For reference only, everyone's actual situation may be different, but in general, you can try such optimization.

1. Mysql read-write separation based on redis.

For redis-based cache processing optimization, it is not very complicated, for operators, you only need to install redis and debug it, about how redis calls mysql (it seems to need java to write a script), that is a matter for development. Redis-based read-write separation optimization is not very easy to talk about, here I will post a picture, and then talk about it. As shown in figure 1-1:

Figure 1-1 mysql read-write separation based on redis

From the figure above, we can see that that's what the redis call is all about. First, it sends the request message (read or write) by the user. However, in the case of redis, depending on the parsing of the diagram, there can be the following.

1.1.The redis response request process.

Read: the process of reading may be a little more complicated. The user will first read the redis database, and then return the request result to client;. If the user does not read the request result in redis, he will directly escape redis and read it directly to mysql, and then redis will copy the data locally.

Write: the process of writing is a little easier. The user writes directly to redis, and then redis caches it on mysql. You may find that redis is basically working in the whole process, and mysql seems to have nothing to do with it. That's right, that's what we want. After any request is handed over to redis, are you afraid that mysql won't be able to respond?

1.2. redis configuration optimization

Redis configuration, the main thing is to do persistent configuration, master-slave replication, as well as some security configuration, about this, I have a picture above, as for the process configuration I do not write, we probably have an idea.

1.3.Optimum configuration of mysql (master-slave replication)

Mysql does master-slave replication, how to say, mainly for security, generally speaking, there are two slave in master, which is enough to deal with a lot of unexpected situations. Do master-slave replication and backup, it should be noted that the configuration of the master-slave mysql is the same, for the backed up data, do not put in the mysql directory, to another path, and give mysql permission. For configuration, please refer to mysql master-slave replication configuration.

1.4.The mysql monitoring system

Personally, I think it is necessary to monitor Mysql. First of all, we can monitor the status of mysql when no one is on duty. Through monitoring, we can not only alarm the load of mysql, but also optimize the performance of mysql itself. Therefore, in the monitoring of mysql, I use zabbix to alarm the load, combined with pmm-server to optimize the mysql system. For pmm-sercer, post a picture here, as shown in figs. 1.2,1.3

Figure 1.2 mysql resource data graph

Figure 1.3 mysql resource data graph

As can be found from the above figure, we can generally see the resource configuration of mysql. According to the numerical values, we can properly optimize and adjust some mysql's own parameters, which is the advantage of pmm-sever monitoring.

For zabbix, I now use it for alarm processing. In the monitoring of mysql, zabbix's own template and combined with percona plug-in basically realize the monitoring of the entire mysql (with reference to percona monitoring mysql data), because the two are combined to achieve a more comprehensive monitoring of mysql. As shown in figure 1.4, you can see the monitoring items provided by the template.

Figure 1.4 mysql template monitoring items for zabbix

Summing up the above, we can find that a good mysql optimization architecture needs to be done, including redis read-write separation, mysql master-slave, monitoring system improvement and so on. If you still have a good solution, remember to share it. Next, we will optimize the performance of mysql itself, × × energy fault tolerance rate, and further improve the performance of mysql.

2. Optimization of mysql itself

Generally speaking, it is more affected by its own factors, we can modify the my.cnf configuration file to further optimize mysql. We can modify the parameters of mysql to make mysql have more reliable performance. The following is my database configuration, through Baidu Google, find a lot of configuration options resolution (configuration for mysql5.5 or above version), and then summarize. I hope it helps you. (note that the optimized configurations are all configured under the [mysqld] option, so don't be mistaken for [mysql])

[mysqld] back_log = 300binlog_format = MIXEDcharacter-set-server=utf8mb4long_query_time = 1log-bin=/databack/data_logbin/mysql_binloginnodb_log_file_size=2Ginnodb_log_buffer_size=4Minnodb_buffer_pool_size=4G#innodb_file_per_table = ONinnodb_thread_concurrency=8innodb_flush_logs_at_trx_commit=2#innodb_additional_mem_pool_size=4Mjoin_buffer_size = 8Mkey_buffer_size=256Mmax_connections = 1000max_allowed_packet = 4Mmax_connect_errors = 10000myisam_sort_buffer_size = 64Mport = 3306query_cache_type=1query_cache_size = 64Mread_buffer_size=4Mread_rnd_buffer_size=4Mserver-id = 1skip-external-lockingslow_query_log = 1 # skip-name-resolve#skip-networkingsort_buffer_size = 8Msocket = / tmp/mysql.socktable_open_cache=1024thread_cache_size = 64thread_stack = 256Ktmp_table_size=64Mwait_timeout = 10

The following is the parsing of the above configuration:

Back_log = 300: the value of this parameter indicates that when the connection data of MySql reaches # max_connections, 300 requests can be stored in the stack shortly before it temporarily stops responding to new requests, that is, new requests will be stored in the stack, waiting for a connection to release resources. The number of stacks is back_log, and mysql will respond to other requests after they have been processed. If the number of waiting connections exceeds # back_log, the connection resource will not be granted. You can set your back_log reasonably, but this value is not higher than the limit of the operating system. The default value for the system is 50. Linux systems generally set integers less than 512.

Binlog_format = MIXED: when configuring master-slave mode, select synchronous mode. Mysql master-slave replication can have three types of replication, namely: statement replication STATEMEN, row replication ROW and mixed type replication MIXED. Statement replication, as the name implies, is the SQL statement executed on the master server, executing the same statement on the slave server, and line replication is copying the changed content. Instead of executing the command on the slave server. Statement-based replication is used by default. Once it is found that statement-based replication cannot be accurately replicated, row-based replication is used. The replication type can be configured on the configuration file through binlog_format =.

Character-set-server=utf8mb4: utf-8 encodes 2-byte, 3-byte, 4-byte characters, but MySQL's utf8 encoding only supports 3 bytes of data, while mobile emoji data is 4 bytes. If the facial expression data is inserted directly into the database encoded by utf-8, the utf8mb4 coding of reporting SQL exceptions in the Java program is a superset of utf8 coding, compatible with utf8, and can store 4 bytes of emoji characters. The advantage of using utf8mb4 coding is that when storing and obtaining data, there is no need to consider the encoding and decoding of emoji characters.

Long_query_time = 1: set the slow query response time and record SQL execution statements for more than 1 second.

Log-bin=/databack/data_logbin/mysql_binlog: set the storage path for binary logs. If the system is not set, it will be stored in the mysql directory by default. It is recommended to create a new directory to store binary logs. The directory is not the same as the database, and the owner of the directory is mysql.

Innodb_log_file_size=2G: this is important in high write loads, especially in large datasets. The higher the value, the higher the performance, depending on the size of the server. This is the size of the redo log. Redo logs are used to ensure that writes are fast and reliable and recover in the event of a crash. The total size of the MySQL 5.5 redo log is limited to 4GB (there can be 2 log files by default). MySQL 5.6can be set to allow more than 4G. You can set it to 4G in the first place. The setting of this value can actually be calculated. You can look at the value of Innodb_os_log_written by the output of SHOW GLOBAL STATUS. Divide this value by 1024log 1024 to get the size of redo logs processed per minute, and then multiply it by 60 to get the size of logs processed per hour, because in versions above 5.5, there are two log redo log files ib_logfile0 and ib_logfile1 by default, and the result is divided by 2. Then round it up and it's time for your redo to be set.

Innodb_log_buffer_size=4M: the default is 1m, and server performance is OK under the default setting of medium write load and short transactions. If there is a peak update operation or a high load, you should consider increasing its value. InnoDB does not write the changed log to disk before the transaction commits, so in large transactions, it can reduce the pressure on disk Ibino. In general, 4MB-8MB is sufficient if you don't write a large amount of very large binary data.

Innodb_buffer_pool_size=4G: this configuration is very important for the Innodb table. The main function of this parameter is to cache the index, data and buffer when inserting data of the innodb table. Since Innodb caches both data and index, you can set up to 60-80% of the available memory when configuring this parameter (the official website is recommended and about 80% of the system memory). A buffer pool is where data and indexes are cached, which ensures that you use memory instead of a hard disk for most read operations. The general configuration values are 5-6GB (8GB memory), 19-25GB (32GB memory), and 38-50GB (64GB memory) for reference only.

# innodb_file_per_table = ON: in 5.6. the default value of this option property is ON, which you need to set to ON in previous versions because of the impact on the newly created table. This setting tells InnoDB whether the data for all tables needs to be placed in a separate .ibd file, which has the advantage that each table has its own separate tablespace. The data and indexes of each table are stored in its own tablespace. It is also realized that a single table can be moved in different databases, and the space can be recycled.

Innodb_thread_concurrency=8: the number of server logical threads can be set to the same number as the system, and the parameters can be configured to twice the number of logical CPU.

The system CPU view command is as follows:

Check the number of logical CPU:

# cat / proc/cpuinfo | grep "processor" | sort-u | wc-l

Check the number of physical CPU:

# cat / proc/cpuinfo | grep "physical id" | sort-u | wc-l

Check the number of cores per physical CPU:

# cat / proc/cpuinfo | grep "cpu cores" | uniq

Innodb_flush_logs_at_trx_commit=2: the default value of the system is 1, but this setting will cause commit update transactions to be flushed to disk, resulting in resource consumption. So you need to set the value to 2 so that you don't have to flush the log to disk, but only to the operating system's cache. However, it can also be set to 0, which is fast, but it also makes it relatively insecure, causing some transactions to be lost when the MySQL server crashes. The setting of 2 is just enough to make up for it.

# innodb_additional_mem_pool_size=4M: this parameter defaults to 1m to properly adjust the size of the parameter to ensure that all data can be stored in memory to improve access efficiency. It is mainly used to store the internal directory of Innodb. This value does not need to be assigned too large, and the system can adjust it automatically. It can be ignored in mysql5.6.3.

Join_buffer_size = 8m: indicates the size of the buffer that can be used by # federated query operations.

Key_buffer_size=256M: specify the size of the index buffer, which determines the speed of index processing. You can set it to 1max 4 of the system's physical memory, which is mainly aimed at the MyISAM engine, but the setting size should not exceed 4G, or there will be problems.

Max_connections = 1000: set the maximum connection of MySQL and set it appropriately according to your actual situation. If you often see a 'Too many connections' error, it is because the max_connections value is too low, so you need to set a higher number of links. If the max_ connection value is set to high, the defect is that the server will become unresponsive when the server runs an active transaction that exceeds the set threshold or higher.

Max_allowed_packet = 4m: this parameter mysql message buffer size, if this is too small may affect some operations, the default is 1m, generally set to 4-16m.

Max_connect_errors = 10000: indicates that if an incorrect connection is interrupted by the same host whose parameter values exceed the number of parameter values, the host will be disabled from connecting. To unblock the host, execute: FLUSH HOST.

Myisam_sort_buffer_size = 64m: this parameter defaults to 8m, which indicates the buffer required for reordering when the MyISAM table changes. 64m is generally sufficient.

Port = 3306: indicates that 3306 is used as mysql startup port

Query_cache_type=1: indicates the type of control cache. Three optional parameters (0, 1, 2) are set to 0, which means that the cache is not applied, which is equivalent to disabling it. Setting it to 1 means caching all results, and setting it to 2 means caching only the queries specified by SQL_CACHE in the select statement.

Query_cache_size=32M: parameter indicates the buffer size of mysql query results. It is generally not recommended to set it too large, because setting it too much will increase the overhead. Generally, you can set it to 32M-256M or so, and set the parameter to a multiple of 2.

Read_buffer_size=4M: indicates the buffer size that can be used for sequential query operations, including read and query operations. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive for each connection, and it is generally not recommended to be too large. For servers with 4G to 16G memory, 2M-8M is fine.

Read_rnd_buffer_size=4M: indicates the random read buffer size of the MySQL. When rows are read in any order, a random read buffer is allocated, and when sorting a query, a random buffer is allocated as the buffer size for the operation, as well as for servers with 4G to 16G memory 2M-8M.

Server-id = 1: the serverid defined by master-slave synchronization. As a master, the server_id must be smaller at the slave end, and the smaller the server_id, the higher the priority. However, the same sever_id is not allowed for mysql services within the same IP address range. The reference range of the parameters can be set (1-200).

Skip-external-locking: enable this option to avoid external locking of MySQL, reduce the probability of error and enhance stability. It is suitable for single-server environment.

Slow_query_log = 1: enable slow log, which works on slow log. As the name implies, slow log is used to query slow log.

Skip-name-resolve: prevents MySQL from doing DNS parsing for external connections. Use this option to eliminate the time MySQL takes for DNS parsing. 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 process connection requests properly.

Skip-networking: enable this option to completely disable the TCP/IP connection mode of MySQL. If the WEB server accesses the MySQL database server by remote connection, do not enable this option, otherwise you will not be able to connect properly.

Sort_buffer_size = 8m: indicates the size of the buffer that can be used when sorting the query. It is directly related to the number of real-time connections, and the number of real-time connections multiplied by the size of the sort_buffer_size is the actual total sort buffer size allocated. Therefore, for servers with memory around 4GB-8G, it can be set to 6-16m.

Socket = / tmp/mysql.sock:mysql.sock file is mainly used to connect server and client on the same server. When using a local connection, socket will be used to connect. The file is usually placed under / var/lib/mysql/mysql.sock, and ln-s is often used to make a soft connection in the / tmp directory.

Table_open_cache=1024:table_cache is mainly used 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. You can check the open_files_limit parameter by calling show variables like'% open%';. In an environment that uses a lot of MyISAM, you should make sure that the open_files_limit table type is at least two to three times that of table_cache, which is best adjusted to 512-1024.

Thread_cache_size = 64: the value of this variable indicates that the number of threads saved in the cache can be reused. If there is space in the cache when disconnected, the client thread will be placed in the cache. If the thread is re-requested, the request will be read from the cache. If the cache is empty or new, the thread will be recreated, if there are many new threads. Increasing this value can improve system performance. By comparing the variables of Connections and Threads_created status, you can see that the role of this variable can be configured according to the physical memory setting rules. 2G-4G can be set to around 16-64. Of course, for servers larger than 4G, setting 64 is enough.

Thread_stack = 256K: indicates that when each connection thread is created, the amount of memory allocated to it by MySQL can be set to 256K for 8-16G servers, and a larger one can be increased appropriately.

Tmp_table_size=64M: defines the size of a temporary table, which defaults to 16m. It can be adjusted to 64-256m. It is best for threads to monopolize. Too much memory may cause a blockage of Ibind O. If dynamic pages can be scaled up appropriately.

Wait_timeout = 100: specify the maximum connection time for a request. If this value is too high, a large number of SLEEP processes in MySQL cannot be released in time, which is a drag on system performance. However, do not set this reference too small, otherwise you may encounter problems such as "MySQL has gone away". The system defaults to 8 hours, which feels too big, so you can set a small point.

3. Summary

To prevent the occurrence of Mysql disease is something that every enterprise has to face. The arrival of big data makes the performance requirements of mysql higher, so it is urgent to optimize and upgrade mysql. The above is my summary, only to provide reference, hope to help you.

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.