SQL server performance issues.
We have several environments Nuxeo 6.0 + SQL Server 2012 and we are experiencing some performance issues.
When we add documents the system slows down. The whole system slows down even login user. For example, with the newly installed system to create 100 documents takes 5 seconds, ith 50,000 documents in the system it takes to create 100 docs, 20 seconds, and with 150,000 documents in the system takes 38 seconds to create 100 docs.
The operating system is ubuntu jessie / sid (Nuxeo) and Windows 2012 server (database). We have followed the procedures of this page https://doc.nuxeo.com/display/ADMINDOC/Configuring+MS+SQL+Server , installing nuxeo from tar.gz file.
Nuxeo with postfgresql does not have this comportment, the same 100 documents takes 3/4 seconds to create.
Is there any specific configuration or upgrade for Nuxeo + mssql?
thanks in advance.
 |
Jose Luis Fernandez
Member (85) 1 1 1 |
11/02/2015 ( History) |
Hi Julien, thank you for your comments.
We have SQL Server 2014 Standard edition running over m4.large EC2 instance.
We have done some testing and see that the CPU time on some stored procedures is high. I attached a screenshot with some data.
What versions and equipment requirements recommended for work with Nuxeo + mssql in good condition?
BR,
|
Jose Luis Fernandez
Member (85) 1 1 1 |
11/03/2015 ( History) |
It sounds that in your case the stored procedure that maintains the read ACL cache is affected by the number of documents in the repository. Our recommendation to get good performance with this database is to disable the stored procedures and fulltext indexing and to rely on Elasticsearch.
You can do this by following this documentation: http://doc.nuxeo.com/display/NXDOC/Moving+Load+from+Database+to+Elasticsearch
Basically:
Edit the nuxeo.conf and set elasticsearch.override.pageproviders with the full list of page providers (the list is provided in comment), also set nuxeo.vcs.fulltext.search.disabled=true
change the repository configuration so aclOptimizations and pathOptimizations is disable.
Regards ben
SQL server has some known limitations: ADMINDOC/Configuring+MS+SQL+Server#ConfiguringMSSQLServer-Limitations
In addition, the SQL server performances highly depend on your Microsoft license: have you paid for using all available system resources (memory and CPU)?
 |
Julien Carsique
DevTools Team manager (19.2K) 4 7 7 |
11/02/2015 ( History) |