Top 10 performance issues with a Database

Here is a list of top 10 performance issues with a Database and their most probable solutions

Too many calls to the DB - There might be multiple trips to a single DB from various middleware components and any of the following scenarios will occur
1. More data is requested than necessary, primarily for faster rendering (but in the slowing down the entire performance )
2. Multiple applications requesting for the same data.
3. Multiple queries are executed which in the end return the same result
This kind of problem generally arises when there is too much object orientation. The key is to strike a balance between how many objects to create and what to put in each object. Object oriented programing may be good for maintenance, but it surely degrades performance if they are not handled correctly

Too much synchronization – Most developers tend to over-synchronize, large pieces of code are synchronized by writing even larger pieces of code. It is generally fine when there is low load, under high load, the performance of the application will definitely take a beating. How to determine if the application has sync issues. The easiest way (but not 100% fool proof) is to chart CPU time and Execution time
CPU Time – is the time spent on the CPU by the executed code
Execution time - This is the total time the method takes to execute. It includes all times including CPU, I/O, waiting to enter sync block, etc
Generally the gap between the two times gives the waiting time. If our trouble making method does not make an I/O call nor an external call, then it’s most probably a sync issue that is causing the slowness.

Joining too many tables – The worst kind of SQL issues creep up when too many tables are joined and the data is to be extracted from it. Sometimes it is just unfortunate that so many tables have to be necessarily joined to be able to pull out the necessary data.
There are two ways to attack this problem
1) Is it possible to denormalize a few tables to have more data?
2) Is it possible to create a summary table with most of the information that will be updated periodically?
Returning a large result set - Generally no user will go through thousands of records in the result set. Most users will generally limit to only the first few hundreds (or the first 3 -4 pages). By returning all the results, the developer is not only slowing the database but also chocking the network. Breaking the result set into batches (on the database side) will generally solve this issue (though not possible always)

Joining tables in the middleware – SQL is a fantastic language for data manipulation and retrieval. There is simply no need to move data to a middle tier and join tables there. Generally by joining data in the middle tier:
1. Unnecessary load on the network as it has to transport data back and forth
2. Increasing memory requirements on the application server to handle the extra load
3. Drop in server performance as the app tier is mainly held up with processing large queries
The best way to approach this problem is to user Inner and Outer joins right in the database itself. By this, the all the power of SQL and the database is utilized for processing the query.

Ad hock queries – just because SQL gives the privilege to create and use ad-hock queries, there is no point in abusing them. In quite a few cases it is seen that ad-hock queries create more mess than advantage they bring. The best way is to use stored procedures. This is not always possible. Sometimes it is necessary to use ad-hock queries, then there is no option but to use them, but whenever possible, it is recommended to use stored procedures. The main advantage with stored procedures is
1. Pre compiled and ready
2. Optimized by DB
3. Stored procedure in on the DB server, i.e. no network transmission of large SQL request.

Lack of indices – You see that the data is not large, yet the DB seems to be taking an abnormally long time to retrieve the results. The most possible cause for this problem could be lack of or misconfigured index. At first sight it might seem trivial, but when the data grows large, then it plays a significant role. There can be significant hit in performance if the indices are not configured properly.

Fill factor – One of the other things to consider along with index is fill factor. MSDN describes fill factor as a percentage that indicates how much the Database Engine should fill each index page during index creation or rebuild. The fill-factor setting applies only when the index is created or rebuilt. Why is this so important? If the fill factor is too high, if a new record is inserted and index rebuilt, then the DB will more often than not split the index (page splitting) into a new page. This is very resource intensive and causes fragmentation. On the other hand having a very low value for fill factor means that lots of space is reserved for index alone. The easiest way to overcome this is to look at the type of queries that come to the DB; if there are too many SELECT queries, then it is best to leave the default fill factor. On the other hand if there are lots of INSERT, UPDATE and DELETE operations, a nonzero fill factor other than 0 or 100 can be good for performance if the new data is evenly distributed throughout the table.

My Query was fine last week but it is slow this week?? – We get to see a lot of this. The load test ran fine last week but this week the search page is taking a long time. What is wrong with the database? The main issue could be that the execution plan (the way the query is going to get executed on the DB) has changed. The easiest way to get the current explain plan the explain plan for the previous week, compare them and look for the differences.

High CPU and Memory Utilization on the DB – There is a high CPU and high Memory utilization on the database server. There could be a multitude of possible reasons for this.
1. See if there are full table scans happening (soln: create index and update stats)
2. See if there is too much context switching (soln: increase the memory)
3. Look for memory leaks (in terms of tables not being freed even after their usage is complete) (soln: recode!)

There can be many more reasons, but there are the most common ones.

Low CPU and Memory utilization yet poor performance – This is also another case (though not frequent). The CPU and memory are optimally used yet the performance is still slow. The only reason why this can be is for two reasons:
1. Bad network – the database server is waiting for a socket read or write
2. Bad disk management – the database server is waiting for a disk controller to become free

As always these are only the most common database performance issues that might come up in any performance test. There are many more of them out there...

Mitigation of Performance Testing Impediments

An impediment is anything that prevents people from doing their job. Here are some impediments that performance testing teams have encountered.

A. Unavailability of subject matter / technical experts such as developers and operations staff.

B. Unavailability of applications to test due to delays or defects in the functionality of the system under test.

C. Lack of Connectivity/access to resources due to network security ports being available or other network blockage.

D. The script recorder fails to recognize applications (due to non-standard security apparatus or other complexity in the application).

E. Not enough Test Data to cover unique conditions necessary during runs that usually go several hours.

F. Delays in obtaining or having enough software licenses and hardware in the performance environment testing.

G. Lack of correspondence between versions of applications in performance versus in active development.

H. Managers not familiar with the implications of ad-hoc approaches to performance testing.

Fight Or Flight? Proactive or Reactive?

      Some call the list above "issues" which an organization may theoretically face.

      Issues become "risks" when they already impact a project.

      A proactive management style at a particular organization sees value in investing up-front to ensure that desired outcomes occur rather than "fight fires" which occur without preparation.

      A reactive management style at a particular organization believes in "conserving resources" by not dedicating resources to situations that may never occur, and addressing risks when they become actual reality.

  
Subject Matter Expertise

      The Impediment
      Knowledge about a system and how it works are usually not readily available to those outside the development team.

      What documents written are often one or more versions behind what is under development.

      Requirements and definitions are necessary to separate whether a particular behavior is intended or is a deviation from that requirement.

      Even if load testers have access to up-to-the-minute wiki entries, load testers usually are not free to interact as a peer of developers.

      Load testers are usually not considered a part of the development team or even the development process, so are therefore perceived as an intrusion to developers.

      To many developers, Performance testers are a nuisence who waste time poking around a system that is "already perfect" or "one we already know that is slow".

      What can reactive load testers do?
      Work among developers and easedrop on their conversations (like those studying animals in the wild).

      What can proactive load testers do?
      Up-front, an executive formally establishes expectations for communication and coordination between developers and load testers.

      Ideally, load testers participate in the development process from the moment a development team is formed so that they are socially bonded with the developers.

      Recognizing that developers are under tight deadlines, the load test team member defines exactly what is needed from the developer and when it is needed.

      This requires up-front analysis of the development organization:

          o the components of the application
          o which developers work on which component
          o contact information for each developer
          o existing documents available and who wrote each document
          o comments in blogs written by each developer

      An executive assigns a "point person" within the development organization who can provide this information.

      Assignments for each developer needs to originate from the development manager under whom a developer works for.

            When one asks/demands something without the authority to do so, that person would over time be perceived as a nuisence.

            No one can serve two masters. For you will hate one and love the other; you will be devoted to one and despise the other.

      A business analyst who is familiar with the application's intended behavior makes a video recording of the application using a utility such as Camtasia from ToolSmith. A recording has the advtange of capturing the timing as well as the steps.

               

The U.S. military developed the web-based CAVNET system to collaborate on innovations to improvise around impediments found in the found.


Availability of applications

      The Impediment
      Parts of an applications under active development become inacessible while developers are in the middle of working on them.

      The application may not have been built successfully. There are many root causes for bad builds:

          o Specification of what goes into each build are not accurate or complete.
          o Resources intended to go into a particular build are not made available.
          o An incorrect version of a component is built with newer incompatible components.
          o Build scripts and processes do not recognize these potential errors, leading to build errors.
          o Inadequate verification of build completeness.

      What can reactive load testers do?
      Frequent (nightly) builds may enable testers more opportunities than losing perhaps weeks wait for the next good build.

      Testers switch to another project/application when one application cannot be tested.

      What can proactive load testers do?
      Use a separate test environment that is updated from the development system only when parts of the application become stable enough to test.

      Have a separate test environment for each version so that work on a prior version can occur when a build is not successful on one particular environment.

      Develop a "smoke test" suite to ensure that applications are testable.

      Coordinate testing schedules with what is being changed by developers.

      Analyze the root causes of why builds are not successful, and track progress on elminating those causes over time.

               
Connectivity/access to resources

      The Impediment
      Workers may not be able to reach the application because of network (remote VPN) connectivity or security access.

      What can reactive load testers do?
      Work near the physical machine.

      Grant unrestricted access to those working on the system.

      What can proactive load testers do?
      Analyze the access for each functionality required by each role.

      Pre-schedule when those who grant access are available to the project.

               
 Script Recorder Recognition

      The Impediment
      Load test script creation software such as LoadRunner work by listening and capturing what goes across the wire and display those conversations as script code which may be modified by humans.

      Such recording mechanisms are designed to recognize only standard protocols going through the wire.

      Standard recording mechanisms will not recognize custom communications, especially within applications using advanced security mechanisms.

      Standard recording mechanisms also have difficulty recognizing complex use of Javascript or CSS syntax in SAP portal code.

      What can reactive load testers do?
      Skip (de-scope) portions which cannot be easily recognized.

      What can proactive load testers do?
      To ensure that utility applications (such as LoadRunner) can be installed, install them before locking down the system.

      Define the pattern install them before locking down the system.

               
 Test Data

      The Impediment
      Applications often only allow a certain combination of values to be accepted. An example of this is only specific postal zip codes being valid within a certain US state.

      Using the same value repeatedly during load testing does not create a realistic emulation of actual behavior because most modern systems cache data in memory, which is 100 times faster than retrieving data from a hard drive.

      This discussion also includes role permissions having a different impact on the system. For example, the screen of an administrator or manager would have more options. The more options, the more resources it takes just to display the screen as well as to edit input fields.

      A wide variation in data values forces databases to take time to scan through files. Specifying an index used to retrieve data is the most common approach to make applications more efficient.

      Growth in the data volume handled by a system can render indexing schemes inefficient at the new level of data.

      What can reactive load testers do?
      Use a single role for all testing.

      Qualify results from each test with the amount of data used to conduct each test.

      Use trial-and-error approachs to finding combinations of values which meet field validation rules.

      Examine application source code to determine the rules.

      Analyze existing logs to define the distribution of function invocations during test runs.

      What can proactive load testers do?
      Project the likely growth of the application in terms of impact to the number of rows in each key data variable. This information is then used to define the growth in row in each table.

      Define procedures for growing the database size, using randomized data values in names.

               
 Test Environment

      The Impediment
      Creating a separate enviornment for load testing can be expensive for a large complex system.

      In order to avoid overloading the production network, the load testing enviornment is often setup so no communication is possible to the rest of the network. This makes it difficult to deploy resources into the environment and then retrieve run result files from the environment.

      A closed environment requires its own set of utility services such as DNS, authentication (LDAP), time sychronization, etc.

      What can reactive load testers do?
      Change network firewalls temporarily while using the development environment for load testing (when developers do not use it).

      Use the production fail-over environment temporarily and hope that it is not needed during the test.

      What can proactive load testers do?
      Build up a production environment and use it for load testing before it is used in actual production.

               
Correspondance Between Versions

      The Impediment
      Defects found in the version running on the perftest environment may not be reproducible by developers in the development/unit test environments running a different (more recent) version.

      Developers may have moved on to a different version, different projects, or even different employers.

      What can reactive load testers do?
      Rerun short load tests on development servers. If the server is shared, the productivity of developers would be affected.

      What can proactive load testers do?
      Before testing, freeze the total state of the application in a full back-up so that the exact state of the system can be restored, even after changes are tried to diagnose or fix the application on the system where it's found.

      Run load tests with trace logs information. This would not duplicate how the system is actually run in production mode.

               
 Ad-hoc Approaches

      The Impediment
      Most established professional fields (such as accounting and medicine) have laws, regulations, and defined industry practices which give legitimacy to certain approaches. People are trained to follow them. The consequences of certain courses of action are known.

      But the profession of performance and load testing has not matured to that point.

      The closest industry document, ITIL, is not yet universally adopted. And ITIL does not clarify the work of performance testing in much detail.

      Consequently, each individual involved with load testing is likely to have his/her own opinions about what actions should be taken.

      This makes rational exploration of the implications of specific courses of action a conflict-ridden and thus time-consuming and expensive endeavor.

      What can reactive load testers do?
      Allocate time for planning before starting actual work until concurrance on the project plan is achieved among the stakeholders.

      Revise project completion estimates or scope as new information becomes available.

      What can proactive load testers do?
      Before the project gets away, agree on the rationale for elements of the project plan and who will do what when (commitments of tasks and deliverables). This is difficult for those who are not accustomed to being accountable, and requests for it would result in withdrawl or other defensive behavior.

      Identify alternative approaches and analyze them before managers come up with it themselves.

      Up-front, identify how to contact each stakeholder and keep them updated at least weekly, and immediately if decisions impact what they are actively working on.

      If a new manager is inserted in the project after it starts, review the project plan and rationale for its elements.

Performance Testing in Agile Framework

This is again not a new thing in the industry. It has been there for some time now. At the first look performance testing within Agile framework do not seem to fit in as performance testing for a large part has always been done within the functionally-stable-code paradigm.

On the other side performance testing at an early stage looks to be a very good idea. The reason is there will be limited possibility of presence hotspots in code-base. The main performance bottlenecks that may be present, would be more on the infrastructure side which are more easier to fix and with almost no functional regression-testing.  Thus we do have a very good case for performance testing to be in Agile framework.

Although agile after each sprint delivers a workable product, there are some challenges that would crop-up here -

1. Stakeholders Buy-in
2. Project Management Vision
3. Definition of the SLAs
4. Unstable builds
5. Development and Execution of Test Cases
6. Highly skilled performance team

1. Stakeholders Buy-in

This is the most important aspect for any project. From a performance point of view this becomes altogether more important. Having an additional performance test team from the early phases of this project does put an additional strain on the budget and the timeline of the projects. Furthermore the advantages of performance testing are more of an intangible kind than say a functional testing. Thus it becomes a tad more difficult to get a stakeholders buy-in.

2. Project Management Vision

Despite the importance of the performance of an application, many project managers are still unaware of the performance test processes and the challenges a performance team faces. This apathy has been the undoing of many good projects. Within Agile framework, this would cause more conflicts between the project managers and performance teams. The ideal way would be to have a detailed meeting between the developers, performance architect/team and project managers and chalk out a plan which would decide the following -

• Modules to be targeted for performance testing
• Approach of performance testing
• In which sprint to begin of performance testing?
• Identification of Module-wise load that needs to be simulated
• Profiling Tool Selection
• Transition of Performance Tool – This would happen, as initially tools like JPerfUnit may be used and at a later stage end-to-end load testing tools like LoadRunner or SilkPerformer may be used.

These are some of the many parameters that needs to be defined at a project management level.

3. Definition of SLAs

This will be one of the most challenging aspect as business can always give an insight on end-to-end SLA. However, at a module-level it becomes more challenging. Here there arises the need for developer and performance architect to arrive at an estimated number. Apart from this,  there may be a need for de-scoping of some modules as not all modules may be critical from performance stand-point.

4. Development and Execution of Test Cases

Although agile delivers a workable product at the end of every sprint, we may not be able to use the standard load testing tools as not all components would be present which a standard load-testing tool may require.  So in most cases, there is an inherent challenge in simulating the load.  Tools may very between phases. For instance, JPerfUnit, a  stub or test harness might be used in the initial phases.  An extra development time for this would have to be estimated in the initial project planning phase. Finally, once created, development and execution of test cases would follow. With the help of profiling tool, most performance hotspots can be identified early.

At later stages of the project, the traditional performance scripting and execution will replace the harness that was created in the earlier stage. So there will be a big re-work which again would be a challenge considering the sprint-timeline. So sprint should be planned taking this into consideration too.

5. Unstable builds

As development and testing is going on simultaneously, a break-off time would be needed within a sprint. The other alternative is to build the script on a continuous basis based which is quite difficult. Performance team would then create the required test script and test the code. This break-off point is only to enable performance team to work on a particular test case. If major changes occur after the break-off time, they will have to be incorporated in the next sprint.

6. High-skilled Performance Team

Last but not the least as the cliché goes. More than the skill-level, it is imperative that team members should have the confidence to get their hands dirty learning about the system and innovating as challenges continue to crop-up during the various sprints, keeping in mind the performance test approach, and thus ensuring a quality product.

The agile methodology does present lot of challenges at least from performance stand-point. However, if the performance angle is kept in mind from the beginning, it will certainly help in reducing lot of pain later. From a performance tester stand-point, agile is a gold-mine, as you get involved with developers at an early stage of the application. This would help in greater understanding of the application and its internal workings which eventually would help in better identification of performance bottlenecks.