Skip to main content

Check out Interactive Visual Stories to gain hands-on experience with the SSE product features. Click here.

Skyhigh Security

Database Maintenance and Cleanup

  1. Last updated
  2. Save as PDF

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

Preventive 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.

If you disabled page views, this is something that you might want to enable to help cutdown 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 > LogSources > **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 :

clipboard_e0064ef52e8c3736f7d045a60b2b2b2a6.png

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.

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

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.clipboard_ef7c7d32ec266e765cd18d471aa1c7e5e.png

* For more information on "Detailed Records" and "Summary Records" please referrer to the following:

Roll ups

In the configuration of the database maintenance there is an option for "Roll ups"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 roll ups.

clipboard_eaee591b3400db187e295b63a1f098323.png

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:

clipboard_e77ec548983a2849cd149e320c9200e9a.png

  • 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 50 GB, it could require an additional 50 GB available to ensure that this operation completes without issues.
  • If you are using ORACLE for your database and the index maintenance is failing,it is possible that you do not have setup the correct privileges for the user. Index maintenance requires elevated privileges.
  • Microsoft SQL Server - 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).
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;

clipboard_ee8e9976c4f4bfb26fa0f06b06ea1ef3c.png

* 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 fi le 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 fi le 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.

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 (ran out of 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 fi le 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 fi les to regain more space on the fi le 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.

  • /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 SmartFilterplugins) 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/
  • 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

clipboard_e1b36c41995a41e85d47aef44d13beb6e.png

* 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 DB / Internal DB)

NOTE: Must 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/Skyhigh/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 steps:

Stop 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;
    • Skyhigh Web Reporter Server
    • Skyhigh 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"fl ag;

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 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;
    • Skyhigh Web Reporter Server
    • Skyhigh 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.
  1. Back to top
  • Was this article helpful?

Recommended articles

  1. Article type
    Topic
  2. Tags
    This page has no tags.