Frequently Asked Questions

Find answers to the most commonly asked questions.

Getting log files for support

  1. cd /opt/dbmarlin/tomcat/logs
  2. tar -czvf logs.tar.gz *

To get them off the server either sftp them or alternatively.

  1. mv logs.tar.gz ../../www/static/
  2. Then download from the browser https://[dbmarlinserver]:[port]/static/logs.tar.gz

Time zone error on starting MySQL sensor

When adding a MySQL database target you might find the sensor fails to connect with a timezone error like this.

Sensor - The server time zone value 'EDT' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the 'serverTimezone' configuration property) to use a more specifc time zone value if you want to utilize time zone support.

The reason is that newer versions of the MySQL JDBC driver which we use for the sensor don’t like older style timezone names such as EDT which have been depracated. The workaround is to use a custom JDBC URL with a parameter to set a new style time zone name.

For example ?serverTimezone=America/New_York would work in this case. See List of tz database time zones for more detail on which timezones are Canonical and which are Deprecated.

You can set by toggling Advanced mode in the Database target setup screen.

https://d33wubrfki0l68.cloudfront.net/faf54053daffdbd55497abdba74b809e5175395e/3bc1b/img/setting-custom-timezone.jpg

Change detection frequency

Change detection runs as a background process that is scheduled every 5 minutes by default so the actual change time could be anytime within the 5 minute period before the change was detected. If you wish you can run change detection more frequently by changing a server side parameter batch_interval_minutes which can be set in ./tomcat/webapps/agent##1.x/META-INF/context.xml. Increasing frequency may increase the overhead of DBmarlin (particularly if you have a very large number of schema objects), so should be done with due caution. This setting will affect all monitored instances.

What is the data retention for DBmarlin?

DBmarlin collects data and stores it at 1 second granularity giving you maximum visibility into database performance problems even if they are only short spikes. That second-level data is stored for 8 days before being purged which means you can do root cause analysis even long after the event. The 1 second data is rolled up into 1 hour aggregations and the hourly data is stored for 31 days currently for longer term trending.

Change retention period for 1s data

DBmarlin stores data at 1s granularity for maximum visibility. This same data is also used for the 1 minute aggregations. The default retention is 8 days from v1.5 onwards (previously 14 days in older versions). Should you wish to keep granular data for longer than 8 days a server side parameter raw_data_retention_days can be set in tomcat/webapps/archiver##1.x/META-INF/context.xml. This should not be increased by too much otherwise DBmarlin performance may degrade and disk space usage will increase. This setting will affect all monitored instances.

Change retention period for hourly data

DBmarlin stores data at hour granularity to allow querying longer time ranges efficiently. The default retention is 31 days. Should you wish to keep hourly data for longer than 31 days a server side parameter hourly_data_retention_days can be set in tomcat/webapps/archiver##1.x/META-INF/context.xml. This should not be increased by too much otherwise DBmarlin performance may degrade and disk space usage will increase. This setting will affect all monitored instances.

Password protecting DBmarlin

You can password protect the DBmarlin Nginx server using Basic authentication.

  1. Verify that apache2-utils (Debian, Ubuntu) or httpd-tools (RHEL/CentOS/Oracle Linux) is installed.
  2. Create a password file and a first user. Run the htpasswd utility with the -c flag (to create a new file), the file pathname as the first argument, and the username as the second argument:
sudo htpasswd -c /opt/dbmarlin/.htpasswd demo
  1. Add 2 lines to the end of /opt/dbmarlin/nginx/shared.conf to point to the .htpasswd file you created.
auth_basic "Password protected";
auth_basic_user_file /opt/dbmarlin/.htpasswd;

See the Nginx docs https://docs.nginx.com/nginx/admin-guide/security-controls/configuring-http-basic-authentication/ for more details.

Deleting old DBmarlin log files

The main log files for DBmarlin are under ./tomcat/logs. The log files will automatically rotate daily but not be deleted.

If you want to delete any files older than 7 days you can run the following command.

find /opt/dbmarlin/tomcat/logs -mtime +7 -exec rm {} \;

If you want this to run on a scheduled then cron can be used to run this command daily.

Change default sampling frequency

DBmarlin samples the target database every second by default. This allow us to show granular 1s data points for maximum visibility. In some instances, it may not be possible to sample every second if the database is too slow to respond or network conditions between the DBmarlin agent and the target database don't allow it. If you wish you can run sampling less frequently by changing a server side parameter monitoring_interval_milliseconds which can be set in ./tomcat/webapps/agent##1.5/META-INF/context.xml. This setting will affect all monitored instances.

Increase connection timeout

Anything that takes longer than a second can cause the sampling to lag. You can increase the connect timeout by editing the JDBC connect string. You can edit your connect string by toggling on Advanced mode. For Oracle for example, setting CONNECT_TIMEOUT=10 would increase the timeout to 10 seconds. The full connect string would look like:

jdbc:oracle:thin:@(DESCRIPTION=(CONNECT_TIMEOUT=10)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=<ip address>)(PORT=<port>)))(CONNECT_DATA=(SERVICE_NAME=<service-name)))

Monitoring MySQL 5.1

MySQL 5.1 is a very old database these days and therefore requires an older version of the JDBC driver than the one that DBmarlin comes with. If you are running DBmarlin v1.5.0 or above it is possible to monitor MySQL 5.1 by copying the older JDBC driver into the tomcat/lib directory, restarting Tomcat and then modifying the Class name that the Sensor uses by editing the DBmarlin database.

  1. Download the ZIP Archive mysql-connector-java-5.1.39.zip from https://downloads.mysql.com/archives/c-j/.
  2. Extract the zip file and find inside it mysql-connector-java-5.1.39-bin.jar which should be copied to the tomcat/lib directory.
  3. cd /opt/dbmarlin to change directory to the base directory where DBmarlin is installed.
  4. ./stop.sh -t to stop the Tomcat process.
  5. For Linux set export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/dbmarlin/lib:/opt/dbmarlin/postgresql/lib
  6. ./postgresql/bin/psql -U dbmarlin -p 9070 to login to the DBmarlin PostgreSQL database. Then run the following 2 SQL statements:
    1. update datasource set driver = 'org.gjt.mm.mysql.Driver' where driver = 'com.mysql.cj.jdbc.Driver'; to change any existing MySQL databases to use the older MySQL 5.1 driver.
    2. update datasource_type set default_driver = 'org.gjt.mm.mysql.Driver' where default_driver = 'com.mysql.cj.jdbc.Driver'; to change the default for any new MySQL databases added.
  7. ./start.sh -t to restart the Tomcat process.

Note that after an upgrade of DBmarlin steps 4-7 above will need to be repeated.

SQL Statements truncated

PostgreSQL

There is a limit on the length of SQL text that can be captured for PostgreSQL databases. The default value is 1024 characters. This means that large SQL statements are truncated and also that the statements cannot be explained, causing the Execution Plans tab to be empty. If you have long SQL text then you can increase this limit by setting a parameter in your postgresql.conf file of the target database. For example, to double the limit to 2048 characters you can set the following parameter. Note that this needs a restart of PostgreSQL to take effect.

track_activity_query_size = 2048 # (change requires restart)

MySQL & MariaDB

If you notice that the SQL text for statements in MySQL or MariaDB are truncated then this is because of a limit in the database and not DBmarlin. The default limit for the SQL text length in the performance schema in MySQL and MariaDB is 1024 characters. You will also not be able to obtain execution plans from such statements, since the complete text is required.

If you wish to increase the length, you will need to change a parameter in the database configuration file and restart the database. Which one you need will depend on the flavour and version of the database. Below are some examples of how to set the limit to 2048 characters. Note that this needs a restart of MySQL/MariaDB to take effect.

MySQL 5.7

max_digest_length = 2048 # (change requires restart)

MariaDB 10.5.2+

performance_schema_max_sql_text_length = 2048 # (change requires restart)

As far as is known, there is not a way to increase the text length in MariaDB prior to 10.5.x: max_digest_length and performance_schema_max_sql_text_length do not seem to work.