mysql5.6 loading Percona version Audit.log plug-in performance loss voltage measurement

Since the mysql5.6 Community Edition does not have an Enterprise Edition-specific audit audit plugin, the recent need to increase the audit function of the production MySQL database, in consideration of Percona, MARIDB and macfee3 versions of audit, finally chose the more familiar Percona version.

Note here, it is best to adopt the same child version of the PXC audit_log.so file, that is, download the PXC binary package file and directly copy its built-in audit_log.so plugin.

With the audit Audit function enabled, there is a certain loss of the performance of the database, and it is necessary to pass the test verification. A test was made on the virtual machine as follows:

To test the virtual machine environment:

Host:

Cpu:intel (R) core (TM) i5-6400 CPU @ 2.70GHz 4 Core

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 {16,32,64,128,256}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/sync_relay_log=0/sync_relay_log=1/g '  /etc/my.cnf sed -i  ' s/sync_binlog=0/sync_binlog=1/g '  /etc/my.cnf  sed -i  ' S/innodb_flush_log_at_trx_commit = 0/innodb_flush_log _at_trx_commit = 1/g '  /etc/my.cnf  for thread in {32,256}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/sync_relay_log=1/sync_relay_log=100/g '  /etc/my.cnfsed -i  ' s/sync_ Binlog=1/sync_binlog=100/g '  /etc/my.cnfsed -i  ' s/innodb_flush_log_at_trx_commit = 1/ Innodb_flush_log_at_trx_commit = 2/g '  /etc/my.cnffor thread in {32,256}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 should not be only 3600s, the number of tables and the number of rows is not too large, if you want to get more accurate pressure measurement, it is recommended to adjust the test time, the number of rows of the table and the number of thread concurrency.

The data tested are as follows:

	sync_binlog=0  innodb_flush_log_at_trx_commit=0 innodb_io_capacity =  innodb_buffer_pool_size = 128M	sync_binlog=1  innodb_flush_log_at_trx_ Commit=1 innodb_io_capacity =  innodb_buffer_pool_size = 128M		sync_binlog=100  innodb_flush_log_at_trx_commit=2 innodb_io_capacity =  innodb_buffer_pool_size = 128M
Thread number	Transactions	95% Response Time	Transactions	95% Response Time	Transactions	95% Response Time
16	32213495	0.25ms	29410504	0.25ms	30523665	0.35ms
32	26159190	0.98ms	27709880	0.66ms	26933062	0.68ms
64	83298987	0.23ms	86423634	0.23ms	77157030	0.27ms
128	88715124	0.34ms	90817420	0.35ms	81349362	0.41ms
256	66369520	2.19ms	69010422	1.98ms	71505144	1.81ms

Audit_log.so	sync_binlog=0 innodb_flush_log_at_trx_commit=0 innodb_io_capacity = innodb_buffer_pool_size = 128M		Sync_binlog=1 innodb_flush_log_at_trx_commit=1 innodb_io_capacity = innodb_buffer_pool_size = 128M		sync_binlog=100 innodb_flush_log_at_trx_commit=2 innodb_io_capacity = innodb_buffer_pool_size = 128M
Thread number	Transactions	95% Response Time	Transactions	95% Response Time	Transactions	95% Response Time
16	28692966	0.50ms	30227040	0.44ms	30635231	0.43ms
32	26350208	0.69ms	26789217	0.64ms	26515925	0.66ms
64	58260078	0.45ms	60129266	0.41ms	62635925	0.37ms
128	61384728	0.69ms	62435697	0.67ms	64455354	0.59ms
256	55560177	2.83ms	55683833	2.87ms	56068342	2.79ms

The data from the test can be found:

1, the use of the database audit plug-in, it does lose a certain database performance, if the best performance of the test 128 threads concurrency data, the database has audit function under the same pressure measurement time, the number of transactions accounted for less than 30%, the response time has been extended 1 time times.

2, the database performance is not a linear relationship with the number of concurrent threads, when the number of concurrency reached 128, the transaction number and response time are the best, and then continue to increase concurrency, performance decreased.

3, here test data sync_binlog and Innodb_flush_log_at_trx_commit for double 1, performance is the highest. This should be the result of insufficient parameter adjustment or low-pressure measurement time. At least innodb_buffer_pool_size should be adjusted to 80% of memory. Therefore, this test data is only as a reference, it is necessary to continue to adjust the parameters before the pressure test to get more accurate values.

This article is from the "thick Product Thin Hair" blog, please make sure to keep this source http://1057212.blog.51cto.com/1047212/1908230

mysql5.6 loading Percona version Audit.log plug-in performance loss voltage measurement