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

This article mainly introduces you what Mysql performance tuning and testing methods, the content of the article is carefully selected and edited by the author, with a certain pertinence, for everyone's reference significance is still relatively great, the following with the author to understand the Mysql performance tuning and testing methods.

I. key indicators

In the database performance evaluation, there are several indicators are very important, using it to evaluate the ability of the database, not how critical they can play, but that they can clearly represent the ability of the database in some aspects.

1.IOPS

IOPS:Input/Output operation Per Second, the number of IO requests processed per second.

We know that IOPS O refers to the read and write ability of the disk, such as 300m read per second and 200m write per second, which is the data throughput (another key indicator of Imax O capability), but IOPS refers to the number of Imax O requests that can be processed per second.

If you want the IOPS system to respond quickly enough, then the higher the IOPS, the better, because IOPS is related to hardware, so at present, to improve IOPS, basically can only spell hardware. The traditional solution is to use multiple disks through RAID stripe to improve the read and write ability of IOPS. We can also use solid state disk SSD to improve IOPS, but the cost of solid state disk may be high.

2.QPS

QPS:Query Per Second, requests (queries) per second.

This parameter is very important and can visually reflect the performance of the system, just like IOPS measures how many requests a disk can receive per second.

We can execute the status command in MySQL command line mode, and the last line of output returned contains the QPS metric.

3.TPS

TPS:Transaction Per Second, transactions per second.

The TPS parameter MySQL is not provided natively. If we need to calculate it ourselves, we can use the calculation formula:

TPS = (Com_commit + Com_rollback) / Seconds

This formula has two state variables, which represent the number of commits and rollbacks, and Seconds is the time interval we define.

II. Key indicators of TPCC testing

TPCC-MySQL is a set of MySQL benchmark programs developed by Percona based on the TPCC specification, and we use this set of tools to test the first three important indicators.

Installation and use of 1.TPCC tools

For specific installation, you can take a look at these two blog posts: mysql stress testing tool tpcc-mysql installation testing, mysql performance testing-tpcc,TPCC can better simulate online business.

III. Optimization of database parameter configuration

If the database parameters are configured reasonably, the operation efficiency can be greatly improved, that is, to maximize the use of system resources.

1. Connection related parameters 1.1 max_connections

Max_connections: specifies the maximum number of concurrent connections on the MySQL server, with a value ranging from 10 to 100,000, with a default value of 151.15.

This parameter is important because it determines the maximum number of sessions that can connect to the MySQL service at a time. When setting this parameter, it is generally not a big problem to set the parameter value to 500 / 2000 according to the configuration and performance of the database cloud server.

1.2 max_connect_errors

Max_connect_errors: specifies the maximum number of attempts allowed to connect unsuccessfully, ranging from 1 to 2 ^ 64, with a default value of 100 in version 5.6.6.

Do not ignore this parameter. If the number of errors you try to connect exceeds the value specified in this parameter, the CVM will no longer allow new connections. Yes, it will reject connections. Although MySQL still provides services, it will not be able to create new connections. You can use FLUSH HOSTS to clear the status or restart the database service, but the cost is too high to do so, so the default value for this parameter is too small, and it is recommended to set it to an order of magnitude above 100000.

1.3 interactive_timeout and wait_timeout

Both of these parameters are related to the automatic timeout disconnection of the connection session, the former is used to specify the wait time before closing the interactive connection, and the latter is used to specify the wait time before closing the non-interactive connection, both in seconds, and the default value is 28800, that is, 8 hours.

1.4 skip-name-resolve

Skip-name-resolve: it can simply be understood as disabling DNS parsing. Note that this is the behavior of the server, where the client hostname is not checked when connecting, but only IP is used. If this parameter is specified, the HOST column must be an IP rather than a hostname when creating the user and granting permissions. It is recommended to enable this parameter, which is helpful to speed up the network connection, which is equivalent to skipping the resolution of the hostname.

1.5 back_log

Back_log: specifies the maximum number of connection requests stored in the MySQL connection request queue. Before version 5.6.6, the default is 50, and the maximum value is no more than 65535. After version 5.6.6, the default value is-1, which means that it is automatically adjusted by MySQL. In fact, there are rules for self-adjustment, that is, 50 + (max_connections/5).

This parameter is mainly used to deal with a large number of connection requests within a short period of time. If the MySQL main thread cannot assign (or create) a connection to each connection request in time, what to do? it cannot directly refuse, so it puts part of the request into the waiting queue to be processed. The length of the waiting queue is the parameter value of back_log. If the waiting queue is also full, then subsequent connection requests will be rejected.

two。 File related parameters 2.1 sync_binlog

Sync_binlog: specifies the flat rate of synchronous binary log files, which defaults to 0. 0.

If you want performance, specify the parameter as 0, and for security reasons, specify the parameter value as 1.

2.2 expire_logs_day

Expire_logs_day: specifies to set the lifecycle of binary log files. If the value exceeds, it will be deleted automatically. The parameter value is in days, and the value ranges from 0 to 99. The default value is 0. It is recommended to set this parameter between 7: 14 and keep it for one to two weeks.

2.2 max_binlog_size

Max_binlog_size: specifies the size of the binary log, which ranges from 4KB~1GB to 1GB by default.

3. Cache control parameter 3.1 thread_cache_size

Thread_cache_size: specifies the number of threads that MySQL caches for rapid reuse. The value ranges from 0 to 16384, and the default value is 0. 0.

In general, when the client interrupts the connection, in order to quickly create a successful connection, MySQL will put the connection interrupted by the client into the cache instead of immediately interrupting and releasing resources. In this way, when a new client requests a connection, it can be created successfully quickly. Therefore, it is best to keep a certain amount of this parameter, which is recommended to be set between 300 and 500. In addition, the hit rate of thread cache is also an important monitoring metric. The calculation rule is (1-Threads_created/Connections) * 100%. We can use this metric to optimize and adjust thread_cache_size parameters.

3.2 query_cache_type

Sql_cache means to put the query results in the query cache.

Sql_no_cache means that query results are not cached when querying.

Sql_buffer_result means that in a query statement, the query results are cached in a temporary table.

These three are just the right match for use. Sql_buffer_result will release the table lock as soon as possible so that other sql can execute as soon as possible.

Using the FLUSH QUERY CACHE command, you can clean up the query cache to make better use of its memory. This command does not remove any queries from the cache. FLUSH TABLES dumps to clear the query cache.

The RESET QUERY CACHE mission removes all query results from the query cache.

So how exactly does mysql decide whether or not to put the query results in the query cache?

The decision is based on the variable query_cache_type.

This variable has three values: 0meme1jin2, which represents off, on, and demand.

Mysql defaults to enabling on.

It means that if it is 0, then query cache is off.

If it is 1, the query is always looked up in the query cache first, even if sql_no_cache is used, because sql_no_cache simply does not cache the query results, not use the query results.

Select count (*) from innodb;1 row in set (1.91 sec) select sql_no_cache count (*) from innodb;1 row in set (0.25 sec)

If it is 2m demand.

Add a line to the my.ini

Query_cache_type=2

Restart the mysql service

Select count (*) from innodb;1 row in set (1.56 sec) select count (*) from innodb;1 row in set (0.28 sec)

Without using sql_cache, it seems that query caching is still used

Select sql_cache count (*) from innodb;1 row in set (0.28 sec)

The query time using sql_cache is the same, because sql_cache only puts the query results in the cache, and it will look up the data in the query cache first without using the sql_cache query.

Conclusion: as long as the query_cache_type is not closed, the sql query always uses the query cache, and if the cache misses, the execution plan of the query starts to query the data in the table.

Advantages and disadvantages of query cache

The advantage is obvious that for some frequent select query,mysql, the corresponding result set is returned directly from the cache without having to be fetched from the table table, which reduces the IO overhead.

Even if the benefits of query cache are obvious, some of its disadvantages should not be ignored:

Resource consumption caused by hash calculation and hash lookup of query statements. Mysql will perform hash calculation on each received select type query and then find out whether the cache of the query exists. Although the efficiency of hash calculation and search is high enough, the consumption caused by one query can be ignored, but when high concurrency is involved and there are thousands of query, the cost of hash calculation and search will be paid more attention.

Failure of query cache. If the table changes more frequently, the failure rate of query cache will be very high. A table change refers not only to a change in the data in the table, but also to any change in structure or index

Query for different sql but the same result set will be cached, which will cause excessive consumption of memory resources. Sql has different character case, spaces or comments, and the cache is considered to be a different sql (because their hash values will be different).

Unreasonable setting of relevant parameters will result in a large number of memory fragments, which will be described later.

Rational use of query cache

Query cache has its advantages and disadvantages. Reasonable use of query cache can make it give full play to its advantages and effectively avoid its disadvantages.

Not all tables are suitable for query cache. The main cause of query cache failure is that the corresponding table has changed, so you should avoid using query cache on table that changes frequently. There are two special sql hint:SQL_NO_CACHE and SQL_CACHE for query cache in mysql, which means that query cache is forcibly used and query cache is forcibly used. By forcing mysql not to use query cache, mysql can not use query cache on frequently changing tables, thus reducing memory overhead and hash computing and lookup overhead.

For more details on query cache, please see the original text here: mysql query cache Optimization

3.3 query_cache_size

Query_cache_size: specifies the size of the memory area used to cache the query result set, which should be an integral multiple of 1024.

This parameter cannot be too large or too small. The query cache at least needs the space of 40KB to be allocated to its own structure. When it is too small, caching the result set is meaningless, the hot spot data can not be saved much, and it is always flushed out quickly. However, it should not be too large, otherwise it may take up too much memory resources and affect the performance of the whole machine, and it is meaningless to say that it is too large, because even if the data is not refreshed, as long as the source data is changed, the data in the cache will automatically become invalid. in this case, most of the allocation is meaningless. Personally, it is recommended that the setting do not exceed 256MB.

3.4 query_cache_limit

Query_cache_limit: used to control the query cache. The maximum result set generated by a single SQL statement that can be cached is 1MB by default. Do not enter the query cache if you exceed it. This size is enough for many scenes. You can consider reducing it, but you don't need to increase it.

3.5 sort_buffer_size

Sort_buffer_size: specifies the size of the sort area that can be used by a single session. The default value is 256KB, and it is recommended to set it between 1~4MB.

3.6 read_buffer_size

Read_buffer_size: specify the size of the data cache for random reading. The default is 256KB, and the maximum 4GB is supported. Increasing this parameter appropriately will help to improve the efficiency of full table scanning.

Special parameters for 4.InnoDB 4.1 innodb_buffer_pool_size

Innodb_buffer_pool_size: specifies the size of the cache dedicated to the InnoDB engine, which is used to cache data and index information of table objects. The default value is 128MB, which can support a maximum of (2 ^ 64-1) B.

If you have a lot of transaction updates, inserts or deletions are very operational, you can save a lot of disk I / O by changing the innodb_buffer_pool size parameter.

Innodb_buffer_pool_size is a global parameter, and the cache allocated by it will be used by all accessed InnoDb table objects. If the table objects in the MySQL database are dominated by InnoDb, the larger the value of this parameter, the better. It is recommended in the official document that this parameter can be set to 70% and 80% of the physical memory of the CVM.

4.2 innodb_buffer_instances

Innodb_buffer_instances: specifies how many regions the InnoDB cache pool is divided into, ranging from 1 to 64, with a default value of-1, indicating that the InnoDB adjusts itself.

This parameter is valid only if the value of the innodb_buffer_pool_size parameter is greater than 1GB, so how to set this parameter? For personal feelings, you can refer to the size of the InnoDB cache pool, using GB as the unit, and specify an instances for each GB. For example, when innodb_buffer_pool_size is set to 16GB, you can specify that innodb_buffer_instances is set to 16.

5. Case of parameter optimization

The test CVM has the physical memory of 16GB. Assuming that the maximum number of connections is 500, and the table object uses the InnoDB storage engine, how to configure our memory parameters?

The specific configuration is as follows:

(1) first of all, reserve 20% of the memory for the operating system, which is about 3GB.

(2) several key parameters related to the thread are set as follows:

Sort_buffer_size=2m read_buffer_size=2m read_rnd_buffer_size=2m join_buffer_size=2m

When the number of connections is expected to peak, the thread expects to consume up to 500 * (2 / 2 / 2 / 2) = 4GB memory (theoretical maximum).

(3) the remaining space 16-3-4=9GB can all be allocated to the cache pool of InnoDB. The relevant parameters are set as follows:

Innodb_buffer_pool_size=9ginnodb_thread_concurrency=8innodb_flush_method=O_DIRECTinnodb_log_buffer_size=16minnodb_flush_log_at_trx_commit= 2 IV. MySQL system status

To understand what the MySQL service is currently doing, there is a very important and very commonly used command:

SHOW [FULL] PROCESSLIST

The SHOW PROCESSLIST command outputs each connected thread as a separate record.

There are similar sentences.

SHOW PROFILES and SHOW PROFILE can get the usage of resources during the session execution statement.

After reading the above about Mysql performance tuning and testing methods, many readers must have some understanding, if you need to get more industry knowledge and information, you can continue to follow our industry information column.

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.