You are viewing limited content. For full access, please sign in.

Question

Question

Low performance in Laserfiche

Laserfiche Ideas
Updated August 17, 2022
asked on August 8, 2022

Hi,

 

We have customers in which the performance of Laserfiche is quite deteriorated, all actions that users perform are slow, for example when searching for a document through the metadata the system takes more than 5 minutes to display the results when we are lucky, in other cases it throws different errors. Navigating between folders is also slow, among many other actions that users perform in the application. The workflows in which I also have search activities remain in "waiting" status for a long time until LF Workflow automatically retries the results and continues with the execution of the workflow.

In a review with the different server and database administrators of our customers, they have agreed that these situations occur because of the costly SQL statements that Laserfiche executes on the database engine to perform operations on the fields of the metadata templates. This does not have much impact when the database has few records but in cases where the propval table has a large number of records, the performance of the application is severely affected.

Here are 2 examples of the table sizes in the repository database of 2 of our customers where this situation has been occurring:

 

Customer 1:

 

 

Customer 2:

 

 

The database servers of both customers are dedicated for Laserfiche and have the following characteristics:

Windows Server 2019
SQL Server 2019
RAM: 40 Gb
CPU: Intel(R) Xeon(R) Gold 5218 CPU @ 2.30GHz, 2295 Mhz, 4 Core(s), 4 Logical Processor(s)

The cases are escalated to Laserfiche but we still don't get a definitive solution or proposal, so we are thinking of separating the information into 2 repositories and using federated search to allow users to perform searches, but we want to know if someone has experienced a similar situation and how they got the solution to this or if someone has any additional recommendations.

 

We appreciate your help!

0 0

Replies

replied on August 8, 2022 • Show version history

I would look at resource allocation and also the performance of the app server, but based on the specs you provided the CPUs may be a bottleneck in your case.

Our main repository has a lot more records than either of those customers:

  • propval: 1,129,773,256 (yes, that's Billion with a B lol)
  • doc: 211,902,263
  • toc: 64,794,880

The total database size is over 700GB.

We currently have 16 Cores and 64GB of RAM allocated to the virtual server hosting the SQL database and running Windows Server 2019; overall performance is solid.

We even have another repository and other databases hosted on that same server.

 

Something to consider is that as the database grows, maintenance on the indexes becomes more important, and efficiency also becomes a bigger priority.

For example, in the early days we had some slowness in the client because users would add a lot of "expensive" columns to their view and it has a cumulative effect.

For example, columns like Page Count and Total Document Size are calculated, not stored, so adding those is costly compared to things like Name and Template; this also applies to searches because a workflow search activity that returns a property like Total Size is infinitely more expensive than one that only returns the entry and normal fields.

More columns = more data, and more users with more columns means more strain on the database, so it's really a balancing act between resources and optimization.

 

Interestingly, we actually started with multiple smaller repositories and went the opposite direction consolidating them into one very big repository. However, one thing we did is offload much of the "high volume" processing into a separate processing repository.

For example, most documents start out in a processing repository go through all the initial workflows and checks there before getting moved into the big repository when they're complete; this helped us keep activity (i.e., db updates) to a bare minimum in the big one.

3 0
View 2 previous replies
replied on August 8, 2022

Jason beat me to most of it. I have a few things to add.

The first thing that jumped out at me is that those SQL reports show extremely high Indexes to Data size ratios for the propval (metadata) table. We spot checked a few well-performing large repositories and found the Indexes were usually 1.5-2x the Data size.

Your Customer 1 and Customer 2 have propval Indexes almost 5x the size of the Data. Here's an example from a real customer repository with pretty close to the same propval record count of Customer 2:

We suspect this indicates your customer's repository databases are desperately in need of index and statistics maintenance. I wrote a reply about doing so in another Answers post here: Re-indexing in MS SQL for Laserfiche Database

We usually recommend running index and statistics maintenance jobs weekly.

The database server is also almost certainly underpowered, especially if they're hosting any other Laserfiche application databases like Forms or Workflow. I would go with a minimum of 8 vCPU and 64 GB RAM (128 GB would be better - SQL can use it), and if possible, tempdb on a fast NVMe SSD.

Since you specifically mentioned having search performance issues, I suspect users are running Basic metadata searches. The performance of those completely falls off when you get into the hundreds of millions of metadata values.

Basic metadata searches automatically add a wildcard character to the start and end of the search term and are literally around 1,000x more computationally expensive (and thus slower) in SQL than exact match search queries. I remember one instance during performance testing where the query execution time difference between searching "*John Doe*" and "John Doe" was 17 seconds vs 17 milliseconds. The solutions are a combination of building and deploying specific Custom Quick Searches for users that use exact match searches, and Indexing fields where you need any form of partial match or fuzzy searching (names are a common one). See the post I linked earlier for some more details on search indexing.

3 0
replied on August 8, 2022

Hi Samuel,

 

We will immediately implement the recommendations you and Jason have provided.

 

we greatly appreciate your help!

1 0
replied on August 17, 2022

@████████

Would you be able to copy the information from your linked post?  It is in a private group which I cannot see.  

 

Thank you!

0 0
replied on August 17, 2022

Sure thing. Here are the relevant portions:

Question:

"This is with regards to one for our customer who has now Laserfiche Volume Size as 48 TB Cumulative and Laserfiche Repository DB crossing 500 GB+. We are facing SQL Performance issues on Laserfiche Database and I believe Re-Indexing of SQL Database is required. We would like to seek help with Re-Indexing Scripts for Laserfiche 10.4. Performance Issues are as follows."

Response:

"SQL Server Index and Statistics Maintenance is critical to keeping performance degradation with large repositories in check. Because the specifics of every repository are different with regard to indexes (e.g. repo with 1M entries, each with 100 fields looks different than 100M entries each with 10 fields), we recommend customers run a set of universal maintenance scripts that are well-regarded within the SQL Server community:

Ola Hallengren's SQL Server Index and Statistics Maintenance Solution

The customer's SQL team should use that as a starting point. If they have not run any kind of maintenance plan on indexes and statistics for a long time (or ever), we would advise rebuilding or reorganize all indexes with fragmentation and updating modified statistics on all [Laserfiche] databases (see Example B in the link).

To speak to item #2, the Laserfiche documentation suggests Indexing commonly-searched text fields if your repository contains more than one million total field values to potentially improve search performance. See: Search and Indexing Administration - Indexing Fields. Indexed field searches are performed with the Laserfiche Full-Text Search Engine (LFFTS) instead of SQL Server. See: Search and Indexing Administration.

You will want to make sure the LFFTS service is on a dedicated server (if it's not already) with a minimum of 8 vCPU, 64 GB RAM, and the search catalog files on fast SSD-backed storage. Double those resources would be appropriate given the repository size."

0 0
replied on August 17, 2022

Thank you!!!!

0 0
replied on August 8, 2022

 Hi Jason,

 

Thank you for your valuable help.

 

We will keep in mind your experience and recommendations.

 

Regards,

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...