5 Replies Latest reply on Aug 3, 2014 9:18 PM by dalelsw

    Compact and repair of the Storage Manager database

    stevehhcp

      How do you run a Compact and repair of the Storage Manager database for optimization purposes?

       

      Thank you

        • Re: Compact and repair of the Storage Manager database
          KMSigma

          We have documentation online which shows everything: Learn to Maintain the Storage Manager Database

           

          This will give you instructions on how to use the dbutil script to run backups and maintenance.

            • Re: Compact and repair of the Storage Manager database
              dalelsw

              The problem with the dbutil is that it keeps STM offline for an extended period of time, especially with a large DB (eg:60GB+).

               

              It would be good if the DB optimize could be done while STM is up and on-line. One of the causes here is the use of MyISAM databases. Moving to use InnoDB would help solve some of this as well as address other locking issues.

               

              Personally (and this is unsupported by SolarWinds), I have experimented with running DB optimizes per table while the DB and STM are up (via a scheduled script). For me so far, this has worked well (but it might not work for you). The only issue is the time that some of the larger tables remain locked, however, this only affects a limited subset of activity, and is less intrusive than having all of STM down for the time it takes to optimize all tables.

                • Re: Compact and repair of the Storage Manager database
                  KMSigma

                  When the Storage Manager monitored environment gets large, we normally suggest that you move all polling jobs from the STM server to a proxy agent.  This effectively makes your primary STM server act as just a database and web server.  Since Proxy Agents are free, there's no reason to not shift the load around.  Do that and make sure that your database is on disks capable of high IO (which is just best practices when working with any database) and your need to "optimize" the database goes down significantly.

                    • Re: Compact and repair of the Storage Manager database
                      dalelsw

                      We currently have all polling located on dedicated polling agent servers. While polling servers can still collect data when the STM server is off-line due to database maintenance this is still an issue:

                      1) We cannot view reports or perform analysis.

                      2) Alerting for performance related events cannot be escalated (agents can be pointed at other secondary SNMP receivers, however this requires another tool, is not centralised and adds management overhead).

                      3) STM has to work hard to catchup with the agents (3+ hours of backlog, more often than not STM and the agents must be restarted).

                       

                      We ensure that the STM DB has dedicated high IO available. However, the full maintenance process still takes a an extended period of time.

                       

                      Moving away from the MyISAM engine (even to InnoDB/XtraDB on MySQL/MariaDB) would be beneficial. Not just for DB maintenance, but also to resolve a lot of the DB locking issues that occur due to day-to-day STM activities and reports.

                       

                      There is currently always a need to optimise the DB for disk space management reasons. This is because MyISAM does not automatically compact files on disk to account for deleted records which causes disk utilisation to needlessly grow..

                • Re: Compact and repair of the Storage Manager database
                  lovedayb

                  I would be interested to know if there were any plans to support an external db instance such as SQL. All of our other SW products such as NPM, NCM and SAM are using SQL and it would make things easier for us. Our SQL engines are Datacentre so allow for online reindexing, etc, which would solve this issue.

                   

                  Cheers!

                  Ben