Statistics DB – anyone using?

[Jim Kosloskey]

Is anyone using the new Statistics DB?

If so, are you willing to share your setup and experience, especially if using history?

 

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

Topic

[Paul Stein]

Hey Jim – I did set this up on a test site but it’s been a while. I set mine up to run every hour because I wanted to be able to query hour by hour volume changes. I didn’t follow through because at the time I couldn’t figure out how to construct a query to give me total messages as well as total messages each hour.  I may give it another shot.
I ended up using the global monitor stats trending feature, although very unreliable and often would kill my host server. Doesn’t work well with high volume multi-site set ups.

As amazing as Cloverleaf is and all the things you can do with it(pretty much anything), statistics and reports is a deficiency out of the box requiring custom set ups or scripting knowledge, sql knowledge.

[Peter Cosman]

Let’s hope in future releases they improve the statistics and reporting funcionalities and make them more reliabable.

 

[Jim Kosloskey]

I have begun this journey.

In the past, I wrote a proc to dump the MSI Stats to a csv file for later analysis and reporting.

The MSI stats accumulate until reset. For the purposes of reporting in the above case, it was not desirable to have that activity. Accumulation would mean that reporting would need to calculate deltas to get analysis showing periodic progressions/changes.

The reporting of periodic changes and progressions was determined to be more important. Aggregation/accumulation can be made from discreet period measurements.

So, we had a script to execute the creation of the csv file proc followed by an execution of hcimsiutil to reset the statistics appropriately. This was in a AIX environment so we used cron to do the scheduling. Archiving was handled that way as well under a different schedule.

With the advent of the Statistics DB, I had hoped to see something that would allow the specification of resetting statistics optionally (perhaps in association with the archiving). I do not see that – but perhaps it is not obvious.

Does anyone know if there is such a capability? If so, please provide some direction.

If not – I think this would make a good enhancement for future release – hint to INFOR folks who might read this.

Here is where I am at with this:

1. I have successfully configured the build of the statistics DB and archiving.
2. I have successfully viewed the Statistics DB via two tools:
   • DB Browser (free handles SQLite only).
   • DB Analyzer (fee-based handles most DBs).
3. I am researching a tool to generate reports/graphs/etc. from the DB. Ideally something free since I do not have a lot of money to spend and I do not intend to get into the report generating business. That is better done by those who want to see the results, in my opinion.
   • I am keenly interested in trying Jasper – If anyone had some experience with this, I would welcome your input – off line directly to my email would be fine.
4. I will begin researching how to tie resetting the statistics to the Statistics DB generation/archiving probably via a Tcl proc.

I will report back as I make any progress.

In the meantime, if anyone is interested in collaborating or just brainstorming – email me directly. I can use all the help I can get.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Jim Kosloskey]

An observation regarding the collection time (Statistics Store Interval in Site Preferences/Statistics DB) – I have it set to 900 seconds (15 minutes) however entries are made (at least in the Hosts Table) at 903-904 when there is activity.

It appears when there is no activity (nothing happening in the site) tuples are not recorded if there are not processes and perhaps threads started.

So, in my case, the site had activity for some processes and threads started for a period of time, then not. In a quick review of the time stamps of Host table tuples there are chunks of gaps longer than 903 or 904 which I think correlate with the situation wherein no processes or threads were started.

Something of which to be aware when analyzing the data.

If I find out the gaps have some other meaning, I will report that. For now, I will proceed with the understanding gaps mean a dormant site.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Jim Kosloskey]

I have made a more concerted effort to detect an imbedded method for associating statistics reset with the creation of the Statistics History.

I did not find any imbedded method (other than executing via hcimsiutil from command line, or manually via NetMonitor).

I am proceeding evaluating techniques for timing resetting uncoupled from History creation and the various impacts.

• This reply was modified 11 months, 1 week ago by Jim Kosloskey.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Jim Kosloskey]

Prior to the Statistics DB, we needed to dump the MSI data on a routine basis then report on the file(s) created. With that process, we could schedule the extraction based on time. We would also reset the statistics at the same time. That worked pretty well.

The Statistics DB facility provided in later Cloverleaf releases improves that activity in that the extraction of the data to the DB can be ‘timed’ based on a number of seconds (duration).

The time that a history copy gets cut can only be controlled by size of the DB. While this accomplishes a goal, I would like to see some enhancement:

1. Allow the creation of a History DB based on Date and Time in addition to the size.
2. Allow specification of statistics reset to occur just prior to the scheduled History creation.
   • Ideally, tie the duration to the date/time in the schedule. In other words, being able to specify durations with each scheduled time period for History creation.

The above would, I think, provide a mechanism by which Historical Statistics Data would represent a collection period of the day. Moreover, to assist analyzing statistics during peak and non-peak demand periods (even helping determine peak demand), being able to set different collection duration paired with different times of the day would (I think) be useful.

The above is what we did with the scheduled MSI dumps but not as well. We would schedule dumps more frequently during the Peak Demand periods and less frequently (in fact varying frequencies) during non-peak demand periods. we tuned that as closely as we could.

Withe the new Statistics DB, we can specify the duration (which we could not prior to the Statistics DB) which is useful – but – we have to have a compromise setting. That is, we need to make it frequent enough to serve peak demand periods but still be useful for non-peak demand periods. I suspect tradeoffs will ensue.

But we also need to time our statistics reset externally based on either creation of the history or time of day. Either technique is not as desirable, in my opinion, as tying the reset inside the product to the creation of the History.

So, I am going to experiment with both techniques:

• Use a scheduler to schedule resets at various times throughout the day tied to the anticipated peak demand period.
• Use the creation of a history copy to trigger the reset. I am planning to attempt to have this done inside the engine via a thread.

The above techniques mean some data for some times of the day will likely span multiple history DBs meaning the reporting methodology will need to compensate.

On the reporting front, I am trying to evaluate Jasper. Paul Stein suggested I also look at Grafana.  Thank you, Paul, for that suggestion. I have looked at their Web page and they do not list SQLite as a supported Data Source. I have reached out to their support to find out if SQLite is supported. I also have Helical on my list. Hopefully one of these will be free, useful, and consume SQLite.

 

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Jim Kosloskey]

OK I am bouncing around a little with my thoughts about an enhancement to tie resetting stats to the Stats data.

Previously, I thought a proper place to add that enhancement was when the history is cut. On further reflection, I think the real spot to tie resetting of stats as an option is with the actual cutting of a tuple set to the DB. So right after the tuples are written to the DB following the user specified duration, optionally the stats could be reset. To further add usability, I could see the value of having that option specifiable by thread or process so that in a given site (since this is set at the site options) resetting of the stats could be done more granularly. I am getting greedy here but as long as it is wishing, wish for everything.

I have come to the above realization as I consider the ramifications of trying to time the resetting of stats asynchronously with the collection of the stats. Having it done within the engine synchronously should reduce or eliminate the potential of lost data.

Whereas asynchronous resetting almost guarantees some statistics data loss.

For example, if the reset is done just prior to the expressed duration (in my case 15 minutes) the stats for the previous 15 minutes are essentially lost. This is the worst case, but any asynchronous attempt will likely lose data.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Author]

Replies