Welcome to docs.opsview.com

MySQL Database Management and Performance Tuning

Use MySQL client to execute SQL statements

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 database 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:


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;

Timezone support

To enable time zone setting in MySQL, for some plugins, follow these instructions.

In Linux, FreeBSD, Solaris, and Mac OS X zoneinfo dir is located at /usr/share/zoneinfo - if your system doesn't have zoneinfo directory, then you might need to download it.

Run following command to find location of zoneinfo file

 whereis zoneinfo 

By default zoneinfo path is /usr/share/zone. To load a single time zone file tz_file that corresponds to a time zone name tz_name, invoke mysql_tzinfo_to_sql like this:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql 

Some timezones might not load into MySQL, so you might see some warning messages like Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh87' as time zone. Skipping it.

After running mysql_tzinfo_to_sql, restart MySQL.

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

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 = 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

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.


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.