Tuning Your Opsview MySQL Database

How to tune your Opsview Monitor MySQL Database

As your Opsview Monitor system grows you may find that certain steps like reloads, navigating the Opsview Monitor User Interface and running reports may become a bit slower. Frequently, this slowness can be addressed by doing some simple tuning of the Opsview Monitor MySQL database.

To start, log into your database server. You may find that your database and Opsview Monitor Orchestrator server are the same system. If you're unsure about which host your database is on, you can check by looking in /opt/opsview/deploy/etc/opsview_deploy.yml:

[[email protected] ~]# grep -A 10 database_hosts /opt/opsview/deploy/etc/opsview_deploy.yml
database_hosts:
  opsview-v63-db.os.opsview.com:
    ip: 192.168.20.150

On our example system,​ you can see that the database is on opsview-v63-db. If you do not see an entry for database_hosts, your database is on your orchestrator.

Once we know where the database is, we can use some database tuning software. We recommend MySQLTuner. It can be downloaded on your host by running:

wget -O mysqltuner.pl mysqltuner.pl

Then, make sure that it is executable by running:

chmod +x mysqltuner.pl

Then run it as shown below:

You'll likely have output similar to what's above. The most important bits are located at the bottom in the "Recommendations" section as shown below:

On our example system we can see that there are some general recommendations as well as variables to adjust. The general recommendations don't necessarily need to be followed and may only give you a limited boost in performance. However, the variables can have a much greater impact and should be carefully considered.

On this system the recommendation is to increase the amount of RAM we'll allow the database to use with the innodb_buffer_pool_size variable. With it recommending only an increase to 131M our example system should have more than enough ram to handle this change and we can easily make it by modifying the my.cnf file on our system.

On Debian and Ubuntu you'll find it here:

/etc/mysql/my.cnf

On Red Hat, CentOS and Oracle Linux you'll find it here:

/etc/my.cnf

Edit the file using your favorite editor, adjust the values and restart the MySQL service. Once that's completed you can enjoy your freshly tuned system.

Keep in mind that these are only some basic recommendations and MySQL database tuning, especially on larger system, can be very complex. Always double check the function of each variable any tuning software gives you against the MySQL documentation for your version before making changes:
http://dev.mysql.com/doc/

Tuning your system improperly can cause it to use too much memory and could even potentially lead to the database crashing and causing database corruption. Additionally you should always check your system's general performance including disks, CPU, and RAM when tuning any database as keeping everything in check will ensure your Opsview Monitor system runs smoothly as your monitoring needs increase.