15. Build a standalone running environment-perform stress testing on MySQL under centos

========================================================== ========================================================== ==============================

Solution 1: MySQL comes with the stress testing tool mysqlslap, so we don't have to write our own programs to test the MySQL read pressure. The stress test shell script is as follows:

Table creation script:

CREATE TABLE `test` (  `c1` int(11) DEFAULT NULL,  `c2` int(11) DEFAULT NULL,  `c3` int(11) DEFAULT NULL,  `c4` varchar(256) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;

VI/usr/local/test_insert.sh

#!/bin/sh  while true  do      mysqlslap --concurrency=100 --iterations=10 --create-schema='test' --query="insert into test(c1,c2,c3,c4) values(1,1,1,'a')" --number-of-queries=200 --debug-info -uroot -pdsideal    usleep 100  done  

Run:

chmod +x /usr/local/test_insert.sh /usr/local/test_insert.sh 

The above script means to do this cyclically every 100 MS: Simulate MySQL clients, perform 200 inserts (number-of-queries = concurrency *) on the test table of the database test. Therefore, the number of queries per MYSQL client is twice ), iterations: 10. -- Debug-Info: prints information about memory and CPU.

Then we can write a shell script to output the number of MySQL operations within the specified time interval (for example, 1 second). The shell script is as follows:

VI/usr/local/view_test.sh

#!/bin/sh    lastTimes="0"    while true  do      currentTimes=$(mysql -uroot -p'dsideal' -e "show global status like 'Com_insert'" | sed '1d' | awk '{print $2}')      times=$(expr ${currentTimes} - ${lastTimes})      lastTimes="${currentTimes}"      echo "${times}"      sleep 1  done  

Run:

chmod +x /usr/local/view_test.sh

/usr/local/view_test.sh

 

To view the number of MySQL operations, you can view the variables starting with 'com _ 'in global status. They are MySQL operation commands. For example, com_insert is the INSERT command and com_update is the update command, for more information, see the documentation. Subtract the number of adjacent two times to obtain the number of executions within the interval.

 

PS: In addition to commands such as iostat, you can also use the top command to view the IO load (see the percentage of wait. If it is greater than or equal to the number of 1/CPU cores, it indicates that the hard disk Io is faulty ). Please refer to: Understanding disk I/O-When shoshould you be worried?

######################################## ######################################## #########################

Solution 2: Use sysbench

 

Sysbench is a modular, cross-platform, multi-thread benchmark testing tool used to evaluate the database load of different system parameters. For details about this project, see: http://sysbench.sourceforge.net.

It mainly includes the following testing methods:

1. CPU Performance
2. Disk I/O performance
3. scheduler Performance
4. Memory Allocation and transmission speed
5. POSIX thread Performance
6. database performance (OLTP benchmark test)
Currently, sysbench mainly supports MySQL, pgsql, and Oracle databases.

I. Installation

First, download the source package in the http://sourceforge.net/projects/sysbench. Next, follow the steps below to install:

yum -y install libtool

cd /usr/local

wget http://downloads.sourceforge.net/project/sysbench/sysbench/0.4.12/sysbench-0.4.12.tar.gz

tar zxf sysbench-0.4.12.tar.gz
cd sysbench-0.4.12
./configure --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib 

cp /usr/bin/libtool libtool

make 

make install

2. Start testing

After the compilation is successful, it is necessary to test various performances. The test methods are also mentioned on the website, but the OLTP test is not accurate enough. Here I will roughly mention:
1. CPU performance test

export LD_LIBRARY_PATH=/usr/local/mysql/libsysbench --test=cpu --cpu-max-prime=20000 run

In the preceding example, the maximum prime number is 20000. You can adjust the value based on the CPU performance.

2. thread testing

sysbench --test=threads --num-threads=64 --thread-yields=100 --thread-locks=2 run

3. Disk I/O performance test

sysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrw preparesysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrw runsysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrw cleanup

The above Parameters specify a maximum of 16 threads, the total size of the created files is 3 GB, and the file read/write mode is random read.

4. Memory Test

sysbench --test=memory --memory-block-size=8k --memory-total-size=4G run

The above parameter specifies that the entire test process is to transfer 4 GB of data in the memory, each block size is 8 K.

5. OLTP Test

sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 \--mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-host=10.10.3.154 \--mysql-password=dsideal --mysql-db=test prepare 

sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 \--mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-host=10.10.3.154 \--mysql-password=dsideal --mysql-db=test run 

Sysbench -- test = OLTP -- mysql-table-engine = InnoDB -- OLTP-table-size = 10000000 -- mysql-socket =/tmp/MySQL. sock -- mysql-user = root -- mysql-host = 10.10.3.154 -- mysql-Password = dsideal -- mysql-DB = test cleanup

The preceding parameter specifies that the table storage engine type for this test is MyISAM. Note that the parameter on the official website is incorrect, that is, MySQL-table-engine, mysql-table-type is written on the official website. This should be caused by the failure of timely updates. In addition, if the maximum number of records in a table is 1000000, other parameters are well understood, mainly specifying the logon mode. When testing OLTP, you can create a database sbtest first, or use the -- mysql-DB parameter to specify other databases. -- Mysql-table-engine can also be specified as a table storage engine supported by MySQL, such as InnoDB.

Well, this is the main thing. For more information, visit the home page of the sysbench project.

Sync_binlog = 1


Sync_binlog: this parameter is crucial for the MySQL system. It not only affects the performance loss caused by BINLOG on MySQL, but also affects the data integrity in MySQL. The settings of the "sync_binlog" parameter are described as follows:

Sync_binlog = 0. After the transaction is committed, MySQL does not perform Disk Synchronization commands such as fsync to refresh the information in binlog_cache to the disk, and filesystem determines when to synchronize the data, or, after the cache is full, it is synchronized to the disk.
Sync_binlog = n. After each n transaction commits, MySQL runs a Disk Synchronization command such as fsync to forcibly write data in binlog_cache to the disk.
In MySQL, sync_binlog is set to 0 by default, that is, no mandatory disk refresh command is performed. At this time, the performance is the best, but the risk is also the biggest. Because once the system crash, all BINLOG information in binlog_cache will be lost. When it is set to "1", it is the safest but the biggest loss of performance. When set to 1, even if the system crash is used, a transaction not completed in binlog_cache can be lost at most, without any material impact on the actual data. From past experience and related tests, for high-concurrency transaction systems, the system write performance gap between "sync_binlog" and "1" may be as high as 5 times or more.

Transaction-isolation = READ-COMMITTEDinnodb_buffer_pool_size = 2500 m

Innodb_buffer_pool_size defines the maximum memory buffer size for table data and index data of the InnoDB Storage engine. Unlike the MyISAM storage engine, MyISAM's key_buffer_size can only cache index keys, while innodb_buffer_pool_size can cache data blocks and index keys. Appropriately increasing the size of this parameter can effectively reduce the disk I/O of InnoDB tables. On an InnoDB-based dedicated database server, you can set this parameter to 60%-80% of the physical memory size.
Innodb_thread_concurrency = 9innodb_flush_log_at_trx_commit = 1innodb_io_capacity = 400

 

Assume that you have a server dedicated to typical web projects and need to store massive data and use the MySQL InnoDB Engine Memory: 16 GB. The question is: how do you configure this server? The following InnoDB performance optimization is generally applicable to most projects. The hardware memory is the most important for storing massive data using the InnoDB engine. 16g-32G is currently relatively low-cost and efficient. The two dual-core CPUs of the CPU are also competent, however, if you have a 4-core CPU, you can certainly take on a larger workload. Io system raid with a backup battery is a good choice. 6 to 8 hard disks will meet the standard requirements, the 2.5 inch SAS hard drive is faster. Raid10 supports data storage and most read operations. If you like redundant disk array RAID5, be careful that its random write operating system uses a 64-bit operating system. In most cases, the ext3 file system is more efficient than LVM in database backup. Setting innodb_buffer_pool_size memory 70-80% in MySQL InnoDB is a safe option. if you have 16 GB of memory, you can set it to 12ginnodb_log_file_size depending on your restoration speed. For a reasonable restoration time and efficient performance, you can set it to 256minnodb_log_buffer_size = 4 m in most cases, 4 m is reasonable, unless you transmit some large binary data blocks to InnoDB, you Can slightly increase innodb_flush_log_at_trx_commit = 2. If you do not care much about the acid attribute and allow the loss of a small number of transactions at the last moment, innodb_thread_concurrency = 8, it should be set based on the actual situation, it is a suitable setting for innodb_flush_method = o_direct to avoid repeated buffering and reduce the pressure on Linux swap partitions. In most cases, this setting can improve performance, but such If you do not have the backup power to back up the raid cache, some caches will be washed away by innodb_file_per_table-if you do not have a large number of tables, you can use this option, but after using this option, you will not be able to control the primary tablespace of InnoDB, which has been added since MySQL 4.1 and can be used stably. If the project can run the READ-COMMITED uniqueness mode, you can set transaction-isolation = read-committed, the MySQL 5.0 option improves the performance of Row-level locks. After MySQL 5.1, it also helps row-level replication to optimize InnoDB applications for developers who are used to MyISAM, some changes need to be understood in InnoDB. When you perform the update operation, make sure to use the transaction, for the sake of consistency and for better performance. If your project has any write operations, prepare possible deadlocks. Check the table structure and think about how to use the features of InnoDB, such as the primary key cluster, to include the primary key in the index (but ensure the length of the primary key), through the primary key quick search, package a large index.

 

Modify the maximum number of connections

VI/etc/My. CNF

max_connections=1000

Mysql-uroot-P

Show variables like '% connection % ';