Welcome to docs.opsview.com

MySQL Database Management and Performance Tuning

Use MySQL client to execute SQL statements

Connecting to Databases

From Opsview 4.6.0, new installs will use randomly generated passwords to connect to the databases. These passwords will be encrypted, so it will not be possible to decrypt these credentials to use to connect to the databases.

If you need to connect to the databases to run your own queries, we recommend that you create your own accounts for this purpose. Also, you can restrict the amount of information that would be available to this account, by limiting the tables that can be queried.

Common Tasks

Backing up Opsview databases and configuration

  • Edit etc/opsview.conf to set correct backup destination
 su - nagios
 /usr/local/nagios/bin/rc.opsview backup

Backing up Opsview database only

  • Ensure opsview.conf is correct
su - nagios
/usr/local/nagios/bin/db_opsview db_backup | gzip -c > {backup file}

The runtime, odw and reports databases may be backed up in the same way.

Restoring from a database backup

Identify the required image to restore from (location is held in $backup_dir variable within the opsview.conf file if using a full backup rather than database only).

su - nagios
gunzip -c {/path/to/nagios-db-{date}.sql.gz} | /usr/local/nagios/bin/db_opsview db_restore

If you need to upgrade the database schema, you can run:

/usr/local/nagios/installer/upgradedb_opsview.pl

Setting MySQL root password

We recommend you set a password for 'root' user

 mysqladmin -u root password {password}

Granting access to remote user

For making remote database connections to Opsview Data Warehouse

 grant all privileges on *.* to '<username>'@'<hostname>' identified by '<password>' with grant option;
 flush privileges;

Fixing damaged database tables

If a database table is damaged, you may get error messages like:

Table 'service_saved_state' is marked as crashed and should be repaired

A common cause is running out of space on /var partition where mysql writes its table files.

 mysqlcheck -p -u <user> <database>

To repair table (from MySQL client - note that you'll need enough disk space free for MySQL to make a new copy of the damaged table as a .TMD file):

 use <database name>;
 REPAIR TABLE <tablename>;

To check all databases you can use the following as the mysql root user

mysqlcheck -A -r -u root -p

Incorrect schemas

We have seen issues where a database has a bad schema and indexes are given the wrong name. This causes problems for the upgrade scripts as they expect specific names to exist when upgrading.

Follow this process to reset the schema while retaining the existing data. You should not normally have to do this.

  • Stop opsview and opsview-web
  • Take a backup of the opsview database: /usr/local/nagios/bin/db_opsview db_backup > /tmp/opsview.db
  • Take another backup, for comparing differences: mysqldump -u {user} -p{password} --skip-extended-insert opsview > /tmp/opsview.diff
  • Export just data from the database: mysqldump --skip-extended-insert -t -c -u {user} -p{password} opsview > /tmp/opsview.data
  • Create the database from scratch: /usr/local/nagios/bin/db_opsview db_install
  • Export the schema information from a fresh install: mysqldump -d -u {user} -p{password} opsview > /tmp/opsview.schema
  • Delete and recreate just the database: echo “drop database opsview; create database opsview” | mysql -u {user} -p{password}
  • Import the fresh schema information: mysql -u {user} -p{password} opsview < /tmp/opsview.schema
  • Import the data: mysql -u {user} -p{password} opsview < /tmp/opsview.data
  • Take a new backup: /usr/local/nagios/bin/db_opsview db_backup > /tmp/opsview_post.db
  • Take another backup, for comparing: mysqldump -u {user} -p{password} --skip-extended-insert opsview > /tmp/opsview2.diff
  • Compare to check differences: diff -u /tmp/opsview.diff /tmp/opsview2.diff
  • Restart Opsview

Opsview Datawarehouse

Script for backing up Opsview Datawarehouse to file
#!/bin/bash
BACKUPTARGET="/var/backups"
BACKUP_RETENTION_DAYS=14
DATE=`date +"%Y-%m-%d-%H%M"`

echo Creating ODW backup in $BACKUPTARGET
nice -n 19 /usr/local/nagios/bin/db_odw db_backup > $BACKUPTARGET/opsview-datawarehouse-$DATE.sql
echo Compressing backup with gzip
nice -n 19 gzip -9 $BACKUPTARGET/opsview-datawarehouse-$DATE.sql
echo Removing old ODW backups in $BACKUPTARGET
find $BACKUPTARGET -type f -name "opsview-datawarehouse-*.gz" -mtime +${BACKUP_RETENTION_DAYS-30} -exec rm {} \;

MySQL Strict Mode

Opsview does not support MySQL's strict mode. Ensure that the following is not set in MySQL's my.cnf:

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

MySQL Binary Logs

Logs are generated with the name mysql-bin.0000xxx and mysql-bin.index or similar. These are binary logs used for crash recovery and replication.

To remove these logs, as root mysql user run “PURGE MASTER LOGS TO 'mysql-bin.0000xx';”

For more info look at http://www.mysql.com/doc/en/Binary_log.html

To disable bin logging, in /etc/mysql/my.cnf:

binlog_ignore_db = odw
binlog_ignore_db = runtime

MySQL Performance Tuning

MySQLReport is a useful tool to evaluate the performance of MySQL. To tune MySQL, edit these values in the mysqld section of /etc/mysql/my.cnf (OS dependent) and restart mysql. Good starting values for a reasonably sized database server of 2-4GB memory are:

  • table_cache = 768 (check tables opened/sec in mysqlreport)
  • query_cache_size = 16M (this should not be any higher due to limitations in mysql - see this post)
  • key_buffer = 256M
  • innodb_buffer_pool_size = 1024M
  • innodb_file_per_table = 1
  • innodb_flush_log_at_trx_commit = 2
  • innodb_autoinc_lock_mode=0 # Required for replication with MySQL 5.1 or later
  • max_allowed_packet = 16M
  • binlog_format = 'MIXED' # when using binary logs in replication NOTE: use 'STATEMENT' for MySQL 5.4 or earlier.

You can see the current values with mysqladmin variables.

You may want to consider starting mysqld without name resolution. See http://dev.mysql.com/doc/refman/5.0/en/dns.html for more information.

You can use the opsview_preupgrade_check script to see if there are any variables that need changing. Obviously, values will depend on the resources available on your server, so this acts as guidelines.

Download the script from our svn repository for the latest version. This needs to be copied into /usr/local/nagios/installer to run. You can specify which components to check from your Opsview system.

Note: the recommendations for mysql server variables depend on your system and what other services run on it, so you have to exercise judgement when changing your system. Make sure that you do not over-commit resources to mysql because if it causes the server to go into swapping, this will reduce the performance of mysql.

Note: the crashed tables check may take a while to run.

More information about the innodb parameters are on the mysql documentation site.

General Hints for Performance Tuning

Check iostat -x 5. This gives I/O statistics per disk. You could have a low overall I/O wait time, but it could be due to a single disk being used 100% of the time.

For maximum I/O, you should stripe the disks so that all disks are being utilised.

You should use separate disks for data files and index files - this improves read and write times.

You should use a fast disk for redo logs.

We have also seen improvements if innodb_flush_log_at_trx_commit is set to 0, although it is possible to lose up to 1 second of data in the event of a disk failure.

Troubleshooting

ERROR 1005 (HY000): Can't create table '<db>.<table>' (errno: 121)

If you get this error when trying to create a table:

ERROR 1005 (HY000): Can't create table '<db>.<table>' (errno: 121

This can occur if the table is an InnoDB table and there is a foreign key constraint where the name of the key is already used.

Can't create file '/tmp/#sql76a1_6ff4_1.frm' (errno: 9)

This can sometimes be seen in /var/log/opsview/opsviewd.log:

[ndoutils_configdumpend] [FATAL] DBD::mysql::db do failed: Can't create file '/tmp/#sql76a1_6ff4_1.frm' (errno: 9) [for Statement "CREATE TEMPORARY TABLE opsview_viewports_temporary LIKE opsview_viewports"]

This has been seen on a MySQL 5.1.61 instance where the permissions on the data files were changed. See this forum post for more information.

ERROR 1062 (23000) at line 12: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry

We have seen this problem occur on some MySQL 5.5 systems on 64 bit platforms where the auto increment column is a BIGINT. This appears to be a bug in MySQL. The workaround is to re-add the AUTO_INCREMENT key back to the table.

Opsview's runtime database has four tables which use BIGINTs:

  • nagios_servicechecks
  • nagios_hostchecks
  • nagios_statehistory
  • snmptrapexceptions

You need to run the following commands in MySQL for the runtime database. Note, this could take a long time if the tables are large. Ensure you have enough disk space as well.

LOCK TABLES nagios_servicechecks WRITE;
ALTER TABLE nagios_servicechecks MODIFY COLUMN servicecheck_id BIGINT UNSIGNED NOT NULL;
LOCK TABLES nagios_servicechecks WRITE;
ALTER TABLE nagios_servicechecks MODIFY COLUMN servicecheck_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
UNLOCK TABLES;

LOCK TABLES nagios_statehistory WRITE;
ALTER TABLE nagios_statehistory MODIFY COLUMN statehistory_id BIGINT UNSIGNED NOT NULL;
LOCK TABLES nagios_statehistory WRITE;
ALTER TABLE nagios_statehistory MODIFY COLUMN statehistory_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
UNLOCK TABLES;

LOCK TABLES nagios_hostchecks WRITE;
ALTER TABLE nagios_hostchecks MODIFY COLUMN hostcheck_id BIGINT UNSIGNED NOT NULL;
LOCK TABLES nagios_hostchecks WRITE;
ALTER TABLE nagios_hostchecks MODIFY COLUMN hostcheck_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
UNLOCK TABLES;

LOCK TABLES snmptrapexceptions WRITE;
ALTER TABLE snmptrapexceptions MODIFY COLUMN id BIGINT UNSIGNED NOT NULL;
LOCK TABLES snmptrapexceptions WRITE;
ALTER TABLE snmptrapexceptions MODIFY COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
UNLOCK TABLES;
Navigation
Print/export
Toolbox