Optimization of DB Server and Web Server to Enhance the Performance of ECM

Abstract

In order to develop ECM system, there are a number of methodologies. Adopting Microsoft's solution and methodology is one of them. ECM has a large number of users to save the documentations in various types of multimedia data. So, managing multimedia database is very critical in ECM. Therefore the unit and integration test to evaluate the performance can detect the flaws of the system early on, and it has to be enables to reflect the user's requirements thru the user acceptance test. In this paper, we are discussing how to optimize the SQL database before the ECM system is built and used in the real situation thru unit and integration tests.

1. INTRODUCTION

In companies which employ large numbers of knowledge workers, the large volume of nonstructured contents generated by knowledge workers are increasingly viewed as vital intellectual assets. In order to manage such contents efficiently, it is necessary to implement an Enterprise Content Management(ECM) system to address the inefficiencies arising from personal PC-based document management[1-2].

To implement an ECM system, several wellknown market alternatives exist: IBM FileNet, ECM Documentum and SharePoint-based ECM which adopts MSF(Microsoft Solutions Framework) development methodology[3-6].

Given the familiarity users across the world typically feel for Microsoft software, which extends to Office applications such as Excel and PowerPoint, there is wide scope for benefits to be derived from the ease of adoption of Windows OS, along with similar server management software, such as Windows Server[7].

This paper thus contributes to the field through providing a workable example of the benefits of optimizing the performance of Web and SQL DB Server through unit and integration testing conducted prior to system operation. And also it could be applied to improve specific performance value of MS Windows server systems[8-9].

 

2. RELATED RESEARCH

System testing allows a high-quality, errorfree system to be implemented in a way that satisfies customer requirements. ECM testing is carried out in the following sequential phases:

(1) Application of MSF methodology to use the testing technique

(2) Test based on hierarchical queries for efficient management of resources used in the testing

(3) Compliance with the quality assurance system through confirmation of the standard by phase-based compliance, test results and troubleshooting

(4) Efficient allocation of resources to segment relevant tasks and the integration of the connection parts of each unit

The logon process can have the heaviest traffic in ECM system, as a business portal, which has attribute that the employees go to work and logon at the same time. They use different documentations at work. Therefore we have to concentrate the performance tuning in logon process. The most important performance counters and threshold values in logon process were analyzed as shown in table 1.

Table 1.The performance counters and Threshold values

The performance evaluation counters for database in ECM system among various counters were listed in table 2.

Table 2.The performance monitoring counters for the DB

The priority of the performance counters for the file & printer servers summarized in previous table summary files is "memory > processor > disk > network". Here are the important performance counters among these as shown below.

 

3. ECM system design with Microsoft solution

The hardware of ECM system for a large number of knowledge workers can be configured as shown in Figure 1.

Fig. 1.Sample of the hardware configuration.

We can monitor the performance of Fast search in Sharepoint through SCOM(System Center Operations Manager). MGE, storage control, can identify the individual space and control the certain area of storage. It will determine where to store the working data ensuring the business continuity.

The ECM system design is implemented to best utilize the 64-bit server and interact with the latest desktop environments. High-level load balancing and stability are ensured through SharePoint configuration information backup and recovery, application-service duplexing, data sharing and partitioning and DB mirroring. Figure 2 shows an overview of the server structure, built around such features.

Fig. 2.ECM structure design with MS solution.

For service duplexing, two search servers - searcher and collector - were configured as Active-Active to ensure high-availability. The indexer was configured as Active-Passive to activate the indexer in the second search server when executing indexing. Table 3 shows the specifications of the ECM system.

Table 3.Hardware specification for ECM

Abbreviation - EP: Enterprise, ST: Standard, W·S: Windows Server, S·S: SQL Server, SP: SharePoint, SP·S: SharePoint Server, App: Application, F·S: FAST Search, STP: Storage Point

 

4. ECM implementation using Microsoft solution

Figure 3 shows an example of the master page implemented using SharePoint.

Fig. 3.An example of the master page using SharePoint

Item ① on the screen above provides features like web part, functions listed in the tab, advanced search, history. Meanwhile, item ② shows the navigation information as it relates to the search result. Developers may click on relevant item to navigate to the search results.

Item ③ searches relevant people who are experts in the search topics and shows their work - documents - and item ④ is a feature that shows the most popular search terms in that week. If a term is clicked, users are forwarded to the relevant search results.

In order to monitor the servers, we can use the performance monitor monitoring program provided by Windows Server, As shown in Figure 4, after we create the log file, we can start the performance objects and counters.

Fig. 4.Create performance log files and add the counters.

When we firstly select the performance object, counter and instance lists are shown. After select a performance object, we can select a counter & instance, and add the required counter. When we add a counter list, we can choose the data sample interval considering the disk space. The generated performance counter log files are shown in Figure 5.

Fig. 5.Status of creation of performance counter log.

In the figure, we can see the three performance logs such as "EKP_performance logs", "System Overview", and "jaehlee". In addition, it shows the information about the route of each logs.

 

5. ECM performance testing with Microsoft solution

5.1 Overview of web server performance improvement

In regard to the improvement of web server performance, the problems identified were as follows:

5.1.1 Memory leak

IIS was being recycled on a regular basis due to a suspected memory leak from a specific process. Hence, when memory usage dropped to nearly 0%, a log of IIS Recycling was created. This problem was primarily found to have occurred in source code which permitted web applications to over-use the memory. Its cause is suspected to have been an absence of deallocation of the memory used from the ASP page. Indeed, the ASP page uses about 30-40MB of memory according to performance. Thus, the memory usage of the ASP page takes up only a small proportion of the total memory usage. It was, however, determined that there were some memory leaks.

5.1.2 IIS Recycling(web server rebooting)

In principle, a normal system does not need to restart IIS or reboot the web server. However, the main causes for the current IIS recycling were found to be Dead Lock State and insufficient memory.

Dead Lock State occurs when the ASP.NET worker process has been idle for the time specified by responseDeadlockInterval in the Machine. config file. At this point, ASP.NET assumes that there is a deadlock and thus, the ASP.NET worker process (Aspnet_wp.exe) is recycled, albeit unexpectedly.

5.1.3 Excessive use of session

The use of the session to archive user-generated data is not recommendable and it may cause overheads when managing sessions in the server. If a user logs on, creates a session and then terminates the browser, the session remains on the server, leading to resources being tied up on redundant tasks.

5.1.4 Problem with transaction handling

There were a number of codes in which a lock is obtained using Begin Tran and other logic, rather than actual DB-related operations. The solution to this problem is as in the following:

5.2 Overview of DB server performance improvement

The following problems were found in the DB Server through the unit and integrating testing:

The main causes of the bottlenecks are as in the following:

In order to distribute the load of DB and web servers, the table used in the new test module related to DB management and several relevant procedures were examined. A significant improvement in performance was found to be achievable through tuning.

The review of the 5 data tables used in the new test module revealed several columns in USERDATA and USERINFO tables in which INSERT frequently occurs, which are defined as VARCHAR data type.

As CHAR is a fixed-length data type, it is stored in the reserved memory, making it faster in terms of reading and writing speeds. VARCHAR, by contrast, is fast in reading, but slow in writing as it requires the system to compute the length of input first and to then dynamically allocate the memory accordingly. Table 4 shows As-Is and To-Be, the latter being the improvements realized through analysis of the new module schema operating the DB.

Table 4.Review of new DB operation modules

The review of user log-in stored procedure among new test modules revealed that a temporal variable was used to store the information of users with an IPADDRESS that is the same or identical to the USERID of those who log in from the USERDATA table.

USERID and IPADDRESS columns in USERDATA table are unique, and as these columns store only two rows, they may negatively affect performance.

As shown below, the OR, <> or NOT operators were used in the WHERE clause in SELECT and DELETE statements. This operator, however, cannot make full use of the index. Therefore, it is more effective, in terms of performance, to separate it into two lines. Furthermore, when deleting a record, the system first retrieves the relevant data, which again may act to degrade the performance. Table 5 below shows the current user Log-in Stored Procedure among new test modules.

Table 5.Login Stored Procedure : As-Is

Based on the analysis of the modules implemented, it was found that by declaring a local variable instead of the table variable in the Stored Procedure called upon in USP-LOGIN, significant performance improvements can be realized.

For USER and IPADDRESS columns in the USERDATA table, the general, local variable was declared, instead of the table variable.

An improvement was thus shown to be realized through the table variable least used in the new module and the OR operator in the WHERE clause in SELECT statement being handled with the local variable only.

Further improvement was found to be realized through alterations to code which does not make the most of the index due to the use of <> and NOT operator in the WHERE clause. Changing it into a query statement that uses a clustered index in the USERDATA table leads to performance gains. Table 6 shows USP_LOGIN module improved through tuning.

Table 6.Login Stored Procedure : To-Be

5.3 Evaluation after DB Server improvement

The modified USP_LOGIN procedure was tested through a process of first tuning by focusing on the alterations made. It was found that CPU usage is reduced while processing speed per second has been increased, as shown in Figure 6.

Fig. 6.Evaluation of DB-related module performance

Table 7 shows the performance of the DB server before and after the tuning.

Table 7.Comparison of performance after DB server tuning

With Stored Procedure in which advanced USP_LOGIN is applied, EXECUTION TOTAL COST was reduced by 20.77% compared to that of the existing module, which is 79.23%. TOTAL PAGE READ count was reduced from 38 to 22 and the average CPU usage decreased to 17.178% while the number of SQL queries handled per second increased from 1,192.963 to 1,303.129–clearly demonstrating that the performance of the server has been improved.

Figure 7 shows a chart picture about the measure value of % Processor Time and Batch requests\sec, which are the important performance counters of database server.

Fig. 7.% Processor Time and Batch requests\sec monitoring.

Among the measured performance counters, % Processor Time showed average 0.4% up to 1.2% per day with the even curve regardless of the peak time. Batch requests\sec showed average 11 process per second per day, not so heavy. Table 8 shows the final report on DB server improvement project.

Table 8.Result of DB server improvement

In addition, the number of log-ins were surveyed for the period following the system improvements. Figure 8 below clearly shows an increasing number of log-ins over time.

Fig. 8.Increase in the number of log-ins after improving DB server.

After this performance improvement project, the number of daily log-ins increased. Significantly, the number of users of each DB server marked a significant increase in the third week.

Such continued increase in the number of users is evidence of the system being stabilized in the initial stages of its implementation and deployment.

 

6. CONCLUSIONS

This paper has shown a way to optimize DB and web servers in order to improve the performance of ECM as based on MSF development methodology.

In terms of the web server performance, this paper provides an example of how the use of accurate code can resolve performance degradation issues. Such issues were in this paper caused by a memory leak due to inadequate handling of an exception in ASP pages, IIS recycling resulting from a Deadlock State involving insufficient memory, and a problem with transaction handling.

In order to improve the performance of the DB server, the new module schema that controls the DB was analyzed and the complicated query statements which resulted in bottlenecks were modified. Such bottlenecks were found to be the cause of excessive page reading.

The evaluation of new Stored Procedure, in which the improvement scheme for DB control module is applied, showed that execution total cost, total page read and CPU usage were reduced compared to that of the previous module. The number of SQL queries handled per second was found to have increased, evidence of server performance having been improved.

Finally, the number of users was surveyed for a period following the DB performance improvement project. There was found to have been an increase in the number of log-ins over time.

With the improved system, the number of users had increased significantly by the third week following implementation, proving that the system can be stabilized in the early stages of its deployment through tuning.