fntestrun.MYD

[paullanders]

Hi Dave.

I noted that my fntestrun.MYD is massive (>1GB), and on the FreeNATS backup page: "fntestrun 422885 Rows, 1123883.64 Mb Historic Test Runs"

Is this normal and how can this be managed/reduced/cleared?

Thank you!

Paul

[dave]

Hi Paul,

No that isn't normal. The cleanup.php script should clear it when run periodically as long as the retain.testrun system variable isn't 0 (retain forever). The variable indicates the number of days to keep this (defaults to 30 if unset).

Can you check your retain.testrun variable?

You can manually run the cleanup script from server/bin with a php cleanup.php if you find this was set to 0.

Let me know how you get on.

Regards,

Dave.

P.S. Just a thought but it may be that it is set to 30 and only keeping the last 30 days which is huge in itself - if it is set to 30 let me know and we can check if cleanup is working of if the last 30 days is the problem.

[paullanders]

OK, retain.testrun was set to 0. I changed it to 30 and and ran cleanup.php:

Code: Select all

Deleted 0 Alerts
Deleted 0 Alert Log Entries
Deleted 1773 Test Runs
Deleted 3458 Result Records
Deleted 7963 System Log Entries
Deleted 0 Stale Sessions

It shrunk only slightly: "421445 Rows, 1123883.64 Mb Historic Test Runs"

I was also confused that the info balloon for retain.testrun says "Days to retain test run records for (default 30 if 0 or unset). Retain forever with value -1." So should 0 = 30 (how do you like my math?)

Thanks again!

[dave]

Hi Paul,

Well it turns out I was giving false information (probably not for the first or last time!). Looking at the code indeed 0 uses the default whereas negative numbers would retain forever.

Ok try this:

Execute the following SQL either manually or in the admin SQL console (set site.enable.adminsql to 1 and then it will appear in the system settings list):

Code: Select all

SELECT trid,FROM_UNIXTIME(startx),finishx FROM fntestrun ORDER BY trid ASC LIMIT 0,30;

This should show you the first (oldest) 30 rows stored in fntestrun and you can see the dates. What we need to know is A. are they 30 days or less old (if they're older than 30 days something is wrong) and B. is finishx set (e.g. not 0) for them (this actually shouldn't affect cleanup but does indicate they finished cleanly).

If they are indeed 30 days or less old then it is only keeping the last 30 days but the table is just huge. Perhaps shrink the retention period? You don't really need to keep them for more than 1 day I'd have thought exept for debug purposes.

HTH,

Dave.

[paullanders]

OK, wow, I guess I just have that much data! I set retention to 5 and ran cleanup.php:

Code: Select all

Deleted 0 Alerts
Deleted 0 Alert Log Entries
Deleted 145913 Test Runs
Deleted 165 Result Records
Deleted 404 System Log Entries
Deleted 0 Stale Sessions

The row count is shrinking rapidly, but the mysql data size is decreasing only slightly:

1150856848 2009-05-21 11:15 fntestrun.MYD

Does this seem normal?

Thanks!

Paul

[dave]

Hi Paul,

I'm pretty confident that is just MySQL not immediately resizing the table on disk. If you're using MyISAM format tables then an OPTIMIZE TABLE should do the trick.

You might want to do this from the CLI client rather than the web interface though as it's liable to take a bit of time.

Cheers,

Dave.

[paullanders]

It's amazing what a good optimize will do! It's like eating an entire box of prunes!

Thanks, Dave!

[dave]

Absolutely cleans you out proper!!