Database performance test (MySQL)

Common database metrics include

TPS/QPS： Measuring throughput .（TPS： Transactions per second (TransactionPerSecond)、 Query rate per second QPS It is a measure of how much traffic a specific query server handles in a given period of time ）
response time ： Including average response time 、 Minimum response time 、 Maximum response time 、 Percentage of time, etc , The time percentage is of great significance , As before 95% The maximum response time for the request of ..
Concurrency ： Number of query requests processed simultaneously .

Why don't you ask me DB limit QPS/TPS

Why don't you ask me DB limit QPS/TPS
Reference resources URL: https://www.cnblogs.com/zhiqian-ali/p/6336521.html

Limit method ; We assume two extreme scenarios :
Extreme scenario 1 , all SQL They are all primary key equivalent queries .
Extreme scenario II , all SQL All are full table scans that cannot be indexed .
In these two scenarios, we can see the supporting capacity of the database at a glance , There will be a big difference between scene one and scene two . Of course , Our realistic business scenario , Between two extreme scenarios , At this time, it is difficult to simply and roughly say how much business the current instance can support , Because of lack of information input .

mysql View common performance commands

Reference resources URL: https://blog.csdn.net/zhangyunsheng11/article/details/80014457

1、 see mysql Version number ：select version();
2、 see mysql Storage engine ：show engines;
3、 see mysql Buffer pool size configuration ：show variables like 'innodb_buffer_pool_size' ;
Buffer pool size , It is generally set to the physical memory of the machine 80%
4、show variables like 'innodb_io%';
5、 The slow query ：show variables like '%slow%';
slow_query_log = on Open the slow query log , Execution time exceeded 2 Second is slow query
6、 maximum connection ：show variables like 'max_connections';
Query the maximum number of connections the server responds to (MySQL The maximum number of connections to the server in the past was )
show global status like 'Max_used_connections';
7、 Process usage ：show global status like 'Thread%';
Threads_created Represents the number of threads created , If you find that Threads_created If it's too much , indicate MySQL The server has been creating threads , It's also quite resource intensive , You can add thread_cache_size value , Query server thread_cache_size To configure ：
show variables like 'thread_cache_size';
8、 The query cache ：show global status like 'qcache%';
MySQL Query cache variable interpretation ：
　　Qcache_free_blocks： The number of adjacent memory blocks in the cache . A large number means there may be debris .FLUSH QUERY CACHE Defragments the cache , To get a free block .
　　Qcache_free_memory： Free memory in the cache .
　　Qcache_hits： Every time a query hits in the cache, it grows
　　Qcache_inserts： Increases each time a query is inserted . The miss ratio is the number of hits divided by the number of inserts .
　　Qcache_lowmem_prunes： The number of times the cache ran out of memory and had to be cleaned up to provide space for more queries . It's better to look at this number for a long time ; If this number is growing , It means that the debris may be very serious , Or there is little memory .( above free_blocks and free_memory Can tell you what kind of situation )
　　Qcache_not_cached： The number of queries that are not suitable for caching , Usually because these queries are not SELECT Sentence or used now() Functions like that .
　　Qcache_queries_in_cache： Queries currently cached ( And response ) The number of .
　　Qcache_total_blocks： The number of blocks in the cache .
Let's check the server again about query_cache Configuration of ：show variables like ‘query_cache%’;
Explanation of each field ：
　　query_cache_limit： Queries larger than this size will not be cached
　　query_cache_min_res_unit： The minimum size of the cache block
　　query_cache_size： Query cache size
　　query_cache_type： Cache type , Decide what queries to cache , In the example, it means no cache select sql_no_cache Inquire about
　　query_cache_wlock_invalidate： When other clients are working on MyISAM When a table is written , If the query is in query cache in , Whether to return cache The result is to wait for the write operation to complete and then read the table to get the result .
　　query_cache_min_res_unit The configuration of is a ” Double-edged sword ”, The default is 4KB, Large settings are good for big data query , But if your queries are all small data queries , It is easy to cause memory fragmentation and waste .
　　 Query cache fragmentation rate = Qcache_free_blocks / Qcache_total_blocks * 100%
　　 If the query cache fragmentation rate exceeds 20%, It can be used FLUSH QUERY CACHE Defragment cache , Or try reducing query_cache_min_res_unit, If your queries are small data .
　　 Query cache utilization = (query_cache_size - Qcache_free_memory) / query_cache_size * 100%
　　 Query cache utilization is 25% Here's a description query_cache_size Set too big , It can be reduced properly ; Query cache utilization is 80% Above and Qcache_lowmem_prunes > 50 The words of query_cache_size It may be a little small , Or there are too many pieces .
　　 Query cache hit rate = (Qcache_hits - Qcache_inserts) / Qcache_hits * 100%

9、 Sort usage ：show global status like 'sort%';
Sort_merge_passes There are two steps .MySQL First try sorting in memory , The amount of memory used is determined by the system variable Sort_buffer_size decision , If it's not big enough, read all the records into memory ,MySQL The result of each sort in memory will be saved in a temporary file , etc. MySQL After finding all the records , Sort the records in the temporary file again . This sort again will increase Sort_merge_passes. actually ,MySQL Another temporary file will be used to store the result of reordering , So you usually see Sort_merge_passes The increase is twice the number of temporary files created . Because temporary files are used , So the speed may be slow , increase Sort_buffer_size Will be reduced Sort_merge_passes and The number of times a temporary file was created . But blindly increasing Sort_buffer_size It doesn't necessarily increase speed
10、 Number of open files ：show global status like 'open_files';
show variables like 'open_files_limit';
More appropriate settings ：Open_files / open_files_limit * 100% <= 75%

11、 Watch lock condition ：show global status like 'table_locks%';
Table_locks_immediate Indicates the number of table locks released immediately ,Table_locks_waited Indicates the number of table locks to wait for , If Table_locks_immediate / Table_locks_waited > 5000, It's better to use InnoDB engine , because InnoDB It's the line lock and MyISAM It's a watch lock , For high concurrency write applications InnoDB It will be better .

MySQL The benchmark

About mysql5.7 Of 160 ten thousand qps
Reference resources URL: https://blog.51cto.com/weikle/1789595

Why do it mysql The benchmark

Mysql The benchmark
Reference resources URL: https://blog.csdn.net/fsx2550553488/article/details/80927933

For example, we got a server , Prepare to be a database server , How much access pressure can the hardware resources of this server support ？ After optimizing the kernel parameters of the operating system , Whether it improves performance ？ Adjust the Mysql After configuring the parameters , How much impact on performance ？……

Through a series of tuning work , Match benchmark , You can adjust this server to its best state , Also master the performance indicators under the healthy state .

Later in the actual operation , When the monitored data is close to the benchmark index , It means that the database server is almost full , It is the database structure design that needs to be analyzed 、SQL The usage of statements , Or hardware resources are indeed insufficient , And then do the corresponding processing .

The database server may also need a hardware upgrade , Benchmarking is also required after upgrading , Compare with the previous test results , Ensure that the upgraded performance is improved , Prevent improper upgrade or incorrect configuration from causing performance degradation .

Mysql Performance testing tools mysqlslap

mysqlslap,mysqlslap yes MySQL5.1.4 And then I brought it with me benchmark Benchmarking tools , The tool can simulate multiple clients simultaneously sending query updates to the server , Gives performance test data and provides performance comparison of various engines .

mysqlslap One of the main work scenarios of is to benchmark the database server .

sysbench

sysbench Is a multithreaded system pressure measurement tool . It can evaluate the performance of the system according to various factors affecting the performance of the database server . for example , Can be used to test files I/O、 Operating system scheduler 、 Memory allocation and transfer speed 、POSIX Threads , And database server .sysbench Support Lua Scripting language ,Lua The setting of various test scenarios can be very flexible .sysbench It is a universal testing tool we like very much , Support MySQL、 Hardware testing of operating system and hardware .（ Excerpt from 《 High performance MySQL》）

summary ： There are many articles using this tool online , It should be regarded as a mainstream tool , This tool is recommended .

Reference resources

Performance test mysql monitor 、 Optimize
Reference resources URL: https://www.cnblogs.com/xiaowenshu/p/10455309.html
MySQL Performance benchmark comparison ：5.7 VS 8.0
Reference resources URL: https://www.cnblogs.com/qcloud1001/p/10488735.html