Forums (Obsolete)

Member

cormac.debarra-zoorepublic.com - 8/7/2013 1:32:23 AM

Truncate OM_Activity table

Hi,

Does Kentico provide any functionality to archive data from the OM_Activity table. Currently we have 12 million rows in this table after only being in production for 8 months. This strikes me as not being the very scalable. If it keeps growing at this rate it won't be long before we have 50 million rows in one table.

Has anyone else run into this problem.?

Thanks

Member

Swainy - 8/7/2013 5:35:06 AM

RE:Truncate OM_Activity table

Hi,

I would be very interested in seeing the answer to this question.

I think the solution here would be to either limit the amount of data that goes into this table (similair to the event log) but then this could have a negative impact on the data that is stored about an individual that might be needed going forward.

Another option if the full history of the data is required would be the cyphon this table off to a seperate secondary data storage, though given this information does need to be freely accessible (CMSDesk/Content Personlisation/Lead Scoring/Dynamic Contact Groups etc) then i'm not sure this is really an option.

You are right that this table grows very rapidly, we have a site launched a bit ago that is already up to nearly 3 million records, I would be very interested to see the performance hit (especially when navigating contacts/activies in CMS Desk) when that tables grows to as you say 50 million.

Thanks,

Matt

Kentico Support

kentico_filipl - 8/7/2013 6:21:03 AM

RE:Truncate OM_Activity table

Hi,

It is possible to perform database separation which moves system's contact management and other online marketing data into a different database. After doing so, you can manage application's main database easier and it is also more flexible since it does not contain as much data as before.

More information about this functionality can be found in On-line Marketing Guide - Database separation.

Best regards,
Filip Ligac


Member	Swainy - 8/7/2013 7:11:56 AM RE:Truncate OM_Activity table Hi Filip, I agree that this does seperate the data out that is good and certainly helpful however won't we still end up in a situation where we could have 50 million records in this table? Won't performance still be pretty horrific when loading activities etc. even if it's in it's own DB? Thanks,

Member

cormac.debarra-zoorepublic.com - 8/7/2013 5:49:41 PM

RE:Truncate OM_Activity table

I agree, Seperating it into a different DB is good, but it just moves the problem to somewhere else. You will still end up with a monster table, that is basically useless as its to big to query.

The solution we are leaning towards is manually "archiving" and records older than a certain date, using a script and having the data available on request. This is not by any means the best solution.

It looks to me like Kentico is not designed for high volume websites.

Kentico Legend

Brenden Kehren - 8/8/2013 6:58:43 AM

RE:Truncate OM_Activity table

cormac.debarra-zoorepublic.com wrote: It looks to me like Kentico is not designed for high volume websites.

Are you curently having performance issues? Have you checked out this KB article? This probably won't reduce the number of records you currently have but will reduce the number you will receive moving forward. Your fix could be as simple as not tracking ALL activity.

Member

Swainy - 8/9/2013 4:56:59 AM

RE:Truncate OM_Activity table

Hi Frogg,

I think what were getting at is whether this table should have a maximum upper limit and at what point it will become unusable from a management point of view.

Regardless of whether were tracking 50% of activities or all activities that occur at some point if the traffic is high enough this table is going to grow. Then 6 months/ a year / 2 years down the line, this table could end up with upwards of 50 million records in.

Is this ok? Is the table properly indexed and the API calls ok with this many records or is there going to be a big performance hit? Do we need to limit this table (either by doing something else with the data (archiving) or just simply only keeping records for the last 6 months. This obviously has a negative effect if you are trying to view history on a persons actions over the course of time.

So I guess the question really should be, does anyone have experience with huge amount of data in this table (50 million plus) and at what point would we start running into big performance problems.

More and more of our clients are opting for EMS and using it extensively to track activies and expect this data to be available forever (well as long as the website is current and active).

Thanks,

Matt

Kentico Legend

Brenden Kehren - 8/9/2013 8:28:59 AM

RE:Truncate OM_Activity table

Hey Matt! Logical answer and great questions for Kentico. I can say I have not yet setup an EMS install so I'm lacking detailed experience with this but I have read up on it quite a bit. Although the questions I asked have still not been answered:

Is the OP having a performance issue?

Has the OP checked out the KB article?

I'm not stating both of your concerns aren't valid, because they are. I'm simply stating if you don't have the system properly setup it could cause the problems both of you talk about.

Here is smaller scale example: I have a client with a limited database size of 200MB. With 2 weeks of go-live their initial database of 78MB had grown to their limit of 200MB al because of 1 simple setting change. All their files were stored in the file system, versioning and workflow were not enabled and they have about 100 documents.

Come to find out their IT person got in the settings and made a change to save sent emails for 2 years. Keep in mind they have a forum with subscriptions, notifications and newsletters so emails are going out all the time. The cms_email table was loaded with every email sent from day 1. Note, I had set it to only save for 7 days prior to go-live. After truncating the table and reverting the setting back, there wasn't/isn't a problem. The site is currently running without an issue for about a year and database size is running about 100MB.

Moral of the story: when all else fails, read the manual and check the settings.

Brenden

Member

cormac.debarra-zoorepublic.com - 8/11/2013 8:05:41 PM

RE:Truncate OM_Activity table

Hi,

Sorry I was away for a few days.

The answers to those questions are:

Is the OP having a performance issue? Yes.

Has the OP checked out the KB article? No (the link does not work)

We are having performance issues and they have been getting worse the longer the site is up. Its been live for 8 months now and has been progressively (or regressively!) getting slower and slower. Our database size is 15GB after 8 months. We are tracking most activities as its a requirement from the client, and we need to have the data available for them.

I have spent some time profiling the entire website, and it looks like most of the time is spent by Kentico, checking document versions and security checks. I believe that Kenticvo is not an optimized system for high volume websites, and will never use it again for any site that will have medium to high volume traffic. It simply does not scale.

We are considering implementing a whole output caching layer on top of Kentico, that caches the entire HTML for the page and runs in the background updating the cache every minute or 2. This way the performance hit will be taken by the cache layer instead of the user. We cannot use the Kentico output caching feature due to the personalized nature of the website.

Hope that explains my problem better.

Thanks.

Kentico Support

kentico_filipl - 8/12/2013 2:49:07 AM

RE:Truncate OM_Activity table

Hi,

The setting which affects size of OM_Activity database table most is tracking all page visits. So one possible solution is to disable this option in Site Manager -> Settings -> On-line marketing -> Contact management -> Activities. This can help you significantly reduce the number of activities which are being logged. You could also try to disable other options you do not consider essential for further use according to On-line marketing Guide - Enabling tracking of activities.

I hope this can help you improve performance of your websites. The thing about logging activities is that you cannot reduce the size of the corresponding table if you want to track every activity for all users, its size will naturally grow in time.

Best regards,
Filip Ligac