Time to forget show processlist for monitoring?

Disclaimer: I’m not specially an expert of benchmarking, this post is to compare different options. All comments and advices are welcome.

I’m not telling you anything new, the show processlist command is a fantastic command line tool for instant check.
But what about monitor your databases with this command embedded in a tool?

Just have a look at this graph:

Screenshot 2014-06-23 at 10.09.38 PM

With 5K queries per seconds, how much will be hidden with a show processlist executed every seconds? Probably a lot.
So, I wanted to test which alternatives could be efficient to retrieve all the queries during a time lapse.

Test procedure and configuration

I used sysbench 0.5 (with oltp.lua) to make my tests on different configurations and tools.
My test server is a 20 cores (hyper-threaded) server with 128Go of RAM and a RAID 10 disk setup.
These tests are based on Percona Server 5.5.31.
I tested the amount of TPS from 1 to 64 concurrent threads.

Here is the standard benchmark graph (TPS):

0a7dffb4f4105bbaecefd912495f7447

Slow query log set to 0 (file output)

d4c4ef22f54e29ebbcfacd3e7395c4b1

Estimated size of the file: 52Go
We reach a stable peak of 1000 transactions per second.

Slow query log set to 0 (table output)

9e9ba5be3a12928fc4ea876c688c1bcf

Estimated size of the table: 8.9Go
Table contention is probably the cause of a stabilization around 600 transactions per second.

General log in a file

806e583c9db079f5ebf7846a27e92a6f

Estimated size of the file: 11Go
2000 transactions per second can be hoped here.

General log in a table

d49dbc548a3fa124ced9b58038581f74

Estimated size of the table: 9.9Go
Again, the results are below compared to the previous results in a file.

Infinite show processlist in a file

62adf7a0696c51338e217ac2bb2894ac

Estimated size of the file: 1.1Go
The results don’t seem so dramatic but not sure all the queries were grabbed in the file.

MariaDB Audit Plugin in a file

c223b317a1d2d8e2f14002d1666fcd6f

Estimated size of the file: 14Go
plugin-load=server_audit=server_audit.so
server_audit_events             = QUERY
server_audit_file_path          = /var/log/mysql/audit.log
server_audit_logging            = ON
server_audit_output_type        = file

MacAfee Audit Plugin (default configuration)

526777e9fa90b1b236b453519d616145

Estimated size of the file: 22Go

audit_offsets=6480, 6528, 4080, 4520, 104, 2584
audit_json_file=1
audit_json_socket_name=/tmp/audit.sock
audit_json_log_file=/var/log/mysql/mysql-audit.log
audit_record_cmds=select,insert,update,delete

MacAfee Audit Plugin (via socket)

fb244656047883f4eeab8a4bf046caf3

Estimated size of the file: 22Go

audit_offsets=6480, 6528, 4080, 4520, 104, 2584
audit_json_file=1
audit_json_socket=1
audit_json_socket_name=/tmp/audit.sock
audit_json_log_file=/var/log/mysql/mysql-audit.log
audit_record_cmds=select,insert,update,delete

MacAfee Audit Plugin (via socket + file sync 1000)

706b1033314f4ef6c774688bc618a612

Estimated size of the file: 22Go

audit_offsets=6480, 6528, 4080, 4520, 104, 2584
audit_json_file=1
audit_json_socket=1
audit_json_socket_name=/tmp/audit.sock
audit_json_log_file=/var/log/mysql/mysql-audit.log
audit_record_cmds=select,insert,update,delete
audit_json_file_sync=1000

Performance Schema activated

eb1ba3455682c347cf34cbaf53aad57a

performance_schema
performance_schema_events_waits_history_long_size = 10000
performance_schema_events_waits_history_size = 1000

Conclusions

Auditing a system necessarily has an impact on your system.
The tools and options presented here can provide various information.
Also, you have to consider how you’ll handle these data after having collected them.

The show processlist command don’t seem so dramatic for performance but not guarantee that all the queries will be retrieve.
You probably have to chose another solution if you really need to collect reliable data.

Hope these quick tests could help you to chose the right tool for your needs.




Share the love!

Subscribe to RSS feed or by email to automatically receive real-time notifications
Oracle, MySQL, and InnoDB are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners

4 Comments

    • It could be an interesting alternative too.
      MySQL Audit Plugin wasn’t available in Persona Server when I did my tests.
      And I dismissed MySQL Enterprise Audit Log Plugin from Oracle because I wanted to consider open source plugins only.

      Reply

Leave a Comment.