It is taking longer and longer to save a report.

Steve Kay shared this question 1 year ago
Answered

Would cleaning out the archived and deleted reports help?

select ReportHeader.reportstatuscode,count(*) from ReportHeader group by ReportHeader.reportstatuscode;


# reportstatuscode, count(*)

'ARCHIVED', '1526'

'DELETED', '1197'

'DRAFT', '21'

'OPEN', '474'

Comments (11)

photo
1

Hi Steve,

While this sort of maintenance will help, 2000 rows is not crippling by any means. Typically, other tables will become a problem before the reportehader table. What are the sizes of your:

  • Event
  • EventArchive
  • ReportInstance

tables?

Nathan

photo
1

The rowcounts for the tables are:

Event 31634

ReportBroadcastResult 43609

ReportInstance 151111

ReportFormat 188312

EventArchive 544955

ReportInstanceFilter 3084440

The reports seem to load and run in a reasonable time.  It is saving a new report or saving changes to an existing report that takes somewhere between 30 seconds and a minute.

Steve

On 2/12/2018 1:39 PM, Yellowfin Support wrote:

photo
1

Any recommendations?

photo
1

Hi Steve,

Sorry for the delay, those sizes look pretty good. It will be worth limiting these at some point but for now they are reasonable. Instead let's take a look at your caching.

Can you please send me a copy of your info_cache.jsp page? (youryfurl:port/info_cache.jsp

Regards,

Nathan

photo
1

Thanks for getting back to me.  Here is the cache info:

Report Data Cache: Cache Max Size: 20

Num Hits Cached 0

Num Hits Not Cached 0

Cache Full 0 items (0%)

Report Definition Cache: Cache Current Size: 154

Cache Max Size: 1000

Cache Max Age: 86400000

Num Hits Cached 436289 (49%)

Num Hits Not Cached 443434 (50%)

Cache Full 154 items (15%)

Oldest Cached Item 23 h, 2 min

Newest Cached Item 21 h, 11 min, 16 sec

Average Cache Time 22 h, 29 min, 35 sec

Dashboard Definition Cache: Cache Current Size: 4

Cache Max Size: 1000

Cache Max Age: 86400000

Num Hits Cached 2146 (96%)

Num Hits Not Cached 67 (3%)

Cache Full 4 items (0%)

Oldest Cached Item 23 h, 2 min

Newest Cached Item 23 h, 1 min, 58 sec

Average Cache Time 23 h, 1 min, 59 sec

Document Cache: Cache Current Size: 0

Cache Max Size: 50

Cache Max Age: 43200000

Num Hits Cached 0

Num Hits Not Cached 0

Cache Full 0 items (0%)

Person Cache: Cache Current Size: 1

Cache Max Size: 1000

Cache Max Age: 43200000

Num Hits Cached 263 (89%)

Num Hits Not Cached 32 (10%)

Cache Full 0 items (0%)

Cached Filter Cache: Cache Settings

Cache Method CACHE_BY_USER

Join Method DATABASE

Cached Filter Cache

Cached Filters maximum size: 100

Cached Filters caching period: 86400000

Num Hits Cached 140 (78%)

Num Hits Not Cached 38 (21%)

Cache Full 0 items (0%)

View Level Filter Items: 0

Cached Filter Query Cache

Cached Query Filters maximum size: 100

Cached Query Filters caching period: 86400000

Num Hits Cached 0

Num Hits Not Cached 0

Cache Full 0 items (0%)

Filter Meta Data Cache

Filter Meta Data Cache maximum size: 1000

Filter Meta Data caching period: 86400000

Num Hits Cached 418 (9%)

Num Hits Not Cached 4029 (90%)

Cache Full 0 items (0%)

View Cache: ReportView records currently cached: 15

Geometry Cache: Max cache size: 4000

Geometry elements currently cached: 0

Report Thumbnail Cache: Max cache size: 100

Thumbnails currently cached: 0

Image Cache: Images cached: 0

Bytes cached: 0 / 52428800

Translation Cache: Max Cache Size: 5000

Max age: 86400000ms (24 hrs)

Num Hits Cached: 0

Num Hits Not Cached: 0

Cache Full: 0 items (0%)

LDAP DN Cache: Max Cache Size: 300000

Max age: 90000000ms (25 hrs)

Num Hits Cached: 0

Num Hits Not Cached: 0

Cache Full: 0 items (0%)

Event Cache: Max Cache Size: 100000

Events cached: 21191 (21%)

Cache hits: 1982 (99%)

Cache misses: 2 (0%)

Date ranges cached (GMT): 2017-12-27 22:34:41 - now

Bytes used: 6342655

On 2/14/2018 1:06 PM, Yellowfin Support wrote:

photo
1

Hi Steve,

Those look fine as well. Next step is info_threads.

Can you collect info_threads for the duration of this save process and send me the result:

https://community.yellowfinbi.com/knowledge-base/article/infothreadrunner

Let me know if you have any questions on this

Nathan

photo
1

Hi Steve,

Thanks, for convenience on my end, could you zip those up next time? :D

Looking through these, it looks like the majority of time here is spent on updating the reportinstancefilter table, so a good place to start will be to clean this out a bit. This link contains many useful queries one of them being a cleanup of this table:

https://community.yellowfinbi.com/knowledge-base/article/why-is-my-yellowfin-database-so-large-and-what-can-i-do-about-it

Let me know if this has any effect on the save times.

Regards,

Nathan

photo
1

What do you think of this?

delete from ReportInstance  where reportstatuscode='ARCHIVED';

DELETE FROM ReportInstanceFilter WHERE InstanceId NOT IN (SELECT ReportInstanceId FROM ReportInstance )

On 2/14/2018 2:21 PM, Yellowfin Support wrote:

photo
1

Hi Steve,

I think those are a good start. These tables are primarily to save filter settings for things like broadcasts, or report bookmarks/snapshots. They also contain report run information which is only used for a short period of time, so as long as you arnt deleting a broadcast/snapshot (will show in the reportstatuscode), you will not see any loss.

Nathan

photo
1

Hi Steve,

I am going to set this ticket to closed for now, but if there is anything else I can do to help here, please just let me know and the case will be re-opened!


Regards,

Nathan