DBSeer middleware collects various statistics from OS and DBMS and transfers them to DBSeer for its performance prediction and analysis. The middleware requires MariaDB MaxScale with the dbseerroute plugin, which is a custom router plugin that is necessary to collect required transaction information. The MariaDB MaxScale with the dbseerroute plugin is available here.
NOTE: DBSeer middleware and the dbseerroute plugin require autocommit option from MySQL/MariaDB to be OFF as it detects 'rollback' or 'commit' statements to distinguish different transactions.
DBSeer middleware requires the following:
- Java 1.7+
- Apache Ant
- Python 2.7+ and MySQL-python (for rs-sysmon)
DBSeer middleware can be compiled with ant command:
$ ant
Once the compilation is successful, you can launch the middleware by running middleware.sh at the root directory of the package.
$ ./middleware.sh
When the middleware starts successfully, you will see the following messages in the terminal:
$ ./middleware.sh
00:00 INFO: Listening port = 3555
00:00 INFO: DB log dir = /tmp/maxscale/perf.log
00:00 INFO: System log dir = /tmp/maxscale
00:00 INFO: [Server : server1]
00:00 INFO: DB Host = localhost
00:00 INFO: DB Port = 3400
00:00 INFO: DB User = root
00:00 INFO: DB PW =
00:00 INFO: SSH User = dyoon
00:00 INFO: Remote Monitor Dir = /home/user/middleware/rs-sysmon2
00:00 INFO: Remote Monitor Script = monitor.sh
00:00 INFO: Middleware is now accepting connections.
The diagram below demonstrates a basic setup of DBSeer middleware and MaxScale for collecting OS/DBMS and query statistics from a single MySQL/MariaDB server (i.e., System Z in the diagram. There can be multiple ***'System Z'***s depending on the MaxScale configuration).
DBSeer middleware and MariaDB MaxScale must run on the same machine (i.e., System Y). The middleware needs to be deployed at the MySQL/MariaDB server side in order to collect OS/DBMS statistics (i.e., System Z). The middleware includes a variant of system monitoring utility called rs-sysmon under the directory rs-sysmon2. rs-sysmon requires Python 2.7+ and MySQL-python installed, so in this example setup, they need to be installed in System Z. The middleware runs the utility remotely via ssh to collect OS/DBMS statistics.
Using the above diagram as an example, a user must have a system account that can access System Z via ssh without authorization from System Y. This can be done by adding the public key of the account at System Y as an authorized key for the account at System Z. Using ssh-copy-id is the easiest way to set this up.
DBSeer middleware reads its configuration from middleware.cnf.
The following is a sample of the configuration file:
[dbseer_middleware]
# middleware user id
id=dbseer
# # middleware user password
password=dbseer
# port that middleware listens to for the communication with DBSeer
listen_port=3555
# path of the named pipe that dbseerroute uses to communicate with the middleware.
named_pipe=/tmp/dbseerroute
# middleware reads the SQL performance log from this file.
dblog_path=/tmp/maxscale/perf.log
# middleware writes OS/DBMS statistics of each server into separate files in this directory.
syslog_dir=/tmp/maxscale
# the list of MySQL/MariaDB servers
servers=server1
# server1 configuration start
[server1]
# SQL credential necessary for middleware to gather the DBMS statistics (it uses "SHOW GLOBAL STATUS")
db_host=localhost
db_port=3400
db_user=root
db_pw=
# you must be able to run monitor script remotely at server1 using the following configuration.
ssh_user=dyoon
monitor_dir=/home/user/middleware/rs-sysmon2
monitor_script=monitor.sh
# server1 configuration end
id specifies an id that DBSeer needs to use for authentication.
password specifies a password that DBSeer needs to use for authentication.
listen_port specifies the port that the middleware listens for the communication with DBSeer.
named_pipe is tha path of the named pipe that dbseerroute plugin from our custom version of MariaDB MaxScale uses to communicate with the middleware.
dblog_path should be the filename of the log file generated by the dbseerroute plugin from MariaDB MaxScale. DBSeer middleware continuously reads the transaction information (e.g., SQL statement, latency, etc.) from the file and transfers them to DBSeer while it is monitoring.
syslog_dir specifies the directory where the middleware collects and writes OS/DBMS log data that are not transaction-related. You specify a directory rather than a file here, because there can be more than one MySQL/MariaDB servers running under MariaDB. DBSeer middleware will generate a log file for each server under the directory. Each file will have the format of sys.log.<server name>.
servers provides a list of MySQL/MariaDB servers, which MaxScale will connect to. Each server is separated by a comma:
servers=server1,server2,server3
NOTE: each server on the list must have its own section in the configuration file where it is defined.
db_host specifies the IP address or hostname of a MySQL/MariaDB server.
db_port specifies the port of a MySQL/MariaDB server.
db_user specifies the username of a MySQL/MariaDB server, which the middleware will use to collect MySQL statistics (by running 'SHOW GLOBAL STATUS').
db_pw specifies the password for the user db_user.
ssh_user specifies the username of *nix account that is used to run rs-sysmon2 remotely.
monitor_dir specifies the directory where rs-sysmon2 is located.
monitor_script specifies the filename of the script that executes rs-sysmon2 (default: monitor.sh).