Best Practices: Web Reporter Maintenance and Space Usage

Version 2

     

    Introduction

     

     

    In the instance, you have had Web Reporter up and running for a while now, it might be a good idea to check your settings for database deletions and data retention.  One issue that is crucial to the health of your Web Reporter system is the amount of free space on the drive containing the data files.  If the system runs out of space, there are many negative impacts, which can be introduced as a result;

     

      • Crashed Database Tables (MYSQL Database/ Internal Database)
      • Failed database or system Maintenance
      • Database service fails to start
      • Unable to parse logs
      • Unable to run reports

     

    * Keeping unnecessary data in Web Reporter can lead to degraded performance

     

    Preventative Measures (Don't run out of disk space in the first place)

    General Considerations

    Turning on page views

     

    "Page Views" are a default log parsing option that condenses log data by removing requests generated by embeded content.  An example of this is taking all of the requests for one website including the components in the page like "style sheets and images" would get recorded as just on site.  For more information on the page views, please referrer to the following communtiy article (DOC-4662).

     

    If you disabled page views, this is something that you might want to enable to help cut down on space and to help reduce the amount of non-needed information in your database.  To enable page views, you will need to go "Administration > Setup > Log Sources > **MyLogSourceName**" then with this selected hit the [Edit] button and go to the section labeled "Processing".  In this location, you will see the following option;

     

    Condense into Page Views.bmp

     

    System Usage

     

      • Is this a shared system?

     

    Shared system - If the Web Reporter is existing on a shared server with other applications, you might want to check the load of the system and the available resources.  In some instances where this system could be used in VMware environments, there could be added impact from slower disk I/O operations due to the Web Reporter existing on the same system as other systems.  Please ensure that you have enough systems resources for the Web Reporter as documented in our product guides.  Additionally, the Web Reporter database can be moved to an external database in another location to reduce the load on the system.  Please reference the following knowledge base article (68025) for additional information.

    Database Maintenance

    Properly configuring Database Maintenance in Web Reporter is the most effective way to control the size of the reporting database. This section will describe in more detail the options available. The location for the Database Maintenance settings are "Administration > Tools > Database Maintenance".  In this location, you will see something like the following;

    Deletion Settings

      Database Deletions.bmp

    Some considerations you might want to take;

        • This will delete information out of the database older than the values listed
        • There are two items, one for the "Summary" and one for the "Detailed" records.  Detailed data normally consumes 80-85% of database disk space.
        • It is better to keep as little detailed data as needed since this will take up the majority of the space

     

     

    * For more information on "Detailed Records" and "Summary Records" please referrer to the following Knowledgebase Article (KB67681).

     

     

    Rollups

     

    In the configuration of the database maintenance there is an option for "Rollups" which in some ways might sound like a good idea but in the end, there is not a big benefit to having this enabled.  It is recommended to disable rollups.

     

    Database rollups.bmp

    * For more information about the database rollups, please reference the following knowledge base article (KB73295).

     

     

    Index Maintenance

     

     

    If you have been using the product for a while and you have noticed that things could be slowing down or performance is lagging, this could be the result of fragmentation or a database which needs to have the indexes rebuilt.  For more information about indexes please reference, ORACLE, Microsoft and MYSQL.  In Web Reporter, you have the option to define a schedule for when you want the database maintenance to perform the database indexing.  This option is located under "Administration > Tools > Database Maintenance" which looks like the following;

     

     

    Database Index Rebuilds.bmp

     

     

    • Note that this option also increases database maintenance times, which could result in other issues with reports running if the maintenance takes too long.
    • It is only a good idea to increase the frequency if you are noticing report slowness between index maintenance jobs. However, if you have a very large database, you might want to keep this value set at monthly.
    • MYSQL - In the instance you are running this on a MYSQL database, you may need 2x the amount of space available as this operation can trigger a rebuild on the table structure.  For example, if the database is 50GB, it could require an additional 50GB available to ensure that this operation completes without issues.
    • If you are using ORACLE for your database and the index maintenence is failing, it is possible that you do not have setup the correct privileges for the user.  Please reference the KBs below for the permissions in the setup guide as the index maintenance requires elevated privileges.
    • Microsoft SQLServer - Index rebuilds can cause the transaction log to grow, especially if you are running your own DB maintenance tasks outside of Web Reporter (such as DB Backup).

     

     

     

    MYSQL Database Setup: https://kc.mcafee.com/corporate/index?page=content&id=KB68025

     

    Microsoft SQL Database Setup: https://kc.mcafee.com/corporate/index?page=content&id=KB75055

     

    ORACLE Database Setup: https://kc.mcafee.com/corporate/index?page=content&id=KB73758

     

     

     

     

     

    Deletion Batch Size

     

    When deleting records for all databases types the information deleted is copied off to a temporary location which is known as a transaction log or temp table space.  This location is essentially a buffer point for records while a specific task is performed on the database.

     

    What does this mean?

     

      • Deleting a large amount of records can cause this location to grow.
      • We need to have the same amount of space free in proportion to the data we are deleting.

     

     

    As the transaction log can grow in size causing issues with an already full drive or system there are some things which can be done to alleviate this issue which are covered in the section for "Reclaiming Space From Deletions" for the Optimize, Shrink and Reorganization commands.

     

    One thing that is crucial in doing database deletions on a system that is critically low on space is to reduce the deletion "Batch" size.  This item is located under "Maintenance Options" the which looks like the following;

     

    Database Batch Deletion.bmp

     

     

    * This will cause Web Reporter to delete smaller chunks of data at a time and help keep the transaction log small.  The default value "All" is recommended under normal operation.

     

     

     

     

    Reclaiming Space From Deletions

     

    After deletions are performed on a database, the database will not re-allocate space back to the file system automatically.  This is a manual process which needs to be performed inside of the database using the information referenced below.

     

    MYSQL / Internal Database

      • To reclaim the space from deletions in the database, you will need to use the optimize command.  This will essentially rebuild the storage file including the information contained to both better optimize the use of storage and aid in more efficient read and write operations.  In turn, if you need more information on the OPTIMIZE TABLE command please reference the following information found on the MYSQL page.  Otherwise, this is also outlined in our best practices knowledge base guide (KB68894)  for MYSQL under the "Optimize Tables" section.  Please note Web Reporter is using the MYISAM storage engine.

     

     

    Microsoft SQL (MSSQL)

      • To reclaim the space from deletions in the database, you will need to go into the "SQL Server Management Studio" and run the "SHRINK" command.  There are a few resources, which can walk you through this process including the Microsoft MSDN site, and IBM's support site.  If you need more information on the "SHRINK" command, please reference the Microsoft MSDN site.

     

     

    ORACLE

      • To reclaim the space from deletions in the database, you will need to use the "Reorganization" command.  For more information on how to run the reorganization command, please reference the following ORACLE-BASE site as this covers the commands and what is needed.

     

     

    Reactive Measures (Oops, I ran out of disk space)

     

     

    Generally there are three steps required to free up space if you ran out of disk space:

     

    1) Free up some space on the file system (outside your database). See "Locations to get some space" below.

    2) Now you have enough space for database maintenance see 'Database Maintenance' section above.

    3) Shrink the database files to regain more space on the file system.  See 'Reclaiming Space From Deletions' section above.

     

    Locations to get some space

    In the event that you are totally out of space and need to make some wiggle room so that you can start the deletion of records. Here are some locations, which can be cleared out to make more space on the system; some of these locations are covered below and in the knowledge base article (KB69642):

     

     

      • /reporter/log/ - This directory contains the log files generated by Web Reporter. The realtime.log* files are generated by real-time log sources (such as SmartFilter plugins) and can be archived off to another server or removed if they are no longer important.  The rest of the files in this directory are not critical.  The server.log can be useful for troubleshooting issues, but is not required.

     

      • /reporter/mysql/var/reporting/#sql-123_12.* - These are temporary tables created by MySQL when database tables are modified.  They can become orphaned if the database service is killed or stopped while performing table operations.  If the Web Reporter database service is stopped, any of these temporary table files can be safely deleted.

     

      • /reporter/tmp/logparsing/dead/ - See KB69641 for more information.

     

      • If you have worked with support I am sure there might have been a few instances where you have been asked to provide a feedback or you have been asked to go to the location "Administration > Tools > System Backup" and click the button which looks like the following;

     

    Backup Feedback.bmp

     

    * Feedbacks need to be removed manually from the system.  You can remove these to get more space in the location described in the dialog box.

     

     

     

    Database Corruption and Repair (MYSQL Database / Internal Database)

     

    To be performed after the disk cleanup!

    In the event the Web Reporter went down in a unclean state or the processes crashed due to either an out of space issue or a environmental issue, this could result in database corruption.  In the event of database corruption, upon logging into the Web Reporter and going into the "Administration > Setup > Database" location, the database could either be in a re-connecting state or a always offline state.  If the Web Reporter internal database is corrupt, you will be able to see "Table 'scr_fct_web' is marked as crashed and should be repaired" errors in the server.log located at "/Web Reporter/reporter/log/" (Windows) or "/opt/Mcafee/WebReporter/reporter/log/" (Unix).  If you are using MYSQL as a external database, you will have to check the locations /var/log/messages, /var/log/mysql.log or wherever the MYSQL log is stored.

     

     

     

    Most of the time, the tables which can crash are the following;

     

      • scr_fct_exact_access
      • scr_fct_web

     

    In the instance of database corruption of the Internal or MYSQL database structures, you will need to use the "myisamchk" utility.  If you need more information on the utility itself, please feel free to reference the MYSQL website.

     

    To repair the database please follow the following information;

     

    Stop the running services

     

      • Open up a "RUN" command using "[WIN] + [ R ]" or going to "Start > Run"
      • Type in the following "services.msc" (Without quotes) and hit "[Enter]"
      • Then in the list locate and stop the following running services for;

     

    McAfee Web Reporter Server

    McAfee Web Reporter Internal Database

     

      • Open up a "RUN" command using "[WIN] + [ R ]" or going to "Start > Run"
      • Then type "taskmgr" (Without Quotes) and hit "[Enter], then search in the process section for the "Java.exe" process owned by "system" and kill it if present

     

     

    Repair the table structure

     

      • Open up a "RUN" command using "[WIN] + [ R ]" or going to "Start > Run"
      • In the "RUN" dialog, please type "cmd" (without quotes) and hit "[Enter]" to bring up a command prompt.
      • Use the "cd" command to change directory locations to where the "myisamchk" is located;

     

     

    \Web Reporter\reporter\mysql\libexec\

    or

    \Web Reporter\reporter\mysql\bin

     

      • Then depending on the broken table enter the commands;

     

    myisamchk --repair ..\var\reporting\scr_fct_exact_access.myi

    myisamchk --repair ..\var\reporting\scr_fct_web.myi

     

      • Depending on the version of "myisamchk" you might just need to use the "-r" flag;

     

    myisamchk -r ..\var\reporting\scr_fct_exact_access.myi

    myisamchk -r ..\var\reporting\scr_fct_web.myi

     

      • If this fails, you might need to do a force command as well;

     

    myisamchk -r -f  ..\var\reporting\scr_fct_exact_access.myi

    myisamchk -r -f ..\var\reporting\scr_fct_web.myi

     

      • If this fails our you get a message about the "Sort_Buffer" you might have to increase the memory for the myisamchk where size=**M is in Megabytes;

     

    myisamchk -r -f --sort_buffer_size=64M ..\var\reporting\scr_fct_exact_access.myi

    myisamchk -r -f --sort_buffer_size=64M ..\var\reporting\scr_fct_web.myi

     

     

    After the repair, start the running services

     

      • Open up a "RUN" command using "[WIN] + [ R ]" or going to "Start > Run"
      • Type in the following "services.msc" (Without quotes) and hit "[Enter]"
      • Then in the list locate and start the following running services for;

     

    McAfee Web Reporter Server

    McAfee Web Reporter Internal Database

     

      • Open up a "RUN" command using "[WIN] + [ R ]" or going to "Start > Run"
      • Then type "taskmgr" (Without Quotes) and hit "[Enter], then search in the process section for the "Java.exe" process owned by "system" and wait for the CPU usage to drop for more than 10 seconds.  When this is done, try to access the Web Reporter WebUI and start the database under "Administration > Setup > Database".  If the database still does not start, there could be additional corrupt tables so please check the logs.

     

     

    Setting up a new database

    In the event that you need to move to or provision a new database, please reference the following best practice setup guides;