MySQL Performance Test-preliminary knowledge reserve

Today is Women's Day. I wish all female compatriots a happy holiday. :) MySQL performance testing will be conducted soon. So I have some knowledge about MySQL testing in advance. This section mainly describes performance testing tools, key MySQL indicators, and basic Benchmark data to prepare test cases and scenarios. Let's talk about the indicators to consider (reprinted

Today is Women's Day. I wish all female compatriots a happy holiday. :) MySQL performance testing will be conducted soon. So I have some knowledge about MySQL testing in advance. This section mainly describes performance testing tools, key MySQL indicators, and basic Benchmark data to prepare test cases and scenarios. Let's talk about the indicators to consider (reprinted

Today is Women's Day. I wish all female compatriots a happy holiday first :)

MySQL performance testing is coming soon. So I have some knowledge about MySQL testing in advance. This section mainly describes performance testing tools, key MySQL indicators, and basic Benchmark data to prepare test cases and scenarios.

Let's talk about the metrics (reposted, the website cannot be found, sorry)

(1) QPS (queries per second)
QPS = Questions (or Queries)/seconds
Mysql> show/* 50000 global */status like 'Question ';
(2) TPS (transaction volume per second)
TPS = (Com_commit + Com_rollback)/seconds
Mysql> show status like 'com _ commit ';
Mysql> show status like 'com _ rollback ';
(3) key Buffer hit rate
Key_buffer_read_hits = (1-key_reads/key_read_requests) * 100%
Key_buffer_write_hits = (1-key_writes/key_write_requests) * 100%
Mysql> show status like 'key % ';
(4) InnoDB Buffer hit rate
Innodb_buffer_read_hits = (1-innodb_buffer_pool_reads/innodb_buffer_pool_read_requests) * 100%
Mysql> show status like 'innodb _ buffer_pool_read % ';
(5) Query Cache hit rate
Query_cache_hits = (Qcahce_hits/(Qcache_hits + Qcache_inserts) * 100%;
Mysql> show status like 'qcache % ';
(6) Number of Table Cache statuses
Mysql> show status like 'open % ';
(7) Thread Cache hit rate
Thread_cache_hits = (1-Threads_created/connections) * 100%
Mysql> show status like 'thread % ';
Mysql> show status like 'connections ';
(8) Lock status
Mysql> show status like '% lock % ';
(9) replication latency
Mysql> show slave status
(10) Tmp Table Status (temporary Table status)
Mysql> show status like 'create _ tmp % ';
(11) Binlog Cache Usage
Mysql> show status like 'binlog _ cache % ';
(12) Innodb_log_waits volume
Mysql> show status like 'innodb _ log_waits ';

QPS and TPS are naturally the key performance indicators. Other indicators can be listed as reference data for each test. If you encounter a bottleneck, you may also need to consider the cpu, network, and disk utilization of the system at that time. Analyze the problem.

In terms of tools, the first choice is mysqlslap, a test tool provided by MySQL.

./mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb --debug-info -uroot -proot

A simple example shows the usage of the tool,

-A automatically generates an SQL statement
-- Concurrency = 50,100 execute 50,100 concurrency respectively,
-- Iterations = 5: Execute five times, and use the myisam and innodb engines for testing respectively.

-- Number-of-queries Limit each client to this number of queries (this is not
Exact ). (the maximum number of test SQL statements executed by a client. You can use -- only-print to check the execution status of automatically generated SQL statements. This limit is the number of SQL statements other than prepared data)
-- Engine = myisam and innodb respectively use myisam and innodb engines for testing.

Of course, you can also use-q to specify the SQL script you want to test. After the test is completed, mysqlslap will provide the relevant data for your test.

For more information about mysqlslap parameters, see: http://dev.mysql.com/doc/refman/5.1/en/mysqlslap.html
At present, it is easier to get started. It is easy to calculate tps, qps, and other indicators by the number and time of queries executed.

SysBench

It is also a benchmark tool mentioned on the MySQL website. It is only difficult to install a 64-bit system. Due to the network environment and considering its functions and the upcoming test scenario, we will temporarily give up.

Performance testing Basics

When thinking about and verifying how to use mysqlslap to plan the test scenario, we will also record some discrete details here. Which one may be helpful someday.

Enable MySQL general_log to record the executed SQL statements

By default, this attribute is disabled. After enabling set global general_log = ON, you can view the SQL records executed by the database in the log file. This requirement is for OneCoder to check whether the batch insert commit method of JDBC is generated when multiple insert statements are executed in the database and then commit is a large list of insert values.

Comparison of multiple insert statements and one insert large values list (values (), (), () for batch data insertion, performance difference

From the current test results through mysqlslap, the latter is much better than the former. The average time of the first 1000 items is about 0.2 ms, while that of the latter is about ms. The idea of this test is to use mysqldump to find out how the SQL file is generated and think of the processing method of the data migration tool from oracle to mysql.

A big misunderstanding about the write performance bottleneck

Once upon a great mistake, we thought that the batchcompute insert data that mysql can process in a single thread is the bottleneck of this node. Therefore, we only think that the optimization of multi-thread concurrent writing is only effective in the NDB environment. Today, when I looked at some benchmark chart curves of MySQL, I suddenly woke up. The concurrency and tps curves are parabolic, which means they are within the range of a certain number of concurrency, TPS is significantly improved. As a result, the previous JDBC code was used to add several threads and write data to the same mysql concurrently. The TPS doubled.

There are so many summary today, because it is an afterthought summary that may not be missed. Warm welcome and guidance.

Original article address: MySQL Performance Test-preliminary knowledge reserve. Thank you for sharing it with me.