Stress testing of single node database

Mysqlslap is MySQL's own benchmark tool, the advantages: query data, simple syntax, flexible and easy to use. The tool can simulate multiple clients concurrently issuing query updates to the server. The performance test data is given and the performance comparisons of various engines are provided. MYSQLSLAP provides an intuitive verification basis before and after MySQL performance optimization.

Can simulate a large number of clients operating the database at the same time, through the results of information to understand the performance of the database.

A major work scenario for MYSQLSLAP is benchmarking the database server.

For example, we get a server, ready to be a database server, then the hardware resources of this server can support how much access pressure? Has the operating system's kernel parameters been optimized to improve performance? How much performance is affected by tuning the MySQL configuration parameters? ......

Through a series of tuning work, with the benchmark test, the server can be adjusted to the best state, but also mastered the health of the performance indicators

Later in the actual operation process, when the monitoring data close to the benchmark, the database server is almost full load, need to analyze the database structure design, SQL statements such usage problems , or hardware resources is not enough, and then the corresponding processing.

The database server may also require a hardware upgrade , and benchmark tests will be required after the upgrade, comparing with previous test results to ensure that the upgraded performance is improved to prevent inappropriate upgrades or incorrect configuration resulting in degraded performance

Here's a look at how to use Mysqlslap.

Common options
--concurrency number of concurrent, multiple can be separated by commas
--engines to test the engine, can have multiple, separated by delimiters, such as--engines=myisam,innodb
--iterations how many times to run these tests
--auto-generate-sql test with the SQL script generated by the system itself
--auto-generate-sql-load-type to test whether to read or write or to mix the two (read,write,update,mixed)
--number-of-queries how many queries to run in total. The number of queries that each client runs can be calculated using the total number of queries/concurrency
--debug-info additional output CPU and memory related information
--number-int-cols the number of int fields to create a test table
--number-char-cols number of chat fields to create a test table
--create-schema Testing the database
--query your own SQL script to perform the test
--only-print If you just want to print and see what the SQL statement is, you can use this option

Test statement:

mysqlslap-hlocalhost-uroot-p123456-p3306--concurrency=500--iterations=1--auto-generate-sql-- Auto-generate-sql-load-type=mixed--auto-generate-sql-add-autoincrement--engine=innodb--number-of-queries=500

DB cluster

PXC data read and write strong consistency, suitable for storing high value important data.

Single-table data volume exceeds 20 million data performance is inconvenient.

The order money is related to strong consistency

Data Segmentation Middleware Mycat

Cross-platform.

Sacrificing performance to ensure data consistency

Logs need to be saved to the database

Algorithms in Big Data: Coordinated filtering algorithm (user generated log data)

Stress testing of single node database