Impact of NodeService calls on MySQL database

orichaud — Tue, 18 Jan 2011 15:28:39 GMT

Hi,I am currently having a look at the database usage as we are currently planning to develop with the Java Foundation API some new components and services. We have defined a new document type (subclassing cm:content) with many metadata and recursive associations connecting sub objects and we need t

Re: Impact of NodeService calls on MySQL database

mrogers — Tue, 18 Jan 2011 15:40:39 GMT

Although some properties are indexed in the lucene index for search purposes the properties live in the database. There will be a sql query retrieving the node properties for each node that you read.

Re: Impact of NodeService calls on MySQL database

orichaud — Tue, 18 Jan 2011 16:00:00 GMT

Thanks for your reply.
I see only the statements for the associations. Is there any caching of properties?

And to sum up, for a given node to traverse, 2 select are necessary:
- one for the properties
- one for the associations

Regards.

Olivier

Re: Impact of NodeService calls on MySQL database

mrogers — Tue, 18 Jan 2011 16:06:37 GMT

Yes there's lots of caching.

Traversing may not need to read the properties, it depends upon the association type.

Re: Impact of NodeService calls on MySQL database

orichaud — Tue, 18 Jan 2011 16:16:26 GMT

I assume caching took place and this is why I did not notice any SQL statement in mysql logs (I use the general_log flag in my.ini file).

I assumed these calls because we need to retrieve, starting from a node, the full object graph with for each object, all the properties. My association are child associations. In fact all those objects in this graph are necessary because we have many list of structured records. Thos objects can only live as long as the content object exists.

As an example, here is a short extract of our custom model. As you can see you have nested child associations and many properties in each type.


… SNIPET …
      <type name="acmc:RefPointType">
         <title>Reference Point</title>
         <parent>sys:base</parent>
         <archive>false</archive>
         <properties>
            <property name="acmc:refPointIndexPointCode">
               <type>d:int</type>
               <multiple>false</multiple>
            </property>
            <property name="acmc:refPointPrimaryIndicator">
               <type>d:boolean</type>
               <multiple>false</multiple>
            </property>
            <property name="acmc:refPointName">
               <type>d:text</type>
               <multiple>false</multiple>
            </property>
            <property name="acmc:refPointDirection">
               <type>d:text</type>
               <multiple>false</multiple>
            </property>
            <property name="acmc:refPointDistance">
               <type>d:float</type>
               <multiple>false</multiple>
            </property>
            <property name="acmc:refPointDistanceUnitName">
               <type>d:text</type>
               <multiple>false</multiple>
            </property>
         </properties>
         <associations>
            <child-association name="acmc:transportations">
               <title>Transportations for this Reference Point</title>
               <source>
                  <mandatory>false</mandatory>
                  <many>false</many>
               </source>
               <target>
                  <class>acmc:TransportationType</class>
                  <mandatory>false</mandatory>
                  <many>true</many>
               </target>
               <duplicate>false</duplicate>
               <propagateTimestamps>true</propagateTimestamps>
            </child-association>
         </associations>
         <mandatory-aspects>
            <aspect>acmc:MultilingDescriptionsAspect</aspect>
         </mandatory-aspects>
      </type>
… SNIPET …
      <aspect name="acmc:PropertyAspect">
         <title>Tag a ConfigurationType as a property</title>
         <properties>
            <property name="acmc:code">
               <title>Chain Code</title>
               <type>d:text</type>
               <multiple>false</multiple>
               <index enabled="true">
                  <atomic>true</atomic>
                  <stored>true</stored>
                  <tokenised>false</tokenised>
               </index>
            </property>
            <property name="acmc:lateCode">
               <title>Late Code</title>
               <type>d:int</type>
               <multiple>false</multiple>
            </property>
            <property name="acmc:contactPhone">
               <title>Contact Phone</title>
               <type>d:text</type>
               <multiple>false</multiple>
            </property>
            <property name="acmc:contactFax">
               <title>Contact Fax</title>
               <type>d:text</type>
               <multiple>false</multiple>
            </property>
            <property name="acmc:contactEmail">
               <title>Contact Email</title>
               <type>d:text</type>
               <multiple>false</multiple>
            </property>
            <property name="acmc:contactUrl">
               <title>Contact URL</title>
               <type>d:text</type>
               <multiple>false</multiple>
            </property>
            <property name="acmc:location">
               <title>location</title>
               <type>d:int</type>
               <multiple>false</multiple>
            </property>
            <property name="acmc:iataCode">
               <title>IATA Code</title>
               <type>d:text</type>
               <multiple>false</multiple>
            </property>
         </properties>
         <associations>
            <association name="acmc:linked-brand">
               <title>Referenced brand</title>
               <source>
                  <mandatory>false</mandatory>
                  <many>true</many>
               </source>
               <target>
                  <class>acmc:ConfigurationType</class>
                  <mandatory>true</mandatory>
                  <many>false</many>
               </target>
            </association>
            <child-association name="acmc:currencies">
               <title>Currencies</title>
               <source>
                  <mandatory>false</mandatory>
                  <many>false</many>
               </source>
               <target>
                  <class>acmc:CurrencyType</class>
                  <mandatory>false</mandatory>
               </target>
               <duplicate>false</duplicate>
               <propagateTimestamps>true</propagateTimestamps>
            </child-association>
            <child-association name="acmc:languages">
               <title>Languages</title>
               <source>
                  <mandatory>false</mandatory>
                  <many>false</many>
               </source>
               <target>
                  <class>acmc:LanguageType</class>
                  <mandatory>false</mandatory>
                  <many>true</many>
               </target>
               <duplicate>false</duplicate>
               <propagateTimestamps>true</propagateTimestamps>
            </child-association>
            <child-association name="acmc:fops">
               <title>Forms Of Payment</title>
               <source>
                  <mandatory>false</mandatory>
                  <many>false</many>
               </source>
               <target>
                  <class>acmc:FOPType</class>
                  <mandatory>false</mandatory>
                  <many>true</many>
               </target>
               <duplicate>false</duplicate>
               <propagateTimestamps>true</propagateTimestamps>
            </child-association>
            <child-association name="acmc:creditCards">
               <title>Credit Cards</title>
               <source>
                  <mandatory>false</mandatory>
                  <many>false</many>
               </source>
               <target>
                  <class>acmc:CreditCardType</class>
                  <mandatory>false</mandatory>
                  <many>true</many>
               </target>
               <duplicate>false</duplicate>
               <propagateTimestamps>true</propagateTimestamps>
            </child-association>
            <child-association name="acmc:addresses">
               <title>Addresses</title>
               <source>
                  <mandatory>false</mandatory>
                  <many>false</many>
               </source>
               <target>
                  <class>acmc:AddressType</class>
                  <mandatory>false</mandatory>
                  <many>true</many>
               </target>
               <duplicate>false</duplicate>
               <propagateTimestamps>true</propagateTimestamps>
            </child-association>
            <child-association name="acmc:refPoints">
               <title>Reference Points</title>
               <source>
                  <mandatory>false</mandatory>
                  <many>true</many>
               </source>
               <target>
                  <class>acmc:RefPointType</class>
                  <mandatory>false</mandatory>
                  <many>true</many>
               </target>
            </child-association>
         </associations>
      </aspect>

… SNIPET …

      <aspect name="acmc:MultilingDescriptionsAspect">
         <associations>
            <child-association name="acmc:multilingDescriptions">
               <title>Descriptions</title>
               <source>
                  <mandatory>false</mandatory>
                  <many>false</many>
               </source>
               <target>
                  <class>acmc:MultilingDescriptionType</class>
                  <mandatory>false</mandatory>
                  <many>true</many>
               </target>
               <duplicate>false</duplicate>
               <propagateTimestamps>true</propagateTimestamps>
            </child-association>
         </associations>
      </aspect>
… SNIPET …
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Regards.

Olivier.

topic Re: Impact of NodeService calls on MySQL database in Alfresco Archive

Impact of NodeService calls on MySQL database

Re: Impact of NodeService calls on MySQL database

Re: Impact of NodeService calls on MySQL database

Re: Impact of NodeService calls on MySQL database

Re: Impact of NodeService calls on MySQL database