Issue adding meta fields to meta classes grammatically

Todd Jasper

Vote:

We’ve created a cms scheduled job that processes a queue of operations on meta classes and meta fields. In this job we Create and Delete meta classes and meta fields. We also relate meta fields to meta classes. During the job execution we have an issue where the sql server cpu is getting slammed due to thousands of calls to the following stored procedure per second: mdpsp_sys_LoadMetaFieldListByMetaClassId. The stored proc alone is pretty simple and fast, but what is making this SP execute so often?

We're using the MetaClass and MetaField classes in the Mediachase.MetaDataPlus.Configurator namespace.

Through some debugging we've narrowed it to the following method in the MetaClass class:

private void AddFields(IEnumerable<MetaField> metaFields, int weight)

In this method the following line is responsible for the call to the stored proc mentioned above, because the MetaFields property calls LoadMetaFields().

this.MetaFields.Add(metaField);

Here is our code that is called maybe 300 times during the life of the job

public string CreateMetaFieldRelation(MetaClass metaClass, MetaField metaField)
        {
            if (metaClass == null)
                return "ERROR: Meta class does not exist";

            metaClass.AddField(metaField);
            return _successMessage;
        }

#200446

Jan 11, 2019 22:42

Quan Mai

Vote:

Would it be possible to post the code of your scheduled job here ?

#200450

Jan 12, 2019 16:48

Quan Mai

Vote:

Even better, try to use some profiler (like dotTrace) to look into the execution of your job, that would reveal a lot of thing you might have overlooked

#200452

Jan 12, 2019 17:08

Scott Reed

Vote:

I'd also recommend Stackify Prefix, it's free and will show you all the SQL executions being made and errors even if they aren't logged on local development.

#200492

Jan 14, 2019 12:52

Quan Mai

Vote:

A word of warning: last time I check, they still haven't fixed it https://vimvq1987.com/a-curious-case-of-memory-dump-diagnostic-how-stackify-can-cause-troubles-to-your-site/

#200494

Jan 14, 2019 13:26

Greg

Vote:

Quan:

Our scheduled job code is calling the following 3 scenarios using the methods below from EPi. It's rather simple what we are doing from our end with very few new classes/fields involved. Yet when tracing via SQL Profiler, the procedure mdpsp_sys_LoadMetaFieldListByMetaClassId is being called 30,000+ times. Any idea?

Create MetaClass -- only 3 new classes created

MetaClass.Load(MetaDataContext.Instance, name);

MetaClass.Create(MetaDataContext.Instance, metaClassNamespace, name, friendlyName, tableName, parentMetaClass, MetaClassType.User, description);

Create MetaField -- only 2 new fields created

MetaField.Load(MetaDataContext.Instance, name);

MetaField.Create(MetaDataContext.Instance, metaFieldNamespace, name, friendlyName, description, dataType, 255, true, false, false, false);

Create Relation -- associating 40 fields per new class

MetaClass.Load(MetaDataContext.Instance, name);

MetaField.Load(MetaDataContext.Instance, name);

metaClass.AddField(metaField);

#200513

Jan 14, 2019 17:27

Quan Mai

Vote:

Care to share your profiling results (probably with code/pdb) to me? Preferably directly via mail,

#200515

Jan 14, 2019 17:58

Dave Delphia

Vote:

Quan:

I've got a hunch that this might have something to do with event replication...

We ran a test in our QA environment which is load balanced between two servers: QA1 and QA2

The job to create the class and add field relations was exectued on QA1 and in SQL profiler we saw QA1 call mdpsp_sys_LoadMetaFieldListByMetaClassId once. On QA2 that same SP was called 11781 times in the same timespan.

We have two applications, our CMS site and our Commerce site. Both are setup for event replication on the same soap udp address/port.

Can you think of anything with event replication that might be causing this activity that we're seeing? Should we be running the same endpoints between our CMS and Commerce sites? Or do you think this event replication thing is unrelated to the issue we're seeing?

Thanks,

Dave

#200526

Jan 14, 2019 23:46

Quan Mai

Vote:

Shouldn't that be clear on dotTrace profile result? So you don't even have to guess

#200531

Jan 15, 2019 7:23

Greg

Vote:

Quan:

Just to follow up, we sent the trace file to you via email and we saw your response. To add to this, we pulled one server out of EPi's event replication and ran the job without any issues. The stored procedure, mdpsp_sys_LoadMetaFieldListByMetaClassId, was called one time each while profiling (just as it should). So this really points to the event replication as being the issue. Let us know what you find!

#200563

Jan 15, 2019 21:41

Quan Mai

Vote:

I decided to file a bug and "fix" it - the fix will be in the later version of Commerce

#200613

Jan 17, 2019 13:52

Greg

Vote:

Thanks Quan! What was the issue and what is the fix? Can you give us a ticket number so we can keep an eye out for this update?

#200616

Jan 17, 2019 14:00

Quan Mai

Vote:

Your "hunch" is correct, and yes this has something to do with event. When looking into it I realize we can reduce the cost of reacting to events, so we did. The fix is under review now.

The bug is COM-8757 but it will be some time before it's made public.

#200619

Jan 17, 2019 14:07

Greg

Vote:

Quan, do you have any update for us on when this fix will be available? Also, can you tell me if this issue appears with both TCP/UDP event replication? How about in a meshed vs non-meshed replication environment? We are just trying to get some additional details so we can make any adjustments in our environment as well. Thanks in advance!

#200865

Jan 29, 2019 16:11

Quan Mai

Vote:

The bug is under review. The best we can do is 12.17, but that is still under discussion (we might skip that and go straight to 13.0)

#200866

Jan 29, 2019 16:24