Monday, October 9, 2017

How To Shrink Foxhound

Question: How do I shrink and reorganize the Foxhound 4 database?

Answer: Foxhound uses SQL Anywhere for its database, and the only way to shrink the size of a SQL Anywhere database file is to unload, recreate and reload the file from scratch. The good news is, that whole process is automatic when you reinstall Foxhound on an existing installation:

  • First, Foxhound creates a new, empty copy of the database file.

  • Second, Foxhound finds the old database file,

  • then it asks you how much of the old data you want to copy to the new database: some, all, none, just the options.

  • Finally, Foxhound copies and loads the data, using just enough space to hold it all.
In this context, "shrink" means "don't copy the empty space, and don't allocate space for data that isn't copied", and "reorganize" means the copy process implicitly organizes the data as it is loaded.

Here's the step-by-step process:

Step 1: Start Foxhound 4

...if it isn't running already, that is.

In this example, the Foxhound 4 database has grown quite large:
 Directory of C:\ProgramData\RisingRoad\Foxhound4

10/02/2017  07:31 PM    37,521,506,304 foxhound4.db

Step 2: Decide How Much Data To Copy

Let's say you want to save half the data, thus shrinking the file by 50%.

Foxhound doesn't understand "half", but it does understand "only copy samples recorded after yyyymmdd".

To convert "half" into "yyyymmdd", start ISQL and run this query:
All Programs - Foxhound4 - Tools - Adhoc Query Foxhound Database via ISQL

SELECT CAST ( SYSTAB.count * 0.50 AS INTEGER ) AS half
 WHERE SYSTAB.table_name = 'rroad_sample_set';


-- The space used by the foxhound4.db file is more-or-less determined by the number 
-- of rows in the rroad_sample_set table, which contains one row for each sample
-- sample recorded by the Foxhound Monitor.

-- In other words, the "half" calculated here is "half the samples", not "half the bytes".
Now use "half" as the START AT value in this query:
       DATEFORMAT ( sample_header.sample_finished_at, 'yyyymmdd' ) AS yyyymmdd
  FROM sample_header
 ORDER BY sample_header.sample_set_number;


-- The sample_finished_at column is used to turn the row number 867871 into a yyyymmdd date.
-- The sample_header view is used because Foxhound only allows SELECT statements on the 
-- adhoc query views (sample_header), not the underlying tables (rroad_sample_set).

Step 3: Run Foxhound's "Unsetup"

You don't have to stop Foxhound to reinstall the software, but you do have to run the special "unsetup" process to prepare for the reinstallation:
All Programs - Foxhound4 - Tools - Unsetup Foxhound

-- The "unsetup" process gets rid of the Windows shortcuts and other items, 
-- but it leaves the Foxhound database alone... and even leaves it running.

Step 4: Run The Foxhound 4 Setup Up To The PLEASE READ THIS

In this example, the new Foxhound 4 build 4740 is used... run it all the way to the PLEASE READ THIS window:

Step 5: Enter The FOXHOUND4UPGRADE=yyyymmdd Value

You can copy and paste the FOXHOUND4UPGRADE=yyyymmdd value, and press Enter twice to continue:

Step 6: Wait... and Wait... and Wait Some More

It takes quite a while for 35G of data to be copied and loaded, even half of 35G.

Eventually, the upgrade process will finish, and shut down, and Foxhound 4 will be restarted with the newer, smaller, reorganized database:
 Directory of C:\ProgramData\RisingRoad\Foxhound4

10/06/2017  07:25 PM    21,720,817,664 foxhound4.db
That's 42% smaller that before... not exactly half, but it meets the "more-or-less" standard :)

Thursday, October 5, 2017

New Foxhound 4 Build 4740

A new build of Foxhound 4 is available here.

  • You can use it to upgrade an existing copy of Foxhound 4 for free.

  • You can also use it to install a new copy of Foxhound 4, or

  • to upgrade an existing copy of Foxhound 1, 2 or 3.
If you are already using Foxhound 4, here's why you should consider upgrading to build 4740:
  • Build 4740 "rolls up" all five patches that were previously released for the original Foxhound 4 build 4729.

  • Several performance problems have been fixed, making it less likely that Foxhound will become unresponsive when the database grows very large.

  • If you want to use the "Reinstall Foxhound" method to reorganize and shrink the Foxhound database, the new build 4740 will let you do that... unlike the previous Patch 10 which made it impossible to preserve any data if you subsequently reinstalled the original build 4729.

  • Foxhound's own purge process has been improved, making it more likely the purge will keep database growth under control... and this fix was not included in the previous patches.
On the other hand...
  • If you don't need any of changes in build 4740, there's no need to upgrade.

  • If you only need one or two of the fixes, applying a patch to to build 4729 is a lot quicker than upgrading to build 4740.
Either way, here's a tip...
Tip: Set the Purge Speed to 10 Fastest on section 6. Purge of the Foxhound Options page.

Do this whether or not you upgrade to build 4740, if your Foxhound database is growing rapidly.