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