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

Let's learn how to pressure test the performance loss of the audit.log plug-in in mysql5.6. I believe you will benefit a lot after reading it. The text is not much in the essence. I hope you want this short article on how to test the performance loss of the audit.log plug-in in mysql5.6.

Test the virtual machine environment:

Host:

CPU:Intel (R) Core (TM) i5-6400 CPU @ 2.70GHz 4 cores

Memory: 1G

Disk: SCSI hard disk 10G

Database:

Version: 5.6.34

Parameters: innodb_buffer_pool_size = 128m, innodb_io_capacity = 2000

Here is my test script: cat for_sysbench.sh

#! / bin/shtime=3600#0.0for thread in {16pcr 32J 64128256} doecho "now the number of theads is $thread" echo "/ bin/sh / home/linzj/shell/mysql.sh restartsleep 30sysbench-- test=oltp-- mysql-host=192.168.110.100-- mysql-port=3306-- mysql-user=root-- mysql-password=root-- mysql-db=sbtest1-- oltp-num-tables=10-- oltp-table-size=500000-- report-interval=10-- max-requests=0-- oltp-test-mode=nontrx-- oltp-nontrx-mode=select-- Oltp-read-only=off-- max-time=$time-- num-threads=$thread runecho "=" done > > / tmp/sysbench.log.0.0#1.1sed-I's s/innodb_flush_log_at_trx_commit etc/my.cnf sed relayworthy logarithm 0 / etc/my.cnf sed-I 's/innodb_flush_log_at_trx_commit = 0/innodb_flush_log_at_trx_commit = 1Universe g' / etc/my.cnf sed-I' / etc/my.cnf for thread in {32256} doecho "now the number of theads is $thread" echo "=" / bin/sh / home/linzj/shell/mysql.sh restartsleep 30sysbench-- test=oltp-- mysql-host=192.168.110.100-- mysql-port=3306-- mysql-user=root-- mysql-password=root-- mysql-db=sbtest1-- oltp-num-tables=10-- oltp-table-size=500000-- report-interval=10-- max-requests=0-oltp-test-mode=nontrx-- oltp-nontrx-mode=select-- oltp- Read-only=off-- max-time=$time-- num-threads=$thread runecho "=" done > > / tmp/sysbench.log.1.1#100.2sed-I's etc/my.cnfsed relative relative logarithmic logarithm 1 and syncised relayworthy logarithms 100 s/innodb_flush_log_at_trx_commit g' / etc/my.cnfsed-I 's/innodb_flush_log_at_trx_commit = 1/innodb_flush_log_at_trx_commit = 2 Universe g' / etc/ My.cnffor thread in {32256} doecho "now the number of theads is $thread" echo "=" / bin/sh / home/linzj/shell/mysql.sh restartsleep 30sysbench-- test=oltp-- mysql-host=192.168.110.100-- mysql-port=3306-- mysql-user=root-- mysql-password=root-- mysql-db=sbtest1-- oltp-num-tables=10-- oltp-table-size=500000-- report-interval=10-- max-requests=0-oltp-test-mode=nontrx-- oltp-nontrx-mode=select-- oltp-read-only=off-- Max-time=$time-- num-threads=$thread runecho "=" done > > / tmp/sysbench.log.100.2

In fact, the test time here should not be only 3600s, and the number of tables and rows is not too large. If you want to get a more accurate pressure value, it is recommended to increase the test time, the number of rows in the table and the number of threads concurrently.

The tested data are as follows:

Not audit_log.sosync_binlog=0 innodb_flush_log_at_trx_commit=0 innodb_io_capacity = 2000 innodb_buffer_pool_size = 128Msync_binlog=1 innodb_flush_log_at_trx_commit=1 innodb_io_capacity = 2000 innodb_buffer_pool_size = 128Msync_binlog=100 innodb_flush_log_at_trx_commit=2 innodb_io_capacity = 2000 innodb_buffer_pool_size = 128Mthread numbertransactions95% response timetransactions95% response timetransactions95% response time16322134950.25ms294105040.25ms305236650.35ms32261591900.98ms277098800.66ms269330620.68ms64832989870.23ms864236340.23ms771570300. 27ms128887151240.34ms908174200.35ms813493620.41ms256663695202.19ms690104221.98ms715051441.81ms

Audit_log.sosync_binlog=0 innodb_flush_log_at_trx_commit=0 innodb_io_capacity = 2000 innodb_buffer_pool_size = 128Msync_binlog=1 innodb_flush_log_at_trx_commit=1 innodb_io_capacity = 2000 innodb_buffer_pool_size = 128Msync_binlog=100 innodb_flush_log_at_trx_commit=2 innodb_io_capacity = 2000 innodb_buffer_pool_size = 128Mthread numbertransactions95% response timetransactions95% response timetransactions95% response time16286929660.50ms302270400.44ms306352310.43ms32263502080.69ms267892170.64ms265159250.66ms64582600780.45ms601292660.41ms626359250.37ms128613847280.69ms624356970.67ms644553540.59ms256555601772.83ms556838332.87ms560683422.79ms

From the test data, it can be found that:

1. The use of the audit plug-in of the database has indeed lost a certain amount of database performance. If you look at the data of 128 threads with the best stress test performance, the database with audit function has reduced the number of transactions by more than 30% under the same stress test time, and the response time has been doubled.

2. The performance of the database is not linearly related to the number of concurrent threads. When the number of concurrent threads reaches 128, the number of transactions and response time are the best, and then continue to increase concurrency, the performance will decline.

3. When the test data sync_binlog and innodb_flush_log_at_trx_commit are double 1, the performance is the highest. This should be caused by insufficient or stress test time for parameter adjustment. Innodb_buffer_pool_size should be adjusted to at least 80% of memory. Therefore, this test data is only as a reference, need to continue to adjust the parameters before the pressure test in order to get a more accurate value.

After reading this article on how to test the performance loss of audit.log plug-ins in mysql5.6, many readers will want to know more about it. For more industry information, you can follow our industry information section.

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.