Startup speed and number of sql queries

[krm1312]

I am experimenting with Alfresco and DoCASU.  I am a bit disappointed in the startup speed and did a little digging (hoping there was a magic "go-faster" flag).  When on a local network with the database (MySql 5), startup speed is on the order of a minute.  On a non-local network (vpn) with a round trip latency of about 13ms, startup is over two minutes (this is a quad core machine with 8g of memory, so those are not an issue).

Versions:

Alfresco started (Labs): Current version 3.0.0 (b 1164) schema 131
DoCASU: 'com.optaros.alfresco.docasu' version 1.3.0
Tomcat 5.5.20
Linux  2.6.18-92.1.6.el5 #1 SMP Fri Jun 20 02:36:06 EDT 2008 x86_64 x86_64 x86_64 GNU/Linux‍‍‍‍

I turned on hibernate's sql logging and we have (during startup only):

$ grep '\[org.hibernate.SQL\] select child' catalina.out | wc -l
1915
$ grep '\[org.hibernate.SQL\] select' catalina.out | wc -l
2191‍‍‍‍

1800+ of the queries are:

select childassoc0_.id as id6_, childassoc0_.version as version6_, childassoc0_.parent_node_id as parent3_6_, childassoc0_.type_qname_id as type4_6_, childassoc0_.child_node_name as child5_6_, childassoc0_.child_node_name_crc as child6_6_, childassoc0_.child_node_id as child7_6_, childassoc0_.qname_ns_id as qname8_6_, childassoc0_.qname_localname as qname9_6_, childassoc0_.is_primary as is10_6_, childassoc0_.assoc_index as assoc11_6_ from alf_child_assoc childassoc0_ where childassoc0_.parent_node_id=? and childassoc0_.type_qname_id=? and childassoc0_.child_node_name=? and childassoc0_.child_node_name_crc=? order by childassoc0_.assoc_index, childassoc0_.id
‍‍

Is there any way to turn this off or load these entities on demand?  Obviously a local database helps…just wondering if there is way to avoid it?

I think this query starts in:

   

at org.alfresco.web.scripts.AbstractWebScript.init(AbstractWebScript.java:114)
   at org.alfresco.web.scripts.DeclarativeWebScript.init(DeclarativeWebScript.java:61)
   at org.alfresco.web.scripts.DeclarativeRegistry.initWebScripts(DeclarativeRegistry.java:306)
   at org.alfresco.web.scripts.DeclarativeRegistry.reset(DeclarativeRegistry.java:168)
   at org.alfresco.web.scripts.AbstractRuntimeContainer.reset(AbstractRuntimeContainer.java:231)
   at org.alfresco.repo.web.scripts.RepositoryContainer.init(RepositoryContainer.java:381)
   at org.alfresco.repo.web.scripts.RepositoryContainer.reset(RepositoryContainer.java:352)
   at org.alfresco.web.scripts.AbstractRuntimeContainer.onApplicationEvent(AbstractRuntimeContainer.java:254)‍‍‍‍‍‍‍‍

Last thing…almost all these queries come after:

01:50:48,643 User:System INFO  [service.descriptor.DescriptorService] Alfresco started (Labs): Current version 3.0.0 (b 1164) schema 131 - Installed version 3.0.0 (b 1164) schema 131‍

Thanks,

Kevin

[krm1312]

Just as a comparison, I exported the database (all 384k uncompressed mysqldump) and locally the same aflresco instance starts up in 19s on the same machine.  So it's clearly just latency due to the number of queries and the round trip time.

–Kevin

[pmonks]

Currently Alfresco is quite "chatty" with respect to the database, so ensuring the connection between Alfresco and the database is high bandwidth / low latency and has minimal contention is very important from a performance perspective.

Cheers,
Peter

[fschnell]

Hi

can you tell me how to turn on hibernate's sql logging?

Tx
Frank

[pmonks]

Hibernate uses a system property called "hibernate.show_sql" that controls whether SQL is logged or not (see http://www.google.com/search?q=hibernate.show_sql for details).

Cheers,
Peter