Opsview Knowledge Center

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 has become a bit slower. Frequently this slowness can be easily and significantly improved by doing some simple tuning of the Opsview Monitor MySQL database.

To start, log into your database server. For many you'll find your database and Opsview Monitor Master server are the same system. If you're unsure you can easily check by taking a look in /usr/local/nagios/etc/opsview.conf

On our example system you can see that everything is set to localhost so lets proceed with grabbing some database tuning software. There are a lot to choose from, but one we recommend is MySQLTuner. It can be downloaded on your host by running:

wget -O mysqltuner.pl

Then, make sure that mysqltuner is executable:

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 you'll find it here:
/etc/mysql/my.cnf

On Red Hat you'll find it here:
/etc/my.cnf

Edit the file using your favorite editor, adjust the values, then fully stop Opsview Monitor and restart the MySQL service. Once that's completed you can start Opsview Monitor back up and 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.

Tuning Your Opsview MySQL Database

How to tune your Opsview Monitor MySQL Database