topic Re: Best practice for working with large dataset in Knowledge Management

Best practice for working with large dataset

mblauw — Mon, 08 May 2017 14:07:02 GMT

I've got a very large dataset which has got 50 M events each month. I've currently got 3 months indexed, so approx 150M events.

Now, when I try to build up an accelerated report it still has got nearly 2M events. This is the least possible as I still need to use quite a lot of files that have unique combinations in the data.

What would be best practice to build up a search on this data? Searches have to be over all-time data.

We also have built this in ES. Here, it only takes about 1 minute to show a result over the full 3 months worth of data.

Re: Best practice for working with large dataset

woodcock — Tue, 09 May 2017 00:48:00 GMT

If it is possible, rollup the distinctives into whatever breakout timespans you need, perhaps one hourly, one daily and one monthly and put them into a summary index. Then you can pull out of that instead of the raw data.

Re: Best practice for working with large dataset

sloshburch — Tue, 09 May 2017 13:12:11 GMT

You mentioned an accelerated report. Did you also try creating a Data Model or Data Set and then accelerating that?

Re: Best practice for working with large dataset

mblauw — Tue, 09 May 2017 13:13:49 GMT

No I did not yet tried to attempt that. Has those got way better performance over large datasets?

Re: Best practice for working with large dataset

sloshburch — Wed, 10 May 2017 12:21:00 GMT

Absolutely! Search Acceleration is great for very specific searches while Datasets are more malleable and allow for a wider variety of data analysis against the data and you can even accelerate them as well!

http://docs.splunk.com/Documentation/Splunk/latest/Knowledge/Aboutdatasets

Re: Best practice for working with large dataset

mblauw — Wed, 10 May 2017 13:00:39 GMT

Unfortunately this also did not had the desired result in terms of search speed.. Now trying to build up a summary index, see how well that goes.

Thank you for your input!

Re: Best practice for working with large dataset

sloshburch — Wed, 10 May 2017 21:12:53 GMT

In terms of search speed, can you confirm that you accelerated the data model after creating it? Between that and the 'tstats' command, you should see an amazing different. You would see no difference if no such acceleration was turned on.

Re: Best practice for working with large dataset

mblauw — Thu, 11 May 2017 08:20:50 GMT

Thank you! I found out I only had an accelerated dataset, not an accelerated datamodel. It is building atm. Will see how that goes.

Re: Best practice for working with large dataset

mblauw — Fri, 12 May 2017 10:52:51 GMT

I've build the datamodel. It shows the following and the tstats command is not working...

ACCELERATION Rebuild Update Edit

Status 100.00% Completed

Access Count 4.
Last Access: 5/12/17 12:11:53.000 PM
Size on Disk 3059.04 MB
Summary Range 0 second(s)
Buckets 2
Updated 5/12/17 12:01:46.000 PM

Re: Best practice for working with large dataset

sloshburch — Fri, 12 May 2017 12:32:56 GMT

When you pivot on the dataset does it work faster than before? Also, show us what of tstats is not working - it's likely just syntax related.

Re: Best practice for working with large dataset

mblauw — Tue, 29 Sep 2020 14:03:00 GMT

Will try that tomorrow. I'm now rebuilding the datamodel acceleration. Hoping that will fix some things..

This is the query I used:

| tstats avg(t_0_10s) FROM datamodel=ndw_acc_datamodel

I've got a datamodel called ndw_acc_datamodel, wherein a dataset (root event) lives which is called ndw_acc_datamodel_set1

Re: Best practice for working with large dataset

mblauw — Fri, 12 May 2017 14:00:31 GMT

Pivot is incredibly fast up untill 113M events, the last 50M events are very slow. The time range for acceleration is set to all time.

Re: Best practice for working with large dataset

mblauw — Tue, 29 Sep 2020 14:04:23 GMT

I see that I received this error message. Does anybody know what this means?

Audit:[timestamp=05-12-2017
16:40:42.547, user=splunk-system-user,
action=search, info=failed,
search_id='SummaryDirector_1494600038.3739', total_run_time=0.15, event_count=0,
result_count=0, available_count=0,
scan_count=0, drop_count=0,
exec_time=1494600038, api_et=N/A,
api_lt=N/A, search_et=N/A,
search_lt=N/A, is_realtime=0,
savedsearch_name="",
search_startup_time="0",
searched_buckets=0,
eliminated_buckets=0,
considered_events=0, total_slices=0,
decompressed_slices=0][n/a]

Re: Best practice for working with large dataset

sloshburch — Mon, 15 May 2017 12:17:51 GMT

I wonder if the last 50m events were just not yet accelerated.

Re: Best practice for working with large dataset

sloshburch — Mon, 15 May 2017 12:19:57 GMT

I think the SummaryDirector items are the autogenerated accelerations. Other than 'info=failed' I'm not seeing an error message. Where did this arise? If its the acceleration then I think Splunk will self-correct.

Re: Best practice for working with large dataset

mblauw — Mon, 15 May 2017 12:20:09 GMT

But in Settings> Data Models it says completed: 100%. That's strange then, right?

Re: Best practice for working with large dataset

sloshburch — Mon, 15 May 2017 12:24:50 GMT

What of this tstats command is not working - is it showing an error message or producing no results?

Try | tstats values FROM datamodel=ndw_acc_datamodel to validate t_0_10s is the right field name.

According to the docs, you can use the summariesonly flag to restrict it to only items accelerated thus far (if you desire). Also, the prestats will return the data like if you had used the summary indexing commands - ready for other stats after with more of the original summarization details.

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Tstats

Re: Best practice for working with large dataset

mblauw — Mon, 15 May 2017 13:19:04 GMT

Your query returns no results..

Re: Best practice for working with large dataset

sloshburch — Tue, 16 May 2017 12:25:11 GMT

"Pivot is incredibly fast up untill 113M events, the last 50M events are very slow." <- is that still the case? Does the Job Inspector tell you anything? I'd try similar searches with tstats using the summariesonly field true and false to see if you can pinpoint more of that 50m part. Also, did you mean the most recent or the earliest (relative to _time) when you mentioned "last"? And does that happen consistently or just that one time? If just that one time then it could have been load on the indexer.